Mga link sa Excel – ganap, kamag-anak at halo-halong. Mga error kapag nagtatrabaho sa mga kamag-anak na link sa Excel

Upang mapadali ang mga kalkulasyon gamit ang mga formula sa awtomatikong mode, ginagamit ang mga sanggunian sa mga cell. Depende sa uri ng pagsulat, nahahati sila sa tatlong pangunahing uri:

1. Mga kamag-anak na link. Ginagamit para sa mga simpleng kalkulasyon. Ang pagkopya ng isang formula ay nangangailangan ng pagbabago ng mga coordinate.
2. Mga ganap na link. Kung kailangan mong gumawa ng mas kumplikadong mga kalkulasyon, ang pagpipiliang ito ay angkop. Para sa pag-aayos, gamitin ang simbolo na "$". Halimbawa: $A$1.
3. magkahalong link. Ang ganitong uri ng addressing ay ginagamit sa mga kalkulasyon kapag kinakailangan upang ayusin ang isang column o linya nang hiwalay. Halimbawa: $A1 o A$1.

Kung kinakailangan upang kopyahin ang data ng inilagay na formula, ang mga sanggunian na may ganap at halo-halong addressing ay ginagamit. Ipapakita ng artikulo ang mga halimbawa kung paano ginagawa ang mga kalkulasyon gamit ang iba't ibang uri ng mga link.

Relatibong cell reference sa Excel

Ito ay isang hanay ng mga character na tumutukoy sa lokasyon ng isang cell. Awtomatikong isinulat ang mga link sa programa na may kamag-anak na pagtugon. Halimbawa: A1, A2, B1, B2. Ang paglipat sa ibang row o column ay nagbabago sa mga character sa formula. Halimbawa, panimulang posisyon A1. Ang paglipat ng pahalang ay nagbabago ng titik sa B1, C1, D1, atbp. Sa parehong paraan, ang mga pagbabago ay nangyayari kapag gumagalaw sa isang patayong linya, sa kasong ito lamang ang numero ay nagbabago - A2, A3, A4, atbp. Kung kinakailangan na i-duplicate isang pagkalkula ng parehong uri sa isang katabing cell, isang pagkalkula ay isinasagawa gamit ang isang kamag-anak na sanggunian. Para magamit ang feature na ito, sundin ang ilang hakbang:

1. Sa sandaling maipasok ang data sa cell, ilipat ang cursor at mag-click gamit ang mouse. Ang pag-highlight na may berdeng parihaba ay nagpapahiwatig ng pag-activate ng cell at ang kahandaan para sa karagdagang trabaho.
2. Sa pamamagitan ng pagpindot sa key na kumbinasyon Ctrl + C, kinokopya namin ang mga nilalaman sa clipboard.
3. Ina-activate namin ang cell kung saan mo gustong maglipat ng data o isang dating nakasulat na formula.
4. Sa pamamagitan ng pagpindot sa kumbinasyong Ctrl + V inililipat namin ang data na naka-save sa clipboard ng system.

Payo ng eksperto! Upang maisagawa ang parehong uri ng mga kalkulasyon sa talahanayan, gumamit ng life hack. Piliin ang cell na naglalaman ng naunang inilagay na formula. Ang pag-hover ng cursor sa isang maliit na parisukat na lumalabas sa kanang sulok sa ibaba, at pagpindot sa kaliwang pindutan ng mouse, i-drag sa ibabang hilera o matinding column, depende sa ginawang pagkilos. Sa pamamagitan ng pagpapakawala ng pindutan ng mouse, awtomatikong isasagawa ang pagkalkula. Ang tool na ito ay tinatawag na auto fill marker.

Halimbawa ng kamag-anak na link

Upang gawing mas malinaw, isaalang-alang ang isang halimbawa ng isang pagkalkula gamit ang isang formula na may kamag-anak na sanggunian. Ipagpalagay na ang may-ari ng isang sports store pagkatapos ng isang taon ng trabaho ay kailangang kalkulahin ang kita mula sa mga benta.

