Общая формула
=SUM(firstcell:INDEX(data,rows,cols))
Резюме
Чтобы определить диапазон на основе значения в другой ячейке, вы можете использовать функцию ИНДЕКС. В показанном примере формула J7 выглядит так:
=SUM(C5:INDEX(data,J5,J6))
где «данные» - именованный диапазон B5: G9.
Объяснение
Эта формула зависит от определенного поведения INDEX - хотя кажется, что INDEX возвращает значение в определенном месте, на самом деле он возвращает ссылку на это местоположение. В большинстве формул вы не заметите разницы - Excel просто оценивает ссылку и возвращает значение. Эта формула использует эту функцию для создания динамического диапазона на основе ввода листа.
Внутри функции суммы первая ссылка - это просто первая ячейка в диапазоне, который охватывает все возможные ячейки:
=SUM(C5:
Чтобы получить последнюю ячейку, мы используем ИНДЕКС. Здесь мы указываем INDEX именованный диапазон «data», который представляет собой максимально возможный диапазон значений, а также значения из J5 (строки) и J6 (столбцы). INDEX не возвращает диапазон, он возвращает только одну ячейку в этом месте, E9 в примере:
INDEX(data,J5,J6) // returns E9
Исходная формула сводится к:
=SUM(C5:E9)
который возвращает 300, сумму всех значений в C5: E9.
Формула в J8 почти такая же, но для вычисления среднего используется СРЕДНЕЕ вместо СУММ. Когда пользователь изменяет значения в J5 или J6, диапазон обновляется, и возвращаются новые результаты.
Альтернатива со смещением
Вы можете построить аналогичные формулы с помощью функции СМЕЩЕНИЕ, показанной ниже:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
OFFSET предназначен для возврата диапазона, поэтому формулы, возможно, проще для понимания. Однако СМЕЩЕНИЕ является непостоянной функцией и может вызвать проблемы с производительностью при использовании в более крупных и сложных рабочих листах.
