Ungawasusa kanjani amakhefu emigqa kumadokhumenti e-Excel 2010, 2013, 2016 - imiyalelo yesinyathelo ngesinyathelo

Lesi sihloko sidingida izindlela ongasusa ngazo ukugoqwa komugqa (ukubuya kwenqola noma ikhefu lomugqa) kumadokhumenti e-Excel. Ngaphezu kwalokho, lapha uzothola ulwazi lokuthi ungayifaka kanjani esikhundleni nezinye izinhlamvu. Zonke izindlela zilungele izinguqulo ze-Excel 2003-2013 no-2016.

Izizathu zokuvela kokunqamuka komugqa kudokhumenti zihlukahlukene. Ngokuvamile kwenzeka lapho ukopisha ulwazi ekhasini lewebhu, lapho omunye umsebenzisi ekunikeza incwadi yokusebenzela ye-Excel eqediwe, noma uma wenza lesi sici sisebenze ngokwakho ngokucindezela izinkinobho ze-Alt + Enter.

Ngakho, ngezinye izikhathi ngenxa yokunqamuka komugqa kunzima ukuthola umusho, futhi okuqukethwe kukholamu kubukeka kuwubudlabha. Yingakho udinga ukuqinisekisa ukuthi yonke idatha itholakala kulayini owodwa. Lezi zindlela kulula ukuzisebenzisa. Sebenzisa oyithanda kakhulu:

• Khipha mathupha konke ukunqamuka kolayini ukuze ubuyisele idatha eseshidini 1 kwesejwayelekile.
• Susa ukuhlukana kwemigqa ngamafomula ukuze uqale ukucutshungulwa kolwazi oluyinkimbinkimbi. 
• Sebenzisa i-VBA macro. 
• Lahla ukunqamuka kolayini nge-Text Toolkit.

Sicela uqaphele ukuthi amagama asekuqaleni athi “Ukubuyiswa kwenqola” kanye “Nesiphakeli somugqa” asetshenziswa lapho kusetshenzwa ngomshini wokubhala. Ngaphezu kwalokho, babonisa izenzo ezi-2 ezahlukene. Ulwazi olwengeziwe mayelana nalokhu lungatholakala kunoma iyiphi insiza yereferensi.

Amakhompuyutha omuntu siqu nezinhlelo zokuhlela umbhalo zakhiwe mayelana nezici zomshini wokubhala. Kungakho, ukukhombisa ukunqanyulwa komugqa, kunezinhlamvu ezi-2 ezingaphrinteki: “I-Carriage return” (noma i-CR, ikhodi 13 kuthebula le-ASCII) kanye “Nesiphakeli somugqa” (LF, ikhodi 10 kuthebula le-ASCII). Ku-Windows, izinhlamvu ze-CR+LF zisetshenziswa ndawonye, ​​kodwa ku-*NIX, i-LF kuphela engasetshenziswa.

ukunakwa: I-Excel inezinketho zombili. Lapho ungenisa idatha kusuka kumafayela we-.txt noma .csv, inhlanganisela yezinhlamvu ze-CR+LF cishe izosetshenziswa. Uma usebenzisa inhlanganisela ye-Alt + Enter, kuzosetshenziswa kuphela ukunqanyulwa komugqa (LF). Kuzokwenzeka okufanayo lapho uhlela ifayela elitholwe kumuntu osebenza ohlelweni lokusebenza lwe-*Nix.

Khipha ikhefu lomugqa mathupha

Izinzuzo: lena indlela elula.

Okumbi: azikho izici ezengeziwe. 

Landela lezi zinyathelo:

1. Khetha amaseli ofuna ukuwakhipha noma ukushintsha ukunqamuka komugqa. 

2. Cindezela u-Ctrl + H ukuze uvule umsebenzi “Thola Bese Ufaka Esikhundleni”. 
3. In the “Thola” thayipha u-Ctrl + J, ngemva kwalokho kuzovela ichashazi elincane kulo. 
4. Emkhakheni "Kufakwe esikhundleni sika” faka noma yimuphi uhlamvu ukuze ungene esikhundleni sokunqamuka komugqa. Ungafaka isikhala ukuze amagama kumaseli angahlangani. Uma udinga ukususa amakhefu olayini, ungafaki lutho ku- “Kufakwe esikhundleni sika”.

