Excel

Rýchlejšie VLOOKUP s 2 VLOOKUPS

Faster Vlookup With 2 Vlookups

Excel vzorec: Rýchlejšie VLOOKUP s 2 VLOOKUPSGenerický vzorec | _+_ | Zhrnutie

Pri veľkých množinách údajov môže byť VLOOKUP s presnou zhodou bolestivo pomalý, ale zosvetlenie VLOOKUP môžete urýchliť pomocou dvoch VLOOKUPS, ako je vysvetlené nižšie.



Poznámky:

  1. Ak máte menší súbor údajov, tento prístup je zbytočný. Používajte ho iba s veľkými množinami údajov, keď sa rýchlosť skutočne počíta.
  2. Aby tento trik fungoval, musíte údaje zoradiť podľa hodnoty vyhľadávania.
  3. Tento príklad používa pomenované rozsahy . Ak nechcete používať pomenované rozsahy, použite namiesto nich absolútne odkazy.

VLOOKUP s presnou zhodou je pomalý

Keď použijete VLOOKUP v „režime presnej zhody“ na veľkej množine údajov, môže to skutočne spomaliť čas výpočtu v pracovnom hárku. Pri povedzme 50 000 záznamoch alebo 100 000 záznamoch môže výpočet trvať minúty.





Presná zhoda sa nastaví zadaním FALSE alebo nuly ako štvrtého argumentu:

= IF ( VLOOKUP (id,data,1,TRUE)=id,  VLOOKUP (id,data,col,TRUE),  NA ())

VLOOKUP v tomto režime je pomalý, pretože musí kontrolovať každý jeden záznam v súbore údajov, kým sa nenájde zhoda. Niekedy sa to nazýva lineárne vyhľadávanie.



VLOOKUP na približný zápas je veľmi rýchly

V režime približnej zhody je VLOOKUP mimoriadne rýchly. Ak chcete použiť funkciu VLOOKUP s približnou zhodou, musíte zoradiť údaje podľa prvého stĺpca (vyhľadávacieho stĺpca) a potom pre štvrtý argument zadajte hodnotu TRUE:

 
= VLOOKUP (val,data,col,FALSE)

(VLOOKUP je predvolene nastavený na hodnotu true, čo je a desivé predvolené , ale to je iný príbeh).

Pri veľmi veľkých množinách údajov môže zmena na VLOOKUP s približnou zhodou znamenať a dramatický zvýšenie rýchlosti.

prevádzať sekundy na minúty v programe Excel

Takže nič, nie? Stačí zoradiť údaje, použiť približnú zhodu a máte hotovo.

Nie tak rýchlo (heh).

Problém s VLOOKUP v režime „približnej zhody“ je tento: VLOOKUP nezobrazí chybu, ak vyhľadávacia hodnota neexistuje. Horšie je, že výsledok môže vyzerať úplne normálne, aj keď je úplne zlý ( pozri príklady ). Nie je to niečo, čo by ste svojmu šéfovi chceli vysvetliť.

Riešením je použiť VLOOKUP dvakrát, oba razy v režime približnej zhody:

ako vytvárať grafy v programe Excel 2013
 
= VLOOKUP (val,data,col,TRUE)
Vysvetlenie

Prvá inštancia VLOOKUP jednoducho vyhľadá vyhľadávaciu hodnotu ( id v tomto prípade):

 
= IF ( VLOOKUP (id,data,1,TRUE)=id,  VLOOKUP (id,data,col,TRUE),  NA ())

a vráti hodnotu TRUE iba vtedy, keď sa nájde hodnota vyhľadávania. V tom prípade,
vzorec znova spustí VLOOKUP v režime približnej zhody, aby získal hodnotu z tejto tabuľky:

 
= IF ( VLOOKUP (id,data,1,TRUE)=id

Chýba žiadna chýbajúca vyhľadávacia hodnota, pretože prvá časť vzorca už bola skontrolovaná, aby sa ubezpečil, že tam je.

Ak sa vyhľadávacia hodnota nenájde, spustí sa časť „hodnota, ak je FALSE“ funkcie IF a môžete vrátiť ľubovoľnú hodnotu, ktorá sa vám páči. V tomto prípade použijeme NA () vrátime chybu #N/A, ale môžete tiež vrátiť správu ako „Chýba“ alebo „Nenašlo sa“.

Aby tento trik fungoval, musíte údaje zoradiť podľa hodnoty vyhľadávania.

Autor Dave Bruns


^