Fuzzy matchning / Fuzzy lookup

Funktioner som LETARAD [VLOOKUP], XLETAUPP [XLOOKUP] eller INDEX/PASSA [INDEX/MATCH] är ju normalt sett perfekta när man ska utföra matchningar av värden mellan två tabeller. Men det finns situationer där ingen av de vanliga Excelfunktionerna räcker till. Va! 😲 Är det verkligen så?

Ja, så är det. Excelfunktioner kräver normalt att hela förekomsten av alla tecken ska stämma för de värden som ska matchas. I exemplet nedan har vi två tabeller.

1. I den vänstra tabellen är en Exceltabell (Kursdeltagare_T) som innehåller namnen på de personer som är anmälda till en viss Excelkurs. Namnen är hämtade från formuläret som deltagarna själva fyllt i.
2. Den högra tabellen (MasterData_T) innehåller data från personalregistret. Där finns fler personer listade och där finns även uppgift om vilken avdelning och ort som deltagarna är knutna mot internt.

Målet är nu att få en lista med alla kursdeltagare, där man också ser information om avdelning och placering.

Ladda ner filen och testa själv här.

Deltagarna har inte riktigt fyllt i sina namn såsom de är införda i personalregistret. Anders Svensson heter Anders B Svensson i personalregistret, Tina har missat mellanslag vid inmatning i formuläret, Lisa Fält har bara använt gemener och Karl Johansson har tydligen stavat sitt namn felaktigt.

Av dessa fyra färgade namnen ovan kommer t.ex. LETARAD [VLOOKUP] bara klara av att matcha en av dem, nämligen Lisa Fält, så funktionen inte gör skillnad mellan små och stora bokstäver. Resten klarar vi inte av att matcha.

Den matchning som vi vill utföra kräver ju någon form av intelligens som testar värden som liknar varandra. Jag behöver hitta ett verktyg som kan hitta ett mönster och samtidigt kan matcha värden mellan mina två tabeller. Fuzzy-matchning [Fuzzy Lookup] gör precis detta.

Två varianter av Fuzzy Lookup

Sedan länge finns det en Add-In för Excel som rätt och slätt heter Fuzzy Lookup och som är utvecklad av Microsoft själva. Du kan ladda ner och själv testa den på:

https://www.microsoft.com/en-us/download/details.aspx?id=15011

I detta tips ska vi dock fokusera på den numera inbyggda funktionaliteten av Fuzzy Lookup som faktiskt finns i Power Query-delen av Excel.

Lösning med Fuzzy Lookup i Power Query

Vi börjar med att lyfta upp de båda tabellerna ”i minnet”. Markera tabell ❶. Klicka på menyfliken Data ❷ och knappen Från tabell/intervall [From Table/Range] ❸.

När Power Query-fönstret öppnas klickar du på menyfliken Start [Home] och Stäng och läs in [Close & Load] och Stäng och läs in till… [Close and load to…].

Välj att alternativet Skapa endast anslutning [Only Create Connection].

Upprepa proceduren för den andra tabellen. I panelen Frågor och anslutningar [Queries and Connections] till höger kommer dina sparade frågor att synas.

Högerklicka på frågan Kursdeltagare_T och välj Slå ihop [Merge].

Lägg till tabellen MasterData_T som matchande tabell och klicka sedan på de fält i respektive tabell som du vill matcha på, d v s Kursdeltagare och Namn.

Notera att Excel/Power Query meddelar att endast 6 av 10 rader kommer att matchas.
(Anledningen till att vanliga letauppfunktioner hittar 7 av 10, medan Power Query hittar 6 av 10 är att Power Query är skiftlägeskänsligt vid matchning, så Lisa Fält hittas inte i detta läge).

Vi ser dock att det finns en lite kryssruta Använd fuzzy-matchning om du vill utföra sammanslagningen. Vad händer om vi kryssar i denna…🤔

Jo, Excel säger att alla värden matchas 🤩.

Vi ser vad som händer när vi fortsätter, så vi bekräftar med OK.

Vi döper om frågan till Fuzzy_T i panelen till höger ❶. Sedan klickar vi på expandera-ikonen i kolumnrubriken MasterData_T ❷ så att vi kan välja vilka fält från den tabellen som vi vill ha med oss för vidare bearbetning. Vi väljer alla kolumner och kryssar ur kryssrutan längst ner för att slippa prefix i kolumnnamnen ❸.

