VLOOKUP

Сериозен блог за Ексел не може да мине без статия за функцията VLOOKUP.  За това реших една от първите публикации в блога да е именно за нея. Повечето статии в интернет се съсредоточават върху синтаксиса, как действа и какви са нейните особености. Най-хубавата такава на български език можете да прочетете в блога на ITraining – една от водещите фирми у нас за обучение по Ексел и други Microsoft продукти. Препоръчвам я горещо.

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

Преди това нека видим на бързо как работи. Отговорът е сравнително лесен – търси информация по същата логика, по която „ръчно“ търсим информация в таблица . Да вземем за пример долната, в която са изброени няколко отбора от Европа и Южна Америка, от коя страна са, как се казва стадионът им и кога са основани.

Как ще разберем как се казва стадионът на  отбора Данубио? Отиваме в колоната с имената на отборите, спускаме се надолу докато намерим реда на Данубио, след това тръгваме надясно докато стигнем колоната с имената на стадионите. Ето така разбираме, че стадионът се казва „Хардинес дел Иподромо“.  Между другото Данубио е отбор, създаден от български емигранти в Уругвай и ако името му ви звучи някак си познато – не се чудете. Това е испанското наименование на река Дунав.

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

1. Обединяване на таблици

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

Проблемът е, че втората таблица съдържа няколко отбора в повече и освен това е сортирана низходящо по брой титли, а нашата – възходящо по име на отбор. Лесно бихме преписали титлите от едната таблица в другата, защото табличките са от по 10-20 реда, но в практиката обикновено работим с много по-големи таблици. Освен това преписването дори и на тези малки таблици ще отнеме време и ще съдържа риск от грешка. И ето тук на помощ идва VLOOKUP, която вижда всеки отбор от  колона В на първата таблица  отива във втората ($H$4:$I$21), намира го в лявата колона и ни връща броя титли от втората колона. Точната формула можете да видите на долната картинка.

В тази формула искам да обърна внимание на две неща:

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

Второ, вместо FALSE съм използвал нула. За Ексел това е едно и също, а за мен по-бързо, т.е. по-ефективно. И да – вместо TRUE, може да се използва 1

2. Извикване на параметър във функция

VLOOKUP можем да използваме и да ни намери някаква стойност по зададен критерий, която да използваме във формула. Например отново се връщаме към нашата таблица с отборите, но сега целта ни е да разберем преди колко години е основан Данубио от нашите сънародници в Уругвай. Без Ексел как ще го направим – ще намерим в коя година е основан (1936) и ще я извадим от текущата година (2018). Е, в Ексел можем да използваме проста формулка с VLOOKUP, както е показано на долната картинка

И ако в клетка С18 напишем името на друг отбор, примерно Коло Коло, ще получим неговата „възраст“.

По абсолютно същата логика във формуляр за поръчка може да се направи като се въведе име на изделие да се появи цената му. Или при въвеждане на булстат във фактура да се появят име, адрес, МОЛ на клиента и так далее.

 3. VLOOKUP като алтернатива на IF

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

Но ако добавим отбори от Бразилия, Парагвай, Колумбия и Суринам – ще се видим в приключение докато напишем формулата. А какво би станало, ако включим и отбори от другите континенти? Ето къде на помощ идва VLOOKUP, както е показано на долната картинка.

Правим допълнителна таблица, в която срещу всяка държава пишем континента. След това в нашата основна таблица го извикваме с VLOOKUP. Фасулска работа!

4. Определяне на категории

Всъщност това е друг случай, в който използваме VLOOKUP вместо IF. Например, ако искаме да степенуваме отборите в зависимост от броя на спечелените титли. Когато един отбор е спечелил под 10 титли, той е „голям“, ако има между 10 и 20 титли – „велик“ и над 40 титли – „Доминатор“. Досещате се, че с IF, ще падне голямо писане, но с VLOOKUP решението е много елегантно. Трябва само да си направим една помощна табличка, в която да подредим категориите:

Как работи номерът?

Първо, отбележете си, че това е един от редките случаи, в които се използва приблизително, а не точно търсене (TRUE вместо FALSE). При него VLOOKUP търси в помощната таблица в колона „Брой титли“ и когато намери стойност, която е по-голяма от критерия – връща се един ред назад и дава стойността от колона „Категория“. Българският отбор в Уругвай, Данубио, има 4 титли. Първото, число, което е по-голямо от 4 е 20, затова VLOOKUP ни връща резултат от  реда преди 20, т.е. „Голям“.

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

Пенярол например има 51 титли (тия не ги е срам). Последното число в помощната таблица, което е по-малко от 51 е 40, затова VLOOKUP връща това, което стои срещу него, а именно – „Доминатор“.

И задължително помощната таблица трябва да е сортирана във възходящ ред по колоната с критериите.

Ако искате да прочетете по-подробно за този начин на използване на VLOOKUP, отново ще ви препоръчам една хубава статия от блога на ITraining

Приблизителното търсене с VLOOKUP като алтернатива на IF може да има много широко приложение. За пресмятане на отстъпки в зависимост от оборота, куриерски услуги според теглото, бонус според процент изпълнение на целите и т.н.

5. Сравняване на списъци

Имаме два списъка с отбори. Искаме да разберем кои се повтарят в двата списъка и кои не.

 

Има няколко начина да се направи това в Ексел. Единият е като направим търсене с VLOOKUP на отборите от първата таблица във втората и обратно.

Ако намери нещо – ще ни върне името на отбора, ако не – ще даде грешка. Така ще разберем, кои отбори ги има и в два списъка, кои само в първия и кои – само във втория.

 

Обърнете внимание на оградената в жълто част от формулата. Областта за търсене е само една колона. Това не му пречи на VLOOKUP. Само не бива да забравяме да сложим 1 в следващия параметър. Ако е число по-голямо от едно – ще даде грешка във всички клетки. Просто няма как да ни върне стойност от втората колона щом областта ни за търсене е само от една колона.

В общи линии това са основните случаи, в които използваме VLOOKUP. Както виждате тази чудесна функция помага в най-различни ситуации. Не случайно се възприема като критерий за майсторско владеене на Ексел. Истинските майстори обаче познават и тъмната страна на VLOOKUP. Има си доста недостатъци тази функция, но пък за всяка болка си има цяр. Следете блога за втората част на статията, в която ще разгледаме и болките, и цяровете.

 

Публикувано в Формули с етикети , , , . Постоянна връзка.

Вашият коментар

Вашият имейл адрес няма да бъде публикуван. Задължителните полета са отбелязани с *