Функция VLOOKUP в Excel – Ръководство за начинаещи: Синтаксис и примери

Съдържание

Функция VLOOKUP в Excel – общо описание и синтаксис
- Синтаксис на функцията VLOOKUP
VLOOKUP Примери
Точно или приблизително съвпадение във функцията VLOOKUP
- Пример 1: Намиране на точно съвпадение с VLOOKUP
- Пример 2: Използване на VLOOKUP за намиране на приблизително съвпадение
VLOOKUP в Excel – трябва да запомните това!

Днес започваме поредица от статии, описващи една от най-полезните функции на Excel − ВПР (VLOOKUP). Тази функция, в същото време, е една от най-сложните и най-слабо разбраните.

В този урок по ВПР Ще се опитам да изложа основите възможно най-просто, за да направя процеса на обучение възможно най-ясен за неопитни потребители. Освен това ще проучим няколко примера с формули на Excel, които ще демонстрират най-честите случаи на използване на функцията ВПР.

Общо описание и синтаксис
VLOOKUP Примери
- Как да използвате VLOOKUP за търсене в друг лист на Excel
- Търсете в друга работна книга с VLOOKUP
- Как да използвате именуван диапазон или таблица във формули с VLOOKUP
- Използване на заместващи символи във VLOOKUP формули
Точно или приблизително съвпадение във функцията VLOOKUP
VLOOKUP в Excel – трябва да запомните това!

Функция VLOOKUP в Excel – общо описание и синтаксис

И така, какво е то ВПР? Е, първо, това е функция на Excel. Какво прави тя? Той търси зададената от вас стойност и връща съответната стойност от другата колона. Технически казано, ВПР търси стойността в първата колона на дадения диапазон и връща резултата от друга колона в същия ред.

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

Първата буква в името на функцията ВПР (VLOOKUP) означава Ввертикално (Vвертикално). По него можете да различите ВПР от GPR (HLOOKUP), който търси стойност в горния ред на диапазон − Гхоризонтално (Hхоризонтално).

функция ВПР наличен в Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP и Excel 2000.

Синтаксис на функцията VLOOKUP

функция ВПР (VLOOKUP) има следния синтаксис:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Както можете да видите, функция ВПР в Microsoft Excel има 4 опции (или аргументи). Първите три са задължителни, последното е по желание.

търсена_стойност (lookup_value) – Стойността, която да търсите. Това може да бъде стойност (число, дата, текст) или препратка към клетка (съдържаща стойността за търсене), или стойност, върната от друга функция на Excel. Например тази формула ще търси стойността 40:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

Ако търсената стойност е по-малка от най-малката стойност в първата колона на диапазона, който се търси, функцията ВПР ще съобщи за грешка #AT (#N/A).

table_array (таблица) – две или повече колони с данни. Запомнете, функцията ВПР винаги търси стойността в първата колона на диапазона, даден в аргумента table_array (таблица). Видимият диапазон може да съдържа различни данни, като текст, дати, числа, булеви стойности. Функцията е нечувствителна към главни и малки букви, което означава, че главните и малките букви се считат за еднакви. Така че нашата формула ще търси стойността 40 в клетки от A2 да се A15, защото A е първата колона от диапазона A2:B15, даден в аргумента table_array (таблица):
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

col_index_num (column_number) е номерът на колоната в дадения диапазон, от който ще бъде върната стойността в намерения ред. Най-лявата колона в дадения диапазон е 1, втората колона е 2, третата колона е 3 и така нататък. Сега можете да прочетете цялата формула:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)
Формула, търсеща стойност 40 в диапазона A2: A15 и връща съответната стойност от колона B (тъй като B е втората колона в диапазона A2:B15).

Ако стойността на аргумента col_index_num (колона_номер) по-малко от 1след това ВПР ще съобщи за грешка #VALUE! (#СТОЙНОСТ!). И ако е повече от броя на колоните в диапазона table_array (таблица), функцията ще върне грешка #REF! (#ВРЪЗКА!).

търсене_обхват (range_lookup) – определя какво да търсите:
- точно съвпадение, аргументът трябва да е равен FALSE (НЕВЯРНО);
- приблизително съвпадение, аргумент е равен ИСТИНСКИ КОД (ВЯРНО) или изобщо не е посочено.
Този параметър не е задължителен, но е много важен. По-късно в този урок ВПР Ще ви покажа няколко примера, обясняващи как да напишете формули за намиране на точни и приблизителни съвпадения.

