Zvisungo muExcel - mhedziso, hama uye yakasanganiswa. Zvikanganiso kana uchishanda nehukama zvinongedzo muExcel

Kugonesa kuverenga uchishandisa mafomula mune otomatiki modhi, mareferensi kumaseru anoshandiswa. Zvichienderana nerudzi rwekunyora, dzakakamurwa kuva mhando nhatu huru:

1. Relative links. Inoshandiswa pakuverenga zviri nyore. Kukopa fomula kunosanganisira kushandura makongiresi.
2. Absolute links. Kana iwe uchida kuburitsa mamwe macalculation akaoma, iyi sarudzo yakakodzera. Kugadzirisa shandisa chiratidzo "$". Muenzaniso: $A$1.
3. misanganiswa links. Rudzi urwu rwekutaura runoshandiswa mukuverenga kana zvichidikanwa kugadzirisa mbiru kana mutsetse zvakasiyana. Muenzaniso: $A1 kana A$1.

Kana zvichidikanwa kukopa iyo data yefomula yakapinda, mareferensi ane mhedziso uye yakasanganiswa kero anoshandiswa. Chinyorwa chinoburitsa nemienzaniso kuti maverengero anoitwa sei uchishandisa marudzi akasiyana ezvekubatanidza.

Relative cell reference muExcel

Iyi imhando yemavara anotsanangura nzvimbo yesero. Manongedzo ari muchirongwa anonyorwa otomatiki ane hama yekutaura. Somuenzaniso: A1, A2, B1, B2. Kuenda kune imwe mutsara kana koramu inoshandura mavara ari mufomula. Semuenzaniso, kutanga chinzvimbo A1. Kufamba kwakatwasuka kunochinja tsamba kuenda kuB1, C1, D1, zvichingodaro. Nenzira imwecheteyo, shanduko dzinoitika kana uchifamba nemutsara wakatwasuka, panguva iyi nhamba inoshanduka - A2, A3, A4, zvichingodaro. Kana zvichidikanwa kutevedzera. kuverenga kwemhando imwechete musero riri pedyo, kuverenga kunoitwa pachishandiswa chirevo chehama. Kuti ushandise chimiro ichi, tevera matanho mashoma:

1. Kana iyo data yangopinzwa muchitokisi, fambisa cursor uye tinya negonzo. Kuratidzira nerectangle yegirini kunoratidza kushandiswa kwesero uye kugadzirira kune rimwe basa.
2. Nekudzvanya kiyi musanganiswa Ctrl + C, isu tinokopa zviri mukati ku clipboard.
3. Isu tinomisikidza sero yaunoda kuendesa data kana fomula yakambonyorwa.
4. Nekudzvanya musanganiswa Ctrl + V isu tinotamisa iyo data yakachengetwa musystem clipboard.

Mazano enyanzvi! Kuti uite mhando imwechete yekuverenga mutafura, shandisa hupenyu hack. Sarudza sero rine fomula yakambopinda. Kutenderedza cursor pamusoro diki sikweya inooneka mukona yekurudyi, uye wakabata bhatani rekuruboshwe, dhonza kumutsara wepasi kana koramu yakanyanyisa, zvichienderana nechiito chaitwa. Nekuburitsa bhatani rembeva, kuverenga kuchaitwa otomatiki. Ichi chishandiso chinodaidzwa kuti auto fill marker.

Relative link muenzaniso

Kuti zvijeke, funga nezvemuenzaniso wekuverenga uchishandisa fomula ine chirevo chehukama. Ngatitii muridzi wechitoro chemitambo mushure megore rekushanda anoda kuverenga purofiti kubva mukutengesa.

Kurongeka kwezviito:

1. Muenzaniso unoratidza kuti makoramu B neC akashandiswa kuzadza huwandu hwezvinhu zvakatengeswa nemutengo wazvo. Saizvozvo, kunyora formula uye kuwana mhinduro, sarudza chikamu D. Iyo fomula inoita seizvi: = B2*C

Teerera! Kuti ufambise maitiro ekunyora fomula, shandisa hunyengeri hudiki. Isa iyo "=" chiratidzo, tinya pahuwandu hwezvinhu zvinotengeswa, isa iyo "*" chiratidzo uye tinya pamutengo wechigadzirwa. Iyo formula mushure mechiratidzo chakaenzana ichanyorwa otomatiki.

2. Kuti uwane mhinduro yekupedzisira, tinya "Enter". Tevere, iwe unofanirwa kuverenga iyo yakazara huwandu hwepurofiti yakagamuchirwa kubva kune mamwe marudzi ezvigadzirwa. Zvakanaka, kana nhamba yemitsara isiri yakakura, saka manipulations ese anogona kuitwa nemaoko. Kuzadza nhamba huru yemitsara panguva imwe chete muExcel, kune rimwe basa rinobatsira rinoita kuti zvikwanise kuendesa fomu kune mamwe masero.
3. Fambisa cursor pakona yekurudyi yerectangle neformula kana mhedzisiro yapera. Kuonekwa kwemuchinjikwa wakasviba kunoshanda sechiratidzo chokuti cursor inogona kudhonzwa pasi. Saka, kuverengera otomatiki kwepurofiti yakagamuchirwa kune chimwe nechimwe chigadzirwa zvakasiyana inoitwa.
4. Nekuburitsa bhatani rembeva rakadzvanywa, tinowana mibairo chaiyo mumitsara yese.

