5 Nzira yekutsiva Commas neDots muExcel

Mune basa reExcel chirongwa, zvese zvinoshanda zvinoenderana neakatemerwa mafomula uye mabasa. Kunyangwe nekuda kwechidoti chimwe chete kana koma, kuchengetwa kwemabhuku kwese kunogona kutadza. Uye izvi zvinoreva kuti zvichave zvinobatsira kune wega wega mushandisi wechirongwa kudzidza kukurumidza kuwana chikanganiso chaitwa uye kuchigadzirisa.

Maitiro ekutsiva

Mushanduro yeExcel, komma inoshandiswa kuratidza zvikamu zvegumi, asi muchirongwa cheChirungu, madonhwe anoshandiswa. Kazhinji kukanganisa uku kunoitika nekuda kwekushanda mumitauro miviri kana nekuda kwekushaya ruzivo.

Kutanga, zvakakodzera kusarudza pamusoro pezvikonzero nei zvakazofanira kutsiva komma nedoti. Mune zvimwe zviitiko, izvi zvinokonzeresa kutaridzika kunoyevedza kwekuona pane kuita zvinodiwa. Asi kana chinodiwa chekutsiva chinoratidzwa nekudiwa kwekuverenga, saka sarudzo yenzira yekutsiva makoma nemadots inofanira kutorwa zvakanyanya. Zvichienderana nechinangwa chekutsiva, nzira yacho ichave yakasiyana.

Method 1: Shandisa Tsvaga uye Kutsiva Tool

Imwe yedzakareruka uye inonyanya kuzivikanwa nzira dzekutsiva koma nedoti kushandisa chishandiso chinonzi Tsvaga uye Tsiva. Sezvineiwo, nzira iyi haina kukodzera zvikamu zvinoshanda. Kana uchitsiva komma nedoti uchishandisa nzira iyi, kukosha kwesero kunoshandurwa kuita mavara fomati. Funga nezve magadzirirwo eiyo Tsvaga uye Kutsiva nzira:

1. Isu tinosarudza mutsara chaiwo wemasero anoda kutsiviwa. Paunobaya-kurudyi panzvimbo yakasarudzwa, menyu inobuda. Pano tinosarudza chinhu chinonzi "Format Cells". Iri basa rinogona kudanwa neiyo keyboard nzira yekudimbudzira Ctrl+1.
2. Kana "Format Cells" yaitwa, hwindo rekufometa rinovhurwa. Mu "Nhamba" parameter, sarudza "Text" chirevo. Kuti uchengetedze shanduko dzakaitwa, iva nechokwadi chekudzvanya "OK". Kana ukangovhara hwindo rekufomatidza, ipapo shanduko dzese dzinorasikirwa nekuita.
3. Ngatipfuurirei padanho rinotevera. Zvakare, sarudza nhamba inodiwa yemasero. Mune iyo inoshanda tebhu "Kumba" tinowana block yemabasa "Editing", sarudza "Tsvaga uye sarudza". Mune menyu inooneka mushure meizvi, iyo "Replace" sarudzo inofanirwa kuvhurwa.

4. Tevere, hwindo rakanzi "Tsvaga uye Replace" rinovhura kuzadza maviri "Tsvaga" paramita - hunhu, izwi kana nhamba inoiswa, uye mu "Tsvanza ne" unofanirwa kutsanangura hunhu, izwi kana nhamba iyo inotsiviwa. made. Nokudaro, mumutsara we "Tsvaga" pachava ne "," chiratidzo, uye mumutsetse "Tsvaura" - "".
5. Mushure mekuzadza ma parameter, tinya "Siva Zvese". Mushure meizvozvo, meseji diki ichaonekwa nezve nhamba yekutsiviwa kwakaitwa. Dzvanya "OK".

Iyi nzira inobvumidza iwe kutsiva makomu ese nenguva munzvimbo yakasarudzwa yemasero. Nzira yacho iri nyore uye inokurumidza. Kuipa kweiyi nzira ndiko kutsiviwa kwefomati nechinyorwa chimwe chete, icho chisingabatanidzi mamwe masvomhu.

Method 2: Shandisa iyo SUBSTITUTE Basa

Nzira yacho inobva pakushandiswa kwebasa rinoenderana rezita rimwe chete. Paunosarudza nzira iyi, zvakakosha kushandura data yesero, wozoikopa uye kuiisa panzvimbo yedata rekutanga.

