Denne artikkelen avhandler funksjonen XOPPSLAG. For å få en hurtigleksjon i XOPPSLAG kan man se leksjonen nedenfor fra Learnesys kurs Excelkurs Basis. Les innlegget på engelsk her.

---

Om man vil lære seg Microsoft Excels nye superfunksjon XOPPSLAG så er et godt tips å forsøke å bruke funksjonen hver gang man har tenkt å bruke FINN.RAD eller FINN.KOLONNE. På den måten vil man raskere lære seg hvordan man arbeider med funksjonen.

Hva er XOPPSLAG?

Funksjonen ble lagt til i Office 365 i 2020 og har siden da til en viss grad erstattet:

• FINN.RAD
• FINN.KOLONNE
• HVISFEIL
• INDEKS & SAMMENLIGNE
• FORSKYVNING

Jeg synes at man skal fortsette på den måten som fungerer bra, men ikke være redd for å evaluere nye funksjoner for å se hva de kan gi og om man kan arbeide enda mer effektivt. Jeg har ikke latt XOPPSLAG erstatte FINN.RAD i mitt arbeid, men jeg bruker funksjonen når jeg vet at den er mer effektiv. Jo mer jeg bruker den desto flere bruksområder finner jeg.

Se nedenfor for argumentene:

1. Oppslagsverdi (samme som OPPSLAG)
2. Oppslagsmatrise (samme som første kolonnen i OPPSLAG)
3. Returmatrise (samme som etter første kolonnen i OPPSLAG)
4. Hva skjer om det ikke finnes noen verdi å returnere? (valgfritt)
5. Samsvarsmodus bestemmer hvordan verdiene skal samsvares (valgfritt)

6. Søkemodus bestemmer i hvilken rekkefølge søket skjer (valgfritt)

Egen erfaring

Jeg har hittil brukt funksjonen når jeg har arbeidet med å sette sammen informasjon som kan finnes litt her og der, og når FINN.RAD-funksjonen ikke fungerer. For eksempel da jeg arbeidet med bonusbudsjettering i vinter, var den nyttig da jeg fikk personaldata fra HR og lønn samt bonusvilkår fra CFO.

5 eksempler

Jeg kommer i forklaringen nedenfor til å ta bonusberegning for en ledergruppe som eksempel. Dette da det ofte er slik at man må fange opp ulike faktorer fra forskjellige tabeller som ikke alltid samsvarer fordi de kommer fra ulike avdelinger med sin spesielle oppsett.

Ved bonusberegninger pleier man ofte å ha følgende faktorer; lønn (kr), bonusandel (%), oppnådd mål (%), maksbonus (kr). Dvs, bonusen beregnes på lønnen og andelen samtidig som man tar hensyn til oppnådd mål. Det finnes også et tak for hvor høy bonus man kan betale ut. Disse forutsetningene har vi i eksempelet nedenfor der vi først må fylle inn de manglende feltene (rød tekst) for ledergruppen samt beregne utbetalingen. Man vil også vite hvem som har høyest utbetaling.

Eksempel 1: Når XOPPSLAG erstatter FINN.RAD

Vi begynner med en enkel øvelse, nemlig å legge inn avdeling som vi har fått fra HR. Vi ser nedenfor at vi bare bruker de tre obligatoriske argumentene for denne øvelsen der vi bare har en eneste kolonne som returnerer verdier. Vi skal straks gjøre det litt mer komplisert…

Eksempel 2: Når XOPPSLAG erstatter FINN.RAD bakover samt Jokertegn

I eksempelet nedenfor ser dere at man kan hente en verdi til venstre for oppslagsverdien. Dette går ikke i FINN.RAD (jeg har dog funnet en løsning på dette). Navnene i ledergruppen skal finnes i tabellen fra Lønnsavdelingen og vi vil mappe inn aktuelle lønninger.

Samtidig har vår analyserute bare fornavn og samsvarer ikke helt med lønnsinfo (denne situasjonen er svært vanlig pga. egne systemer). Vi bruker nå jokertegn eller wildcards som det heter på engelsk. Dette innebærer at vi må velge cellen C4 samt legge til &”” hvilket innebærer at vi utgår fra eksisterende fornavn og sier at det kan komme ytterligere et navn etter fornavnet i tabellen vi søker i. Om vi setter ”” foran C4 så blir det motsatt.

Begrensningen i denne funksjonen er om det finnes flere personer med samme fornavn. Observer at man også kan skrive: “”&C3&””, dvs. joker på begge sider, men i vårt tilfelle vet vi at vi har fornavnet på alle i vår ledergruppe.