Nekudzvanya pane sero D3, unogona kuona kuti maseru anorongedzera akashandurwa otomatiki, uye zvino tarisa seizvi: =B3*C3. Zvinotevera kuti ma link acho aive akabatana.

Zvikanganiso zvinogoneka paunenge uchishanda nehukama hwehukama

Pasina kupokana, iri Excel basa rinorerutsa zvakanyanya kuverenga, asi mune dzimwe nguva matambudziko anogona kumuka. Ngatitarisei muenzaniso wakapfava wekuverenga purofiti coefficient yechinhu chimwe nechimwe chezvinhu:

1. Gadzira tafura uye uzadze: A - zita rechigadzirwa; B - huwandu hwakatengeswa; C - mutengo; D ndiyo mari yakagamuchirwa. Ngatitii pane zvinhu gumi nerimwe chete muassortment. Naizvozvo, tichifunga nezve tsananguro yemakoramu, mitsara gumi nembiri inozadzwa uye iyo yakazara purofiti iD.
2. Dzvanya pane cell E2 uye pinda =D2/D13.
3. Mushure mekudzvanya bhatani re "Enter", iyo coefficient yechikamu chekutengesa chechinhu chekutanga chinooneka.
4. Tambanudza mbiru pasi uye kumirira mhedzisiro. Nekudaro, sisitimu inopa kukanganisa "#DIV/0!"

Chikonzero chekukanganisa ndiko kushandiswa kwechirevo chehukama pakuverenga. Nekuda kwekukopa fomula, macoordinates anoshanduka. Ndiko kuti, yeE3, iyo formula ichaita seizvi =D3/D13. Nekuti sero D13 haina kuzadzwa uye ne theoretically ine zero kukosha, chirongwa chinopa chikanganiso neruzivo rwekuti kupatsanura ne zero hazvigoneke.

Zvinokosha! Kuti ugadzirise kukanganisa, zvakakosha kunyora fomu nenzira yekuti D13 inoronga inogadziriswa. Relative addressing haina basa rakadaro. Kuti uite izvi, pane imwe mhando yezvibatanidza - zvachose. 

Iwe unogadzira sei chinongedzo chakazara muExcel

Nekuda kwekushandiswa kweiyo $ chiratidzo, zvakave zvinogoneka kugadzirisa maseru coordinates. Kuti izvi zvinoshanda sei, tichaongorora zvimwe. Sezvo chirongwa chinoshandisa hama kero nekusarudzika, zvinoenderana, kuti ive yakakwana, iwe uchafanirwa kuita akati wandei zviito. Ngationgororei mhinduro yekukanganisa "Maitiro ekuwana iyo coefficient kubva pakutengeswa kwezvinhu zvakati wandei", tichiita kuverenga tichishandisa absolute addressing:

1. Dzvanya paE2 uye isa macoordha eiyo link =D2/D13. Sezvo chinongedzo chiri hama, chiratidzo chinofanira kuiswa kuti chigadzirise data.
2. Gadzirisa zvinorongeka zvesero D Kuti uite chiito ichi, tungamira vara rinotaridza koromo uye nhamba yemutsara nekuisa “$” chiratidzo.

Mazano enyanzvi! Kuti ufambise basa rekupinda, zvinokwana kuita kuti sero rigadzirise fomu uye tinya F4 kiyi kakawanda. Kusvikira wawana hunhu hunogutsa. Iyo chaiyo formula ndeyotevera: =D2/$D$13.

3. Dzvanya bhatani rekuti "Enter". Somugumisiro wezviito zvakaitwa, chigumisiro chakanaka chinofanira kuonekwa.
4. Dhonza chicherechedzo kuzasi kuti ukope fomula.

Nekuda kwekushandiswa kweabsolute addressing mukuverenga, mhedzisiro yekupedzisira mumitsara yasara ichave yakarurama.

Maitiro ekuisa yakasanganiswa link muExcel

Pakuverengera formula, kwete chete mareferensi ehukama uye akazara anoshandiswa, asiwo akasanganiswa. Chimiro chavo chakasiyana ndechekuti ivo vanogadzirisa imwe yemakongiresi.

• Semuenzaniso, kuti uchinje chinzvimbo chemutsara, unofanirwa kunyora chiratidzo che$ pamberi pezita retsamba.
• Pane zvinopesana, kana chiratidzo chedhora chakanyorwa mushure mekutsvaga tsamba, ipapo zviratidzo mumutsara zvicharamba zvisina kuchinjwa.

