VLOOKUP функция

Използвайте VLOOKUP, една от функциите за търсене и препратки, когато е необходимо да намерите неща в таблица или диапазон по ред. Например търсите фамилното име на служителка по нейния номер на служител или искате да намерите телефонния й номер, като търсите по фамилно име (точно като телефонен указател).

Тайната на VLOOKUP е да организирате данните си така, че стойността, по която търсите (фамилното име на служителката) да е отляво на върнатата стойност, която искате да намерите (телефонния номер на служителката).

Синтаксис

VLOOKUP(справка_стойност; таблица_масив; кол_индекс_ном; [диапазон_справка])

Например:

  • =VLOOKUP(105;A2:C7;2,TRUE)

  • =VLOOKUP("Тодоров";B2:E7;2;FALSE)

Име на аргумент

Описание

справка_стойност    (задължителен)

Стойността, по която търсите. Стойността, по която искате да търсите, трябва да бъде в първата колона на диапазона от клетки, които задавате в таблица_масив.

Например ако таблица_масив обхваща клетки B2:D7, справка_стойност трябва да е в колона B. Вижте графиката по-долу. Справка_стойност може да бъде стойност или препратка към клетка.

таблица_масив    (задължителен)

Диапазонът от клетки, в който VLOOKUP ще извърши търсене за справка_стойност и върнатата стойност.

Първата колона в диапазона от клетки трябва да съдържа справка_стойност (например фамилното име на изображението по-долу.) Диапазонът от клетки също така трябва да включва върнатата стойност (например собствено име на изображението по-долу), която искате да намерите.

Научете как се избират диапазони в работен лист.

кол_индекс_ном    (задължителен)

Номерът на колоната (започвайки от 1 за най лявата колона от таблица_масив), която съдържа върнатата стойност.

диапазон_справка    (незадължителен)

Логическа стойност, указваща дали VLOOKUP да търси точно или приблизително съвпадение:

  • TRUE приема, че първата колона в таблицата е сортирана по числов или азбучен ред, и затова ще извърши търсене за най-близката стойност. Това е методът по подразбиране, ако не зададете друг.

  • FALSE търси точната стойност в първата колона.

Следващата картина ви показва как да настроите своя работен лист с =VLOOKUP("Akers",B2:D5,2,FALSE) така, че да върне Богдана.

Пример за стойност и масив, необходими за създаване на формула VLOOKUP в Excel

Примери

За да използвате тези примери в Excel, копирайте данните в таблицата по-долу и ги поставете в клетка A1 на нов работен лист.

ИД

Фамилно име

Собствено име

Длъжност

Рождена дата

101

Думанова

Милена

Продавач

8.12.1968

102

Тодоров

Теодор

Ръководител продажби

19.02.1952

103

Христова

Христина

Продавач

30.08.1963

104

Христов

Пламен

Продавач

19.09.1958

105

Белишки

Костадин

Мениджър продажби

4.03.1955

106

Банков

Мартин

Продавач

2.07.1963

Формула

Описание

=VLOOKUP("Тодоров";B2:E7;2;FALSE)

Търси стойността Тодоров в първата колона (колона B) на таблица_масив B2:E7 и връща стойността Теодор, намерена във втората колона (колона C) на таблица_масив. FALSE за диапазон_справка връща точно съвпадение.

=VLOOKUP(102;A2:C7;2;FALSE)

Търси точно съвпадение на фамилното име за справка_стойност102 в колона A. Връща Тодоров. Ако справка_стойност е 105, връща Белишки.

=IF(VLOOKUP(103;A1:E7;2;FALSE)="Банков";"Намерен/а";"Не е намерен/а")

Проверява дали фамилното име на служител с ИД 103 е Банков. Тъй като 103 е всъщност Христова, резултатът е Не е намерен/а. Ако промените "Банков" на "Христова" във формулата, резултатът е Намерен/а.

=INT(YEARFRAC(DATE(2014;6;30);, VLOOKUP(105;A2:E7;5; FALSE); 1))

За финансова година 2014 търси възрастта на служител, чийто ИД е 105. Използва функцията YEARFRAC за изваждане на рождената дата от крайната дата на финансовата година и показва резултата 59 като цяло число с помощта на функцията INT.

