За втория казус за анализ на данни с Excel , в който става дума за обработка на телефонни номера в Excel, получих 6 различни решения. Благодаря на тези ентусиасти. Много интересно – две от решенията бяха базирани на Text-to-columns, две на формули и две на Power Query. Като две с две не си приличат – всеки от участниците е използвал различна идея и по различен начин е приложил избрания от него инструмент.
Независимо от това подходът на участниците е много подобен. Те са разбили задачата на малки стъпки и поседователно преодоляват предизвикателствата на казуса. Нека да видим кои са
Основните стъпки при обработка на телефонни номера в Excel
1. Изчистване на текста
който се забелязва в някои от номерата („Панчо“, „Манчо“ и др.). Тук предизвикателството е, че този текст се появява на различни места – преди или след номера, има различна дължина и е отделен от цифрите с различни символи – някъде тире, някъде интервал, някъде и двете. Някои участници използват Find & Replace за целта, други формули като REPLACE, SUBSTITUE, трети – вградените функционалности на Power Query. Има участник, който си е направил труда ръчно да ги премахне. Което също е вариант и щом върши работа не трябва да се изключва. Но, разбира се, това е с най-малка ефективност.
2. Премахване на кода на България (359).
Тук има два подхода на действие. Единият е да се замени „359“ с нула или нищо. А другият е да се „отрежат“ 9 символа отдясно на номера с функцията RIGHT или аналогичната End в Power Query. Вторият е малко по-удачен, защото с него се избягва един проблем. Вижте този номер (измислен е случайно): 0888123359. Тук да заменим „359“ с нищо е неправилно, защото „359“ е част от напълно валиден номер, а не код за България. При използването на RIGHT това изобщо не е проблем.
3. Добавяне на нула пред номера
с функция CONCATENATE или друг подобен инструмент се добавя нула пред номера, ако е необходимо. Например номер +359881111111 след изчистването на кода на България няма да има нула отпред, а по условие на казуса тя е необходима.
4. Проверка за валидността на номера.
По условие валидните номера започват с 088, 087 или 089 и са 10 цифрени. Трябва да филтрираме само номерата, които отговарят на тези условия.
5. Премахване на дублиращите се номера.
Тази стъпка не е изрично посочена в условието на задачата. Предполагам затова много от участниците са я пропуснали. Други са се досетили, че няма икономически смисъл да изпратим повтарящи се номера на агенцията. Вероятно няма да заплатим повече, ако тя изпрати 50-100 съобщения повече. Но представете си само някой клиент да се повтаря 3 пъти и да получи по 3 съобщения едновременно. Няма да му стане много приятно и може би няма да увеличим шансовете за продажба с това. Това е малък пример за бизнес ориентирано мислене, когато боравим с данни.
6. Проверка на невалидните номера.
Това пък е пример за изминаване на допълнителната крачка. Както спомена един от участниците – добре, че в допълнителния текст към номерата няма цифри. Представете си например към някой номер да е добавено „Пешо 88Блок“ или „Kir4o 6ofior”, или пък „УВО 99“. Като премахнем текста, тези цифри ще останат и ще се разбъркат страшно много номерата. Възможно е да изпратим търговско съобщение на неподходящи клиенти, от което да пострата репутацията на фирмата. Или просто да ги изпратим на несъществуващ номер. Как може да се подсигури минимален риск от грешки ще видим малко по-надолу.
Примерно решение на казуса
Тук ще дам решение в Power Query, защото смятам, че такова е най-ефективно при обработка на телефонни номера в Excel (а и за всякакъв друг текст всъщност). То спестява най-много време първоначално при създаване на алгоритъма и най-вече впоследсвтие, ако трябва да повтаряме операцията всеки месец, например. В изложението допускам, че читателят има някаква минимална представа какво е Power Query. В случай, че не всичко от обяснението по-долу ви е ясно – винаги може да се свържете с мен, чрез формата за контакт, за да обсъдим.
Започваме като заредим данните в Power Query
След това добавяме една колона (Custom column), в която изчистваме номерата от всякакъв текст. В случая е направено с помощта на функцията Text.Select, което е по-ефективно от Replace
Ето така изглеждат двете колони:
На следващо място с десен бутон върху новата колона избираме Replace values
И заменяме 00359 с 0
В следваща стъпка повтаряме същото, но този път заменяме 359 с 0.
Следва добавяне на една нула отпред на всеки номер, който си няма с тази формула:
Още една допълнителна колона, с която определяме дължината на всеки номер:
И още една допълнителна колона, в която се съдържа “yes”, ако префиксът е 088, 087 или 089. Съответно “no”, ако е нещо друго
В Excel, бихме използвали комбинация от функциите IF, OR и LEFT. В Power Query вместо OR се използва функцията List.ContainsAny, а Text.Start е алтернативата на LEFT.
Последната помощна колона проверява кои от номерата са валидни, т.е. отговарят и на двете условия – да имат валиден префикс и дължината им да е 10 цифри. Формулата по-долу връща TRUE за валидни номера и FALSE за невалидни.
В Excel, такава проверка бихме направили са AND, в Power Query се използва List.ContainsAll.
С това завършва работата по обработка на номерата и можем да пристъпим към
Изготвяне на списъка с номера
Нека заявката с трансформацията на данните (нарекох я Numbers transformation) да бъде т.нар. staging query. Тоест тя ще служи само за обработка на данните, но не и за крайния списък и за проверките, които ще направим. За целта, в лявата част на екрана на Power Query, кликаме с десен бутон върху името на заявката и избираме Reference.
Така ще създадем нова заявка, която използва за източник Numbers transformation. Можем да правим всякакви обработки след това без да засягаме основните. Това е една добра практика, която позволява да имаме различни заявки за различни цели, без да повтаряме всички стъпки по основната обработка в тях. Повече за staging queries и как да ги използвате може да научите от тази чудесна статия.
В новата заявка просто ще филтрираме в колоната Number valid всички стойности, които са TRUE, за да получим списък с валидните номера.
Остава само да премахнем всички колони без тази с номерата и след това с Remove duplicates да премахнем дублиращите се номера
С това нашата задача изглежда решена. По желание можем да сортираме номерата за прегледност.
Допълнителни проверки
Както казахме обаче, възможни са всякакви изненади. Затова не е лошо да си направим още две заявки, които да ни служат за проверка.
С едната ще проверим дали нещо не се е объркало в номерата, които определихме като валидни. Тази заявка е същата като тази за списъка с номерата, но в нея ще оставим и оригиналната колона с данни и ще добавим една проверка дали последните 9 знака в двете колони съвпадат.
Ако нещо се е объркало, например защото оригиналният запис съдържа текст като „Пешо 88Блок“ или „Kir4o 6ofior” би трябвало тук да си проличи.
Втората ни заявка ще съдържа всички невалидни номера. Правим я като реферираме основнота заявка и в колоната Number valid избираме FALSE. След това просто трябва да прегледаме номерата, за да видим дали няма да намерим нещо нередно. Ето така се открива един валиден номер, който при подмяната на 359 с 0 се е объркал.
Ако имаме 1-2 такива – най-добре ръчно да ги добавим към списъка с номерата за агенцията. Но ако са повече – добре е да се поразсъждава каква е причината да ги има. След това евентуално да се промени алгоритъмът на трансформиране на данните, за да се избегне тази грешка.
Заключение
Този простичък наглед казус за обработка на телефонни номера в Excel нагледно показва няколко неща:
Първо, има много начини да се свърши една работа в Excel. Важен е подходът и логическата последователност на работа.
Второ, когато става въпрос за работа с данни, добре е да се замисляме върху крайният резултат и да „направим допълнителната крачка“. В случая пример за това е премахването на дублиращите се номера, но още повече – проверките за валидни номера, определени като невалидни и обратно.
Трето, при изчистването на текстови данни – възможни са всякакви изненади. Ето защо проверките са много важни.
И накрая – Power Query е много мощен инструмент за обработка на данни!