Уважаемые Гуру !!!
Очень нужна ваша помощь.
Есть два массива чисел. Один массив содержится в столбце А (условно это некие значения показателя до каких-то преобразований).
Второй массив содержится в столбце 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))}
то ошибки нет, но значение рассчитывается неправильно из-за того, что в массивах есть нулевые значения.
Подскажите, как нужно правильно написать формулу или может быть вы подскажите другой вариант решения поставленной задачи?
Спасибо.