Съвет
Опитайте да използвате новата функция XLOOKUP – подобрена версия на VLOOKUP, която работи във всяка посока и връща точни съвпадения по подразбиране, което я прави по-лесна и по-удобна за използване от предшественика си.
Използвайте VLOOKUP, когато е необходимо да намерите неща в таблица или диапазон по ред. Например търсите цената на дадена автомобилна част по номера на част или намерете името на служител въз основа на неговия ИД на служител.
В най-простия си вид функцията VLOOKUP казва:
= VLOOKUP(това, което искате да търсите, къде искате да го търсите, номера на колоната в диапазона, съдържащ стойността за връщане, връща приблизително или точно съвпадение – посочено като 1/TRUE или 0/FALSE).
Съвет
- Тайната на VLOOKUP е да организирате данните си така, че стойността, по която търсите (плодове) да е отляво на върнатата стойност (количество), която искате да намерите.
- Ако сте абонат на Microsoft Copilot, Copilot може да улесни още повече вмъкването и използването на функции VLookup или XLookup. Вижте "Получаване на прозрения за данните с Copilot в Excel".
Технически подробности
Използване на функцията VLOOKUP за търсене на стойност в таблица.
Синтаксис
VLOOKUP(справка_стойност; таблица_масив; кол_индекс_ном; [диапазон_справка])
Например:
- =VLOOKUP(A2;A10:C20;2;TRUE)
- =VLOOKUP("Тодоров";B2:E7;2;FALSE)
- =VLOOKUP(A2;'Данни за клиентите'! A:F,3,FALSE)
| Име на аргумент | Описание |
|---|---|
| справка_стойност (задължителен) | Стойността, по която търсите. Стойността, по която искате да търсите, трябва да бъде в първата колона на диапазона от клетки, които задавате в аргумента table_array . Ако например таблица-масив обхваща клетки B2:D7, вашето lookup_value трябва да е в колона B. Lookup_value може да бъде стойност или препратка към клетка. |
| таблица_масив (задължителен) | Диапазонът от клетки, в който VLOOKUP ще извърши търсене за lookup_value и върнатата стойност. Можете да използвате наименуван диапазон или таблица и можете да използвате имена в аргумента вместо препратки към клетки. Първата колона в диапазона от клетки трябва да съдържа lookup_value. Диапазонът от клетки също така трябва да включва върнатата стойност, която искате да намерите. |
| кол_индекс_ном (задължителен) | Номерът на колоната (започвайки от 1 за най-лявата колона от table_array), която съдържа върнатата стойност. |
| диапазон_справка (незадължителен) | Логическа стойност, указваща дали VLOOKUP да търси приблизително, или точно съвпадение:
|
Как да започнете
Има четири сегмента от информация, които ще ви трябват, за да съставите синтаксиса на VLOOKUP:
- Стойността, която искате да търсите, наричана също стойност на справка.
- Диапазонът, където се намира стойността на справка. Не забравяйте, че стойността на справка винаги трябва да бъде в първата колона в диапазона, за да може VLOOKUP да работи правилно. Например ако стойността на справка е в клетка C2, тогава диапазонът ви трябва да започва с C.
- Номерът на колона в диапазона, който съдържа върнатата стойност. Например ако укажете B2:D11 като диапазон, трябва да броите B за първа колона, C за втора и т.н.
- По желание можете да зададете TRUE, ако искате приблизително съвпадение, или FALSE, ако искате точно съвпадение на върнатата стойност. Ако не зададете нищо, стойността по подразбиране винаги ще бъде TRUE, или приблизително съвпадение.
Сега съберете заедно всичко по-горе, както следва:
= VLOOKUP(стойност за справка; диапазон, съдържащ стойността за справка; номер на колона в диапазона, съдържащ стойността за справка; приблизително съвпадение (TRUE) или точно съвпадение (FALSE)).
Примери
Ето няколко примера за функцията VLOOKUP:
Пример 1
Пример 2
Пример 3
Пример 4
Пример 5
Често срещани проблеми
| Проблем | Защо е възникнала грешка |
|---|---|
| Върната е грешна стойност | Ако range_lookup е TRUE или е пропусната, първата колона трябва да се сортира по азбучен или числов ред. Ако първата колона не е сортирана, върнатата стойност може да е нещо, което не очаквате. Или сортирайте първата колона, или използвайте "FALSE" за точно съвпадение. |
| #N/A в клетка |
|
| #REF! в клетка | Ако col_index_num е по-голямо от броя на колоните в таблица_масив, ще получите #REF! стойността на грешка. За повече информация за отстраняване на #REF! във VLOOKUP вижте Как да коригирате грешка #REF!. |
| #VALUE! в клетка | Ако table_array е по-малко от 1, ще получите #VALUE! стойността на грешка. За повече информация за отстраняване на грешки тип #VALUE! във VLOOKUP вижте Как да коригирате грешка #VALUE! във функцията VLOOKUP. |
| #NAME? в клетка | На #NAME? обикновено означава, че липсват кавички във формулата. За да търсите името на човек, трябва да използвате кавички около името във формулата. Например въведете името като "Тодоров" в =VLOOKUP("Тодоров";B2:E7;2;FALSE). За повече информация вижте Коригиране на грешка #NAME!. |
| #ПРЕЛИВАНЕ! в клетка | Тази конкретна грешка #SPILL! обикновено означава, че формулата разчита на неявно сечение за търсената стойност и използва цяла колона като препратка. Например =VLOOKUP(A :A;A:C;2;FALSE). Можете да решите проблема, като свържете справочната препратка с оператора @ по следния начин: =VLOOKUP(@A:A;A:C;2;FALSE). Като алтернатива можете да използвате традиционния метод VLOOKUP и да препратите към една клетка, а не към цяла колона: =VLOOKUP(A2;A:C;2;FALSE). |
Най-добри практики
| Направете това | Защо |
|---|---|
| Използвайте абсолютни препратки за range_lookup | Използването на абсолютни препратки ви позволява да попълвате надолу формула, така че винаги да търси в един и същ диапазон за справка. Научете как да използвате абсолютни препратки към клетките. |
| Не съхранявайте стойности, които са число или дата, като текст. | При търсене на стойности, които са число или дата, се уверете, че данните в първата колона на table_array не се съхраняват като текстови стойности. В противен случай VLOOKUP може да върне неправилна или неочаквана стойност. |
| Сортиране на първата колона | Сортирайте първата колона на table_array , преди да използвате VLOOKUP, когато range_lookup е TRUE. |
| Използване на заместващи символи | Ако range_lookup е FALSE и lookup_value е текст, можете да използвате заместващи символи – въпросителен знак (?) и звездичка (*) – в lookup_value. Въпросителният знак съответства на произволен един знак. Звездичката съответства на произволна поредица от знаци. Ако искате да намерите самите въпросителен знак или звездичка, напишете тилда (~) пред знака. Например =VLOOKUP("Тодоро?";B2:E7;2;FALSE) ще търси всички екземпляри на Тодоров, като последната буква може да е различна. |
| Уверете се, че вашите данни не съдържат грешни знаци. | При търсене на текстови стойности в първата колона проверете дали данните в нея нямат начални интервали, крайни интервали, несъгласувана употреба на прави (' или ") или типографски (' или ") кавички или знаци, които не могат да се отпечатат. В тези случаи VLOOKUP може да върне неочаквана стойност. За да получите точни резултати, опитайте да използвате функцията CLEAN или функцията TRIM, за да премахнете крайните интервали след стойностите на таблицата в клетка. |
Имате нужда от още помощ?
Винаги можете да попитате експерт в техническата общност за Excel или да получите поддръжка в общностите.