Excel 2020: Dvanásť výhod XLOOKUPU - Tipy pre Excel

Obsah

Nová funkcia XLOOKUP sa do služieb Office 365 zavádza od novembra 2019. Joe McDaid z tímu Excel navrhol XLOOKUP na zjednotenie ľudí, ktorí používajú VLOOKUP, a ľudí, ktorí používajú INDEX / MATCH. V tejto časti sa bude diskutovať o 12 výhodách programu XLOOKUP:

  1. Predvolená je presná zhoda.
  2. Tretí argument založený na celých číslach programu VLOOKUP je teraz správnym odkazom.
  3. IFNA je zabudovaná na spracovanie chýbajúcich hodnôt.
  4. Program XLOOKUP nemá problém ísť doľava.
  5. Nájdite nasledujúcu menšiu alebo nasledujúcu väčšiu zhodu bez zoradenia tabuľky.
  6. XLOOKUP môže robiť HLOOKUP.
  7. Nájdite posledný zápas hľadaním zdola.
  8. Zástupné znaky sú predvolene vypnuté, môžete ich však znova zapnúť.
  9. Vrátiť všetkých 12 mesiacov v jednom vzorci.
  10. Môže vrátiť odkaz na bunku, ak je XLOOKUP vedľa dvojbodky, napríklad XLOOKUP (); XLOOKUP ()
  11. Môže robiť obojsmernú zhodu, ako to dokáže INDEX (, MATCH, MATCH).
  12. Dokáže zhrnúť všetky vyhľadávania do jedného vzorca, aký by mohol urobiť LOOKUP.

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

Výhoda XLOOKUP 1: Štandardná presná zhoda

99% mojich vzorcov VLOOKUP končí na, FALSE alebo, 0, čo naznačuje presnú zhodu. Ak vždy používate verziu VLOOKUP s presnou zhodou, môžete začať nechať match_mode vypnutú funkciu XLOOKUP.

Na nasledujúcom obrázku hľadáte W25-6 z bunky A4. Chcete hľadať túto položku v L8: L35. Keď sa nájde, chcete zodpovedajúcu cenu zo stĺpca N. Nie je potrebné uvádzať ako match_mode hodnotu False, pretože XLOOKUP má predvolenú presnú zhodu.

XLOOKUPUJTE hodnotu v A4. Pozrite sa na L8: L35. Vrátiť zodpovedajúcu cenu z N8: N35.

Výhoda XLOOKUPu 2: Pole Results_Array je odkazom namiesto celého čísla

Popremýšľajte, aký vzorec VLOOKUP by ste použili pred XLOOKUPOM. Tretím argumentom by bola číslica 3, ktorá naznačuje, že chcete vrátiť 3. stĺpec. Vždy hrozilo, že bezradný spolupracovník vloží (alebo vymaže) stĺpec do vašej tabuľky. S extra stĺpcom v tabuľke by VLOOKUP, ktorý vracal cenu, začne vracať popis. Pretože XLOOKUP ukazoval na odkaz na bunku, vzorec sa prepisuje tak, aby ukazoval na cenu, ktorá je teraz v stĺpci O.

Starý VLOOKUP by zlyhal, keby niekto vložil nový stĺpec do vyhľadávacej tabuľky. XLOOKUP stále funguje.

Výhoda XLOOKUPu 3: IFNA je zabudovaná ako voliteľný argument

Obávaná chyba # N / A sa vráti, keď sa vaša vyhľadávacia hodnota nenájde v tabuľke. Ak chcete v minulosti nahradiť # N / A niečím iným, museli by ste použiť IFERROR alebo IFNA omotané okolo VLOOKUP.

Ak sa položka nenájde, vráti # N / A z VLOOKUP alebo XLOOKUP …

Vďaka návrhu od Rica na mojom kanáli YouTube začlenil tím Excel voliteľný štvrtý argument pre if_not_found. Ak chcete nahradiť tieto chyby # N / A nulou, jednoducho pridajte 0 ako štvrtý argument. Môžete tiež použiť text, napríklad „Hodnota sa nenašla“.

Voliteľný štvrtý argument v XLOOKUP-e je „ak sa nenájde“. Vložte tam 0 alebo „Nenašlo sa“.

Výhoda XLOOKUP 4: Žiadny problém pozerať sa doľava od poľa kľúča

VLOOKUP nemôže pozerať doľava od poľa kľúča bez použitia možnosti VLOOKUP (A4, CHOOSE ((1,2), G7: G34, F7: F34), 2, False). Pri XLOOKUP nie je problém mať pole Results_array naľavo od poľa Lookup_array.

Pri XLOOKUP nie je problém vrátiť kategóriu zo stĺpca F pri vyhľadávaní čísel dielov v stĺpci G. To bola vždy slabosť VLOOKUPU: nemohol sa pozerať doľava.

