Izixhumanisi ku-Excel - ngokuphelele, isihlobo futhi ixubile. Amaphutha lapho usebenza nezixhumanisi ezihlobene ku-Excel

Ukwenza izibalo kusetshenziswa amafomula kwimodi ezenzakalelayo, izinkomba zamaseli ziyasetshenziswa. Ngokuya ngohlobo lokubhala, zihlukaniswe zibe izinhlobo ezintathu eziyinhloko:

1. Izixhumanisi ezihlobene. Isetshenziselwa izibalo ezilula. Ukukopisha ifomula kuhlanganisa nokushintsha izixhumanisi.
2. Izixhumanisi eziphelele. Uma udinga ukukhiqiza izibalo eziyinkimbinkimbi, lolu khetho lufanelekile. Ukuze ulungise sebenzisa uphawu "$". Isibonelo: $A$1.
3. izixhumanisi ezixubile. Lolu hlobo lwamakheli lusetshenziswa ekubaleni uma kudingekile ukulungisa ikholomu noma umugqa ngokuhlukana. Isibonelo: $A1 noma A$1.

Uma kudingekile ukukopisha idatha yefomula efakiwe, izinkomba ezinamakheli aphelele naxubile ziyasetshenziswa. I-athikili izodalula ngezibonelo ukuthi izibalo zenziwa kanjani kusetshenziswa izinhlobo ezahlukene zezixhumanisi.

Ireferensi yeseli ehlobene ku-Excel

Leli iqoqo lezinhlamvu ezichaza indawo yeseli. Izixhumanisi ohlelweni zibhalwa ngokuzenzakalelayo ngamakheli ahlobene. Isibonelo: A1, A2, B1, B2. Ukuya kumugqa noma ikholomu ehlukile kushintsha izinhlamvu zefomula. Isibonelo, ukuqala isikhundla A1. Ukuhamba ngokuvundlile kushintsha uhlamvu lube B1, C1, D1, njll. Ngendlela efanayo, izinguquko zenzeka lapho uhamba emugqeni oqondile, kuphela kulesi simo inombolo iyashintsha - A2, A3, A4, njll. Uma kudingekile ukuphindaphinda isibalo sohlobo olufanayo kuseli eliseduze, ukubala kwenziwa kusetshenziswa ireferensi ehlobene. Ukuze usebenzise lesi sici, landela izinyathelo ezimbalwa:

1. Ngokushesha nje lapho idatha ifakiwe kuseli, hambisa ikhesa bese uchofoza ngegundane. Ukugqamisa ngonxande oluhlaza kubonisa ukwenziwa kusebenze kweseli kanye nokulungela umsebenzi owengeziwe.
2. Ngokucindezela inhlanganisela yokhiye Ctrl + C, sikopisha okuqukethwe ebhodini lokunamathisela.
3. Senza kusebenze iseli ofuna ukudlulisa kulo idatha noma ifomula ebhalwe ngaphambilini.
4. Ngokucindezela inhlanganisela Ctrl + V sidlulisela idatha elondolozwe ebhodini lokunamathisela lohlelo.

Iseluleko sochwepheshe! Ukuze wenze uhlobo olufanayo lwezibalo kuthebula, sebenzisa i-life Hack. Khetha iseli eliqukethe ifomula efakwe ngaphambilini. Uhambisa ikhesa phezu kwesikwele esincane esivela ekhoneni elingezansi kwesokudla, futhi ubambe inkinobho yegundane kwesokunxele, hudula uye kumugqa ongezansi noma ikholomu eyeqile, kuye ngesenzo esenziwe. Ngokukhulula inkinobho yegundane, ukubala kuzokwenziwa ngokuzenzakalelayo. Leli thuluzi libizwa ngokuthi umaka wokugcwalisa okuzenzakalelayo.

Isibonelo sesixhumanisi esihlobene

Ukuze ukwenze kucace, cabanga ngesibonelo sokubala usebenzisa ifomula enereferensi ehlobene. Ake sithi umnikazi wesitolo sezemidlalo ngemva konyaka womsebenzi udinga ukubala inzuzo evela ekuthengisweni.

