Formuła ogólna
=SUM(INDEX(range,N(IF(1,(1,2,3)))))
Podsumowanie
Aby funkcja INDEKS zwróciła tablicę elementów do innej funkcji, możesz użyć niejasnej sztuczki opartej na funkcjach JEŻELI i N. W pokazanym przykładzie formuła w E5 to:
=SUM(INDEX(data,N(IF(1,(1,2,3)))))
gdzie „dane” to nazwany zakres B5: B10.
Wyjaśnienie
Zaskakująco trudno jest sprawić, by INDEKS zwracał więcej niż jedną wartość innej funkcji. Aby zilustrować, poniższą formułę można użyć do zwrócenia pierwszych trzech elementów z nazwanego zakresu „dane”, jeśli zostanie wprowadzony jako formuła tablicowa z wieloma komórkami.
(=INDEX(data,(1,2,3)))
Wyniki można zobaczyć w zakresie D10: F10, który poprawnie zawiera 10, 15 i 20.
Jeśli jednak zawiniemy formułę w funkcji SUMA:
=SUM(INDEX(data,(1,2,3)))
Ostateczny wynik to 10, a powinien wynosić 45, nawet jeśli zostanie wprowadzony jako formuła tablicowa. Problem polega na tym, że INDEKS zwraca do funkcji SUMA tylko pierwszy element tablicy. Aby zmusić INDEKS do zwracania wielu elementów do SUMA, możesz zawinąć stałą tablicową w funkcje N i JEŻELI w następujący sposób:
=SUM(INDEX(data,N(IF(1,(1,2,3)))))
co zwraca poprawny wynik 45. Podobnie, ta formuła:
=SUM(INDEX(data,N(IF(1,(1,3,5)))))
poprawnie zwraca 60, czyli sumę 10, 20 i 30.
Ta mało znana technika jest czasami nazywana „wyłuskiwaniem”, ponieważ powoduje, że indeks INDEX nie obsługuje wyników jako odwołań do komórek, a następnie usuwa wszystkie elementy tablicy oprócz pierwszego. Zamiast tego, INDEKS dostarcza pełną tablicę wartości do sumowania. Jeff Weir ma tutaj dobre wyjaśnienie na temat przepływu stosu.
Uwaga dla czytelników: nie jestem pewien, dlaczego to działa. Jeśli możesz podać jasne wyjaśnienie, zaktualizuję, aby uwzględnić.
