Excel 2020: dvanaest blagodati XLOOKUP-a - Excel savjeti

Nova funkcija XLOOKUP uvodi se u Office 365 od studenog 2019. Joe McDaid iz Excelovog tima dizajnirao je XLOOKUP kako bi objedinio ljude koji koriste VLOOKUP i ljude koji koriste INDEX / MATCH. Ovaj odjeljak raspravljat će o 12 prednosti XLOOKUP-a:

  1. Točno podudaranje je zadana vrijednost.
  2. Treći argument VLOOKUP-a zasnovan na cijelim brojevima sada je odgovarajuća referenca.
  3. IFNA je ugrađena za rukovanje vrijednostima koje nedostaju.
  4. XLOOKUP nema problema s odlaskom na lijevu stranu.
  5. Pronađite sljedeće manje ili sljedeće veće podudaranje bez sortiranja tablice.
  6. XLOOKUP može raditi HLOOKUP.
  7. Pronađite posljednje podudaranje pretraživanjem od dna.
  8. Zamjenski znakovi su po defaultu "isključeni", ali možete ih ponovno uključiti.
  9. Vratite svih 12 mjeseci u jednoj formuli.
  10. Može vratiti referencu na ćeliju ako je XLOOKUP pored dvotačke kao što je XLOOKUP (); XLOOKUP ()
  11. Može napraviti dvosmjernu utakmicu kao što to može INDEX (, MATCH, MATCH).
  12. Može sažeti sva pretraživanja u jednoj formuli kao što je LOOKUP.

Evo sintakse: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).

XLOOKUP Pogodnost 1: Točno podudaranje prema zadanim postavkama

99% mojih formula VLOOKUP-a završava se na, FALSE ili, 0 da označi točno podudaranje. Ako uvijek koristite verziju VLOOKUP-a s točnim podudaranjem, možete započeti isključivanje match_mode funkcije XLOOKUP.

Na sljedećoj slici tražite W25-6 iz ćelije A4. Taj predmet želite potražiti u L8: L35. Kada se pronađe, želite odgovarajuću cijenu iz stupca N. Nije potrebno navesti False kao match_mode jer XLOOKUP zadane vrijednosti točno odgovaraju.

XLOOKUP vrijednost u A4. Pogledajte L8: L35. Vratite odgovarajuću cijenu iz N8: N35.

Prednost XLOOKUP-a 2: Rezultat_Array je referenca umjesto cijelog broja

Razmislite o formuli VLOOKUP koju biste koristili prije XLOOKUP-a. Treći argument bi bio oznaka 3 koja ukazuje na to da želite vratiti 3. stupac. Uvijek je postojala opasnost da bi nesretni suradnik umetnuo (ili izbrisao) stupac u vašu tablicu. S dodatnim stupcem u tablici, VLOOKUP koji je vraćao cijenu počeo bi vraćati opis. Budući da je XLOOKUP ukazivao na referencu ćelije, formula se prepisuje kako bi nastavila ukazivati ​​na cijenu koja je sada u stupcu O.

Stari VLOOKUP ne bi uspio ako bi netko umetnuo novi stupac u tablicu pretraživanja. XLOOKUP nastavlja raditi.

XLOOKUP Prednost 3: IFNA je ugrađen kao neobavezni argument

Užasna pogreška # N / A vraća se kada vaša vrijednost pretraživanja nije pronađena u tablici. U prošlosti, da biste zamijenili # N / A nečim drugim, morali biste koristiti IFERROR ili IFNA omotane oko VLOOKUP-a.

Kada stavka nije pronađena, vraća # N / A s VLOOKUP-a ili XLOOKUP-a …

Zahvaljujući prijedlogu Rica na mom YouTube kanalu, tim programa Excel ugradio je neobavezni četvrti argument za if_not_found. Ako želite zamijeniti greške # N / A s nulom, jednostavno dodajte 0 kao četvrti argument. Ili biste mogli upotrijebiti neki tekst, poput "Vrijednost nije pronađena".

Neobvezni četvrti argument u XLOOKUP-u je "ako nije pronađen". Tamo stavite 0 ili "Nije pronađeno".

XLOOKUP Prednost 4: Nema problema s lijevom stranom ključnog polja

VLOOKUP ne može gledati lijevo od polja ključa bez pribjegavanja VLOOKUP-u (A4, CHOOSE ((1,2), G7: G34, F7: F34), 2, False). S XLOOKUP-om nema problema s nizom rezultata_lijevo od niza Lookup_računa.

