Съвет: Опитайте да използвате новата функция XLOOKUP – подобрена версия на VLOOKUP, която работи в произволна посока и връща точни съвпадения по подразбиране, което го прави по-лесен и по-удобен за използване от предшествието си.

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

В най-простия си вид функцията VLOOKUP казва:

=VLOOKUP(Какво искате да търсите, къде искате да го търсите, номера на колоната в диапазона, съдържащ стойността за връщане, връщане на приблизително или точно съвпадение – посочено като 1/TRUE или 0/FALSE).

Вашият браузър не поддържа видео.

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

Използване на функцията 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.

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

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

Диапазонът от клетки, в който VLOOKUP ще извърши търсене за справка_стойност и върнатата стойност. Можете да използвате наименуван диапазон или таблица и можете да използвате имена в аргумента вместо препратки към клетки. 

Първата колона в диапазона от клетки трябва да съдържа lookup_value. Диапазонът от клетки трябва също да включва върнатата стойност, която искате да намерите.

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

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

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

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

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

  • Приблизително съвпадение – 1/TRUE предполага, че първата колона в таблицата е сортирана по число или по азбучен ред и след това ще търси най-близката стойност. Това е методът по подразбиране, ако не зададете друг. Например =VLOOKUP(90;A1:B100;2;TRUE).

  • Точно съвпадение – 0/FALSE търси точната стойност в първата колона. Например =VLOOKUP("Smith";A1:B100;2;FALSE).

Как да започнете

Има четири сегмента от информация, които ще ви трябват, за да съставите синтаксиса на VLOOKUP:

  1. Стойността, която искате да търсите, наричана също стойност на справка.

  2. Диапазонът, където се намира стойността на справка. Не забравяйте, че стойността на справка винаги трябва да бъде в първата колона в диапазона, за да може VLOOKUP да работи правилно. Например ако стойността на справка е в клетка C2, тогава диапазонът ви трябва да започва с C.

  3. Номерът на колона в диапазона, който съдържа върнатата стойност. Ако например зададете B2:D11 като диапазон, трябва да преброите B като първа колона, C като втора и т.н.

  4. По желание можете да зададете TRUE, ако искате приблизително съвпадение, или FALSE, ако искате точно съвпадение на върнатата стойност. Ако не зададете нищо, стойността по подразбиране винаги ще бъде TRUE, или приблизително съвпадение.

Сега съберете заедно всичко по-горе, както следва:

=VLOOKUP(справочна стойност, диапазон, съдържащ справочна стойност, номер на колона в диапазона, съдържащ върнатата стойност, Приблизително съвпадение (TRUE) или Точно съвпадение (FALSE)).

Примери

Ето няколко примера за VLOOKUP:

Пример 1

=VLOOKUP (B3;B2:E7;2;FALSE)

VLOOKUP търси Fontana в първата колона (колона B) в table_array B2:E7 и връща Olivier от втората колона (колона C) на table_array.  False връща точно съвпадение.

Пример 2

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

VLOOKUP търси точно съвпадение (FALSE) на последното име за 102 (lookup_value) във втората колона (колона B) в диапазона A2:C7 и връща Fontana.

Пример 3

=IF(VLOOKUP(103;A1:E7;2;FALSE)="Souse";"Намира се";"Не е намерен")

IF проверява дали VLOOKUP връща Sousa като последно име на correspoinding на 103 (lookup_value) в A1:E7 (table_array). Тъй като последното име, съответстващо на 103, е Leal, условието IF е невярно и не е намерено.

Пример 4

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

VLOOKUP търси датата на раждане на служителя, съответстваща на 109 (lookup_value) в диапазона A2:E7 (table_array), и връща 03.04.1955 г. След това YEARFRAC изважда тази дата на раждане от 2014/6/30 и връща стойност, която след това се преобразува от INY в цяло число 59.

Пример 5

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

IF проверява дали VLOOKUP връща стойност за фамилия от колона B за 105 (lookup_value). Ако VLOOKUP намери фамилия, тогава IF ще покаже последното име, в противен случай IF връща Служител, който не е намерен. ISNA гарантира, че ако VLOOKUP върне #N/A, грешката се замества от Служител, който не е намерен, а не #N/A.



В този пример върнатата стойност е Бърк, който е последното име, съответстващо на 105.