1. Nekusarudza sero risina chinhu, pedyo nesero rinokwanisa kuchinja. Shandisa "Insert function" - chiratidzo mumutsara wemabasa "fx".
2. Muhwindo rinoonekwa nemabasa aripo, tinowana chikamu che "Text". Sarudza fomula inonzi "Substitute" uye chengetedza sarudzo nekudzvanya bhatani re "OK".

3. Hwindo rinoonekwa rekuzadza ma parameter anodiwa - "Text", "Old text" uye "New text". Iyo "Text" parameter inosanganisira kuisa kero yesero ine kukosha kwepakutanga. Mutsetse wekuti "Chinyorwa Chekare" unoitirwa kuratidza mavara achatsiviwa, kureva, ",", uye mu "New text" parameter tinoisa "." Kana ma parameter ese azadzwa mukati, tinya OK. Zvinotevera zvichaonekwa musero rinoshanda: =SUBSTITUTE(C4; ","; ".").

4. Somugumisiro, kukosha kwesero kunoshandurwa kubudirira. Aya manipulations anofanirwa kudzokororwa kune mamwe maseru ese.
5. Iyi nzira ine zvakare yakakosha kusakosha. Kana mashoma mashoma achizotsiviwa, saka kudzokorora matanho hakutore nguva yakawanda. Asi ko kana iwe uchida kushandura yakakura yakakura yedata. Iwe unogona, semuenzaniso, kushandisa sero rekuzadza marker.
6. Kuti uite chinhu ichi, unofanirwa kuseta chitubu kukona yekurudyi yesero rinoshanda rine basa rakatopinda. Muchiitiko ichi, muchinjikwa uchaonekwa - iyo inonzi kuzadza marker. Nekudzvanya bhatani rekuruboshwe mbeva, iwe unofanirwa kudhonza muchinjiko uyu pamwe nekoramu nehunhu hunoda kuchinjwa.
7. Nekuda kweizvozvo, tsika dzakatochinja dzichaonekwa mukoramu yakasarudzwa - panzvimbo yemakoma muzvikamu zvedesimali, ikozvino kune madotsi. Iye zvino iwe unofanirwa kutevedzera uye kuendesa ese akawanikwa akashandurwa kukosha kumasero eiyo nhamba dzepakutanga. Simbisa maseru akachinja. Dzvanya bhatani re "Copy" mu "Main" tab.
8. Paunodzvanya-kurudyi komputa pamasero akasarudzwa, menyu inooneka ine chikamu "Namira Sarudzo", tsvaga uye sarudza iyo "Values" parameter. Schematically, chinhu ichi chinoratidzwa sebhatani "123".
9. Hwanhu hwakashandurwa huchaendeswa kumaseru akakodzera. Kuti ubvise zvisina kufanira kukosha mune imwecheteyo menyu, sarudza chikamu "Bvisa zviri mukati".

Nekudaro, kutsiva kwemakoma kwenhambo mumhando yakasarudzwa yehukoshi kwaitwa, uye hunhu husina basa hwakabviswa.

Method 3: Gadzirisa Excel Options

Nekugadzirisa mamwe maparamita echirongwa cheExcel, unogona zvakare kuwana kutsiva kwechiratidzo "," ne ".". Muchiitiko ichi, maitiro emasero acharamba ari manhamba, uye haazoshanduki kune zvinyorwa.

1. Nekuita iyo "Faira" tebhu, sarudza iyo "Options" block.
2. Iwe unofanirwa kuenda kune "Advanced" chikamu uye uwane "Editing Options". Uncheck bhokisi riri padivi pe "Shandisa system separators" criterion. Mumutsara we "Separator ye integer uye fractional parts" tinoshandura dot, iyo inongogara, kune comma.

Mushure meshanduko dzakaitwa muExcel chirongwa marongero, delimiter yekureva zvikamu zvikamu zvino yave nguva.

Nzira 4: Shandisa Custom Macro

Imwe nzira yekutsiva semicolon muExcel inosanganisira kushandiswa kwemacros. Asi usati wavashandisa, unofanirwa kufunga nezve chokwadi chekuti macros akaremara nekukasira muchirongwa. Saka, kuti utange, unofanirwa kugonesa iyo "Developer" tab uye activate macros.

Iyo "Developer" mode inogoneswa kuburikidza nezvirongwa zvechirongwa. Muchikamu chinonzi "Gadzirisa ribhoni", ipapo muchikamu che "Main tabs" tinowana chinhu "Developer", pamberi pacho tinoisa tiki. Zvirongwa zvinogadziriswa mushure mekudzvanya bhatani re "OK".

