VLOOKUP е една от най-полезните функции в Microsoft Excel за обработка на голям обем от данни.
Какво предстои? Ще опишем основните предназначения на функцията VLOOKUP, ще се запознаете със синтаксис и модели на съвпадение, с това как чете данните VLOOKUP и ще получите ценни практически съвети за използването на тази важна функция в Excel.
VLOOKUP има две основни приложения:
- Да прехвърля записите от една таблица в друга на базата на уникални стойности (примера долу);
- Да категоризира стойности на базата на зададени критерии.
Определение
VLOOKUP търси конкретна стойност в най-ляво маркираната колона на таблица, наречена база данни и връща стойност от същия ред, но от друга зададена колона от базата данни.
Практически пример на функцията VLOOKUP:
Имаме таблица на поръчките, в която ще определим общата цена, като използваме данни от втора таблица - Ценова листа:
Първа таблица
За по-лесно може да маркирате текста отдолу и да го разположите в екселски файл.
| Таблица на поръчките | ||||
| № | Наименование | Количество, кг | Единична цена | Обща цена |
| 1 | ябълки | 62 | ||
| 2 | круши | 32 | ||
| 3 | банани | 56 | ||
| 4 | фурми | 40 | ||
| 5 | кайсии | 20 | ||
| 6 | портокали | 38 | ||
| 7 | мандарини | 22 | ||
| 8 | домати | 45 | ||
| 9 | чушки | 26 | ||
| 10 | картофи | 120 | ||
| 11 | лук | 78 | ||
| 12 | краставици | 35 | ||
| 13 | лимони | 19 | ||
Втора таблица
| Ценова листа | |
| Наименование | Цена за кг |
| банани | 2.60 лв. |
| домати | 2.90 лв. |
| кайсии | 3.20 лв. |
| картофи | 0.85 лв. |
| краставици | 1.90 лв. |
| круши | 3.25 лв. |
| лимони | 2.60 лв. |
| лук | 1.05 лв. |
| мандарини | 1.65 лв. |
| портокали | 2.20 лв. |
| фурми | 3.20 лв. |
| чушки | 1.65 лв. |
| ябълки | 1.45 лв. |
Нека намерим къде стои тази функция! Вижте снимката отдолу!
Кликнете в клетка D3 (1), Натиснете бутона за функция fx (2) . Отваря се прозореца Insert Function (3). Изберете категория и след това функцията (4,5) и накрая ОК(6).
Отваря се диалоговият прозорец Function Arguments.
Tук е определящото от къде ще черпи данни функцията VLOOKUP.
Със следващите стъпки ще опишем начина, по който се въвеждат аргументите във функцията и синтаксиса на функцията.
1. Lookup_value – Стойност от таблицата за попълване – Таблица на поръчките, по която ще търсим в базата данни – Ценова листа. В примера горе ще използваме Наименование (на продукта).
2. Table_array – областта от клетки в базата данни – Ценова листа, от която ще взимаме данни. При маркиране на колоните, изключително важно е, първата маркирана колона да съдържа стойността, по която се търси – Lookup_valuе (Наименование). Тук маркираме от клетка G3 до клетка H15. И задължително от относителни стойности в клетките ги правим абсолютизирани.( $G$3:$H$15).
3. Col_index_num – в това поле въвеждаме поредния номер на колоната от базата данни – Ценова листа, от която ще взимаме данните (в случая втора колона - 2).
4. Range_lookup – Аргумент, който показва за какво ще се използва функцията. Ако ще се използва за прехвърляне на данни, то се нарича точно съвпадение и се изписва 0 или FALSE. Ако ще се използва функцията за категоризиране на информация, то се нарича приблизително търсене и се изписва 1 или TRUE.
Натискаме ОК и имаме функцията в колона D3.
Нека дадем формат на числата в колона Н от таблица Ценова листа. Маркираме в колона Н, клетките от Н2 до Н15. С десен клик избираме Format cells, избираме Currency и избираме символ лв. с два знака след десетчината запетая. Вижте снимката отдолу. ОК.
Така указваме, че това са цени в левове.
Сега е време да маркирате клетка Е3 и в нея ръчно да въвете формула, която ще дава общата цена на продукта - ябълки. Изпишете =C3*D3. Натиснете клавиша ентер.
Размножете формулите с двоен клик в десния ъгъл на клеките. Първо C3, след това D3. Ако сте работили правилно, ще имате следните данни в таблицата на поръчките:
Полседно, за да е завършена тази таблица, нека форматираме клетките от колона Обща цена по начина, който изполвахме за колона Н в таблица Ценова листа.
ВАЖНО: Нека като допълнение да добавим и следната информация:
Как да подготвим данните си за VLOOKUP?
Преди да използваме VLOOKUP трябва да сме сигурни, че данните ни са добре структурирани.
- Колоната, по която се търси се намира от ляво на данните, които ще извличаме
- Данните по които търсим в таблицата за попълване и базата данни са от един и същ тип;
- Колоната, по която се търси съдържа уникални стойности в базата данни
VLOOKUP „чете” данни отляво надясно
Едно от изискванията за работа с VLOOKUP е колоната, по която търсим в базата данни – Ценова листа да се намира от ляво на колоните, от които извличаме информация.
В примера използвахме Наименование, за да намерим продукта в базата данни – Ценова листа (Т1) и да ги запишем в таблицата за попълване – Таблица на поръчките (Т2). В базата данни – Т1-Ценова листа колоната с Наименование се намира отляво спрямо колоната с Цена на кг.. Това е задължително условие за извличането на данни при използване на VLOOKUP.
VLOOKUP намира винаги първото съвпадение
При подготовка на базата данни – Т1 трябва да се има предвид, че VLOOKUP дава като резултат първата срещната стойност. Това означава, базата данни – Т1 трябва да е подготвена с уникални стойности в колоната за търсене, в случая Наименование.
С това упражнението приключи! Успех!
Урокът е подготвен с информация от сайта Itrainig.bg