Maulalo mu Excel - mtheradi, wachibale komanso wosakanikirana. Zolakwika mukamagwira ntchito ndi maulalo achibale mu Excel

Kuti muthandizire kuwerengera pogwiritsa ntchito ma fomula munjira yokhayokha, zolozera zama cell zimagwiritsidwa ntchito. Kutengera ndi mtundu wa zolemba, iwo amagawidwa m'magulu atatu:

1. Maulalo achibale. Amagwiritsidwa ntchito powerengera zosavuta. Kukopera chilinganizo kumafuna kusintha ma coordinates.
2. Mtheradi maulalo. Ngati mukufuna kupanga mawerengedwe ovuta kwambiri, njirayi ndi yoyenera. Kuti mukonze, gwiritsani ntchito chizindikiro "$". Chitsanzo: $A$1.
3. maulalo osakanikirana. Kulankhula kwamtunduwu kumagwiritsidwa ntchito powerengera pakafunika kukonza ndime kapena mzere padera. Chitsanzo: $A1 kapena A$1.

Ngati kuli kofunikira kukopera zomwe zalembedwazo, maumboni okhala ndi ma adilesi osakanikirana komanso osakanikirana amagwiritsidwa ntchito. Nkhaniyi idzawulula ndi zitsanzo momwe mawerengedwe amapangidwira pogwiritsa ntchito mitundu yosiyanasiyana ya maulalo.

Relative cell reference mu Excel

Ili ndi gulu la zilembo zomwe zimatanthauzira malo a selo. Maulalo mu pulogalamuyi amalembedwa okha ndi maadiresi achibale. Mwachitsanzo: A1, A2, B1, B2. Kusunthira ku mzere wina kapena mzere wosiyana kumasintha zilembo mu fomula. Mwachitsanzo, poyambira malo A1. Kusuntha mopingasa kumasintha chilembo kukhala B1, C1, D1, ndi zina zotero. Momwemonso, kusintha kumachitika poyenda pamzere wolunjika, pokhapokha chiwerengerocho chimasintha - A2, A3, A4, ndi zina zotero. kuwerengera kwamtundu womwewo mu selo loyandikana nalo, kuwerengera kumachitidwa pogwiritsa ntchito chidziwitso chachibale. Kuti mugwiritse ntchito izi, tsatirani njira zingapo:

1. Deta ikangolowa muselo, sunthani cholozera ndikudina ndi mbewa. Kuunikira ndi rectangle wobiriwira kumasonyeza kutsegula kwa selo ndi kukonzekera ntchito ina.
2. Mwa kukanikiza kuphatikiza kiyi Ctrl + C, timakopera zomwe zili pa clipboard.
3. Timayatsa foni yomwe mukufuna kusamutsiramo deta kapena fomula yolembedwa m'mbuyomu.
4. Mwa kukanikiza kuphatikiza Ctrl + V timasamutsa zomwe zasungidwa mu clipboard system.

Malangizo a akatswiri! Kuti mugwiritse ntchito mawerengedwe amtundu womwewo patebulo, gwiritsani ntchito kuthyolako kwa moyo. Sankhani selo lomwe lili ndi fomula yomwe mwalowa kale. Kusunthika cholozera pamalopo ang'onoang'ono omwe amawoneka pansi kumanja kumanja, ndikugwirizira batani lakumanzere la mbewa, kokerani ku mzere wapansi kapena gawo lalikulu, kutengera zomwe mwachita. Potulutsa batani la mbewa, kuwerengera kudzachitika zokha. Chida ichi chimatchedwa auto fill marker.

Chitsanzo cha chiyanjano

Kuti mumveke bwino, talingalirani chitsanzo cha kuŵerengera pogwiritsa ntchito chilinganizo chogwirizana nacho. Tiyerekeze kuti mwiniwake wa sitolo ya masewera pambuyo pa chaka cha ntchito ayenera kuwerengera phindu la malonda.

Ndondomeko ya zochita:

1. Chitsanzo chikuwonetsa kuti mizati B ndi C idagwiritsidwa ntchito kudzaza kuchuluka kwa katundu wogulitsidwa ndi mtengo wake. Chifukwa chake, kuti mulembe fomu ndikupeza yankho, sankhani gawo D. Njirayi ikuwoneka motere: = B2*C

Tcherani khutu! Kuti muwongolere ntchito yolemba chilinganizo, gwiritsani ntchito chinyengo pang'ono. Ikani chizindikiro "=", dinani kuchuluka kwa katundu wogulitsidwa, ikani chizindikiro "*" ndikudina pamtengo wa chinthucho. Fomula pambuyo pa chizindikiro chofanana idzalembedwa yokha.