5. Cindezela inkinobho "Faka Konke esikhundleni". 

Susa ukunqamuka komugqa ngamafomula e-Excel

Izinzuzo: kungenzeka ukusebenzisa uchungechunge lwamafomula okucubungula idatha eyinkimbinkimbi. Isibonelo, ungasusa ukunqamuka kolayini futhi ukhiphe izikhala ezengeziwe. 

Futhi, ungase udinge ukususa ukugoqa ukuze usebenze nedatha njengokuphikisana komsebenzi.

Okumbi: udinga ukudala ikholomu eyengeziwe futhi wenze izenzo ezisizayo.

1. Engeza ikholomu eyengeziwe kwesokudla. Yisho “umugqa 1”.
2. Kuseli lokuqala lale kholomu (C2), faka ifomula ezosusa ukunqamuka komugqa. Ngezansi kunezinhlanganisela ezihlukene ezifanele zonke izimo: 

• Ifanele izinhlelo zokusebenza zeWindows ne-Unix: 

=I-SUBSTITUTE(I-SUBSTITUTU(B2,CHAR(13),»»),CHAR(10),»»)

• Le fomula izokuvumela ukuthi ubeke omunye uhlamvu esikhundleni sokuhlukana komugqa. Kulokhu, idatha ngeke ihlangane ibe yinto eyodwa, futhi izikhala ezingadingekile ngeke zivele: 

=INKOMULA(I-SUBSTITUTE(B2,CHAR(13),»»),CHAR(10),», «)

• Uma udinga ukususa zonke izinhlamvu ezingaphrinteki, okuhlanganisa ukunqanyulwa komugqa, ifomula izoba usizo:

=HLANZEKILE(B2)

3. Phinda kabili ifomula kwamanye amaseli ekholomu. 
4. Uma kunesidingo, idatha evela kukholomu yasekuqaleni ingathathelwa indawo umphumela wokugcina:

• Khetha wonke amaseli kukholomu C bese ucindezela u-Ctrl + C ukuze ukopishe idatha.
• Manje khetha iseli B2 bese ucindezela u-Shift + F10 bese u-V.
• Susa ikholomu eyengeziwe.

I-VBA macro ukususa amakhefu emigqa

Izinzuzo: Uma isidaliwe, i-macro ingasetshenziswa kabusha kunoma iyiphi incwadi yokusebenzela.

Okumbi: Kuyadingeka ukuqonda VBA. 

I-macro yenza umsebenzi omuhle kakhulu wokukhipha ukunqamuka komugqa kuwo wonke amaseli ekhasini lokusebenza elisebenzayo. 

Susa ukuhlukana komugqa nge-Text Toolkit

Uma usebenzisa i-Text Toolkit noma i-Ultimate Suite ye-Excel, ngeke kudingeke ukuthi uchithe isikhathi kunoma yikuphi ukukhohlisa. 

Konke okudingeka ukwenze:

1. Khetha amaseli ofuna ukususa kuwo ukunqamuka komugqa.
2. Kuribhoni ye-Excel, hamba kuthebhu "Ablebits Data", bese kuyinketho "Iqembu Lombhalo” bese uchofoza inkinobho "Guqula" .

3. Kuphaneli "Guqula Umbhalo" khetha inkinobho yomsakazo "Guqula ukuhlukana komugqa kube “, ngena “Ukufaka esikhundleni” endaweni bese uchofoza "Guqula".

Lapha, ikhefu ngalinye lomugqa lithathelwa indawo yisikhala, ngakho-ke udinga ukubeka ikhesa yegundane endaweni bese ucindezela ukhiye u-Enter.

Uma usebenzisa lezi zindlela, uzothola ithebula elinedatha ehlelwe kahle.