Можете да използвате VLOOKUP, за да комбинирате няколко таблици в една, стига една от таблиците да има общи полета с всички останали. Това може да бъде особено полезно, ако трябва да споделите работна книга с хора, които имат по-стари версии на Excel, които не поддържат функции за данни с множество таблици като източници на данни – чрез комбиниране на източниците в една таблица и промяна на източника на данни на функцията за данни в новата таблица, функцията за данни може да се използва в по-стари версии на Excel (при условие че самата функция за данни се поддържа от по-старата версия).

Работен лист с колони, които използват VLOOKUP за получаване на данни от други таблици

Тук колоните A-F и H имат стойности или формули, които използват само стойности в работния лист, а останалата част от колоните използват VLOOKUP и стойностите на колона A (клиентски код) и колона B (адвокат), за да получите данни от други таблици.

  1. Копирайте таблицата, която има често срещаните полета, в нов работен лист и й дайте име.

  2. Щракнете върху Инструменти > данни >релации, за да отворите диалоговия прозорец Управление на релации.

    Диалоговият прозорец Управление на релации
  3. За всяка релация в списъка обърнете внимание на следното:

    • Полето, което свързва таблиците (изброени в скоби в диалоговия прозорец). Това е lookup_value за вашата формула VLOOKUP.

    • Името на свързаната справочна таблица. Това е table_array във вашата формула VLOOKUP.

    • Полето (колоната) в таблицата "Свързани справки", което съдържа данните, които искате в новата колона. Тази информация не се показва в диалоговия прозорец Управление на релации – ще трябва да погледнете таблицата "Свързана справка", за да видите кое поле искате да извлечете. Искате да обърнете внимание на номера на колоната (A=1) – това е col_index_num във вашата формула.

  4. За да добавите поле към новата таблица, въведете формулата си VLOOKUP в първата празна колона, като използвате информацията, която сте събрали в стъпка 3.

    В нашия пример колона G използва "Адвокат" (lookup_value),за да получи данните за ставката на сметката от четвъртата колона (col_index_num = 4) от таблицата на работния лист "Адвокати", tblAttorneys (table_array), с формулата =VLOOKUP([@Attorney];tbl_Attorneys;4;FALSE).

    Формулата може също да използва препратка към клетка и препратка към диапазон. В нашия пример ще бъде =VLOOKUP(A2;'Адвокати'! A:D;4;FALSE).

  5. Продължете да добавяте полета, докато не имате всички полета, които ви трябват. Ако се опитвате да подготвите работна книга, съдържаща функции за данни, които използват няколко таблици, променете източника на данни на функцията за данни на новата таблица.

Проблем

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

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

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

#N/A в клетка

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

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

За повече информация за отстраняване на грешки тип #N/A във VLOOKUP вижте Как да коригирате грешка #N/A във функцията VLOOKUP.

#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! в клетка

Тази конкретна #SPILL! грешка обикновено означава, че формулата ви разчита на имплицитното сечение за стойността на справката и използва цяла колона като препратка. Например =VLOOKUP(A:A;A:C;2;FALSE). Можете да решите проблема, като закотвите справката за справкас оператора @ по този начин: =VLOOKUP( @A:A,A:C;2;FALSE). Като алтернатива можете да използвате традиционния метод VLOOKUP и да направите справка с една клетка вместо цяла колона: =VLOOKUP(A2;A:C;2;FALSE).

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

Защо

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

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

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

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

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

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

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

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

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

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

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

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

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

Имате нужда от още помощ?

Винаги можете да попитате експерт в техническата общност на excel или да получите поддръжка в Общността за отговори от.

Вж. също

Карта за бързи справки: обновяване на VLOOKUP
Карта за бързи справки: Съвети за отстраняване на неизправности на VLOOKUP
Как да коригирате #VALUE! грешка във функцията VLOOKUP
Как да коригирате #N/A грешка във функцията VLOOKUP
Общ преглед на формулите в Excel
Как да избегнете повредени формули
Откриване на грешки във формули
Excel функции (по азбучен ред)
Excel функции (по категория)
VLOOKUP (безплатна визуализация)

Нуждаете се от още помощ?

Разширете уменията си
Преглед на обучението
Получавайте първи новите функции
Присъединяване към Microsoft Office участници в Insider

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

Доколко сте доволни от качеството на езика?
Какво е повлияло на вашия потребителски опит?

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

×