Excel képletek a közös adatok tisztításához

Excel adattisztítási képletek

Évek óta a kiadványt erőforrásként használtam arra, hogy ne csak leírjam, hogyan kell csinálni a dolgokat, hanem hogy nyilvántartást vezessek magamról, hogy később utánanézhessek! Ma volt egy ügyfelünk, aki katasztrófának számító ügyféladat-fájlt adott át nekünk. Gyakorlatilag minden mezőt rosszul formáztak, és; ennek következtében nem tudtuk importálni az adatokat. Bár vannak remek kiegészítések az Excel számára a Visual Basic használatával történő takarításhoz, az Office for Mac programot futtatjuk, amely nem támogatja a makrókat. Ehelyett egyenes képleteket keresünk, amelyek segítenek. Gondoltam megosztok néhányat itt, csak hogy mások felhasználhassák őket.

Távolítsa el a nem numerikus karaktereket

A rendszerek gyakran megkövetelik, hogy a telefonszámokat egy meghatározott, 11 számjegyből álló képletbe illesszék be az országkóddal és írásjelek nélkül. Az emberek azonban gyakran kötőjelekkel és pontokkal írják be ezeket az adatokat. Itt van egy nagyszerű képlet az összes nem numerikus karakter eltávolítása az Excelben. A képlet áttekinti az A2 cella adatait:

=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))

Most átmásolhatja a kapott oszlopot és felhasználhatja Szerkesztés> Értékek beillesztése hogy az adatokat megfelelően formázott eredménnyel írjuk át.

Értékeljen több mezőt VAGY-val

A hiányos rekordokat gyakran megtisztítjuk az importból. A felhasználók nem veszik észre, hogy nem mindig kell összetett hierarchikus képleteket írni, és ehelyett OR-utasítást is írhat. Ebben az alábbi példában A2, B2, C2, D2 vagy E2 hiányzó adatokat akarok ellenőrizni. Ha valamilyen adat hiányzik, akkor 0-t, egyébként 1-et adok vissza. Ez lehetővé teszi számomra az adatok rendezését és a hiányos rekordok törlését.

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

Mezők vágása és összefűzése

Ha az adatai tartalmazzák a Keresztnév és a Vezeték mezőt, de az importáláshoz teljes név mező tartozik, akkor a mezőket szépen összefűzhetjük a beépített Excel függvény összefűzéssel, de a TRIM használatával távolítsuk el az üres helyeket a szöveg. A teljes mezőt befogjuk a TRIM-be, ha az egyik mező nem rendelkezik adatokkal:

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

Ellenőrizze az érvényes e-mail címet

Egy nagyon egyszerű képlet, amely a @ és a egy e-mail címen:

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

Bontsa ki a kereszt- és utóneveket

Néha a probléma épp az ellenkezője. Adatai teljes névmezővel rendelkeznek, de ki kell elemeznie az utó- és vezetékneveket. Ezek a képletek megkeresik az utónév és a vezetéknév közötti helyet, és szükség esetén megragadják a szöveget. Az IT akkor is kezeli, ha nincs vezetéknév, vagy üres bejegyzés van az A2-ben.

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

És a vezetéknév:

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

Korlátozza a karakterek számát, és adja hozzá…

Valaha meg akarta tisztítani a metaleírásokat? Ha tartalmat szeretett volna beilleszteni az Excelbe, majd levágni a tartalmat a Meta Description mezőben (150–160 karakter), akkor ezt a következő képlettel teheti meg: Saját folt. Tiszta módon megtöri a leírást egy szóköznél, majd hozzáadja a…:

=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)

Természetesen ezek nem célja, hogy átfogóak legyenek ... csak néhány gyors képlet, amely elősegíti az ugrásszerű indulást! Milyen más képleteket talál magának? Add hozzá őket a megjegyzésekbe, és jóváírom, amint frissítem ezt a cikket.

Mit gondolsz?

Ez az oldal Akismet-et használ a levélszemét csökkentése érdekében. Ismerje meg, hogyan dolgozik a megjegyzésed.