Indlela yokufriza iseli kufomula ye-Excel

Ngokuvamile, abasebenzisi babhekana nesidingo sokuphina iseli kufomula. Isibonelo, kwenzeka ezimeni lapho ufuna ukukopisha ifomula, kodwa ukuze isixhumanisi singayi phezulu naphansi ngenani elifanayo lamaseli njengoba likopishwe endaweni yalo yasekuqaleni.

Kulokhu, ungalungisa ireferensi yeseli ku-Excel. Futhi lokhu kungenziwa ngezindlela eziningana ngesikhathi esisodwa. Ake sihlolisise indlela yokufeza lo mgomo.

Yini isixhumanisi se-Excel

Ishidi lakhiwe ngamaseli. Ngayinye yazo iqukethe ulwazi oluthile. Amanye amaseli angayisebenzisa ekubaleni. Kodwa baqonda kanjani ukuthi bangayithola kuphi idatha? Kuyabasiza ukuthi benze izixhumanisi.

Isixhumanisi ngasinye sibonisa iseli elinohlamvu olulodwa nenombolo eyodwa. Uhlamvu lumelela ikholomu futhi inombolo imele umugqa. 

Kunezinhlobo ezintathu zezixhumanisi: okuphelele, okuhlobene nokuxubile. Eyesibili isethwe ngokuzenzakalelayo. Isithenjwa esiphelele yileso esinekheli elingashintshi lakho kokubili ikholomu nekholomu. Ngokuvumelana nalokho, okuxutshiwe yilokho lapho ikholomu ehlukile noma umugqa kumiswa khona.

Indlela engu-1

Ukuze ulondoloze kokubili amakheli ekholomu nemigqa, landela lezi zinyathelo:

1. Chofoza iseli eliqukethe ifomula.
2. Chofoza ibha yefomula yeseli esiyidingayo.
3. Cindezela i-F4.

Ngenxa yalokho, ireferensi yeseli izoshintsha ibe i-absolute. Ingabonwa ngophawu lwe-dollar oluyisici. Isibonelo, uma uchofoza kuseli B2 bese uchofoza ku-F4, isixhumanisi sizobukeka kanje: $B$2.

Lusho ukuthini uphawu lwedola ngaphambi kwengxenye yekheli ngeseli ngayinye?

1. Uma ibekwe phambi kwencwadi, ibonisa ukuthi inkomba yekholomu ihlala injalo, kungakhathaliseki ukuthi ifomula ihanjiswe kuphi.
2. Uma uphawu lwedola luphambi kwenombolo, lubonisa ukuthi intambo iphiniwe. 

Indlela engu-2

Le ndlela icishe ifane neyangaphambili, kuphela udinga ukucindezela u-F4 kabili. isibonelo, uma besineseli B2, ngemva kwalokho izoba ngu-B$2. Ngamazwi alula, ngale ndlela sikwazile ukulungisa umugqa. Kulokhu, uhlamvu lwekholomu luzoshintsha.

Kuyinto elula kakhulu, isibonelo, kumathebula lapho udinga ukukhombisa okuqukethwe kweseli yesibili ukusuka phezulu kuseli elingezansi. Esikhundleni sokwenza ifomula enjalo izikhathi eziningi, kwanele ukulungisa umugqa futhi uvumele ikholomu ishintshe.

Indlela engu-3

Lokhu kufana ngokuphelele nendlela yangaphambilini, udinga kuphela ukucindezela ukhiye we-F4 izikhathi ezintathu. Khona-ke ireferensi kuphela yekholomu izoba iphelele, futhi umugqa uzohlala ungashintshi.

Indlela engu-4

Ake sithi sinereferensi ephelele yeseli, kodwa lapha bekudingeka ukuze sihlobane. Ukwenza lokhu, cindezela ukhiye we-F4 izikhathi eziningi kangangokuthi azikho izimpawu ze-$ kusixhumanisi. Khona-ke izoba isihlobo, futhi lapho uhambisa noma ukopisha ifomula, kokubili ikheli lekholomu nekheli lomugqa kuzoshintsha.

Ukuphina amaseli kububanzi obukhulu

Siyabona ukuthi lezi zindlela ezingenhla azibonisi nhlobo ubunzima bokwenza. Kodwa imisebenzi iqondile. Futhi, isibonelo, yini okufanele siyenze uma sinamafomula ambalwa ngesikhathi esisodwa, izixhumanisi okudingeka ziguqulwe zibe yizo eziphelele. 