Nu har vi en tabell som visar vilka namn som har matchats. Allt ser snyggt och rätt ut 👍. Vi skulle givetvis i detta läge – när vi sett att rätt namn har matchats – kunnat ta bort den första kolumnen, men vi behåller den för vidare bearbetning i denna övning.

Vi skjuter nu ut resultatet vi har som en Exceltabell i samma bladflik. Klicka på menyfliken Start [Home] och knappen Stäng och läs in [Close & Load] och Stäng och läs in till… [Close and load to…].

Fungerar Fuzzy Lookup alltid så här enkelt?

…eller är det här bara ett skolbokexempel?

Exemplet ovan visade absolut hur smart Fuzzy Lookup kan vara och vi hade ju flera olika typer av avvikelser mellan värdena i tabellerna. Men i verkligheten finns det många gånger ännu svårare logiker och jobbar man med större datamängder som innehåller många olika variationer, behöver man nästan alltid göra lite fininställning av hur Fuzzy Lookup ska operera på dina data. Och självklart finns ”case” där du inte till 100% klarar av att lösa matchningen med Fuzzy Lookup…men vad är alternativet då? (antagligen manuellt arbete).

Själv har jag använt Fuzzy Lookup vid migrering och matchning av data vid byte av ekonomisystem och andra system där man till exempel behövde match kundnamn, artiklar, leverantörsnamn etc mellan olika tabeller/system. Även om man inte kom i mål med 100% av alla matchningar, så bidrog Fuzzy Lookup till mindre manuellt arbete. Kunde man matcha 96% av alla rader, så var det bara 4% kvar att rätta manuellt 😉.

Var noga med att ha i åtanke att du själv aldrig vet exakt hur Excel tänker och vilka algoritmer som används i bakgrunden när du använder Fuzzy Lookup, så det är betydligt viktigare att kontrollera resultatet, jämfört med lösningar du i andra fall gör med formler/funktioner.

Avancerade inställningar i Fuzzy Lookup

Det finns några olika typer av inställningar som du kan göra för att justera hur din Fuzzy Lookup fungerar på dina data.

1. Likhetströskelvärde. Standardvärdet är 0.8 (notera att man skriver punkt istället för komma i Power Query här). Ju lägre värde du sätter, ju större avvikelser kan Excel hitta och matcha. Sätter du ett lägre värde är samtidigt problemet att du kan få flera värden som matchar flera andra värden. Rekommendation är att utgå från standardvärdet och justera stegvis nedåt om matchningen är ”dålig”.
2. De två kryssrutorna Ignorera skiftläge och Matcha genom att kombinera textdelar är default ikryssade. Kryssa ur om du endast vill matcha på exakta gemener/versaler eller om du inte vill matcha t.ex. LisaFält med Lisa Fält.
3. Maximalt antal matchningar. Standardvärdet här är 1. Detta innebär att även om Excel hittar två möjliga matchningar (utifrån ditt valda tröskelvärde) så kommer ändå bara den bäst matchande att returneras. Min erfarenhet (redan vid smått komplexa data) är att det kan vara en rekommendation att välja fler t.ex. 3 st. Då upptäcker man lättare om felmatchningar (enligt ditt sätt att se) har uppkommit från Excels beräkningar.
4. Omvandlingstabell. Här kan man skapa en tabell som kommer att användas för att mappa värden (t.ex. lisafält till Lisa Fält). Det här är ett sätt att underlätta för matchningen, som då i första hand går efter omvandlingstabellen och först därefter (om matchning inte kan ske) utför själva Fuzzy-matchningen. Med hjälp av omvandlingstabellen kan man höja likhetströskelvärdet och/eller minska antalet matchningar och få ett bättre totalresultat.

I e-kursen Power Query på Officekurs.se (ingår i Guldpaketet och Powerpaketet) finns mycket mer om Power Query.

Vill du lära dig mer om Power Query via en lärarledd kurs? Se våra kurser på infocell.se eller kontakta oss för en anpassad kurs om ni är en hel grupp.

Excella lugnt 🤠

Tobias Ljung