Antall tilgjengelige elementer for filtrering er begrenset. Excel kan ikke filtrere kolonner der antall elementer overstiger 999 (ikke antall rader).
For å filtrere når det er mer enn 999 elementer, bruk avansert filter.
For å lage et avansert filter, bruker vi funksjonene “OFFSET” og “COUNTA” i Microsoft Excel.
COUNTA: Den returnerer tellingen av antall celler som inneholder verdier.
Syntaks for “COUNTA” -funksjon: = COUNTA (verdi1, verdi2, verdi3….)
Eksempel: I område A1: A5, cellene A2, A3 og A5 inneholder verdiene, og cellene A1 og A4 er tomme. Velg cellen A6 og skriv formelen-
= COUNTA (A1: A5) funksjonen kommer tilbake 3
OFFSET: Den returnerer en referanse til et område som er forskjøvet et antall rader og kolonner fra et annet område eller en celle.
Syntaks for OFFSET -funksjon: = OFFSET (referanse, rader, cols, høyde, bredde)
Henvisning:- Dette er cellen eller området du vil kompensere fra.
Rader og kolonner som skal flyttes: - Antall rader du vil flytte fra startpunktet, og begge disse kan være positive, negative eller null.
Høyde og bredde: - Dette er størrelsen på området du vil returnere. Dette er et valgfritt felt.
La oss ta et eksempel for å forstå Offset -funksjonen i Excel.
Vi har data i området A1: D10. Kolonne A inneholder produktkode, kolonne B inneholder mengde, kolonne C inneholder per produktkostnad og kolonne D inneholder total kostnad. Vi må returnere verdien av celle C5 i celle E2.
For å få ønsket resultat må vi følge trinnene nedenfor.
- Velg celle E2 og skriv formelen.
- = OFFSET (A1,4,2,1,1)og trykk Enter på tastaturet.
- Funksjonen vil returnere verdien av celle C5.
I dette eksemplet må vi hente verdien fra cellen C5 til E2. Referansecellen vår er den første cellen i området som er A1 og C5 er 4 rader under og 2 kolonner til høyre fra A1. Derfor er formelen = OFFSET (A1,4,2,1,1) eller = OFFSET (A1,4,2) (siden 1,1 er valgfritt).
La oss nå ta et eksempel for å hente den siste verdien i en dynamisk liste.
Vi har landnavn i et område. Hvis vi legger til flere land på denne listen, bør den være tilgjengelig i rullegardinlisten automatisk.
Følg trinnene nedenfor for å forberede avansert filter:-
- Velg cellen B2.
- Gå til kategorien Data, velg Datavalidering fra gruppen Dataverktøy.
- Dialogboksen "Datavalidering" vises. I kategorien "Innstillinger" velger du "Tilpasset" fra rullegardinlisten Tillat.
- Formelboksen aktiveres.
- Skriv formelen i denne boksen.
- = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1).
- Klikk på OK.
- På dette stadiet er den siste oppdaterte cellen A11.
- For å sjekke om datavalideringen fungerer som den skal, legg til et bynavn i celle A12.
Så snart du legger til en oppføring i A12, vil den bli lagt til i rullegardinlisten.
Dette er måten vi kan legge til flere oppføringer enn 999 elementer i Microsoft Excel.