Generische Formel
=SUM(INDEX(range,N(IF(1,(1,2,3)))))
Zusammenfassung
Um INDEX dazu zu bringen, ein Array von Elementen an eine andere Funktion zurückzugeben, können Sie einen obskuren Trick verwenden, der auf den Funktionen IF und N basiert. In dem gezeigten Beispiel lautet die Formel in E5:
=SUM(INDEX(data,N(IF(1,(1,2,3)))))
wobei "Daten" der benannte Bereich B5: B10 ist.
Erläuterung
Es ist überraschend schwierig, INDEX dazu zu bringen, mehr als einen Wert an eine andere Funktion zurückzugeben. Zur Veranschaulichung kann die folgende Formel verwendet werden, um die ersten drei Elemente im benannten Bereich "Daten" zurückzugeben, wenn sie als mehrzellige Arrayformel eingegeben werden.
(=INDEX(data,(1,2,3)))
Die Ergebnisse sind im Bereich D10: F10 zu sehen, der 10, 15 und 20 korrekt enthält.
Wenn wir jedoch die Formel in die SUMME-Funktion einschließen:
=SUM(INDEX(data,(1,2,3)))
Das Endergebnis ist 10, während es 45 sein sollte, selbst wenn es als Array-Formel eingegeben wird. Das Problem ist, dass INDEX nur das erste Element im Array an die SUMME-Funktion zurückgibt. Um INDEX zu zwingen, mehrere Elemente an SUM zurückzugeben, können Sie die Array-Konstante wie folgt in die N- und IF-Funktionen einschließen:
=SUM(INDEX(data,N(IF(1,(1,2,3)))))
Dies ergibt ein korrektes Ergebnis von 45. In ähnlicher Weise lautet diese Formel:
=SUM(INDEX(data,N(IF(1,(1,3,5)))))
gibt korrekt 60 zurück, die Summe von 10, 20 und 30.
Diese obskure Technik wird manchmal als "Dereferenzierung" bezeichnet, da INDEX daran gehindert wird, Ergebnisse als Zellreferenzen zu verarbeiten und anschließend alle Elemente außer dem ersten im Array zu löschen. Stattdessen liefert INDEX eine vollständige Reihe von Werten an SUM in. Jeff Weir hat hier eine gute Erklärung zum Stapelüberlauf.
Hinweis für die Leser: Ich bin mir nicht sicher, warum dies genau funktioniert. Wenn Sie eine klare Erklärung geben können, werde ich aktualisieren, um aufzunehmen.
