Die Anzahl der zum Filtern verfügbaren Elemente ist begrenzt. Excel kann keine Spalten filtern, in denen die Anzahl der Elemente 999 überschreitet (nicht die Anzahl der Zeilen).
Um zu filtern, wenn mehr als 999 Elemente vorhanden sind, verwenden Sie den erweiterten Filter.
Um einen erweiterten Filter zu erstellen, verwenden wir die Funktionen „OFFSET“ und „COUNTA“ in Microsoft Excel.
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: - Die Anzahl der Zeilen, die Sie vom Startpunkt aus 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.
Um das gewünschte Ergebnis zu erzielen, müssen wir die unten aufgeführten Schritte befolgen.
- 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 der Zelle C5 bis 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 jetzt weitere Länder hinzufügen, sollte sie automatisch in der Dropdown-Liste verfügbar sein.
Um den erweiterten Filter vorzubereiten, befolgen Sie die unten angegebenen Schritte:
- 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 wir mehr Einträge als 999 Elemente in Microsoft Excel hinzufügen.