В първата статия за 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}.
Много важна забележка: Ако регионалните настройки на вашия компютър са такива, че десетичният символ е запетая, т.е. десетичните дроби се изписват така: 1,2; 3,14 и т.н., тогава за да работи формулата трябва да напишете: CHOOSE{1\2}. Ако използвате за разделител запетая, а не наклонената черта, Excel приема, че 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 извършва операцията процент по процент .
Две идеи как да облекчите положението:
- Сортирайте двете таблици във възходящ ред по колоната с ключа. Това помага много на VLOOKUP, защото той хваща една стойност в първата таблица, отива във втората и започва да я търси надолу. Като я намери – връща резултат, взема втора стойност и отива пак да я търси и т.н.. Когато и двете таблици са сортирани това спестява голяма част от циркулирането нагоре-надолу.
- Правете копи/пейст на формулата на порции от по 40-50 хил. реда. Странно е, но ако имате таблица от 200 хиляди реда и пуснете 5 пъти VLOOKUP, това ще ви отнеме много по-малко време отколкото, а ко пуснете формула за висчките 200 реда
Тази дълга, но надявам се интересна и полезна за вас статия е към своя край. Един последен коментар – вместо CHOOSE({1,2} можете да използвате IF({1,0}. Върши същата работа, като обърнете внимание, че 1 е преди 0, а не обратното, както изглежда по-логично.