Uhlamvu lwekhefu lomugqa ku-Excel. Indlela yokwenza ikhefu lomugqa kuseli le-Excel - zonke izindlela

Ngokuvamile abantu abasebenza kuhlelo lwesipredishithi se-Excel babhekana nesimo lapho badinga ukusonga umugqa. Ungakwazi ukusebenzisa le nqubo elula ngezindlela ezihlukahlukene. Esihlokweni, sizohlaziya ngokuningiliziwe zonke izindlela ezikuvumela ukuthi udlulise umugqa endaweni yokusebenza yedokhumenti yesipredishithi.

Ungawasusa kanjani amakhefu emigqa kumaseli ku-Excel 2013, 2010 kanye no-2007

Kunezindlela ezi-3 zokuqalisa ukususwa kwempahla yenqola ezinkambeni. Ezinye zazo zisebenzisa ukushintshwa kwezinhlamvu zokuhlukana komugqa. Izinketho okuxoxwe ngazo ngezansi zisebenza ngokufanayo ezinguqulweni eziningi zomhleli wespredishithi.

Ukugoqwa komugqa olwazini lombhalo kwenzeka ngenxa yezizathu ezahlukahlukene. Izizathu ezijwayelekile zihlanganisa izinto ezifana nokusebenzisa isinqamuleli sekhibhodi ye-Alt+Enter, kanye nokudlulisa idatha yombhalo isuka ekhasini lewebhu iye endaweni yokusebenza yohlelo lwesipredishithi. Sidinga ukususa ukubuya kwenqola, ngoba ngaphandle kwale nqubo akunakwenzeka ukusebenzisa usesho olujwayelekile lwemisho eqondile.

Kubalulekile! Ekuqaleni, amagama athi “Okuphakelayo komugqa” kanye nokuthi “Ukubuya kwenqola” ayesetshenziswa ngenkathi kusetshenzwa emishinini yokunyathelisa futhi abonisa izenzo ezi-2 ezihlukene. Amakhompiyutha omuntu adalwe kucatshangelwa imisebenzi yemishini yokunyathelisa.

Ikhipha izimbuyiselo zenqola mathupha

Ake sihlaziye indlela yokuqala ngokuningiliziwe.

• Inzuzo: ukubulawa ngokushesha.
• Ububi: Ukuntuleka kwezici ezengeziwe.

Imiyalelo enemininingwane ibukeka kanje:

1. Senza ukukhetha kwawo wonke amaseli lapho kudingeka khona ukusebenzisa lo msebenzi noma ukushintsha izinhlamvu.

2. Usebenzisa ikhibhodi, cindezela inhlanganisela yokhiye "Ctrl + H". Iwindi elibizwa ngokuthi "Thola futhi Umiselele" livele esikrinini.
3. Sibeka i-pointer emgqeni othi "Thola". Usebenzisa ikhibhodi, cindezela inhlanganisela yokhiye “Ctrl + J”. Kunechashaza elincane emgqeni.
4. Emugqeni othi “Faka esikhundleni ngokuthi” sifaka inani elithile elizofakwa esikhundleni sokubuyisela ikalishi. Imvamisa, kusetshenziswa indawo, ngoba ikuvumela ukuthi ukhiphe i-gluing yemisho emi-2 eseduze. Ukuze usebenzise ukususwa kokugoqwa komugqa, ulayini othi “Faka esikhundleni ngokuthi” akumele ugcwaliswe nganoma yiluphi ulwazi.

5. Usebenzisa i-LMB, chofoza okuthi "Faka esikhundleni Konke". Ilungile! Senze ukususwa kokubuyisela inqola.

Susa amakhefu emigqa usebenzisa amafomula e-Excel

• Inzuzo: ikhono lokusebenzisa amafomula ahlukahlukene enza ukuqinisekiswa okuyinkimbinkimbi kakhulu kolwazi lombhalo endaweni ekhethiwe. Isibonelo, ungasebenzisa ukususwa kwempahla yenqola, bese uthola izikhala ezingadingekile.
• Ububi: udinga ukudala ikholomu eyengeziwe, futhi wenze nenani elikhulu lokukhohlisa.