VLOOKUP Примери

Надявам се функцията ВПР да ти стане малко по-ясно. Сега нека разгледаме някои случаи на употреба ВПР във формули с реални данни.

Как да използвате VLOOKUP за търсене в друг лист на Excel

На практика формули с функция ВПР рядко се използват за търсене на данни в един и същи работен лист. По-често ще търсите и извличате съответните стойности от друг лист.

За да използвате ВПР, потърсете в друг лист на Microsoft Excel, Трябва в аргумента table_array (таблица) посочете името на листа с удивителен знак, последван от диапазон от клетки. Например следната формула показва, че диапазонът A2: B15 е на лист с име Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Разбира се, името на листа не трябва да се въвежда ръчно. Просто започнете да въвеждате формулата и когато става въпрос за аргумента table_array (таблица), превключете на желания лист и изберете желания диапазон от клетки с мишката.

Формулата, показана на екранната снимка по-долу, търси текста „Продукт 1“ в колона A (това е първата колона от диапазона A1:B2) на работен лист Цени.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Моля, не забравяйте, че когато търсите текстова стойност, трябва да я поставите в кавички („”), както обикновено се прави във формулите на Excel.

За аргумент table_array (таблица) е желателно винаги да се използват абсолютни препратки (със знак $). В този случай обхватът на търсене ще остане непроменен при копиране на формулата в други клетки.

Търсете в друга работна книга с VLOOKUP

Да функционира ВПР работи между две работни книги на Excel, трябва да посочите името на работната книга в квадратни скоби преди името на листа.

Например по-долу е дадена формула, която търси стойността 40 върху листа Sheet2 в книгата Числа.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Ето най-лесния начин да създадете формула в Excel с ВПРкойто препраща към друга работна книга:

Отворете и двете книги. Това не е задължително, но е по-лесно да създадете формула по този начин. Не искате да въведете името на работната книга ръчно, нали? Освен това ще ви предпази от случайни печатни грешки.
Започнете да въвеждате функция ВПРи когато става въпрос за спора table_array (таблица), превключете към друга работна книга и изберете необходимия диапазон за търсене в нея.

Екранната снимка по-долу показва формулата с търсене, зададено на диапазон в работната книга PriceList.xlsx върху листа Цени.

функция ВПР ще работи дори когато затворите търсената работна книга и пълният път до файла на работната книга се появи в лентата с формули, както е показано по-долу:

Ако името на работната книга или лист съдържа интервали, то трябва да бъде оградено с апостроф:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Как да използвате именуван диапазон или таблица във формули с VLOOKUP

Ако планирате да използвате един и същ диапазон за търсене в множество функции ВПР, можете да създадете именуван диапазон и да въведете името му във формулата като аргумент table_array (таблица).

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

Сега можете да запишете следната формула за намиране на цената на продукт Продукт 1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

Повечето имена на диапазони работят за цялата работна книга на Excel, така че не е необходимо да се указва името на листа за аргумента table_array (таблица), дори ако формулата и диапазонът за търсене са на различни работни листове. Ако те са в различни работни книги, тогава преди името на диапазона трябва да посочите името на работната книга, например така:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

Така че формулата изглежда много по-ясна, съгласни ли сте? Освен това използването на именувани диапазони е добра алтернатива на абсолютните препратки, тъй като именуваният диапазон не се променя, когато копирате формулата в други клетки. Това означава, че можете да сте сигурни, че диапазонът на търсене във формулата винаги ще остане правилен.

Ако конвертирате диапазон от клетки в пълноценна електронна таблица на Excel с помощта на командата Маса (Таблица) раздел вмъкване (Вмъкване), тогава, когато изберете диапазон с мишката, Microsoft Excel автоматично ще добави имената на колоните (или името на таблицата, ако изберете цялата таблица) към формулата.

Готовата формула ще изглежда така:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

Или може би дори така:

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

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

Използване на заместващи символи във VLOOKUP формули

Както при много други функции, ВПР Можете да използвате следните заместващи знаци:

Въпросителен знак (?) – замества всеки отделен знак.
Звездичка (*) – замества всяка последователност от знаци.

