Ungawagcwalisa kanjani amaseli angenalutho ngamavelu aphezulu ku-Excel

Ngemuva kokugcwalisa ithebula le-Excel ngamanani athile (imvamisa lapho wengeza uhlu lwemininingwane), imvamisa kuba nezikhala ezingenalutho. Ngeke ziphazamise ukucutshungulwa kwefayela elisebenzayo ngokwalo, nokho, zizokwenza kube nzima imisebenzi yokuhlunga, ukubala idatha, ukuhlunga izinombolo ezithile, amafomula, nemisebenzi. Ukuze uhlelo lusebenze ngaphandle kobunzima, kuyadingeka ukufunda indlela yokugcwalisa ama-voids ngamanani avela kumaseli angomakhelwane.

Ungawagqamisa kanjani amaseli angenalutho ekhasini lokusebenza

Ngaphambi kokuba uqale ukucabangela indlela yokugcwalisa amaseli angenalutho ekhasini lokusebenzela le-Excel, udinga ukufunda ukuthi uwakhetha kanjani. Lokhu kulula ukukwenza kuphela uma itafula lincane. Kodwa-ke, uma idokhumenti ihlanganisa inqwaba yamaseli, izikhala ezingenalutho zingatholakala ezindaweni ezithile. Ukukhetha mathupha kwamaseli ngamanye kuzothatha isikhathi eside, kuyilapho ezinye izikhala ezingenalutho zingeqiwa. Ukuze wonge isikhathi, kunconywa ukwenza le nqubo ngokuzenzakalelayo ngamathuluzi akhelwe ngaphakathi ohlelo:

1. Okokuqala, udinga ukumaka wonke amaseli ephepha lokusebenzela. Ukuze wenze lokhu, ungasebenzisa igundane kuphela noma wengeze okhiye SHIFT, CTRL ukuze ukhethe.
2. Ngemva kwalokho, cindezela inhlanganisela yokhiye kukhibhodi CTRL + G (enye indlela F5).
3. Iwindi elincane elibizwa ngokuthi Go To kufanele livele esikrinini.
4. Chofoza inkinobho ethi "Khetha".

Ukuze umake amaseli kuthebula, kubha yamathuluzi eyinhloko, udinga ukuthola umsebenzi othi "Thola futhi Khetha". Ngemuva kwalokho kuzovela imenyu yokuqukethwe, lapho udinga ukukhetha khona ukukhethwa kwamanani athile - amafomula, amaseli, ama-constants, amanothi, amaseli amahhala. Khetha umsebenzi “Khetha iqembu lamaseli. Okulandelayo, iwindi lezilungiselelo lizovuleka lapho udinga ukuhlola ibhokisi eliseduze nepharamitha ethi "Amaseli angenalutho". Ukuze ulondoloze izilungiselelo, udinga ukuchofoza inkinobho ethi "KULUNGILE".

Ungawagcwalisa kanjani amaseli angenalutho ngesandla

Indlela elula yokugcwalisa amaseli angenalutho ekhasini lokusebenza ngamavelu asuka kumaseli aphezulu iwumsebenzi othi “Gcwalisa amaseli angenalutho”, otholakala kuphaneli ye-XLTools. Inqubo:

1. Cindezela inkinobho ukuze uvule umsebenzi othi "Gcwalisa amaseli angenalutho".
2. Iwindi lezilungiselelo kufanele livuleke. Ngemuva kwalokho, kuyadingeka ukumaka ububanzi bamaseli lapho kudingekile ukugcwalisa izikhala ezingenalutho.
3. Nquma indlela yokugcwalisa - kusukela kuzinketho ezitholakalayo okudingeka ukhethe: kwesokunxele, kwesokudla, phezulu, phansi.
4. Maka ibhokisi eliseduze kokuthi “Susa Ukuhlanganisa Amaseli”.

Kusele ukucindezela inkinobho ethi "KULUNGILE" ukuze amaseli angenalutho agcwaliswe ngolwazi oludingekayo.

Kubalulekile! Esinye sezici eziwusizo zalo msebenzi ukugcinwa kwamanani asethiwe. Ngenxa yalokhu, kuzokwazi ukuphinda isenzo ngohlu olulandelayo lwamaseli ngaphandle kokumisa kabusha umsebenzi.

Amanani atholakalayo okugcwalisa amaseli angenalutho

Kunezinketho eziningi zokugcwalisa amaseli angenalutho ekhasini lokusebenzela le-Excel:

