Het aantal items dat kan worden gefilterd, is beperkt. Excel kan geen kolommen filteren waarin het aantal items groter is dan 999 (niet het aantal rijen).
Gebruik geavanceerd filter om te filteren wanneer er meer dan 999 items zijn.
Om een geavanceerd filter te maken, gebruiken we de functies "OFFSET" en "COUNTA" in Microsoft Excel.
AANTAL: Het retourneert de telling van het aantal cellen dat waarden bevat.
Syntaxis van de functie "COUNTA": =COUNTA (waarde1, waarde2, waarde3… .)
Voorbeeld: In het bereik A1:A5 bevatten de cellen A2, A3 en A5 de waarden en zijn de cellen A1 en A4 leeg. Selecteer de cel A6 en schrijf de formule-
=AANTALLEN(A1:A5) de functie keert terug 3
OFFSET: Het retourneert een verwijzing naar een bereik dat een aantal rijen en kolommen uit een ander bereik of cel verschoven is.
Syntaxis van OFFSET-functie: =OFFSET (referentie, rijen, kolommen, hoogte, breedte)
Verwijzing:- Dit is de cel of het bereik waarvan u wilt compenseren.
Rijen en kolommen om te verplaatsen: - Het aantal rijen dat u vanaf het startpunt wilt verplaatsen en beide kunnen positief, negatief of nul zijn.
Hoogte en breedte: - Dit is de grootte van het assortiment dat u wilt retourneren. Dit is een optioneel veld.
Laten we een voorbeeld nemen om de functie Offset in Excel te begrijpen.
We hebben gegevens in het bereik A1:D10. Kolom A bevat Productcode, kolom B bevat Hoeveelheid, kolom C bevat kosten per product en kolom D bevat Totale kosten. We moeten de waarde van cel C5 in cel E2 retourneren.
Om het gewenste resultaat te krijgen, moeten we de onderstaande stappen volgen.
- Selecteer de cel E2 en schrijf de formule.
- =VERSCHUIVING(A1,4,2,1,1)en druk op Enter op het toetsenbord.
- De functie retourneert de waarde van cel C5.
In dit voorbeeld moeten we de waarde uit de cel C5 tot E2 halen. Onze referentiecel is de eerste cel in het bereik dat A1 is en C5 is 4 rijen lager en 2 kolommen rechts van A1. Daarom is de formule =OFFSET(A1,4,2,1,1) of =OFFSET(A1,4,2) (aangezien 1,1 optioneel is).
Laten we nu een voorbeeld nemen om de laatste waarde in een dynamische lijst op te halen.
We hebben landnamen in een reeks. Als we nu meer landen aan deze lijst toevoegen, zou deze automatisch beschikbaar moeten zijn in de vervolgkeuzelijst.
Volg de onderstaande stappen om een geavanceerd filter voor te bereiden: -
- Selecteer de cel B2.
- Ga naar het tabblad Gegevens, selecteer Gegevensvalidatie in de groep Gegevenshulpmiddelen.
- Het dialoogvenster "Gegevensvalidatie" verschijnt. Selecteer op het tabblad "Instellingen" "Aangepast" in de vervolgkeuzelijst Toestaan.
- Het formulevak wordt geactiveerd.
- Schrijf de formule in dit vak.
- =OFFSET(A:A,1,0,COUNTA(A:A)-1,1).
- Klik op OK.
- In dit stadium is de laatst bijgewerkte cel A11.
- Om te controleren of de gegevensvalidatie goed werkt, voegt u een plaatsnaam toe in cel A12.
Zodra u een invoer toevoegt in A12, wordt deze toegevoegd aan de vervolgkeuzelijst.
Dit is de manier waarop we meer items dan 999 items kunnen toevoegen in Microsoft Excel.