VLOOKUP е една от най-полезните функции в Microsoft Excel за обработка на голям обем от данни. Тя е лесна за използване, но понякога е изисква да знаете някои тънкости. В тази статия ще опишем основните предназначения на VLOOKUP, ще се запознаете със синтаксис и модели на съвпадение, с това как чете данните VLOOKUP и ще получите ценни практически съвети за използването на тази важна функция в Excel.
Автор: Валентина Стефанова – сертифициран Microsoft Office Specialist
 | Валентина Стефанова е Junior 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).
Фиг. 1
Фиг. 2
Синтаксис на функцията
Със следващите стъпки ще опишем начина, по който се въвеждат аргументите във функцията (фиг. 3):
Фиг. 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 за прехвърляне на данни, препоръчителното търсене е по точно съвпадение.