Използване на заместващи знаци във функциите ВПР може да бъде полезно в много случаи, например:

Когато не си спомняте точно текста, който трябва да намерите.
Когато искате да намерите някаква дума, която е част от съдържанието на клетка. Знам, че ВПР търси по съдържанието на клетката като цяло, сякаш опцията е активирана Съвпадение на цялото съдържание на клетка (Цялата клетка) в стандартното търсене на Excel.
Когато клетка съдържа допълнителни интервали в началото или края на съдържанието. В такава ситуация можете да разбиете мозъка си дълго време, опитвайки се да разберете защо формулата не работи.

Пример 1: Търсене на текст, който започва или завършва с определени знаци

Да приемем, че искате да търсите конкретен клиент в базата данни, показана по-долу. Не си спомняте фамилията му, но знаете, че започва с „ack“. Ето формула, която ще свърши добре работата:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

След като сте сигурни, че сте намерили правилното име, можете да използвате същата формула, за да намерите сумата, платена от този клиент. За да направите това, просто променете третия аргумент на функцията ВПР до желания номер на колона. В нашия случай това е колона C (3-та в диапазона):

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Ето още няколко примера със заместващи знаци:

~ Намерете име, завършващо на "човек":

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Намерете име, което започва с „ad“ и завършва със „son“:

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Намираме първото име в списъка, състоящ се от 5 знака:

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Да функционира ВПР със заместващи знаци работи правилно, като четвърти аргумент, който винаги трябва да използвате FALSE (НЕВЯРНО). Ако обхватът на търсене съдържа повече от една стойност, която съответства на думите за търсене със заместващи знаци, тогава ще бъде върната първата намерена стойност.

Пример 2: Комбинирайте заместващи символи и препратки към клетки във формули на VLOOKUP

Сега нека разгледаме малко по-сложен пример за това как да търсите с помощта на функцията ВПР по стойност в клетка. Представете си, че колона A е списък с лицензни ключове, а колона B е списък с имена, които притежават лиценз. Освен това имате част (няколко знака) от някакъв лицензен ключ в клетка C1 и искате да намерите името на собственика.

Това може да стане с помощта на следната формула:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

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

Както можете да видите на фигурата по-долу, функцията ВПР връща „Джеръми Хил“, защото неговият лицензен ключ съдържа последователността от знаци от клетка C1.

Имайте предвид, че аргументът table_array (таблица) на екранната снимка по-горе съдържа името на таблицата (Таблица7), вместо да указва диапазон от клетки. Това направихме в предишния пример.

Точно или приблизително съвпадение във функцията VLOOKUP

И накрая, нека разгледаме по-отблизо последния аргумент, който е посочен за функцията ВПР - търсене_обхват (интервал_изглед). Както бе споменато в началото на урока, този аргумент е много важен. Можете да получите напълно различни резултати в една и съща формула с нейната стойност ИСТИНСКИ КОД (ВЯРНО) или FALSE (НЕВЯРНО).

Първо, нека разберем какво означава Microsoft Excel под точни и приблизителни съвпадения.

Ако аргументът търсене_обхват (диапазон_търсене) е равно на FALSE (FALSE), формулата търси точно съвпадение, т.е. точно същата стойност като дадената в аргумента търсена_стойност (търсеща_стойност). Ако в първата колона на диапазона tспособен_масив (таблица) среща две или повече стойности, които съответстват на аргумента търсена_стойност (търсена_стойност), тогава първата ще бъде избрана. Ако не бъдат намерени съвпадения, функцията ще докладва грешка #AT (#N/A). Например следната формула ще отчете грешка #AT (#N/A), ако няма стойност в диапазона A2:A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)
Ако аргументът търсене_обхват (диапазон_търсене) е равно на ИСТИНСКИ КОД (ВЯРНО), формулата търси приблизително съвпадение. По-точно, първо функцията ВПР търси точно съвпадение и ако не бъде намерено такова, избира приблизително. Приблизително съвпадение е най-голямата стойност, която не надвишава стойността, посочена в аргумента. търсена_стойност (търсеща_стойност).