Imiyalelo enemininingwane ibukeka kanje:

1. Masisebenzise ukungezwa kwekholomu eyengeziwe ekugcineni kolwazi lokuqala. Kulesi sibonelo, izobizwa "umugqa ongu-1"
2. Esigabeni sokuqala sekholomu eyengeziwe (C1), sishayela ngefomula esebenzisa ukususwa noma ukushintshwa kwamakhefu olayini. Kusetshenziswa amafomula amaningana ukwenza lo msebenzi. Ifomula elungele ukusetshenziswa nezinhlanganisela zokubuya kwenqola kanye nokuphakelayo komugqa kubukeka kanje: =I-SUBSTITUTE(I-SUBSTITUTU(B2,CHAR(13),””);CHAR(10),””).
3. Ifomula efanelekile esikhundleni sokuhlukana komugqa ngohlamvu oluthile ibukeka kanje: =I-TRIMSPACES(SUBSTITUTE(SUBSTITUTE(B2,CHAR(13)),””);CHAR(10);”, “). Kuyaphawuleka ukuthi kuleli cala ngeke kube nokuhlanganiswa kwemigqa.
4. Ifomula yokususa zonke izinhlamvu ezingaphrinteki kudatha yombhalo ibonakala kanje: =IHLANZEKILE(B2).

5. Sikopisha ifomula, bese siyinamathisela kuseli ngalinye lekholomu eyengeziwe.
6. Ukwengeza, ungakwazi esikhundleni sekholomu yokuqala ufake entsha, lapho kuzosuswa khona ukunqamuka komugqa.
7. Senza ukukhetha kwawo wonke amaseli akukholomu C. Sibamba inhlanganisela ethi “Ctrl + C” kukhibhodi ukuze sisebenzise ukukopishwa kolwazi.
8. Sikhetha inkambu B2. Cindezela inhlanganisela yokhiye "Shift + F10". Ohlwini oluncane oluvelayo, chofoza i-LMB engxenyeni enegama elithi "Faka".
9. Masisebenzise ukukhishwa kwekholomu eyisizayo.

Susa amakhefu emigqa nge-VBA macro

• Inzuzo: ukudalwa kwenzeka isikhathi esingu-1 kuphela. Ngokuzayo, le macro ingasetshenziswa kwamanye amadokhumenti esipredishithi.
• Ububi: Udinga ukuqonda ukuthi ulimi lohlelo lwe-VBA lusebenza kanjani.

Ukuze usebenzise le ndlela, udinga ukungena efasiteleni lokufaka ama-macros bese ufaka ikhodi elandelayo lapho:

Goqa umbhalo ngeseli

Isihleli sespredishithi i-Excel ikuvumela ukuthi udlulisele ulwazi lombhalo endaweni. Lokhu kwenziwa ukuze idatha yombhalo iboniswe emigqeni eminingana. Ungenza inqubo yokusetha yenkambu ngayinye ukuze ukudluliswa kwedatha yombhalo kwenziwe ngokuzenzakalelayo. Ukwengeza, ungasebenzisa ukunqanyulwa komugqa mathupha.

Ukugoqa umbhalo okuzenzakalelayo

Ake sihlaziye ngokuningiliziwe indlela yokuqalisa ukudluliswa okuzenzakalelayo kwamanani ombhalo. I-algorithm yesinyathelo ngesinyathelo ibonakala kanje:

1. Sikhetha iseli edingekayo.
2. Esigatshaneni esithi "Ekhaya" sithola ibhulokhi yemiyalo ebizwa ngokuthi "Ukuqondanisa".
3. Usebenzisa i-LMB, khetha into ethi “Hambisa Umbhalo”.

Kubalulekile! Ulwazi oluqukethwe kumaseli luzodluliswa kucatshangelwa ububanzi bekholomu. Ukuhlela ububanzi bekholomu kuzolungisa ngokuzenzakalelayo ukugoqwa kwedatha yombhalo. 

Lungisa ubude bomugqa ukuze ubonise wonke umbhalo

