Dynamiska listor med UNIQUE och FILTER-funktionerna

Du har gjort en lista över kunder, produkter eller säljare med Ta bort dubbletter, och allt ser bra ut. Sedan kommer det in nya rader i underlaget, och listan stämmer inte längre. Den uppdateras inte av sig själv, så du får göra om samma moment varje gång data ändras. Det är problemet med statiska listor: de visar hur det såg ut när du skapade dem.

I nyare versioner av Excel finns ett bättre sätt. Med funktionerna UNIQUE och FILTER (på svenska UNIK och FILTER) bygger du en dynamisk lista med en enda formel. Formeln spiller ut resultatet i cellerna under och räknar om automatiskt när du lägger till, ändrar eller tar bort data i källan.

Här går vi igenom hur du använder UNIQUE och FILTER i Excel var för sig och tillsammans. Du får också se hur du sorterar resultatet och vilka fel du kan stöta på.

Innan du bygger dynamiska listor kan det vara smart att omvandla området till en Excel-tabell, så slipper du justera referenser manuellt.

Varför dynamiska listor är smidigare än gamla metoder

Det finns sedan länge två inbyggda sätt att plocka fram unika värden eller utvalda rader i Excel: kommandot Ta bort dubbletter och Avancerat filter. Båda fungerar bra, men de ger ett statiskt resultat. Excel gör jobbet en gång, och sedan är listan fryst.

Ta bort dubbletter och Avancerat filter

Ta bort dubbletter hittar du under fliken Data i gruppen Dataverktyg. Kommandot raderar dubblettraderna direkt i ditt dataområde. Vill du behålla originaldatan måste du alltså först kopiera den till ett annat ställe.

Avancerat filter finns under Data → Sortera och filtrera → Avancerat. Där kan du ange villkor, kryssa i Endast unika poster och låta Excel kopiera resultatet till en annan plats. Originalet blir orört, men resultatet är fortfarande bara en kopia av hur datan såg ut just då.

Gemensamt för båda är att du måste köra kommandot igen så fort källdatan ändras. Lägger du till en ny kund, rättar ett stavfel eller byter region på en rad, så märker den färdiga listan ingenting. Det är lätt att glömma, och då sitter du plötsligt med en lista som inte stämmer. Hur den äldre metoden fungerar steg för steg kan du läsa i Skapa en unik lista i Excel.

Dynamiska matrisformler spiller ut resultatet

Med UNIQUE och FILTER, på svenska UNIK och FILTER, skriver du i stället en formel i en enda cell. Formeln kan returnera många värden, och Excel spiller ut dem i cellerna nedanför och bredvid. Du behöver inte kopiera formeln eller markera ett område i förväg, och du behöver inte trycka Ctrl+Skift+Enter som för gamla matrisformler.

Eftersom det är en formel räknas resultatet om automatiskt varje gång källdatan ändras. Kommer det till en ny unik post växer listan av sig själv. Försvinner en post krymper den.

	Ta bort dubbletter / Avancerat filter	UNIQUE / FILTER
Resultat	Statiskt, en ögonblicksbild	Dynamiskt, uppdateras automatiskt
Vid ändrad källdata	Kommandot måste köras igen	Räknas om direkt
Originaldata	Ta bort dubbletter ändrar den, Avancerat filter kan kopiera	Lämnas alltid orörd
Kan kombineras med andra funktioner	Nej	Ja, t.ex. med SORT

De gamla kommandona har fortfarande sin plats, till exempel när du bara vill städa upp en lista en gång. Men behöver du en lista som alltid ska stämma, som en rapport eller källan till en listruta, är dynamiska formler det smidigare valet.

Skapa en unik lista med UNIQUE

UNIQUE heter UNIK i svenska Excel och gör precis vad namnet säger: den returnerar varje värde i ett område en gång. Syntaxen ser ut så här:

=UNIQUE(matris; [efter_kolumn]; [exakt_en_gång])

