Excel-kaavat yleiseen tietojen puhdistamiseen

Excelin tietojen puhdistuskaavat

Olen käyttänyt julkaisua vuosia resurssina kuvaamaan vain sitä, miten tehdä asioita, mutta myös pitämään kirjaa itselleni, jotta voin katsoa myöhemmin! Tänään meillä oli asiakas, joka luovutti meille katastrofaalisen asiakastiedoston. Lähes jokainen kenttä oli muotoiltu väärin ja; Tämän seurauksena emme voineet tuoda tietoja. Vaikka Excelissä on hienoja lisäosia puhdistukseen Visual Basicin avulla, käytämme Office for Macia, joka ei tue makroja. Sen sijaan etsimme suoria kaavoja avuksi. Ajattelin jakaa joitain niistä täällä, jotta muut voivat käyttää niitä.

Poista ei-numeeriset merkit

Järjestelmät vaativat usein puhelinnumeroiden lisäämisen tiettyyn 11-numeroiseen kaavaan, jossa on maakoodi ja ilman välimerkkejä. Ihmiset kuitenkin syöttävät nämä tiedot usein väliviivoilla ja pisteillä. Tässä on loistava 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))

Nyt voit kopioida tuloksena olevan sarakkeen ja käyttää Muokkaa> Liitä arvot kirjoittaa tiedot oikein muotoillun tuloksen kanssa.

Arvioi useita kenttiä OR: lla

Puhdistamme puutteelliset tietueet usein tuonnista. Käyttäjät eivät ymmärrä, että sinun ei aina tarvitse kirjoittaa monimutkaisia ​​hierarkkisia kaavoja ja että voit kirjoittaa OR-lauseen sen sijaan. Tässä alla olevassa esimerkissä haluan tarkistaa, puuttuuko A2, B2, C2, D2 tai E2 tietoja. Jos tietoja puuttuu, palautan nollan, muuten 0. Tämän ansiosta voin lajitella tiedot järjestyksessä ja poistaa puutteelliset tietueet.

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

Trimmaus- ja ketjutuskentät

Jos tiedoissasi on Etunimi ja Sukunimi -kentät, mutta tuonnissa on koko nimikenttä, voit ketjuttaa kentät yhteen siististi käyttämällä sisäänrakennettua Excel-toimintojen ketjutusta, mutta muista poistaa TRIM: n avulla tyhjät tilat ennen tai jälkeen teksti. Kääritämme koko kentän TRIM: llä, jos yhdellä kentistä ei ole tietoja:

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

Tarkista kelvollinen sähköpostiosoite

Melko yksinkertainen kaava, joka etsii sekä @ että. sähköpostiosoitteessa:

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

Pura etu- ja sukunimet

Joskus ongelma on päinvastainen. Tietoissasi on koko nimikenttä, mutta sinun on jäsennettävä etu- ja sukunimet. Nämä kaavat etsivät etu- ja sukunimen välistä tilaa ja tarttuvat tekstiin tarvittaessa. IT hoitaa myös, jos sukunimeä ei ole tai A2: ssa on 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ää…

Halusitko koskaan puhdistaa metakuvauksesi? Jos haluat vetää sisältöä Exceliin ja sitten leikata sisältöä Sisällönkuvaus-kentässä (150-160 merkkiä) käytettäväksi, voit tehdä sen käyttämällä tätä kaavaa Oma Spot. Se rikkoo kuvauksen puhtaasti 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 joitain nopeita kaavoja, joiden avulla pääset alkuun! Mitä muita kaavoja käytät? Lisää ne kommentteihin ja annan sinulle kunnian päivitessäni tätä artikkelia.

Mitä mieltä olet?

Tämä sivusto käyttää Akismetiä roskapostin vähentämiseksi. Lue, miten kommenttitietosi käsitellään.