Excel 2020: Dvanajst prednosti XLOOKUP-a - Nasveti za Excel

Nova funkcija XLOOKUP se uvaja v Office 365 od novembra 2019. Joe McDaid iz Excelove ekipe je oblikoval XLOOKUP za poenotenje ljudi, ki uporabljajo VLOOKUP, in ljudi, ki uporabljajo INDEX / MATCH. Ta razdelek bo obravnaval 12 prednosti XLOOKUP-a:

  1. Natančno ujemanje je privzeto.
  2. Tretji argument VLOOKUP, ki temelji na celoštevilnih vrednostih, je zdaj ustrezna referenca.
  3. IFNA je vgrajena za obdelavo manjkajočih vrednosti.
  4. XLOOKUP nima težav z levo.
  5. Poiščite naslednje manjše ali naslednje večje ujemanje brez razvrščanja tabele.
  6. XLOOKUP lahko HLOOKUP.
  7. Poiščite zadnje ujemanje z iskanjem od spodaj.
  8. Nadomestni znaki so privzeto izključeni, vendar jih lahko znova vklopite.
  9. Vrnite vseh 12 mesecev v eni formuli.
  10. Lahko vrne referenco celice, če je XLOOKUP poleg dvopičja, kot je XLOOKUP (); XLOOKUP ()
  11. Lahko izvede dvosmerno ujemanje, kot to zmore INDEX (, MATCH, MATCH).
  12. Lahko sešteje vsa iskanja v eno formulo, kot je LOOKUP.

Tu je sintaksa: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).

Prednost XLOOKUP 1: Privzeto natančno ujemanje

99% mojih formul VLOOKUP se konča z, FALSE ali, 0, da označi natančno ujemanje. Če vedno uporabljate različico VLOOKUP z natančnim ujemanjem, lahko funkcijo match_mode izključite iz funkcije XLOOKUP.

Na naslednji sliki iščete W25-6 iz celice A4. Ta element želite poiskati v L8: L35. Ko jo najdete, iz stolpca N želite ustrezno ceno. Kot match_mode ni treba navajati False, ker XLOOKUP privzeto prikaže natančno ujemanje.

XLOOKUP vrednost v A4. Poglejte v L8: L35. Vrnite ustrezno ceno iz N8: N35.

Prednost XLOOKUP 2: matrika Results_Array je referenca namesto celega števila

Razmislite o formuli VLOOKUP, ki bi jo uporabili pred XLOOKUP. Tretji argument bi bil 3, ki bi označeval, da želite vrniti 3. stolpec. Vedno je obstajala nevarnost, da bi brezvestni sodelavec v tabelo vstavil (ali izbrisal) stolpec. Z dodatnim stolpcem v tabeli bi VLOOKUP, ki je vračal ceno, začel vrniti opis. Ker je XLOOKUP kazal na sklic na celico, se formula prepiše, da še naprej kaže na ceno, ki je zdaj v stolpcu O.

Stari VLOOKUP ne bi uspel, če bi nekdo v iskalno tabelo vstavil nov stolpec. XLOOKUP še naprej deluje.

Prednost XLOOKUP 3: IFNA je vgrajen kot neobvezen argument

Strašna napaka # N / A se vrne, ko vaše vrednosti iskanja v tabeli ni mogoče najti. V preteklosti bi morali za zamenjavo # N / A z nečim drugim uporabiti IFERROR ali IFNA, ovito okoli VLOOKUP-a.

Ko predmeta ni mogoče najti, vrne # N / A od VLOOKUP ali XLOOKUP…

Zahvaljujoč predlogu Rica na mojem kanalu YouTube je skupina Excel vključila neobvezni četrti argument za if_not_found. Če želite te napake # N / A nadomestiti z nič, preprosto dodajte 0 kot četrti argument. Lahko pa uporabite nekaj besedila, na primer "Vrednost ni najdena".

Neobvezni četrti argument v XLOOKUP je "če ga ne najdem". Tam dodajte 0 ali "Ni najdeno".

XLOOKUP Prednost 4: Brez težav gledamo levo od ključnega polja

VLOOKUP ne more gledati levo od ključnega polja, ne da bi se zatekel k VLOOKUP (A4, CHOOSE ((1,2), G7: G34, F7: F34), 2, False). Pri XLOOKUP-u ni težav, če bi levi od polja Lookup_array imeli matriko Results_arr.

