Maitiro ekubvisa mitsara yekutyora (kutakura kudzoka) kubva kumaseru muExcel 2013, 2010 uye 2007

Ichi chidzidzo chinokuzivisa iwe kune nzira nhatu dzekubvisa inodzosa ngoro kubva kumaseru muExcel. Iwe unozodzidza zvakare kutsiva mitsara yekutyora nemamwe mavara. Yese yakakurudzirwa mhinduro inoshanda muExcel 2013, 2010, 2007 uye 2003.

Kutyora kwemitsara kunogona kuoneka mumavara nekuda kwezvikonzero zvakasiyana. Kazhinji kudzoka kwekutakura kunoitika mubhuku rekushanda, semuenzaniso kana zvinyorwa zvichikopwa kubva pawebhu peji, kana zvatova mubhuku rebasa rakagamuchirwa kubva kumutengi, kana isu pachedu tichizviwedzera nekudzvanya makiyi. Alt + Pinda.

Chero chikonzero chavo, dambudziko riripo nderekubvisa zvitakuriso zvinodzosa, sezvo zvichikanganisa kutsvaga kwemitsara uye zvinokonzeresa kukoromoka kwekoromo kana kuputira kuchigoneswa.

Nzira nhatu dzese dzakaratidzwa dzinokurumidza. Sarudza iyo inonyatsokunakira iwe:

• Isu tinobvisa mitsara yese yekutyora nemaoko kuti tikurumidze kuisa zvinhu zvakarongeka pane rimwe pepa.
• Isu tinobvisa mitsara yekutyora tichishandisa mafomula uye nekudaro toseta kuomarara kugadzirisa kwe data data mumaseru.
• Isu tinoshandisa VBA macro kujekesa mitsara yekutyora kubva kune akati wandei mabhuku ebasa.

Cherechedza: Pakutanga, mazwi ekuti "Carriage return" uye "Line feed" aishandiswa pakushanda pamatapureta uye aireva maitiro maviri akasiyana. Muverengi anobvunza anogona kuzvimiririra kuwana ruzivo rwakadzama nezve izvi paInternet.

Makomputa uye software yekugadziridza mazwi akagadzirwa aine hunhu hwetapureta mundangariro. Ichi ndicho chikonzero mavara maviri akasiyana asina kudhindwa ave kushandiswa kuratidza mutsara wekutyora: kudzoka kwengoro (Carriage return, CR kana ASCII code 13) uye Dudziro yemutsetse (Mutsara wekudya, LF kana ASCII kodhi 10). PaWindows, mavara ese anoshandiswa pamwechete, uye pa *NIX masisitimu, mitsva mitsva chete ndiyo inoshandiswa.

Chenjera: Idzi sarudzo dziri mbiri dzinowanikwa muExcel. Paunenge uchiunza kubva kune mafaira .txt or . Csv iyo data inowanzo ine kutakura kudzoka uye mitsetse yekudyara. Kana kutyora kwemutsara kunopindwa nemaoko nekudzvanya Alt + Pinda, Excel inongoisa mutsara mutsva. Kana iyo faira . Csv yakagamuchirwa kubva kune fan yeLinux, Unix kana imwe yakafanana sisitimu, wobva wagadzirira kusangana neunyowani chete hunhu.

Kubvisa ngoro inodzosa nemaoko

zvayakanakira: Iyi nzira ndiyo inokurumidza.

nezvayakaipira: Hapana zvekuwedzera 🙁

Aya ndiwo mabvisiro aungaita mitsara yekupwanya uchishandisa "Tsvaga uye kutsiva":

