Ungawasusa kanjani amaseli ku-Excel. Susa amaseli ahlakazekile nangenalutho, izindlela ezi-3 zokususa iseli ku-Excel

Enqubweni yokusebenza nemibhalo ye-Excel, abasebenzisi badinga njalo ukuthi bangafaki amaseli kuphela, kodwa futhi bawasuse. Inqubo ngokwayo ilula kakhulu, kodwa kunezindlela ezithile zokusebenzisa le nqubo ezingasheshisa futhi ziyenze ibe lula. Kulesi sihloko, sizohlola ngokuningiliziwe zonke izindlela zokususa amaseli kudokhumenti.

Inqubo yokususa amaseli

Izinto ezicatshangelwayo zethebula zingaba izinhlobo ezi-2: lezo eziqukethe ulwazi nezingenalutho. Unalokhu engqondweni, inqubo yokuwasusa izokwehluka, ngoba uhlelo ngokwalo lunikeza inketho yokuzenzakalela inqubo yokukhetha nokuqhubeka nokususa amaseli angadingekile.

Kufanele futhi kuthiwe lapha ukuthi inqubo yokususa isici esisodwa noma eziningi zetafula, ulwazi olukubo lungashintsha isakhiwo salo, ngoba ngenxa yezinyathelo ezithathiwe, ezinye izingxenye zetafula zingase zisuswe. Mayelana nalokhu, ngaphambi kokususa amaseli angadingekile, kuyadingeka ukucabangela imiphumela emibi futhi, ukuze uphephe, wenze ikhophi yokusekelayo yalo mbhalo.

Kubalulekile! Enqubweni yokususa amaseli noma izakhi ezimbalwa, futhi hhayi yonke imigqa namakholomu, ulwazi olungaphakathi kwethebula le-Excel luyagudluzwa. Ngakho-ke, ukuqaliswa kwenqubo okukhulunywa ngayo kufanele kubhekwe ngokucophelela.

Indlela 1: imenyu yokuqukethwe

Okokuqala, udinga ukucabangela ukuqaliswa kwenqubo okukhulunywa ngayo ngemenyu yokuqukethwe. Le ndlela ingenye evame kakhulu. Ingasetshenziselwa amaseli agcwalisiwe kanye nezinto ezingenalutho zetafula.

1. Kuyadingeka ukukhetha iseli elingu-1 noma izakhi ezimbalwa ezizosuswa. Ngokuchofoza okukhethiwe ngenkinobho yegundane engakwesokudla. Okulandelayo, kufanele uqalise imenyu yokuqukethwe. Kuyo, udinga ukukhetha ibhokisi lokuhlola elithi "Susa ...".
   

   

2. Iwindi elinemisebenzi emi-4 lizovezwa kumonitha. Njengoba sidinga ukususa amaseli ngokuqondile, hhayi imigqa yonke noma amakholomu, bese kukhethwa isenzo esingu-1 kwezingu-2 - ukususa izakhi nge-offset ohlangothini lwesobunxele noma nge-offset up. Ukukhethwa kwesenzo kufanele kusekelwe emisebenzini ethile ebhekene nomsebenzisi. Khona-ke, lapho inketho ethile ikhethiwe, isenzo siqinisekiswa ngokucindezela inkinobho ethi "KULUNGILE".
   

   

3. Njengoba kuhleliwe, zonke izici ezimakiwe ziyakhishwa kudokhumenti. Inketho yesi-2 (shift up) ikhethiwe, ngenxa yokuthi iqembu lamaseli angaphansi kwendawo ephawuliwe liye lanyuswa ngemigqa eminingi njengoba bekusesikhala esikhethiwe.
   

   

4. Uma ukhetha inketho yokuqala (shift kuya kwesokunxele), iseli ngalinye elingakwesokudla kwalawo asusiwe lizogudluzelwa kwesokunxele. Le nketho ingaba yinhle kakhulu esimweni sethu, njengoba bekukhona izici ezingenalutho kwesokudla sobubanzi obushiwo. Ngenxa yalokhu, ngaphandle kubonakala sengathi ulwazi lwesikhawu esimakiwe lwamane lwasulwa kuyilapho kugcinwa ubuqotho besakhiwo sombhalo. Nakuba, eqinisweni, umphumela ofanayo ufinyelelwa ngokuqondile ngenxa yokuthi izakhi zetafula ezithathe indawo yokuqala azinayo idatha kuzo.
   

   