Pri XLOOKUP-u ni težav pri vrnitvi kategorije iz stolpca F med iskanjem številk delov v stolpcu G. To je bila vedno slabost VLOOKUP-a: ni mogel gledati v levo.

Prednost XLOOKUP 5: Naslednje manjše ali naslednje večje ujemanje brez razvrščanja

VLOOKUP je imel možnost iskati natančno ujemanje ali samo manjšo vrednost. Četrti argument lahko pustite zunaj VLOOKUP ali pa False spremenite v True. Da bi to delovalo, je bilo treba iskalno tabelo razvrstiti po naraščajočem zaporedju.

Primer različice približnega ujemanja VLOOKUP. Vsaka prodaja od 10 tisoč do 20 tisoč dobi bonus v višini 12 dolarjev.

Toda VLOOKUP ni mogel vrniti natančnega ujemanja ali naslednjega večjega predmeta. Za to ste morali preklopiti na uporabo MATCH z -1 kot match_mode in paziti morate, da je iskalna tabela razvrščena padajoče.

Neobvezni peti argument XLOOKUP-a match_mode lahko išče samo natančno ujemanje, enako ali samo manjše, enako ali samo večje. Upoštevajte, da so vrednosti v XLOOKUP bolj smiselne kot v MATCH:

  • -1 najde vrednost, ki je enaka ali le manjša
  • 0 poiščite natančno ujemanje
  • 1 najde vrednost, ki je enaka ali samo večja.

Toda najbolj neverjeten del: iskalne tabele ni treba razvrščati in kateri koli match_mode bo deloval.

Spodaj match_mode -1 najde naslednji manjši element.

Peti argument XLOOKUP-a je Match_Mode. 0 je za natančno ujemanje. Negativni se uporablja za natančno ujemanje ali naslednji manjši element. Pozitivna 1 je za natančno ujemanje ali naslednji večji element. 2 je za Wildcard Match. Če želite zrcaliti, kaj bi počel VLOOKUP s True v četrtem argumentu, v XLOOKUP vstavite negativnega kot argument match_mode.

Tu, match_mode 1, poišče, katero vozilo je potrebno, odvisno od števila ljudi v zabavi. Iskalna tabela ni razvrščena po potnikih in ime vozila je na levi strani tipke.

XLOOKUP lahko naredi nekaj, česar VLOOKUP ni mogel: poiščite natančno ujemanje ali samo večje. V tem primeru ima turistično podjetje seznam rezervacij. Na podlagi števila potnikov pregledna tabela prikazuje, katero vozilo potrebujete za te ljudi.

V tabeli piše:

  • Avtobus sprejme 64 ljudi
  • Avto sprejme 4 osebe
  • Motorno kolo sprejme 1 osebo
  • Tour Van sprejme 12 ljudi
  • Kombi sprejme 6 ljudi.

