Iifomula ze-Excel zokuCocwa kweDatha ngokuQhelekileyo

Iifomula zokuCoca idatha yeExcel

Iminyaka, ndisebenzise upapasho njengesixhobo sokungachazi nje indlela yokwenza izinto, kodwa nokugcina irekhodi ukuze ndijonge kamva! Namhlanje, sinomthengi osinike ifayile yedatha yomthengi eyintlekele. Phantse yonke intsimi ayifakwanga kakuhle kwaye; ngenxa yoko, khange sikwazi ukungenisa idatha. Ngelixa kukho ezinye izongezo ezinkulu ze-Excel zokwenza ukucoca usebenzisa iVisual Basic, siqhuba iOfisi yeMac engayi kuxhasa ii-macros. Endaweni yoko, sijonga iifomyula ezichanekileyo zokunceda. Ndicinge ukuba ndiza kwabelana ngezinye zazo apha ukuze abanye bazisebenzise.

Susa iiNqaku ezingezoNombolo

Iinkqubo zihlala zifuna iinombolo zefowuni ukuba zifakwe kwifomathi ethile, ene-11 eneekhowudi yelizwe kwaye akukho ziphumlisi. Nangona kunjalo, abantu bahlala befaka le datha ngamanani kunye namaxesha endaweni yoko. Nantsi ifomula enkulu kususwa bonke oonobumba abangenani kwi-Excel. Ifomula iphonononga idatha ekwiseli 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))

Ngoku unokukopa ikholamu esiphumo kunye nokusetyenziswa Hlela> Cola amaxabiso ukubhala ngaphezulu idatha enefomathi efanelekileyo.

Vavanya imihlaba emininzi nge-OR

Sivame ukucoca iirekhodi ezingaphelelanga kuthengiso. Abasebenzisi abaqapheli ukuba akusoloko kufuneka ubhale iifomula ezintsonkothileyo kwaye ungabhala ingxelo ye-OR endaweni yoko. Kulo mzekelo ungezantsi, ndifuna ukujonga i-A2, B2, C2, D2, okanye i-E2 ngedatha elahlekileyo. Ukuba kukho nayiphi na idatha elahlekileyo, ndiza kubuyisa u-0, kungenjalo u-1. Oko kuyakundivumela ukuba ndilungelelanise idatha kwaye ndicime iirekhodi ezingaphelelanga.

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

Nciphisa kunye neeNdawo eziDibeneyo

Ukuba idatha yakho inendawo yokuQala neFani, kodwa ukungenisa kwakho kunendawo enegama elipheleleyo, ungadibanisa amasimi ngokucocekileyo usebenzisa i-Excel Function Concatenate, kodwa qiniseka ukusebenzisa i-TRIM ukususa naziphi na izithuba ezingenanto ngaphambi okanye emva isicatshulwa. Sisonga yonke intsimi nge-TRIM kwimeko apho elinye lamacandelo lingenayo idatha:

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

Jonga idilesi ye-imeyile efanelekileyo

Ifomula elula elula ejonge zombini @ kunye. kwidilesi ye-imeyile:

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

Susa amagama okuQala kunye neeFani

Ngamanye amaxesha, ingxaki ichasene. Idatha yakho inendawo epheleleyo yamagama kodwa kuya kufuneka uhlalutye amagama okugqibela nawokugqibela. Ezi fomula zikhangela isithuba phakathi kwegama kunye nefani kwaye ubambe isicatshulwa apho kukho imfuneko. Ikwaphatha ukuba akukho gama lokugqibela okanye akukho kungeno kungenanto kwi-A2.

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

Kwaye igama lokugqibela:

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

Nciphisa inani labalinganiswa kwaye Wongeze…

Ngaba ukhe wafuna ukucoca iinkcazo zakho zeemeta? Ukuba ufuna ukutsala umxholo kwi-Excel kwaye usike umxholo oza kusetyenziswa kwicandelo leNkcazo yeMeta (abalinganiswa abali-150 ukuya kwali-160), ungayenza le fomyula Indawo yam. Iphula inkcazo endaweni kwaye yongeze…:

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

Ewe kunjalo, ezi azenzelwanga ukuba zibanzi… ezinye iifomula ezikhawulezayo zokukunceda ufumane isiqalo! Zeziphi ezinye iindlela ozifumana uzisebenzisa? Yongeza kubo kwizimvo kwaye ndiza kukunika ityala njengoko ndihlaziya eli nqaku.

Ingaba ucinga ntoni?

Le sayithi isebenzisa i-Akismet ukunciphisa ugaxekile. Funda indlela idatha yakho yokubhaliweyo isetyenziswe ngayo.