Výhoda XLOOKUP 5: Nasledujúca menšia alebo ďalšia väčšia zhoda bez triedenia

VLOOKUP mal možnosť hľadať presnú zhodu alebo len menšiu hodnotu. Môžete vynechať štvrtý argument z VLOOKUP alebo zmeniť hodnotu False na True. Aby to fungovalo, musela byť vyhľadávacia tabuľka zoradená vzostupne.

Príklad verzie programu VLOOKUP na približnú zhodu. Akýkoľvek predaj od 10 do 20 tisíc dostane bonus 12 dolárov.

Ale VLOOKUP nemal schopnosť vrátiť presnú zhodu alebo ďalšiu väčšiu položku. Za týmto účelom ste museli prejsť na použitie MATCH s -1 ako match_mode a museli ste dávať pozor, aby bola vyhľadávacia tabuľka zoradená zostupne.

Voliteľný piaty argument XLOOKUP match_mode môže hľadať iba presnú zhodu, rovnú alebo len menšiu, rovnú alebo len väčšiu. Všimnite si, že hodnoty v XLOOKUPe dávajú väčší zmysel ako v MATCH:

  • -1 nájde hodnotu rovnakú alebo len menšiu
  • 0 nájsť presnú zhodu
  • 1 nájde hodnotu rovnakú alebo len väčšiu.

Ale najúžasnejšia časť: vyhľadávacia tabuľka sa nemusí triediť a akýkoľvek match_mode bude fungovať.

Ďalej, match_mode -1 nájdite nasledujúcu menšiu položku.

Piaty argument aplikácie XLOOKUP je Match_Mode. 0 je pre presnú zhodu. Negatívny sa používa pre presnú zhodu alebo nasledujúcu menšiu položku. Kladné 1 je pre presnú zhodu alebo ďalšiu väčšiu položku. 2 slúži na zápas so zástupnými znakmi. Ak chcete zrkadliť, čo by urobila funkcia VLOOKUP s True vo štvrtom argumente, vložte záporný argument ako argument match_mode do XLOOKUP.

Tu match_mode 1 nájde, ktoré vozidlo je potrebné v závislosti od počtu ľudí na večierku. Upozorňujeme, že vyhľadávacia tabuľka nie je zoradená podľa cestujúcich a názov vozidla je naľavo od kľúča.

XLOOKUP dokáže niečo, čo VLOOKUP nedokáže: nájsť presnú zhodu alebo len väčšiu. V takom prípade má cestovná spoločnosť zoznam rezervácií. Na základe počtu cestujúcich vyhľadávacia tabuľka zobrazuje, aké vozidlo pre týchto ľudí potrebujete.

V tabuľke sa uvádza:

  • Autobus pojme 64 osôb
  • Auto pojme 4 osoby
  • Motorka má 1 osobu
  • Tour Van pojme 12 osôb
  • Van má 6 osôb.

