Excel 2020: Tolv fordele ved XLOOKUP - Excel-tip

Indholdsfortegnelse

Den nye XLOOKUP-funktion rulles ud til Office 365 fra november 2019. Joe McDaid fra Excel-teamet designet XLOOKUP til at forene de mennesker, der bruger VLOOKUP, og de mennesker, der bruger INDEX / MATCH. Dette afsnit diskuterer de 12 fordele ved XLOOKUP:

  1. Præcis match er standard.
  2. Heltalsbaseret tredje argument af VLOOKUP er nu en ordentlig reference.
  3. IFNA er indbygget til at håndtere manglende værdier.
  4. XLOOKUP har ikke noget problem at gå til venstre.
  5. Find næste-mindre eller næste-større match uden at sortere tabellen.
  6. XLOOKUP kan gøre HLOOKUP.
  7. Find den sidste kamp ved at søge fra bunden.
  8. Jokertegn er som standard "slukket", men du kan slå dem til igen.
  9. Returner alle 12 måneder i en enkelt formel.
  10. Kan returnere en cellehenvisning, hvis XLOOKUP er ved siden af ​​et kolon såsom XLOOKUP (); XLOOKUP ()
  11. Kan lave en tovejskamp som INDEX (, MATCH, MATCH) kan gøre.
  12. Kan opsummere alle opslag i en enkelt formel som LOOKUP kunne gøre.

Her er syntaksen: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).

XLOOKUP Fordel 1: Præcis match som standard

99% af mine VLOOKUP-formler slutter med, FALSE eller, 0 for at indikere et nøjagtigt match. Hvis du altid bruger den nøjagtige matchversion af VLOOKUP, kan du begynde at forlade match_mode fra din XLOOKUP-funktion.

I den følgende figur ser du W25-6 op fra celle A4. Du vil kigge efter den vare i L8: L35. Når den findes, skal du have den tilsvarende pris fra kolonne N. Der er ikke behov for at angive Falsk som match_mode, fordi XLOOKUP som standard er et nøjagtigt match.

XLOOKUP værdien i A4. Se i L8: L35. Returner den tilsvarende pris fra N8: N35.

XLOOKUP Fordel 2: Results_Array er en reference i stedet for et heltal

Tænk på den VLOOKUP-formel, som du ville bruge før XLOOKUP. Det tredje argument ville have været en 3 for at indikere, at du ønskede at returnere 3. kolonne. Der var altid en fare for, at en clueless kollega ville have indsat (eller slettet) en kolonne i din tabel. Med en ekstra kolonne i tabellen vil VLOOKUP, der havde returneret en pris, begynde at returnere en beskrivelse. Fordi XLOOKUP pegede på en cellehenvisning, omskrives formlen selv for fortsat at pege på den pris, der nu er i kolonne O.

Den gamle VLOOKUP ville mislykkes, hvis nogen indsatte en ny kolonne i opslagstabellen. XLOOKUP fortsætter med at arbejde.

XLOOKUP Fordel 3: IFNA er indbygget som et valgfrit argument

Den frygtede # N / A-fejl returneres, når din opslagsværdi ikke findes i tabellen. Tidligere, for at erstatte # N / A med noget andet, skal du bruge IFERROR eller IFNA viklet rundt om VLOOKUP.

Når en vare ikke findes, returnerer den # N / A fra VLOOKUP eller XLOOKUP …

Takket være et forslag fra Rico på min YouTube-kanal indarbejdede Excel-teamet et valgfrit fjerde argument for if_not_found. Hvis du vil erstatte disse # N / A-fejl med nul, skal du blot tilføje 0 som det fjerde argument. Eller du kan bruge noget tekst, såsom "Værdi ikke fundet".

Det valgfri fjerde argument i XLOOKUP er "hvis ikke fundet". Sæt et 0 eller "Ikke fundet" der.

XLOOKUP Fordel 4: Intet problem at se til venstre for nøglefeltet

VLOOKUP kan ikke se til venstre for nøglefeltet uden at ty til VLOOKUP (A4, VÆLG ((1,2), G7: G34, F7: F34), 2, Falsk). Med XLOOKUP er der ikke noget problem at have Results_array til venstre for Lookup_array.

