Okuqukethwe
Ukuhlukana komugqa ngaphakathi kweseli efanayo, kwengezwe kusetshenziswa isinqamuleli sekhibhodi Alt+Faka kuyinto evamile futhi evamile. Ngezinye izikhathi zenziwa abasebenzisi ngokwabo ukwengeza ubuhle kumbhalo omude. Ngezinye izikhathi ukudluliselwa okunjalo kwengezwa ngokuzenzakalelayo lapho kuthululwa idatha kusuka kunoma yiziphi izinhlelo zokusebenza (sawubona 1C, SAP, njll.) Inkinga yukuthi akufanele nje uhloniphe amatafula anjalo, kodwa usebenze nawo - bese lezi zinhlamvu ezingabonakali zokudluliselwa zingaba inkinga. Futhi zingase zingabi - uma wazi ukuthi ungazisingatha kanjani ngendlela efanele.
Ake sibheke lolu daba ngokuningiliziwe.
Ukukhipha izinqamuleli zomugqa ngokufaka esikhundleni
Uma sidinga ukususa amakhonco, khona-ke into yokuqala evame ukufika engqondweni yindlela yakudala "yokuthola futhi ubeke esikhundleni". Khetha umbhalo bese ushayela iwindi lokumiselela ngesinqamuleli sekhibhodi Ctrl+H noma nge- Ikhaya - Thola bese Khetha - Faka esikhundleni (Ikhaya — Thola & Khetha — Faka esikhundleni). Ukungahambisani okukodwa - akucaci kahle ukuthi ungena kanjani emkhakheni ophezulu Ukuthola (Thola ini) umlingiswa wethu ongabonakali wokunqamuka komugqa. Alt+Faka lapha, ngeshwa, akusasebenzi, ukukopisha lolu phawu ngqo kusuka kuseli nokulunamathisela lapha nakho kuyahluleka.
Inhlanganisela izosiza Ctrl+J – lokho kungenye indlela Alt+Faka emabhokisini engxoxo e-Excel noma ezinkambu zokufaka:
Sicela uqaphele ukuthi ngemva kokufaka ikhesa ecwayizayo endaweni ephezulu bese ucindezela Ctrl+J - akukho lutho oluzovela ensimini ngokwayo. Ungesabi - lokhu kujwayelekile, uphawu alubonakali 🙂
Ensimini engezansi I-Substitute (Faka esikhundleni ngokuthi) noma ungafaki lutho, noma ufake isikhala (uma singafuni ukususa amakhonco kuphela, kodwa ukuwashintsha ngesikhala ukuze imigqa ingahlangani ibe yinto eyodwa). Vele ucindezele inkinobho Miselela yonke into (Faka Konke esikhundleni) futhi izikhonkwane zethu zizonyamalala:
umthunzi: ngemva kokwenza ukushintshwa okufakwe nge Ctrl+J umlingiswa ongabonakali uhlala ensimini Ukuthola futhi ingase iphazamise esikhathini esizayo - ungakhohlwa ukuyisusa ngokubeka ikhesa kulo mkhakha kanye nezikhathi ezimbalwa (ngokwethembeka) ngokucindezela okhiye. Susa и I-Backspace.
Ukukhipha ukuhlukana komugqa ngefomula
Uma udinga ukuxazulula inkinga ngamafomula, ungasebenzisa umsebenzi owakhelwe ngaphakathi PHRINTA (HLANZEKILE), engasula umbhalo wazo zonke izinhlamvu ezingaphrinteki, okuhlanganisa nokunqamuka kwethu kolayini okungalungile:
Nokho, le nketho ayilula ngaso sonke isikhathi, ngoba imigqa ngemva kwalokhu kusebenza ingahlanganiswa ndawonye. Ukuze uvimbele lokhu ukuthi kungenzeki, awudingi ukususa kuphela i-hyphen, kodwa ubeke indawo esikhundleni (bheka isigaba esilandelayo).
Ukushintsha ukuhlukana kwemigqa ngefomula
Futhi uma ungafuni ukususa kuphela, kodwa ukufaka esikhundleni Alt+Faka ku, ngokwesibonelo, indawo, bese kudingeka enye, ukwakhiwa okuyinkimbinkimbi kancane:
Ukusetha i-hyphen engabonakali sisebenzisa umsebenzi SYMBOL (CHAR), ekhipha uhlamvu ngekhodi yalo (10). Bese umsebenzi I-SUBSTITUTE (SUBSTITUTE) isesha amakhonco ethu kudatha yomthombo bese iwafaka esikhundleni sanoma yimuphi omunye umbhalo, ngokwesibonelo, ngesikhala.
Hlukanisa ube amakholomu ngokuhlukana komugqa
Ijwayele ithuluzi abaningi futhi Ukugunda kakhulu Umbhalo ngamakholomu kusuka kuthebhu Idatha (Idatha — Umbhalo Kumakholomu) ingaphinda isebenze kahle ngokuhlukana kwemigqa futhi ihlukanise umbhalo usuka kuseli eyodwa ube amaningana, uwuhlephule ngawo Alt+Faka. Ukuze wenze lokhu, esinyathelweni sesibili sewizadi, udinga ukukhetha okuhlukile kohlamvu lwe-delimiter yangokwezifiso Izincwajana zemininingwane (Ngokwezifiso) futhi sisebenzise isinqamuleli sekhibhodi esesivele sisazi Ctrl+J njengenye indlela Alt+Faka:
Uma idatha yakho ingase iqukathe ukuhlukana kwemigqa okuningana ngokulandelana, ungakwazi "ukuyigoqa" ngokuvula ibhokisi lokuthikha. Phatha abahlukanisi abalandelanayo njengento eyodwa (Phatha abahlukanisi abalandelanayo njengento eyodwa).
Ngemuva kokuchofoza Olandelayo (Olandelayo) futhi sidlula kuzo zonke izinyathelo ezintathu zewizadi, sithola umphumela oyifunayo:
Sicela uqaphele ukuthi ngaphambi kokwenza lo msebenzi, kuyadingeka ukuthi ufake inombolo eyanele yamakholomu angenalutho kwesokudla sekholomu ehlukanisiwe ukuze umbhalo owumphumela ungawakhiphi amanani (amanani) abengakwesokudla.
Hlukanisa ube imigqa ngo-Alt + Enter ngokusebenzisa Umbuzo Wamandla
Omunye umsebenzi othokozisayo ukuhlukanisa umbhalo wemigqa eminingi kusuka kuseli ngalinye hhayi kumakholomu, kodwa kube imigqa:
Kuthatha isikhathi eside ukwenza lokhu ngesandla, kunzima ngamafomula, akuwona wonke umuntu ongakwazi ukubhala i-macro. Kodwa ekusebenzeni, le nkinga ivela kaningi kunalokho esingathanda. Isixazululo esilula nesilula ukusebenzisa isengezo Sombuzo Wamandla kulo msebenzi, owakhiwe ku-Excel kusukela ngo-2016, futhi ezinguqulweni zangaphambili zika-2010-2013 ungalandwa mahhala ngokuphelele kuwebhusayithi ye-Microsoft.
Ukuze ulayishe idatha yomthombo kokuthi Umbuzo Wamandla, kufanele uqale uyiguqule ibe “kuthebula elihlakaniphile” ngesinqamuleli sekhibhodi. Ctrl+T noma ngenkinobho Fometha njengetafula ithebhu Ikhaya (Ekhaya — Fometha Njengethebula). Uma ngesizathu esithile ungafuni noma awukwazi ukusebenzisa "amatafula ahlakaniphile", ungasebenza "nabayiziphukuphuku". Kulokhu, vele ukhethe ububanzi boqobo bese usinika igama kuthebhu Amafomula – Isiphathi Segama – Okusha (Amafomula — Isiphathi Segama — Okusha).
Ngemva kwalokho, kuthebhu Idatha (uma une-Excel 2016 noma kamuva) noma kuthebhu Umbuzo Wamandla (uma une-Excel 2010-2013) ungachofoza inkinobho Kusukela kuthebula/ububanzi (Kusuka kuThebula/Ububanzi)ukulayisha ithebula lethu kusihleli Sombuzo Wamandla:
Ngemva kokulayisha, khetha ikholomu enombhalo wemigqa eminingi kumaseli bese ukhetha umyalo kuthebhu ethi Main Hlukanisa Ikholomu – Nge-Delimiter (Ikhaya — Hlukanisa Ikholomu — Nge-delimiter):
Kungenzeka ukuthi, Umbuzo Wamandla uzobona ngokuzenzakalelayo umgomo wokuhlukanisa futhi ufake uphawu ngokwalo #(lf) uhlamvu lwesiphakeli somugqa ongabonakali (lf = feed line = feed line) kunkambu yokufaka isihlukanisi. Uma kunesidingo, ezinye izinhlamvu zingakhethwa ohlwini lokudonsela phansi phansi efasiteleni, uma kuqala uqoka ibhokisi. Hlukanisa ngezinhlamvu ezikhethekile (Ihlukaniswe ngezinhlamvu ezikhethekile).
Ukuze yonke into ihlukaniswe yaba imigqa, hhayi amakholomu - ungakhohlwa ukushintsha isikhethi Imigqa (Ngemigqa) eqenjini lezinketho ezithuthukisiwe.
Okusele nje ukuchofoza OK futhi uthole okufunayo:
Ithebula eliqediwe lingalayishwa libuyiselwe eshidini kusetshenziswa umyalo Vala futhi ulayishe - Vala futhi ulayishe... ithebhu Ikhaya (Ekhaya — Vala&Layisha — Vala&Layisha ku…).
Kubalulekile ukuqaphela ukuthi uma usebenzisa i-Power Query, kufanele ukhumbule ukuthi uma idatha yomthombo ishintsha, imiphumela ayibuyekezwa ngokuzenzakalelayo, ngoba. lawa akuwona amafomula. Ukuze ubuyekeze, kufanele uchofoze kwesokudla kuthebula lokugcina eshidini bese ukhetha umyalo Buyekeza & Londoloza (Vuselela) noma cindezela inkinobho Vuselela konke ithebhu Idatha (Idatha — Vuselela Konke).
Imakhro yokuhlukaniswa kube imigqa ngo-Alt+Enter
Ukuqedela isithombe, ake siphinde sikhulume ngesixazululo senkinga yangaphambilini ngosizo lwe-macro. Vula i-Visual Basic Editor usebenzisa inkinobho yegama elifanayo kuthebhu Unjiniyela (Unjiniyela) noma izinqamuleli zekhibhodi Alt+F11. Ewindini elivelayo, faka imojuli entsha ngemenyu Faka – Imojula bese ukopisha ikhodi elandelayo lapho:
I-Sub Split_By_Rows() Dim cell As Range, n As Integer Set cell = ActiveCell For i = 1 To Selection.Rows.Count ar = Hlukanisa(iseli, Chr(10)) 'nquma inani lezicucu zeseli.Offset(1, 0 ).Shintsha usayizi(n, 1).I-EntireRow.Faka 'faka imigqa engenalutho ngezansi kweseli.Resize(n + 1, 1) = I-WorksheetFunction.Transpose(ar) 'faka kuwo idatha kusuka ohlwini Setha iseli = cell.Offset(n + 1, 0) 'shintshela kuseli elandelayo Okulandelayo futhi Qeda I-Sub
Buyela ku-Excel bese ukhetha amaseli anombhalo wemigqa eminingi ofuna ukuwahlukanisa. Bese usebenzisa inkinobho amamakhro ithebhu Unjiniyela (Unjiniyela - Amamakhro) noma isinqamuleli sekhibhodi Alt+F8ukusebenzisa i-macro edaliwe, ezokwenzela wonke umsebenzi:
Voila! Eqinisweni, abahleli bangabantu abavilaphayo abangathanda ukusebenza kanzima kanye bese bengenzi lutho 🙂
- Ukuhlanza umbhalo kumfucumfucu nezinye izinhlamvu
- Ukushintsha umbhalo nokuhlubula izikhala ezinganqamuki ngomsebenzi we-SUBSTITUTE
- Ungahlukanisa kanjani umbhalo onamathelayo ube izingxenye ku-Excel