Тъмната страна на Силата – ограниченията на VLOOKUP и как да ги преодоляваме

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

 

Проблем1: VLOOKUP търси само надясно

Функцията работи по този начин – ключът, по кото се търси трябва да е в най-лявата колона на областта за търсене.  В нашата примерна таблица по номер на фактура с VLOOKUP можем да търсим в колоните Основание, Тип плащане, Стойност и Съставил, но не и в Клиент и Дата.

Но какво  да правим, ако искаме да разберем на кой клиент е издадена фактура 1858?

Решение1: Първото очевидно решение е да преместим колоната с номерата на фактурите най-отляво. Може да стане с Cut & Paste. Може и да вмъкнем празна колона и в нея да въведем стойностите от колоната с номерата на фактурите (с формула, разбира се).

Решение2: INDEX+MATCH

MATCH е функция, която търси определена стойност в ред или колона и връща номера на клетката, в която търсената стойност се среща за пръв път.

INDEX връща стойността от клетка, която се явява пресечна за даден ред и дадена колона.

В нашия пример: MATCH търси стойност 1858 в колоната с фактурите. Като я намери – „съобщава” на INDEX на кой ред се намира и INDEX връща името от колоната с клиентите

Ако ви звучи сложно – не се притеснявайте. Просто запомнете формулата по следния начин:

=INDEX(колона, от която искаме да получим стойност;MATCH(Ключ за търсене;колоната, в която е ключът;0))

С данните от нашия пример формулата е:

INDEX(B3:B17;MATCH(A20;C3:C17;0))

А за INDEX и MATCH ще има допълнителни статии в блога.

Решение3: VLOOKUP(A20;CHOOSE({1,2};C3:C17;B3:B17);2;0)

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

CHOOSE({1,2};C3:C17;B3:B17) всъщност създава една виртуална таблица от две колони – първата е колоната с номерата на фактурите (т.е. с ключовете за търсене), а втората – тази с имената на клиентите (данните, които търсим).

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

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