• matris är området du vill hämta unika värden från, till exempel en kolumn med namn.
• efter_kolumn (valfritt) styr hur värdena jämförs. Standard är FALSKT, och då jämförs rader, vilket passar en vanlig lista uppifrån och ned. Med SANT jämförs i stället kolumner, vilket är användbart när data ligger vågrätt.
• exakt_en_gång (valfritt) är FALSKT som standard och ger då alla olika värden. Med SANT returneras bara de värden som förekommer exakt en gång i listan. Det är praktiskt när du vill hitta säljare som bara har en enda affär.

I de flesta fall räcker det första argumentet. I exemplet nedan finns en försäljningslista där samma säljare dyker upp flera gånger i kolumn A. I cell E2 står formeln =UNIQUE(A2:A10), och den ger de fem säljarna Anna, Erik, Sara, Mikael och Lina i den ordning de först förekommer i listan.

Spill-området och #-referensen

Formeln skrivs bara i en cell, men resultatet spills ut i cellerna nedanför. Hela det blå inramade området kallas spill-området. Du kan inte skriva över enskilda celler i det, eftersom det är formeln i E2 som äger hela resultatet. Lägger du till en ny säljare i kolumn A, eller tar bort en, växer eller krymper spill-området automatiskt.

Vill du hänvisa till hela resultatet från en annan formel använder du cellen med formeln följd av ett nummertecken, till exempel E2#. Med =ANTALV(E2#) räknar du hur många unika säljare det finns, och siffran stämmer även när listan ändrar storlek. Det är mycket säkrare än att peka på ett fast område som E2:E6.

Sortera den unika listan med SORT

UNIQUE behåller ordningen från källdatan, och det är sällan den ordning man vill visa. Lägg därför SORT runt formeln. SORT heter SORTERA på svenska:

=SORT(UNIQUE(A2:A10))

I kolumn G i exemplet ger formeln samma fem namn i bokstavsordning: Anna, Erik, Lina, Mikael och Sara. Vill du ha fallande ordning lägger du till argumenten för sorteringsindex och ordning: =SORT(UNIQUE(A2:A10);1;-1). Resultatet är en sorterad lista utan dubbletter som uppdaterar sig själv, och det enda du behöver underhålla är källdatan.

Filtrera data automatiskt med FILTER

UNIQUE tar bort dubbletter. FILTER plockar i stället ut de rader som uppfyller ett villkor, ungefär som Autofilter. Skillnaden är att resultatet hamnar i ett eget område och uppdateras direkt när data eller villkor ändras. Funktionen heter FILTER även i svenska Excel.

Syntaxen ser ut så här:

=FILTER(matris; inkludera; [om_tom])

• matris: området du vill hämta data från, till exempel hela tabellen A2:C9.
• inkludera: ett villkor som ger SANT eller FALSKT för varje rad, till exempel C2:C9>G2. Området måste ha lika många rader som matrisen.
• om_tom (valfritt): det som ska visas om inga rader matchar.

Exempel: försäljning över ett visst belopp

I exemplet nedan finns en lista med säljare, region och belopp. I cell G2 står gränsen, här 20 000 kr. För att visa vad FILTER egentligen gör har jag lagt till en hjälpkolumn D med formeln =C2>$G$2. Det är precis den här testen som argumentet inkludera utför för varje rad. Raderna med SANT är de som FILTER returnerar. Du behöver inte hjälpkolumnen när du använder FILTER, den finns bara med för att visa logiken.

Själva filtreringen gör du med en enda formel i en tom cell, till exempel I2:

=FILTER(A2:C9;C2:C9>G2;"Inga träffar")

Resultatet spiller ut över I2:K5 och visar de fyra rader som har SANT i hjälpkolumnen: Erik, Johan, Anna och Sara, i samma ordning som i källdatan. Antalet träffar stämmer med =ANTAL.OM(C2:C9;">"&G2) i G4. Om du ändrar gränsen i G2 till 25 000 krymper listan direkt till två rader, Erik och Johan. Lägg gärna villkoret i en egen cell i stället för att skriva in talet i formeln. Då kan du ändra urvalet utan att röra formeln.

Flera villkor med * och +

