Antallet af tilgængelige emner til filtrering er begrænset. Excel kan ikke filtrere kolonner, hvor antallet af varer overstiger 999 (ikke antallet af rækker).
Brug avanceret filter til at filtrere, når der er mere end 999 varer.
For at oprette et avanceret filter bruger vi funktionerne “OFFSET” og “COUNTA” i Microsoft Excel.
COUNTA: Det returnerer antallet af celler, der indeholder værdier.
Syntaks for funktionen “COUNTA”: = COUNTA (værdi1, værdi2, værdi3….)
Eksempel: I område A1: A5, cellerne A2, A3 og A5 indeholder værdierne, og cellerne A1 og A4 er tomme. Vælg celle A6, og skriv formlen-
= COUNTA (A1: A5) funktionen vender tilbage 3
OFFSET: Det returnerer en reference til et område, der er forskudt et antal rækker og kolonner fra et andet område eller en celle.
Syntaks for OFFSET funktion: = OFFSET (reference, rækker, kolber, højde, bredde)
Reference:- Dette er den celle eller det område, du vil kompensere fra.
Rækker og kolonner, der skal flyttes: - Antallet af rækker, du vil flytte fra startpunktet, og begge disse kan være positive, negative eller nul.
Højde og bredde: - Dette er størrelsen på det område, du vil returnere. Dette er et valgfrit felt.
Lad os tage et eksempel for at forstå offset -funktionen i Excel.
Vi har data i området A1: D10. Kolonne A indeholder produktkode, kolonne B indeholder mængde, kolonne C indeholder pr. Produktomkostning og kolonne D indeholder samlede omkostninger. Vi skal returnere værdien af celle C5 i celle E2.
For at få det ønskede resultat skal vi følge nedenstående trin.
- Vælg celle E2, og skriv formlen.
- = OFFSET (A1,4,2,1,1)og tryk på Enter på tastaturet.
- Funktionen returnerer værdien af celle C5.
I dette eksempel skal vi hente værdien fra cellen C5 til E2. Vores referencecelle er den første celle i området, som er A1 og C5 er 4 rækker nedenfor og 2 kolonner til højre fra A1. Derfor er formlen = OFFSET (A1,4,2,1,1) eller = OFFSET (A1,4,2) (da 1,1 er valgfri).
Lad os nu tage et eksempel for at hente den sidste værdi i en dynamisk liste.
Vi har lande navne i en række. Hvis vi nu tilføjer flere lande til denne liste, bør den automatisk være tilgængelig på rullelisten.
Følg nedenstående trin for at forberede avanceret filter:-
- Vælg cellen B2.
- Gå til fanen Data, vælg Datavalidering fra gruppen Dataværktøjer.
- Dialogboksen "Datavalidering" vises. I fanen "Indstillinger" skal du vælge "Brugerdefineret" fra rullelisten Tillad.
- Formelboksen aktiveres.
- Skriv formlen i denne boks.
- = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1).
- Klik på OK.
- På dette stadium er den sidste opdaterede celle A11.
- For at kontrollere, om datavalideringen fungerer korrekt, skal du tilføje et bynavn i celle A12.
Så snart du tilføjer en post i A12, tilføjes den til rullelisten.
Dette er den måde, vi kan tilføje flere poster end 999 elementer i Microsoft Excel.