Momwe mungachotsere zodulira mizere (kubwerera pamagalimoto) m'maselo a Excel 2013, 2010 ndi 2007

Phunziroli likuwonetsani njira zitatu zochotsera zobweza zamagalimoto ku ma cell a Excel. Muphunziranso momwe mungasinthire zoduka mizere ndi zilembo zina. Mayankho onse omwe aperekedwa amagwira ntchito mu Excel 2013, 2010, 2007 ndi 2003.

Kuduka kwa mzere kumatha kuwoneka m'mawu pazifukwa zosiyanasiyana. Nthawi zambiri zobweza zonyamula katundu zimachitika m'bukhu lantchito, mwachitsanzo, mawu akakopera kuchokera patsamba, akakhala kale m'bukhu lantchito lomwe adalandira kuchokera kwa kasitomala, kapena tikamawonjezera patokha mwa kukanikiza makiyi. Alt + Lowani.

Kaya ali ndi chifukwa chotani, chovuta tsopano ndikuchotsa zobweza zangolo, chifukwa zimasokoneza kusaka kwa mawu ndikupangitsa kuti pakhale kusanjikana m'mizere ikatsekedwa.

Njira zitatu zoperekedwa ndizofulumira kwambiri. Sankhani yomwe ili yoyenera kwa inu:

  • Timachotsa zotsalira zonse pamanja kuti tikonze zinthu mwachangu patsamba limodzi.
  • Timachotsa zoduka mizere pogwiritsa ntchito ma fomula ndipo motero timakhazikitsa zovuta zosinthira zolemba pamaselo.
  • Timagwiritsa ntchito VBA macro kuchotsa mizere yoduka kuchokera m'mabuku angapo ogwira ntchito.

Zindikirani: Poyambirira, mawu akuti "kubwerera kwagalimoto" ndi "chakudya chamzere" ankagwiritsidwa ntchito popanga mataipilapo ndipo amatanthauza ntchito ziwiri zosiyana. Wowerenga wofuna kudziwa akhoza kupeza mwatsatanetsatane za izi pa intaneti.

Makompyuta ndi mapulogalamu osinthira mawu adapangidwa motengera mawonekedwe a makina ojambulira. Ichi ndichifukwa chake zilembo ziwiri zosiyana zosasindikizidwa tsopano zimagwiritsidwa ntchito kusonyeza kutha kwa mzere: kubwerera kwagalimoto (Kubwerera kwagalimoto, CR kapena ASCII code 13) ndi Kumasulira kwa mzere (Chakudya chamzere, LF kapena ASCII code 10). Pa Windows, zilembo zonse ziwiri zimagwiritsidwa ntchito limodzi, ndipo pamakina a *NIX, mizere yatsopano yokha ndiyomwe imagwiritsidwa ntchito.

Samalani: Zosankha ziwirizi zimapezeka mu Excel. Mukamaitanitsa kuchokera ku mafayilo .ndilembereni or . Csv deta nthawi zambiri imakhala ndi zobweza zamagalimoto ndi ma feed a mzere. Pamene mzere yopuma analowa pamanja ndi kukanikiza Alt + Lowani, Excel imangoyika zilembo zatsopano. Ngati fayilo . Csv adalandira kuchokera kwa wokonda Linux, Unix kapena makina ena ofanana, ndiye konzekerani kukumana ndi munthu watsopano.

Kuchotsa zobwerera zamagalimoto pamanja

ubwino: Njira imeneyi ndi yofulumira kwambiri.

kuipa: Palibe zowonjezera 🙁

