В тази статия ще ви покажа как ефективно да намерите всички невалидни стойности в дадена колона, т.е. тези, които не отговарят на определени условия. Тази ситуация е много често срещана, когато работим с данни за клиенти, доставчици, продукти, материали и т.н. Няколко примера:
- Годината на раждане трябва да е число между 1900 и 2020
- Имейлът трябва да съдържа символа @ и някъде след него трябва да има точка (.com, .info, .gov, . org и т.н.)
- Държавата трябва да е изписана по точно определен начин, обикновено зададен в падащо меню
Търсенето на невалидни стойности в Ексел е доста често срещан бизнес казус. Например когато се мигрират данни от една система в друга, или се подготвя списък за изпращане на писма до клиенти, или просто изчистваме и стандартизираме данните, с които разполагаме. Възможностите са много, но работата винаги опира до преглед на голям брой записи, намиране на всички невалидни стойности в Ексел и тяхната корекция.
За целите на примера, нека допуснем, че имаме следния
Казус
Във фирмата, в която работим, клиентите са разделени на 4 региона – EU, UK, Russia, USA. Нашият списък от клиенти е дълъг няколко хиляди реда и през годините регионите са въвеждани по различен начин, от различни хора и в различни системи. В резултат ситуацията изглежда така:
С цел подобряване на отчетността и бизнес процесите искаме да уеднаквим стойностите на регионите и първата стъпка е да проверим кои от записите в колона В не отговарят на посочените стойности в колона D.
Решение 1
Наскоро приятел ми изпрати клип, на който е демонстрирана една функционалност за откриване на невалидни стойности в Ексел. Отивате на Data > Data validation > Circle Invalid Data
Това огражда с червено тези стойности, които не отговарят на зададения критерий. В случая – списъкът в колона D.
Интересна функционалност, но с много малка практическа приложимост поради следните проблеми:
- Предполага, че в колона В е сложено условие за валидни стойности (Data Validation). Но това може и да не е така. На долната картинка са подчертани две стойности, които не са в списъка, но не са оградени в червено
Как можем да проверим в кои клетки е сложена валидация? Най-напред натискаме CTRL+G за да отворим менюто Go To и там избираме Special
След това избираме Data validation > All
И когато дадем ОК, Ексел маркира всички клетки, в които е въведена валидация
Обърнете внимание, че на ред 6, 7 и 9 валидация има, но там са въведени невалидни стойности. Това се получава когато валидацията е сложена СЛЕД като стойностите са въведени. Тя важи при въвеждане на стойности, но не е и за вече въведените.
- Валидацията може да е променена (изтрита) при въвеждането на данните. Най-простият начин е като се копира и пейстне стойност от друго място в работния лист или дори друг файл. Тогава клетката приема правилата за валидация заедно с копираната стойност (или липсата на такива). Това може би се е случило на ред 13 и 14 от горната картинка
- Най-голямото ограничение на този метод е, че той е удачен за малък брой стойности – 10, 20, 50. Въобще брой, който може лесно да се обхване с поглед. А ако имаме 20 хиляди реда – каква е ползата, че 3 хиляди са оградени с кръгчета? Как ще ги обхванем?
Решение 2
С две думи добре е да знаете горната функционалност в случай, че търсите няколко на брой невалидни стойности (или ви предстои някой тест по Ексел 😉). Но от практична гледна точка по-удачното решение е да се използва VLOOKUP, както е показано по-долу
Така за всички невалидни стойности ще се появи #N/A. От тук нататък вече е много лесно да ги филтрираме с филтър
или с пивот таблица да си направим справка кои са най-често срещаните невалидни стойности.
Виждаме, че в нашия случай те са основно с клиенти от България и Европа, но имаме и няколко от Азия и Африка.
Какви стойности трябва да имат тези клиенти в колоната „Регион“ е един интересен въпрос, който изисква анализ на конкретните данни и комуникация с отговорните звена във фирмата. Целта на тази статия беше да ви покаже ефективен начин да установите бързо и лесно всички невалидни стойности в Ексел.