Excel формули за заедничко чистење податоци

Формули за чистење податоци на Excel

Со години, ја користев публикацијата како ресурс за да не опишам само како да правам работи, туку и да чувам евиденција за себе, за да побарам подоцна! Денес, имавме клиент кој ни подари датотека со податоци за клиенти што беше катастрофа. Практично, секое поле беше погрешно форматирано и; како резултат, не можевме да ги увеземе податоците. Додека има неколку одлични додатоци за Excel за да го расчисти користејќи Visual Basic, ние работиме Office for 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))

Сега можете да ја копирате добиената колона и да ја користите Уреди> Вметни вредности да пишува над податоците со правилно форматиран резултат.

Оценете повеќе полиња со ИЛИ

Ние често прочистуваме нецелосни записи од увоз. Корисниците не сфаќаат дека не мора секогаш да пишувате сложени хиерархиски формули и дека наместо тоа можете да напишете изјава ИЛИ. Во овој пример подолу, сакам да проверам 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)))

Извлечете ги имињата и презимињата

Понекогаш, проблемот е спротивен. Вашите податоци имаат поле со полно име, но треба да ги анализирате имињата и презимињата. Овие формули бараат простор помеѓу името и презимето и зграбуваат текст каде што е потребно. Исто така, ИТ се справува ако нема презиме или има празен запис во А2.

=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 за намалување на спам. Научете како се обработува вашиот коментар.