Загальна формула
=SUM(firstcell:INDEX(data,rows,cols))
Резюме
Щоб визначити діапазон на основі значення в іншій комірці, ви можете використовувати функцію INDEX. У наведеному прикладі формула в J7 має вигляд:
=SUM(C5:INDEX(data,J5,J6))
де "дані" - названий діапазон B5: G9.
Пояснення
Ця формула спирається на конкретну поведінку INDEX - хоча здається, що INDEX повертає значення в певному місці, насправді повертає посилання на місце. У більшості формул ви не помітите різниці - Excel просто оцінює посилання та повертає значення. Ця формула використовує цю функцію для побудови динамічного діапазону на основі введення робочого аркуша.
Усередині функції сума перша посилання - це просто перша комірка в діапазоні, яка охоплює всі можливі комірки:
=SUM(C5:
Щоб отримати останню комірку, ми використовуємо INDEX. Тут ми надаємо INDEX названий діапазон "дані", який є максимально можливим діапазоном значень, а також значення з J5 (рядки) та J6 (стовпці). INDEX не повертає діапазон, він повертає лише одну клітинку в цьому місці, E9 у прикладі:
INDEX(data,J5,J6) // returns E9
Вихідна формула зведена до:
=SUM(C5:E9)
який повертає 300, сума всіх значень у C5: E9.
Формула в J8 майже однакова, але використовує AVERAGE замість SUM для обчислення середнього. Коли користувач змінює значення в J5 або J6, діапазон оновлюється, і повертаються нові результати.
Альтернатива OFFSET
Ви можете створити подібні формули за допомогою функції OFFSET, показаної нижче:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
OFFSET призначений для повернення діапазону, тому формули, можливо, простіші для розуміння. Однак OFFSET - це нестабільна функція, яка може спричинити проблеми з продуктивністю при використанні у великих, більш складних робочих аркушах.