Ngeshwa, izindlela ezijwayelekile ze-Excel ngeke zifinyelele lo mgomo. Ukuze wenze lokhu, udinga ukusebenzisa i-addon ekhethekile ebizwa nge-VBA-Excel. Iqukethe izici eziningi ezengeziwe ezikuvumela ukuthi wenze imisebenzi evamile nge-Excel ngokushesha okukhulu.

Ihlanganisa imisebenzi engaphezu kwekhulu echazwe ngabasebenzisi kanye nama-macros ahlukene angama-25, futhi ibuye ibuyekezwe njalo. Ikuvumela ukuthi uthuthukise umsebenzi cishe nganoma yisiphi isici:

1. Amaseli.
2. IMacro
3. Imisebenzi yezinhlobo ezahlukene.
4. Izixhumanisi nama-array.

Ikakhulukazi, lesi sengezo sikuvumela ukuthi ulungise izixhumanisi ngenani elikhulu lamafomula ngesikhathi esisodwa. Ukuze wenze lokhu, kufanele wenze lezi zinyathelo ezilandelayo:

1. Khetha ububanzi.
2. Vula ithebhu ye-VBA-Excel ezovela ngemva kokufakwa. 
3. Vula imenyu ethi "Imisebenzi", lapho inketho ethi "Khiya amafomula" ikhona.
   

   

4. Okulandelayo, ibhokisi lengxoxo lizovela lapho udinga ukucacisa ipharamitha edingekayo. Le addon ikuvumela ukuthi uphine ikholomu nekholomu ngokuhlukana, ndawonye, ​​futhi ususe ukuphina osevele kukhona ngephakheji. Ngemuva kokuthi ipharamitha edingekayo ikhethiwe kusetshenziswa inkinobho yomsakazo ehambisanayo, udinga ukuqinisekisa izenzo zakho ngokuchofoza "KULUNGILE".

Isibonelo

Ake sithathe isibonelo ukuze sikwenze kucace kakhudlwana. Ake sithi sinolwazi oluchaza izindleko zempahla, inani lazo eliphelele kanye nemali engenayo yokuthengisa. Futhi sibhekene nomsebenzi wokwenza itafula, ngokusekelwe ebuningini nasezindlekweni, sinqume ngokuzenzakalelayo ukuthi ingakanani imali esikwazile ukuyizuza ngaphandle kokudonsa ukulahlekelwa.

Esibonelweni sethu, kulokhu udinga ukufaka ifomula =B2*C2. Kulula kakhulu, njengoba ubona. Kulula kakhulu ukusebenzisa isibonelo sakhe ukuchaza ukuthi ungalungisa kanjani ikheli leseli noma ikholomu noma umugqa wayo. 

Yebo, kulesi sibonelo, ungazama ukuhudulela ifomula phansi usebenzisa umaka wokugcwalisa okuzenzakalelayo, kodwa kulokhu, amaseli azoshintshwa ngokuzenzakalelayo. Ngakho-ke, kuseli D3 kuzoba nenye ifomula, lapho izinombolo zizoshintshwa khona, ngokulandelana, ngo-3. Ngaphezu kwalokho, ngokusho kohlelo - D4 - ifomula izothatha ifomu = B4 * C4, D5 - ngokufanayo, kodwa nge inombolo 5 njalo njalo.

Uma kudingekile (ezimweni eziningi kuvela), azikho izinkinga. Kodwa uma udinga ukulungisa ifomula esitokisini esisodwa ukuze ingashintshi lapho uhudula, khona-ke lokhu kuzoba nzima kakhulu. 

Ake sithi sidinga ukunquma imali engenayo yedola. Asibeke kuseli B7. Ake sithole i-nostalgic encane futhi sibonise izindleko zama-ruble angu-35 ngedola ngalinye. Ngakho-ke, ukuze unqume imali engenayo ngamadola, kuyadingeka ukuhlukanisa inani lama-ruble ngesilinganiso sokushintshaniswa kwedola.

Nakhu ukuthi kubukeka kanjani esibonelweni sethu.

Uma thina, ngokufanayo nenguqulo yangaphambilini, sizama ukunikeza ifomula, sizobe sehluleka. Ngokufanayo, ifomula izoshintsha iye kwefanele. Esibonelweni sethu, kuzoba kanje: =E3*B8. Ukusuka lapha singabona. ukuthi ingxenye yokuqala yefomula isiphenduke i-E3, futhi sizibekela lo msebenzi, kodwa asikho isidingo sokushintsha ingxenye yesibili yefomula ibe yi-B8. Ngakho-ke, sidinga ukuguqula ireferensi ibe yi-absolute. Ungakwenza lokhu ngaphandle kokucindezela ukhiye we-F4, ngokubeka nje uphawu lwedola.