Med XLOOKUP er der ikke noget problem at returnere kategorien fra kolonne F, mens man ser delnumre op i kolonne G. Dette var altid VLOOKUPs svaghed: den kunne ikke se til venstre.

XLOOKUP Fordel 5: Næste-mindre eller næste-større match uden sortering

VLOOKUP havde en mulighed for at se efter det nøjagtige match eller bare mindre værdi. Du kan enten forlade det fjerde argument ude af VLOOKUP eller ændre False til True. For at dette kunne fungere, skulle opslagstabellen sorteres i stigende rækkefølge.

Et eksempel på den omtrentlige kampversion af VLOOKUP. Ethvert salg fra 10 tusind til 20 tusind får en bonus på $ 12.

Men VLOOKUP havde ikke mulighed for at returnere det nøjagtige match eller den næste større vare. Til det var du nødt til at skifte til at bruge MATCH med en -1 som match_mode, og du var nødt til at være forsigtig med, at opslagstabellen blev sorteret faldende.

XLOOKUPs valgfri femte argument match_mode kan kun se efter det nøjagtige match, lig med eller bare mindre, lig med eller bare større. Bemærk, at værdierne i XLOOKUP giver mere mening end i MATCH:

  • -1 finder værdien lig med eller bare mindre
  • 0 find et nøjagtigt match
  • 1 finder værdien lig med eller bare større.

Men den mest fantastiske del: Opslagstabellen behøver ikke at blive sorteret, og enhver match_mode fungerer.

Nedenfor finder en match_mode på -1 det næste mindre element.

XLOOKUPs femte argument er Match_Mode. 0 er for nøjagtig matchning. Negativ en bruges til eksakt match eller næste mindre artikel. Positiv 1 er for nøjagtig matchning eller næste større vare. 2 er til Wildcard Match. For at spejle, hvad VLOOKUP med True i det fjerde argument ville gøre, skal du sætte en negativ som match_mode-argumentet i XLOOKUP.

Her finder en match_mode på 1, hvilket køretøj der er behov for afhængigt af antallet af personer i festen. Bemærk, at opslagstabellen ikke er sorteret efter passagerer, og køretøjets navn er til venstre for nøglen.

XLOOKUP kan gøre noget VLOOKUP kunne ikke gøre: find det nøjagtige match eller bare større. I dette tilfælde har et rejsefirma en liste over reservationer. Baseret på antallet af passagerer viser opslagstabellen, hvilket køretøj du har brug for til disse mennesker.

Tabellen siger:

  • Bus rummer 64 personer
  • Bilen rummer 4 personer
  • Motorcykel rummer 1 person
  • Tour Van har 12 personer
  • Van holder 6 personer.