=IF(ISNA(VLOOKUP(105;A2:E7;2;FALSE)) = TRUE; "Служителят не е открит"; VLOOKUP(105;A2:E7;2;FALSE))

Ако има служител с ИД 105, показва фамилното му име, което е Белишки. В противен случай показва съобщението Служителят не е открит. Функцията ISNA (вижте IS функции) връща стойност TRUE, когато VLOOKUP върне стойност на грешка #N/A.

=VLOOKUP(104;A2:E7;3;FALSE) & " " & VLOOKUP(104;A2:E7;2;FALSE) & " е " & VLOOKUP(104;A2:E7;4;FALSE)

За служителя с ИД 104съединява (съчетава) стойностите на трите клетки в едно изречение Пламен Христов е продавач.

Често срещани проблеми

Проблем

Защо е възникнала грешка

Върната е грешна стойност

Ако аргументът диапазон_справка е TRUE или е пропуснат, първата колона трябва да се сортира по азбучен или числов ред. Ако първата колона не е сортирана, върнатата стойност може да е нещо, което не очаквате. Или сортирайте първата колона, или използвайте "FALSE" за точно съвпадение.

#N/A в клетка

  • Ако диапазон_справка е TRUE и ако стойността в справка_стойност е по-малка от най-малката стойност в първата колона на таблица_масив, ще получите стойността за грешка #N/A.

  • Ако диапазон_справка е FALSE, стойността за грешка #N/A означава, че не е намерено точното число.

Научете повече за грешките в работни листове, като #N/A, #REF и др..

#REF! в клетка

Ако кол_индекс_ном е по-голямо от броя на колоните в таблица_масив, ще получите стойността за грешка #REF!.

#VALUE! в клетка

Ако таблица_масив е по-малко от 1, ще получите стойността за грешка #VALUE!.

#NAME? в клетка

Стойността за грешка #NAME? обикновено означава, че липсват кавички във формулата. За да търсите името на човек, трябва да използвате кавички около името във формулата. Например въведете името като "Тодоров" в =VLOOKUP("Тодоров";B2:E7;2;FALSE).

Най-добри практики

Направете следното

Защо

Използвайте абсолютни препратки за диапазон_справка

Използването на абсолютни препратки ви позволява да попълвате надолу формула, така че винаги да търси в един и същ диапазон за справка.

Научете как да използвате абсолютни препратки към клетките.

Не съхранявайте стойности, които са число или дата, като текст.

При търсене на стойности, които са число или дата, се уверете, че данните в първата колона на таблица_масив не се съхраняват като текстови стойности. В противен случай VLOOKUP може да върне неправилна или неочаквана стойност.

Сортиране на първата колона

Сортирайте първата колона на таблица_масив, преди да използвате VLOOKUP, когато диапазон_справка е TRUE.

Използване на заместващи символи

Ако диапазон_справка е FALSE и справка_стойност е текст, можете да използвате заместващи символи – въпросителен знак (?) и звездичка (*) – в справка_стойност. Въпросителният знак съответства на единичен знак. Звездичката съответства на произволна поредица от знаци. Ако искате да намерите самия въпросителен знак или звездичка, напишете тилда (~) преди знака.

Например =VLOOKUP("Тодоро?";B2:E7;2;FALSE) ще търси всички екземпляри на Тодоров, като последната буква може да е различна.

Уверете се, че вашите данни не съдържат грешни знаци.

При търсене на текстови стойности в първата колона проверете дали данните в нея нямат начални интервали, крайни интервали, несъгласувана употреба на прави (' или ") или типографски (‘ или “) кавички или знаци, които не могат да се отпечатат. В тези случаи VLOOKUP може да върне неочаквана стойност.

За да получите точни резултати, опитайте да използвате функцията CLEAN или функцията TRIM, за да премахнете крайните интервали след стойностите на таблицата в клетка.

Свързани теми

Отнася се за: Excel Starter, Excel for Mac 2011, Excel 2016 for Mac, Excel 2010, Excel 2013, Excel 2007, Excel Online



Беше ли полезна тази информация?

Да Не

Как можем да подобрим това?

255 оставащи знаци

За защита на поверителността на данните ви не включвайте информация за връзка във вашата обратна връзка. Прегледайте нашите правила за поверителност.

Благодарим ви за обратната връзка!

Ресурси за поддръжка

Промяна на езика