Ето онагледени формулите с INDEX и MATCH и CHOOSE({1,2}.

 

Проблем2: VLOOKUP изисква номер на колона, от която да върне резултат

Да кажем, че искаме по номер на фактурата да намерим как е платено – в брой, с карта или по банка.

Нищо сложно с VLOOKUP, въвеждаме простичка формула:

VLOOKUP(A21;E4:M18;3;0)

Но резултатът е разочароващ – кръгла нула. Защо?

Ако се вгледате по-внимателно, ще видите, че след колона F на Ексел стои колона К. Това изначава, че имаме скрити колони и неправилно сме преброили, че типът на плащането е в третата колона от таблицата за търсене.

Много често няма скрити колони, но те са толкова много, че рискът да се сбърка с броенето им е голям.

Дори и да познаем точния брой на колоните, това не бива да ни успокоява. Защото ако някой вмъкне нова колона след време – това ще доведе до грешка.

При това положение – какво може да се направи?

Решение1: INDEX + MATCH

В тази формула ние боравим с точно две колони. В едната се намират ключовете за търсене, в другата – това, което търсим. Няма нужда от броене на колони, а дори и да се вмъкне (или изтрие) някоя колона след написването на формулата, това не ни влияе.

Решение2: CHOOSE({1,2}

Точно така – тук също работим с две колони и колко скрити между тях има няма никакво значение.

Решение3: COLUMNS

COLUMNS е функция, която връща като резултат броя на колоните в дадена област.

В нашия пример COLUMNS изброява колко са колоните между „Фактура №” и „Тип плащане” – 7. И понеже „Тип плащане” е последната колона в областта, то 7 е нейният номер. Точно това му трябва на VLOOKUP.

Решение4: MATCH

Както казах по-горе – MATCH търси стойност в дадена област и щом я намери – връща номера на реда или колоната, в която се намира. И точно това е използвано в четвъртата формула. Отново VLOOKUP ще ни връща правилен резултат дори и да вмъкнем, изтрием колона или преместим тази с типа на плащане някъде. Стига, разбира се, наименованието на колоните да си остане  на ред 2.

MATCH има и това предимство пред другите варианти, че може да променя аргумента динамично. Например обърнете внимание, че ако в В23 въведем не „Тип плащане”, а „Стойност”, само VLOOKUP в съчетание са MATCH се „адаптира” към новите реалности и връща сумата на фактурата.

Проблем3: Областта за търсене трябва да е таблица

Така е създаден VLOOKUP, че данните, които търси трябва да са подредени в таблица с последователни редове и колони. Може някои от тях да са празни, но да са редове и колони.

Много често обаче, данните, с които разполагаме не са подредени така. Например счетоводната програма може да подаде данните в този вид:

Как от тази неразбория да разберем кой е клиентът, на който сме издали фактура 1858?

Решение1: Първо да подредим таблицата и след това да приложим VLOOKUP. Подреждането може да стане с много копи/пейст, което е неефективно и носи риск от грешка. Затова по-добре да го направим с помощта на макрос или Power Query.  Но в този случай трябва да положим известно първоначално усилие за написване на съответния код.

Решение2: INDEX + MATCH

Прилагаме си формулата по стандартния начин. Но прилагаме хитрост – леко „разминаваме” двете области защото номерът на фактурата е винаги един ред под името на клиента. Така искаме INDEX да ни върне стойност от C4:C18. А в същото време MATCH търси в B3:B17. Може би така ще стане по-ясно:

Решение3: CHOOSE{1,2}

Подобно на решение 2, отново разминаваме областта за търсене с тази за ключовете. И отново връща коректен резултат.

Ето двете формули онагледени:

На долната картинка е показано как изглеждат формулите, ако ни трябва името на служителя, който е съставил фактурата.

Проблем 4: VLOOKUP използва по подразбиране приблизително търсене

Ако не укажем изрично четвъртият аргумент (TRUE или FALSE), VLOOKUP по подразбиране приема, че е TRUE и извършва приблизително търсене. Сиреч търси най-близкото до зададения ключ за търсене.

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

 

Проблем 5: VLOOKUP е волатилна функция

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

Тази особеност на VLOOKUP не създава проблем при по-малки обеми от данни. Но ако работите с таблици от 50-60 колони и 300-400 хиляди реда, това сериозно може да забави работата на компютъра ви. Особено, ако машината е по-слабичка.

Решение 1: INDEX + MATCH

Тези функции не са волатилни, съответно преизчисляват се само, когато се променят клетки пряко свързани с тях.

Решение 2: Copy/Paste as values

Много често използваме VLOOKUP, за да извикаме някакви стойности от друга таблица. Знаем, че те няма да се променят, дори и да се случват промени в други колони. Тогава просто можем да направим Copy -> Paste Special -> Values на колоната с VLOOKUP. Така вече няма да имаме формули, а само стойности.

Но много внимавайте при този вариант. Все пак не са изключени промени. Аз лично ви съветвам да си запазите формулата в първия ред и от време на време да я копирате до края, за да „опресните” стойностите.

 

Проблем 6: VLOOKUP връща само първата стойност, която намери

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

Вариант1: Интересува ни дали дадена фирма присъства в списъка с фактурите без значение колко пъти. Тогава прилагаме VLOOKUP и ако не намери стойността, ще върне #N/A.

Вариант2: Искаме да получим сумата на фактурите на дадена фирма. Тогава всъщност не ни трябва VLOOKUP, а примерно SUMIF или пивот таблица

 

Проблем7: VLOOKUP търси само по един критерий

Можем да търсим с VLOOKUP по име на клиент, но дали е възможно да намерим кой е съставил фактура на XYZ Ltd, с начин на плащане „в брой”?

 Решение1: Помощна колона

Вмъкваме нова колона в която с формулата CONCATENATE или с помощта на символа „&” „слепваме” колоните за клиенто и начин на плащане. Това ще бъде нашият ключ за търсене.

След това си прилагаме стандартен VLOOKUP. В случая съм използвал COLUMNS за определяне номера на колоната, от която да върне резултат.

Решение2: CHOOSE{1,2}

 Казах ви, че магията на CHOOSE{1,2} е много силна. В този случай отново я прилагаме, за да създадем виртуална таблица от две колони. Първата колона обаче е също виртуална – получава се, чрез слепване на колоните за „Клиент” и „Тип плащане”. Това се случва в подчертаната част от формулата. И понеже станаха много виртуалните колони, когато завършим формулата трябва да натиснем SHIFT + CTRL + ENTER. Ако натиснем само ENTER формулата няма да сработи.

Това е така, защото сме създали т.нар. array formula. Повече за тези формули – някой друг път. Сега само обърнете внимание на къдравите скоби. Всяка array formula има такива, по което можете да я разпознаете. Но не е нужно да ги пишете – Ексел си ги добавя автоматично.

Проблем8: VLOOKUP не е case sensitive

На български език това означава, че VLOOKUP не прави разлика между АБВ ООД, абв ООД, АбВ оод, Абв ооД и всякакви други вариации на това име.

Понякога това може да е проблем, защото според долната таблица например, АБВ ООД е от България, а абв оод – от Китай. Как тогава можем да направим VLOOKUP case sensitive и да „извикаме” страната по точното име на фирмата?

Има няколко решения на този проблем, но аз ще ви покажа това, което за мен е най-разбираемо. Може би защото сам си го измислих преди време, когато се сблъсках с този проблем. Сетихте ли се вече коя магия се използва?

Точно така CHOOSE{1,2} само, че този път ще ни помогне и функцията EXACT. Това е една малка, рядко използвана функция, която проверява дали две стойности са НАПЪЛНО еднакви. Ако са – връща резултат TRUE, в противен случай – FALSE.

Например EXACT(„АБВ ООД”;”абв оод”) ще върне резултат FALSE, докато проверка с „АБВ ООД”=”абв оод” ще даде резултат TRUE.

В такъв случай, за да намерим страната на фирма „абв оод” използваме VLOOKUP по този начин:

={ VLOOKUP(TRUE;CHOOSE({1,2};EXACT(„абв оод“;B4:B11);C4:C11);2;0)}

Как работи?

Хитрината тук е, че ние искаме от VLOOKUP да търси не „абв оод”, а TRUE.

Областта за търсене е виртуална таблица от две колони (CHOOSE({1,2})

И тук се появява на сцената EXACT. Обърнете внимание, че в случая казваме на EXACT да сравнява текста „абв оод” с всички стойности в колоната B4:B11. Най-напред сравнява с В4 и връща FALSE, защото в В4 стойността е „АБВ ООД”, която е различна от „абв оод”. След това проверява в В5, където е „XYZ Ltd” – също различна от „абв оод”, съответно отново връща FALSE. И така EXACT създава първата колона на нашата виртуална област за търсене, която изглежда така:

{FALSE; FALSE; FALSE; …..;FALSE; TRUE}

Втората колона е С4:С11, т.е. колоната със страните

Останалото мисля е ясно – когато VLOOKUP намери  TRUE в първата колона на виртуалната област, връща стойността от втората.

Накрая трябва да натиснете SHIFT + CTRL + ENTER, защото това е array формула.

Разбира се, за да направим нашата формула по-гъвкава, можем вместо фиксираната стойност „абв оод” да използваме адреса на клетката, в която се намира. Ето финалният вид на формулата:

{ VLOOKUP(TRUE;CHOOSE({1,2};EXACT(B14;B4:B11);C4:C11);2;0)}

Споменах, че има и други решения на този проблем. Те всички използват EXACT.

За повече подробности:

https://wmfexcel.com/2016/04/09/three-different-ways-to-do-case-sensitive-lookup/

https://chandoo.org/wp/2015/09/07/case-sensitive-lookups/

https://trumpexcel.com/vlookup-case-sensitive/

Проблем9: #N/A пречи на изчисленията

По принцип за нас е добре, когато VLOOKUP върне резултат #N/A, когато не намери каквото търсим. Така знаем, че го няма. Но понякога това ни пречи.

Долната таблица например е един типичен отчет, сравняващ продажбите по отчет спрямо бюджета. Най-често отчетните данни ги взимаме от счетоводна или друга система, а бюджетните – от друг файл. Естествено сглобяваме отчета с помощта на VLOOKUP. Проблемът в случая обаче е, че имаме трима клиенти (оцветени в жълто), за които нямаме бюджетирани продажби. VLOOKUP не ги намира и връща #N/A, което обърква всички формули надолу.

Решението в случая е формулата ISERROR. С тази формула проверяваме дали резултатът от VLOOKUP е грешка. Ако не е – формулата връща това, което е намерил VLOOKUP. Ако обаче е грешка – казваме да върне нула. Просто, но ефективно.

Има и друг случай – когато #N/A се явява критерий, т.е. ако резултатът от VLOOKUP е #N/A, трябва да се случи нещо. Ако не е – да се случи нещо друго. Класическа задача за формулата IF. И понеже трябва да проверим дали резултатът от VLOOKUP е грешка, използваме също така и ISERROR. Последната функция връща TRUE ако даден израз е грешка и FALSE, ако не е.

Нека да разгледаме за пример следната таблица. Тя показва бонусите на служителите в една измислена фирма. Само че, шефът в тази фирма е решил да допринесе за повишаване културата на шофиране на своите служители. Затова е въвел следното правило – ако служителят няма глоби от КАТ, бонусът се завишава с 20%. Ако има – с 5% за всеки един фиш или акт. Информацията за глобите се съдържа в отделна таблица. А формулата я виждате в лентата за формули.

Горкият Илиян. Завалията е единственият, който си е заработил петцифрен бонус, но многото глоби му го изяждат. Което само показва, че правилата трябва да се спазват. И дай боже повече такива шефове.

Проблем10:VLOOKUP е много бавен при големи файлове

Не знам колко често ви се случва да работите с файлове от по 50-100 хиляди реда и 30-40 колони, за мен това е кажи-речи ежедневие. Едно от най-неприятните неща е когато направиш един VLOOKUP от една такава таблица в друга и изчислението започва да се влачи със скоростта на костенурка. Лошото е, че компютърът става много бавен и можеш само да гледаш как VLOOKUP извършва операцията процент по процент .

Две идеи как да облекчите положението:

  1. Сортирайте двете таблици във възходящ ред по колоната с ключа. Това помага много на VLOOKUP, защото той хваща една стойност в първата таблица, отива във втората и започва да я търси надолу. Като я намери – връща резултат,  взема втора стойност и отива пак да я търси и т.н.. Когато и двете таблици са сортирани това спестява голяма част от циркулирането нагоре-надолу.
  2. Правете копи/пейст на формулата на порции от по 40-50 хил. реда. Странно е, но ако имате таблица от 200 хиляди реда и пуснете 5 пъти VLOOKUP, това ще ви отнеме много по-малко време отколкото, а ко пуснете формула за висчките 200 реда

 

Тази дълга, но надявам се интересна и полезна за вас статия е към своя край. Един последен коментар – вместо CHOOSE({1,2} можете да използвате IF({1,0}. Върши същата работа, като обърнете внимание, че 1 е преди 0, а не обратното, както изглежда по-логично.

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