2. Kuti mupeze yankho lomaliza, dinani "Enter". Kenako, muyenera kuwerengera kuchuluka kwa phindu lomwe mwalandira kuchokera kumitundu ina yazinthu. Chabwino, ngati mizere si yayikulu, ndiye kuti zosintha zonse zitha kuchitidwa pamanja. Kuti mudzaze mizere yambiri nthawi imodzi mu Excel, pali ntchito imodzi yothandiza yomwe imapangitsa kuti zisamutse fomuyi ku maselo ena.
3. Sunthani cholozera pakona yakumanja kwa rectangle ndi fomula kapena zotsatira zomalizidwa. Maonekedwe a mtanda wakuda amakhala ngati chizindikiro chakuti cholozeracho chikhoza kukokera pansi. Chifukwa chake, kuwerengera kodziwikiratu kwa phindu lomwe lalandira pa chinthu chilichonse padera kumapangidwa.
4. Potulutsa batani la mbewa, timapeza zotsatira zolondola m'mizere yonse.

Mwa kuwonekera pa cell D3, mutha kuwona kuti zolumikizira za cell zasinthidwa zokha, ndipo zikuwoneka ngati izi: =B3*C3. Izi zikutanthauza kuti maulalo anali ofanana.

Zolakwika zotheka pogwira ntchito ndi maulalo achibale

Mosakayikira, ntchito iyi ya Excel imathandizira kwambiri kuwerengera, koma nthawi zina zovuta zimatha. Tiyeni tilingalire chitsanzo chosavuta chowerengera kuchuluka kwa phindu la chinthu chilichonse:

1. Pangani tebulo ndikulemba: A - dzina lazogulitsa; B - kuchuluka kwa malonda; C - mtengo; D ndi ndalama zomwe walandira. Tiyerekeze kuti pali zinthu 11 zokha mu assortment. Chifukwa chake, poganizira mafotokozedwe amizere, mizere 12 yadzazidwa ndipo phindu lonse ndi D.
2. Dinani pa selo E2 ndikulowa =D2/D13.
3. Pambuyo kukanikiza batani la "Lowani", coefficient ya gawo lachibale la malonda a chinthu choyamba chikuwonekera.
4. Tambasulani mzati pansi ndikudikirira zotsatira. Komabe, makinawa amapereka cholakwika "#DIV/0!"

Chifukwa cha cholakwikacho ndikugwiritsa ntchito chilankhulo chowerengera. Chifukwa cha kukopera chilinganizo, ma coordinates amasintha. Ndiye kuti, kwa E3, chilinganizocho chidzawoneka chonchi =D3/D13. Chifukwa selo la D13 silinadzazidwe ndipo mwachidziwitso lili ndi mtengo wa zero, pulogalamuyi idzapereka zolakwika ndi chidziwitso chakuti kugawanitsa ndi zero sikungatheke.

Zofunika! Kuti mukonze zolakwikazo, muyenera kulemba chilinganizocho kuti makonzedwe a D13 akhazikitsidwe. Mayankhulidwe achibale alibe magwiridwe antchito. Kuti muchite izi, pali mtundu wina wa maulalo - mtheradi. 

Momwe mungapangire ulalo wathunthu mu Excel

Chifukwa chogwiritsa ntchito chizindikiro cha $, zidakhala zotheka kukonza ma cell. Momwe izi zimagwirira ntchito, tikambirana zambiri. Popeza pulogalamuyi imagwiritsa ntchito maadiresi achibale mwachisawawa, motero, kuti ikhale yokhazikika, muyenera kuchita zingapo. Tiyeni tiwunikenso yankho la cholakwikacho "Momwe tingapezere coefficient pakugulitsa zinthu zingapo", ndikuwerengera pogwiritsa ntchito maadiresi onse:

1. Dinani pa E2 ndikulowetsa zolumikizira za ulalo =D2/D13. Popeza ulalo ndi wachibale, chizindikiro chiyenera kukhazikitsidwa kuti chikonze deta.
2. Konzani zolumikizana za selo D Kuti muchite izi, tsogolereni chilembo chomwe chikuwonetsa gawo ndi nambala ya mzere ndikuyika chizindikiro "$".

Malangizo a akatswiri! Kuti muwongolere ntchito yolowera, ndikwanira kuyambitsa cell kuti isinthe mawonekedwe ndikusindikiza batani la F4 kangapo. Mpaka mutapeza zikhalidwe zokhutiritsa. Njira yolondola ndi iyi: =D2/$D$13.

3. Dinani batani "Enter". Chifukwa cha zomwe zachitika, zotsatira zolondola ziyenera kuwoneka.
4. Kokani chikhomo mpaka pansi kuti mukopere fomula.

Chifukwa chogwiritsa ntchito maadiresi mtheradi powerengera, zotsatira zomaliza m'mizere yotsalayo zidzakhala zolondola.

Momwe mungayikitsire ulalo wosakanikirana mu Excel

Powerengera ma formula, sikuti maumboni achibale komanso mtheradi amagwiritsidwa ntchito, komanso osakanikirana. Chosiyanitsa chawo ndikuti amakonza chimodzi mwazogwirizanitsa.

• Mwachitsanzo, kuti musinthe malo a mzere, muyenera kulemba chizindikiro cha $ kutsogolo kwa kalatayo.
• M'malo mwake, ngati chizindikiro cha dola chalembedwa pambuyo pa kutchulidwa kwa kalata, ndiye kuti zizindikiro zomwe zili pamzerewo sizidzasintha.