Indlela yesi-2: Amathuluzi eRibhoni

Ungakwazi futhi ukususa amaseli kumathebula e-Excel usebenzisa amathuluzi anikezwe kuribhoni.

1. Ekuqaleni, udinga ukumaka ngandlela thize into ofuna ukuyisusa. Ngemuva kwalokho kufanele ushintshele kuthebhu eyinhloko bese uchofoza "Susa" (etholakala kumenyu ethi "Amaseli").
   

   

2. Manje ungabona ukuthi iseli elihloliwe likhishiwe etafuleni, futhi izakhi ezingaphansi kwalo zinyukile. Ngaphezu kwalokho, kufanele kugcizelelwe ukuthi le ndlela ngeke ikuvumele ukuthi unqume isiqondiso lapho izakhi zizosuswa khona ngemva kokususwa ngokwayo.
   

   

Lapho kudingekile ukususa iqembu elivundlile lamaseli usebenzisa le ndlela, kufanelekile ukunaka lezi zincomo ezilandelayo:

• Kukhethwa uhla lwamaseli avundlile. Chofoza "Susa" kuthebhu "Ekhaya".
  

  

• Njengasesimeni sangaphambilini, izakhi ezishiwo zisuswa nge-offset eya phezulu.
  

  

Uma iqembu elime mpo lamaseli lisuswa, ukushintshwa kwenzeka ngakolunye uhlangothi:

• Iqembu lama-elementi aqondile liyagqanyiswa. Chofoza okuthi "Susa" kuribhoni.
  

  

• Ungabona ukuthi ekupheleni kwale nqubo, izakhi ezimakiwe ziyasuswa ngokushintshwa kwesokunxele.
  

  

Manje njengoba imisebenzi eyisisekelo isimboziwe, kungenzeka ukusebenzisa indlela eguquguqukayo yokususa izakhi. Kubandakanya ukusebenza ngamathebula nobubanzi bamaseli avundlile naqondile:

• Isikhawu sedatha esidingekayo siyagqanyiswa futhi inkinobho yokususa etholakala kuribhoni iyacindezelwa.
• Uhlu olukhethiwe luyasuswa futhi amaseli aseduze ashintshelwe kwesokunxele.

Kubalulekile! Ukusebenzisa ukhiye othi Susa otholakala kuribhoni yethuluzi ngeke usebenze kakhulu kunokususa ngemenyu yokuqukethwe, njengoba kungavumeli umsebenzisi ukuthi alungise ukususwa kweseli.

Ukusebenzisa amathuluzi kuribhoni, kungenzeka ukususa izakhi ngokukhetha kusengaphambili isiqondiso sokushintsha. Kufanele ufunde ukuthi lokhu kwenziwa kanjani:

• Ububanzi obuzosuswa bugqanyisiwe. Kodwa-ke, manje kuthebhu ethi "Amaseli", akuyona inkinobho ethi "Susa" echofozwayo, kodwa unxantathu, otholakala ngakwesokudla kokhiye. Kumenyu ye-pop-up, chofoza "Susa Amaseli...".
  

  

• Manje ungabona iwindi selivele livela elinezinketho zokususa nokushintsha. Okuzofanela izinhloso ezithile kuyakhethwa, bese ucindezela ukhiye "KULUNGILE" ukuze uthole umphumela wokugcina. Isibonelo, kuzoba ukukhuphuka okukhuphukayo.
  

  

• Inqubo yokususa yaba yimpumelelo, futhi ukushintshwa kwenzeka ngokuqondile phezulu.
  

  

Indlela yesi-3: usebenzisa ama-hotkeys

Kungenzeka futhi ukwenza inqubo okukhulunywa ngayo usebenzisa iqoqo lezinhlanganisela ze-hotkey:

1. Khetha ububanzi kuthebula ofuna ukubususa. Khona-ke udinga ukucindezela inhlanganisela yezinkinobho "Ctrl" + "-" kukhibhodi.
   

   

