Dynamischen Bereich in Excel erstellen
Dynamischer Bereich mit Formeln
Der Einfachheit halber gehen wir in diesem Beispiel von einem Kundenstamm aus. Wir können davon ausgehen, dass es hier regelmäßig zu Änderungen kommt. Wenn das Geschäft gut läuft, kommen regelmäßig neue Kunden hinzu.
Über eine Dropdown-Liste können Sie den jeweiligen Schuldner auswählen, danach werden alle weiteren Informationen eingetragen.
Dropdown-Liste erstellen
Wählen Sie zum Erstellen einer Dropdown-Liste die Zelle aus, in der die Liste angezeigt werden soll (z. B. B17). Wechseln Sie im Menüband zur Registerkarte Fakten die Gruppe Datenwerkzeuge und Klicks Datenvalidierung. Ein neues Fenster wird geöffnet.
Unter der Registerkarte Einstellungen wähle dich Erlauben: für „Liste“ und bei Quelle: Geben Sie den Bereich ein (siehe Bild unten).
Jedes Mal, wenn Sie einen Kunden zum Kundenstamm hinzufügen, muss der Umfang der Dropdown-Liste angepasst werden. Dies ist nicht wünschenswert. Es wäre schön, wenn dies ein dynamischer Bereich wäre. Nachfolgend finden Sie eine Erklärung, wie wir dies tun werden.
Reichweite dynamisch gestalten
In der Formel verwende ich die Funktionen: OFFSET und COUNTA. Die OFFSET-Funktion arbeitet wie folgt: OFFSET(ref, Zeilen, Spalten, [Höhe], [Breite]).
Das Argument Ref kann als Leuchtfeuer gesehen werden. Hier geben Sie an, von welcher Zelle aus Sie die Verschiebung durchführen möchten. Im Beispiel ist dies die Zelle A3. Von dieser Zelle aus wollen wir mit der Verschiebung beginnen
Reihen gibt an, von wie vielen Zeilen aus Sie beginnen Ref nach oben oder unten bewegen möchten. Im Beispiel beginnen wir in Zelle A3. Diese Zelle enthält die Beschreibung der entsprechenden Spalte. Wir möchten nicht, dass dies in der Dropdown-Liste erscheint. Wir gehen also eine Zeile nach unten, tragen Sie hier also eine 1 ein. Wenn Sie eine negative Zahl eingeben, schalten Sie nach oben.
Säulen gibt an, von wie vielen Spalten Sie ausgehen Ref nach links oder rechts bewegen möchten. Da wir in derselben Spalte bleiben, tragen wir hier eine 0 ein.
Mit den letzten beiden Argumenten legen wir die Höhe und Breite des Dynamikbereichs fest. Wir verwenden die Funktion, um die Höhe des Bereichs zu bestimmen ZÄHLEN. Diese Funktion zählt die Anzahl der nicht leeren Zellen.
Die Funktion ZÄHLEN setzt sich wie folgt zusammen: COUNTA(Wert1, [Wert2,...]). Vor dem wert1 Wir füllen Spalte A:A aus, wonach alle nicht leeren Zellen in dieser Spalte gezählt werden. Davon ziehen wir dann 2 ab, weil die Werte in den Zellen A1 und A3 nicht zählen.
Die endgültige Formel lautet dann wie folgt: =OFFSET(Kundendatei!$A$3;1;0;COUNT(Kundendatei!$A:$A)-2;1).
Namen verwalten
Wir können die obige Formel an der Quelle der Dropdown-Liste eingeben, aber wir können die Formel auch unter einem leichter zu merkenden Namen speichern. Das machst du mit der Option Namen definieren.
Wechseln Sie im Menüband zur Registerkarte Formeln die Gruppe Definierte Namen und klicken Namen definieren.
Es öffnet sich ein neues Fenster namens Neuer Name.
Füllen Sie das Eingabefeld aus Name: ein leicht zu merkender Name für Ihre Formel.
Am Feld Bezieht sich auf: geben Sie die Formel ein =OFFSET(Kundendatei!$A$3;1;0;COUNT(Kundendatei!$A:$A)-2;1) in.
Dann klicken Sie auf OK und der definierte Name wird gespeichert.
Voreinstellungsliste dynamisch machen
Stellen Sie nun die Referenz des ein Dropdown-Liste indem Sie den Verweis auf ändern =selKundenname. klicke auf OK , um die dynamische Dropdown-Liste zu speichern.
Haben Sie den Namen des benannten Bereichs vergessen? Wählen Sie das Eingabefeld aus Quelle: und drücken Sie die Funktionstaste F3. Es öffnet sich ein neues Fenster mit allen benannten Bereichen. Dies verhindert auch die Möglichkeit von Tippfehlern.
Wenn Sie nun einen neuen Kunden zur Kundendatei hinzufügen, wird dieser automatisch der Drop-down-Liste hinzugefügt.
