Definieren Sie den Bereich basierend auf dem Zellenwert

Um einen Bereich basierend auf einem Wert in einer anderen Zelle zu definieren, können Sie die INDEX-Funktion . Im gezeigten Beispiel lautet die Formel in J7:

= SUM (firstcell: INDEX (data,rows,cols))

wo 'Daten' sind benannter Bereich B5: G9.

Diese Formel beruht auf einem bestimmten Verhalten von INDEX - obwohl es scheint, dass INDEX die Wert an einem bestimmten Ort gibt es tatsächlich die Hinweis zum Standort. 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 dynamischen Bereich basierend auf der Arbeitsblatteingabe zu erstellen.

Excel-Konvertierungszeit in Dezimalstunden

Innerhalb der Summenfunktion ist die erste Referenz einfach die erste Zelle im Bereich, der alle möglichen Zellen abdeckt:

 
= SUM (C5: INDEX (data,J5,J6))

Um die letzte Zelle zu erhalten, verwenden wir INDEX. Hier geben wir INDEX die benannter Bereich 'data', das ist der maximal mögliche Wertebereich, sowie die Werte von J5 (Zeilen) und J6 (Spalten). INDEX gibt keinen Bereich zurück, sondern nur eine einzelne Zelle an dieser Stelle, E9 im Beispiel:

zählen, wenn die Zelle bestimmten Text enthält

 
= SUM (C5:

Die ursprüngliche Formel wird reduziert auf:

 
 INDEX (data,J5,J6) // returns E9

was 300 zurückgibt, die Summe aller Werte in C5:E9.

wie man eine Spalte in Excel immer sichtbar macht

Die Formel in J8 ist fast die gleiche, verwendet aber DURCHSCHNITT Anstatt von SUMME 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 eine ähnliche Formel mit dem erstellen OFFSET-Funktion , unten gezeigt:

 
= SUM (C5:E9)

OFFSET wurde entwickelt, um einen Bereich zurückzugeben, daher sind die Formeln vielleicht einfacher zu verstehen. OFFSET ist jedoch a flüchtige Funktion , und kann zu Leistungsproblemen führen, wenn sie in größeren, komplexeren Arbeitsblättern verwendet wird.