Ang pagkakasunud-sunod ng mga aksyon:

1. Ang halimbawa ay nagpapakita na ang mga hanay B at C ay ginamit upang punan ang dami ng mga kalakal na naibenta at ang presyo nito. Alinsunod dito, para isulat ang formula at makuha ang sagot, piliin ang column D. Ganito ang hitsura ng formula: = B2 *C

Magbayad ng pansin! Upang mapadali ang proseso ng pagsulat ng isang formula, gumamit ng isang maliit na trick. Ilagay ang “=” sign, i-click ang dami ng naibenta, itakda ang “*” sign at i-click ang presyo ng produkto. Ang formula pagkatapos ng equal sign ay awtomatikong isusulat.

2. Para sa huling sagot, pindutin ang "Enter". Susunod, kailangan mong kalkulahin ang kabuuang halaga ng kita na natanggap mula sa iba pang mga uri ng mga produkto. Buweno, kung ang bilang ng mga linya ay hindi malaki, kung gayon ang lahat ng mga manipulasyon ay maaaring maisagawa nang manu-mano. Upang punan ang isang malaking bilang ng mga hilera sa parehong oras sa Excel, mayroong isang kapaki-pakinabang na function na ginagawang posible na ilipat ang formula sa iba pang mga cell.
3. Ilipat ang cursor sa ibabang kanang sulok ng parihaba na may formula o ang natapos na resulta. Ang hitsura ng isang itim na krus ay nagsisilbing isang senyas na ang cursor ay maaaring i-drag pababa. Kaya, ang isang awtomatikong pagkalkula ng kita na natanggap para sa bawat produkto nang hiwalay ay ginawa.
4. Sa pamamagitan ng pagpapakawala sa pinindot na pindutan ng mouse, nakukuha namin ang mga tamang resulta sa lahat ng linya.

Sa pamamagitan ng pag-click sa cell D3, makikita mo na ang mga cell coordinate ay awtomatikong nabago, at ngayon ay ganito ang hitsura: =B3 *C3. Ito ay sumusunod na ang mga link ay kamag-anak.

Mga posibleng error kapag nagtatrabaho sa mga kamag-anak na link

Walang alinlangan, ang Excel function na ito ay lubos na pinapasimple ang mga kalkulasyon, ngunit sa ilang mga kaso ay maaaring magkaroon ng mga paghihirap. Isaalang-alang natin ang isang simpleng halimbawa ng pagkalkula ng koepisyent ng tubo ng bawat item ng mga kalakal:

1. Gumawa ng talahanayan at punan ang: A – pangalan ng produkto; B – dami ng naibenta; C – gastos; D ang halagang natanggap. Ipagpalagay na mayroon lamang 11 na mga item sa assortment. Samakatuwid, isinasaalang-alang ang paglalarawan ng mga haligi, 12 linya ang napuno at ang kabuuang halaga ng kita ay D
2. Mag-click sa cell E2 at ipasok =D2 /D13.
3. Matapos pindutin ang pindutan ng "Enter", ang koepisyent ng kamag-anak na bahagi ng mga benta ng unang item ay lilitaw.
4. I-stretch ang column pababa at hintayin ang resulta. Gayunpaman, ang system ay nagbibigay ng error na "#DIV/0!"

Ang dahilan ng error ay ang paggamit ng isang kamag-anak na sanggunian para sa mga kalkulasyon. Bilang resulta ng pagkopya ng formula, nagbabago ang mga coordinate. Iyon ay, para sa E3, ang formula ay magiging ganito =D3 /D13. Dahil ang cell D13 ay hindi napuno at theoretically ay may zero na halaga, ang programa ay magbibigay ng isang error sa impormasyon na ang paghahati sa pamamagitan ng zero ay imposible.

