Sådan bruges den nye Excel opslagsfunktion XOPSLAG

Lidt fra kolonne A, lidt fra kolonne B: XOPSLAG er den nye, lettere måde at hente oplysninger på tværs af Excel-regneark.

Hvis du arbejder med dine konti, og du skal konvertere alle dine oversøiske udgifter til den valuta, du indsender dine selvangivelser i, skal du kende valutakursen for datoen for hver udgift. Du behøver ikke at udfylde det i hånden: du kan få en liste med de daglige valutakurser og få Excel til at slå den rigtige konvertering op for hver udgift. Du kan også bruge LOPSLAG, når du skal finde en komponent efter varenummeret, en medarbejder efter medarbejdernummeret, den internationale opkaldskode for et land eller andet, som du slår op med en nøgle fra en hovedpost, der ikke giver mening at kopiere til dit nuværende regneark.

Men hvis du kun gør det en gang om året, skal du sandsynligvis undersøge, hvordan du bruger LOPSLAG-funktionen i Excel.

Den tredje mest anvendte funktion i Excel (efter SUM og MIDDEL) LOPSLAG er kompleks. Som standard finder den omtrentlige matches, selvom det næsten aldrig er det, du vil have, så du skal huske at angive FALSK i funktionen. Og det er ikke fleksibelt: Hvis du tilføjer eller sletter en kolonne i arket, hvor du leder efter tal, opdateres formlen ikke, som den gør for funktioner som SUM, så du skal ændre din LOPSLAG-formel i hånden. Du er nødt til at arrangere kildedataene, så indekskolonnen, ligesom datoen, er til venstre, og de værdier, du vil have, ligesom hastigheden, er til højre; du kan ikke finde den dag, hvor udvekslingen var værst eller bedst uden at have en anden kopi af dataene med dataene i en anden rækkefølge. Du kan ikke ændre søgerækkefølgen, og hvis du skal slå op i oplysninger, der er i rækker frem for kolonner, skal du bruge en anden funktion i stedet, VOPSLAG.

LOPSLAG kan også være et præstationshit, fordi det skaber en matrix, der dækker indekskolonnen, kolonnen med de data, du har brug for, og andre kolonner imellem dem – selvom du er ligeglad med dataene imellem.

Den nye XOPSLAG-funktion løser alle disse problemer, da den er enklere, mere fleksibel og ikke bremser dine regneark på samme måde. Du kan slå resultaterne op fra rækker eller kolonner, og datakolonnen behøver ikke at være til højre for indekskolonnen. Du kan pege XOPSLAG på flere kolonner og hente mere end en information – du kan f.eks. slå op på både medarbejdernavnet og den afdeling, de er i. Du kan inkludere en brugerdefineret fejl for at udfylde (f.eks. ”Navn ikke fundet”), hvis der ikke findes et match i stedet for at få standard #/T. Du kan tilpasse både søgerækkefølgen og hvordan matchning fungerer. Og det resultat, du får tilbage, er en reference, ikke en værdi, hvilket betyder, at du kan videregive det til en anden formel.

XOPSLAG har brug for mindst tre parametre: det udtryk, du bruger til at søge efter, hvor du skal lede efter udtrykket og de data, du skal hente, og hvor du skal placere de data, der bliver slået op.

Det første udtryk, opslagsværdi, kan være en celle med den værdi, du leder op, en værdi, du skriver i formlen som et navn, eller en anden formel som en beregning. Det er det samme som med LOPSLAG, men det kan også være en sammenkædning af flere celler i et array – for eksempel B1&B2&B3 leder efter værdien, hvor alle tre celler matcher – i stedet for kun en enkelt celle der skal matche.

Men i stedet for et array som A2:D200, der fortæller LOPSLAG at kigge i cellerne A2 til A200 efter opslagsværdien og derefter hente værdien fra den samme række i kolonne D, bruger XOPSLAG to parametre – opslagsmatrix og returmatrix. For det samme opslag ville det være A2:A200 og D2:D200. I stedet for at oprette et array med næsten 800 celler, skal XOPSLAG kun håndtere knap 400. Jo større regnearket du kigger efter, jo større ydelsesforskel gør det.

Endvidere, hvis du indsætter en ny kolonne mellem indekskolonnen og den, resultaterne er i, opdateres formlen automatisk i stedet for at bryde den måde, den ville med LOPSLAG – fordi du ville få resultater tilbage fra den forkerte kolonne.

