Имате нужда от Microsoft обучение? ITraining е решението!Курс ТОП 5 на най-полезното в Microsoft ExcelВсичко за Pivot Tables в Excel – онлайн курс на ITraining

VLOOKUP е една от най-полезните функции в Microsoft Excel за обработка на голям обем от данни.

Тя е лесна за използване, но понякога е изисква да знаете някои тънкости. В тази статия ще опишем основните предназначения на VLOOKUP, ще се запознаете със синтаксис и модели на съвпадение, с това как чете данните VLOOKUP и ще получите ценни практически съвети за използването на тази важна функция в Excel.

Автор: Валентина Стефанова – сертифициран Microsoft Office Specialist

Валентина Стефанова е Trainer в ITraining. Тя е сертифициран Microsoft Office Specialist (MOS) и е инструктор по най-популярните продукти в Office пакета – Excel, Word, PowerPoint и Outlook. Личният й преподавателски подход към курсистите помага за по-лесното усвояване на материала и овладяване на тънкостите на офис приложенията, независимо дали става въпрос за версия 2003 или 2016, за ниво начинаещи или напреднали.

 

VLOOKUP има две основни приложения:

• Да прехвърля записите от една таблица в друга на базата на уникални стойности (примера долу);

• Да категоризира стойности на базата на зададени критерии.

За повече информация по втората функционалност прочетете -> VLOOKUP vs IF в Excel или какво да правим, когато имаме ограничение в броя на вложените IF функции?

Определение

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

Ето един практически пример:

Разполагаме с работен лист Служители (фиг. 1), съдържащ пълна информация за служителите в една организация. Този лист ще наречем база данни – Т1. Имаме за задача в отделен лист Длъжност (фиг.2) – таблица за попълване – Т2, да се попълнят имената и съответната длъжност на служителите, като за целта трябва да ги намерим в базата данни по служебен номер (ID).

 

Фиг. 2

 

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

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

 

Lookup_value – Стойност от таблицата за попълване – Т2, по която ще търсим в базата данни – T1. В примера горе ще използваме ID на служителите.

Table_array – областта от клетки в базата данни – Т1, от която ще взимаме данни. При маркиране на колоните, изключително важно е, първата маркирана колона да съдържа стойността, по която се търси – Lookup_valuе (ID).

Col_index_num – в това поле въвеждаме поредния номер на колоната от базата данни – Т1, от която ще взимаме данните.

Range_lookup – Аргумент, който показва за какво ще се използва функцията. Ако ще се използва за прехвърляне на данни, то се нарича точно съвпадение и се изписва 0 или FALSE. Ако ще се използва функцията за категоризиране на информация, то се нарича приблизително търсене и се изписва 1 или TRUE.

За да попълним имената на служителите в лист Длъжност, прозорецът на функцията би изглеждал така:

Lookup_value ще е първото ID в списъка, стойността, по която ще търсим от таблицата за попълване – Т2.

Table_array съдържа работния лист (Служители) от базата данни – Т1, колона А (ID) и В (имена).

В Col_index_num записваме поредния номер на колоната от базата данни от която , които искаме резултат. При маркирането на колоните в Table_array преди да пуснете бутона на мишката се вижда номера на колоната:

Последният аргумент на функцията – Range_lookup, може да има 2 стойности – 1 или 0.

Крайният прозорец на функцията изглежда така:

Получения резултат изглежда така:

Формулата се копира до края на таблицата.
При така зададените параметри функцията VLOOKUP търси номер 908 в първата маркирана колона (обикновено най-лявата колона – ID) от таблица Служители на базата данни – Т1 и връща стойността, която съответства на 908 във втората колона – Десислава Екова.

Как да подготвим данните си за VLOOKUP?

Преди да използваме VLOOKUP трябва да сме сигурни, че данните ни са добре структурирани.

• Колоната, по която се търси се намира от ляво на данните, които ще извличаме
• Данните по които търсим в таблицата за попълване и базата данни са от един и същ тип;
• Колоната, по която се търси съдържа уникални стойности в базата данни

VLOOKUP „чете” данни отляво надясно

Едно от изискванията за работа с VLOOKUP е колоната, по която търсим в базата данни – Т1 да се намира от ляво на колоните, от които извличаме информация.

В примера използвахме ID, за да намерим имената на служителите в базата данни – Т1 и да ги запишем в таблицата за попълване – Т2. В базата данни – Т1– Служители, колоната с ID се намира отляво спрямо колоната с Име на служител. Това е задължително условие за извличането на данни при използване на VLOOKUP.

Ако разменим местата на ID и Име на служител в базата данни – Т1 лист Служители, то може да извлечем само ЕГН, но не и Име на служител.

 

VLOOKUP намира винаги първото съвпадение

При подготовка на базата данни – Т1 трябва да се има предвид, че VLOOKUP дава като резултат първата срещната стойност. Това означава, базата данни – Т1 трябва да е подготвена с уникални стойности в колоната за търсене.

Ето един пример, в който илюстрираме ситуация с неправилно подготвени данни:

Искаме да използваме колона В (Имена на служители) като уникална стойност, по която ще попълним Длъжност в лист Длъжност:

Не сме забелязали, но името Иван Иванов се среща два пъти в базата данни – Т1 – Служители. Единият е на длъжност „главен експерт“, а другият – специалист „обслужване и продажби“.

В работен лист Длъжност записваме следната формула:
Търсената стойност е по име на служител, а колоните в базата данни – Т1  са от B до G.

Индексът на колоната, от която ще взимаме стойности е 6 и търсим точно съвпадение – 0.

Резултатът изглежда така:

Както се вижда и двамата са главни експерти, но съвсем не е така.
Много трудно може да се открие такъв тип грешка особено ако работим с голям обем от данни. За това за ключова колона винаги се използва, такава, в която има уникални записи. Например: ИД, ЕГН, Email, телефонен номер или други уникални данни.

Данните подредени по колони са от един и същ тип.
Имаме следната ситуация:
В таблицата източник пред ID – тата на служителите има апостроф, така Excel разпознава данните в тази колона като текст. (Как Ексел чете данни)

А в таблица Служители колоната с ID е тип General, но Excel разпознава данните като числа.

В този случай резултатът би бил този, което означава, че VLOOKUP не намира тези стойности в базата данни.

Да сме сигурни, че няма дублиращи се данни

Най-лесният начин за установяване на дублиращи се данни е да приложим условно форматиране (Conditional formatting).
Първо маркираме колоната, към която да го приложим. Нека да е колона B с имената на служителите:
След това в таб Home избираме Conditional Formatting – > Duplicate Values

В прозореца Duplicate Values трябва да потвърдим, че дублиращите стойности ще се оцветяват в червен цвят: ОК.

Получаваме следния резултат:

Това отново доказва, че имената не са подходящ избор за уникална стойност, по която да търсим с VLOOKUP.

VLOOKUP нe е case-sensitive

Това означава, че не прави разлика между големи и малки букви.

Ето пример:
По-горе търсихме „Десислава Екова“ в лист Служители. Няма значение дали името е написано с големи или малки букви, VLOOKUP ще го открие.

В заключение:

За VLOOKUP трябва да запомните поне тези 3 неща:

• Търси данни отляво надясно
• Търси се по уникална стойност
• При използване на VLOOKUP за прехвърляне на данни, препоръчителното търсене е по точно съвпадение.