Ulususa kanjani uhlamvu lokuqala kuseli lespredishithi se-excel

Ngokuvamile, abasebenzisi bomhleli wespredishithi se-Excel babhekana nomsebenzi onjengokususa uhlamvu lokuqala kuseli yetafula. Ungasebenzisa le nqubo usebenzisa izindlela ezihlukahlukene, isibonelo, usebenzisa ama-opharetha ahlanganisiwe akhethekile. Esihlokweni, sizocubungula ngokuningiliziwe, sisebenzisa izibonelo, izindlela ezimbalwa ezisebenzisa ukususwa kwezinhlamvu esitokisini sedatha yethebula.

Susa uhlamvu lokuqala kusipredishithi se-excel

Ukuze kusetshenziswe le nqubo elula, kusetshenziswa umsebenzi okhethekile ohlanganisiwe. Imiyalelo enemininingwane yokususa uhlamvu lokuqala ibukeka kanje:

1. Isibonelo, sinepuleti elinjalo eliqukethe isethi ethile yedatha endaweni yokusebenza yedokhumenti yesipredishithi. Kudingeka sisebenzise ukususwa komlingiswa wokuqala.

2. Ekuqaleni, sidinga ukukhomba ingqikithi yenani lezinhlamvu kuwo wonke amaseli. Ukwenza lesi senzo, kufanele usebenzise i-opharetha ye-DLSTR. Lo msebenzi ikuvumela ukubala inani lezinhlamvu. Hambisa ikhesa kuseli B2 bese uyikhetha ngenkinobho yegundane engakwesokunxele. Lapha sishayela ngefomula elandelayo: =DLSTR(A2). Manje sidinga ukukopisha le fomula kumaseli aphansi. Hambisa isikhombisi segundane ekhoneni elingezansi kwesokudla senkambu B2. Ikhesa ithathe isimo sophawu oluncane lokuhlanganisa lomthunzi omnyama. Bamba i-LMB bese uhudulela ifomula kuwo wonke amaseli.

3. Esigabeni esilandelayo, siqhubeka nokususa uhlamvu lokuqala kwesokunxele. Ukwenza le nqubo, kusetshenziswe opharetha okuthiwa RIGHT. Hambisa ikhesa kuseli B1 bese uyikhetha ngenkinobho yegundane engakwesokunxele. Lapha sishayela ngefomula elandelayo: =PRAWSIMV(A2;DLSTR(A2)-1). Kule fomula, i-A2 iyinhlanganisela yeseli lapho sikhipha khona uhlamvu lokuqala kwesokunxele, futhi i-LT(A2)-1 inombolo yezinhlamvu ezibuyisiwe kusukela ekugcineni komugqa ohlangothini lwesokudla.

Lesi sibalo senkambu ngayinye sibalwa ngokukhipha uhlamvu olulodwa enanini eliphelele lezinhlamvu.

4. Manje sidinga ukukopisha le fomula kumaseli aphansi. Hambisa isikhombisi segundane ekhoneni elingezansi kwesokudla senkambu B2. Ikhesa ithathe isimo sophawu oluncane lokuhlanganisa lomthunzi omnyama. Bamba i-LMB bese uhudulela ifomula kuwo wonke amaseli. Njengomphumela, sisebenzise ukukhishwa kohlamvu lokuqala kwesokunxele seseli ngalinye elikhethiwe. Ilungile!

Ukwengeza, ungasebenzisa i-opharetha ekhethekile ebizwa nge-PSR. Isibonelo, sinedatha kumaseli lapho inombolo ye-serial yabasebenzi iboniswa khona. Sidinga ukususa izinhlamvu zokuqala ngaphambi kwechashazi noma isikhala. Ifomula izobukeka kanje: =MID(A:A;SESHA(“.”;A:A)+2;DLSTR(A:A)-SESHA(“.”;A:A)).

Ukukhipha uhlamvu ngaphambi kohlamvu kusihleli sespredishithi

Kunezimo lapho kudingekile ukususa izinhlamvu kuze kufike kuhlamvu oluthile kudokhumenti yesipredishithi. Kulokhu, ifomula elandelayo elula iyasebenza: =INDAWO(A1,SESHA(“uhlamvu”,A1),). Umphumela wezinguquko:

• I-A1 inkambu ehlolwayo.
• Uhlamvu luyinto noma ulwazi lombhalo lapho iseli izosikwa khona kwesokunxele.

Ukwengeza, le nqubo ingahlanganiswa nokuhlanza idatha "Ngemva".

Ukususa uhlamvu ngaphambi kokhefana kusihleli sespredishithi

Kunezimo lapho kudingekile ukususa izindawo zedesimali kudokhumenti yesipredishithi. Kulokhu, ifomula elandelayo elula iyasebenza: =REPLACE(A1;1;SESHA(“&”;A1);). Umphumela wezinguquko:

Ikhipha izinhlamvu ziye esikhaleni kumhleli wespredishithi

Kunezimo lapho kudingekile ukususa izinhlamvu kuze kufike esikhaleni kudokhumenti yesipredishithi. Kulokhu, ifomula elandelayo elula iyasebenza: =REPLACE(A1;1;SESHA(“&”;A1);). Umphumela wezinguquko:

Isusa ngo-opharetha we-SUBSTITUTE

Ukukhipha izinhlamvu kungenziwa ngesitatimende esilula esibizwa nge-SUBSTITUTE. Ukubuka okujwayelekile komsebenzisi: =SUBSTITUTE(umbhalo, umbhalo_omdala, umbhalo_omusha, inombolo_yokufaka).

• Umbhalo - lapha inkambu enedatha ezoshintshwa isethiwe.
• Old_text idatha ezoshintsha.
• New_text – idatha ezofakwa esikhundleni soqobo.
• entry_number iwukungqubuzana kokuzithandela. Ikuvumela ukuthi ushintshe izinhlamvu uqale ngenombolo ethile.

Isibonelo, uma sidinga ukusebenzisa ukukhishwa kwamaphoyinti abekwe kwesokunxele sombhalo oyinhloko, khona-ke sidinga ukufaka ifomula elandelayo: = SUBSTITUTE(A1;”.”;” “).

Sisebenzisa le fomula, sizoshintsha uhlamvu olunikeziwe, olubhalwe kwesokunxele sombhalo oyinhloko, sibe nezikhala. Manje sidinga ukwenza ukususwa kwalezi zikhala. Ukuqalisa le nqubo, kusetshenziswa opharetha, onegama elithi TRIM. Umsebenzi ikuvumela ukuthi uthole izikhala ezingadingekile futhi uzisuse. Ukubuka okuvamile komsebenzisi kubukeka kanje: =TRIMSPACES().

Kubalulekile! Le fomula isusa izikhala ezijwayelekile kuphela. Isibonelo, uma umsebenzisi engeze ulwazi olukopishwe kusuka kwenye isayithi kuya ekhasini lokusebenzela, lokho kusho ukuthi lingaqukatha izikhala, kodwa izinhlamvu ezifana nazo. Kulokhu, u-opharetha we-TRIM ngeke asebenzele ukususwa. Lapha uzodinga ukusebenzisa ithuluzi elithi Thola futhi Ususe.

Isusa nge-opharetha ye-CLEAN

Ngokuzithandela, ungasebenzisa u-opharetha we-PRINT. Ukubuka okuvamile kwesisebenzisi sokususa izinhlamvu ezingaphrinteki kubukeka kanje: =IHLANZEKILE(). Lo msebenzi ususa izinhlamvu ezingaphrintiwe emgqeni (ukuhlukana kwemigqa, izinhlamvu zezigaba, izikwele ezihlukahlukene, njalonjalo). U-opharetha uyadingeka ezimeni lapho kudingekile ukuthi kusetshenziswe ukususwa kwekhefu lomugqa.

Kubalulekile! Umsebenzisi ususa kuphela izinhlamvu eziningi ezengeziwe.

Isiphetho neziphetho mayelana nokususwa kwabalingiswa bokuqala

Sicabangele izindlela zokususa uhlamvu lokuqala kulwazi lwethebula. Izindlela zisho ukusetshenziswa kwama-opharetha ahlanganisiwe. Ukusebenzisa imisebenzi kukuvumela ukuthi usheshise kakhulu inqubo yokusebenza ngenani elikhulu lemininingwane yethebula.