Um den letzten Wert in einer dynamischen Liste abzurufen, verwenden wir die Option Datenvalidierung zusammen mit den Funktionen OFFSET und COUNTA in Microsoft Excel 2010/2013.
ANZAHL: Es gibt die Anzahl der Zellen zurück, die Werte enthalten.
Syntax der Funktion „COUNTA“: =COUNTA (Wert1, Wert2, Wert3… .)
Beispiel: Im Bereich A1:A5 enthalten die Zellen A2, A3 und A5 die Werte, und die Zellen A1 und A4 sind leer. Wählen Sie die Zelle A6 aus und schreiben Sie die Formel-
=ANZAHLA(A1:A5) die Funktion wird zurückkehren 3
VERSETZUNG: Es gibt einen Verweis auf einen Bereich zurück, der um eine Reihe von Zeilen und Spalten von einem anderen Bereich oder einer anderen Zelle versetzt ist.
Syntax der OFFSET-Funktion: =OFFSET (Referenz, Zeilen, Spalten, Höhe, Breite)
Referenz:- Dies ist die Zelle oder der Bereich, von der Sie einen Offset durchführen möchten.
Zeilen und Spalten zum Verschieben: - Wie viele Zeilen Sie den Startpunkt verschieben möchten, und beide können positiv, negativ oder null sein.
Höhe und Breite: - Dies ist die Größe des Bereichs, den Sie zurückgeben möchten. Dies ist ein optionales Feld.
Nehmen wir ein Beispiel, um die Offset-Funktion in Excel zu verstehen.
Wir haben Daten im Bereich A1:D10. Spalte A enthält den Produktcode, Spalte B enthält die Menge, Spalte C enthält die Kosten pro Produkt und Spalte D enthält die Gesamtkosten. Wir müssen den Wert von Zelle C5 in Zelle E2 zurückgeben.
Befolgen Sie die unten aufgeführten Schritte.
- Wählen Sie die Zelle E2 aus und schreiben Sie die Formel.
- =OFFSET(A1,4,2,1,1)und drücken Sie die Eingabetaste auf der Tastatur.
- Die Funktion gibt den Wert der Zelle C5 zurück.
In diesem Beispiel müssen wir den Wert aus Zelle C5 in E2 abrufen. Unsere Referenzzelle ist die erste Zelle im Bereich A1 und C5 ist 4 Zeilen darunter und 2 Spalten rechts von A1. Daher lautet die Formel =OFFSET(A1,4,2,1,1) oder =OFFSET(A1,4,2) (da 1,1 optional ist).
Nehmen wir nun ein Beispiel, um den letzten Wert in einer dynamischen Liste abzurufen.
Wir haben Ländernamen in einer Reihe. Wenn wir dieser Liste nun weitere Länder hinzufügen, sollte sie automatisch in der Dropdown-Liste verfügbar sein.
Um eine dynamische Liste vorzubereiten, müssen wir eine Formel erstellen, die den letzten Wert in der Spalte abruft und automatisch aktualisiert, wenn eine neue Zahl hinzugefügt wird.
Folgen Sie den unten angegebenen Schritten:-
- Wählen Sie die Zelle B2 aus.
- Wechseln Sie zur Registerkarte Daten, und wählen Sie Datenvalidierung aus der Gruppe Datentools aus.
- Das Dialogfeld „Datenvalidierung“ wird angezeigt. Wählen Sie auf der Registerkarte "Einstellungen" aus der Dropdown-Liste "Zulassen" die Option "Benutzerdefiniert".
- Die Formelbox wird aktiviert.
- Schreiben Sie die Formel in dieses Feld.
- =OFFSET(A:A,1,0,ANZAHLA(A:A)-1,1).
- Klicken Sie auf OK.
- In diesem Stadium ist die letzte aktualisierte Zelle A11.
- Um zu überprüfen, ob die Datenvalidierung ordnungsgemäß funktioniert, fügen Sie in Zelle A12 einen Städtenamen hinzu.
Sobald Sie in A12 einen Eintrag hinzufügen, wird dieser der Dropdown-Liste hinzugefügt.
Auf diese Weise können Sie in Microsoft Excel 2010 und 2013 eine dynamische Liste erstellen und diese automatisch mit neuen Einträgen füllen.