S XLOOKUP-om nema problema s vraćanjem kategorije iz stupca F dok se traže brojevi dijelova u stupcu G. To je uvijek bila slabost VLOOKUP-a: nije mogao gledati lijevo.

XLOOKUP Prednost 5: Sljedeće manje ili sljedeće veće podudaranje bez sortiranja

VLOOKUP je imao mogućnost traženja točnog podudaranja ili samo manje vrijednosti. Četvrti argument možete izostaviti iz VLOOKUP-a ili False promijeniti u True. Da bi ovo funkcioniralo, tablicu pretraživanja trebalo je sortirati u uzlaznom slijedu.

Primjer verzije VLOOKUP-a s približnim podudaranjem. Svaka prodaja od 10 tisuća do 20 tisuća dobiva bonus od 12 dolara.

Ali VLOOKUP nije imao mogućnost vratiti točno podudaranje ili sljedeću veću stavku. Za to ste morali prijeći na upotrebu MATCH-a s -1 kao match_mode i morali ste biti oprezni da bi se tablica pretraživanja sortirala silazno.

Neobvezni peti argument XLOOKUP-a match_mode može tražiti samo točno podudaranje, jednako ili samo manje, jednako ili samo veće. Imajte na umu da vrijednosti u XLOOKUP-u imaju više smisla nego u MATCH-u:

  • -1 pronalazi vrijednost jednaku ili samo manju
  • 0 pronaći točno podudaranje
  • 1 pronalazi vrijednost jednaku ili samo veću.

Ali, najnevjerojatniji dio: tablicu pretraživanja ne mora se sortirati i bilo koji match_mode će raditi.

Ispod, match_mode od -1 pronađi sljedeću-manju stavku.

Peti argument XLOOKUP-a je Match_Mode. 0 je za točno podudaranje. Negativni se koristi za Točno podudaranje ili Sljedeći manji predmet. Pozitivna 1 odnosi se na točno podudaranje ili sljedeću veću stavku. 2 je za Wildcard Match. Da biste zrcalili što bi radio VLOOKUP s True u četvrtom argumentu, stavite negativni kao argument match_mode u XLOOKUP.

Ovdje, match_mode of 1, pronalazi koje je vozilo potrebno, ovisno o broju ljudi u stranci. Imajte na umu da se tablica pretraživanja ne razvrstava po putnicima, a naziv vozila nalazi se s lijeve strane tipke.

XLOOKUP može učiniti nešto što VLOOKUP nije mogao: pronaći točno podudaranje ili samo veće. U ovom slučaju, turistička tvrtka ima popis rezervacija. Na temelju broja putnika, tablica pretraživanja pokazuje koje vozilo trebate za te ljude.

Tablica kaže:

  • Autobus prima 64 osobe
  • Automobil ima 4 osobe
  • Motocikl ima 1 osobu
  • Tour Van ima 12 ljudi
  • Kombi ima 6 osoba.

