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:
- Predvolená je presná zhoda.
- Tretí argument založený na celých číslach programu VLOOKUP je teraz správnym odkazom.
- IFNA je zabudovaná na spracovanie chýbajúcich hodnôt.
- Program XLOOKUP nemá problém ísť doľava.
- Nájdite nasledujúcu menšiu alebo nasledujúcu väčšiu zhodu bez zoradenia tabuľky.
- XLOOKUP môže robiť HLOOKUP.
- Nájdite posledný zápas hľadaním zdola.
- Zástupné znaky sú predvolene vypnuté, môžete ich však znova zapnúť.
- Vrátiť všetkých 12 mesiacov v jednom vzorci.
- Môže vrátiť odkaz na bunku, ak je XLOOKUP vedľa dvojbodky, napríklad XLOOKUP (); XLOOKUP ()
- Môže robiť obojsmernú zhodu, ako to dokáže INDEX (, MATCH, MATCH).
- 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.
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.
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.
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“.
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.
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.
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.
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.
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.
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.
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.
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.
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 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.
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.
Stlačte Vyhodnotiť ešte dvakrát a dočasný vzorec bude = SUMA (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.
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.
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.
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.