Ukuhleleka kwezenzo:

1. Isibonelo sibonisa ukuthi amakholomu B no-C asetshenziselwa ukugcwalisa inani lezimpahla ezithengisiwe kanye nenani lazo. Ngokufanelekile, ukuze ubhale ifomula futhi uthole impendulo, khetha ikholomu D. Ifomula ibukeka kanje: = B2*C

Nakani! Ukwenza lula inqubo yokubhala ifomula, sebenzisa iqhinga elincane. Faka uphawu “=", chofoza inani lezimpahla ezithengisiwe, setha uphawu “*” bese uchofoza inani lomkhiqizo. Ifomula ngemva kophawu olulinganayo izobhalwa ngokuzenzakalelayo.

2. Ukuze uthole impendulo yokugcina, cindezela "Faka". Okulandelayo, udinga ukubala inani eliphelele lenzuzo etholwe kwezinye izinhlobo zemikhiqizo. Hhayi-ke, uma inombolo yemigqa ingenkulu, khona-ke konke ukukhohlisa kungenziwa ngesandla. Ukugcwalisa inombolo enkulu yemigqa ngesikhathi esifanayo ku-Excel, kunomsebenzi owodwa owusizo owenza kube nokwenzeka ukudlulisa ifomula kwamanye amaseli.
3. Hambisa ikhesa ekhoneni elingezansi kwesokudla likanxande ngefomula noma umphumela ophelile. Ukubonakala kwesiphambano esimnyama kusebenza njengesibonakaliso sokuthi isikhombisi singahudulwa phansi. Ngakho-ke, ukubalwa okuzenzakalelayo kwenzuzo etholwe kumkhiqizo ngamunye ngokwehlukana kwenziwa.
4. Ngokukhulula inkinobho yegundane ecindezelwe, sithola imiphumela efanele kuyo yonke imigqa.

Ngokuchofoza kuseli D3, ungabona ukuthi izixhumanisi zamaseli zishintshiwe ngokuzenzakalelayo, futhi manje zibukeka kanjena: =B3*C3. Lokhu kulandela ukuthi izixhumanisi bezihlobene.

Amaphutha angenzeka uma usebenza nezixhumanisi ezihlobene

Ngokungangabazeki, lo msebenzi we-Excel wenza izibalo zibe lula, kodwa kwezinye izimo kungase kuphakame ubunzima. Ake sicabangele isibonelo esilula sokubala i-coefficient yenzuzo yempahla ngayinye:

1. Dala itafula bese ugcwalisa: A - igama lomkhiqizo; B - inani elithengisiwe; C - izindleko; D inani elitholiwe. Ake sithi kunezinto eziyi-11 kuphela ku-assortment. Ngakho-ke, kucatshangelwa incazelo yamakholomu, imigqa engu-12 igcwalisiwe futhi inani eliphelele lenzuzo ngu-D.
2. Chofoza kuseli E2 bese ufaka =D2/D13.
3. Ngemva kokucindezela inkinobho ethi "Enter", i-coefficient yesabelo esihlobene sokuthengiswa kwento yokuqala ivela.
4. Yelula ikholomu phansi bese ulinda umphumela. Nokho, uhlelo lunikeza iphutha elithi “#DIV/0!”

Isizathu sephutha ukusetshenziswa kwereferensi ehlobene nezibalo. Njengomphumela wokukopisha ifomula, izixhumanisi ziyashintsha. Okusho ukuthi, ku-E3, ifomula izobukeka kanje =D3/D13. Ngenxa yokuthi iseli D13 aligcwalisiwe futhi ngokwethiyori inenani elinguziro, uhlelo luzonikeza iphutha ngolwazi lokuthi ukuhlukaniswa ngoziro akunakwenzeka.

