Общи формули за почистване на данни в Excel

Excel формули

От години използвам публикацията като ресурс, за да не просто опиша как да се правят нещата, но и да запазя запис за себе си, който да търся по-късно! Днес имахме клиент, който ни предаде файл с данни за клиенти, който беше катастрофа. Почти всяко поле е било неправилно форматирано и; в резултат на това не успяхме да импортираме данните. Въпреки че има някои страхотни добавки за Excel за почистване с помощта на Visual Basic, ние стартираме Office за Mac, който няма да поддържа макроси. Вместо това търсим прави формули, които да подпомогнат. Мислех, че ще споделя някои от тях тук, само за да могат други да ги използват.

Премахване на нецифрени знаци

Системите често изискват телефонни номера да се вмъкват в конкретна, 11-цифрена формула с код на държавата и без препинателни знаци. Хората обаче често въвеждат тези данни с тирета и точки. Ето една чудесна формула за премахване на всички нецифрени знаци в Excel. Формулата преглежда данните в клетка А2:

=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

Сега можете да копирате получената колона и да използвате Редактиране> Поставяне на стойности за записване на данните с правилно форматиран резултат.

Оценете няколко полета с ИЛИ

Често изчистваме непълни записи от импорт. Потребителите не осъзнават, че не винаги трябва да пишете сложни йерархични формули и че вместо това можете да напишете оператор OR. В този пример по-долу искам да проверя A2, B2, C2, D2 или E2 за липсващи данни. Ако липсват някакви данни, ще върна 0, в противен случай 1. Това ще ми позволи да подредя данните и да изтрия непълните записи.

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

Подрязване и свързване на полета

Ако вашите данни имат полета Име и Фамилия, но импортирането им има поле с пълно име, можете да свържете полетата заедно, като използвате вградената функция за конкатенация на Excel, но не забравяйте да използвате TRIM, за да премахнете празните интервали преди или след текст. Обгръщаме цялото поле с TRIM, в случай че едно от полетата няма данни:

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

Проверете за валиден имейл адрес

Доста проста формула, която търси както @, така и. в имейл адрес:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

Извличане на собствено и фамилно име

Понякога проблемът е обратен. Вашите данни имат поле с пълно име, но трябва да анализирате собственото и фамилното име. Тези формули търсят интервала между собственото и фамилното име и при необходимост вземат текст. ИТ също се справя, ако няма фамилно име или има празен запис в A2.

=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))

И фамилното име:

=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")

Ограничете броя на символите и добавете ...

Искали ли сте някога да изчистите мета описанията си? Ако искате да изтеглите съдържание в Excel и след това да отрежете съдържанието за използване в полето за мета описание (150 до 160 знака), можете да го направите, като използвате тази формула от Моето място. Той чисто разбива описанието в интервал и след това добавя ...:

=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)

Разбира се, те не са предназначени да бъдат изчерпателни ... просто някои бързи формули, които да ви помогнат да започнете да скочите! Какви други формули намирате да използвате? Добавете ги в коментарите и ще ви дам кредит, докато актуализирам тази статия.

Какво мислите?

Този сайт използва Akismet за намаляване на спама. Научете как се обработват данните за коментарите ви.