Generische Formel
=SUM(firstcell:INDEX(data,rows,cols))
Zusammenfassung
Um einen Bereich basierend auf einem Wert in einer anderen Zelle zu definieren, können Sie die INDEX-Funktion verwenden. Im gezeigten Beispiel lautet die Formel in J7:
=SUM(C5:INDEX(data,J5,J6))
wobei "Daten" der benannte Bereich B5: G9 ist.
Erläuterung
Diese Formel basiert auf einem bestimmten Verhalten von INDEX. Obwohl INDEX anscheinend den Wert an einem bestimmten Ort zurückgibt, gibt es tatsächlich den Verweis auf den Ort zurück. In den meisten Formeln würden Sie den Unterschied nicht bemerken - Excel wertet einfach die Referenz aus und gibt den Wert zurück. Diese Formel verwendet diese Funktion, um einen Dynamikbereich basierend auf der Eingabe von Arbeitsblättern zu erstellen.
Innerhalb der Summenfunktion ist die erste Referenz einfach die erste Zelle in dem Bereich, der alle möglichen Zellen abdeckt:
=SUM(C5:
Um die letzte Zelle zu erhalten, verwenden wir INDEX. Hier geben wir INDEX den benannten Bereich "Daten", der den maximal möglichen Wertebereich darstellt, sowie die Werte aus J5 (Zeilen) und J6 (Spalten). INDEX gibt keinen Bereich zurück, sondern nur eine einzelne Zelle an dieser Stelle, E9 im Beispiel:
INDEX(data,J5,J6) // returns E9
Die ursprüngliche Formel reduziert sich auf:
=SUM(C5:E9)
Dies gibt 300 zurück, die Summe aller Werte in C5: E9.
Die Formel in J8 ist fast dieselbe, verwendet jedoch AVERAGE anstelle von SUM, um einen Durchschnitt zu berechnen. Wenn ein Benutzer Werte in J5 oder J6 ändert, wird der Bereich aktualisiert und neue Ergebnisse werden zurückgegeben.
Alternative mit OFFSET
Sie können ähnliche Formeln mit der unten gezeigten OFFSET-Funktion erstellen:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
OFFSET ist so konzipiert, dass es einen Bereich zurückgibt, sodass die Formeln möglicherweise einfacher zu verstehen sind. OFFSET ist jedoch eine flüchtige Funktion und kann bei Verwendung in größeren, komplexeren Arbeitsblättern zu Leistungsproblemen führen.