Som en bonus sorteres dataene efter køretøj (i den gamle løsning, ved hjælp af MATCH, skal tabellen sorteres faldende efter kapacitet. Også: køretøjet er til venstre for kapacitet.

XLOOKUP Fordel 6: sidelæns XLOOKUP erstatter HLOOKUP

Lookup_array og results_array kan være vandret med XLOOKUP, hvilket gør det nemt at udskifte HLOOKUP.

Her er opslagstabellen vandret. Tidligere ville dette kræve HLOOKUP, men XLOOKUP kan håndtere et bord, der går sidelæns.

XLOOKUP Fordel 7: Søg fra bunden til den seneste kamp

Jeg har en gammel video på YouTube, der besvarer et spørgsmål fra en britisk hestefarm. De havde en flåde af køretøjer. Hver gang et køretøj kom ind til brændstof eller service, loggede de køretøj, dato og kilometertal i et regneark. De ønskede at finde den seneste kendte kilometertal for hvert køretøj. Mens Excel-2017-æraen MAXIFS muligvis løser dette i dag, var løsningen for mange år siden en uformel formel, der bruger LOOKUP og involveret opdeling med nul.

I dag giver XLOOKUPs valgfri sjette argument dig mulighed for at specificere, at søgningen skal starte fra bunden af ​​datasættet.

Find den sidste kamp på listen.

Bemærk

Selvom dette er en stor forbedring, lader det dig kun finde den første eller sidste kamp. Nogle mennesker håbede, at dette ville lade dig finde det andet eller tredje match, men det er ikke meningen med argumentet search_mode.

Advarsel

Ovenstående figur viser, at der er søgemetoder ved hjælp af den gamle binære søgning. Joe McDaid fraråder at bruge disse. For det første er den forbedrede opslagsalgoritme fra 2018 hurtig nok til, at der ikke er nogen væsentlig hastighedsfordel. For det andet risikerer du, at en clueless kollega sorterer opslagstabellen og introducerer forkerte svar.

XLOOKUP Fordel 8: Jokertegn er "slået fra" som standard

De fleste mennesker var ikke klar over, at VLOOKUP behandler stjerne, spørgsmålstegn og tilde som jokertegn som beskrevet i "# 51 Brug et jokertegn i VLOOKUP" på side 143. Med XLOOKUP er jokertegn som standard slået fra. Hvis du vil have XLOOKUP til at behandle disse tegn som et jokertegn, skal du bruge 2 som Match_Mode.

Meget få mennesker indså, at VLOOKUP behandler stjerner i opslagsværdien som et jokertegn. Som standard bruger XLOOKUP ikke wildcards, men du kan tvinge det til at opføre sig som VLOOKUP, hvis du bruger en Match Mode på 2: Wildcard Character Match.

XLOOKUP Fordel 9: Returner alle 12 måneder i en enkelt formel!

Dette er virkelig en fordel ved Dynamic Arrays, men det er min favoritårsag til at elske XLOOKUP. Når du skal vende tilbage alle 12 måneder i et opslag, vil en enkelt formel indtastet i B6 med en rektangulær return_array returnere flere resultater. Disse resultater vil smitte ind i tilstødende celler.

I nedenstående figur returnerer en enkelt formel indtastet i B7 alle 12 svar vist i B7: M7.

En enkelt XLOOKUP i kolonnen januar returnerer tal for januar til december. Dette gøres ved at angive en resultatsammensætning med 12 kolonner.

XLOOKUP Fordel 10: Kan returnere en cellereference, hvis den støder op til tyktarmen

Denne er kompleks, men smuk. Tidligere var der syv funktioner, der ville skifte fra at returnere en celleværdi til at returnere en cellehenvisning, hvis funktionen rørte ved et kolon. Se Brug A2: INDEX () som en ikke-flygtig OFFSET for et eksempel. XLOOKUP er ottefunktionen til at tilbyde denne adfærd ved at slutte sig til VÆLG, HVIS, IFS, INDEKS, INDIREKTE, FORSKIFTNING og SWITCH.

Overvej følgende figur. Nogen vælger Cherry i E4 og Fig i E5. Du vil have en formel, der opsummerer alt fra B6 til B9.

Figuren viser to XLOOKUP-formler i to celler. Den første returnerer 15 fra celle B6. Den anden kører 30 fra B9. Men så i en tredje celle er der en formel, der forbinder de to XLOOKUP-formler med et kolon og derefter indpakker den i en SUM-funktion. Resultatet er SUM for B6: B9, fordi XLOOKUP kan returnere en cellehenvisning, hvis funktionen vises ved siden af ​​en operator, såsom et kolon. For at bevise, at dette fungerer, viser de næste mange figurer denne formel i dialogboksen Evaluer formel.

I figuren ovenfor kan du se, at en XLOOKUP af E4 returnerer 15 fra celle B6. En XLOOKUP på E5 returnerer 30 fra B9. Men hvis du tager de to XLOOKUP-funktioner fra cellerne D9 og D10 og sætter dem sammen med et kolon imellem, ændres XLOOKUP's opførsel. I stedet for at returnere 15 returnerer den første XLOOKUP celleadressen B6!

For at bevise dette har jeg valgt D7 og brugt formler, evaluer formel. Efter at have trykket på Evaluer to gange er den næste del, der skal beregnes, XLOOKUP ("Cherry", A4: A29, B4: B29), som vist her.

Dette viser dialogboksen Evaluer formel lige før evaluering af den første XLOOKUP. Denne XLOOKUP vises lige før et kolon.

Tryk på Evaluer igen og forbløffende, XLOOKUP-formlen returnerer $ B $ 6 i stedet for de 15, der er gemt i B6. Dette sker, fordi der er et kolon umiddelbart efter denne XLOOKUP-formel.

Klik på Evaluer, og den første XLOOKUP returnerer $ B $ 6 i stedet for 15.

Tryk på Evaluer to gange mere, og den midlertidige formel vil være = SUM (B6: B9).

Efter evaluering af den anden XLOOKUP er interimsformlen = SUM (B6: B9).

Dette er fantastisk opførsel, som de fleste ikke kender til. Excel MVP Charles Williams fortæller mig, at det kan udløses med en af ​​disse tre operatører ved siden af ​​XLOOKUP:

  • Kolon
  • Rum (krydsoperatør)
  • Komma (EU-operatør)

XLOOKUP Fordel 11: Tovejs match som INDEX (, MATCH, MATCH)

For alle mine VLOOKUP-venner har INDEX / MATCH-folk ventet på at se, om XLOOKUP kan håndtere en tovejskamp. Den store nyhed: det kan gøre det. Den dårlige nyhed: Metoden er lidt anderledes end INDEX / MATCH fans ville forvente. Det kan være lidt over hovedet på dem. Men jeg er sikker på, at de kan komme rundt til denne metode.

For en tovejskamp skal du finde ud af, hvilken række der indeholder kontonummer A621 vist i J3. Så XLOOKUP starter let nok: = XLOOKUP (J3, A5: A15. Men så skal du give et resultat_array. Du kan bruge det samme trick som i XLOOKUP Fordel 9: Returner alle 12 måneder i en enkelt formel ovenfor, men brug den til at returnere en lodret vektor. En indre XLOOKUP søger efter J4-måneden i månedens overskrifter i B4: G4. Return_array er angivet som B5: G15. Resultatet er, at den indre XLOOKUP returnerer en matrix som den, der er vist i I10 : I20 nedenfor. Da A621 findes i den femte celle i look_array og 104 findes i den femte celle i results_array, får du det rigtige svar fra formlen. Nedenfor viser J6 den gamle vej. J7 returnerer den nye vej.

XLookup J3 i listen over konti i A5: A15. For resultatarrayet skal du bruge XLOOKUP (J4, B4: G4, B5: G15). I denne formel er B4: G4 en liste over måneder. B5: G15 er det rektangulære array af værdier for alle konti i alle måneder. I en anden celle viser bare den indvendige XLOOKUP, hvordan den returnerer hele værdikolonnen for maj.

XLOOKUP Fordel 12: Summ alle opslagsværdier i en enkelt formel

Den gamle LOOKUP-funktion tilbød to mærkelige tricks. For det første, hvis du prøver at finde ud af det samlede beløb for bonusomkostninger, der skal påløbe, kan du bede LOOKUP om at slå alle værdierne op i en enkelt formel. I billedet nedenfor viser LOOKUP (C4: C14 11 opslag. Men LOOKUP-funktionen tilbød ikke et nøjagtigt match og krævede, at opslagstabellen skulle sorteres.

Opslag 13-værdier og summer dem. Dette fungerede tidligere med LOOKUP, men det fungerer også med XLOOKUP. Angiv alle opslagsværdierne C4: C14 som det første argument. Indpak XLOOKUP i en SUM-funktion.

Med XLOOKUP kan du angive et interval som lookup_value, og XLOOKUP returnerer alle svarene. Fordelen er, at XLOOKUP kan lave nøjagtige matches.

Tricket med at bruge LOOKUP til at opsummere alle opslagsresultaterne fungerede kun med den omtrentlige matchversion af Opslag. Her foretager XLOOKUP et nøjagtigt match på alle navnene i L4: L14 og får i alt alle resultaterne.

Bonus Tip: Hvad med en Twisted LOOKUP?

Excel MVP Mike Girvin viser ofte et trick af LOOKUP-funktionen, hvor Lookup_Vector er lodret og Result_Vector er vandret. XLOOKUP understøtter ikke dette trick. Men hvis du snyder lidt og indpakker results_array i TRANSPOSE-funktionen, kan du administrere et snoet opslag.

Her er opslagsarray lodret, og resultatarray er vandret. Den gamle LOOKUP-funktion kan håndtere dette, men for at gøre det med XLOOKUP skal du indpakke begge array i TRANSPOSE.

Interessante artikler...