FILTER har inga egna argument för flera villkor. I stället kombinerar du villkoren i argumentet inkludera och sätter varje villkor inom parentes.

• OCH: multiplicera villkoren med *. En rad kommer bara med om alla villkor är sanna. =FILTER(A2:C9;(C2:C9>G2)*(B2:B9="Norr")) ger rader i region Norr med belopp över gränsen. Med datan i exemplet blir det bara Johan.
• ELLER: addera villkoren med +. En rad kommer med om minst ett villkor är sant. =FILTER(A2:C9;(B2:B9="Norr")+(B2:B9="Syd")) ger alla rader från Norr eller Syd.

Det fungerar eftersom Excel räknar SANT som 1 och FALSKT som 0. Vid multiplikation blir resultatet 1 bara om alla villkor är 1. Vid addition räcker det att ett av dem är 1. Du kan också blanda de två, till exempel (C2:C9>G2)*((B2:B9="Norr")+(B2:B9="Syd")). Parenteserna styr då vilka villkor som hör ihop.

Använd om_tom för att slippa #BERÄKNA!

Om inget i listan uppfyller villkoren visar FILTER felet #BERÄKNA!, eftersom det inte finns något att returnera. Med det tredje argumentet visar du något annat i stället, till exempel en text som "Inga träffar" eller en tom sträng "" om cellen bara ska se tom ut. Ange alltid om_tom när villkoret styrs av en cell som användaren kan ändra. Annars syns felet så fort någon skriver in ett värde som inte ger några träffar.

Kombinera UNIQUE och FILTER för smarta listor

Det är när UNIQUE och FILTER används tillsammans som de blir riktigt användbara. FILTER plockar ut de rader som uppfyller ett villkor. UNIQUE rensar bort dubbletterna i resultatet. Lägger du dessutom SORT runt alltihop får du en färdig, sorterad lista i en enda formel, och den uppdateras när du ändrar villkoret eller lägger till nya rader i datat.

I exemplet nedan finns en försäljningslista med region, säljare och belopp. I cell E2 skriver du vilken region du vill titta på. I G2 står formeln:

=SORT(UNIQUE(FILTER(B2:B11;A2:A11=E2)))

I svensk Excel skriver du samma formel som =SORTERA(UNIK(FILTER(B2:B11;A2:A11=E2))).

Formeln läses inifrån och ut:

1. FILTER returnerar alla säljarnamn i B2:B11 där regionen i kolumn A är lika med värdet i E2. För Syd blir det Erik, Lena, Erik, Ahmed och Lena.
2. UNIQUE tar bort dubbletterna, så att varje säljare bara finns med en gång: Erik, Lena och Ahmed.
3. SORT sorterar namnen i bokstavsordning, så att resultatet blir Ahmed, Erik och Lena.

Resultatet spiller ut nedåt från G2. Om du byter E2 till Nord ändras listan direkt till Anna, Johan och Maria. Om regionen inte finns i datat returnerar FILTER felet #BERÄKNA!. Lägg därför gärna till det tredje argumentet, till exempel FILTER(B2:B11;A2:A11=E2;"Inga säljare"), så visas en text i stället.

Använd listan i en listruta

En sådan formel fungerar bra som källa för en dynamisk listruta. Markera cellen där listrutan ska finnas och välj Data > Dataverifiering. Välj Lista under Tillåt och skriv =$G$2# som källa. Tecknet # efter cellreferensen betyder att hela spill-området används. Listrutan växer och krymper alltså i takt med formeln. När en ny säljare dyker upp i regionen kommer namnet med i listrutan utan att du behöver ändra något.

Gör du regionen i E2 till en listruta också får du två beroende listrutor: först väljer användaren region, sedan visas bara de säljare som hör till den regionen. Vill du läsa mer om hur listor kan byggas på och bearbetas med formler finns det en separat artikel om komplettering av listor i Excel.

Vanliga fel och begränsningar

Dynamiska matrisformler är robusta, men några felmeddelanden och begränsningar dyker upp ofta. Här är de vanligaste och hur du löser dem.

#SPILL! – spill-området är blockerat

