С 99.99% вероятност съм сигурен, че сте виждали грешка #N/A при VLOOKUP или XLOOKUP в Ексел. Това е много често срещан проблем, особено когато се обединяват данни от различни източници (системи). В тази статия ще разгледаме как да анализирате и коригирате данните, за да избегнете ръчна работа. Идеята за нея е породена от реална бизнес ситуация, в която помогнах на приятел наскоро. И съм убеден, че той не е единственият с такъв проблем.
Какъв е казусът
Наглед стандартен – имаме две таблици с данни за наши клиенти (данните са напълно измислени). Първата таблица съдържа име и имейл, извадени от системата за клиентите (CRM). Втората – име и приходи от счетоводната система. Искаме към първата таблица да добавим за всеки клиент приходите му от втората.
По принцип това е фасулска работа с VLOOKUP или както е в случая с XLOOKUP. Само че – виждате от картинката колко много грешки от типа „грешка #N/A при VLOOKUP“ връща формулата. Това се получава, когато формулата не намира името в колоната с имената от втората таблица.
Точно това беше казусът на моя приятел – XLOOKUP връщаше някои стойности, но не всички. Ако трябва да съм точен – за около половината от имената имаше грешка.
„Няма проблем – каза той – така спестихме половината работа. Останалото ще го копираме и пейстнем ръчно.“
Проблемът с това ръчно копиране и пействане мисля, че е ясен – отнема време и подлежи на грешки.
Какво може да се направи?
Много просто – да се уеднаквят имената в двете колони. Приемаме едната за вярна, трябва да направим имената в другата като тези в нея. За целта предлагам една изпитана във времето и практиката ми методика. Тя изисква да отделите малко време да анализирате и почистите данните. Но обикновено времето, което ще изгубите за това е много по-малко от времето за „ръчна“ работа и имате няколко допълнителни бонуса, които ще спомена накрая.
Ето накратко в какво се състои тази методика:
- Разглеждаме данните и анализираме причините за грешката.
- Отстраняваме причините
- Прилагаме VLOOKUP / XLOOKUP или друг подходящ метод за обединяване на таблиците
- Изводи и последващи действия
Анализ на данните
Както казах по-горе – за да получаваме грешка #N/A при VLOOKUP, значи формулата не открива името от първата таблица във втората. Това е теорията. В практиката най-често името съществува, но по малко по-различен начин. За да видим какъв е проблемът – правим едно просто сравнение. Взимаме имената от първата таблица и ги сортираме във възходящ ред. След това в съседна колона до тях поставяме имената от втората таблица, отново сортирани във възходящ ред. Това е направено на долната картинка и веднага се забелязва, че нещо не е наред.
Вижте как в лявата колона имената започват от ABC Co. В дясната първото име е Carlos Gonzalez, а АВС Со е чак на 20 ред. Тук сигурно се питате – „Но как е възможно това, нали имената са еднакви (уж)?“
Краткият отговор е, че много често имената са еднакви, но са въведени по различен начин в различните системи. В конкретния случай, пред „Carlos Gonzalez“ има един интервал и затова това име отива най-отгоре. И понеже Excel не може да разбере като нас, че „Carlos Gonzalez“ със и без интервал е едно и също – получава се грешка #N/A при VLOOKUP.
Причини за различие на уж еднакви имена
От опит съм установил тези най-често срещани:
- Излишни интервали преди, след и между имената
- Добавени кавички – вижте например „High Mountain“ OOD на 18 ред във втората таблица. Такова име съществува в първата, но без кавички
- Подобно на горното – могат да бъдат добавени всякакви символи и препинателни знаци. Особено, когато не става дума за имена, а за адреси, кодове, географски региони и др.
- Съкръщаване на имената или пропускане на част от тях. Например Гр. Димитров, вместо Григор Димитров или „High Mountain“ вместо „High Mountain“ OOD
- Използване на латиница вместо кирилица и обратно. Например “Pesho Goshev” вместо„Пешо Гошев“ и обратно.
- Подмяна на отделни букви на кирилица или латиница с подобните им от другата азбука. Това е частен случай на горното. Само че тук особено неприятно е, че не си личи толкова явно. Защото „а“, „о“, „М“, „Т“ и др. са абсолютно еднакви в двете азбуки и често някой пише „Пешо“ с латинско „о“ например. Иди че го разбери после.
- Подобен проблем е когато нула (0) е използвана вместо буквата „О“, 4 вместо „Ч“. Сещам се за един случай, когато вместо „Л“ някой беше използвал двете наклонени черти – „/\“
- Печатна грешка – винаги е възможно някой да изпусне някоя буква например
- Дублетни форми – „Илиян“ вместо „Илиан“, „Роберт“ вместо „Робърт“ и любимото ми – „Никола“ вместо „Колю“
- Текст, който изглеждат числа. Много често, когато се изваждат данни от една система, кодове, които са съставени от числа се експортират като текст. Тоест ние виждаме числото „1234“, но за Ексел то всъщност е текст
Това са само няколко примера, които може да имате предвид. Но винаги очаквайте неочакваното. Хората могат да бъдат много, много креативни – волно или неволно.
Нашата цел при анализа е да хванем поне няколко такива модела и да започнем да ги борим един по един.
Отстраняване на причините
В моята практика съм се убедил, че е много ефективно да се добави една колона отстрани на втората таблица, в която да се изчистват „пороците“ на данните. Съответно LOOKUP формулата в първата таблица да гледа в „изчистената“ колона.
Ползите от това са поне две. Първо, оригиналните данни са запазени в случай, че се наложи да се ползват за друга цел. Второ, имаме готова таблица за корекции, която може да се използва да се отстранят грешките във втората система.
Премахване на излишни интервали
Дори и без анализ можем да започнем като отстраним излишните интервали с функцията TRIM. Тя премахва излишните интервали, които може да се появят преди или след текста. Не пречи да я комбинираме и с друга подобна функция – CLEAN, която изчиства някои „невидими“ символи като прословутия „мек Enter” (ALT + ENTER). Това няма как да навреди.
Обърнете внимание, че това просто действие значително намали броя на #N/A грешките в жълтата колона.
Премахване на кавички и други препинателни знаци
На следваща стъпка можем да заменим всички кавички, скоби, тирета и други подобни с нищо (“”) с помощта на Find/Replace. Това също носи лесни точки, защото такъв „порок“ е много вероятно да съществува. Моят съвет е да направим втора допълнителна колона. В нея с Paste > Special > Values да пейстнем стойностите от първата („client name – TRIMMED“) и да работим в тази втора колона. Съответно XLOOKUP формулата в първата таблица, трябва да се промени да „дърпа“ от втората помощна колона
Проверка на временния резултат
За целта филтрираме #N/A грешките в първата таблица
Това, че са малко не бива да ви заблуждава. В действителността може да са хиляди редове. Ако разгледаме по-внимателно данните ще видим, че има няколко имена във втората таблица, в които нула е използвана вместо „О“ и @ вместо „а“. За по-лесно съм ви ги оцветил в розово.
Можем да приложим Find/Replace. НО вече трябва да се внимава. Възможно е нулата и @ да са напълно валидни символи. Например чували сте за рапър с име 100 кила или сладкарница „100 грама сладки“ предполагам. Няма универсално правило – пробвате и виждате какво се получава.
В нашия случай подмяната на тези два символа свежда случаите, в които имаме грешка #N/A при VLOOKUP до едноцифрено число:
Моментът за преценка
Винаги в такива казуси се стига до момент, в който остават малко грешки без установен модел. Тогава трябва да се вземе решение – продължаваме да инвестираме време и нерви да издирваме грешките. Или спираме до тук и живеем с тях.
Ако решим да се ровим, има една много проста, но и много ефективна техника за откриване на грешките. Отново копираме имената от двете колони на друг шийт, сортираме всяка колона поотделно възходящо (или низходящо) и между тях с проста формула сравняваме стойностите на всеки ред.
След това там, където имаме FALSE търсим обяснение за разликите.
На ред 6 и 7 например причината е, че в едната таблица имаме „Blue Sky Inc“ вместо “Blue line Inc”.
Този казус е неясен и няма да стане с екселски фокуси. Трябва да проверим в документи и системи кое е вярното име.
Вижте също и на ред 16 как след „Carlos Sanches“ има една точка
На ред 40 има интересен казус – тук във втората таблица липсва тире
Печатна грешка на ред 40
А това тук е интересно.
Някак си някой е въвел Robert Simpson и Sumo Co наобратно. И ако си мислите, че такива работи не се случват – помислете си пак. Аз съм го виждал с очите си.
В случая неприятното е, че това обърква сортирането и всички формули надолу дават FALSE. Трябва да отидем във втората таблица, да оправим тези две сгрешени имена, пак да се върнем и да пейстнем, да ги сортираме и да продължим напред с проверките.
И така напред-назад докато изчистим всички имена или поне паднем под прага на същественост
Изводи и последващи действия
Тази стъпка не е задължителна, но е силно препоръчителна. Добре е резултатите от анализа да се използват, за да се отстранят грешките в самите системи. Иначе всеки път ще си играете да чистите данните.
Също така може да се помисли защо се случва определен вид грешка. Може някой служител да не е обучен добре. Или просто да му е счупена клавиатурата и защото няма Л да пише /\ (прилики с действителни лица и събития не са случайни).
Понякога е удачно дори да се променят/подобрят отделни работни процеси. Например – вместо да се въвеждат данните в двете системи поотделно, те могат да се въвеждат само в едната и да се експортират във втората.
Могат да се променят и указанията за въвеждане на данните. Например – вместо да питате клиента за името, препишете го от личната му карта.
Или пък да се въведат падащи менюта, от които да се извикват определени стойности. Например градовете в България. Така елиминирате бъдещи разминавания защото някой е написал „Стара Загора“, друг „Ст. Загора“, а трети „Old Zagar City“.
Всички тези промени и подобрения в процесите са допълнителни бонуси от една наглед толкова проста и скучна операция като комбиниране на две таблици.