Vil du bli bedre på Excel-funksjoner?

Fyll inn din e-postadresse og vi gir deg 7 dager gratis tilgang til kurset Excelfunksjoner Fordypning.

Del

Denne leksjonen og mange andre kan man finne i Learnesys grunnkurs Excelkurs Basis!

Excel-funksjonen XOPPSLAG er en moderne og fleksibel erstatning for eldre funksjoner som FINN.RAD, FINN.KOLONNE og SLÅ.OPP. XOPPSLAG støtter delvis og nøyaktige samsvar, spesialtegn (*?) for delvis samsvar og oppslag i både vertikale eller horisontale celleområder.

Argumentene til XOPPSLAG-funksjonen er kun tilgjengelig på engelsk.

Hensikt

Finn verdier i et område eller en matrise

Returverdi

Samsvarende verdier fra returmatrise

Syntaks

= XOPPSLAG (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Argument

lookup_value – Oppslagsverdien.

lookup_array – Matrisen eller celleområdet du vil søke i.

return_array – Matrisen eller celleområdet som skal returneres.

if_not_found – [valgfritt] Verdien som skal returneres hvis ingen samsvar blir funnet.

match_mode – [valgfritt] 0 = eksakt samsvar (standard), -1 = eksakt samsvar eller nærmeste minimumsverdi, 1 = eksakt samsvar eller nærmeste maksimumsverdi, 2 = samsvar med jokertegn.

search_mode – [valgfritt] 1 = søk fra første til siste (standard), -1 = søk fra sist til første, 2 = binært søk stigende, -2 = binært søk synkende.

Bruksnotater

XOPPSLAG er en moderne erstatning for FINN.RAD-funksjonen. Det er en fleksibel og allsidig funksjon som kan brukes i en rekke situasjoner.

XOPPSLAG kan finne verdier i både vertikale eller horisontale områder, kan utføre omtrentlige og eksakte treff, og støtter spesialtegn (*?) for delvis treff. I tillegg kan XOPPSLAG søke etter data fra den første verdien til den siste eller fra den siste verdien til den første. Sammenlignet med eldre funksjoner som FINN.RAD, FINN.KOLONNE og SLÅ.OPP, gir XOPPSLAG flere fordeler.

Matchende type

Som standard vil XOPPSLAG utføre en eksakt match. Samsvarsatferden styres av det valgfrie argumentet match_type, som har følgende alternativer:

Matchende typeAtferd
0 (standard)Nøyaktig match. Kommer tilbake #I/T hvis ingen treff blir funnet.
-1Nøyaktig match eller det nest minste objektet
1Nøyaktig match eller det nest største objektet.
2Jokertegn matching (*, ? , ~)

Søkemodus 

Som standard begynner XOPPSLAG å søke fra den første dataverdien til den siste. Søkemodusen styres av det valgfrie argumentet search_mode, som har følgende alternativer:

SøkemodusAtferd
1 (standard)Søk fra første til siste verdi
-1Søk fra siste til første verdi (omvendt)
2Binære søkeverdier – sortert i stigende rekkefølge
-2Binære søkeverdier sortert i synkende rekkefølge

Binære søk er raske, men dataene må sorteres etter behov. Hvis dataene våre ikke er riktig sortert, kan et binært søk gi ugyldige resultater som i utgangspunktet ser helt normale ut.

Grunnleggende eksakt matching

Som standard vil XOPPSLAG utføre en eksakt match. I eksemplet nedenfor brukes XOPPSLAG for å hente salg basert på en nøyaktig match. Formelen i H5 er:

= XOPPSLAG(I4;C5:C9;F5:F9)

Grunnleggende omtrentlig match

For å aktivere et omtrentlig matching, skriv inn en verdi for match_mode-argumentet. I eksemplet nedenfor brukes XOPPSLAG til å beregne en rabatt basert på antall, som krever en omtrentlig match. Formelen i G5 tar -1 som match_mode for å aktivere et omtrentlig samsvar med atferden “eksakt matching eller nærmeste minimum”:

= XOPPSLAG(F5;C5:C9;D5:D9;;-1)

Flere verdier

XOPPSLAG har muligheten til å returnere mer enn én verdi om gangen for samme match. Eksemplet nedenfor viser hvordan du oppretter en XOPPSLAG-funksjon for å returnere tre samsvarende verdier, alle med en enkelt formel. Formelen i D5 er:

= XOPPSLAG(C5;C8:C15;D8:F15)

Merk at returmatrisen (D8:F15) inneholder 3 kolonner: Fornavn, Etternavn og Avdeling. Alle tre verdiene returneres og går over i celleområdet D5: F5.

Toveis oppslag

XOPPSLAG kan brukes til å utføre et toveis oppslag ved å sette inn en XOPPSLAG inni en annen. I eksemplet nedenfor henter den “indre” XOPPSLAG-funksjonen en hel rad (alle verdier for raden kalt Glass), som deretter sendes til den “ytre” XOPPSLAG som returmatrisen til funksjonen. Den ytre XOPPSLAG finner den passende gruppen (C) og returnerer den tilsvarende verdien som sluttresultat.

=XOPPSLAG(J6;D4:G4;XOPPSLAG(J5;C5:C9;D5:G9)) 

“Ikke funnet”-melding

Hvis XOPPSLAG ikke finner en samsvarende verdi, returneres feilen #I/T. Hvis du vil vise en egendefinert melding i stedet, skriv inn en verdi for det valgfrie argumentet “if_not_found”, i anførselstegn (“”). Hvis du for eksempel vil vise “Ikke funnet” når ingen samsvarende film er funnet i regnearket nedenfor, bruk denne formelen:

= XOPPSLAG(I4;C5:C9;F5:F9;”Ikke funnet”)

Du kan tilpasse denne meldingen som du ønsker.

Komplekse vilkår

På grunn av evnen til å håndtere matriser internt, kan XOPPSLAG brukes sammen med komplekse vilkår. I eksemplet nedenfor samsvarer XOPPSLAG med den første oppføringen i tabellen der:

  • kontoen starter med “x”
  • regionen er “øst”
  • måneden er ikke mars:

= XOPPSLAG(1;(VENSTRE(C5:C16)=”x”)*(D5:D16=”øst”)*IKKE(MÅNED(E5:E16)=3);C5:F16)

Fordeler med XOPPSLAG

XOPPSLAG tilbyr flere viktige fordeler, spesielt sammenlignet med FINN.RAD:

XOPPSLAG kan finne både data til høyre eller venstre for oppslagsverdier

XOPPSLAG kan returnere flere resultater fra en enkelt funksjon

Som standard kjører XOPPSLAG en eksakt match (mens FINN.RAD kjører som standard omtrentlig)

XOPPSLAG fungerer med både vertikale og horisontale data

XOPPSLAG kan utføre et omvendt søk (sist til først)

XOPPSLAG kan returnere hele rader eller kolonner, i stedet for bare én verdi

XOPPSLAG kan arbeide med matriser internt for å anvende komplekse forhold

Notater

  • XOPPSLAG kan fungere med både vertikale og horisontale matriser.
  • XOPPSLAG returnerer #I/T-feilen hvis oppslagsverdien ikke blir funnet.
  • Argumentet lookup_array må ha en dimensjon som er kompatibel med return_array-argumentet, ellers oppstår det en verdifeil.
  • Hvis XOPPSLAG brukes mellom arbeidsbøker, må begge arbeidsbøkene være åpne, ellers vil en referansefeil bli returnert.

En av de nyere funksjonene i Excel er XLETAOPP. XLETAOPP-funksjonen ligner veldig på LETERAD og LETAKOLONNE, men i motsetning til disse trenger vi ikke å angi en indeksverdi for kolonner eller rader hvor vi henter verdien fra. I stedet angir vi et område. Vi trenger heller ikke å ha verdien vi leter opp lengst til venstre eller øverst, noe som gjør funksjonen mer fleksibel.

I dette eksempelet skal vi finne ut skattesatsen og gruppen for en inntekt som vi angir i celle F2. Vi går til celle H2 og skriver inn XLETAOPP-funksjonen. Det første argumentet er verdien vi leter opp, så vi markerer cellen der vi angir inntekten. Det neste argumentet er området vi leter i, og her angir vi celle C2 til C9. Deretter angir vi området som funksjonen skal returnere verdier fra. Når vi bruker XLETAOPP, trenger vi bare å angi et område hvor verdier skal returneres fra, som i vårt tilfelle er alle celler i kolonne A og B. Vi er nå ferdige med alle argumentene som trengs for XLETAOPP-funksjonen, men som vi ser, kan vi også angi ytterligere argumenter.

Det første er verdien som returneres hvis vi ikke finner den oppsøkte verdien. Her skriver vi null. Deretter skriver vi 1 for nøyaktig samsvar, og hvis det ikke finnes, returneres neste større verdi i listen. I det siste argumentet skriver vi også 1 for å søke fra første til siste.

Vi er nå ferdige med XLETAOPP-funksjonen vår, så vi trykker Enter og skriver inn en inntekt på 36 000 dollar i celle G2 og ser at XLETAOPP-funksjonen returnerer riktig skattesats og skattegruppe.