Den nya superhjältefunktionen XLETAUPP (XLOOKUP)
Den här artikeln avhandlar funktionen XLETAUPP. För att få en snabblektion i XLETAUPP kan man se lektionen nedan från Learnesys kurs Excelkurs Bas. Läs inlägget på engelska här.
Om man vill lära sig Microsoft Excels nya superfunktion XLETAUPP så är ett bra tips att försöka använda funktionen varje gång man avser använda LETARAD eller LETAKOLUMN. På så sätt kommer man snabbare lära sig hur man arbetar med funktionen.
Vad är XLETAUPP?
Funktionen lades till i Office 365 år 2020 och har sedan dess till viss del ersatt:
- LETARAD
- LETAKOLUMN
- OMFEL
- INDEX & PASSA
- FÖRSKJUTNING
Jag tycker att man ska fortsätta på det sätt som fungerar bra men inte vara rädd för att utvärdera nya funktioner för att se vad de kan ge och om man kan arbeta ännu effektivare. Jag har inte låtit XLETAUPP ersätta LETARAD i mitt arbete, däremot använder jag funktionen när jag vet att den är effektivare. Ju mer jag använder den desto mer användningsområden hittar jag.
Se nedan för argumenten:
- LETAUPP-värdet (samma som LETAUPP)
- LETAUPP-område (samma som första kolumnen i LETAUPP)
- Retur-intervallet (samma som efter första kolumnen i LETAUPP)
- Vad händer om det inte finns något värde att returnera? (valfri)
- Match-mode bestämmer hur värdena ska matchas (valfri)
6. Search-mode bestämmer i vilken ordning sökningen sker (valfri)
Egen erfarenhet
Jag har hittills använt funktionen när jag arbetat med att sätta ihop information som kan finnas lite varstans och när LETARAD’s funktion inte fungerar. Till exempel när jag arbetade med bonus budgetering i vintras var den nyttig då jag fick personaldata från HR och lön samt bonusvillkor från CFO.
5 stycken exempel
Jag kommer i nedan förklaring ta just bonusberäkning för en ledningsgrupp som exempel. Detta då det brukar vara så att man behöver fånga upp olika faktorer från olika tabeller som inte alltid matchar varandra beroende på att de kommer från olika avdelningar med sin speciella uppsättning.
Vid bonus beräkningar brukar man ofta ha följande faktorer; lön (kr), bonusandel (%), uppnått mål (%), maxbonus (kr). Dvs, bonusen beräknas på lönen och andelen samtidigt som man beaktar uppnått mål. Det finns även ett tak för hur hög bonus som man kan betala ut. Dessa förutsättningar har vi i vårt exempel nedan där vi först behöver populera de saknade fälten (röd text) för ledningsgruppen samt beräkna utbetalningen. Man vill även veta vem som har högst utbetalning.
Exempel 1: När XLETAUPP ersätter LETARAD
Vi börjar med en enkel övning, nämligen att lägga in avdelning vilket vi fått från HR. Vi ser nedan att vi bara använder de tre obligatoriska argumenten för denna övning där vi bara har en enda kolumn som returnerar värden. Vi ska strax göra det lite mer komplicerat…..?.
Exempel 2: När XLETAUPP ersätter LETARAD bakåt samt Jokerteckenmatchning
I nedan exempel ser ni att man kan fånga upp ett värde till vänster om LETAUPP-värdet. Detta går inte i LETARAD (jag har dock hittat en workaround på detta). Namnen i ledningsgruppen ska hittas i tabellen från Löneavdelningen och vi vill mappa in aktuella löner.
Samtidigt har vår analys ruta bara förnamn och matchar inte helt och hållet löns info (denna situation är mycket vanligt pga egna system). Vi nyttjar nu Jokerteckenmatchning eller Wildcards som det kallas på engelska. Detta innebär att vi behöver välja cellen C4 samt lägga till &”*” vilket innebär att vi utgår från befintligt förnamn och gör gällande att det kan komma ytterligare ett namn efter förnamnet i tabellen vi letar i. Om vi sätter ”*” före C4 så blir det tvärtom.
Begränsningen i denna funktion är om det finns flera personer med samma förnamn. Observera att man kan även skriva; “*”&C3&”*”, dvs Joker på båda sidor men i vårt fall vet vi att vi har förnamnet på alla i vår ledningsgrupp.
Notera att jag har valt att lägga till det valfria argumentet som definierar vad som händer om vi inte hittar något namn och skrivit ”Saknas”. Jag har även i matchningen valt ”2” som innebär Jokerteckenmatchning.
Exempel 3: När XLETAUPP letar upp flera faktorer
Funktionen kan, om man markerar ett område men flera rader samt flera kolumner, hitta flera specificerade faktorer för ett letauppvärde. I exemplet nedan har jag markerat 2 kolumner (T samt U) då vi vill hitta både bonus andelen i procent samt maxbonus per namn.
Begränsningen som jag ser det, ligger i att faktorerna i sammanställningen behöver vara densamma som i tabellen och i samma ordning. Dvs F2 behöver vara samma som T2 samt G2 samma som U2.
Om vi skriver in formeln i F2 så kommer den även automatiskt populeras i kolumn G och vi kommer se denna automatisering genom att formeln i G är grå:
Funktionen är toppen om man har många faktorer som ska hittas efter varandra. Håll dock koll så det hela inte går för fort!?
Exempel 4: När XLETAUPP använder en kapslad formel (nested funktion)
I detta exempel ser vi hur vi kan använda en kapslad formel i XLETAUPP. Detta då vi behöver få in värden från två tabeller på olika platser i Excelfilen. Det är inte ovanligt att man erhåller rådatan på detta sätt och jag har för enkelhetens skull lagt de små tabellerna intill varandra men de kan ligga i olika flikar samt även i olika filer.
=XLETAUPP(D3;Tabell5[Avdelning];Tabell5[Uppnått mål];XLETAUPP(D3;Tabell6[Avdelning];Tabell6[Uppnått mål]))
Vad gör då denna formel…jo, vi har här använt det valfria argumentet If_not_found och skrivit in att XLETAUPP (igen) ska kolla i en sekundär tabell om den inte hittar något värde…inte krångligare än så. Jag hittade en video på Youtube där en indiskt Excel stjärna testade att lägga till 10-15 tabeller och det funkade utmärkt.
Vi ser nu att vår beräkning för ledningsgruppen är klar och Nisse är den som får mest i bonus då han får ut max bonus på 100 tkr. Nisse har uppnått 75% av målet och har hela 80 tkr i månadslön varmed han för detta skulle fått 108 tkr om styrelsen inte satt ett tak på 100 tkr. Pia är den enda i ledningsgruppen som inte får någon bonus då kravet är att uppnå minst 50% av målet.
Exempel 5: När XLETAUPP ersätter INDEX&MATCH
Låt oss säga att vi backar bandet något och inte fått ”Uppnått mål” ännu och vill simulera vem som eventuellt kan få högst bonus. Innan ledningen signar av bonus målen behöver ekonomi alltid göra en konsekvensanalys där man går igenom vad det eventuella utfallet kan bli samt vad det innebär för de anställda samt i förhållande till budget.
Vi behöver nu involvera MAX-formeln i vår super-formel XLETAUPP.
Vad händer då i denna formel? Jo, vi låter formeln ta reda på vilket värde som är störst i kolumnen för utbetalning och sen leta upp vem som får den specifika utbetalning. Som ni ser så har jag markerat alla kolumner som ett område att leta i och finessen med min formel är att jag via en filterlista kan definiera om det är utbetalning eller lön etc som vi vill kontrollera.
Hur funkar formeln egentligen….?
Lookup value
Om vi tittar på funktionsargumenten så börjar vi formeln med att definiera att det är en XLETAUPP-funktion. Sedermera får MAX-formeln med hjälp av XLETAUPP hitta högsta värdet för den faktor som vi definierar i cell D11.
Lookup array
Här letar XLETAUPP efter värdena, i vårt fall har jag satt filtret på uppnått mål så ni ser att de värden som syns är 0,75 (75%), 1,0 (100% etc.
Return array
Här finner funktionen namnen på de personer som finns med i urvalet samt vem som har det högsta värdet.
Avslutande ord
Fokuset för detta lite längre inlägg har varit XLETAUPP samt 5 användningsområden som jag oftast nyttjar denna till. Kom ihåg att bästa sättet att lära sig formeln på är att tillfälligt låta bli LETARAD. Välkomna att connecta med mig på LinkedIn och kolla in Learnesys kursutbud.
Nedan kan ni även se det webinarium jag höll i Learneys regi. Jag hoppas ni ska lära er något nytt.
Carl Stiller i samarbete med Learnesy
