Általános adattisztítási képletek az Excelben

excel 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 is 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ő tisztí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. 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ásnak teljes névmezője van, akkor a mezőket szépen összefűzheti a beépített Excel függvény összefűzésével, de a TRIM használatával távolítsa el az üres tereket 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 a kereszt- és vezetéknév közötti helyet, és szükség esetén megragadják a szöveget. Az IT azt 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, amelyek segítenek neked az indulásban! Milyen más képleteket talál magának? Adja 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.