Opslag i alle retninger med XOPSLAG
I en række tidlige artikler på denne side, har jeg beskæftiget mig med forskellige former for opslag. Blandt disse artikler er Almindelig brug af LOPSLAG() og VOPSLAG(), Opslag i Intervaller (Endnu mere om opslag), Opslag til højre og venstre og Opslag via koordinater (to-dimensionalt opslag). I disse artikler anvender jeg primært funktionerne LOPSLAG, VOPSLAG samt en kombination af INDEKS og SAMMENLIGN. LOPSLAG, der så op i en tabel organiseret i kolonner, og VOPSLAG, der tilsvarende slår op i en tabel organiseret i rækker, rækker har den samme skavank, nemlig at heholdsvis kolonner og rækker, skal være placeret korrekt. Lad mig bruge LOPSLAG som eksempel:
Alle eksemplerne (på nær de to sidste), vil tage udgangspunkt i nedenstående dataeksempel.
Hvis jeg vil finde navnet på skoleeleven, kan jeg bruge denne formel: =LOPSLAG(H5;A2:G17;2;0). I den første kolonne søges efter den tekst, jeg har skrevet i H2, og LOPSLAG returnerer indholdet fra anden kolonne i samme række:
Og vi kan se, at skoleeleven (titlen tastet i H5), altså hedder Palle Alene, hvilket var det mål, vi havde. Udfordringen er imidlertid, at den kolonne, der søges i, altid skal stå til venstre for den kolonne, resultatet skal returneres fra, og hvis vi bruger VLOPSLAG, skal den række, der returneres fra, altid stå under den række, der søges i. Hvis dette ikke er tilfældet, skal vi enten ud i noget med hjælpekolonner, eller vi må kaste os ud i en kombination af funktionerne INDEKS og SAMMENLIGN. Hvis vi fx vil finde navnet på den person, som bro på Grønnevej 3 (navnet står til venstre for adressen), kan vi bruge denne formel:
=INDEKS(A2:G217;SAMMENLIGN(H5;C2:C17;0);2) og det vil give følgende resultat:
hvoraf vi kan se, at på Grønnevej 3 bor Pia Skrubbetrang.
Da jeg allerede har gennemgået disse funktioner i ovennævnte artikler, vil jeg ikke komme nærmere ind på disse her. I stedet vil jeg vende mig mod den funktion, som artiklen faktisk skal handle om, nemlig funktionen XLOPSLAG. Denne funktion, der blev tilgængelig i 2020, findes KUN i Excel365, og hvis man skal dele et regneark med brugere, der ikke har denne version, er man derfor stadig nødt til at at anvende de "gamle" funktioner i sine regneark, men har man Excel365 og skal kun dele sine ark med andre med samme version af programmet, har man fået en særdeles effektiv og fleksibel funktion, som løser alle ovenstående opgaver plus lidt til. Men lad mig se på hvordan man i første omgang kan løse ovenstående opgaver med XOPSLAG (XLOOKUP på engelsk). Her har jeg valgt at vise Guiden Indsæt funktion, for at kunne gennemgå de enkelte argumenter til funktionen.
Som det fremgår er der tre obligatoriske argumenter, og så er der tre valgfrie, selv om den sidste ikke kan ses, da der skal scrolles ned, for at kunne se ruden, men jeg vender tilbage til den nedenfor.
De to første argumenter minder om dem, vi kender fra fx LOPSLAG. Opslagsværdi er værdien, der skal søges efter, hvad enten den tastes direkte, eller i en celle, som der så refereres til i funktionen, hvilket man bør foretrække.
Opslagsmatrix er området, der skal søges i efter opslagsværdien I modsætning til fx LOPSLAG, vil man her ikke specificerer hele området, men kun den eller de kolonner, der kan indeholde opslagsværdien (fx B2:B17). I LOPSLAG ville det næste argument være Kolonneindeksnummer. men som det fremgår, eksisterer dette argument ikke mere. I stedet skal man referere til det område, Returmatrix, som værdien skal returneres fra, som en reference til et celleområde, fx D2:D17. Og så er man klar, men ofte har man også brug for at specificerer de valgfri argumenter.
I de gamle funktioner, får man en cellefejl, typisk #I/T!, hvis det man søger efter ikke findes, men i XLOOKUP har man selv mulighed for at specificere en tekst, der er mere sigende. Det gøres i argumentet Hvis_:ikke_:fundet.
Matchtilstand fortæller, hvordan der sammenlignes, og her skal man angive den sammenligningstype, der ønskes. Denne type repræsenteres af et tal som følgende: 0 betyder, at der skal findes et nøjagtigt match. Hvis der ikke findes et nøjagtigt match, returneres #I/T eller den tekst, man selv har specificeret i argumentet Hvis_ikke_fundet. Dette er standardindstillingen, hvis argumentet ikke udfyldes. -1 (altså minus 1) søger efter et nøjagtigt match. Hvis der ikke findes et sådant, returneres det næste lavere element. Dette svarer til at sætte argumentet Sorteret til SAND i LOPSLAG, hvor det vil kræve at tabellen er sorteret i stigende orden. Sortering er ikke et krav i XOPSLAG. 1 søger efter et nøjagtigt match. Hvis det ikke findes, returneres det næste større element. Og endelig giver 2 mulighed for at ancende jokertegnene * og ? i sin opslagsværdi.
Det sidste argument, som ikke er vist i illustrationen ovenfor hedder Søgetilstand. Her kan man også bruge værdierne 1 og -1 (minus 1). ! betyder at der søges fra første til sidste element (oppefra og ned i kolonnen eller fra venstre mog højre i rækken), og -1 præcis det modsatte, at der søges fra sidste til første element. Der er yderligere to muligheder, nemlig 2 og -2, som jeg ikke vil komme nærmere ind på. De er rettet mod binære søgninger, som kræver at tabellen er sorteret i enten stigende (2) eller faldende (-2) orden .Men lad mig se på nogle eksempler med XOPSLAG:
=XOPSLAG(H9;A2:A17;B2:B17;"Personen eksisterer ikke";0;1) vil returnere
som i eksemplet med LOPSLAG.
=XOPSLAG(H9;C2:C17;B2:B17;"Personen eksisterer ikke";0;1) vil returnere
som i eksempelet med INDEKS og SAMMENLIGN. XOPSLAG kan altså direkte finde værdier, der står til venstre for opslagskolonnen vewd simpelthen at specificere en retrumatrix, der ligger til venstre for opslagsmatricen.
=XOPSLAG(H9;F2:F17;G2:G17;";-1;1) vil returnere
Her søger vi efter et beløb, som ikke findes for at få returneret datoen. Da beløbet ikke findes returneres den nærmeste lavere dato (som ikke er vist i illustrationen, men findes i række 5. Jeg indrømmer at eksemplet kunne have været bedre, men det var det eneste, som datatabellen gav anledning til at bruge. Det svarer altså til, at man havde sorteret regbearket efter dato og så brugt SAND som sidste argument i en LOPSLAG.
=XOPSLAG(H9;B2:B17;C2:C17;"Personen eksisterer ikke";2;1) vil returnere
Her er der brugt * i opslagsværdien med 2 i argumentet matchtilstand, hvilket netop tillader brugen af jokertegn.
Hvis man vil have flere kolonner retur, kunne man med LOPSLAG specificere fx =LOPSLAG(H5;A2:G17;2;0) i en celle, =LOPSLAG(H5;A2:G17;3;0) i en anden, =LOPSLAG(H5;A2:G17;4;0) i endnu en og så fremdeles, men det er ikke nødvendigt med XLOOKUP. Her kan man fx nøjes med
=XOPSLAG(H8;A2:A17;B2:E17;"Personen findes ikke";0;1)
som vil returnere
og de sidste to kolonner kunne naturligvis også have være kommet med ved at ændre B2:E17 til B2:G17. Her vil formlen kun stå i den celle, hvor den er indsat, og kun tilsyneladende i de øvrige, i det der anvendes et par andre funktioner, der også er nye i Excel 365, Dynamisk Array og #Overløb (#Spill på engelsk). Dette kan give problemer, hvis overløbet går ud over regnearkets yderste høje kolonne, men med 16.384 kolonner at gøre godt med, skal der en hel del til.
Til sidst et par eksempler på vandret opslag i stedet for lodret. Her bruger jeg disse eksempeldata:
Her vil jeg gerne finde ud af, hvad sygeplejersken hedder, og det kan jeg gøre med denne formel
=VOPSLAG(E3;A1:D7;2;)
eller med
=XOPSLAG(E3;B1:D1;B2:D2;"Findes ikke";0;1)
I begge tilfælde returneres i F3
Der er i øvrigt komme en del flere nye funktioner i Excel365, og jeg vil muligvis tilbage til nogle af disse i kommende artikler. Det vil også være i Excel 365 at eventuelt ny udvikling vil ske, fx overgangen fra VBA til Office Scripts som programmeringsværktøj.