1. SoftwareMicrosoft OfficeExcelHvordan bruges XLOOKUP-funktionen i Excel 2016

Af Greg Harvey

Excel 2016 til Office 365-abonnenter på Windows og Mac understøtter nu en ny XLOOKUP-funktion, anført som en betydeligt enklere og mere alsidig erstatning for den meget populære (men ofte malignede) lodrette opslagningsfunktion, VLOOKUP (ved ikke hvad X i XLOOKUP står for; eXtensive, måske?).

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

  • Standard er at finde nøjagtige matchninger for din opslagværdi i opslagsområdet Kan søge både lodret (efter række) og vandret (efter kolonne) i en tabel og derved erstatte behovet for at bruge HLOOKUP-funktionen, når du søger vandret efter kolonne Kan søge til venstre eller højre, så opslagsområdet i din opslagstabel ikke behøver at være placeret i en kolonne til venstre for den, der er angivet som returområdet, 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 den nederste række til toppen i opslagsarrayområdet ved hjælp af et valgfrit søgemodusargument

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

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

Det krævede lookup_value-argument angiver den værdi eller det element, du søger efter. Det krævede array-look_up-array angiver det område af celler, der skal søges efter denne opslagværdi, og return_array-argumentet angiver det område af celler, der indeholder den værdi, du vil have returneret, når Excel finder et nøjagtigt match.

* Husk, når du udpeger lookup_array- og return_array-argumenterne i din XLOOKUP-funktion, begge områder skal være af samme længde, ellers returnerer Excel #VALUE! fejl til din formel. Dette er meget mere grunden til, at du bruger rækkenavne eller kolonnenavne i en udpeget datatabel, når du definerer disse argumenter i stedet for at påpege dem eller skrive deres cellehenvisninger.

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

  • 0 for et nøjagtigt match (standard, det samme som når der ikke er angivet et match_mode-argument) -1 for nøjagtigt match eller næste mindste værdi 1 for nøjagtigt match eller næste større værdi 2 til delvis matchning ved hjælp af jokertegn, der er knyttet til cellehenvisning i argumentet lookup_value

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

  • 1 for at søge først til sidste, 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ørste, det vil sige bund til top 2 for en binær søgning i stigende rækkefølge -2 til binær søgning i faldende rækkefølge

Den bedste måde at forstå kraften og alsidigheden i den nye XLOOKUP-funktion er at se den i handling i et Excel-regneark. I den følgende figur har jeg et regneark med en simpel salgsdatatabel for 2019 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 tage disse trin:

  1. Placer cellemarkøren i celle E4 i regnearket Klik på funktionen 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 cellehenvisning i tekstfeltet Lookup_value-argument. Tryk på Tab for at vælge tekstfeltet Lookup_array, og klik derefter på celle A4 og hold Skift nede, mens du trykker på Ctrl-pil ned for at vælge A4: A8 som det område, der skal søges (fordi området A3: B8 er defineret som en Excel-datatabel, Tabel1 [Land] vises i tekstfeltet i stedet for området A4: A8). Tryk på Tab for at vælge tekstfeltet Return_array, og klik derefter på celle B4 og hold Skift 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å søgeresultaterne (der vises som Tabel1 [Samlet salg] i tekstfeltet).

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

Oprettelse af XLOOKUP-formel

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

Fordi XLOOKUP fungerer lige fra venstre lige så godt som venstre til højre, kan du bruge denne funktion lige så godt til at returnere landet fra denne salgstabel baseret på et bestemt salgstall. Følgende figur viser, hvordan du gør det. Denne gang opretter du XLOOKUP-formlen i celle D4 og udpeger den værdi, der er indtastet i celle E4 (11.000, i dette tilfælde) som lookup_value-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, der er indtastet i opslagcellen 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 udpege et match_mode-argument for denne formel, ville Excel returnere #NA som resultatet, fordi der ikke er noget nøjagtigt match til $ 11.000 i denne salgstabel.

XLOOKUP formel i D4

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

indlejrede XLOOKUP-funktioner

I celle B12 oprettede jeg følgende formel:

= XLOOKUP (part_lookup, $ A $ 3: $ A $ 6, XLOOKUP (date_lookup, $ 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 en nøjagtig match til den delindgang, der er foretaget 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 LOOKUP-funktion i sig selv er en anden XLOOKUP-funktion.

Denne anden, indlejrede XLOOKUP-funktion søger celleområdet $ B $ 2: $ J $ 2 vandret efter kolonne for at få et nøjagtigt match til datoen, der er foretaget i cellen navngivet date_lookup (celle B11, i dette tilfælde). Argumentet return_array for denne anden, indlejrede XLOOKUP-funktion er $ B $ 3: $ J $ 6, celleområdet for alle produktionsværdier i tabellen.

Den måde, 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ådet D3: D6 i kolonnen Jun-19 (med værdierne: 438, 153, 306 og 779) som resultat. Dette resultat bliver igen return_array-argumentet for den oprindelige XLOOKUP-funktion, der udfører en lodret søgning efter række for et nøjagtigt match til den delnummerindgang, der er foretaget i celle B11 (kaldet part_lookup). Fordi denne del_lookup-celle i dette eksempel indeholder AB-102, returnerer formlen bare produktionsværdien Jun-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 temmelig let at bruge ny opslag-funktion, der ikke kun kan udføre de enkeltværdsopslag, der udføres af VLOOKUP- og HLOOKUP-funktionerne, men også tovejs-værktøjsopslag udført ved at kombinere INDEX og MATCH fungerer også.

* Desværre er XLOOKUP-funktionen ikke bagudkompatibel med tidligere versioner af Microsoft Excel, der kun understøtter VLOOKUP- og HLOOKUP-funktionerne eller kompatible med nuværende versioner, der endnu ikke inkluderer den som en af ​​deres opslagfunktioner, såsom Excel 2019 og Excel Online. Dette betyder, at hvis du deler en projektmappe, der indeholder XLOOKUP-formler med medarbejdere eller klienter, der bruger en version af Excel, der ikke inkluderer denne nye opslagningsfunktion, returnerer alle disse formler #NAME? fejlværdier, når de åbner sit regneark.