Amafomula Wokuhlanza Idatha Ejwayelekile ku-Excel

ukugqama amafomula

Sekuyiminyaka ngisebenzisa ukushicilelwa njengesisetshenziswa hhayi nje ukuchaza ukuthi zenziwa kanjani izinto, kepha futhi nokugcina irekhodi lami engizolibheka kamuva! Namuhla, besineklayenti elisinikeze idatha yamakhasimende eyinhlekelele. Cishe yonke imikhakha yayiqondiswe kabi futhi; ngenxa yalokho, asikwazanga ukungenisa idatha. Ngenkathi kukhona ezinye izengezo ezinhle ze-Excel zokwenza ukuhlanzwa usebenzisa i-Visual Basic, sisebenzisa i-Office for Mac engeke isekele ama-macros. Esikhundleni salokho, sibheka amafomula aqondile okusiza. Ngicabange ukuthi ngizohlanganyela ezinye zalezi lapha ukuze abanye bazisebenzise.

Susa izinhlamvu ezingezona ezezinombolo

Amasistimu ajwayele ukuthi afune izinombolo zocingo zifakwe kwifomula ethile enamadijithi ayi-11 enekhodi yezwe futhi kungabi nezimpawu zokubhala. Kodwa-ke, abantu bavame ukufaka le datha ngamadeshi nezikhathi esikhundleni salokho. Nayi ifomula enhle ye- kususwa zonke izinhlamvu ezingezona izinombolo ku-Excel. Ifomula ibuyekeza idatha kuseli 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))

Manje usungakopisha ikholomu evelayo bese usebenzisa Hlela> Namathisela Amanani ukubhala phezu kwedatha ngomphumela ofomethwe kahle.

Linganisa Izinkambu Eziningi nge-OR

Sivame ukukhipha amarekhodi angaphelele kokungeniswa. Abasebenzisi abaqapheli ukuthi akudingeki ngaso sonke isikhathi ukuthi ubhale amafomula anzima kakhulu nokuthi ungabhala isitatimende se-OR esikhundleni salokho. Kulesi sibonelo esingezansi, ngifuna ukubheka i-A2, B2, C2, D2, noma i-E2 ngemininingwane engekho. Uma noma iyiphi idatha ilahlekile, ngizobuyisa u-0, kungenjalo ngingu-1. Lokho kuzongivumela ukuthi ngihlele i-data futhi ngisuse amarekhodi angaphelele.

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

Izinkundla ze-Trim and Concatenate

Uma idatha yakho inezinkambu zegama lokuqala nesibongo, kepha ukungenisa kwakho kunenkambu yamagama ephelele, ungahlanganisa izinkambu ndawonye ngobunono usebenzisa i-Excel Function Concatenate eyakhelwe, kepha qiniseka ukusebenzisa i-TRIM ukususa noma yiziphi izikhala ezingenalutho ngaphambi noma ngemuva kwe umbhalo. Sisonga yonke insimu nge-TRIM uma kwenzeka enye yezinkambu ingenayo idatha:

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

Bheka ikheli le-imeyili elivumelekile

Ifomula elula elula ebheka kokubili @ no. ekhelini le-imeyili:

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

Khipha Amagama Wokuqala Nesibongo

Kwesinye isikhathi, inkinga iba ngokuhlukile. Imininingwane yakho inenkambu yamagama ephelele kepha udinga ukuhlaziya amagama okuqala nesibongo. Lawa mafomula abheka isikhala esiphakathi kwegama nesibongo bese ebamba umbhalo lapho kudingeka khona. Ibuye iphathe uma singekho isibongo noma kunokufakwa okungenalutho ku-A2.

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

Futhi isibongo:

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

Khawulela inombolo yezinhlamvu bese ungeze…

Ngabe wake wafuna ukuhlanza izincazelo zakho ze-meta? Uma ubufuna ukudonsa okuqukethwe ku-Excel bese unquma okuqukethwe ukuze kusetshenziswe enkambisweni Ye-Meta (izinhlamvu eziyi-150 kuye kwezingu-160), ungakwenza lokho usebenzisa le fomula I-Spot Yami. Iphula kahle incazelo esikhaleni bese ingeza…:

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

Vele, lokhu akuhloselwe ukuba kube okuphelele… amafomula athile nje asheshayo wokukusiza ukuthi uqale ngokushesha! Yiziphi ezinye izindlela ozithola uzisebenzisa? Bangeze kumazwana futhi ngizokunika isikweletu njengoba ngivuselela le ndatshana.

UCABANGANI?

Le sayithi isebenzisa i-Akismet ukunciphisa ugaxekile. Funda ukuthi idatha yakho yokuphawula isetshenziswa kanjani.