Ngemva kokuba siguqule ireferensi yeseli yesibili yaba iseli eliphelele, yavikeleka ezinguqukweni. Manje usungayihudula ngokuphephile usebenzisa isibambo sokugcwalisa ngokuzenzakalelayo. Yonke idatha engashintshi izohlala injalo, kungakhathaliseki ukuthi ifomula ikuyiphi, futhi idatha engazibophezeli izoshintsha kalula. Kuwo wonke amaseli, imali engenayo ngama-ruble achazwe kulo mugqa izohlukaniswa ngenani lokushintshisana ngedola elifanayo.

Ifomula ngokwayo izobukeka kanje:

=D2/$B$7

Qaphela! Sibonise izimpawu ezimbili zamadola. Ngale ndlela, sibonisa uhlelo ukuthi kokubili ikholomu nomugqa kufanele kulungiswe.

Izinkomba zeseli kumamakhro

I-macro iyi-subroutine ekuvumela ukuthi wenze ngokuzenzakalelayo izenzo. Ngokungafani nokusebenza okujwayelekile kwe-Excel, i-macro ikuvumela ukuthi usethe ngokushesha iseli elithile futhi wenze izenzo ezithile ngemigqa embalwa yekhodi. Iwusizo ekucubunguleni ulwazi lweqoqo, isibonelo, uma ingekho indlela yokufaka izengezo (isibonelo, kusetshenziswa ikhompyutha yenkampani, hhayi eyomuntu siqu).

Okokuqala udinga ukuqonda ukuthi umqondo oyinhloko we-macro yizinto ezingaqukatha ezinye izinto. Inhloso ye-Workbooks inesibopho sencwadi ye-elekthronikhi (okungukuthi, idokhumenti). Kuhlanganisa into ethi AmaSpredishithi, okuyiqoqo lawo wonke amashidi edokhumenti evuliwe. 

Ngokunjalo, amaseli ayinto yamaseli. Iqukethe wonke amaseli eshidi elithile.

Into ngayinye ifaneleka ngezimpikiswano ezifakwe abakaki. Endabeni yamaseli, abhekisela kulolu hlelo. Inombolo yomugqa ibalwe kuqala, ilandelwe yinombolo yekholomu noma uhlamvu (zombili amafomethi amukelekile).

Isibonelo, umugqa wekhodi oqukethe ireferensi yeseli C5 ungabukeka kanje:

Izincwadi Zokusebenza(“Book2.xlsm”).Amashidi(“Uhlu2”).Amaseli(5, 3)

Izincwadi Zokusebenza(“Ibhuku2.xlsm”).Amashidi(“Uhlu2”).Amaseli(5, “C”)

Ungakwazi futhi ukufinyelela iseli usebenzisa into Icocekile. Ngokuvamile, kuhloswe ukunikeza ireferensi kububanzi (izakhi zazo, ngendlela, ezingaba ngokuphelele noma isihlobo), kodwa ungamane unikeze igama leseli, ngefomethi efanayo naleyo ekudokhumenti ye-Excel.

Kulokhu, umugqa uzobukeka kanje.

Izincwadi Zokusebenzela(“Ibhuku2.xlsm”).Amashidi(“Uhlu2”).Ububanzi(“C5”)

Kungase kubonakale sengathi le nketho ilula kakhulu, kodwa inzuzo yezinketho ezimbili zokuqala ukuthi ungasebenzisa okuguquguqukayo kubakaki futhi unikeze isixhumanisi esingasenasici, kodwa into efana nesihlobo, esizoncika emiphumeleni izibalo.

Ngakho-ke, ama-macros angasetshenziswa ngempumelelo ezinhlelweni. Eqinisweni, zonke izinkomba zamaseli noma ububanzi lapha zizoba ziphelele, ngakho-ke zingalungiswa kanye nawo. Yiqiniso, akulula kangako. Ukusetshenziswa kwama-macros kungaba usizo lapho ubhala izinhlelo eziyinkimbinkimbi ezinenani elikhulu lezinyathelo ku-algorithm. Ngokuvamile, indlela evamile yokusebenzisa izinkomba eziphelele noma ezihlobene ilula kakhulu. 

iziphetho

Sithole ukuthi iyini ireferensi yeseli, ukuthi isebenza kanjani, ingenzela ini. Sawuqonda umehluko phakathi kwezinkomba eziphelele nezihlobene futhi sathola ukuthi yini okufanele yenziwe ukuze siguqule uhlobo oluthile lube olunye (ngamagama alula, lungisa ikheli noma ususe ukuphina). Sithole ukuthi ungakwenza kanjani ngokushesha ngenani elikhulu lamanani. Manje usunamandla okusebenzisa lesi sici ezimeni ezifanele.