Ако аргументът търсене_обхват (диапазон_търсене) е равно на ИСТИНСКИ КОД (TRUE) или не е посочено, тогава стойностите в първата колона на диапазона трябва да бъдат сортирани във възходящ ред, т.е. от най-малката към най-голямата. В противен случай функцията ВПР може да върне грешен резултат.

За да разберем по-добре значението на избора ИСТИНСКИ КОД (ИСТИНА) или FALSE (FALSE), нека разгледаме още няколко формули с функцията ВПР и вижте резултатите.

Пример 1: Намиране на точно съвпадение с VLOOKUP

Както си спомняте, за търсене на точно съвпадение, четвъртият аргумент на функцията ВПР трябва да има значение FALSE (НЕВЯРНО).

Нека се върнем към таблицата от първия пример и да разберем кое животно може да се движи със скорост 50 мили в час. Вярвам, че тази формула няма да ви създаде затруднения:

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Имайте предвид, че нашият диапазон на търсене (колона A) съдържа две стойности 50 – в клетките A5 и A6. Формулата връща стойност от клетката B5. Защо? Тъй като когато търсите точно съвпадение, функцията ВПР използва първата намерена стойност, която съответства на тази, която се търси.

Пример 2: Използване на VLOOKUP за намиране на приблизително съвпадение

Когато използвате функцията ВПР за търсене на приблизително съвпадение, т.е. когато аргументът търсене_обхват (диапазон_търсене) е равно на ИСТИНСКИ КОД (TRUE) или пропуснато, първото нещо, което трябва да направите, е да сортирате диапазона по първата колона във възходящ ред.

Това е много важно, тъй като функцията ВПР връща следващата по големина стойност след дадената и след това търсенето спира. Ако пренебрегнете правилното сортиране, ще получите много странни резултати или съобщение за грешка. #AT (#N/A).

Сега можете да използвате една от следните формули:

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

Както можете да видите, искам да разбера кое от животните има най-близка скорост до 69 мили в час. И ето резултата, който функцията ми върна ВПР:

Както можете да видите, формулата върна резултат антилопа (Антилопа), чиято скорост 61 мили в час, въпреки че списъкът също включва Вид леопард (Гепард), който бяга със скорост 70 мили в час, а 70 е по-близо до 69 от 61, нали? Защо се случва това? Тъй като функцията ВПР при търсене на приблизително съвпадение, връща най-голямата стойност, която не е по-голяма от тази, която се търси.

Надявам се тези примери да хвърлят малко светлина върху работата с функцията ВПР в Excel и вече не гледате на нея като на външен човек. Сега не боли да повторим накратко ключовите моменти от материала, който сме изучавали, за да го фиксираме по-добре в паметта.

VLOOKUP в Excel – трябва да запомните това!

функция ВПР Excel не може да гледа наляво. Той винаги търси стойността в най-лявата колона на диапазона, даден от аргумента table_array (таблица).
Във функция ВПР всички стойности са нечувствителни към малки и големи букви, т.е. малки и големи букви са еквивалентни.
Ако стойността, която търсите, е по-малка от минималната стойност в първата колона на търсения диапазон, функцията ВПР ще съобщи за грешка #AT (#N/A).
Ако 3-ти аргумент col_index_num (колона_номер) по-малко от 1функция ВПР ще съобщи за грешка #VALUE! (#СТОЙНОСТ!). Ако е по-голям от броя на колоните в диапазона table_array (таблица), функцията ще отчете грешка #REF! (#ВРЪЗКА!).
Използвайте абсолютни препратки към клетки в аргумента table_array (таблица), така че правилният обхват на търсене да се запази при копиране на формулата. Опитайте да използвате именувани диапазони или таблици в Excel като алтернатива.
Когато извършвате търсене на приблизително съвпадение, не забравяйте, че първата колона в диапазона, който търсите, трябва да бъде сортирана във възходящ ред.
И накрая, не забравяйте важността на четвъртия аргумент. Използвайте стойности ИСТИНСКИ КОД (ИСТИНА) или FALSE (НЕВЯРНО) умишлено и ще се отървете от много главоболия.

В следващите статии от нашия урок по функции ВПР в Excel ще научим по-сложни примери, като извършване на различни изчисления с помощта на ВПР, извличане на стойности от множество колони и др. Благодаря ви, че прочетохте този урок и се надявам да се видим отново следващата седмица!