Yleiset tietojen puhdistuskaavat Excelissä

Excel-kaavat

Vuosien ajan olen käyttänyt julkaisua resurssina paitsi kuvailemaan miten tehdä asioita, myös pitää itselleni kirjaa etsiäksesi myöhemmin! Tänään meillä oli asiakas, joka antoi meille katastrofin aiheuttaneen asiakastiedoston. Lähes jokainen kenttä oli muotoiltu väärin ja; tämän seurauksena emme voineet tuoda tietoja. Vaikka Excelissä on hienoja lisäosia puhdistuksen suorittamiseen Visual Basicilla, suoritamme Office for Macin, joka ei tue makroja. Sen sijaan etsimme suoria kaavoja avuksi. Ajattelin jakaa joitain niistä täällä vain, jotta muut voivat käyttää niitä.

Poista ei-numeeriset merkit

Järjestelmät edellyttävät usein, että puhelinnumerot lisätään tiettyyn 11-numeroiseen kaavaan, jossa on maakoodi eikä välimerkkejä. Ihmiset kuitenkin syöttävät nämä tiedot usein väliviivoilla ja pisteillä. Tässä on hieno 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 sen sijaan TAI-lauseen. 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-funktion ketjutusta, mutta muista poistaa TRIM-toiminnolla 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 tilaa etu- ja sukunimen välillä 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)

Tietenkään näiden ei ole tarkoitus olla kattavia ... vain joitain nopeita kaavoja, joiden avulla pääset alkuun! Mitä muita kaavoja löydät itsesi käyttävän? Lisää ne kommentteihin ja annan sinulle kunniaa päivittäessäni tätä artikkelia.

Mitä mieltä olet?

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