Mahalaga! Upang iwasto ang error, kinakailangang isulat ang formula sa paraang naayos ang mga coordinate ng D13. Ang relatibong pag-address ay walang ganoong functionality. Upang gawin ito, mayroong isa pang uri ng mga link - ganap. 

Paano ka gumawa ng ganap na link sa Excel

Salamat sa paggamit ng simbolo ng $, naging posible na ayusin ang mga coordinate ng cell. Kung paano ito gumagana, isasaalang-alang pa natin. Dahil ang programa ay gumagamit ng kamag-anak na pagtugon bilang default, nang naaayon, upang gawin itong ganap, kakailanganin mong magsagawa ng ilang mga aksyon. Suriin natin ang solusyon sa error na "Paano mahahanap ang koepisyent mula sa pagbebenta ng ilang mga item ng mga kalakal", na isinasagawa ang pagkalkula gamit ang ganap na pagtugon:

1. Mag-click sa E2 at ipasok ang mga coordinate ng link =D2 /D13. Dahil kamag-anak ang link, kailangang magtakda ng simbolo para ayusin ang data.
2. Ayusin ang mga coordinate ng cell D Upang maisagawa ang pagkilos na ito, unahan ang titik na nagpapahiwatig ng column at ang row number sa pamamagitan ng paglalagay ng “$” sign.

Payo ng eksperto! Upang mapadali ang gawain ng pagpasok, sapat na upang maisaaktibo ang cell para sa pag-edit ng formula at pindutin ang F4 key nang maraming beses. Hanggang sa makakuha ka ng mga kasiya-siyang halaga. Ang tamang formula ay ang mga sumusunod: =D2/$D$13.

3. Pindutin ang pindutan ng "Enter". Bilang resulta ng mga ginawang aksyon, dapat na lumitaw ang tamang resulta.
4. I-drag ang marker sa ilalim na linya upang kopyahin ang formula.

Dahil sa paggamit ng absolute addressing sa mga kalkulasyon, magiging tama ang mga huling resulta sa natitirang mga row.

Paano maglagay ng mixed link sa Excel

Para sa mga kalkulasyon ng formula, hindi lamang mga kamag-anak at ganap na sanggunian ang ginagamit, kundi pati na rin ang mga halo-halong. Ang kanilang natatanging tampok ay ang pag-aayos ng isa sa mga coordinate.

• Halimbawa, upang baguhin ang posisyon ng isang linya, dapat mong isulat ang $ sign sa harap ng pagtatalaga ng titik.
• Sa kabaligtaran, kung ang dollar sign ay isinulat pagkatapos ng pagtatalaga ng titik, kung gayon ang mga tagapagpahiwatig sa linya ay mananatiling hindi nagbabago.

Mula dito ay sumusunod na upang malutas ang nakaraang problema sa pagtukoy ng koepisyent ng mga benta ng mga kalakal gamit ang halo-halong addressing, kinakailangan upang ayusin ang numero ng linya. Iyon ay, ang $ sign ay inilalagay pagkatapos ng sulat ng haligi, dahil ang mga coordinate nito ay hindi nagbabago kahit na sa isang kamag-anak na sanggunian. Kumuha tayo ng isang halimbawa:

1. Para sa tumpak na mga kalkulasyon, ipasok =D1/$D$3 at pindutin ang "Enter". Ang programa ay nagbibigay ng eksaktong sagot.
2. Upang ilipat ang formula sa kasunod na mga cell pababa sa column at makakuha ng mga tumpak na resulta, i-drag ang handle sa ibabang cell.
3. Bilang resulta, ang programa ay magbibigay ng tamang mga kalkulasyon.

Atensyon! Kung itinakda mo ang $ sign sa harap ng liham, magbibigay ang Excel ng error na "#DIV/0!", na nangangahulugang hindi maisagawa ang operasyong ito.

"SuperAbsolute" addressing

