Общая формула
=SUM(INDEX(range,N(IF(1,(1,2,3)))))
Резюме
Чтобы заставить ИНДЕКС возвращать массив элементов другой функции, вы можете использовать неясный трюк, основанный на функциях ЕСЛИ и N. В показанном примере формула E5 выглядит так:
=SUM(INDEX(data,N(IF(1,(1,2,3)))))
где «данные» - именованный диапазон B5: B10.
Объяснение
На удивление сложно заставить ИНДЕКС возвращать более одного значения другой функции. Чтобы проиллюстрировать, следующую формулу можно использовать для возврата первых трех элементов в именованном диапазоне «данные» при вводе в виде формулы массива с несколькими ячейками.
(=INDEX(data,(1,2,3)))
Результаты можно увидеть в диапазоне D10: F10, который правильно содержит 10, 15 и 20.
Однако, если мы заключим формулу в функцию СУММ:
=SUM(INDEX(data,(1,2,3)))
Конечный результат - 10, хотя он должен быть 45, даже если вводится как формула массива. Проблема в том, что ИНДЕКС возвращает только первый элемент массива в функцию СУММ. Чтобы заставить ИНДЕКС возвращать несколько элементов в СУММ, вы можете заключить константу массива в функции N и ЕСЛИ следующим образом:
=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 полный массив значений. Джефф Вейр дает хорошее объяснение по stackoverflow.
Примечание для читателей: я не совсем уверен, почему это работает. Если вы можете дать четкое объяснение, я обновлю, чтобы включить.