Kot bonus so podatki razvrščeni po vozilu (v stari rešitvi z uporabo MATCH bi morali tabelo razvrstiti padajoče glede na zmogljivost. Tudi: vozilo je levo od kapacitete.

Prednost XLOOKUP 6: Bočni XLOOKUP nadomešča HLOOKUP

Lookup_array in results_array sta lahko vodoravna z XLOOKUP, kar olajša zamenjavo HLOOKUP.

Tu je iskalna tabela vodoravna. V preteklosti bi to zahtevalo HLOOKUP, XLOOKUP pa lahko obravnava mizo, ki gre vstran.

XLOOKUP Ugodnost 7: Poiščite zadnjo tekmo od spodaj

Na YouTubu imam star video, na katerem odgovarjam na vprašanje iz britanske konjske farme. Imeli so vozni park. Vsakokrat, ko je vozilo prišlo po gorivo ali servis, so vozilo, datum in kilometrino zabeležili v preglednico. Za vsako vozilo so želeli najti zadnjo znano kilometrino. Čeprav bi lahko MAXIFS iz obdobja Excel-2017 to rešil danes, je bila rešitev pred mnogimi leti skrivnostna formula, ki je uporabljala LOOKUP in je vključevala deljenje z ničlo.

Danes z neobveznim šestim argumentom XLOOKUP-a lahko določite, da se iskanje začne z dna nabora podatkov.

Poiščite zadnje ujemanje na seznamu.

Opomba

Čeprav je to velik napredek, lahko le poiščete prvo ali zadnjo tekmo. Nekateri so upali, da boste s tem lahko našli drugo ali tretjo ujemanje, vendar to ni namen argumenta search_mode.

Previdno

Zgornja slika prikazuje, da obstajajo načini iskanja, ki uporabljajo staro binarno iskanje. Joe McDaid odsvetuje njihovo uporabo. Prvič, izboljšani algoritem iskanja od leta 2018 je dovolj hiter, da ni večje koristi pri hitrosti. Drugič, tvegate, da bo brezvestni sodelavec razvrstil iskalno tabelo in uvedel napačne odgovore.

Prednost XLOOKUP 8: Nadomestni znaki so privzeto "izklopljeni"

Večina ljudi ni vedela, da VLOOKUP zvezdico, vprašaj in tildo obravnava kot nadomestne znake, kot je opisano v "# 51 Uporaba nadomestnega znaka v VLOOKUP" na strani 143. Pri XLOOKUP so nadomestni znaki privzeto izklopljeni. Če želite, da XLOOKUP te znake obravnava kot nadomestne znake, uporabite 2 kot Način ujemanja.

Le malo ljudi je ugotovilo, da VLOOKUP zvezdice v vrednosti iskanja obravnava kot nadomestni znak. XLOOKUP privzeto ne uporablja nadomestnih znakov, lahko pa ga prisilite, da se obnaša kot VLOOKUP, če uporabljate način ujemanja 2: Nadomestni znak znakov.

Prednost XLOOKUP 9: Vrnite vseh 12 mesecev v eni formuli!

To je resnično prednost Dynamic Arrays, vendar je moj najljubši razlog, da imam rad XLOOKUP. Ko morate v iskanju vrniti vseh 12 mesecev, bo ena sama formula, vnesena v B6 s pravokotnim nizom return_array, vrnila več rezultatov. Ti rezultati se bodo prelili v sosednje celice.

Na spodnji sliki ena sama formula, vnesena v B7, vrne vseh 12 odgovorov, prikazanih v B7: M7.

En sam XLOOKUP v januarskem stolpcu vrne številke od januarja do decembra. To se naredi tako, da se z 12 stolpci poda niz_rezultatov.

Prednost XLOOKUP 10: lahko vrne referenco celice, če je v bližini debelega črevesa

Ta je zapletena, a lepa. V preteklosti je bilo sedem funkcij, ki bi se spremenile od vračanja vrednosti celice do vračanja sklica na celico, če bi se funkcija dotikala dvopičja. Za primer glejte Uporaba A2: INDEX () kot nehlapni pomik. XLOOKUP je osmica funkcij, ki ponuja to vedenje in se pridruži CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET in SWITCH.

Upoštevajte naslednjo sliko. Nekdo izbere Cherry v E4 in Fig v E5. Želite formulo, ki bo seštevala vse od B6 do B9.

Slika prikazuje dve formuli XLOOKUP v dveh celicah. Prva vrne 15 iz celice B6. Druga ponovitev 30 iz B9. Potem pa v tretji celici obstaja formula, ki združi dve formuli XLOOKUP s dvopičjem in nato to zavije v funkcijo SUM. Rezultat je vsota B6: B9, ker lahko XLOOKUP vrne referenco celice, če se funkcija prikaže poleg operaterja, kot je dvopičje. Da bi dokazali, da to deluje, bo naslednjih nekaj slik to formulo prikazalo v pogovornem oknu Evaluate Formula.

Na zgornji sliki lahko vidite, da bo XLOOKUP E4 vrnil 15 iz celice B6. XLOOKUP E5 bo vrnil 30 iz B9. Če pa dve celici XLOOKUP vzamete iz celic D9 in D10 in ju sestavite z dvopičjem, se vedenje XLOOKUP spremeni. Namesto da vrne 15, prvi XLOOKUP vrne naslov celice B6!

Da bi to dokazal, sem izbral D7 in uporabil formule, oceni formulo. Po dvakratnem pritisku na Evaluate je naslednji del, ki ga je treba izračunati, XLOOKUP ("Češnja", A4: A29, B4: B29), kot je prikazano tukaj.

To prikaže pogovorno okno Evaluate Formula tik pred oceno prvega XLOOKUP-a. Ta XLOOKUP se pojavi tik pred dvopičjem.

Ponovno pritisnite Evaluate in presenetljivo formula XLOOKUP vrne 6 $ B $ 6 namesto 15, shranjenih v B6. To se zgodi, ker je debelo črevo takoj po tej formuli XLOOKUP.

Kliknite Evaluate in prvi XLOOKUP vrne 6 $ B $ 6 namesto 15.

Pritisnite Evaluate še dvakrat in vmesna formula bo = SUM (B6: B9).

Po oceni drugega XLOOKUP-a je vmesna formula = SUM (B6: B9).

To je neverjetno vedenje, o katerem večina ljudi ne ve. Excel MVP Charles Williams mi pravi, da ga je mogoče sprožiti s katerim koli od teh treh operaterjev poleg XLOOKUP-a:

  • Debelo črevo
  • Vesolje (operater križišča)
  • Vejica (operater Unije)

Prednost XLOOKUP 11: Dvosmerna tekma, kot je INDEX (, TEKMA, TEKMA)

Za vse moje prijatelje VLOOKUP so ljudje INDEX / MATCH čakali, ali XLOOKUP lahko obvlada dvosmerno tekmo. Odlična novica: zmore. Slabe novice: metodologija je nekoliko drugačna, kot bi pričakovali oboževalci INDEX / MATCH. Mogoče jim je malo čez glavo. Prepričan pa sem, da se lahko lotijo ​​te metode.

Za dvosmerno ujemanje želite najti, katera vrstica vsebuje številko računa A621, prikazano v J3. Torej, XLOOKUP se začne dovolj enostavno: = XLOOKUP (J3, A5: A15. Potem pa morate navesti result_array. Uporabite lahko isti trik kot v XLOOKUP Ugodnost 9: Vrnite vseh 12 mesecev zgoraj v eno samo formulo, vendar uporabite ga za vrnitev navpičnega vektorja. Notranji XLOOKUP išče mesec J4 v naslovih meseca v B4: G4. Matrika return_arry je podana kot B5: G15. Rezultat je, da notranji XLOOKUP vrne matriko, kot je prikazana v I10 : I20 spodaj. Ker je A621 najden v peti celici lookup_array in 104 je v peti celici result_array, dobite pravilen odgovor iz formule. Spodaj J6 prikazuje staro pot, J7 vrne novo pot.

XLookup J3 na seznamu računov v A5: A15. Za matriko rezultatov uporabite XLOOKUP (J4, B4: G4, B5: G15). V tej formuli je B4: G4 seznam mesecev. B5: G15 je pravokotna matrika vrednosti za vse račune za vse mesece. V drugi celici samo notranji XLOOKUP pokaže, kako vrne celoten stolpec vrednosti za maj.

Prednost XLOOKUP 12: seštejte vse iskalne vrednosti v eno formulo

Starodavna funkcija LOOKUP je ponujala dva čudna trika. Najprej, če poskušate ugotoviti skupni znesek nabranih stroškov, lahko LOOKUP poiščete, da poišče vse vrednosti v eni sami formuli. Na spodnji sliki LOOKUP (C4: C14 izvaja 11 iskanj. Vendar funkcija LOOKUP ni ponudila natančnega ujemanja in je zahtevala razvrščanje iskalne tabele.

Poiščite 13 vrednosti in jih seštejte. To je nekoč delovalo z LOOKUP, deluje pa tudi z XLOOKUP. Kot prvi argument podajte vse iskalne vrednosti C4: C14. Zavijte XLOOKUP v funkcijo SUM.

Z XLOOKUP lahko določite obseg, saj bo lookup_value in XLOOKUP vrnil vse odgovore. Prednost je, da lahko XLOOKUP natančno ujema.

Trik uporabe LOOKUP-a za seštevanje vseh rezultatov iskanja je deloval samo z približno različico Lookup-a z ujemanjem. Tu se XLOOKUP natančno ujema z vsemi imeni v L4: L14 in dobi skupaj vse rezultate.

Bonus nasvet: Kaj pa Twisted LOOKUP?

Excel MVP Mike Girvin pogosto pokaže trik funkcije LOOKUP, kjer je Lookup_Vector navpičen in Result_Vector vodoraven. XLOOKUP tega trika ne bo podprl. Ampak, če malo goljufate in rezultat_marijo zavijete v funkcijo TRANSPOSE, lahko upravljate zvito iskanje.

Tu je iskalno polje navpično, polje rezultatov pa vodoravno. Stara funkcija LOOKUP lahko to reši ,, toda če želite to narediti z XLOOKUP, morate eno matriko zaviti v TRANSPOSE.

Zanimive Članki...