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