Umu ndi momwe mungachotsere zoduka pamzere pogwiritsa ntchito "Pezani ndikusintha":

  1. Sankhani maselo onse omwe mukufuna kuchotsa zobweza zamagalimoto kapena m'malo mwake ndi zilembo zina.Momwe mungachotsere zodulira mizere (kubwerera pamagalimoto) m'maselo a Excel 2013, 2010 ndi 2007
  2. Press Ctrl + Hkubweretsa dialog box Pezani ndikusintha (Pezani ndi Kusintha).
  3. Ikani cholozera m'munda Kuti mupeze (Pezani chiyani) ndikudina Ctrl+J. Poyamba, mundawo udzaoneka wopanda kanthu, koma ngati mutayang’anitsitsa, mudzaona kadontho kakang’ono mmenemo.
  4. Mu Kulowa m'malo (Bwezerani ndi) lowetsani mtengo uliwonse kuti muyike m'malo mwazobweza zangolo. Nthawi zambiri danga limagwiritsidwa ntchito popewa kulumikiza mwangozi mawu awiri oyandikana. Ngati mukungofuna kuchotsa zoduka mizere, chokani m'munda Kulowa m'malo (Bwezerani ndi) opanda kanthu.Momwe mungachotsere zodulira mizere (kubwerera pamagalimoto) m'maselo a Excel 2013, 2010 ndi 2007
  5. atolankhani Bwezerani zonse (Bwezerani Zonse) ndikusangalala ndi zotsatira zake!Momwe mungachotsere zodulira mizere (kubwerera pamagalimoto) m'maselo a Excel 2013, 2010 ndi 2007

Chotsani zoduka mizere pogwiritsa ntchito ma fomula a Excel

ubwino: Mutha kugwiritsa ntchito mafomu otsatizana kapena osakhazikika kuti mutsimikizire zolemba zovuta mu cell yosinthidwa. Mwachitsanzo, mutha kuchotsa zobweza zamagalimoto ndikupeza malo otsogola owonjezera kapena otsata, kapena mipata yowonjezera pakati pa mawu.

Nthawi zina, zoduka mizere ziyenera kuchotsedwa kuti pambuyo pake zigwiritse ntchito mawuwo ngati mikangano yantchito popanda kusintha ma cell oyamba. Zotsatira zake zitha kugwiritsidwa ntchito, mwachitsanzo, ngati mkangano wantchito Onani (YANG'ANANI).

kuipa: Muyenera kupanga gawo lothandizira ndikuchita zina zambiri.

  1. Onjezani gawo lothandizira kumapeto kwa deta. Mu chitsanzo chathu, idzatchedwa Mizere ya 1.
  2. Mu selo loyamba la gawo lothandizira (C2), lowetsani fomula kuti muchotse / m'malo moduka mizere. Pansipa pali njira zingapo zothandiza pazochitika zosiyanasiyana:
    • Fomula iyi ndi yoyenera kugwiritsidwa ntchito ndi Windows ndi UNIX carriage return/line feed kuphatikiza.

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

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

    • Njira yotsatilayi ndiyoyenera kusinthira kutsika kwa mzere ndi munthu wina aliyense (mwachitsanzo, ", " - koma + space). Pankhaniyi, mizere sidzaphatikizidwa ndipo malo owonjezera sadzawonekera.

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

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

    • Umu ndi momwe mungachotsere zilembo zonse zosasindikizidwa pamawu, kuphatikiza zoduka mizere:

      =ПЕЧСИМВ(B2)

      =CLEAN(B2)

    Momwe mungachotsere zodulira mizere (kubwerera pamagalimoto) m'maselo a Excel 2013, 2010 ndi 2007

  3. Lembani fomulayi m'maselo onse omwe ali mugawoli.
  4. Mwachidziwitso, mutha kusintha gawo loyambirira ndi latsopano, ndikuchotsa mizere:
    • Sankhani maselo onse muzambiri C ndi kukakamiza Ctrl + C koperani deta pa clipboard.
    • Kenako, sankhani selo B2, dinani njira yachidule ya kiyibodi Shift + F10 Kenako Ikani (Ikani).
    • Chotsani gawo lothandizira.

Chotsani zotsalira za mzere ndi VBA macro

ubwino: Pangani kamodzi - gwiritsani ntchito mobwerezabwereza ndi buku lililonse.

kuipa: Osachepera chidziwitso choyambirira cha VBA ndichofunika.

VBA macro mu chitsanzo chotsatirachi imachotsa zobweza zamagalimoto kuchokera kumaselo onse patsamba logwira ntchito.

Sub RemoveCarriageReturns() Dim MyRange Monga Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For MyRange Iliyonse Mu ActiveSheet.UsedRange Ngati 0 <InStr(MyRange, Chr(10)) Ndiye MyRange = Replace(MyRange, "Chr(10) ") Kutha Ngati Ntchito Yotsatira.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub

Ngati simukudziwa bwino VBA, ndikupangira kuti muwerenge nkhaniyi momwe mungayikitsire ndikuyika VBA code mu Excel.

Siyani Mumakonda