1. Gcwalisa kwesokunxele. Ngemva kokuvula lo msebenzi, amaseli angenalutho azogcwaliswa ngedatha evela kumaseli angakwesokudla.
2. Gcwalisa kwesokudla. Ngemva kokuchofoza leli nani, amaseli angenalutho azogcwaliswa ngolwazi olusuka kumaseli angakwesokunxele.
3. Gcwalisa. Amaseli angaphezulu azogcwaliswa ngedatha evela kumaseli angezansi.
4. Ukugcwalisa. Inketho edume kakhulu yokugcwalisa amaseli angenalutho. Ulwazi olusuka kumaseli angenhla ludluliselwa kumaseli ethebula elingezansi.

Umsebenzi othi "Gcwalisa amaseli angenalutho" ukopisha ngqo lawo manani (izinombolo, izinhlamvu) atholakala kumaseli agcwalisiwe. Nokho, kukhona ezinye izici lapha:

1. Ngisho nalapho ufihla noma uvimba iseli egcwele, ulwazi oluvela kuyo luzodluliselwa kuseli yamahhala ngemva kokuvula lo msebenzi.
2. Ngokuvamile kwenzeka izimo lapho inani lokudlulisa liwumsebenzi, ifomula, isixhumanisi esiya kwamanye amaseli ekhasini lokusebenzela. Kulesi simo, iseli elingenalutho lizogcwaliswa ngenani elikhethiwe ngaphandle kokulishintsha.

Kubalulekile! Ngaphambi kokuvula umsebenzi othi "Gcwalisa amaseli angenalutho", udinga ukuya kuzilungiselelo zeshidi lokusebenzela, ubone ukuthi kukhona yini ukuvikeleka. Uma inikwe amandla, ulwazi ngeke ludluliswe.

Ukugcwalisa amaseli angenalutho ngefomula

Indlela elula nesheshayo yokugcwalisa amaseli kuthebula ledatha kusuka kumaseli angomakhelwane iwukusebenzisa ifomula ekhethekile. Inqubo:

1. Maka wonke amaseli angenalutho ngendlela echazwe ngenhla.
2. Khetha umugqa wokufaka amafomula e-LMB noma cindezela inkinobho F
3. Faka uphawu “=".

4. Ngemva kwalokho, khetha iseli etholakala ngenhla. Ifomula kufanele ikhombise iseli lapho ulwazi luzokopishelwa khona kuseli yamahhala.

Isenzo sokugcina ukucindezela inhlanganisela yokhiye "CTRL + Enter" ukuze ifomula isebenze kuwo wonke amaseli amahhala.

Kubalulekile! Akufanele sikhohlwe ukuthi ngemva kokusebenzisa le ndlela, wonke amaseli amahhala azogcwaliswa ngamafomula. Ukuze ulondoloze i-oda etafuleni, kunconywa ukuthi esikhundleni sazo kube namanani ezinombolo.

Ukugcwalisa amaseli angenalutho nge-macro

Esimweni lapho kufanele ugcwalise njalo amaseli angenalutho kumakhasi okusebenza, kunconywa ukuthi wengeze i-macro ohlelweni, uyisebenzise kamuva ukuze wenze inqubo yokukhetha, ugcwalise amaseli angenalutho. Gcwalisa ikhodi ye-macro:

I-Sub Fill_Blanks()

    Ngeseli Ngalinye Ekukhetheni

        Uma IsEmpty(cell) Bese kuba cell.Value = cell.Offset(-1, 0).Value

    Olandelayo iseli

Ukuphela Okungaphansi

Ukwengeza i-macro, udinga ukwenza izenzo ezimbalwa:

1. Cindezela inhlanganisela yokhiye ALT+F
2. Lokhu kuzovula umhleli we-VBA. Namathisela ikhodi engenhla efasiteleni lamahhala.

Kusele ukuvala iwindi lezilungiselelo, bonisa isithonjana esikhulu kuphaneli yokufinyelela okusheshayo.

Isiphetho

Phakathi kwezindlela ezichazwe ngenhla, udinga ukukhetha evumelana kakhulu nesimo esithile. Indlela yesandla yokwengeza idatha ezindaweni zamahhala zeshidi lokusebenzela ilungele ukujwayelana okujwayelekile, ukusetshenziswa kanye. Ngokuzayo, kunconywa ukuthi ukwazi kahle ifomula noma ubhalise i-macro (uma inqubo efanayo yenziwa kaningi).