Excel-kaavat yleiseen tietojen puhdistamiseen
Olen käyttänyt julkaisua vuosia resurssina kertoessani, miten asioita tehdään ja pitääkseni kirjaa itselleni, jotta voin katsoa sitä myöhemmin! Asiakas antoi meille asiakastietotiedoston, joka oli katastrofi. Käytännössä kaikki kentät oli muotoiltu väärin, minkä vuoksi emme voineet tuoda tietoja. Vaikka Excelissä on joitain hienoja lisäosia puhdistamiseen Visual Basicin avulla, käytämme Office for Macia, joka ei tue makroja. Sen sijaan etsimme suoria kaavoja avuksi. Ajattelin jakaa joitain niistä tänne, jotta voit käyttää niitä.
Poista ei-numeeriset merkit
Järjestelmät vaativat usein puhelinnumeroita, jotka on lisätty tiettyyn 11-numeroiseen kaavaan maakoodin kanssa ilman välimerkkejä. Ihmiset kuitenkin syöttävät nämä tiedot usein väliviivojen ja pisteiden avulla. Tässä on erinomainen kaava poistamalla kaikki ei-numeeriset merkit Excelissä. Kaava tarkastelee solun A2 tietoja:
=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))
Voit kopioida tuloksena olevan sarakkeen ja käyttää sitä Muokkaa> Liitä arvot kirjoittaa tiedot oikein muotoillun tuloksen kanssa.
Arvioi useita kenttiä OR: lla
Tyhjennämme usein epätäydelliset tietueet tuonnista. Käyttäjät eivät ymmärrä, että sinun ei aina tarvitse kirjoittaa monimutkaisia hierarkkisia kaavoja ja että voit kirjoittaa sen sijaan TAI-käskyn. Haluan tarkistaa A2, B2, C2, D2 tai E2 puuttuvien tietojen varalta alla olevassa esimerkissä. Jos tietoja puuttuu, palautan 0; muussa tapauksessa a 1. Sen avulla voin lajitella tiedot ja poistaa epätäydelliset tietueet.
=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)
Trimmaus- ja ketjutuskentät
Jos tiedoissasi on Etu- ja Sukunimi-kentät, mutta tuonnissasi on koko nimikenttä, voit ketjuttaa kentät siististi yhteen sisäänrakennetun Excel-funktion Concatenate avulla, mutta muista käyttää TRIM-työkalua poistaaksesi tyhjät välilyönnit ennen tai jälkeen teksti. Käärimme koko kentän TRIM:iin, jos yhdessä kentissä ei ole tietoja:
=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))
Tarkista kelvollinen sähköpostiosoite
Melko yksinkertainen kaava, joka etsii sekä @- että . sähköpostiosoitteeseen (ei RFC standardi
):=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))
Pura etu- ja sukunimet
Joskus ongelma on päinvastainen. Tiedoissasi on koko nimikenttä, mutta sinun on jäsennettävä etu- ja sukunimi. Nämä kaavat etsivät välilyönnin etu- ja sukunimen väliltä ja tarttuvat tekstiin tarvittaessa. Se käsittelee myös, jos A2:ssa ei ole sukunimeä tai tyhjää merkintää.
=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))
Ja sukunimi:
=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")
Rajoita merkkien määrää ja lisää…
Oletko koskaan halunnut puhdistaa metakuvauksiasi? Jos haluat vetää sisältöä Exceliin ja sitten leikata sisältöä käytettäväksi Meta Description -kentässä (150–160 merkkiä), voit tehdä sen tällä kaavalla. Se katkaisee kuvauksen selkeästi välilyönnillä ja lisää sitten…:
=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)
Näiden ei tietenkään ole tarkoitus olla kattavia... vain muutamia nopeita kaavoja, joiden avulla pääset alkuun! Mitä muita kaavoja huomaat käyttäväsi? Lisää ne kommentteihin, niin kerron sinulle, kun päivitän tätä artikkelia.