Formula generică
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
rezumat
Cu seturi mari de date, VLOOKUP de potrivire exactă poate fi dureros de lent, dar puteți face ca VLOOKUP să se ușureze rapid folosind două VLOOKUPS, așa cum se explică mai jos.
Note:
- Dacă aveți un set mai mic de date, această abordare este exagerată. Folosiți-l numai cu seturi de date mari atunci când viteza contează cu adevărat.
- Trebuie să sortați datele după valoarea de căutare pentru ca acest truc să funcționeze.
- Acest exemplu folosește intervale denumite. Dacă nu doriți să utilizați intervale denumite, folosiți în schimb referințe absolute.
VLOOKUP cu potrivire exactă este lent
Când utilizați VLOOKUP în „modul de potrivire exactă” pe un set mare de date, acesta poate încetini timpul de calcul într-o foaie de lucru. Cu, să zicem, 50.000 de înregistrări sau 100.000 de înregistrări, calculul poate dura câteva minute.
Potrivirea exactă este setată furnizând FALS sau zero ca al patrulea argument:
=VLOOKUP(val,data,col,FALSE)
Motivul pentru care VLOOKUP în acest mod este lent este că trebuie să verifice fiecare înregistrare din setul de date până când se găsește o potrivire. Aceasta este uneori denumită căutare liniară.
VLOOKUP cu potrivire aproximativă este foarte rapid
În modul de potrivire aproximativă, VLOOKUP este extrem de rapid. Pentru a utiliza VLOOKUP cu potrivire aproximativă, trebuie să sortați datele după prima coloană (coloana de căutare), apoi să specificați TRUE pentru al 4-lea argument:
=VLOOKUP(val,data,col,TRUE)
(VLOOKUP implicit este adevărat, ceea ce este un implicit înfricoșător, dar asta este o altă poveste).
Cu seturi foarte mari de date, trecerea la VLOOKUP cu potrivire aproximativă poate însemna o creștere dramatică a vitezei.
Deci, nu-i așa, nu? Doar sortați datele, utilizați potrivirea aproximativă și ați terminat.
Nu atât de repede (heh).
Problema cu VLOOKUP în modul „potrivire aproximativă” este următoarea: VLOOKUP nu va afișa o eroare dacă valoarea de căutare nu există. Mai rău, rezultatul poate părea complet normal, chiar dacă este total greșit (vezi exemple). Nu ceva ce vrei să-i explici șefului tău.
Soluția este să folosiți VLOOKUP de două ori, ambele ori în modul de potrivire aproximativ:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Explicaţie
Prima instanță a VLOOKUP caută pur și simplu valoarea de căutare ( ID - ul din acest exemplu):
=IF(VLOOKUP(id,data,1,TRUE)=id
și returnează TRUE doar atunci când se găsește valoarea de căutare. În acest caz,
formula rulează din nou VLOOKUP în modul de potrivire aproximativ pentru a extrage o valoare din tabelul respectiv:
VLOOKUP(id,data,col,TRUE)
Nu există pericolul lipsei unei valori de căutare, deoarece prima parte a formulei a fost deja verificată pentru a vă asigura că există.
Dacă valoarea de căutare nu este găsită, porțiunea „valoare dacă FALS” a funcției IF rulează și puteți returna orice valoare doriți. În acest exemplu, folosim NA () returnăm o eroare # N / A, dar ați putea returna și un mesaj ca „Lipsește” sau „Nu a fost găsit”.
Amintiți-vă: trebuie să sortați datele după valoarea de căutare pentru ca acest truc să funcționeze.