Kubva pane izvi zvinotevera kuti kuitira kugadzirisa dambudziko rekare nekugadzirisa coefficient yekutengeswa kwezvinhu uchishandisa kusanganiswa kwekutaura, zvakakosha kugadzirisa nhamba yemutsara. Ndiko kuti, chiratidzo chemadhora chinoiswa mushure metsamba yekoramu, nekuti marongero ayo haachinje kunyangwe mune imwe chirevo. Ngatitorei muenzaniso:

1. Kuti uwane maverengero chaiwo, isa =D1/$D$3 uye tinya "Enter". Chirongwa chinopa mhinduro chaiyo.
2. Kufambisa fomula kumaseru anotevera pasi pasi pekoramu uye kuwana mhinduro chaidzo, dhonza mubato kuzasi sero.
3. Nekuda kweizvozvo, chirongwa chinopa maverengero chaiwo.

Chenjerera! Kana iwe ukaisa chiratidzo che $ pamberi petsamba, ipapo Excel ichapa kukanganisa "#DIV/0!", zvinoreva kuti oparesheni iyi haigone kuitwa.

"SuperAbsolute" kutaura

Mukupedzisira, ngatitarisei mumwe muenzaniso weabsolute link – “SuperAbsolute” addressing. Ndeapi maitiro ayo uye akasiyana. Tora inenge nhamba 30 woiisa muchitokisi B2. Ndiyo nhamba iyi ichava iyo huru, inofanirwa kuita mutsara wezviito nayo, somuenzaniso, kuisimudza kune simba.

1. Kuti uite kwakaringana zviito zvese, isa iyo inotevera fomula mumutsara C: =$B$2^$D2. Muchikamu cheD tinopinda kukosha kwemadhigirii.

2. Mushure mekudzvanya bhatani rekuti "Enter" uye nekumisikidza fomula, tinotambanudza mucherechedzo pasi pembiru.
3. Tinowana mhedzisiro chaiyo.

Chinokosha ndechekuti zviito zvese zvakaitwa zvinonongedza kune imwe yakagadziriswa sero B2, saka:

• Kukopa fomula kubva muchitokisi C3 kuenda kusero E3, F3, kana H3 hakuzochinji mhedzisiro. Icharamba isina kuchinjwa - 900.
• Kana iwe uchida kuisa mutsara mutsva, zvinorongeka zvesero zvine fomula zvinoshanduka, asi mhedzisiro icharamba isina kuchinjika.

Uku ndiko kusarudzika kwe "SuperAbsolute" link: kana iwe uchida kufambisa mhedzisiro haichinji. Nekudaro, pane mamiriro ezvinhu apo data inoiswa kubva kune wechitatu-bato masosi. Saka, makoramu anochinjirwa kudivi, uye iyo data yakaiswa munzira yekare mukoramu B2. Chii chinoitika munyaya iyi? Kana yakasanganiswa, chimiro chinoshanduka maererano nechiito chakaitwa, kureva kuti, haichazonongedzera kuB2, asi kuC2. Asi sezvo kuiswa kwakaitwa muB2, mhedzisiro yekupedzisira ichave isiriyo.

Reference! Kuti ukwanise kuisa macros kubva kune wechitatu-bato masosi, iwe unofanirwa kugonesa dhizaini marongero (akaremara nekusarudzika). Kuti uite izvi, enda kuSarudzo, vhura ribhoni marongero uye tarisa bhokisi riri mukoramu yekurudyi yakatarisana ne "Developer". Mushure meizvozvo, kupinda kune akawanda mabasa ayo aimbove akavanzwa kubva pameso eavhareji mushandisi achavhura.

Izvi zvinokumbira mubvunzo: zvinokwanisika here kushandura fomu kubva muchitokisi C2 kuitira kuti nhamba yepakutanga iunganidzwe kubva muchitokisi B, pasinei nekuiswa kwemakoramu matsva e data? Kuti uve nechokwadi chekuti shanduko mutafura haikanganisi kutsunga kweiyo yakazara kana uchiisa data kubva kune wechitatu-bato masosi, iwe unofanirwa kuita anotevera matanho:

1. Panzvimbo pezvirongwa zvesero B2, isa zvinotevera zviratidzo: =INDIRECT(“B2”). Nekuda kweizvozvo, mushure mekufambisa iyo yekugadzira iyo kuumbwa ichaita seizvi: =INDIRECT(“B2”)^$E2.
2. Nekuda kweichi basa, chinongedzo chinogara chichinongedza kuskweya ine marongedzero B2, zvisinei nekuti makoramu akawedzerwa here kana kubviswa mutafura.

Zvinofanira kunzwisiswa kuti sero risina chero data rinogara richiratidza kukosha "0".

mhedziso

Nekuda kwekushandiswa kwemhando nhatu dzeakatsanangurwa zvinongedzo, yakawanda mikana inooneka inoita kuti zvive nyore kushanda nekuverenga muExcel. Naizvozvo, usati watanga kushanda nemafomati, tanga waverenga zvinongedzo uye mitemo yekuiswa kwavo.