Vispārēja formula
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Kopsavilkums
Izmantojot lielus datu kopas, precīzās atbilstības VLOOKUP var būt sāpīgi lēns, taču jūs varat ātri izgaismot VLOOKUP, izmantojot divus VLOOKUPS, kā paskaidrots tālāk.
Piezīmes:
- Ja jums ir mazāks datu kopums, šī pieeja ir pārspīlēta. Izmantojiet to tikai ar lielām datu kopām tikai tad, ja ātrums patiešām ir svarīgs.
- Lai šis triks darbotos, dati ir jāšķiro pēc uzmeklēšanas vērtības.
- Šajā piemērā tiek izmantoti nosauktie diapazoni. Ja nevēlaties izmantot nosauktos diapazonus, tā vietā izmantojiet absolūtās atsauces.
Precīzās spēles VLOOKUP ir lēns
Izmantojot VLOOKUP "precīzās atbilstības režīmā" lielam datu kopumam, tas var patiešām palēnināt aprēķinu laiku darblapā. Ar, teiksim, 50 000 vai 100 000 ierakstu, aprēķināšana var aizņemt minūtes.
Precīzā atbilstība tiek iestatīta, kā ceturto argumentu norādot FALSE vai zero:
=VLOOKUP(val,data,col,FALSE)
VLOOKUP šajā režīmā ir lēns tāpēc, ka tam jāpārbauda katrs atsevišķs datu kopas ieraksts, līdz tiek atrasta atbilstība. To dažreiz sauc par lineāru meklēšanu.
Aptuvenais spēles VLOOKUP ir ļoti ātrs
Aptuvenās spēles režīmā VLOOKUP ir ārkārtīgi ātrs. Lai izmantotu aptuveno atbilstības VLOOKUP, dati ir jāšķiro pēc pirmās kolonnas (uzmeklēšanas kolonna), pēc tam 4. argumentam jānorāda PATIESA:
=VLOOKUP(val,data,col,TRUE)
(VLOOKUP pēc noklusējuma ir taisnība, kas ir biedējošs noklusējums, bet tas ir cits stāsts).
Ar ļoti lieliem datu kopumiem pāreja uz aptuveno atbilstību VLOOKUP var nozīmēt dramatisku ātruma pieaugumu.
Tātad, bez prāta, vai ne? Vienkārši kārtojiet datus, izmantojiet aptuveno atbilstību, un viss ir pabeigts.
Ne tik ātri (heh).
VLOOKUP problēma "aptuvenās atbilstības" režīmā ir šāda: VLOOKUP neparādīs kļūdu, ja uzmeklēšanas vērtība nepastāv. Sliktāk, rezultāts var izskatīties pilnīgi normāls, kaut arī tas ir pilnīgi nepareizs (skat. Piemērus). Nevis kaut ko tādu, ko vēlaties paskaidrot priekšniekam.
Risinājums ir divreiz izmantot VLOOKUP, abas reizes aptuvenajā spēles režīmā:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Paskaidrojums
Pirmā VLOOKUP instance vienkārši uzmeklē uzmeklēšanas vērtību ( ID šajā piemērā):
=IF(VLOOKUP(id,data,1,TRUE)=id
un atgriež vērtību TRUE tikai tad, kad tiek atrasta uzmeklēšanas vērtība. Tādā gadījumā
formula atkal palaiž VLOOKUP aptuvenajā atbilstības režīmā, lai izgūtu vērtību no šīs tabulas:
VLOOKUP(id,data,col,TRUE)
Nav bīstama pazaudēta uzmeklēšanas vērtība, jo formulas pirmā daļa jau ir pārbaudīta, lai pārliecinātos, ka tā ir.
Ja uzmeklēšanas vērtība nav atrasta, darbojas funkcijas IF vērtība "vērtība, ja FALSE", un jūs varat atgriezt jebkuru vēlamo vērtību. Šajā piemērā mēs izmantojam NA (), mēs atgriežam kļūdu # N / A, bet jūs varat arī atgriezt ziņojumu, piemēram, "Trūkst" vai "Nav atrasts".
Atcerieties: lai šis triks darbotos, dati ir jāšķiro pēc uzmeklēšanas vērtības.
