Fformiwlâu Glanhau Data Cyffredin yn Excel

fformwlâu excel

Am flynyddoedd, rwyf wedi defnyddio'r cyhoeddiad fel adnodd i nid yn unig ddisgrifio sut i wneud pethau, ond hefyd i gadw cofnod i mi fy hun edrych i fyny yn nes ymlaen! Heddiw, roedd gennym gleient a roddodd ffeil ddata cwsmeriaid inni a oedd yn drychineb. Roedd bron pob maes yn cael ei gamffurfio ac; o ganlyniad, nid oeddem yn gallu mewnforio'r data. Er bod rhai ychwanegiadau gwych i Excel wneud y gwaith glanhau gan ddefnyddio Visual Basic, rydym yn rhedeg Office for Mac na fydd yn cefnogi macros. Yn lle, rydym yn edrych am fformiwlâu syth i gynorthwyo. Roeddwn i'n meddwl y byddwn i'n rhannu rhai o'r rheiny yma er mwyn i eraill eu defnyddio.

Dileu Cymeriadau Di-rif

Yn aml mae systemau'n ei gwneud yn ofynnol i rifau ffôn gael eu mewnosod mewn fformiwla benodol, 11 digid gyda'r cod gwlad a dim atalnodi. Fodd bynnag, mae Folks yn aml yn mewnbynnu'r data hwn gyda thaenau a chyfnodau yn lle. Dyma fformiwla wych ar gyfer cael gwared ar bob nod nad yw'n rhifol yn Excel. Mae'r fformiwla'n adolygu'r data yng nghell 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))

Nawr gallwch chi gopïo'r golofn sy'n deillio ohoni a'i defnyddio Golygu> Gludo Gwerthoedd i ysgrifennu dros y data gyda'r canlyniad wedi'i fformatio'n gywir.

Gwerthuso Meysydd Lluosog gydag OR

Rydym yn aml yn glanhau cofnodion anghyflawn o fewnforio. Nid yw defnyddwyr yn sylweddoli nad oes rhaid i chi ysgrifennu fformwlâu hierarchaidd cymhleth bob amser ac y gallwch ysgrifennu datganiad NEU yn lle. Yn yr enghraifft hon isod, rwyf am wirio A2, B2, C2, D2, neu E2 am ddata coll. Os oes unrhyw ddata ar goll, rydw i'n mynd i ddychwelyd 0, fel arall 1. Bydd hynny'n caniatáu imi ddidoli trefn y data a dileu'r cofnodion sy'n anghyflawn.

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

Caeau Trimio a Concatenate

Os oes gan eich data feysydd Enw Cyntaf ac Olaf, ond mae gan eich mewnforio faes enw llawn, gallwch chi gyd-fynd â'r caeau gyda'i gilydd yn daclus gan ddefnyddio'r Concatenate Swyddogaeth Excel adeiledig, ond gwnewch yn siŵr eich bod chi'n defnyddio TRIM i gael gwared ar unrhyw fannau gwag cyn neu ar ôl y testun. Rydyn ni'n lapio'r maes cyfan gyda TRIM os nad oes gan un o'r meysydd ddata:

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

Gwiriwch am Cyfeiriad E-bost Dilys

Fformiwla eithaf syml sy'n edrych am y @ a. mewn cyfeiriad e-bost:

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

Detholiad Enwau Cyntaf ac Olaf

Weithiau, y broblem i'r gwrthwyneb. Mae gan eich data faes enw llawn ond mae angen i chi rannu'r enwau cyntaf a'r enwau olaf. Mae'r fformwlâu hyn yn edrych am y gofod rhwng yr enw cyntaf a'r enw olaf ac yn cydio mewn testun lle bo angen. Mae TG hefyd yn trin os nad oes enw olaf neu os oes cofnod gwag yn A2.

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

A'r enw olaf:

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

Cyfyngu ar Nifer y Cymeriadau ac Ychwanegu…

Oeddech chi erioed wedi bod eisiau glanhau'ch disgrifiadau meta? Os oeddech chi am dynnu cynnwys i mewn i Excel ac yna trimio'r cynnwys i'w ddefnyddio mewn maes Disgrifiad Meta (150 i 160 nod), gallwch chi wneud hynny gan ddefnyddio'r fformiwla hon o Fy Smotyn. Mae'n torri'r disgrifiad mewn man yn lân ac yna'n ychwanegu'r…:

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

Wrth gwrs, nid yw'r rhain i fod i fod yn gynhwysfawr ... dim ond rhai fformiwlâu cyflym i'ch helpu chi i ddechrau naid! Pa fformiwlâu eraill ydych chi'n eu defnyddio eich hun? Ychwanegwch nhw yn y sylwadau a byddaf yn rhoi credyd i chi wrth i mi ddiweddaru'r erthygl hon.

Beth ydych chi'n feddwl?

Mae'r wefan hon yn defnyddio Akismet i leihau sbam. Dysgwch sut mae eich data sylwadau yn cael ei brosesu.