Microsoft обучениe и професионални IT консултации за всеки бизнес

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

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

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

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

valentina-stefanova Валентина Стефанова е 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).

vlookup

Фиг. 1

 

vlookup

Фиг. 2

 

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

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

vlookup

Фиг. 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.

vlookup

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

vlookup-itraining

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

vlookup4

vlookup5

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

vlookup6

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

vlookup7

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

vlookup8

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

vlookup9

vlookup10

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

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

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

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

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

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

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

vlookup12

 

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

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

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

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

vlookup14

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

vlookup15

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

vlookup16

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

vlookup17

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

vlookup18

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

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

vlookup19

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

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

vlookup20

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

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

vlookup21

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

vlookup22

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

vlookup23

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

VLOOKUP нe е case-sensitive

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

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

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

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

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

 

ITraining
Privacy Overview

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.