Kuchokera pa izi zikutsatira kuti pofuna kuthetsa vuto lapitalo pozindikira coefficient ya malonda a katundu pogwiritsa ntchito maadiresi osakanikirana, m'pofunika kukonza nambala ya mzere. Ndiko kuti, chizindikiro cha $ chimayikidwa pambuyo pa chilembo, chifukwa makonzedwe ake sasintha ngakhale muzolemba zachibale. Tiyeni titenge chitsanzo:

1. Kuti muwerenge molondola, lowetsani =D1/$D$3 ndipo dinani "Enter". Pulogalamuyi imapereka yankho lenileni.
2. Kuti musunthire fomula kumaselo otsatira mgawo ndikupeza zotsatira zolondola, kokerani chogwiriracho ku selo yapansi.
3. Zotsatira zake, pulogalamuyi idzapereka mawerengedwe olondola.

Chenjerani! Ngati muyika chizindikiro cha $ kutsogolo kwa chilembocho, ndiye kuti Excel ipereka cholakwika "#DIV/0!", zomwe zikutanthauza kuti ntchitoyi siyingachitike.

"SuperAbsolute" kulankhula

Pamapeto pake, tiyeni tiwone chitsanzo china cha ulalo wamtheradi - "SuperAbsolute" yolankhula. Kodi mawonekedwe ake ndi kusiyana kwake. Tengani pafupifupi nambala 30 ndikuyiyika mu cell B2. Ndi nambala iyi yomwe idzakhala yayikulu, ndikofunikira kuchita nawo zinthu zingapo, mwachitsanzo, kukweza mphamvu.

1. Kuti zonse zichitike molondola, lowetsani fomula ili mgawo C: =$B$2^$D2. Mu gawo D timalowetsa mtengo wa madigiri.

2. Pambuyo kukanikiza batani la "Lowani" ndikuyambitsa chilinganizo, timatambasula cholembera pansi pamzati.
3. Timapeza zotsatira zolondola.

Chofunikira ndichakuti zonse zomwe zidachitika zimatengera selo limodzi lokhazikika la B2, chifukwa chake:

• Kukopera fomula kuchokera ku selo C3 kupita ku selo E3, F3, kapena H3 sikungasinthe zotsatira. Idzakhalabe yosasinthika - 900.
• Ngati mukufuna kuyika gawo latsopano, zolumikizira za selo ndi fomula zidzasintha, koma zotsatira zake sizisintha.

Izi ndizopadera za ulalo wa "SuperAbsolute": ngati mukufuna kusuntha zotsatira sizisintha. Komabe, pali nthawi zina pomwe deta imayikidwa kuchokera kuzinthu zina. Choncho, mizati imasinthidwa kumbali, ndipo deta imayikidwa mu njira yakale mu gawo la B2. Kodi chimachitika ndi chiyani pamenepa? Mukasakanizidwa, mawonekedwewo amasintha malinga ndi zomwe achita, ndiye kuti, sizimaloza B2, koma C2. Koma popeza kuyikako kudapangidwa mu B2, zotsatira zomaliza zidzakhala zolakwika.

Buku! Kuti muthe kuyika ma macros kuchokera kumagwero a chipani chachitatu, muyenera kuyatsa zoikamo za otukula (zimayimitsidwa mwachisawawa). Kuti muchite izi, pitani ku Zosankha, tsegulani zoikamo za riboni ndikuyang'ana bokosi lomwe lili kumanja kwa "Developer". Pambuyo pake, kupeza ntchito zambiri zomwe poyamba zinali zobisika pamaso pa wogwiritsa ntchito wamba zidzatsegulidwa.

Izi zikufunsa funso: kodi ndizotheka kusintha chilinganizo kuchokera ku selo C2 kuti nambala yoyambirira isonkhanitsidwe kuchokera ku selo B, ngakhale kuyika mizati yatsopano ya deta? Kuwonetsetsa kuti zosintha patebulo sizikhudza kutsimikiza kwachiwerengerocho pakuyika zidziwitso kuchokera kwa anthu ena, muyenera kuchita izi:

1. M'malo mwa ma coordinates a cell B2, lowetsani zizindikiro zotsatirazi: = INDIRECT(“B2”). Zotsatira zake, mutatha kusuntha zomwe zikupangidwira zidzawoneka motere: =INDIRECT(“B2”)^$E2.
2. Chifukwa cha ntchitoyi, ulalowo nthawi zonse umaloza pabwalo lomwe lili ndi ma coordinates B2, mosasamala kanthu kuti mizati ikuwonjezedwa kapena kuchotsedwa patebulo.

Ziyenera kumveka kuti selo lomwe lilibe deta nthawi zonse limasonyeza mtengo "0".

Kutsiliza

Chifukwa chogwiritsa ntchito mitundu itatu ya maulalo omwe afotokozedwa, mwayi wambiri umawoneka womwe umapangitsa kuti zikhale zosavuta kuwerengera mu Excel. Choncho, musanayambe kugwira ntchito ndi mafomu, choyamba werengani maulalo ndi malamulo a kukhazikitsa kwawo.