Kubalulekile! Ukuze ulungise iphutha, kuyadingeka ukubhala ifomula ngendlela yokuthi izixhumanisi ze-D13 zilungiswe. Ukukhuluma okuhlobene akunawo umsebenzi onjalo. Ukuze wenze lokhu, kukhona olunye uhlobo lwezixhumanisi - ngokuphelele. 

Usenza kanjani isixhumanisi esiphelele ku-Excel

Ngenxa yokusetshenziswa kophawu lwe-$, kube nokwenzeka ukulungisa izixhumanisi zamaseli. Ukuthi lokhu kusebenza kanjani, sizocubungula ngokuqhubekayo. Njengoba uhlelo lusebenzisa ikheli elihlobene ngokuzenzakalelayo, ngokufanele, ukuze likwenze kube ngokuphelele, uzodinga ukwenza inani lezenzo. Ake sihlaziye isisombululo sephutha "Indlela yokuthola i-coefficient ekuthengisweni kwezinto ezimbalwa zezimpahla", senza ukubala sisebenzisa ikheli eliphelele:

1. Chofoza ku-E2 bese ufaka izixhumanisi zesixhumanisi =D2/D13. Njengoba isixhumanisi sihlobene, uphawu kufanele lusethwe ukuze kulungiswe idatha.
2. Lungisa izixhumanisi zeseli D Ukwenza lesi senzo, yandulela uhlamvu olubonisa ikholomu nenombolo yomugqa ngokubeka uphawu “$”.

Iseluleko sochwepheshe! Ukwenza lula umsebenzi wokufaka, kwanele ukwenza iseli lisebenze ukuhlela ifomula bese ucindezela ukhiye F4 izikhathi eziningana. Uze uthole amanani agculisayo. Ifomula elungile imi kanje: =D2/$D$13.

3. Cindezela inkinobho ethi "Enter". Njengomphumela wezenzo ezenziwe, umphumela olungile kufanele uvele.
4. Hudula umaka emgqeni ongezansi ukuze ukopishe ifomula.

Ngenxa yokusetshenziswa kwamakheli aphelele ezibalweni, imiphumela yokugcina emigqeni esele izolunga.

Ungasifaka kanjani isixhumanisi esixubile ku-Excel

Ukuze kube nezibalo zefomula, akusetshenzisiwe kuphela izinkomba ezihlobene neziphelele, kodwa futhi ezixubile. Isici sabo esihlukile ukuthi balungisa enye yezixhumanisi.

• Isibonelo, ukuze ushintshe indawo yomugqa, kufanele ubhale uphawu lwe-$ phambi kwegama lezinhlamvu.
• Ngokuphambene nalokho, uma uphawu lwedola lubhalwa ngemva kokuqokwa kwencwadi, khona-ke izinkomba emgqeni zizohlala zingashintshiwe.

Kulokhu kulandela ukuthi ukuze kuxazululwe inkinga yangaphambilini ngokunquma i-coefficient yokuthengisa kwezimpahla usebenzisa ikheli elixubile, kuyadingeka ukulungisa inombolo yomugqa. Okusho ukuthi, uphawu lwe-$ lubekwe ngemva kohlamvu lwekholomu, ngoba izixhumanisi zalo azishintshi ngisho nakureferensi ehlobene. Ake sithathe isibonelo:

1. Ukuze uthole izibalo ezinembile, faka =D1/$D$3 bese ucindezela "Enter". Uhlelo lunikeza impendulo eqondile.
2. Ukuze uhambise ifomula kumaseli alandelayo phansi kwekholomu futhi uthole imiphumela enembile, hudula isibambo uye kuseli elingezansi.
3. Ngenxa yalokho, uhlelo luzonikeza izibalo ezifanele.

Qaphela! Uma usetha uphawu lwe-$ phambi kohlamvu, i-Excel izonikeza iphutha elithi “#DIV/0!”, okuzosho ukuthi lo msebenzi awukwazi ukwenziwa.

"SuperAbsolute" ekhuluma

