Обща формула
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Обобщение
С големи набори от данни VLOOKUP с точно съвпадение може да бъде болезнено бавен, но можете да направите VLOOKUP светкавично бързо, като използвате две VLOOKUPS, както е обяснено по-долу.
Бележки:
- Ако имате по-малък набор от данни, този подход е прекален. Използвайте го само с големи набори от данни, когато скоростта наистина е от значение.
- Трябва да сортирате данните по справочна стойност, за да работи този трик.
- Този пример използва наименувани диапазони. Ако не искате да използвате именувани диапазони, вместо това използвайте абсолютни препратки.
VLOOKUP с точно съвпадение е бавен
Когато използвате VLOOKUP в „режим на точно съвпадение“ за голям набор от данни, това наистина може да забави времето за изчисление в работен лист. С, да речем, 50 000 записа или 100 000 записа, изчислението може да отнеме минути.
Точното съвпадение се задава чрез предоставяне на FALSE или нула като четвърти аргумент:
=VLOOKUP(val,data,col,FALSE)
Причината VLOOKUP в този режим да е бавен е, че трябва да проверява всеки отделен запис в набора от данни, докато се намери съвпадение. Това понякога се нарича линейно търсене.
Приблизителното съвпадение на VLOOKUP е много бързо
В режим на приблизително съвпадение VLOOKUP е изключително бърз. За да използвате VLOOKUP с приблизително съвпадение, трябва да сортирате данните си по първата колона (справочната колона), след което да посочите TRUE за четвъртия аргумент:
=VLOOKUP(val,data,col,TRUE)
(VLOOKUP по подразбиране е вярно, което е страшно по подразбиране, но това е друга история).
С много големи набори от данни, преминаването към приблизително съвпадение на VLOOKUP може да означава драстично увеличение на скоростта.
Така че, без умора, нали? Просто сортирайте данните, използвайте приблизително съвпадение и готово.
Не толкова бързо (хе).
Проблемът с VLOOKUP в режим „приблизително съвпадение“ е следният: VLOOKUP няма да покаже грешка, ако стойността за справка не съществува. По-лошото е, че резултатът може да изглежда напълно нормален, въпреки че е напълно грешен (вижте примерите). Не е нещо, което искате да обясните на шефа си.
Решението е да използвате VLOOKUP два пъти, и двата пъти в режим на приблизително съвпадение:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Обяснение
Първият екземпляр на VLOOKUP просто търси стойността за търсене ( идентификатора в този пример):
=IF(VLOOKUP(id,data,1,TRUE)=id
и връща TRUE само когато е намерена справочната стойност. В този случай
формулата стартира VLOOKUP отново в режим на приблизително съвпадение, за да извлече стойност от тази таблица:
VLOOKUP(id,data,col,TRUE)
Няма опасност от липсваща справочна стойност, тъй като първата част от формулата вече е проверена, за да се увери, че е там.
Ако търсената стойност не е намерена, частта IF стойност на функцията IF се изпълнява и можете да върнете всяка стойност, която искате. В този пример използваме NA (), връщаме грешка # N / A, но можете също да върнете съобщение като „Липсва“ или „Не е намерено“.
Запомнете: трябва да сортирате данните по справочна стойност, за да работи този трик.
