Eine Einführung in die Dynamikbereiche
Die SVERWEIS-Funktion wird häufig verwendet, um Informationen zu finden, die in Tabellen in Excel gespeichert sind. Wenn wir zum Beispiel eine Liste mit Namen und Alter von Personen haben:
Und dann können wir in einer nahegelegenen Zelle die Funktion SVERWEIS verwenden, um Pauls Alter zu bestimmen:
Bisher ist dies ein ziemlicher Standard. Aber was passiert, wenn wir der Liste noch weitere Namen hinzufügen müssen? Der naheliegende Gedanke wäre, den Bereich im SVERWEIS zu ändern. In einem wirklich komplexen Modell kann es jedoch mehrere Verweise auf SVERWEIS geben. Das bedeutet, dass wir jede Referenz ändern müssten - vorausgesetzt, wir wüssten, wo sie sich befinden.
Excel bietet jedoch einen alternativen Weg - einen sogenannten DYNAMIC-Bereich. Dies ist ein Bereich, der ein Update automatisch erweitert. Dies ist perfekt, wenn Ihre Listen ständig erweitert werden (z. B. Monat für Monat Verkaufsdaten).
Um einen dynamischen Bereich einzurichten, benötigen wir einen Bereichsnamen - also nennen wir unseren AGE_DATA. Der Ansatz zum Einrichten von dynamischen Bereichen unterscheidet sich zwischen Excel 2007 und früheren Versionen von Excel:
Klicken Sie in Excel 2007 unter Formeln auf „Namen definieren“:
In früheren Excel-Versionen klicken Sie auf „Einfügen“ und dann auf Namen“ .
Geben Sie in das Popup-Feld den Namen unseres Dynamikbereichs ein - das ist "AGE DATA":
Im Feld „Refers To“ müssen wir den Umfang unserer Daten eintragen. Dies wird durch eine OFFSET-Funktion erreicht. Dies hat 5 Argumente:
=OFFSET(Referenz, Zeilen, Spalten, Höhe, Breite)
- Die Referenz ist die Adresse der OBEREN LINKEN Ecke unseres Sortiments - in diesem Fall Zelle B5
- The Rows ist die Anzahl der Zeilen von OBEN LINKS, die dieser Bereich sein soll - in diesem Fall 0
- Cols ist die Anzahl der Zeilen von OBEN LINKS, die dieser Bereich sein soll - in diesem Fall 0
- Die Höhe des Bereichs - siehe unten dafür
- Die Breite des Bereichs - das sind 2 wir haben ZWEI Spalten in unserem Bereich (der Name der Person und ihr Alter)
Jetzt muss die Höhe des Bereichs je nach Anzahl der Einträge in unserer Tabelle (derzeit 7) variieren.
Natürlich möchten wir die Zeilen in unserer Tabelle hochzählen, die automatisch aktualisiert wird. Eine Möglichkeit hierfür ist die Verwendung der COUNTA-Funktion. Dies zählt nur die Anzahl der nicht leeren Zellen in einem Bereich. Da unsere Namen in Spalte B stehen, ist die Anzahl der Einträge in unseren Daten COUNTA(B:B).
Beachten Sie, dass Sie, wenn Sie dies in eine Zelle einfügen, den Wert 8 erhalten - da es die Kopfzeilennamen enthält. Das ist jedoch unerheblich.
In das Feld "Bezieht sich auf" geben wir also Folgendes ein:
=OFFSET($B$5,0,0,counta(B:B),2)
Und klicken Sie auf die Schaltfläche OK. Unser dynamischer Bereich ist jetzt erstellt.
Kehren Sie nun zu den SVERWEIS-Formeln zurück und ersetzen Sie den Bereich $B:4:$C11 durch den Namen unseres neuen dynamischen Bereichs AGE_DATA, sodass wir:
Bisher hat sich nichts geändert. Wenn wir unserer Tabelle jedoch noch ein paar weitere Namen hinzufügen:
Und in der Zelle, in der wir Paul hatten, ersetzen Sie ihn durch einen neuen Namen wie Pedro (der nicht auf der ursprünglichen Liste stand):
Und wir sehen, dass Excel automatisch Pedros Alter zurückgegeben hat - obwohl wir die SVERWEIS-Formeln nicht geändert haben. Stattdessen wurde der Umfang des Dynamikbereichs um die zusätzlichen Namen erweitert.
Dynamische Bereiche sind sehr nützlich, wenn wir wachsende Datenmengen haben - insbesondere wenn SVERWEIS- und PIVOT-Tabellen erforderlich sind.