2. Bese udinga ukuvula iwindi eselivele lijwayele ukususa amaseli kuthebula. Isiqondisindlela esifiswayo se-offset sikhethiwe futhi inkinobho ethi OK iyachofozwa.
   

   

3. Ngenxa yalokho, ungabona ukuthi amaseli akhethiwe ayasuswa ngendlela ye-offset eshiwo esigabeni sokugcina.
   

   

Indlela yesi-4: Ukususa izakhi ezihlukene

Kunezimo lapho ufuna ukususa ububanzi obuningi obungabhekwa njengokuhlangana, obutholakala ezindaweni ezihlukene kudokhumenti. Angasuswa kusetshenziswa enye yezindlela ezingenhla, ngokuphatha iseli ngalinye ngokwehlukana. Nokho, lokhu ngokuvamile kuthatha isikhathi esiningi. Kukhona inketho yokususa izakhi ezihlakazekile etafuleni, okusiza ukubhekana nomsebenzi ngokushesha okukhulu. Nokho, ngenxa yale njongo, kufanele baqale bahlonzwe.

1. Iseli lokuqala likhethwa ngendlela evamile, libambe inkinobho yegundane elingakwesokunxele bese liyizungeza ngekhesa. Okulandelayo, udinga ukubamba ukhiye "Ctrl" bese uchofoza ezintweni ezisalile ezihlakazekile noma uzungelezele ububanzi usebenzisa ikhesa ngokucindezela inkinobho yegundane kwesokunxele.
2. Khona-ke, lapho amaseli adingekayo ekhethiwe, kungenzeka ukufeza ukususwa nganoma iyiphi yezindlela ezingenhla. Ngemva kwalokho, wonke amaseli adingekayo azosuswa.
   

   

Indlela 5: Ukususa Amaseli Angenalutho

Uma umsebenzisi edinga ukususa amaseli angenalutho kudokhumenti, kungenzeka ukwenza inqubo okukhulunywa ngayo ngokuzenzakalelayo futhi ungakhethi i-elementi ngayinye ngayinye. Kunezinketho ezimbalwa zokuxazulula inkinga, kodwa indlela elula yokwenza lokhu ngokusebenzisa ithuluzi lokukhetha.

1. Kukhethwa ithebula noma obunye ububanzi eshidini lapho kudingeka khona ukususwa. Ngemuva kwalokho, ukhiye wokusebenza "F5" uchofozwa kukhibhodi.
   

   

2. Iwindi loshintsho livuliwe. Kuyo, udinga ukuchofoza inkinobho ethi "Khetha ...", etholakala ngezansi kwesokunxele.
   

   

3. Bese kuzovuleka iwindi lokukhetha amaqembu ezinto. Efasiteleni ngokwalo, iswishi isethelwe endaweni ethi “Amaseli Angenalutho”, bese inkinobho ethi “KULUNGILE” ichofozwa ezansi kwesokudla.
   

   

4. Ngemva kwalokho, ungabona ukuthi ngemva kwesenzo sokugcina, amaseli angenalutho ebangeni elimakiwe azogqanyiswa.
   

   

5. Manje umsebenzisi kuzomele akhiphe amaseli okukhulunywa ngawo nganoma yiziphi izinketho ezikhonjiswe ngenhla.

Indlela 1. Emahhadlahhadla futhi iyashesha

Ukuze ususe amaseli angadingekile kuthebula le-Excel ngendlela efanayo, landela lezi zinyathelo:

1. Khetha ububanzi obufunayo.
2. Khona-ke inkinobho esebenzayo "F5" icindezelwa, ngemuva kokhiye othi "Khetha (Okukhethekile)". Kumenyu evelayo, khetha "Izikhala" bese uchofoza "Kulungile". Khona-ke zonke izici ezingenalutho kububanzi kufanele zikhethwe.
   

   

3. Ngemva kwalokho, imenyu inikeza umyalo wokususa izici ezishiwo zethebula le-RMB - "Susa Amaseli (Susa Amaseli) ngokushintsha phezulu".

Indlela 2: I-Array Formula

Ukuze wenze kube lula inqubo yokususa amaseli angadingekile kuthebula, kufanele unikeze amagama ezibangeni zokusebenza ezidingekayo usebenzisa “Isiphathi Segama” kuthebhu “Amafomula”, noma – ku-Excel 2003 nangaphezulu – “Faka iwindi” – “Igama” - "Yabela".

