FILTRER og ENTYDIGE: lister der opdaterer sig selv

Hver mandag åbner du ordrelisten, sætter et filter på status Åben, markerer rækkerne, kopierer dem og sætter dem ind i det ark, du sender videre. Bagefter laver du en liste over sælgerne, og for at få hver kun én gang bruger du Fjern dubletter.

Onsdag kommer der tre nye ordrer. Nu passer din kopi ikke længere, og du gør det hele igen.

Siden Excel 2021 findes der to formler, der gør netop det, bare levende: FILTRER trækker de rækker ud, der opfylder dine betingelser, og ENTYDIGE laver en liste uden gengangere. Du skriver formlen i én celle, og resultatet løber selv ned i cellerne nedenunder. Her får du de fem måder, du oftest bruger dem på, og de fejl, du typisk møder undervejs. Regn med tyve minutter.

Bruger du engelsk Excel, hedder funktionerne FILTER og UNIQUE. Jeg bruger de danske navne.

Først: hvad "løber ned" betyder

De nye funktioner returnerer ikke ét tal. De returnerer en hel liste, og Excel lægger den ud i så mange celler, som der er brug for. Microsoft kalder det et overløbsområde.

Det betyder i praksis: Du skriver kun i den øverste celle. Klikker du på en af cellerne nedenunder, kan du se formlen i grå skrift, men du kan ikke redigere den der. Og bliver listen længere, fordi der kommer nye ordrer, vokser området af sig selv.

Versionen tæller: Funktionerne findes i Excel til Microsoft 365, Excel 2021 og Excel 2024. Sidder du i Excel 2019 eller ældre, får du #NAVN? (engelsk Excel: #NAME?), og så er det ikke formlen, der er noget galt med.

1. En liste over sælgere, hver kun én gang

=ENTYDIGE(Ordrer[Sælger])

Hvad den gør: Går kolonnen igennem og returnerer hver sælger én gang, i den rækkefølge de optræder første gang.

Vil du have listen sorteret, pakker du den ind i SORTER:

=SORTER(ENTYDIGE(Ordrer[Sælger]))

Hvorfor det er bedre end Fjern dubletter: Fjern dubletter ændrer dine data og er et engangsklik. ENTYDIGE lader dataene være og opdaterer sig, når der kommer en ny sælger til. Det er forskellen på en liste og en kopi af en liste.

2. Alle rækker der opfylder én betingelse

=FILTRER(Ordrer;Ordrer[Status]="Åben")

Hvad den gør: Det første argument er det, der skal hentes ud, her hele tabellen. Det andet er betingelsen. Du får alle kolonner for de rækker, hvor status er Åben, og intet andet.

En detalje der gør livet lettere: Lav dine data om til en tabel med Ctrl+T, før du skriver formlen. Så kan du skrive Ordrer[Status] i stedet for B2:B500, og når der kommer nye rækker i tabellen, er de automatisk med.

3. To betingelser på én gang: gange betyder OG

=FILTRER(Ordrer;(Ordrer[Status]="Åben")*(Ordrer[Region]="Nord"))

Hvorfor gangetegn: Hver betingelse giver SAND eller FALSK for hver række, som Excel regner som 1 og 0. Ganger du dem, bliver resultatet kun 1, når begge er sande. Det er en OG.

Parenteserne om hver betingelse er ikke pynt. Uden dem regner Excel i en anden rækkefølge, og du får enten en fejl eller et forkert resultat.

4. Enten den ene eller den anden: plus betyder ELLER

=FILTRER(Ordrer;(Ordrer[Region]="Nord")+(Ordrer[Region]="Syd"))

Hvorfor plustegn: Lægger du 1 og 0 sammen, bliver resultatet større end nul, så snart én af dem er sand. Det er en ELLER.

Det er den samme logik, du kender fra et almindeligt filter med to afkrydsninger, bare skrevet ind i en formel, der kan stå i et rapportark og opdatere sig selv.

5. Når der ikke er noget at vise

Spørger du om noget, der ikke findes, for eksempel ordrer i Vest over 38.000 når den største er præcis 38.000, får du #BEREGN! (engelsk Excel: #CALC!). Excel kan ikke vise en tom liste, så det bliver til en fejl.

Sådan retter du: Brug det tredje argument, som bestemmer hvad der skal stå i stedet:

=FILTRER(Ordrer;(Ordrer[Region]="Vest")*(Ordrer[Beløb]>38000);"Ingen ordrer")

Nu står der en tekst, der giver mening for den, der læser arket, i stedet for en fejl, der ligner et problem.

Den fejl alle møder: #OVERLØB!

Microsofts danske oversættelse er ikke helt konsekvent her, så du kan møde både #OVERLØB! og #SPILL!. Det er den samme fejl.

Hvad den betyder: Listen vil gerne løbe ned, men der står noget i vejen.

Typisk årsag: Der står en værdi, et mellemrum eller en gammel formel i en af cellerne nedenunder. Det kan også være flettede celler, eller at du har skrevet formlen inde i en Excel-tabel, hvor overløb ikke er tilladt.

Sådan retter du: Klik på fejlen, og vælg Markér blokerende celler. Excel viser dig præcis den celle, der blokerer. Slet indholdet, og listen løber ned med det samme. Er det en tabel, så flyt formlen ud ved siden af tabellen.

Det gode ved fejlen er, at den aldrig overskriver noget. Excel stopper hellere end at slette dine data.

Tre greb der gør det endnu bedre

Henvis til hele listen med #. Står din sorterede sælgerliste i D2, betyder D2# hele listen, uanset hvor lang den bliver. Så kan du skrive =SUM.HVISER(Ordrer[Beløb];Ordrer[Sælger];D2#) i cellen ved siden af og få omsætningen ud for hver sælger, også når der kommer en ny til.
Brug ENTYDIGE som kilde til en rulleliste. Peg datavalideringen på =$D$2#, så følger rullelisten automatisk med, når der kommer nye sælgere.
Hold kilden og resultatet på hver sit ark. Tabellen med data ét sted, formlerne et andet. Så kommer du ikke til at skrive i et overløbsområde ved et uheld.

Prøv det selv

Jeg har lavet en Excel-fil med 40 ordrer i tabellen Ordrer og syv opgaver: en liste over sælgere, den samme sorteret, åbne ordrer, åbne ordrer i Nord, Nord eller Syd, et filter der ikke finder noget, og omsætning per sælger med #.

Løsningsarket viser formlerne og det resultat, du skal ende med. På et ekstra ark kan du selv fremkalde #OVERLØB! og rette den, så du genkender den, næste gang den dukker op.

Hent øvelsesfilen (Excel, xlsx)

Til sidst

Den største gevinst ved FILTRER og ENTYDIGE er ikke formlen. Det er, at du holder op med at lave kopier, der bliver forældede i det øjeblik, du har sendt dem.

Hos KontorAkademiet tager jeg en time om måneden med den slags praktiske greb. Ikke i et bestemt program, men i det du sidder med til daglig, så du og dine kollegaer får timerne tilbage lidt efter lidt.

Table of contents