En formel med UNIQUE eller FILTER behöver tomma celler att spilla ut i. Står det redan något i området under eller bredvid formeln, även ett mellanslag eller en formel som returnerar tom text, visar Excel #SPILL! i stället för resultatet. Klicka på cellen med felet och sedan på varningsikonen. Där kan du välja Markera blockerande celler och se exakt vad som står i vägen. Töm de cellerna så spiller formeln ut direkt.

Samma fel visas om spill-området innehåller sammanfogade celler, eller om resultatet skulle gå utanför kalkylbladets kant.

#BERÄKNA! – inget att visa

Om FILTER inte hittar en enda rad som uppfyller villkoren blir resultatet en tom matris, och då returnerar Excel #BERÄKNA!. Det är alltså inget fel i själva formeln. Lösningen är att fylla i argumentet om_tom, till exempel med "Inga träffar", så att användaren får ett begripligt besked i stället för en felkod.

Spill fungerar inte i Excel-tabeller

Dynamiska matrisformler kan inte spilla inuti en Excel-tabell (Ctrl+T). Skriver du en UNIQUE– eller FILTER-formel i en tabellkolumn får du #SPILL!. Däremot går det utmärkt att använda en tabell som källa, till exempel =UNIQUE(Försäljning[Säljare]). Placera bara formeln i ett vanligt område utanför tabellen. Fördelen med en tabell som källa är att nya rader automatiskt kommer med i resultatet.

Vilka Excel-versioner stöds?

UNIQUE (UNIK) och FILTER finns i Excel 2021, Excel för Microsoft 365 och Excel på webben. I Excel 2019, 2016 och äldre saknas både funktionerna och själva spill-tekniken.

Öppnar någon din arbetsbok i en äldre version visas oftast de senast beräknade värdena, men formeln har gjorts om till en traditionell matrisformel med klammerparenteser, och funktionsnamnen får prefixet _xlfn., till exempel =_xlfn.UNIQUE(A2:A20). Så fort cellen redigeras eller räknas om visar den #NAMN?, eftersom den äldre versionen inte känner igen funktionen. Listan slutar också att växa och krympa när källdatan ändras.

Delar du filer med kollegor som har äldre Excel är det därför säkrare att använda de klassiska metoderna, alltså Ta bort dubbletter, Avancerat filter eller formelbaserade lösningar med till exempel INDEX och PASSA. De uppdateras inte lika smidigt, men fungerar i alla versioner.

💡 Ytterligare läsning
Vill du gå ett steg längre och bygga en meny som växer med dina värden, rekommenderar vi den här guiden.

Sammanfattning

Med UNIQUE och FILTER blir listorna en del av arbetsboken i stället för en engångsåtgärd. Du skriver formeln en gång, och när källdatan ändras följer resultatet med. Valet mellan funktionerna beror på vad du vill få fram:

• UNIQUE (UNIK) när du behöver en lista utan dubbletter, till exempel alla kunder, produkter eller regioner som förekommer i en kolumn. Lägg SORT runt om listan ska vara i bokstavsordning.
• FILTER när du vill plocka ut hela rader som uppfyller ett eller flera villkor. Använd * för OCH, + för ELLER och fyll alltid i om_tom, så slipper du #BERÄKNA! när inget matchar.
• En kombination när du vill ha unika värden ur ett urval, till exempel säljarna i en viss region. Med SORT runt om får du en färdig, sorterad lista som passar bra som källa till en listruta i dataverifiering.

Se till att spill-området är tomt, och placera formlerna utanför Excel-tabeller. Om du eller dina kollegor använder en äldre version än Excel 2021 fungerar funktionerna inte. Då är de klassiska verktygen fortfarande det säkra valet.

Vill du jämföra med den traditionella metoden finns den i artikeln Skapa en unik lista i Excel. Vill du bearbeta listor vidare med formler kan du läsa om komplettering av listor i Excel.

💡 Ytterligare läsning
Vill du läsa mer om hur du bygger upp en automatisk hantering av dina listor i Excel, finns det fler tekniker att utforska.