Yakajairika Dhata Kuchenesa Mafomu muExcel

kukunda mazano

Kwemakore, ini ndashandisa kuburitswa sechishandiso kwete kungotsanangudza maitiro ekuita zvinhu, asi zvakare kuchengetedza rekodhi yangu yekutarisa kumusoro! Nhasi, isu tanga tine mutengi uyo akatipa isu yevatengi data faira yaive njodzi. Anenge ndima dzese dzaisanzwisiswa uye; semhedzisiro, isu hatina kukwanisa kuendesa iyo data. Kunyange paine zvimwe zvakanaka zvekuwedzera zveExcel zvekuchenesa uchishandisa Visual Basic, isu tinomhanya Office yeMac iyo isingatsigire macros. Pane kudaro, isu tinotarisa nzira dzakatwasuka dzekubatsira. Ini ndaifunga kuti ndichagovana vamwe veava pano kuitira kuti vamwe vagone kuashandisa.

Bvisa Asiri-Nhamba Nhamba

Masystem anowanzoda kuti nhamba dzenhare dziiswe mune yakatarwa, 11-manhamba fomu nekodhi yenyika uye isina nyora. Nekudaro, vanhu vanowanzo pinda iyi dhata nemadhishi uye nguva panzvimbo. Heano akakura fomula ye kubvisa ese asiri manhamba mavara muExcel. Iyo fomula inoongorora iyo data muchitokisi 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))

Iye zvino unogona kuteedzera ikholamu inoguma uye shandisa Hora> Namatira Maitiro kunyora pamusoro peiyo data nemhedzisiro yakanyatsorongwa.

Ongorora Akawanda Minda ine OR

Isu tinowanzo bvisa zvisina kukwana marekodhi kubva kune yekunze. Vashandisi havazive kuti iwe haufanire kugara uchinyora mafomati eheerarchical akaomarara uye kuti iwe unogona kunyora chirevo OR panzvimbo. Mumuenzaniso uyu pazasi, ini ndoda kutarisa A2, B2, C2, D2, kana E2 yekushaya data. Kana paine chero dhata isipo, ndiri kuzodzosera 0, zvikasadaro a 1. Izvo zvinondibvumidza kuti ndirongedze dhata uye kudzima marekodhi asina kukwana.

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

Trim uye Concatenate Minda

Kana yako data iine zita reKutanga neRokupedzisira minda, asi kupinza kwako kune zita rakazara munda, unogona kubatanidza minda pamwe zvakanyatsonaka uchishandisa yakavakirwa muExpcel Function Concatenate, asi ive shuwa kushandisa TRIM kubvisa chero nzvimbo dzisina chinhu pamberi kana mushure me chinyorwa. Isu tinoputira iyo munda wese neTRIM muchiitiko chekuti imwe yeminda haina data:

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

Tarisa uone Yakakodzera Email Kero

Fomula yakareruka inotarisa zvese @ uye. mune email kero:

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

Bvisa Ekutanga uye Ekupedzisira Mazita

Dzimwe nguva, dambudziko rinopesana. Dhata rako rine zita rakazara munda asi iwe unofanirwa kuongorora ekutanga uye ekupedzisira mazita. Aya mafomula anotarisa nzvimbo pakati pezita rekutanga uye rekupedzisira uye vobata mavara pazvinenge zvakakodzera. Iyo inobatawo kana pasina zita rekupedzisira kana paine isina chinhu yekupinda muA2.

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

Uye zita rekupedzisira:

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

Limit the Number of Characters and Add…

Wakamboda here kuchenesa yako meta tsananguro? Kana iwe waida kudhonza zvemukati muExcel uye wobva wadimbura zvemukati zvekushandisa mune Meta Tsananguro munda (150 kusvika 160 mavara), unogona kuzviita uchishandisa iyi fomula kubva My Spot. Iyo inonyatso pwanya rondedzero pane imwe nzvimbo uye ndokuwedzera iyo…:

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

Ehezve, izvi hazvirevi kuti zvive zvakakwana ... ingori nzira dzinokurumidza kukubatsira kuti utange kutanga! Ndeapi mamwe mafomula aunozviwana iwe uchishandisa? Vawedzere mune zvakataurwa uye ini ndichakupa iwe chikwereti apo ini ndichigadzirisa ichi chinyorwa.

Unofungei?

Nzvimbo iyi inoshandisa Akismet kuderedza spam. Dzidza kuti sei deta yako inoshandiswa.