1. Tab "Developer" → block "Code", tinya bhatani rinonzi "Visual Basic".
2. Iro macro editor window richavhura. Muhwindo iri, iwe unofanirwa kuisa inotevera chirongwa chekodhi:

Panguva ino, tinopedzisa basa mupepeti nekungovhara hwindo remupepeti.

3. Sarudza maseru umo shanduko ichaitwa. Dzvanya bhatani re "Macros", iro riri mubhokisi rekushandisa.
4. Hwindo rinoonekwa rinoratidza macros aripo. Sarudza iyo ichangobva kugadzirwa macro. Iine macro yakasarudzwa, tinya "Mhanya" kuti uiite.

5. Kutsiva kunoitwa - madonhwe akaonekwa panzvimbo yemakoma.

Kushandiswa kweiyi nzira kunoda kunyatsotariswa. Mushure mekuita macro, hazvichagone kudzosera zvese kumashure. Paunenge uchisarudza maseru ane humwe hunhu, iwe unofanirwa kuve nechokwadi chekuti shanduko ichaitwa chete kune iyo data inoida chaizvo.

Nzira 5: Shandura masisitimu ekombuta

Iyi nzira haina kunyanya kuwanda, zvisinei, inoshandiswawo kutsiva makoma nenguva paunenge uchiverenga muExcel zvinyorwa. Isu tichashandura marongero zvakananga musoftware. Funga nezvemaitiro aya uchishandisa muenzaniso we Windows 10 Pro software.

1. Tinoenda ku "Control Panel", iyo inogona kunzi kuburikidza ne "Start".

2. Muchikamu che "Nguva nemutauro", sarudza "Region" sarudzo.
3. Mushure meizvozvo, hwindo rinovhurwa. Pano isu tinomutsa "Dzimwe sarudzo dzezuva, nguva, dunhu".

4. Iwindo idzva richazaruka, umo tinoenda ku "Regional Standards".

5. Zvino enda ku "Formats" tab uye pazasi pehwindo shandisa "Advanced sarudzo ...".

6. Tevere, muchikamu che “Nhamba”, tsanangura chinodiwa chinopatsanura mutsara mutsetse wekuti “Mupatsanuri wezvikamu zvese nezvidimbu. Mushure mekuita shanduko, tinya "OK".

Nekuda kwebasa redu, makoma mumaseru-minda eExcel matafura akazadzwa nenhamba dzenhamba anozoshandurwa otomatiki kuita nguva. Muchiitiko ichi, sero format haina basa, ingave "General" kana "Numeric".

Zvinokosha! Paunenge uchiendesa faira kune imwe komputa ine yakajairwa marongero, matambudziko nemaitiro ekuverenga anogona kuitika.

Imwe nzira: Kutsiva dot necomma muExcel uchishandisa Notepad

Windows software ine Notepad chirongwa chinoshanda pahwaro hwehuwandu hushoma hwemabasa uye marongero. "Notepad" inogona kushandiswa semurevereri pakukopa, kuongorora data.

1. Iwe unofanirwa kusarudza iyo yaunoda huwandu hwemasero uye uikope. Vhura Notepad uye unamate iyo yakakopwa tsika muhwindo rinovhura.

2. Mu "Edit" tab, sarudza chikamu che "Replace". Semakiyi anopisa, musanganiswa "CTRL + H" unoshandiswa. Hwindo rinoonekwa ratinozadza minda. Mumutsara "Chii" pinda ",", mumutsetse "Chii" - "". Kana minda yazadzwa, tinya "Dzorera Zvese".

Mushure mekushandiswa uku muzvinyorwa zvakaiswa, makoma ese akashandurwa kuita nguva. Ikozvino zvangosara kukopa akachinja mafractional values ​​uXNUMXbuXNUMXbagain uye anamira patafura yeExcel gwaro.

mhedziso

Chinyorwa chakaongorora nzira dzinoshanda uye dzakakurumbira dzekutsiva iyo comma muzvikamu zvedesimali nemadots muExcel spreadsheets. Kazhinji, vashandisi vanosarudza yakavakirwa-mukati Tsvaga uye Kutsiva chishandiso chekutaridzika kunokwezva kwenhamba dzenhamba, uye iyo SUBSTITUTE basa rinoshandiswa kuita masvomhu.