1. Sarudza maseru ese kwaunoda kubvisa zvingoro zvinodzosa kana kuatsiva neimwe mavara.
2. dzvanya Ctrl + Hkuunza dialog box Tsvaga uye kutsiva (Tsvaga uye Tsiva).
3. Isa cursor mumunda Kuwana (Tsvaga chii) uye tinya Ctrl+J. Pakutanga kuona, munda uchaita seusina chinhu, asi kana ukanyatsotarisisa, uchaona kadonhwe kadiki mariri.
4. Mu Kudzorerwa (Replace With) isa chero kukosha kuti uise panzvimbo yekutakura inodzoka. Kazhinji nzvimbo inoshandiswa kune izvi kudzivirira kunamatira netsaona kwemazwi maviri ari pedyo. Kana iwe uchingoda kubvisa mitsara yekutyora, siya munda Kudzorerwa (Tsvaura Na) isina chinhu.
5. dzvanya Tsiva zvese (Tsvanura Zvese) uye unakirwe nemhedzisiro!

Bvisa mitsetse yemitsara uchishandisa Excel mafomula

zvayakanakira: Iwe unogona kushandisa akatevedzana kana nested mafomula ekuomesera mameseji ekuongorora musero yakagadziriswa. Semuyenzaniso, unogona kubvisa zvinodzosa ngoro uye wobva wawana imwe nzvimbo inotungamira kana yekutevera, kana imwe nzvimbo pakati pemazwi.

Mune zvimwe zviitiko, kutyora kwemitsara kunofanirwa kubviswa kuitira kuti gare gare shandisa zvinyorwa senharo dzebasa pasina kuita shanduko kumaseru ekutanga. Mhedzisiro inogona kushandiswa, semuenzaniso, senharo yebasa ONA (TARISA MUDENGA).

nezvayakaipira: Iwe unozofanirwa kugadzira koramu yemubatsiri uye kuita akawanda ekuwedzera matanho.

1. Wedzera chikamu chekubatsira pamagumo e data. Mumuenzaniso wedu, ichanzi 1 mitsara.
2. Musero rekutanga rekoramu yekubatsira (C2), isa fomula yekubvisa / kutsiva mitsara yekutyora. Pazasi pane mashoma anobatsira mafomula ezviitiko zvakasiyana:
   • Iyi fomula yakakodzera kushandiswa neWindows uye UNIX ngoro yekudzoka/mutsara wekudyisa musanganiswa.

     =ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;СИМВОЛ(13);"");СИМВОЛ(10);"")

     =SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),"")

   • Iyi inotevera fomula yakakodzera kutsiva mutsara wekutyora nechero humwe hunhu (semuenzaniso, ", " - koma + nzvimbo). Muchiitiko ichi, mitsetse haizobatanidzwa uye nzvimbo dzekuwedzera hazvizooneki.

     =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;СИМВОЛ(13);"");СИМВОЛ(10);", ")

     =TRIM(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),", ")

   • Uye aya ndiwo maitiro aunokwanisa kubvisa ese asina kudhinda mavara kubva kumavara, kusanganisira mitsara yekutyora:

     =ПЕЧСИМВ(B2)

     =CLEAN(B2)

   

3. Kopa fomula kumaseru ese ari mukoramu.
4. Sarudzo, unogona kutsiva iyo yekutanga koramu neitsva, ine mitsetse yakabviswa:
   • Sarudza maseru ese muchikamu C uye kuomesa Ctrl + C kopira data kune clipboard.
   • Zvadaro, sarudza sero B2, dzvanya nzira yekudimbudzira kiibhodhi Shift + F10 ndokuzo Insert (Insert).
   • Bvisa mutsara wemubatsiri.

Bvisa mitsara inotyora neVBA macro

zvayakanakira: Gadzira kamwe - shandisa zvakare uye zvakare nechero bhuku rebasa.

nezvayakaipira: Ruzivo rwekutanga rweVBA runodiwa.

Iyo VBA macro mumuenzaniso unotevera inobvisa inodzosa ngoro kubva kumaseru ese pane inoshanda shizha rekushanda.

Sub RemoveCarriageReturns() Dim MyRange As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Every MyRange In ActiveSheet.UsedRange Kana 0 < InStr(MyRange, Chr(10)) Then MyRange = Replace(MyRange, "Chr(10) ") Pedzisa Kana Inotevera Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub

Kana iwe usiri kunyanya kujairana neVBA, ini ndinokurudzira kuti udzidze chinyorwa pamusoro pekuisa uye kuita VBA kodhi muExcel.