Noter at jeg har valgt å legge til det valgfrie argumentet som definerer hva som skjer om vi ikke finner et navn og skrevet ”Mangler”. Jeg har også i samsvaringen valgt ”2” som innebærer jokertegn-samsvar.

Eksempel 3: Når XOPPSLAG finner flere faktorer

Funksjonen kan, om man markerer et område med flere rader samt flere kolonner, finne flere spesifiserte faktorer for en oppslagsverdi. I eksempelet nedenfor har jeg markert 2 kolonner (T samt U) da vi vil finne både bonusandelen i prosent samt maksbonus per navn.

Begrensningen som jeg ser det, ligger i at faktorene i sammenstillingen må være de samme som i tabellen og i samme rekkefølge. Dvs. F2 må være den samme som T2 samt G2 den samme som U2.

Om vi skriver inn formelen i F2 så vil den også automatisk bli fylt inn i kolonne G, og vi vil se denne automatiseringen gjennom at formelen i G er grå:

Funksjonen er topp om man har mange faktorer som skal finnes etter hverandre. Hold dog kontroll så det hele ikke går for fort!

Eksempel 4: Når XOPPSLAG bruker en nestet formel

I dette eksempelet ser vi hvordan vi kan bruke en nestet formel i XOPPSLAG. Dette da vi må få inn verdier fra to tabeller på forskjellige steder i Excel-filen. Det er ikke uvanlig at man får rådata på denne måten, og jeg har for enkelhets skyld lagt de små tabellene ved siden av hverandre, men de kan ligge i forskjellige faner samt også i ulike filer.

=XOPPSLAG(D3;Tabell5[Avdeling];Tabell5[Oppnådd mål];XOPPSLAG(D3;Tabell6[Avdeling];Tabell6[Oppnådd mål]))

Hva gjør da denne formelen… jo, vi har her brukt det valgfrie argumentet Hvis_ikke_funnet og skrevet inn at XOPPSLAG (igjen) skal se i en sekundær tabell om den ikke finner en verdi… ikke mer komplisert enn det. Jeg fant en video på Youtube der en indisk Excel-stjerne testet å legge til 10–15 tabeller og det fungerte utmerket.

Vi ser nå at vår beregning for ledergruppen er klar og Nisse er den som får mest bonus da han får ut maksbonus på 100 000 kr. Nisse har oppnådd 75 % av målet og har hele 80 000 kr i månedslønn, hvilket ville gitt ham 108 000 kr om styret ikke hadde satt et tak på 100 000 kr. Pia er den eneste i ledergruppen som ikke får noen bonus da kravet er å oppnå minst 50 % av målet.

Eksempel 5: Når XOPPSLAG erstatter INDEKS & SAMMENLIGNE

La oss si at vi spoler litt tilbake og ikke har fått ”Oppnådd mål” ennå og vil simulere hvem som eventuelt kan få høyest bonus. Før ledelsen godkjenner bonusmålene må økonomi alltid gjøre en konsekvensanalyse der man går gjennom hva det mulige utfallet kan bli samt hva det innebærer for de ansatte og i forhold til budsjett.

Vi må nå involvere MAKS-formelen i vår superformel XOPPSLAG.

Hva skjer da i denne formelen? Jo, vi lar formelen finne ut hvilket verdi som er størst i kolonnen for utbetaling og så finne hvem som får den spesifikke utbetalingen. Som dere ser har jeg markert alle kolonner som et område å søke i, og finessen med formelen er at jeg via en filterliste kan definere om det er utbetaling eller lønn osv. vi vil kontrollere.

• 
• 
• 

Hvordan fungerer formelen egentlig…?

Oppslagsverdi
Om vi ser på funksjonsargumentene, så begynner vi formelen med å definere at det er en XOPPSLAG-funksjon. Deretter får MAKS-formelen med hjelp av XOPPSLAG finne høyeste verdi for den faktoren vi definerer i celle D11.

Oppslagsmatrise
Her leter XOPPSLAG etter verdiene, i vårt tilfelle har jeg satt filteret på oppnådd mål så dere ser at verdiene som vises er 0,75 (75 %), 1,0 (100 %) osv.

Returmatrise
Her finner funksjonen navnene på de personene som finnes med i utvalget samt hvem som har den høyeste verdien.

Avsluttende ord

Fokuset for dette litt lengre innlegget har vært XOPPSLAG samt 5 bruksområder som jeg oftest benytter denne til. Husk at den beste måten å lære seg formelen på er å midlertidig la være å bruke FINN.RAD. Velkommen til å connecte med meg på LinkedIn og sjekke ut Learnesys kurstilbud.

Nedenfor kan dere også se webinaret jeg holdt i Learnesys regi. Jeg håper dere lærer noe nytt.

Carl Stiller i samarbeid med Learnesy