Sådan bruges XLOOKUP-funktionen i Excel 2016

Excel 2016 til Office 365-abonnenter på Windows og Mac understøtter nu en ny XLOOKUP-funktion, udråbt som en betydeligt enklere og mere alsidig erstatning for den meget populære (men ofte udskældte) vertikale opslagsfunktion, VLOOKUP (ved ikke hvad X'et i XLOOKUP står for; omfattende, måske?).

For dem af jer, der endnu ikke er bekendt med VLOOKUP (anset som den tredje mest brugte funktion lige efter SUM og AVERAGE), søger denne funktion lodret for række i kolonnen længst til venstre i en udpeget opslagstabel fra top til bund, indtil den finder en værdi i en opslagskolonne angivet med et offsetnummer, der matcher eller overstiger det, du slår op. Selvom det er enormt nyttigt til at lokalisere bestemte elementer i en lang liste eller kolonne i en datatabel i dit regneark, har VOPSLAG-funktionen adskillige begrænsninger, som ikke deles af denne nye opslagsfunktion, som XLOOKUP:

• Finder som standard nøjagtige match for din opslagsværdi i opslagsområdet
• Kan søge både lodret (efter række) og vandret (efter kolonne) i en tabel, og erstatter derved behovet for at bruge HLOOKUP-funktionen ved søgning vandret efter kolonne
• Kan søge til venstre eller højre, så opslagsområdet i din opslagstabel ikke skal være placeret i en kolonne til venstre for det, der er udpeget som returområde, for at funktionen kan fungere
• Når den nøjagtige match-standard bruges, fungerer den, selv når værdier i opslagsområdet ikke er sorteret i særlig rækkefølge
• Kan søge fra nederste række til toppen i opslagsmatrixområdet ved hjælp af et valgfrit søgetilstandsargument

XLOOKUP-funktionen har fem mulige argumenter, hvoraf de tre første er påkrævet og de sidste to valgfrie, ved hjælp af følgende syntaks:

XLOOKUP ( opslagsværdi , opslagsmatrixen , return_array , [ match_mode ], [ search_mode ])

Det påkrævede opslagsværdi- argument angiver den værdi eller det element, som du søger efter. Det påkrævede opslagsmatrixargument angiver det celleområde, der skal søges efter denne opslagsværdi, og argumentet return_array angiver det celleområde, der indeholder den værdi, du ønsker returneret, når Excel finder et eksakt match.

* Husk, når du designer lookup_array og return_array-argumenterne i din XLOOKUP-funktion, skal begge områder være lige lange, ellers returnerer Excel #VALUE! fejl i din formel. Dette er så meget mere grunden til, at du bruger områdenavne eller kolonnenavne på en udpeget datatabel, når du definerer disse argumenter, i stedet for at påpege dem eller indtaste deres cellereferencer .

Det valgfri match_mode- argument kan indeholde en hvilken som helst af følgende fire værdier:

• 0 for et nøjagtigt match (standard, samme som når der ikke er angivet noget match_mode- argument)
• -1 for nøjagtig match eller næste mindre værdi
• 1 for nøjagtig match eller næste større værdi
• 2 for delvist match ved hjælp af jokertegn forbundet med cellereference i lookup_value- argumentet

Det valgfrie search_mode- argument kan indeholde en hvilken som helst af følgende fire værdier:

• 1 for at søge først-til-sidst, det vil sige fra top til bund (standard, det samme som når der ikke er angivet noget search_mode- argument)
• -1 for at søge sidst-til-først, det vil sige bund til top
• 2 for en binær søgning i stigende rækkefølge
• -2 for binær søgning i faldende rækkefølge

Den bedste måde at forstå kraften og alsidigheden af ​​den nye XLOOKUP-funktion er at se den i aktion i et Excel-regneark. I den følgende figur har jeg et regneark med en simpel 2019 salgsdatatabel arrangeret efter land. For at bruge XLOOKUP til at returnere det samlede salg fra denne tabel i celle E4 baseret på det land, du indtaster i celle D4 i regnearket, skal du følge disse trin:

Placer cellemarkøren i celle E4 i regnearket

Klik på indstillingen Opslag og reference på fanen Formler efterfulgt af XLOOKUP nær bunden af ​​rullemenuen for at åbne dialogboksen Funktionsargumenter.

Klik på celle D4 i regnearket for at indtaste dens cellereference i tekstfeltet Lookup_value-argument.

Tryk på Tab for at vælge tekstfeltet Lookup_array-argument, klik derefter på celle A4 og hold Shift nede, mens du trykker på Ctrl-pil ned for at vælge A4:A8 som det område, der skal søges i (fordi området A3:B8 er defineret som en Excel-datatabel, Tabel1[Land] vises i tekstboksen i stedet for området A4:A8).

Tryk på Tab for at vælge Return_array-argumenttekstboksen, klik derefter på celle B4, og hold Shift nede, mens du trykker på Ctrl-pil ned for at vælge B4:B8 som det område, der indeholder de værdier, der skal returneres baseret på resultaterne af søgningen (der vises som Tabel 1[Samlet salg] i tekstboksen).

Klik på OK for at indtaste XLOOKUP-formlen i celle E4.

Oprettelse af en formel med XLOOKUP i celle E4, der returnerer salg baseret på det land, der er indtastet i celle D4.

Excel indtaster XLOOKUP-formlen i celle E4 i regnearket og returnerer 4900 som resultat, fordi Costa Rica i øjeblikket er indtastet i opslagscellen D4, og som du kan se i salgstabellen for 2019, er dette faktisk det samlede salg for dette land.

Fordi XLOOKUP fungerer lige så godt fra højre mod venstre som fra venstre mod højre, kan du lige så godt bruge denne funktion til at returnere landet fra denne salgstabel baseret på et bestemt salgstal. Følgende figur viser dig, hvordan du gør dette. Denne gang opretter du XLOOKUP-formlen i celle D4 og angiver værdien, der er indtastet i celle E4 (11.000, i dette tilfælde) som opslagsværdi-argumentet.

Derudover indtaster du -1 som match_mode-argumentet for at tilsidesætte funktionens nøjagtige match-standard, så Excel returnerer landet med et nøjagtigt match til salgsværdien indtastet i opslagscellen E4 eller den med det næste lavere samlede salg (Mexico med $10.000 i dette tilfælde, da der ikke er noget land i denne tabel med $11.000 af det samlede salg). Uden at angive et match_mode-argument for denne formel, ville Excel returnere #NA som resultat, fordi der ikke er noget nøjagtigt match til $11.000 i denne salgstabel.

Oprettelse af en formel med XLOOKUP i celle D4, der returnerer landet baseret på det salg, der er indtastet i celle E4

Fordi XLOOKUP-funktionen er lige så behagelig at søge vandret efter kolonne, som den søger lodret for række, kan du bruge den til at oprette en formel, der udfører et to-vejs opslag (erstatter behovet for at oprette en formel, der kombinerer INDEX- og MATCH-funktionerne som i fortiden). Følgende figur, der indeholder 2019 produktionsplantabellen for varenumre, AB-100 til AB-103 for månederne april til december, viser dig, hvordan dette gøres.

Oprettelse af en formel med indlejrede XLOOKUP-funktioner for at returnere antallet af producerede enheder for en del i en bestemt måned

I celle B12 oprettede jeg følgende formel:

=XLOOKUP(part_lookup,$A$3:$A$6,XLOOKUP(datoopslag,$B$2:$J$2,$B$3:$J$6))

Denne formel begynder med at definere en XLOOKUP-funktion, der lodret søger efter række efter et nøjagtigt match til den delindtastning, der er lavet i cellen med navnet part_lookup (celle B10, i dette tilfælde) i celleområdet $A$3:$A$6 i produktionstabellen . Bemærk dog, at return_array-argumentet for denne originale OPSLAG-funktion i sig selv er en anden XOPSLAG-funktion.

Denne anden, indlejrede XLOOKUP-funktion søger i celleområdet $B$2:$J$2 vandret efter kolonne for et nøjagtigt match til datoindtastningen i cellen med navnet date_lookup (celle B11, i dette tilfælde). Return_array-argumentet for denne anden, indlejrede XLOOKUP-funktion er $B$3:$J$6, celleområdet for alle produktionsværdier i tabellen.

Måden denne formel fungerer på er, at Excel først beregner resultatet af den anden, indlejrede XLOOKUP-funktion ved at udføre en vandret søgning, der i dette tilfælde returnerer matrixen i celleområde D3: D6 i kolonnen Jun-19 (med værdierne: 438, 153, 306 og 779) som resultat. Dette resultat bliver på sin side return_array-argumentet for den oprindelige XLOOKUP-funktion, der udfører en lodret søgning efter række for et nøjagtigt match til varenummerindtastningen i celle B11 (kaldet part_lookup). Fordi denne del_opslag-celle i dette eksempel indeholder AB-102, returnerer formlen kun produktionsværdien for juni-19, 306, fra resultatet af den anden, næste XLOOKUP-funktion.

Der har du det! Et første kig på XLOOKUP, en kraftfuld, alsidig og ret brugervenlig ny opslagsfunktion, der ikke kun kan udføre enkeltværdiopslag udført af funktionerne VLOOKUP og HLOOKUP, men også de to-vejs værdiopslag, der udføres ved at kombinere INDEX og MATCH funktioner også.

* Desværre er XLOOKUP-funktionen ikke bagudkompatibel med tidligere versioner af Microsoft Excel, der kun understøtter VLOOKUP- og HLOOKUP-funktionerne eller kompatibel med nuværende versioner, der endnu ikke inkluderer det som en af ​​deres opslagsfunktioner, såsom Excel 2019 og Excel Online . Det betyder, at hvis du deler en projektmappe, der indeholder XLOOKUP-formler med kolleger eller klienter, der bruger en version af Excel, der ikke indeholder denne nye opslagsfunktion, vil alle disse formler returnere #NAVN? fejlværdier, når de åbner dets arbejdsark.