Se video - Excel XLOOKUP -funksjon (10 XLOOKUP -eksempler)
utmerke XLOOKUP -funksjon har endelig kommet.
Hvis du har brukt VLOOKUP eller INDEX/MATCH, er jeg sikker på at du vil like fleksibiliteten som XLOOKUP -funksjonen gir.
I denne opplæringen vil jeg dekke alt det er å vite om XLOOKUP -funksjonen og noen eksempler som hjelper deg å vite hvordan du bruker den best.
Så la oss komme i gang!
Hva er XLOOKUP?
XLOOKUP er en ny funksjon er Office 365 og er en ny og forbedret versjon av VLOOKUP/HLOOKUP -funksjonen.
Den gjør alt VLOOKUP pleide å gjøre, og mye mer.
XLOOKUP er en funksjon som lar deg raskt lete etter en verdi i et datasett (vertikal eller horisontal) og returnere den tilsvarende verdien i en annen rad/kolonne.
For eksempel, hvis du har fått poengsummene for studenter i en eksamen, kan du bruke XLOOKUP til raskt å sjekke hvor mye en elev har scoret ved å bruke elevens navn.
Kraften til denne funksjonen vil bli enda tydeligere etter hvert som jeg dykker dypt ned i noen XLOOKUP -eksempler senere i denne opplæringen.
Men før jeg går inn på eksemplene, er det et stort spørsmål - hvordan får jeg tilgang til XLOOKUP?
Hvordan få tilgang til XLOOKUP?
Per nå er XLOOKUP bare tilgjengelig for brukerne av Office 365.
Så hvis du bruker tidligere versjoner av Excel (2010/2013/2016/2019), kan du ikke bruke denne funksjonen.
Jeg er heller ikke sikker på om dette noen gang ville bli utgitt for tidligere versjoner eller ikke (kanskje Microsoft kan lage et tillegg som de gjorde for Power Query). Men fra nå av får du bare bruke den hvis du bruker Office 365.
Klikk her for å oppgradere til Office 365
Hvis du allerede er på Office 365 (Home, Personal eller University edition) og ikke har tilgang til den, kan du gå til Fil -fanen og deretter klikke på Konto.
Det ville være et Office Insider -program, og du kan klikke og bli med i Office Insider -programmet. Dette gir deg tilgang til XLOOKUP -funksjonen.
Jeg forventer at XLOOKUP snart er tilgjengelig på alle Office 365 -versjoner.
Merk: XLOOKUP er også tilgjengelig for Office 365 for Mac og Excel for Internett (Excel online)XLOOKUP Funksjon Syntaks
Nedenfor er syntaksen til XLOOKUP -funksjonen:
= XLOOKUP (oppslag_verdi, oppslag_array, retur_array, [hvis_funnet], [match_modus], [søkemodus])
Hvis du har brukt VLOOKUP, vil du legge merke til at syntaksen er ganske lik, med noen fantastiske tilleggsfunksjoner selvfølgelig.
Ikke bekymre deg hvis syntaksen og argumentet ser litt for mye ut. Jeg dekker disse med noen enkle XLOOKUP -eksempler senere i denne opplæringen som vil gjøre det krystallklart.XLOOKUP -funksjonen kan fortelle 6 argumenter (3 obligatoriske og 3 valgfrie):
- oppslag_verdi - verdien du leter etter
- oppslag_array - matrisen du leter etter oppslagsverdien i
- retur_array - matrisen du vil hente og returnere verdien fra (tilsvarer posisjonen der oppslagsverdien er funnet)
- [if_not_found] - verdien som skal returneres hvis oppslagsverdien ikke blir funnet. Hvis du ikke spesifiserer dette argumentet, vil en #I/A -feil returneres
- [match_mode] - Her kan du angi hvilken type kamp du ønsker:
- 0 - Eksakt samsvar, der oppslagsverdi nøyaktig skal samsvare med verdien i oppslag_rommet. Dette er standardalternativet.
- -1 - Ser etter den eksakte matchingen, men hvis den blir funnet, returnerer den neste mindre varen/verdien
- 1 - Ser etter den eksakte matchingen, men hvis den blir funnet, returnerer den neste større varen/verdien
- 2 - For å gjøre delvis matching ved hjelp av jokertegn (* eller ~)
- [søkemodus] - Her angir du hvordan XLOOKUP -funksjonen skal søke i lookup_array
- 1 - Dette er standardalternativet der funksjonen begynner å lete etter oppslagsverdi fra toppen (første element) til bunn (siste element) i oppslag_rommet
- -1 - Gjør søket fra bunn til topp. Nyttig når du vil finne den siste matchende verdien i lookup_array
- 2 - Utfører et binært søk der dataene må sorteres i stigende rekkefølge. Hvis det ikke er sortert, kan dette gi feil eller feil resultater
- -2 - Utfører et binært søk der dataene må sorteres i synkende rekkefølge. Hvis det ikke er sortert, kan dette gi feil eller feil resultater
XLOOKUP Funksjon Eksempler
La oss nå komme til den interessante delen - noen praktiske XLOOKUP -eksempler.
Disse eksemplene vil hjelpe deg å bedre forstå hvordan XLOOKUP fungerer, hvordan det er forskjellig fra VLOOKUP og INDEX/MATCH og noen forbedringer og begrensninger av denne funksjonen.
Klikk her for å laste ned eksempelfilen og følge med
Eksempel 1: Hent en oppslagsverdi
Anta at du har følgende datasett, og du vil hente matematikkpoengene for Greg (oppslagsverdien).
Nedenfor er formelen som gjør dette:
= XLOOKUP (F2, A2: A15, B2: B15)
I formelen ovenfor har jeg nettopp brukt de obligatoriske argumentene der det ser etter navnet (fra topp til bunn), finner en eksakt samsvar og returnerer den tilsvarende verdien fra B2: B15.
En åpenbar forskjell XLOOKUP og VLOOKUP -funksjonen har er måten de håndterer oppslagsmateriale på. I VLOOKUP har du hele matrisen der oppslagsverdien er i kolonnen lengst til venstre, og deretter angir du kolonnummeret du vil hente resultatet fra. XLOOKUP, derimot, lar deg velge lookup_array og return_array separatEn umiddelbar fordel med å ha lookup_array og return_array som separate argumenter betyr at nå kan du se til venstre. VLOOKUP hadde denne begrensningen der du bare kan slå opp og finne en verdi som er til høyre. Men med XLOOKUP er den begrensningen borte.
Her er et eksempel. Jeg har det samme datasettet, der navnet er til høyre og return_range er til venstre.
Nedenfor er formelen jeg kan bruke for å få poengsummen for Greg i matematikk (som betyr å se til venstre for oppslagsverdi)
= XLOOKUP (F2, D2: D15, A2: A15)
XLOOKUP løser et annet stort problem - Hvis du setter inn en ny kolonne eller flytter kolonner rundt, vil de resulterende dataene fortsatt være riktige. VLOOKUP vil sannsynligvis gå i stykker eller gi et feil resultat i slike tilfeller, ettersom kolonneindeksverdien oftest er hardkodet.
Eksempel 2: Slå opp og hent en hel post
La oss ta de samme dataene som et eksempel.
I dette tilfellet vil jeg ikke bare hente Gregs poengsum i matematikk. Jeg ønsker å få poengsummen i alle fagene.
I dette tilfellet kan jeg bruke formelen nedenfor:
= XLOOKUP (F2, A2: A15, B2: D15)
Formelen ovenfor bruker et return_array -område som er mer enn en kolonne (B2: D15). Så når oppslagsverdien er funnet i A2: A15, returnerer formelen hele raden fra return_array.
Du kan heller ikke slette bare celler som er en del av matrisen som ble fylt ut automatisk. I dette eksemplet kan du ikke slette H2 eller I2. Hvis du prøver, ville ingenting skje. Hvis du velger disse cellene, vil formelen i formellinjen bli nedtonet (noe som indikerer at den ikke kan endres)
Du kan slette formelen i celle G2 (der vi opprinnelig skrev den inn), den vil slette hele resultatet.
Dette er en nyttig forbedring som tidligere med VLOOKUP, du må spesifisere kolonnenummeret separat for hver formel.
Eksempel 3: Toveis oppslag ved hjelp av XLOOKUP (horisontalt og vertikalt oppslag)
Nedenfor er et datasett der jeg vil vite poengsummen til Greg i matematikk (emnet i celle G2).
Dette kan gjøres ved å bruke et toveis oppslag hvor jeg ser etter navnet i kolonne A og emnets navn i rad 1. Fordelen med dette toveis oppslaget er at resultatet er uavhengig av studentnavnet til emnetavnet. Hvis jeg endrer fagnavnet til kjemi, vil denne toveis XLOOKUP-formelen fortsatt fungere og gi meg det riktige resultatet.
Nedenfor er formelen som vil utføre toveisoppslag og gi det riktige resultatet:
= XLOOKUP (G1, B1: D1, XLOOKUP (F2, A2: A15, B2: D15))
Denne formelen bruker en nestet XLOOKUP, der jeg først bruker den til å hente alle karakterene til eleven i celle F2.
Så resultatet av XLOOKUP (F2, A2: A15, B2: D15) er {21,94,81}, som er en rekke merker som ble scoret av Greg i dette tilfellet.
Dette brukes deretter igjen i den ytre XLOOKUP -formelen som returmatrise. I den ytre XLOOKUP -formelen ser jeg etter emnetavnet (som er i celle G1), og oppslagsoppsettet er B1: D1.
Hvis emnetavnet er matematikk, henter denne ytre XLOOKUP -formelen den første verdien fra returmatrisen - som er {21,94,81} i dette eksemplet.
Dette gjør det samme som hittil ble oppnådd ved å bruke kombinasjonen INDEX og MATCH
Klikk her for å laste ned eksempelfilen og følge med
Eksempel 4: Når oppslagsverdi ikke blir funnet (feilhåndtering)
Feilhåndtering er nå lagt til i XLOOKUP -formelen.
Det fjerde argumentet i XLOOKUP -funksjonen er [if_not_found], der du kan spesifisere hva du vil i tilfelle oppslaget ikke blir funnet.
Anta at du har datasettet som vist nedenfor der du vil få matematikkpoengene hvis matchen, og hvis navnet ikke blir funnet, vil du returnere - 'Vises ikke'
Formelen nedenfor vil gjøre dette:
= XLOOKUP (F2, A2: A15, B2: B15, "Vises ikke")
I dette tilfellet har jeg hardkodet hva jeg vil få i tilfelle det ikke er noen match. Du kan også bruke en cellereferanse til en celle eller en formel.
Eksempel 5: Nestet XLOOKUP (oppslag i flere områder)
Det geniale ved å ha [if_not_found] argumentet er at det lar deg bruke nestet XLOOKUP -formel.
Anta for eksempel at du har to separate lister som vist nedenfor. Selv om jeg har disse to tabellene på samme ark, kan du ha disse i separate ark eller til og med arbeidsbøker.
Nedenfor er den nestede XLOOKUP -formelen som vil se etter navnet i begge tabellene og returnere den tilsvarende verdien fra den angitte kolonnen.
= XLOOKUP (A12, A2: A8, B2: B8, XLOOKUP (A12, F2: F8, G2: G8))
I formelen ovenfor har jeg brukt [if_not_found] -argumentet for å bruke en annen XLOOKUP -formel. Dette lar deg legge til den andre XLOOKUP i samme formel og skanne to tabeller med en enkelt formel.
Jeg er ikke sikker på hvor mange nestede XLOOKUP -er du kan bruke i en formel. Jeg prøvde til 10 og det fungerte, så ga jeg opp 🙂
Eksempel 6: Finn den siste matchende verdien
Denne var sårt tiltrengt og XLOOKUP gjorde dette mulig. Nå trenger du ikke finne innviklede måter å få den siste matchende verdien i et område.
Anta at du har datasettet som vist nedenfor, og du vil kontrollere når den siste personen ble ansatt i hver avdeling og hva som var ansettelsesdatoen.
Formelen nedenfor vil slå opp den siste verdien for hver avdeling og gi navnet på den siste leien:
= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ A $ 2: $ A $ 15 ,,,-1)
Og formelen nedenfor gir leiedatoen for den siste leieperioden for hver avdeling:
= XLOOKUP (F1, $ B $ 2: $ B $ 15, $ C $ 2: $ C $ 15 ,,,-1)
Siden XLOOKUP har en innebygd funksjon for å spesifisere oppslagets retning (først til siste eller siste til første), gjøres dette med en enkel formel. Med vertikale data ser VLOOKUP og INDEX/MATCH alltid fra topp til bunn, men med XLOOKUP og kan også angi retningen som bunn til topp.
Eksempel 7: Omtrentlig match med XLOOKUP (Finn skattesats)
En annen bemerkelsesverdig forbedring med XLOOKUP er at det nå er fire matchmoduser (VLOOKUP har 2 og MATCH har 3).
Du kan angi et av de fire argumentene for å bestemme hvordan oppslagsverdien skal matches:
- 0 - Eksakt samsvar, der oppslagsverdi nøyaktig skal samsvare med verdien i oppslag_rommet. Dette er standardalternativet.
- -1 - Ser etter den eksakte matchingen, men hvis den blir funnet, returnerer den neste mindre varen/verdien
- 1 - Ser etter den eksakte matchingen, men hvis den blir funnet, returnerer den neste større varen/verdien
- 2 - For å gjøre delvis matching ved hjelp av jokertegn (* eller ~)
Nedenfor har jeg et datasett der jeg vil finne provisjonen til hver person - og kommisjonen må beregnes ved hjelp av tabellen til høyre.
Nedenfor er formelen som gjør dette:
= XLOOKUP (B2, $ E $ 2: $ E $ 6, $ F $ 2: $ F $ 6,0, -1)*B2
Dette bruker ganske enkelt salgsverdien som oppslag og ser gjennom oppslagstabellen til høyre. I denne formelen har jeg brukt -1 som det femte argumentet ([match_mode]), noe som betyr at den vil lete etter en eksakt samsvar, og når den ikke finner en, vil den returnere verdien bare mindre enn oppslagsverdien .
Og som jeg sa, du trenger ikke å bekymre deg for om dataene dine er sortert på ikke.
Klikk her for å laste ned eksempelfilen og følge med
Eksempel 8: Horisontal oppslag
XLOOKUP kan gjøre både vertikal og horisontal oppslag.
Nedenfor har jeg et datasett der jeg har elevnavn og poengsummen deres i rader, og jeg vil hente poengsummen for navnet i celle B7.
Formelen nedenfor vil gjøre dette:
= XLOOKUP (B7, B1: O1, B2: O2)
Dette er ikke annet enn et enkelt oppslag (lik det vi så i eksempel 1), men horisontalt.
Alle eksemplene jeg dekker om vertikal oppslag kan også gjøres med et horisontalt oppslag ved hjelp av XLOOKUP (farvel til VLOOKUP og HLOOKUP).
Eksempel 9: Betinget oppslag (bruk av XLOOKUP med andre formler)
Dette er et litt avansert eksempel, og viser også kraften til XLOOKUP når du trenger å gjøre komplekse oppslag.
Nedenfor er et datasett der jeg har navnene på elevene og poengsummen deres, og jeg vil vite navnet på eleven som har scoret maksimum i hvert fag og antallet elever som har scoret mer enn 80 i hvert fag.
Nedenfor er formelen som vil gi navnet på eleven med de høyeste karakterene i hvert emne:
= XLOOKUP (MAX (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)), XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), $ A $ 2: $ A $ 15)
Siden XLOOKUP kan brukes til å returnere en hel matrise, har jeg brukt den til først å få alle karakterene for det nødvendige emnet.
For eksempel, for matematikk, når jeg bruker XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), gir det meg alle poengsummene i matte. Jeg kan deretter bruke MAX -funksjonen til å finne maksimal score i dette området.
Denne maksimale poengsummen blir deretter min oppslagsverdi, og oppslagsområdet vil være matrisen som returneres av XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)
Jeg bruker dette i en annen XLOOKUP -formel for å hente navnet på eleven som har fått maksimalkarakterene.
Og for å telle antall studenter som har scoret mer enn 80, bruk formelen nedenfor:
= TELLING (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), "> 80")
Denne bruker ganske enkelt XLOOKUP -formelen for å få et område med alle verdiene for det gitte emnet. Den pakker den inn i COUNTIF -funksjonen for å få antall poeng som er mer enn 80.
Eksempel 10: Bruke jokertegn i XLOOKUP
Akkurat som du kan bruke jokertegn i VLOOKUP og MATCH, kan du også gjøre dette med XLOOKUP.
Men det er en forskjell.
I XLOOKUP må du spesifisere at du bruker jokertegn (i det femte argumentet). Hvis du ikke spesifiserer dette, vil XLOOKUP gi deg en feil.
Nedenfor er et datasett der jeg har firmanavn og deres markedsverdi.
Jeg vil slå opp et firmanavn i kolonne D, og hente markedsverdi fra tabellen til venstre. Og siden navnene i kolonne D ikke er eksakte samsvar, må jeg bruke jokertegn.
Nedenfor er formelen som gjør dette:
= XLOOKUP ("*" & D2 & "*", $ A $ 2: $ A $ 11, $ B $ 2: $ B $ 11,, 2)
I formelen ovenfor har jeg brukt asterisk (*) jokertegn før som etter D2 (det må være innenfor doble anførselstegn og sluttet med D2 ved hjelp av ampersand).
Dette forteller formelen for å se gjennom alle cellene, og hvis det inneholder ordet i celle D2 (som er Apple), bør du vurdere det som en eksakt samsvar. Uansett hvor mange og hvilke tegn som er før og etter teksten i celle D2.
Og for å sikre at XLOOKUP godtar jokertegn, har det femte argumentet blitt satt til 2 (jokertegn).
Eksempel 11: Finn den siste verdien i kolonnen
Siden XLOOKUP lar deg søke fra bunn til topp, kan du enkelt finne den siste verdien i en liste, samt hente den tilsvarende verdien fra en kolonne.
Anta at du har et datasett som vist nedenfor, og du vil vite hva som er det siste selskapet og hva som er markedsverdi for dette siste selskapet.
Formelen nedenfor gir deg navnet på det siste selskapet:
= XLOOKUP ("*", A2: A11, A2: A11,, 2, -1)
Og formelen nedenfor vil gi markedsverdi for det siste selskapet på listen:
= XLOOKUP ("*", A2: A11, B2: B11,, 2, -1)
Disse formlene bruker igjen jokertegn. I disse har jeg brukt stjerne (*) som oppslagsverdi, noe som betyr at denne vil betrakte den første cellen den møter som en eksakt samsvar (da stjerne kan være et hvilket som helst tegn og et hvilket som helst antall tegn).
Og siden retningen er fra bunn til topp (for de vertikalt ordnede dataene), vil den returnere den siste verdien i listen.
Og den andre formelen siden bruker en egen return_range for å få markedsverdien på etternavnet i listen.
Klikk her for å laste ned eksempelfilen og følge med
Hva om du ikke har XLOOKUP?
Siden XLOOKUP sannsynligvis bare vil være tilgjengelig for Office 365 -brukere, er en måte å få det på å oppgradere til Office 365.
Hvis du allerede har Office 365 Home, Personal eller University edition, har du allerede tilgang til XLOOKUP. Alt du trenger å gjøre er å bli med i Office Insider -programmet.
For å gjøre dette, gå til kategorien Fil, klikk på Konto og klikk deretter på alternativet Office insider. Det ville være et alternativ å bli med i insider -programmet.
Hvis du har andre Office 365 -abonnementer (for eksempel Enterprise), er jeg sikker på at XLOOKUP og andre fantastiske funksjoner (for eksempel dynamiske matriser, formler som SORT og FILTER) snart blir tilgjengelige.
Hvis du bruker Excel 2010/2013/2016/2019, har du ikke XLOOKUP, og du må fortsette å bruke kombinasjonen VLOOKUP, HLOOKUP og INDEX/MATCH for å få det beste ut av oppslagsformler.
XLOOKUP bakoverkompatibilitet
Dette er en ting du må være forsiktig med - XLOOKUP er IKKE bakoverkompatibel.
Dette betyr at hvis du oppretter en fil og bruker XLOOKUP -formelen, og deretter åpner den i en versjon som ikke har XLOOKUP, vil den vise feil.
Siden XLOOKUP er et stort skritt fremover i riktig retning, tror jeg at dette vil bli standard oppslagsformel, men det vil sikkert ta noen år før det blir bredt vedtatt. Tross alt ser jeg fremdeles noen som bruker Excel 2003.
Så dette er 11 XLOOKUP -eksempler som kan hjelpe deg med å gjøre alle oppslag og referanse ting gjort raskere og også gjøre det enkelt å bruke.
Håper du synes denne opplæringen var nyttig!
