Mga Formula ng Excel Para sa Karaniwang Paglilinis ng Data

Mga Formula ng Paglinis ng Data ng Excel

Sa loob ng maraming taon, ginamit ko ang publication bilang isang mapagkukunan upang hindi lamang ilarawan kung paano gumawa ng mga bagay, ngunit upang mapanatili rin ang isang tala para sa aking sarili na maghanap sa paglaon! Ngayon, mayroon kaming kliyente na nagbigay sa amin ng isang file ng data ng customer na isang sakuna. Halos bawat patlang ay maling anyo at; bilang isang resulta, hindi namin nagawang i-import ang data. Habang may ilang magagaling na mga add-on para sa Excel na gawin ang paglilinis gamit ang Visual Basic, pinapatakbo namin ang Office for Mac na hindi susuportahan ang macros. Sa halip, naghahanap kami ng mga tuwid na formula na tutulong. Naisip kong ibahagi ang ilan sa mga narito para lamang magamit ng iba.

Alisin ang Mga Hindi Character na Hindi Numeric

Ang mga system ay madalas na nangangailangan ng mga numero ng telepono upang maipasok sa isang tukoy, 11-digit na pormula na may country code at walang bantas. Gayunpaman, ang mga tao ay madalas na ipinasok ang data na ito na may mga gitling at mga yugto sa halip. Narito ang isang mahusay na formula para sa inaalis ang lahat ng mga hindi numerong character sa Excel. Sinusuri ng formula ang data sa cell A2:

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

Ngayon ay maaari mong kopyahin ang nagresultang haligi at gamitin I-edit> I-paste ang Mga Halaga upang isulat ang data na may maayos na nai-format na resulta.

Suriin ang Maramihang Mga Patlang sa isang OR

Madalas naming pinupurga ang mga hindi kumpletong tala mula sa isang pag-import. Hindi namalayan ng mga gumagamit na hindi mo palaging magsusulat ng mga kumplikadong hierarchical na pormula at sa halip ay maaari kang magsulat ng isang pahayag O. Sa halimbawang ito sa ibaba, nais kong suriin ang A2, B2, C2, D2, o E2 para sa nawawalang data. Kung may nawawalang anumang data, babalik ako ng 0, kung hindi man ay isang 1. Papayagan akong ayusin ang pag-order ng data at tanggalin ang mga tala na hindi kumpleto.

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

Gupitin at Magkakasabay na Mga Patlang

Kung ang iyong data ay may mga patlang ng Una at Huling Pangalan, ngunit ang iyong pag-import ay may isang buong patlang ng pangalan, maaari mong pagsamahin ang mga patlang nang maayos gamit ang built in na Excel Function Concatenate, ngunit tiyaking gumamit ng TRIM upang alisin ang anumang walang laman na mga puwang bago o pagkatapos ng text Binalot namin ang buong patlang ng TRIM sa kaganapan na ang isa sa mga patlang ay walang data:

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

Suriin ang wastong Email Address

Isang medyo simpleng formula na naghahanap para sa parehong mga @ at. sa isang email address:

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

I-extract ang Una at Huling Mga Pangalan

Minsan, ang problema ay kabaligtaran. Ang iyong data ay may isang buong patlang ng pangalan ngunit kailangan mong i-parse ang una at huling mga pangalan. Ang mga formula na ito ay naghahanap ng puwang sa pagitan ng una at apelyido at grab text kung kinakailangan. Humahawak din ang IT kung walang apelyido o mayroong isang blangkong entry sa A2.

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

At ang apelyido:

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

Limitahan ang Bilang ng Mga Character at Idagdag…

Nais mo bang linisin ang iyong mga paglalarawan sa meta? Kung nais mong hilahin ang nilalaman sa Excel at pagkatapos ay i-trim ang nilalaman para magamit sa isang patlang ng Paglalarawan ng Meta (150 hanggang 160 na mga character), magagawa mo iyon gamit ang pamamaraang ito mula sa Ang Aking Spot. Malinis nitong binabasag ang paglalarawan sa isang puwang at pagkatapos ay idinagdag ang…:

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

Siyempre, ang mga ito ay hindi sinadya upang maging komprehensibo ... ilang mabilis na mga formula lamang upang matulungan kang makapagsimula! Ano ang iba pang mga formula na nakikita mong ginagamit mo? Idagdag ang mga ito sa mga komento at bibigyan kita ng kredito habang ina-update ko ang artikulong ito.

Ano sa tingin ninyo?

Ang site na ito ay gumagamit ng Akismet upang mabawasan ang spam. Alamin kung paano naproseso ang data ng iyong komento.