Isibonelo, ububanzi B3:B10 buzoba negama elithi “IsEmpty”, ububanzi D3:D10 – “NoEmpty”. Izikhala kumele zibe nosayizi ofanayo, futhi zingatholakala noma yikuphi.

Ngemuva kokusebenza okwenziwe, ingxenye yokuqala yesikhawu sesibili (D3) ikhethiwe bese kufakwa ifomula elandelayo: =IF(UMUGQA()-UROW(Akunalutho)+1>NOTROWS(YesEmpty)-COUNTBLLANK(YesEmpty);”«;INDIRECT(IKHELI(IPHANSI KAKHULU((IF(Empty<>“«;ROW(ThereEmpty);ROW)) + IMIGWA (Akunalutho))); UMUGQA ()-UROW (Akunalutho) + 1); IKHOLOMU (Akunalutho); 4))).

Ifakwe njengefomula yohlu, ngemva kokufaka, udinga ukuchofoza "Ctrl + Shift + Enter". Ngemva kwalokho, ifomula okukhulunywa ngayo ingakopishwa phansi kusetshenziswa ukugcwaliswa okuzenzakalelayo (uphawu olumnyama lokuhlanganisa lunwebeka ekhoneni elingezansi kwesokudla lesici) - ngemva kwalokhu, ububanzi bangempela buzotholakala, kodwa ngaphandle kwezinto ezingenalutho.

Indlela 3. Umsebenzi wangokwezifiso ku-VBA

Uma umsebenzisi kufanele aphinde njalo ukusebenza okukhulunywa ngakho ukuze asuse amaseli angadingekile etafuleni, kunconywa ukuthi wengeze umsebenzi onjalo kusethi kanye futhi uyisebenzise esimweni ngasinye esilandelayo. Ngale njongo, i-Visual Basic Editor ivuliwe, imojula entsha engenalutho ifakiwe, futhi umbhalo womsebenzi uyakopishwa.

Kubalulekile ukuthi ungakhohlwa ukulondoloza ifayela bese ubuyela ku-Visual Basic Editor kuya ku-Excel. Ukusebenzisa umsebenzi okukhulunywa ngawo esibonelweni esithile:

1. Ububanzi obudingekayo bezinto ezingenalutho bugqanyisiwe, isibonelo F3:F10.
2. Vula ithebhu ethi “Faka”, bese “Umsebenzi”, noma cindezela inkinobho ethi “Faka Umsebenzi” esigabeni esithi “Amafomula” enguqulweni entsha yomhleli. Kumodi echazwe ngumsebenzisi, ama-NoBlanks akhethiwe.
3. Njengokuphikisana komsebenzi, cacisa ibanga lokuqala elinezikhala (B3:B10) bese ucindezela u-“Ctrl + Shift + Enter”, lokhu kuzokuvumela ukuthi ufake umsebenzi njengefomula yohlu.

Isiphetho

Ngokusekelwe ku-athikili, inani elikhulu lezindlela liyaziwa, lapho kungenzeka khona ukususa amaseli angadingekile kumathebula e-Excel. Kumele kuqashelwe ukuthi ukuqaliswa kweziningi zazo kuyafana, futhi kwezinye izimo inqubo iyafana ngempela. Ngakho-ke, abasebenzisi bangakhetha indlela ezokwenza bakwazi ukuxazulula inkinga ethile ngokushesha nangempumelelo. Ngaphezu kwalokho, umhleli ngokuqondile ngomsebenzi wokusula izici zetafula unikeza "izinkinobho ezishisayo" ezikuvumela ukuthi ulondoloze isikhathi ekusebenzeni okukhulunywa ngakho. Ngaphezu kwalokho, lapho idokhumenti iqukethe amaseli angenalutho, asikho isidingo sokukhetha ngalinye ngokuhlukile ukuze liphinde lisuswe. Ngalezi zinhloso, kungenzeka ukusebenzisa ithuluzi lokuqoqa, elikhetha ngokuzenzakalelayo izakhi ezingenayo idatha. Ngemva kwalokho, umsebenzisi udinga kuphela ukuwasusa nganoma iyiphi yezindlela ezingenhla.