Sa huli, tingnan natin ang isa pang halimbawa ng isang ganap na link – "SuperAbsolute" na pagtugon. Ano ang mga katangian at pagkakaiba nito. Kunin ang tinatayang numero 30 at ilagay ito sa cell B2. Ang numerong ito ang magiging pangunahing isa, kinakailangan na magsagawa ng isang serye ng mga aksyon kasama nito, halimbawa, upang itaas ito sa isang kapangyarihan.

1. Para sa tamang pagpapatupad ng lahat ng mga aksyon, ilagay ang sumusunod na formula sa column C: =$B$2^$D2. Sa hanay D namin ipasok ang halaga ng mga degree.

2. Matapos pindutin ang pindutan ng "Enter" at i-activate ang formula, iniuunat namin ang marker pababa sa hanay.
3. Nakukuha namin ang tamang resulta.

Ang ilalim na linya ay ang lahat ng mga aksyon na isinagawa ay tumutukoy sa isang nakapirming cell B2, samakatuwid:

• Ang pagkopya ng formula mula sa cell C3 patungo sa cell E3, F3, o H3 ay hindi magbabago sa resulta. Ito ay mananatiling hindi nagbabago - 900.
• Kung kailangan mong magpasok ng isang bagong column, ang mga coordinate ng cell na may formula ay magbabago, ngunit ang resulta ay mananatiling hindi nagbabago.

Ito ang kakaiba ng link na "SuperAbsolute": kung kailangan mong ilipat ang resulta ay hindi magbabago. Gayunpaman, may mga sitwasyon kung kailan ipinasok ang data mula sa mga mapagkukunan ng third-party. Kaya, ang mga haligi ay inilipat sa gilid, at ang data ay nakatakda sa lumang paraan sa hanay B2. Ano ang mangyayari sa kasong ito? Kapag pinaghalo, nagbabago ang formula ayon sa aksyon na ginawa, iyon ay, hindi na ito ituturo sa B2, ngunit sa C2. Ngunit dahil ang pagpasok ay ginawa sa B2, ang huling resulta ay magiging mali.

Sanggunian! Upang makapagpasok ng mga macro mula sa mga mapagkukunan ng third-party, kailangan mong paganahin ang mga setting ng developer (naka-disable ang mga ito bilang default). Upang gawin ito, pumunta sa Mga Pagpipilian, buksan ang mga setting ng ribbon at lagyan ng tsek ang kahon sa kanang hanay sa tapat ng "Developer". Pagkatapos nito, magbubukas ang pag-access sa maraming mga pag-andar na dati ay nakatago mula sa mga mata ng karaniwang gumagamit.

Nagtatanong ito: posible bang baguhin ang formula mula sa cell C2 upang makolekta ang orihinal na numero mula sa cell B, sa kabila ng pagpasok ng mga bagong column ng data? Upang matiyak na ang mga pagbabago sa talahanayan ay hindi makakaapekto sa pagtukoy ng kabuuan kapag nag-i-install ng data mula sa mga mapagkukunan ng third-party, dapat mong gawin ang mga sumusunod na hakbang:

1. Sa halip na mga coordinate ng cell B2, ilagay ang mga sumusunod na indicator: =INDIRECT("B2"). Bilang isang resulta, pagkatapos ilipat ang formulating komposisyon ay magiging ganito: =INDIRECT(“B2”)^$E2.
2. Salamat sa function na ito, ang link ay palaging tumuturo sa parisukat na may mga coordinate B2, hindi alintana kung ang mga column ay idinagdag o inalis sa talahanayan.

Dapat itong maunawaan na ang isang cell na walang anumang data ay palaging nagpapakita ng halaga na "0".

Konklusyon

Salamat sa paggamit ng tatlong uri ng inilarawan na mga link, maraming pagkakataon ang lumilitaw na nagpapadali sa paggawa ng mga kalkulasyon sa Excel. Samakatuwid, bago ka magsimulang magtrabaho sa mga formula, basahin muna ang mga link at ang mga patakaran para sa kanilang pag-install.