Momwe mungachotsere zilembo zoyambirira mu cell spreadsheet ya Excel

Nthawi zambiri, ogwiritsa ntchito mkonzi wa Excel spreadsheet amakumana ndi ntchito ngati kuchotsa munthu woyamba patebulo. Mutha kugwiritsa ntchito njirayi pogwiritsa ntchito njira zosiyanasiyana, mwachitsanzo, pogwiritsa ntchito ophatikizira apadera. M'nkhaniyi, tikambirana mwatsatanetsatane, pogwiritsa ntchito zitsanzo, njira zingapo zomwe zimagwiritsa ntchito kuchotsa zilembo mu selo la deta ya tabular.

Chotsani munthu woyamba mu Excel spreadsheet

Kuti agwiritse ntchito njirayi yosavuta, ntchito yapadera yophatikizidwa imagwiritsidwa ntchito. Malangizo atsatanetsatane ochotsera munthu woyamba akuwoneka motere:

1. Mwachitsanzo, tili ndi mbale yotere yomwe ili ndi seti inayake ya data pa malo ogwirira ntchito a chikalata cha spreadsheet. Tiyenera kukhazikitsa kuchotsedwa kwa munthu woyamba.

2. Poyamba, tiyenera kuzindikira chiwerengero chonse cha zilembo m'maselo onse. Kuti muchite izi, muyenera kugwiritsa ntchito DLSTR. Ntchitoyi imakupatsani mwayi wowerengera kuchuluka kwa zilembo. Sunthani cholozera ku cell B2 ndikusankha ndi batani lakumanzere. Apa tikuyendetsa mu formula iyi: =DLSTR(A2). Tsopano tikuyenera kukopera fomulayi m'maselo apansi. Sunthani cholozera cha mbewa kumunsi kumanja kwa munda B2. Cholozera chatenga mawonekedwe ang'onoang'ono kuphatikiza chizindikiro cha mthunzi wakuda. Gwirani LMB ndikukokera fomula kumaselo ena onse.

3. Pa siteji yotsatira, timapitiriza kuchotsa munthu 1 kumanzere. Kuti akwaniritse njirayi, wogwiritsa ntchito wotchedwa RIGHT amagwiritsidwa ntchito. Sunthani cholozera ku cell B2 ndikusankha ndi batani lakumanzere. Apa tikuyendetsa mu formula iyi: =PRAWSIMV(A2;DLSTR(A2)-1). Munjira iyi, A2 ndiye kulumikizana kwa selo pomwe tikuchotsa munthu woyamba kumanzere, ndipo LT (A2) -1 ndi kuchuluka kwa zilembo zomwe zabwezedwa kuchokera kumapeto kwa mzere kumanja.

Chiwerengerochi cha gawo lililonse chimawerengedwa pochotsa munthu m'modzi kuchokera pa chiwerengero chonse cha zilembo.

4. Tsopano tikuyenera kukopera fomulayi m'maselo apansi. Sunthani cholozera cha mbewa kumunsi kumanja kwa munda B2. Cholozera chatenga mawonekedwe ang'onoang'ono kuphatikiza chizindikiro cha mthunzi wakuda. Gwirani LMB ndikukokera fomula kumaselo ena onse. Chotsatira chake, takhazikitsa kuchotsa chilembo choyamba kumanzere kwa selo iliyonse yosankhidwa. Okonzeka!

Kuphatikiza apo, mutha kugwiritsa ntchito wogwiritsa ntchito wapadera wotchedwa PSTR. Mwachitsanzo, tili ndi deta m'maselo omwe chiwerengero cha antchito chikuwonetsedwa. Tiyenera kuchotsa zilembo zoyambirira pasanakhale kadontho kapena danga. Fomula idzawoneka motere: =MID(A:A;FUFUZANI(“.”;A:A)+2;DLSTR(A:A)-PANGANI(“.”;A:A)).

Kuchotsa munthu pamaso pa munthu mumkonzi wa spreadsheet

Pali nthawi zina pamene kuli kofunikira kufufuta zilembo mpaka munthu wina mu chikalata cha spreadsheet. Pankhaniyi, njira yosavuta iyi ikugwira ntchito: =MALO(A1,FUWANI(“khalidwe”,A1),). Zotsatira zakusintha:

• A1 ndiye gawo lomwe likuwunikiridwa.
• Khalidwe ndi chinthu kapena zolemba zomwe seloyo imadulidwa kumanzere.

Kuonjezera apo, njirayi ikhoza kuphatikizidwa ndi kuyeretsa deta "Pambuyo".

Kuchotsa munthu pamaso pa koma mumkonzi wa spreadsheet

Pali nthawi zina pamene kuli kofunikira kuchotsa malo a decimal mu chikalata cha spreadsheet. Pachifukwa ichi, njira yosavuta iyi ikugwira ntchito: =REPLACE(A1;1;KUFUNA(“&”;A1);). Zotsatira zakusintha:

Kuchotsa zilembo mpaka pamipata mumkonzi wa spreadsheet

Pali nthawi zina pamene kuli kofunikira kufufuta zilembo mpaka malo mu chikalata cha spreadsheet. Pankhaniyi, njira yosavuta iyi ikugwira ntchito: =KUSINTHA(A1;1;POSAKANI(“&”;A1);). Zotsatira zakusintha:

Kuchotsa ndi SUBSTITUTE woyendetsa

Kuchotsa zilembo zitha kuchitika ndi mawu osavuta otchedwa SUBSTITUTE. Maonedwe onse a wogwiritsa ntchito: =SUBSTITUTE(zolemba, zakale_zolemba, zatsopano, nambala_zolowera).

• Zolemba - apa munda womwe uli ndi deta yosinthidwa wakhazikitsidwa.
• Old_text ndi data yomwe idzasinthe.
• New_text - deta yomwe idzalowetsedwe m'malo mwa choyambirira.
• entry_number ndi mkangano wosankha. Zimakuthandizani kuti musinthe zilembo kuyambira ndi nambala inayake.

Mwachitsanzo, ngati tikufuna kugwiritsa ntchito kuchotsa mfundo zomwe zili kumanzere kwa mutu waukulu, ndiye kuti tifunika kuyika njira iyi: =KUSINTHA(A1;”.”;” “).

Pogwiritsa ntchito fomulayi, tisintha mawonekedwe omwe tapatsidwa, olembedwa kumanzere kwa mawu akulu, ndi mipata. Tsopano tiyenera kukhazikitsa kuchotsa malowa. Kuti agwiritse ntchito njirayi, wogwiritsa ntchito amagwiritsidwa ntchito, yemwe ali ndi dzina la TRIM. Ntchitoyi imakupatsani mwayi wopeza malo osafunikira ndikuchotsa. Mawonekedwe a opareshoni amawoneka motere: =TRIMSPACES().

Zofunika! Fomula iyi imachotsa mipata yokhazikika. Mwachitsanzo, ngati wogwiritsa ntchitoyo awonjezera zambiri zomwe zakopedwa kuchokera patsamba lina kupita patsamba, ndiye kuti sizingakhale ndi mipata, koma zilembo zofanana nazo. Pankhaniyi, woyendetsa TRIM sangagwire ntchito kuti achotse. Apa muyenera kugwiritsa ntchito Pezani ndi Chotsani chida.

Kuchotsa ndi wogwiritsa ntchito CLEAN

Mukasankha, mutha kugwiritsa ntchito PRINT. Mawonedwe onse a wogwiritsa ntchito pochotsa zilembo zosasindikizidwa amawoneka motere: = ZOYERA (). Ntchitoyi imachotsa zilembo zosasindikiza pamzere (kuduka kwa mizere, zilembo za ndime, mabwalo osiyanasiyana, ndi zina zotero). Wogwira ntchitoyo ndi wofunikira pazochitika zomwe zimafunika kuti athetse kuchotsedwa kwa mzere wopuma.

Zofunika! Wogwiritsa ntchito amachotsa zilembo zambiri zokha.

Mapeto ndi zomaliza za kuchotsedwa kwa zilembo zoyambirira

Takambirana njira zochotsera munthu woyamba pazambiri zama tabular. Njira zimatanthauza kugwiritsa ntchito ophatikizira ophatikizika. Kugwiritsa ntchito ntchito kumakupatsani mwayi wofulumizitsa kwambiri ntchito yogwira ntchito ndi zidziwitso zambiri zama tabular.