Страницы: 1
RSS
Как правильно указать для функции ИНДЕКС динамический адрес ячейки со ссылкой на другой лист при работе с массивами
 
Уважаемые Гуру !!!
Очень нужна ваша помощь.

Есть два массива чисел. Один массив содержится в столбце А (условно это некие значения показателя до каких-то преобразований).
Второй массив содержится в столбце B (условно это некие значения того же показателя после проведения каких-то преобразований).
Задача - одной формулой найти среднее значение разности между всеми парами значений.

Формула {=СРЗНАЧ(ИНДЕКС(B2:B12;0)-ИНДЕКС(A2:A12;0))} успешно решает данную задачу,
Но если задачу усложнить и указать, что оба этих массивов динамические, т.е. в разных случаях могут быть разное кол-во пар значений, но не более 100, то такую задачу можно решить, использовав такую формулу:
{=СРЗНАЧ(ИНДЕКС(B2:ДВССЫЛ(АДРЕС(СЧЁТ(B2:B100)+1;2));0)-ИНДЕКС(A2:ДВССЫЛ(АДРЕС(СЧЁТ(A2:A100)+1;1));0))}

Теперь еще усложняем эту задачу. Необходимо последнюю формулу перенести на второй лист, чтобы массив данных был на первом листе, а среднее значение выводилось именно на втором листе.
Я попробовал добавить в формулу ссылку на первый лист, где находятся данные:
{=СРЗНАЧ(ИНДЕКС(Лист1!B2:ДВССЫЛ(АДРЕС(СЧЁТ(Лист1!B2:B100)+1;2));0)-ИНДЕКС(Лист1!A2:ДВССЫЛ(АДРЕС(СЧЁТ(Лист1!A2:A100)+1;1));0))}
но EXCEL  в таком случае выдает ошибку #ЗНАЧ!.
При этом, если ДВССЫЛ(АДРЕС(СЧЁТ(Лист1!B2:B100)+1;2) заменить жестко на адрес ячейки, например B100, а ДВССЫЛ(АДРЕС(СЧЁТ(Лист1!A2:A100)+1;1)) заменить на A100:
{=СРЗНАЧ(ИНДЕКС(Лист1!B2:B100;0)-ИНДЕКС(Лист1!A2:A100;0))}
то ошибки нет, но значение рассчитывается неправильно из-за того, что в массивах есть нулевые значения.

Подскажите, как нужно правильно написать формулу или может быть вы подскажите другой вариант решения поставленной задачи?
Спасибо.
 
Код
=СРЗНАЧ(Лист1!B2:ИНДЕКС(Лист1!B2:B30;ПОИСКПОЗ(9^9;Лист1!B2:B30;1))-Лист1!A2:ИНДЕКС(Лист1!A2:A30;ПОИСКПОЗ(9^9;Лист1!A2:A30;1)))
 
Код
=СУММПРОИЗВ(Лист1!B2:B30-Лист1!A2:A30)/СЧЁТЕСЛИ(Лист1!A2:A30;"<>")
 
V, спасибо. Обе формулы работают.

Теперь такой вопрос, а как надо изменить эти формулы, чтобы можно было оставить массив данных в одном файле, а в другой выводить среднее значение разностей, обращаясь к этим данным?
Я попробовал Вашу формулу перенести в другой файл, она работает только если файл с массивом данных открыт. Если его закрыть, то и первый и второй вариант формулы выдает ошибку #ЗНАЧ!

Спасибо.
Страницы: 1
Наверх