Наскоро попаднах на интересен казус – как два напълно еднакви на пръв поглед текста са всъщност различни. Причината – в единия по някаква причина на произволни места вместо българските букви като е, а, о, р, М, Н, Т и др. са използвани аналозите им от латинската азбука. Този проблем се случва често при отговори на отворени въпроси в анкета, ръчни корекции, въвеждане ръчно на текст в система, дори повредена клавиатура. Не винаги проблемът е замяна на български с латински букви (и обратно). Понякога може да бъде използвано късо тире (-), вместо дълго (–), нула (0) вместо (О), “ (прави кавички) вместо „“ (отварящи/затварящи кавички) и много други.
Кога това може да е проблем
В повечето случаи подмяната на латински букви с български (и обратно) или на други приличащи си символи не е проблем. Например, ако си правите списък за пазаруване или коледна томбола. Но има ситуации, в които това може да създаде доста неприятности. Например:
- Филтриране и сортиране пропускат стойности: Търсите „Гошо“ и Excel ви връща всички редове, в които пише „Гошо“, но не и тези, в които някое от двете български букви „о“ са заменени с латинските букви „о“;
- Формули като VLOOKUP, XLOOKUP, MATCH връщат грешни резултати: Аналогично на горното е, само че вместо да филтрирате използвате формули и се чудите защо Excel ви връща #N/A;
- Обединяване (merge/join) между таблици се проваля: абсолютно аналогични на горното, само че се опитваме да обединим две таблици в Power Query
- Пивот таблицата ви разделя „еднакви“ стойности в отделни редове: в отчета се появяват два реда: „Гошо“ и „Гошo“. Разбира се – едното е изписано само с български букви, другото – не
Тези Excel-ски проблеми всъщност са бели кахъри в сравнение с реални ситуации, които могат да възникнат. Да кажем Excel файл е използван за наливане на клиентски данни в система. По някаква причина клиентът Гошо е въведен с две латински букви „о“ вместо български. Човекът се явява в клон на фирмата и желае да закупи нещо с полагащата му се отстъпка като редовен клиент – но не може да бъде намерен в системата. Просто клиент Гошо, изписан на правилен български език не съществува!
Ето и друг съвсем реален пример: Фирма купува свой конкурент. Част от процеса по сливането е обединяване на базите данни с клиенти. По някаква причина за голям брой клиенти в базата данни на закупуваната фирма липсва надеждно ЕГН. Единственият начин да се обединят двете бази е по име. Да, но се оказва че хиляди имена и в двете системи са написани на българо-английски.
Проблемът е илюстриран на долната картинка. Виждате как проверка с формулата EXACT връща резултат FALSE, т.е. индикира, че двете стойности не са еднакви
Как да познаем кои са
В казуса, с който започнах, ставаше въпрос как да познаем на коя позиция са сгрешените букви и кои са. Веднага казвам, че това няма да реши автоматично проблема, но така беше поискано, така ще представя и решението.
В случая трябва да направим формула, която да сравнява позиция по позиция стойностите в клетките в колони А и В и да върне като резултат номера на позицията, на която има разминаване. Ако са повече от една – да ги даде като списък, разделен със запетая. В отделна колона трябва да са изброени и съответните символи.
Ето как изглежда крайният резултат:
Решение с традиционни формули
Това решение ще работи във версии на Excel преди 2021 и Офис 365. В тези версии няма поддръжка за динамични масиви, така че решението изисква помощна таблица.
Помощната таблица съдържа 10 колони, защото дължината на най-дългото име е 9 символа. Нищо не пречи да бъде и 100 колони за по-дълъг текст. На първия ред изброяваме поредните номера на позициите – 1, 2, 3 …10
Определяне на позициите с различни символи
=(NOT(MID($A2, E$1, 1)=MID($B2, E$1, 1))*1)*E$1
Формулата сравнява символите от две клетки (A2 и B2), символ по символ, и ако символите на дадена позиция са различни, връща номера на тази позиция. В противен случай връща нула.
Нека да разтълкуваме формулата част по част:
MID($A2, E$1, 1)
Извлича символа на позицията, определена с числото в клетка E$1 от клетка A2. В E1 пише 1, това ще върне 1-ия символ от текста в A2, т.е. „Г“.
MID($B2, E$1, 1)
Същото, но за B2 — връща 1-ия символ от другия текст. В случая отново „Г“
(MID(…) = MID(…))
Връща TRUE, ако символите са еднакви, и FALSE, ако са различни.
NOT(…)
Обръща логиката:
ако символите съвпадат → TRUE → NOT(TRUE) → FALSE. ако са различни → FALSE → NOT(FALSE) → TRUE
А защо умножаваме с 1? Това превръща логическата стойност в число. Като умножим TRUE с 1, получава се 1. Като умножим FALSE с 1 – резултатът е нула (0). Така получаваме съвкупност от нули и единици
Накрая, ако символите са различни, умножаваме 1 по стойността в клетка E1 (т.е. номерът на позицията). Ако са еднакви — 0 * позицията → 0. По този начин във всеки ред получаваме списък от нули и номерата на позициите, на които има разлика в символите.
Формулите са копирани надясно и надолу в областта E2:N5 и така получаваме таблица, съдържаща нули и числа.
Списък на позициите с различни символи
Тук формулата изглежда страховито, но е по-проста, отколкото изглежда на пръв поглед:
Какво реално прави тази формула:
1. CONCATENATE(E2,“,“,F2,“,“,G2,“,“,…,M2,“,“,N2)
Обединява стойностите от клетките E2 до N2, като между всяка стойност слага запетая.
На ред 2 резултатът от CONCATENATE(…) ще бъде нещо като: 0,2,0,0,0,0,9,0,12,
Това много прилича на списъка, който целим, но има излишни нули и запетая в края, които трябва да се премахнат.
По принцип за премахване на текст в Excel се използват функциите SUBSTITUTE или REPLACE. В случая аз се спрях на SUBSTITUTE.
2. Първи SUBSTITUTE(…,“0,“,““)
Премахва всички последователни „0,“ (нула, последвана от запетая) от получения низ. Така:
0,2,0,0,0,0,9,0,12, → 2,9,12,0
Но остава последната нула, предшествана от запетая. Затова правим и втори SUBSTITUTE
3. Втори SUBSTITUTE(…,“,0″,““)
Премахва останалите единични нули, които не са последвани от запетая, а са предшествани от запетая (т.е. накрая на реда).
В обобщение, тази формула:
- Премахва „0,“ където се среща в началото или средата
- Премахва „,0“ накрая
- Оставя само числата, различни от 0, разделени със запетая, т.е. позициите, на които има разминаване
Накрая в колона Р получаваме това, което целим – списък от позициите, на които символите в двете клетки са различни.
Списък на символите, които са различни
Логиката, която се използва във формулите е абсолютно същата, само че формулата вече не връща 0 и 1, а 0 и самия символ, който е различен. Това, което ви изглежда като по-дребна нула, всъщност е буква „о“.
Втората формула е абсолютно същата, защото отново събираме в списък някакви стойности
Както виждате задачата може да бъде решена сравнително елегантно с класически Ексел функции. Но има някои недостатъци – използват се помощни колони. Но най-вече – ето тази част E2,“,“,F2,“,“,G2,“,“,H2,“,“,I2,“,“,J2,“,“,K2,“,“,L2,“,“,M2,“,“,N2 се въвежда на ръка. Което е предпоставка за грешки, особено, ако трябва да го правим не за 10 клетки, а примерно за 30. Или 50.
За сметка на това пък решението ще работи във всяка една версия на Ексел.
След тези дълги и за писане, и за осмисляне формули – нека да видим как стават нещата в модерния Excel.
Решение с динамични формули
След като разгледахме класическия подход, нека видим как в модерния Ексел може да се постигне същия резултат само с една формула. За целта ще използваме комбинация от динамични функции. Те бяха въведени в Excel, за да позволят по-лесна и гъвкава обработка на данни. Настоящият казус е много добър пример за това.
Списък с позициите на различните символи
Вижте как с една единствена формула (без помощни колони) е изведен списъкът с позициите, където има разлика
Как работи формулата
Магията е във функцията FILTER.
Тя филтрира дадена област от стойности по определен признак. Съответно има два параметъра:
1. Област със стойности
Тук областта със стойности се задава от функцията SEQUENCE. Тази функция създава редица от числа като започва от стартово число (параметър), увеличава всяко следващо число с определена стъпка (втори параметър) и спира, когато редицата достигне определен брой (трети параметър). От тези три параметъра абсолютно задължителен е само третият – колко числа да има редицата. Ако другите два не са зададени, приема се по подразбиране 1. Нашата функция SEQUNCE има само един параметър, затова Excel автоматично приема, че трябва да го използва за броя на числата в редицата и приема, че тя започва от 1 и всяко следващо е с 1 по-голямо от предишното.
А броят на числата е зададен с функцията LEN, която определя дължината на текста в дадена клетка. С други думи SEQUENCE(LEN(A2)) ще върне редица от числа 1,2,3,4 защото текстът в клетка А2 (Гошо) е има 4 символа. А това всъщност е списък с всички позиции на текста.
2. Критерии
Критериите във функцията FILTER винаги са такива, че връщат TRUE/FALSE
В случая това е познатото ни сравнение
=NOT(MID(A2, SEQUENCE(LEN(A2)), 1) = MID(B2, SEQUENCE(LEN(B2)), 1)
Само че, понеже работим с динамични функции, няма нужда от допълнителни колони. Ексел сам разбира, че трябва да обходи позиция по позиция текста в клетка А2 и да го сравни със съответната позиция от текста в В2.
След като със SEQUENCE сме създали списък с позициите, с FILTER сме филтрирали тези, в които има разлики, накрая TEXTJOIN ги обединява в един ред, разделени със запетаи
И всичко това – с една единствена формула
Списъкът със самите символи
За него се използва същата логика, само че с MID извличаме символа на съответната позиция.
Заключение
В тази статия разгледахме един много специфичен казус с грешни данни. Съответно и решението е доста специфично. Видяхме как можем да го постигнем с класически Ексел формули и с модерните динамични такива. Ето накратко предимствата и недостатъците на двата вида формули:
Класически формули (Legacy Excel):
➕ Работят във всички версии на Excel
➖ Изискват помощни колони и копиране
➖ Изисква ръчно въвеждане на много клетки в CONCATENATE функцията.
Динамични формули (модерен Excel):
➕ Една компактна формула
➕ Без досадно въвеждане на много клетки
➖ Работи само в Excel 365+ / 2021+
Осъзнавам, че представените формули са малко трудни за осмисляне по картинка. Затова може да свалите файла с решенията от тук и да ги разгледате на спокойствие.
Но ако трябва да си вземете едно нещо от статията, то нека това да бъде, че с новите динамични формули можем да правим обработки на данни и пресмятания много по-лесно и гъвкаво.