Ake sihlaziye ngokuningiliziwe indlela yokuqalisa inqubo yokulungisa ubude bomugqa ukuze kuboniswe lonke ulwazi lombhalo. Imiyalelo enemininingwane ibukeka kanje:

1. Sikhetha amaseli esiwafunayo.
2. Esigatshaneni "Ekhaya" sithola ibhulokhi yemiyalo ebizwa ngokuthi "Amaseli".
3. Usebenzisa i-LMB, khetha into ethi “Fometha”.
4. Ebhokisini elithi “Usayizi Weseli”, kufanele wenze enye yezinketho ezichazwe ngezansi. Inketho yokuqala - ukuqondisa ngokuzenzakalelayo ubude bomugqa, chofoza i-LMB entweni ethi "Zilingana ngokuzenzakalelayo ubude bomugqa". Inketho yesibili ukusetha mathupha ubude bomugqa ngokuchofoza into ethi "Ubude bomugqa", bese ufaka inkomba oyifunayo emugqeni ongenalutho.

Ukufaka ikhefu lomugqa

Ake sihlaziye ngokuningiliziwe ukuthi singayisebenzisa kanjani inqubo yokufaka ikhefu lomugqa. Imiyalelo enemininingwane ibukeka kanje:

1. Ngokuchofoza kabili i-LMB, sikhetha inkambu lapho sifuna ukushayela khona ukunqamuka komugqa. Kuyaphawuleka ukuthi ungakhetha inkambu edingekayo, bese uchofoza "F2".
2. Ngokuchofoza kabili i-LMB, sikhetha indawo lapho kuzongezwa khona ukunqanyulwa komugqa. Cindezela inhlanganisela ye-Alt+Enter. Ilungile!

Indlela yokwenza ikhefu lomugqa kuseli le-Excel ngefomula

Ngokuvamile, abasebenzisi bomhleli wespredishithi bengeza amashadi namagrafu anhlobonhlobo endaweni yokusebenza. Ngokuvamile, le nqubo idinga ukugoqwa komugqa kulwazi lombhalo wenkambu. Ake sibone ngokuningiliziwe ukuthi singasisebenzisa kanjani lesi sikhathi.

Ifomula yokugoqa umugqa kumaseli e-Excel

Isibonelo, sine-histogram esetshenziswe kuhlelo lwesipredishithi. I-x-eksisi iqukethe amagama abasebenzi, kanye nolwazi mayelana nokudayiswa kwabo. Lolu hlobo lwesiginesha lulula kakhulu, njengoba lubonisa ngokucacile inani lomsebenzi owenziwa ngabasebenzi.

Kulula kakhulu ukusebenzisa le nqubo. Kuyadingeka ukwengeza i-opharetha ye-SYMBOL esikhundleni sefomula. Ikuvumela ukuthi usebenzise ukukhiqizwa kwezinkomba emikhakheni yokusayina imininingwane kumdwebo.

Vele, ensimini, ungasebenzisa inqubo yokugoqa umugqa noma yikuphi, ngenxa yenhlanganisela yezinkinobho ze-Alt + Enter. Nokho, le ndlela ayilungile ezimeni lapho kunedatha eningi kakhulu.

Indlela umsebenzi we-CHAR osebenza ngayo lapho usonga imigqa kuseli

Uhlelo lusebenzisa amakhodi asuka kuthebula lezinhlamvu ze-ASCII. Iqukethe amakhodi ezinhlamvu aboniswa esibukweni ku-OS. Ithebhulethi iqukethe amakhodi anezinombolo angamakhulu amabili namashumi amahlanu nanhlanu.

Umsebenzisi womhleli wethebula owazi lawa makhodi angawasebenzisa ku-opharetha we-CHAR ukuze asebenzise ukufakwa kwanoma yiluphi uhlamvu. Esibonelweni okukhulunywe ngaso ngenhla, kuyengezwa ikhefu lomugqa, elixhunywe nhlangothi zombili ze-"&" phakathi kwezinkomba zezinkambu C2 ne-A2. Uma imodi ebizwa ngokuthi “Hambisa umbhalo” ingenziwanga isebenze ensimini, lapho ke umsebenzisi ngeke abone ukuba khona kophawu lokuhlukana komugqa. Lokhu kungabonakala esithombeni esingezansi:

