Obecný vzorec
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
souhrn
U velkých sad dat může být VLOOKUP s přesnou shodou bolestně pomalý, ale můžete rychle zesvětlit VLOOKUP pomocí dvou VLOOKUPS, jak je vysvětleno níže.
Poznámky:
- Pokud máte menší soubor dat, je tento přístup přehnaný. Používejte jej pouze u velkých souborů dat, když se skutečně počítá rychlost.
- Aby tento trik fungoval, musíte data seřadit podle vyhledávací hodnoty.
- Tento příklad používá pojmenované rozsahy. Pokud nechcete používat pojmenované rozsahy, použijte místo toho absolutní odkazy.
VLOOKUP s přesnou shodou je pomalý
Když použijete VLOOKUP v „režimu přesné shody“ na velkou sadu dat, může to opravdu zpomalit dobu výpočtu v listu. Řekněme, že 50 000 záznamů nebo 100 000 záznamů může výpočet trvat minuty.
Přesná shoda se nastaví zadáním FALSE nebo nula jako čtvrtý argument:
=VLOOKUP(val,data,col,FALSE)
Důvod, proč je VLOOKUP v tomto režimu pomalý, je ten, že musí kontrolovat každý jednotlivý záznam v datové sadě, dokud není nalezena shoda. Toto se někdy označuje jako lineární vyhledávání.
Přibližná shoda VLOOKUP je velmi rychlá
V režimu přibližné shody je VLOOKUP extrémně rychlý. Chcete-li použít VLOOKUP s přibližnou shodou, musíte seřadit data podle prvního sloupce (vyhledávací sloupec) a pro 4. argument zadat TRUE:
=VLOOKUP(val,data,col,TRUE)
(VLOOKUP má výchozí hodnotu true, což je strašidelné výchozí nastavení, ale to je jiný příběh).
U velmi velkých souborů dat může změna na VLOOKUP s přibližnou shodou znamenat dramatický nárůst rychlosti.
Takže ne, přemýšlejte, že? Stačí seřadit data, použít přibližnou shodu a hotovo.
Ne tak rychle (heh).
Problém s VLOOKUP v režimu „přibližné shody“ je tento: VLOOKUP nezobrazí chybu, pokud vyhledávací hodnota neexistuje. Horší je, že výsledek může vypadat úplně normálně, i když je zcela špatný (viz příklady). Ne něco, co bys chtěl vysvětlit svému šéfovi.
Řešením je použít VLOOKUP dvakrát, oba v režimu přibližné shody:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Vysvětlení
První instance VLOOKUP jednoduše vyhledá vyhledávací hodnotu ( ID v tomto příkladu):
=IF(VLOOKUP(id,data,1,TRUE)=id
a vrátí TRUE pouze v případě, že je nalezena vyhledávací hodnota. V takovém případě
vzorec spustí VLOOKUP znovu v režimu přibližné shody a načte hodnotu z této tabulky:
VLOOKUP(id,data,col,TRUE)
Neexistuje nebezpečí chybějící hodnoty vyhledávání, protože první část vzorce již byla zkontrolována, aby se ujistil, že je tam.
Pokud vyhledávací hodnota není nalezena, spustí se část funkce „value if FALSE“ funkce IF a můžete vrátit libovolnou hodnotu, která se vám líbí. V tomto příkladu použijeme NA () vrátíme chybu # N / A, ale můžete také vrátit zprávu jako „Chybějící“ nebo „Nebylo nalezeno“.
Pamatujte: abyste mohli tento trik fungovat, musíte data seřadit podle vyhledávací hodnoty.