Kao bonus, podaci se sortiraju prema vozilu (u starom rješenju, pomoću MATCH, tablica bi se trebala sortirati silazno prema kapacitetu. Također: Vozilo je lijevo od kapaciteta.

XLOOKUP Prednost 6: Bočno XLOOKUP zamjenjuje HLOOKUP

Lookup_array i results_array mogu biti vodoravni s XLOOKUP-om, što olakšava zamjenu HLOOKUP-a.

Ovdje je tablica pretraživanja vodoravna. U prošlosti bi to zahtijevalo HLOOKUP, ali XLOOKUP se može nositi sa stolom koji ide bočno.

XLOOKUP Benefit 7: Potražite najnovije podudaranje odozdo

Imam stari video na YouTubeu kako odgovaram na pitanje s britanske farme konja. Imali su vozni park. Svaki put kad bi vozilo ušlo po gorivo ili servis, oni su u proračunsku tablicu upisali vozilo, datum i kilometražu. Željeli su pronaći najnoviju poznatu kilometražu za svako vozilo. Iako bi MAXIF-ovi iz razdoblja Excel-2017 ovo mogli riješiti danas, rješenje prije mnogo godina bila je tajna formula koja je koristila LOOKUP i uključivala je dijeljenje s nulom.

Danas, neobavezni šesti argument XLOOKUP-a omogućuje vam da odredite da pretraživanje treba započeti s dna skupa podataka.

Pronađite posljednje podudaranje na popisu.

Bilješka

Iako je ovo veliko poboljšanje, omogućuje vam samo pronalazak prvog ili zadnjeg podudaranja. Neki su se ljudi nadali da će vam ovo omogućiti da pronađete drugo ili treće podudaranje, ali to nije namjera argumenta search_mode.

Oprez

Gornja slika pokazuje da postoje načini pretraživanja pomoću starog binarnog pretraživanja. Joe McDaid ne savjetuje upotrebu ovih. Prvo, poboljšani algoritam pretraživanja iz 2018. godine dovoljno je brz da nema značajne koristi od brzine. Drugo, riskirate da nesretni suradnik sortira tablicu za pretraživanje i uvede pogrešne odgovore.

Prednost XLOOKUP-a 8: Zamjenski znakovi su "isključeni" prema zadanim postavkama

Većina ljudi nije shvatila da VLOOKUP tretira zvjezdicu, upitnik i tildu kao zamjenske znakove kao što je opisano u "# 51 Upotreba zamjenskog znaka u VLOOKUP-u" na stranici 143. S XLOOKUP-om, zamjenski znakovi su isključeni prema zadanim postavkama. Ako želite da XLOOKUP tretira ove znakove kao zamjenski znak, upotrijebite 2 kao Match_Mode.

Vrlo je malo ljudi shvatilo da VLOOKUP zvjezdice u vrijednosti pretraživanja tretira kao zamjenski znak. Prema zadanim postavkama XLOOKUP ne koristi zamjenske znakove, ali možete ga prisiliti da se ponaša poput VLOOKUP-a ako koristite način podudaranja 2: Podudarnost znakova.

XLOOKUP Pogodnost 9: Vratite svih 12 mjeseci u jednu formulu!

Ovo je stvarno prednost Dynamic Arrays, ali to je moj omiljeni razlog da volim XLOOKUP. Kad u pretraživanju morate vratiti svih 12 mjeseci, jedna formula unesena u B6 s pravokutnim nizom return_array vratit će više rezultata. Ti će se rezultati preliti u susjedne stanice.

Na donjoj slici, jedna formula unesena u B7 vraća svih 12 odgovora prikazanih u B7: M7.

Jedan XLOOKUP u stupcu za siječanj vraća brojeve za razdoblje od siječnja do prosinca. To se postiže specificiranjem rezultata_ niz s 12 stupaca.

Prednost XLOOKUP-a 10: Može vratiti referencu na ćeliju ako je u blizini debelog crijeva

Ovaj je složen, ali lijep. U prošlosti je bilo sedam funkcija koje bi se promijenile od vraćanja vrijednosti stanice do vraćanja reference stanice ako funkcija dodiruje dvotočku. Primjer pogledajte Upotreba A2: INDEX () kao nehlapljivi pomak. XLOOKUP je osmica funkcija koja nudi ovo ponašanje, pridružujući se IZBORI, IF, IFS, INDEKS, INDIRECT, OFFSET i SWITCH.

Razmotrite sljedeću sliku. Netko odabere Cherry u E4 i Fig u E5. Želite formulu koja će zbrojiti sve od B6 do B9.

Na slici su prikazane dvije XLOOKUP formule u dvije ćelije. Prvi vraća 15 iz ćelije B6. Drugi ponavlja 30 iz B9. Ali onda u trećoj ćeliji postoji formula koja spaja dvije XLOOKUP formule dvotačkom i zatim umotava u funkciju SUM. Rezultat je ZBIR B6: B9, jer XLOOKUP može vratiti referencu na ćeliju ako se funkcija pojavi pored operatora kao što je dvotačka. Da bi dokazali da ovo funkcionira, sljedećih nekoliko slika prikazat će ovu formulu u dijaloškom okviru Procjena formule.

Na gornjoj slici možete vidjeti da će XLOOKUP E4 vratiti 15 iz ćelije B6. XLOOKUP E5 vratit će 30 s B9. Međutim, ako dvije XLOOKUP funkcije uzmete iz ćelija D9 i D10 i složite ih s dvotačkom, ponašanje XLOOKUP-a se mijenja. Umjesto da vrati 15, prvi XLOOKUP vraća adresu ćelije B6!

Da bih to dokazao, odabrao sam D7 i koristio Formule, procijeni formulu. Nakon dva puta pritiska na Evaluate, sljedeći dio koji se izračunava je XLOOKUP ("Trešnja", A4: A29, B4: B29), kao što je ovdje prikazano.

Ovo prikazuje dijalog Evaluate Formula neposredno prije procjene prvog XLOOKUP-a. Ovaj XLOOKUP pojavljuje se neposredno prije dvotočke.

Pritisnite Evaluate ponovno i nevjerojatno, formula XLOOKUP vraća $ B $ 6 umjesto 15 spremljenih u B6. To se događa jer odmah iza ove XLOOKUP formule postoji dvotočka.

Pritisnite Evaluate i prvi XLOOKUP vraća $ B $ 6 umjesto 15.

Pritisnite Evaluate još dva puta, a privremena formula bit će = SUM (B6: B9).

Nakon procjene drugog XLOOKUP-a, privremena formula je = SUM (B6: B9).

Ovo je nevjerojatno ponašanje za koje većina ljudi ne zna. Excel MVP Charles Williams kaže mi da ga se može pokrenuti bilo kojim od ova tri operatora uz XLOOKUP:

  • Debelo crijevo
  • Prostor (operater križanja)
  • Zarez (operater Unije)

XLOOKUP pogodnost 11: Dvosmjerna utakmica poput INDEKSA (, UTAKMICA, UTAKMICA)

Za sve moje prijatelje VLOOKUP-a, INDEX / MATCH ljudi su čekali da vide mogu li XLOOKUP podnijeti dvosmjernu utakmicu. Sjajne vijesti: može to učiniti. Loše vijesti: metodologija je malo drugačija nego što bi INDEX / MATCH fanovi očekivali. Moglo bi im biti malo iznad glave. Ali siguran sam da mogu doći do ove metode.

Za dvosmjernu utakmicu želite pronaći koji redak sadrži broj računa A621 prikazan u J3. Dakle, XLOOKUP započinje dovoljno jednostavno: = XLOOKUP (J3, A5: A15. Ali tada morate navesti result_array. Možete koristiti isti trik kao u XLOOKUP Benefit 9: Vratite svih 12 mjeseci u jednoj formuli gore, ali upotrijebite ga za vraćanje okomitog vektora. Unutarnji XLOOKUP traži mjesec J4 u naslovima mjeseca u B4: G4. Niz_vrata_određen je kao B5: G15. Rezultat je da unutarnji XLOOKUP vraća niz poput prikazanog u I10. : I20 u nastavku. Budući da se A621 nalazi u petoj ćeliji niza pretraživanja_, a 104 nalazi se u petoj ćeliji niza rezultata_, iz formule dobivate točan odgovor. Ispod J6 pokazuje stari način. J7 vraća novi način.

XLookup J3 na popisu računa u A5: A15. Za polje rezultata upotrijebite XLOOKUP (J4, B4: G4, B5: G15). U ovoj formuli, B4: G4 je popis mjeseci. B5: G15 je pravokutni niz vrijednosti za sve račune za sve mjesece. U drugoj ćeliji samo unutarnji XLOOKUP pokazuje kako vraća cijeli stupac vrijednosti za svibanj.

Prednost XLOOKUP-a 12: Zbroj svih vrijednosti pretraživanja u jednu formulu

Drevna funkcija LOOKUP nudila je dva čudna trika. Prvo, ako pokušavate dokučiti ukupan iznos dodatnih troškova koji se prikupljaju, možete zatražiti da LOOKUP traži sve vrijednosti u jednoj formuli. Na donjoj slici LOOKUP (C4: C14 vrši 11 pretraživanja. Ali funkcija LOOKUP nije ponudila točno podudaranje i tražila je sortiranje tablice pretraživanja.

Potražite 13 vrijednosti i zbrojite ih. Ovo je nekada radilo s LOOKUP-om, ali također radi i s XLOOKUP-om. Navedite sve vrijednosti pretraživanja C4: C14 kao prvi argument. Zamotajte XLOOKUP u funkciju SUM.

Pomoću XLOOKUP-a možete odrediti raspon jer će lookup_value i XLOOKUP vratiti sve odgovore. Prednost je u tome što XLOOKUP može točno podudarati.

Trik korištenja LOOKUP-a za zbrajanje svih rezultata pretraživanja uspio je samo s približnom verzijom pretraživanja koja se podudara. Ovdje XLOOKUP točno podudara sva imena u L4: L14 i dobiva ukupno sve rezultate.

Bonus savjet: Što je s Twisted LOOKUP-om?

Excel MVP Mike Girvin često pokazuje trik funkcije LOOKUP gdje je Lookup_Vector okomit, a Result_Vector vodoravni. XLOOKUP neće podržati ovaj trik. Ali, ako malo prevarite i zamotate result_array u funkciju TRANSPOSE, možete upravljati uvrnutim pretraživanjem.

Ovdje je polje pretraživanja okomito, a polje rezultata vodoravno. Stara funkcija LOOKUP to može podnijeti, ali da biste to učinili s XLOOKUP-om, morate oba polja zamotati u TRANSPOSE.

Zanimljivi članci...