Kuyaphawuleka ukuthi kumashadi ahlukahlukene, ukuhlukana kwemigqa okungezwe kusetshenziswa ifomula kuzovezwa ngendlela evamile. Ngamanye amazwi, umugqa wombhalo uzohlukaniswa ube ngu-2 noma ngaphezulu.

Hlukanisa ube amakholomu ngokuhlukana komugqa

Uma umsebenzisi esigatshaneni esithi "Idatha" ekhetha into ethi "Umbhalo ngamakholomu", uzokwazi ukusebenzisa ukudluliswa kwemigqa nokuhlukaniswa kolwazi lokuhlola kumaseli amaningana. Inqubo yenziwa kusetshenziswa inhlanganisela ye-Alt + Enter. Ebhokisini elithi "Iwizadi yokusabalalisa umbhalo ngamakholomu", kufanele uhlole ibhokisi eliseduze nombhalo othi "okunye" bese ufaka inhlanganisela "Ctrl + J".

Uma uqoka ibhokisi eliseduze nombhalo othi “Izihlukanisi ezilandelanayo njengezodwa”, ungakwazi ukusebenzisa “ukugoqa” kokuhlukana kwemigqa eminingana ngokulandelana. Ekugcineni, chofoza "Okulandelayo". Ngenxa yalokho, sizothola:

Hlukanisa ube imigqa ngo-Alt + Enter ngokusebenzisa Umbuzo Wamandla

Kunezimo lapho umsebenzisi edinga ukuhlukanisa ulwazi lombhalo wemigqa eminingi hhayi kumakholomu, kodwa abe imigqa.

Ukuze usebenzise le nqubo, isengezo Sombuzo Wamandla, esivele kumhleli wespredishithi kusukela ngo-2016, sihle. Imiyalelo enemininingwane ibukeka kanje:

1. Ngokusebenzisa inhlanganisela "Ctrl + T", siguqula idatha yomthombo ibe ipuleti "ehlakaniphile". Enye inketho ukuya esigatshaneni esithi “Ekhaya” bese uchofoza i-LMB engxenyeni ethi “Fometha njengethebula”.
2. Hambisa esigatshaneni esithi “Idatha” bese uchofoza into ethi “Kusuka Kuthebula/Ububanzi”. Lokhu kusebenza kuzongenisa ipuleti ethuluzini Lombuzo Wamandla.

3. Sikhetha ikholomu enolwazi lombhalo wemigqa eminingi. Sithuthela esigatshaneni esithi "Ekhaya". Nweba uhlu lwenkomba ethi “Hlukanisa ikholomu” bese uchofoza i-LMB engxenyeni ethi “Ngesihlukanisi”.

4. Chofoza ku-"KULUNGILE" ukuze uqinisekise izinguquko ezenziwe. Ilungile!

Imakhro yokuhlukaniswa kube imigqa ngo-Alt+Enter

Ake sibone ukuthi singayisebenzisa kanjani le nqubo usebenzisa i-macro ekhethekile. Sivula i-VBA sisebenzisa inhlanganisela yokhiye we-Alt + F11 kukhibhodi. Ewindini elivelayo, chofoza okuthi "Faka", bese "Imojula". Lapha sengeza ikhodi elandelayo:

Sibuyela endaweni yokusebenza bese sikhetha izinkambu lapho ulwazi lwe-multiline lutholakala khona. Cindezela inhlanganisela ethi “Alt + F8” kukhibhodi ukuze uvule i-macro edaliwe.

iziphetho

Ngokusekelwe kumbhalo we-athikili, ungabona ukuthi kunenombolo enkulu yezindlela zokusebenzisa ukugoqwa komugqa kudokhumenti yesipredishithi. Ungenza le nqubo usebenzisa amafomula, opharetha, amathuluzi akhethekile namamakhro. Umsebenzisi ngamunye uzokwazi ukukhetha indlela elula kakhulu kuye.