Okuqukethwe
Lesi sifundo sizokwethula ngezindlela ezintathu zokususa ukubuyisela ikalishi kumaseli e-Excel. Uzofunda futhi ukuthi ungafaka kanjani ezinye izinhlamvu esikhundleni sokuhlukana kwemigqa. Zonke izixazululo eziphakanyisiwe zisebenza ku-Excel 2013, 2010, 2007 kanye no-2003.
Ukuhlukana kwemigqa kungavela embhalweni ngezizathu ezihlukahlukene. Ngokuvamile ukubuyiselwa kwenqola kwenzeka ebhukwini lokusebenza, isibonelo uma umbhalo ukopishwa ekhasini lewebhu, uma usuvele usebhukwini lokusebenza elitholwe yiklayenti, noma uma thina ngokwethu sengeza ngokucindezela izinkinobho. I-Alt + Enter.
Kungakhathaliseki ukuthi siyini isizathu sazo, inselele manje iwukukhipha izimbuyiselo zenqola, njengoba ziphazamisa ukusesha imisho futhi ziphumela emfuhlungwini wekholomu lapho ukugoqa kunikwe amandla.
Zontathu izindlela ezethulwe zishesha kakhulu. Khetha ekufanele kakhulu:
Qaphela: Ekuqaleni, amagama athi “Ukubuyiswa kwenqola” kanye “Nesiphakeli somugqa” ayesetshenziswa lapho kusetshenzwa ngomshini wokubhala futhi echaza imisebenzi emibili ehlukene. Umfundi othanda ukwazi angathola ngokuzimela ulwazi oluningiliziwe mayelana nalokhu ku-inthanethi.
Amakhompyutha nesofthiwe yokucubungula amagama kwaklanywa kucatshangelwa izici zomshini wokubhala. Yingakho izinhlamvu ezimbili ezihlukene ezingaphrinteki manje sezisetshenziswa ukukhombisa ukunqamuka komugqa: ukubuya kwenqola (Ukubuya kwenqola, CR noma ikhodi ye-ASCII 13) kanye Ukuhumusha komugqa (Isiphakeli somugqa, i-LF noma i-ASCII ikhodi 10). Ku-Windows, zombili izinhlamvu zisetshenziswa ndawonye, futhi ezinhlelweni ze-*NIX, kusetshenziswa imigqa emisha kuphela.
Qaphela: Zombili izinketho zitholakala ku-Excel. Lapho ungenisa kusuka kumafayela .txt or . Csv idatha ngokuvamile iqukethe ukubuyiswa kwenqola kanye nokuphakelayo kolayini. Uma ikhefu lomugqa lifakwa ngesandla ngokucindezela I-Alt + Enter, I-Excel ifaka uhlamvu olusha lomugqa kuphela. Uma ifayela . Csv itholwe kubalandeli be-Linux, i-Unix noma olunye uhlelo olufanayo, bese ulungiselela ukuhlangana nomlingiswa omusha kuphela.
Ikhipha izimbuyiselo zenqola mathupha
buhle: Le ndlela ishesha kakhulu.
bawo: Azikho izinzuzo ezengeziwe 🙁
Nansi indlela ongasusa ngayo ukunqamuka kolayini usebenzisa “Thola futhi ubeke esikhundleni":
- Khetha wonke amaseli lapho ufuna ukususa khona ukubuyisela ikalishi noma ubeke olunye uhlamvu esikhundleni salo.
- Cindezela Ctrl + Hukuletha ibhokisi lengxoxo Thola futhi ubeke esikhundleni (Thola bese Ufaka esikhundleni).
- Faka ikhesa ensimini Ukuthola (Thola ini) bese ucindezela I-Ctrl + J. Uma uthi nhlá, insimu izobonakala ingenalutho, kodwa uma ubhekisisa, uzobona ichashaza elincane kuyo.
- In the Kubuyiselwe esikhundleni (Faka esikhundleni Ngathi) faka noma yiliphi inani ozolifaka esikhundleni sezimpahla ezibuyiswayo. Ngokuvamile kusetshenziselwa isikhala kulokhu ukugwema ukunamathela ngephutha kwamagama amabili ancikene. Uma nje ufuna ukususa amakhefu emigqa, shiya inkambu Kubuyiselwe esikhundleni (Faka esikhundleni ngokuthi) akunalutho.
- Chofoza inkinobho Faka esikhundleni konke (Faka esikhundleni Konke) futhi ujabulele umphumela!
Susa amakhefu emigqa usebenzisa amafomula e-Excel
buhle: Ungasebenzisa amafomula alandelanayo noma akhiwe ukuze uqinisekise umbhalo oyinkimbinkimbi kuseli elicutshunguliwe. Isibonelo, ungasusa izimbuyiselo zenqola bese uthola izikhala ezengeziwe eziholayo noma ezilandelanayo, noma izikhala ezengeziwe phakathi kwamagama.
Kwezinye izimo, ukunqanyulwa kwemigqa kufanele kususwe ukuze kamuva kusetshenziswe umbhalo njengama-agumenti okusebenza ngaphandle kokwenza izinguquko kumaseli okuqala. Umphumela ungasetshenziswa, isibonelo, njengokuphikisana komsebenzi VIEW (BHEKA).
bawo: Uzodinga ukudala ikholomu yomsizi futhi wenze izinyathelo eziningi ezengeziwe.
- Engeza ikholomu eyisizayo ekugcineni kwedatha. Esibonelweni sethu, izobizwa Imigqa ye-1.
- Kuseli lokuqala lekholomu elisizayo (C2), faka ifomula ukuze ususe/ushintshe ukuhlukana komugqa. Ngezansi kukhona amafomula ambalwa awusizo ezikhathi ezihlukahlukene:
- Le fomula ilungele ukusetshenziswa nenhlanganisela ye-Windows ne-UNIX carriage return/line feed.
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;СИМВОЛ(13);"");СИМВОЛ(10);"")=SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),"") - Ifomula elandelayo ilungele ukufaka esikhundleni sokunqamuka komugqa nanoma yiluphi olunye uhlamvu (isibonelo, “, ” – ikhoma + isikhala). Kulokhu, imigqa ngeke ihlanganiswe futhi izikhala ezengeziwe ngeke zivele.
=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B2;СИМВОЛ(13);"");СИМВОЛ(10);", ")=TRIM(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),", ") - Futhi nansi indlela ongazisusa ngayo zonke izinhlamvu ezingaphrinteki embhalweni, okuhlanganisa nokunqamuka komugqa:
=ПЕЧСИМВ(B2)=CLEAN(B2)
- Le fomula ilungele ukusetshenziswa nenhlanganisela ye-Windows ne-UNIX carriage return/line feed.
- Kopisha ifomula kuwo wonke amaseli kukholamu.
- Ngokuzithandela, ungashintsha ikholomu yokuqala ufake entsha, kususwe ukunqanyulwa komugqa:
- Khetha wonke amaseli kukholamu C futhi ucindezela Ctrl + C kopisha idatha ebhodini lokunamathisela.
- Okulandelayo, khetha iseli B2, cindezela isinqamuleli sekhibhodi Shift + F10 bese Faka (Faka).
- Susa ikholomu yomsizi.
Susa amakhefu emigqa nge-VBA macro
buhle: Dala kanye – sebenzisa ngokuphindaphindiwe nanoma iyiphi incwadi yokusebenzela.
bawo: Okungenani ulwazi oluyisisekelo lwe-VBA luyadingeka.
I-VBA macro esibonelweni esilandelayo isusa ukubuyisela ikalishi kuwo wonke amaseli ekhasini lokusebenza elisebenzayo.
I-Sub RemoveCarriageReturns() Fiphalisa Ububanzi Bami Njengohlelo Lohlelo Lokusebenza.ScreenUpdating = Isicelo esingamanga.Calculation = xlCalculationManual For My Range Ngayinye Ku-ActiveSheet.UsedRange Uma 0 < InStr(MyRange, Chr(10)) Bese I-MyRange = Faka esikhundleni(MyRange, "Chr(10) ") Qeda Uma Isicelo Esilandelayo.ScreenUpdating = Uhlelo Lweqiniso.Calculation = xlCalculationAutomatic End Sub
Uma ungayazi kakhulu i-VBA, ngincoma ukuthi ufunde isihloko sendlela yokufaka nokusebenzisa ikhodi ye-VBA ku-Excel.