Hvis du vil se på flere kolonner for den matchende værdi, skal du samle dem ved hjælp af & – C2:C200&E2:E200.

Hvis du har brug for at finde ud af, hvilken placering der havde det højeste salg, hvem der bruger mest af diskkvoten, eller hvem der har brugt det færreste antal feriedage, skal du bruge en MAKS- eller MIN-formel i opslagsværdien. Dette er mere fleksibelt, end det var med LOPSLAG, fordi du ikke behøver at omarrangere dataene, så den værdi, du bruger til at slå oplysninger op med, altid er i kolonnen længst til venstre; XOPSLAG kan se til venstre eller højre, op eller ned – du angiver bare hvilke kolonner eller rækker der skal kigges i.

Hvis du ser på disse tal i det samme regneark, kan du bare bruge den samlede række i en tabel og indstille totalen til MAKS eller MIN eller bruge betinget formatering til at trække oplysninger ud. Men hvis du vil hente det minimum eller maksimum, der skal bruges i et andet regneark, har du brug for en opslagsfunktion.

Hvis du vil slå flere oplysninger op med den samme søgning, kan du stadig bruge en matrix, men du kan hente alt med et enkelt opslag frem for at have brug for en for hver information, du vil have tilbage. Så for at lede efter medarbejdernavnet i kolonne C og deres afdeling i kolonne D, udfylder du returmatrix som C2:D200.

Hvis du er tilfreds med standardsøgerækkefølgen og fejl, er det alt, hvad du skal bruge til at søge. Det er en lettere formel at forstå end LOPSLAG. Men du kan også tilpasse opslaget.

Hvis du vil have en brugerdefineret fejl som ”navn ikke fundet” eller ”ingen valutakurs for denne dato” eller noget, der tydeligere forklarer fejlen end #/T, at der ikke var et match at returnere, skal du sætte det i anførselstegn som det fjerde parameter (som kaldes [Hvis_ikke_fundet]).

Normalt leder du efter det nøjagtige svar – f.eks. et navn eller valutakursen for en bestemt dag. Men du kan også slå op, hvor et tal falder på en skala, som f.eks. At finde, hvilken skatteklasse en løn falder i. I så fald kan du muligvis ikke få et eksakt match. Indstil den femte parameter [Matchtilstand] til 1, og hvis der ikke er et eksakt match, får du det næststørste resultat; -1 henter det næst mindste element. Hvis du matcher en løn, der falder inden for et skatteinterval, vil du matche det næststørste resultat, fordi skatteintervallet gælder op til et maksimumstal. Så en løn på kr. 300.000 skulle slå op på skattesatsen på 40%, som gælder op til kr. 500.000: matchning på det næststørste resultat får det.

Indstil Matchtilstand til 2, og du kan bruge de sædvanlige Excel jokertegn ? og * for at angive, hvad der skal matches på. På den måde kan du søge efter Syd og Sydøst med ”Syd*” eller Nordvest og Sydvest med ”*vest”.

Standardsøgningen efter XOPSLAG er top til bund: Indstil den sjette parameter [Søgetilstand] til -1, hvis du vil søge fra bunden af listen, indtil du finder det første match.

Funktion til funktion

Du kan indlejre XOPSLAG-funktioner for at trække en celle ud af en tabel ved at slå etiketterne i den øverste række og venstre kolonne op: Brug den første XOPSLAG til at finde den første etiket og udfyld en anden XOPSLAG-funktion som returmatrix for at finde den anden etiket og resultatet bliver indholdet af cellen, hvor de skærer hinanden. (Det er ligesom at bruge INDEX- og SAMMENLIGN-funktionerne, men du behøver ikke at lære yderligere to funktioner.) Den nemmeste måde at gøre det på er at have de samme etiketter ovenfor og ved siden af cellen, hvor du vil se resultatet, og for at bruge disse etiketter til at matche.

Resultatet, der kommer tilbage fra XOPSLAG, er en reference til en celle frem for en kopi af værdien i den, så du kan også videregive den til en anden formel. Hvis du bruger to XOPSLAG, kan du forvandle det til et array og bruge SUM til at tilføje alle cellerne mellem dem, eller MAKS for at finde de største værdier mellem de to. På den måde kunne du slå valutakursen op i begyndelsen og slutningen af måneden og vise den højeste kurs.

Husk også at læse denne artikel med eksempler!

Bo Jönsson

One Comment

Skriv et svar

Din e-mailadresse vil ikke blive publiceret. Krævede felter er markeret med *

16 − fourteen =