Загальна формула
=SUM(INDEX(range,N(IF(1,(1,2,3)))))
Резюме
Щоб INDEX повернув масив елементів іншій функції, ви можете використати незрозумілий фокус, заснований на функціях IF та N. У наведеному прикладі формула в E5 має вигляд:
=SUM(INDEX(data,N(IF(1,(1,2,3)))))
де "дані" - названий діапазон B5: B10.
Пояснення
Напрочуд складно отримати INDEX, щоб повернути більше одного значення іншій функції. Для ілюстрації, наступна формула може бути використана для повернення перших трьох елементів у названому діапазоні "дані", коли вони вводяться як формула масиву з багато комірок.
(=INDEX(data,(1,2,3)))
Результати можна побачити в діапазоні D10: F10, який правильно містить 10, 15 і 20.
Однак, якщо ми обернемо формулу у функцію SUM:
=SUM(INDEX(data,(1,2,3)))
Кінцевий результат - 10, тоді як він повинен бути 45, навіть якщо він введений як формула масиву. Проблема полягає в тому, що INDEX повертає лише перший елемент масиву до функції SUM. Щоб змусити INDEX повертати кілька елементів у SUM, можна обернути константу масиву у функції N та IF, як це:
=SUM(INDEX(data,N(IF(1,(1,2,3)))))
який повертає правильний результат 45. Аналогічно, ця формула:
=SUM(INDEX(data,N(IF(1,(1,3,5)))))
правильно повертає 60, суму 10, 20 і 30.
Цей незрозумілий прийом іноді називають "розмежуванням посилань", оскільки він зупиняє INDEX обробляти результати як посилання на клітинки і згодом скидати всі елементи, крім першого, у масиві. Натомість INDEX забезпечує повний масив значень для SUM in. Джефф Вейр має гарне пояснення тут щодо stackoverflow.
Примітка для читачів: Я не впевнений, чому саме це працює. Якщо ви можете дати чітке пояснення, я оновлю, щоб включити.