Ako bonus sú dáta zoradené podľa vozidla (v starom riešení, pri použití MATCH, by sa tabuľka musela triediť zostupne podľa kapacity. Tiež: Vozidlo je naľavo od kapacity.

Výhoda XLOOKUP 6: Boční XLOOKUP nahradzuje HLOOKUP

Pole lookup_array a results_array môžu byť s XLOOKUPOM vodorovné, čo uľahčuje nahradenie HLOOKUPU.

Tu je vyhľadávacia tabuľka vodorovná. V minulosti by to vyžadovalo HLOOKUP, ale XLOOKUP si poradí s tabuľkou, ktorá ide bokom.

Výhoda XLOOKUP 7: Vyhľadajte poslednú zhodu zdola

Mám staré video na YouTube, ktoré odpovedá na otázku z britskej konskej farmy. Mali flotilu vozidiel. Zakaždým, keď vozidlo prišlo kvôli palivu alebo servisu, zaevidovali vozidlo, dátum a počet najazdených kilometrov do tabuľky. Chceli nájsť najnovšie známe najazdené kilometre pre každé vozidlo. Kým MAXIFS z éry Excel-2017 to dnes môže vyriešiť, riešením pred mnohými rokmi bol tajomný vzorec využívajúci LOOKUP a zahŕňajúci delenie nulou.

Dnes je voliteľný šiesty argument XLOOKUPU, ktorý vám umožňuje určiť, že hľadanie by sa malo začať od spodnej časti množiny údajov.

Nájdite poslednú zhodu v zozname.

Poznámka

Aj keď ide o veľké zlepšenie, umožní vám nájsť iba prvý alebo posledný zápas. Niektorí dúfali, že vám to umožní nájsť druhú alebo tretiu zhodu, čo však nie je zámerom argumentu search_mode.

Pozor

Obrázok vyššie ukazuje, že existujú režimy vyhľadávania, ktoré používajú staré binárne vyhľadávanie. Joe McDaid neodporúča ich používať. Po prvé, vylepšený vyhľadávací algoritmus z roku 2018 je dostatočne rýchly, takže neprináša žiadne významné výhody v rýchlosti. Po druhé, riskujete, že bezradný spolupracovník zoradí vyhľadávaciu tabuľku a zavedie nesprávne odpovede.

Výhoda XLOOKUP 8: Zástupné znaky sú predvolene „vypnuté“

Väčšina ľudí si neuvedomila, že VLOOKUP zaobchádza s hviezdičkou, otáznikom a vlnovkou ako so zástupnými znakmi, ako je to popísané v časti „# 51 Použite zástupný znak vo VLOOKUP“ na strane 143. V prípade XLOOKUP sú zástupné znaky predvolene vypnuté. Ak chcete, aby XLOOKUP považoval tieto znaky za zástupné znaky, použite 2 ako Match_Mode.

Len veľmi málo ľudí si uvedomilo, že VLOOKUP zaobchádza s hviezdičkami vo vyhľadávacej hodnote ako so zástupnými znakmi. V predvolenom nastavení XLOOKUP nepoužíva zástupné znaky, ale môžete ho donútiť, aby sa správal ako VLOOKUP, ak použijete režim zhody 2: Zástupný znak.

Výhoda XLOOKUP 9: Vrátenie všetkých 12 mesiacov v jedinom vzorci!

Toto je skutočne výhoda dynamických polí, ale je to môj obľúbený dôvod, prečo milovať XLOOKUP. Ak musíte pri vyhľadávaní vrátiť všetkých 12 mesiacov, jeden vzorec zadaný v B6 s obdĺžnikovým return_array vráti viac výsledkov. Tieto výsledky sa rozlejú do susedných buniek.

Na nasledujúcom obrázku jediný vzorec zadaný v B7 vráti všetkých 12 odpovedí zobrazených v B7: M7.

Jeden XLOOKUP v stĺpci január vráti čísla za január až december. To sa deje tak, že zadáte pole results_array s 12 stĺpcami.

Výhoda XLOOKUP 10: Môže vrátiť referenciu na bunku, ak susedí s dvojbodkou

Tento je zložitý, ale krásny. V minulosti existovalo sedem funkcií, ktoré by sa zmenili z vrátenia hodnoty bunky na vrátenie odkazu na bunku, ak by sa funkcia dotýkala dvojbodky. Napríklad viď Použitie A2: INDEX () ako neprchavého OFSETU. XLOOKUP je funkcia osmičiek, ktorá ponúka toto správanie a spája sa s CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET a SWITCH.

Zvážte nasledujúci obrázok. Niekto vyberie Cherry v E4 a Fig v E5. Chcete vzorec, ktorý zhrnie všetko od B6 po B9.

Na obrázku sú dva vzorce XLOOKUP v dvoch bunkách. Prvý vráti 15 z bunky B6. Druhý retrunuje 30 z B9. Ale potom v tretej bunke existuje vzorec, ktorý spojí dva vzorce XLOOKUP s dvojbodkou a potom ho zabalí do funkcie SUMA. Výsledkom je SUM B6: B9, pretože XLOOKUP môže vrátiť odkaz na bunku, ak sa funkcia objaví vedľa operátora, ako je dvojbodka. Aby sme dokázali, že to funguje, na nasledujúcich niekoľkých obrázkoch sa tento vzorec zobrazí v dialógovom okne Vyhodnotiť vzorec.

Na obrázku vyššie vidíte, že XLOOKUP E4 vráti 15 z bunky B6. XLOOKUP E5 vráti 30 z B9. Ak však vezmete dve funkcie XLOOKUP z buniek D9 a D10 a spojíte ich s dvojbodkou medzi nimi, správanie XLOOKUPu sa zmení. Namiesto vrátenia 15 prvý XLOOKUP vráti adresu bunky B6!

Aby som to dokázal, vybral som si D7 a používam vzorce, vyhodnotiť vzorec. Po dvojitom stlačení tlačidla Vyhodnotiť je ďalšou časťou na výpočet XLOOKUP („Čerešňa“, A4: A29, B4: B29), ako je to znázornené tu.

Zobrazí sa dialógové okno Vyhodnotiť vzorec tesne pred vyhodnotením prvého XLOOKUPU. Tento XLOOKUP sa zobrazí tesne pred dvojbodkou.

Opäť a s úžasom stlačte Vyhodnotiť, vzorec XLOOKUP vráti 15 B $ 6 namiesto 15 uložených v B6. Stáva sa to preto, že za týmto vzorcom XLOOKUPU bezprostredne nasleduje dvojbodka.

Kliknite na tlačidlo Vyhodnotiť a prvý XLOOKUP vráti 15 B $ 6 $.

Stlačte Vyhodnotiť ešte dvakrát a dočasný vzorec bude = SUMA (B6: B9).

Po vyhodnotení druhého XLOOKUPU je priebežný vzorec = SUM (B6: B9).

Je to úžasné správanie, o ktorom väčšina ľudí nevie. Excel MVP Charles Williams mi hovorí, že ho možno spustiť ktorýmkoľvek z týchto troch operátorov vedľa XLOOKUP:

  • Dvojbodka
  • Vesmír (operátor križovatky)
  • Čiarka (operátor Únie)

Výhoda XLOOKUPu 11: Obojsmerná zhoda ako INDEX (, ZÁPAS, ZÁPAS)

Na všetkých mojich priateľov VLOOKUP čakali INDEX / MATCH ľudia, či XLOOKUP zvládne obojstranný zápas. Skvelá správa: dokáže to. Zlá správa: metodika je trochu iná, ako by fanúšikovia INDEX / MATCH očakávali. Možno by im to bolo trochu nad hlavu. Som si však istý, že k tejto metóde môžu dospieť.

Pri obojsmernej hre chcete zistiť, ktorý riadok obsahuje číslo účtu A621 zobrazené v J3. Takže XLOOKUP začína dosť ľahko: = XLOOKUP (J3, A5: A15. Potom však musíte poskytnúť pole results_array. Môžete použiť rovnaký trik ako v prípade XLOOKUP Výhoda 9: Vrátiť všetkých 12 mesiacov v jedinom vzorci vyššie, ale použite ho na vrátenie zvislého vektora. Vnútorný XLOOKUP hľadá mesiac J4 v nadpisoch mesiacov v B4: G4. Return_array je určený ako B5: G15. Výsledkom je, že vnútorný XLOOKUP vráti pole ako je to zobrazené v I10. : Nižšie I20. Pretože A621 sa nachádza v piatej bunke vyhľadávacieho poľa a 104 sa nachádza v piatej bunke výsledkového poľa, dostanete správnu odpoveď zo vzorca. Nižšie J6 ukazuje starý spôsob. J7 vracia nový spôsob.

XLookup J3 v zozname účtov v A5: A15. Pre pole výsledkov použite XLOOKUP (J4, B4: G4, B5: G15). V tomto vzorci je B4: G4 zoznam mesiacov. B5: G15 je obdĺžnikové pole hodnôt pre všetky účty za všetky mesiace. V inej bunke iba vnútorný XLOOKUP ukazuje, ako vráti celý stĺpec hodnôt za máj.

Výhoda XLOOKUP 12: Sčítajte všetky vyhľadávané hodnoty do jedného vzorca

Starodávna funkcia LOOKUP ponúkla dva zvláštne triky. Najskôr, ak sa snažíte zistiť celkovú sumu bonusových výdavkov, ktoré sa môžu akumulovať, môžete požiadať LOOKUP o vyhľadanie všetkých hodnôt v jednom vzorci. Na nasledujúcom obrázku LOOKUP (C4: C14) robí 11 vyhľadávaní. Funkcia LOOKUP však neponúkala presnú zhodu a vyžadovala zoradenie vyhľadávacej tabuľky.

Vyhľadajte 13 hodnôt a spočítajte ich. Toto predtým fungovalo s VYHĽADÁVANÍM, ale funguje to aj s XLOOKUPOM. Ako prvý argument zadajte všetky vyhľadávané hodnoty C4: C14. Zabalte XLOOKUP do funkcie SUMA.

Pomocou XLOOKUP môžete určiť rozsah, pretože lookup_value a XLOOKUP vráti všetky odpovede. Výhodou je, že program XLOOKUP dokáže robiť presné zhody.

Trik, ktorý spočíva v použití funkcie VYHĽADÁVANIE na zhrnutie všetkých výsledkov vyhľadávania, fungoval iba s verziou vyhľadávania v približnej zhode. Tu XLOOKUP robí presnú zhodu so všetkými menami v L4: L14 a získava celkovo všetky výsledky.

Bonusový tip: A čo Twisted LOOKUP?

Excel MVP Mike Girvin často ukazuje trik funkcie LOOKUP, keď je Lookup_Vector vertikálny a Result_Vector horizontálny. XLOOKUP tento trik natívne nepodporí. Ale ak trochu podvádzate a zabalíte výsledný súbor do funkcie TRANSPOSE, môžete spravovať skrútené vyhľadávanie.

Tu je vyhľadávacie pole vertikálne a pole výsledkov horizontálne. Stará funkcia LOOKUP to zvládne, ale na to, aby ste to mohli urobiť s XLOOKUP, musíte oba pole zabaliť do TRANSPOSE.

Zaujímavé články...