Ekugcineni, ake sibheke esinye isibonelo sesixhumanisi esiphelele - ikheli elithi “SuperAbsolute”. Yiziphi izici zayo kanye nomehluko. Thatha cishe inombolo engu-30 bese uyifaka kuseli B2. Yile nombolo ezoba yiyona eyinhloko, kuyadingeka ukwenza uchungechunge lwezenzo nayo, isibonelo, ukuyiphakamisa emandleni.

1. Ukuze wenze zonke izenzo zenziwe ngendlela efanele, faka ifomula elandelayo kukholomu C: =$B$2^$D2. Kukholomu D sifaka inani lamadigri.

2. Ngemuva kokucindezela inkinobho ethi "Faka" nokwenza ifomula isebenze, selula umaka phansi kwekholomu.
3. Sithola imiphumela efanele.

Iphuzu elibalulekile ukuthi zonke izenzo ezenziwe zibhekiselwa kuseli eyodwa engaguquki B2, ngakho-ke:

• Ukukopisha ifomula kusuka kuseli C3 kuya kuseli E3, F3, noma H3 ngeke kuwushintshe umphumela. Izohlala ingashintshiwe - 900.
• Uma udinga ukufaka ikholomu entsha, izixhumanisi zeseli nefomula zizoshintsha, kodwa umphumela nawo uzohlala ungashintshiwe.

Lokhu okungavamile kwesixhumanisi "SuperAbsolute": uma udinga ukuhambisa umphumela ngeke ushintshe. Nokho, kunezimo lapho idatha ifakwa emithonjeni yezinkampani zangaphandle. Ngakho, amakholomu ashintshelwa ohlangothini, futhi idatha isethwe ngendlela endala kukholomu B2. Kwenzekani kulesi simo? Uma ixutshwe, ifomula iyashintsha ngokuya ngesenzo esenziwe, okungukuthi, ngeke lisakhomba ku-B2, kodwa ku-C2. Kodwa njengoba ukufakwa kwenziwe ku-B2, umphumela wokugcina uzobe ungalungile.

Ireferensi! Ukuze ukwazi ukufaka ama-macros avela emithonjeni yezinkampani zangaphandle, udinga ukunika amandla izilungiselelo zonjiniyela (zikhutshazwa ngokuzenzakalela). Ukuze wenze lokhu, iya kokuthi Izinketho, vula izilungiselelo zeribhoni bese uhlola ibhokisi elikukholomu elingakwesokudla elibhekene nethi “Unjiniyela”. Ngemuva kwalokho, ukufinyelela emisebenzini eminingi ebifihliwe ngaphambili emehlweni omsebenzisi ojwayelekile kuzovuleka.

Lokhu kubuza umbuzo: ingabe kungenzeka ukuguqula ifomula kuseli C2 ukuze inombolo yoqobo iqoqwe kuseli B, naphezu kokufakwa kwamakholomu edatha amasha? Ukuqinisekisa ukuthi izinguquko kuthebula akuthinti ukunqunywa kwesamba uma ufaka idatha evela emithonjeni yezinkampani zangaphandle, kufanele wenze lezi zinyathelo ezilandelayo:

1. Esikhundleni sezixhumanisi zeseli B2, faka izinkomba ezilandelayo: =I-INDIRECT(“B2”). Ngenxa yalokho, ngemva kokuhambisa ukwakheka kokubunjwa kuzobukeka kanje: =I-INDIRECT(“B2”)^$E2.
2. Ngenxa yalo msebenzi, isixhumanisi sihlala sikhomba isikwele esinezixhumanisi B2, kungakhathaliseki ukuthi amakholomu engeziwe noma asusiwe etafuleni.

Kumelwe kuqondwe ukuthi iseli elingenayo idatha lihlala libonisa inani elithi “0”.

Isiphetho

Ngenxa yokusetshenziswa kwezinhlobo ezintathu zezixhumanisi ezichaziwe, maningi amathuba avelayo enza kube lula ukusebenza ngezibalo ku-Excel. Ngakho-ke, ngaphambi kokuba uqale ukusebenza namafomula, qala ufunde izixhumanisi nemithetho yokufakwa kwazo.