Функцията VLOOKUP в Excel търси стойност в диапазон от клетки, след което връща стойност, която е в същия ред като стойността, която търсите.
VLOOKUP, което означава „Вертикално търсене“, е функция за търсене, която търси стойност в най-лявата колона (първата колона) на диапазона и връща паралелната стойност от колоната вдясно. Функцията VLOOKUP търси само стойността нагоре (отгоре надолу) в таблица, подредена вертикално.
Например, да кажем, че имаме инвентарен списък в работен лист с таблица, показваща имена на артикули, дата на покупка, количество и цена. След това бихме могли да използваме VLOOKUP в друг работен лист, за да извлечем количеството и цената за определено име на артикул от работния лист за инвентара.
Функцията VLOOKUP може да изглежда обезсърчителна в началото, но всъщност е доста лесна за използване, след като разберете как работи. Тук, в този урок, ще ви покажем как да използвате функцията VLOOKUP в Excel.
Синтаксис и аргументи на VLOOKUP
Ако ще използвате функцията VLOOKUP, трябва да знаете нейния синтаксис и аргументите.
Синтаксис на функцията VLOOKUP:
=VLOOKUP(търсенна_стойност,масив_таблица,номер_индекс_на_столче,[търсен_обхват])Тази функция се състои от 4 параметъра или аргумента:
- търсеща_стойност: Това определя стойността, която търсите в първата колона на дадения масив от таблица. Стойността за търсене винаги трябва да е в най-лявата (колоната на таблицата за търсене.
- таблица_масив: Това е таблицата (диапазон от клетки), в която искате да търсите стойност. Тази таблица (таблица за търсене) може да бъде в същия работен лист или в различен работен лист или дори в различна работна книга.
- номер_индекс_столба: Това определя номера на колоната на масива на таблицата, който има стойността, която искате да извлечете.
- [range_lookup]: Този параметър определя дали искате да извлечете точно или приблизително съвпадение. Или е TRUE или FALSE, въведете „FALSE“, ако искате точната стойност или въведете „TRUE“, ако сте ОК с приблизителната стойност.
Използване на функцията VLOOKUP в Excel
Нека разгледаме как да използваме VLOOKUP в Microsoft Excel.
Основен пример
За да използвате VLOOKUP, първо трябва да създадете своята база данни или таблица (вижте по-долу).
След това създайте таблица или диапазон от мястото, където искате да търсите, и извлечете стойностите от таблицата за търсене.
След това изберете клетката, в която искате извлечената стойност и въведете следната формула VLOOKUP. Например, искаме да потърсим телефонния номер на 'Ena', след което трябва да въведете стойността за търсене като B13, A2:E10 като масив на таблицата, 5 за номера на колоната на телефонния номер и FALSE, за да върнем точната стойност. След това натиснете „Enter“, за да завършите формулата.
=VLOOKUP(B13,A2:E10,5,FALSE)Не е необходимо да въвеждате ръчно диапазона на таблицата, можете просто да изберете диапазона или таблицата с помощта на мишката за аргумента table_array. И ще бъде добавен към аргумента автоматично.
Не забравяйте, че за да работи това, стойността за търсене трябва да е в най-лявата част на нашата таблица за търсене (A2:E10). Също така, Lookup_value не трябва непременно да бъде в колона А на работния лист, просто трябва да бъде най-лявата колона от диапазона, който искате да търсите.
Vlookup изглежда правилно
Функцията VLOOKUP може да гледа само отдясно на таблицата. Той търси стойност в първата колона на таблица или диапазон и извлича съответстващата стойност от колона вдясно.
Точно съвпадение
Функцията VLOOKUP на Excel има два метода за съпоставяне, те са: точен и приблизителен. Параметърът ‘range_lookup’ във функцията VLOOKUP определя какъв вид търсите, точен или приблизителен.
Ако въведете range_lookup като „FALSE“ или „0“, формулата търси стойност, която е точно равна на lookup_value (може да бъде число, текст или дата).
=VLOOKUP(A9,A2:D5,3,FALSE)
Ако в таблицата не бъде намерено точно съвпадение, тя ще върне грешка #N/A. Когато се опитахме да търсим „Япония“ и да върнем съответната стойност в колона 4, възниква грешката #N/A, тъй като в първата колона на таблицата няма „Япония“.
Можете да въведете число „0“ или „FALSE“ в последния аргумент. И двете означават едно и също нещо в Excel.
Приблизително съвпадение
Понякога не е задължително да се нуждаете от точно съвпадение, достатъчно е най-доброто съвпадение. В такива случаи можете да използвате режим на приблизително съвпадение. Задайте крайния аргумент на функцията на „TRUE“, за да намерите приблизително съвпадение. Стойността по подразбиране е TRUE, което означава, че ако не добавите последния аргумент, функцията ще използва приблизително съвпадение по подразбиране.
=VLOOKUP(B10,A2:B7,2,TRUE)В този пример не се нуждаем от точен резултат, за да намерим подходяща оценка. Всичко, от което се нуждаем, са оценките, за да бъдем в този диапазон.
Ако VLOOKUP намери точно съвпадение, тогава ще върне тази стойност. В горния пример, ако формулата не може да намери стойността за търсене 89 в първата колона, тогава тя ще върне следващата най-голяма стойност (80).
Първи мач
Ако най-лявата колона на таблицата съдържа дубликати, VLOOKUP ще намери и върне първото съвпадение.
Например, VLOOKUP е конфигуриран да намери фамилното име за първото име „Mia“. Тъй като има 2 записа с първото име „Mia“, така че функцията връща фамилното име за първия запис, „Bena“.
Съвпадение с заместващи символи
Функцията VLOOKUP ви позволява да намерите частично съвпадение на определена стойност с помощта на заместващи знаци. Ако искате да намерите стойност, която съдържа стойността за търсене на произволна позиция, добавете знак амперсанд (&), за да присъедините нашата стойност за търсене със заместващия знак (*). Използвайте знаците „$“, за да направите абсолютни препратки към клетките и добавете заместващ знак „*“ преди или след стойността за търсене.
В примера имаме само част от стойността за търсене (Vin) в клетка B13. Така че, за да извършите частично съвпадение на дадените знаци, конкатенирайте заместващ знак „*“ след препратката към клетката.
=VLOOKUP($B$13&"*",$A$2:$E$10,3,FALSE)
Множество претърсвания
Функцията VLOOKUP ви позволява да създадете динамично двупосочно търсене, съвпадащо както в редовете, така и в колоните. В следващия пример VLOOKUP е настроен да извършва търсене въз основа на име (Mayra) и град. Синтаксисът в B14 е:
=VLOOKUP(B13,A2:E10,СВЪВПАДАНЕ(A14,A1:E1,0),0)
Как да направите VLOOKUP от друг лист в Excel
Обикновено функцията VLOOKUP се използва за връщане на съвпадащи стойности от отделен работен лист и рядко се използва с данни в същия работен лист.
За да търсите от друг лист на Excel, но в същата работна книга, въведете името на листа преди table_array с удивителен знак (!).
Например, за да потърсите стойността на клетка A2 на работния лист „Продукти“ в диапазона A2:B8 в работния лист „ItemPrices“ и да върнете съответната стойност от колона B:
=VLOOKUP(A2,Цени на артикули!$A$2:$C$8,2,FALSE)Снимката по-долу показва таблица в работния лист „Цени на артикули“.
Когато въведем формулата VLOOKUP в колона C на работния лист „Продукти“, тя извлича съвпадащи данни от работния лист „ItemPrices“.
Как да направите VLOOKUP от друга работна книга в Excel
Можете също да потърсите стойността в съвсем различна работна книга. Ако искате да извършите VLOOKUP от друга работна книга, трябва да поставите името на работната книга в квадратни скоби, последвано от името на листа преди table_array с удивителен знак (!) (както е показано по-долу).
Например, използвайте тази формула, за да потърсите стойността на клетка A2 на различен работен лист от работния лист с име „ItemPrices“ в работната книга „Item.xlsx“:
=VLOOKUP(A2,[Item.xls]Цени на артикули!$A$2:$B$8,2,FALSE)Първо отворете и двете работни книги, след това започнете да въвеждате формулата в клетка C2 на работен лист (Продуктов работен лист) и когато стигнете до аргумента table_array, отидете на основната работна книга с данни (Item.xlsx) и изберете диапазона на таблицата. По този начин не е нужно да въвеждате ръчно името на работната книга и работния лист. Въведете останалите аргументи и натиснете клавиша „Enter“, за да завършите функцията.
Дори ако затворите работната книга, която съдържа таблицата за търсене, формулата VLOOKUP ще продължи да работи, но вече можете да видите пълния път на затворената работна книга, както е показано на следващата екранна снимка.
Използвайте функцията VLOOKUP от лентата на Excel
Ако не можете да запомните формули, винаги можете да получите достъп до функцията VLOOKUP от лентата на Excel. За да получите достъп до VLOOKUP, отидете на раздела „Формули“ в лентата на Excel и щракнете върху иконата „Търсене и справка“. След това изберете опцията „VLOOKUP“ в долната част на падащото меню.
След това въведете аргументи в диалоговия прозорец „Аргументи на функцията“. След това щракнете върху бутона „OK“.
В примера потърсихме първото име „Шерил“ в таблицата, за да върнем съответното му състояние в колона D.
Надяваме се, че сте научили как да използвате функцията VLOOKUP в Excel от тази статия. Ако искате да научите повече за това как да използвате Excel, вижте другите ни статии, свързани с Excel.