Ito ay pinaniniwalaan na ang mga cyclical reference sa Excel ay isang maling pagpapahayag. Sa katunayan, medyo madalas ito ang kaso, ngunit hindi pa rin palagi. Kung minsan ang mga ito ay sadyang inilalapat. Alamin kung ano ang mga cyclic link, kung paano lilikha ang mga ito, kung paano hanapin ang mga umiiral na sa isang dokumento, kung paano magtrabaho sa kanila, o kung paano tanggalin ang mga ito kung kinakailangan.
Paggamit ng mga pabilog na sanggunian
Una sa lahat, alamin kung ano ang bumubuo ng isang circular reference. Sa katunayan, ito ay isang expression na, sa pamamagitan ng formula sa iba pang mga cell, ay tumutukoy sa sarili nito. Maaari din itong isang link na matatagpuan sa sheet na elemento kung saan ito mismo ay tumutukoy.
Dapat tandaan na sa pamamagitan ng default, ang mga modernong bersyon ng Excel ay awtomatikong i-block ang proseso ng pagsasagawa ng cyclic operation. Ito ay dahil sa ang katunayan na ang mga expression na ay labis na mali, at looping ay gumagawa ng isang pare-pareho ang proseso ng muling pagkalkula at pagkalkula, na lumilikha ng isang karagdagang load sa system.
Paglikha ng isang paikot na sanggunian
Ngayon tingnan natin kung paano lumikha ng pinakamadaling looping expression. Ito ay isang link na matatagpuan sa parehong cell na kung saan ito ay tumutukoy.
- Piliin ang sheet item A1 at isulat ang sumusunod na pagpapahayag dito:
= A1Susunod, mag-click sa pindutan Ipasok sa keyboard.
- Pagkatapos nito, lilitaw ang dialog box na babala ng cyclic expression. Mag-click kami dito sa pindutan. "OK".
- Sa gayon, nakatanggap kami ng isang pagpapatakbo ng cyclic sa isang sheet kung saan ang cell ay tumutukoy sa sarili nito.
Iyan ay bahagyang kumplikado ang gawain at lumikha ng isang cyclic na expression mula sa ilang mga cell.
- Sumulat ng isang numero sa anumang elemento ng sheet. Hayaan itong maging isang cell A1at ang numero 5.
- Sa isa pang cell (B1) isulat ang pananalita:
= C1 - Sa susunod na item (C1) isulat ang sumusunod na formula:
= A1 - Pagkatapos nito bumalik kami sa cell. A1kung saan ang numero ay nakatakda 5. Tinutukoy namin ang kanyang elemento B1:
= B1Pinindot namin ang pindutan Ipasok.
- Kaya, ang loop ay sarado, at nakakuha tayo ng klasikong cyclic na link. Pagkatapos na sarado ang babalang window, nakikita namin na ang programa ay minarkahan ng isang paikot na koneksyon na may mga asul na arrow sa sheet, na tinatawag na mga bakanteng arrow.
Kami ngayon ay lumiliko sa paglikha ng isang cyclical expression sa halimbawa ng isang table. Mayroon kaming table ng mga benta ng pagkain. Ito ay binubuo ng apat na haligi na kung saan ang pangalan ng produkto, ang bilang ng mga produkto na nabili, ang presyo at ang halaga ng mga nalikom mula sa pagbebenta ng buong volume ay ipinahiwatig. Mayroon nang mga formula sa talahanayan sa huling haligi. Kinakalkula nila ang kita sa pamamagitan ng pagpaparami ng dami ayon sa presyo.
- Upang i-loop ang formula sa unang linya, piliin ang elemento ng sheet na may dami ng unang produkto (B2). Sa halip na isang static na halaga (6) pumasok kami doon ang formula na bibilangin ang dami ng mga kalakal sa pamamagitan ng paghati sa kabuuang halaga (D2) sa presyo (C2):
= D2 / C2Mag-click sa pindutan Ipasok.
- Nakuha namin ang unang cyclic na link, ang relasyon kung saan ay karaniwang ipinahiwatig ng isang nagpapalitan ng arrow. Ngunit tulad ng makikita mo, ang resulta ay mali at katumbas ng zero, dahil ito ay sinabi na bago, ang mga bloke ng Excel ang pagpapatupad ng mga pagpapatakbo ng cyclic.
- Kopyahin ang expression sa lahat ng iba pang mga cell ng haligi na may bilang ng mga produkto. Upang gawin ito, itakda ang cursor sa ibabang kanang sulok ng elemento na naglalaman na ng formula. Ang cursor ay na-convert sa isang krus, na tinatawag na fill marker. Pindutin nang matagal ang kaliwang pindutan ng mouse at i-drag ang cross na ito hanggang sa dulo ng talahanayan.
- Tulad ng makikita mo, ang expression ay kinopya sa lahat ng mga elemento ng haligi. Ngunit, isang relasyon lamang ang minarkahan ng isang arrow na bakas. Tandaan ito para sa hinaharap.
Maghanap ng mga pabilog na sanggunian
Tulad ng nakita natin sa itaas, hindi sa lahat ng kaso ang programa ay nagmamarka ng pakikipag-ugnayan ng isang pabilog na sanggunian sa mga bagay, kahit na ito ay nasa sheet. Dahil sa katunayan na ang labis na operasyon ng paikot ay nakakapinsala, dapat silang alisin. Ngunit para sa ito dapat sila unang natagpuan. Paano ito magagawa kung ang mga expression ay hindi minarkahan ng linya na may mga arrow? Harapin natin ang gawaing ito.
- Kaya, kung nagpapatakbo ka ng Excel file kapag binuksan mo ang isang window ng impormasyon na nagsasabi na naglalaman ito ng isang paikot na link, pagkatapos ay ipinapayong mahanap ito. Upang gawin ito, lumipat sa tab "Mga Formula". Mag-click sa laso sa tatsulok, na matatagpuan sa kanan ng button "Lagyan ng check para sa mga error"na matatagpuan sa isang bloke ng mga tool "Dependencies ng Formula". Magbubukas ang isang menu kung saan dapat mong ilipat ang cursor sa item "Mga cyclic link". Pagkatapos nito, bubukas sa susunod na menu ang isang listahan ng mga address ng mga elemento ng sheet kung saan nakita ng programa ang cyclic expression.
- Kapag nag-click ka sa isang tukoy na address, ang kaparehong cell sa sheet ay napili.
May isa pang paraan upang malaman kung saan matatagpuan ang pabilog na link. Ang mensahe tungkol sa problemang ito at ang address ng elemento na naglalaman ng katulad na expression ay matatagpuan sa kaliwang bahagi ng status bar, na matatagpuan sa ilalim ng Excel window. Gayunpaman, sa kaibahan sa nakaraang bersyon, ang mga address sa status bar ay magpapakita ng mga address na hindi lahat ng mga elemento na naglalaman ng mga reference na pabilog, kung maraming ng mga ito, ngunit isa lamang sa mga ito, na lumitaw sa harap ng iba.
Bilang karagdagan, kung ikaw ay nasa isang libro na naglalaman ng isang looping expression, hindi sa sheet kung saan ito matatagpuan, ngunit sa isa pa, pagkatapos sa kasong ito lamang ng isang mensahe tungkol sa pagkakaroon ng isang error na walang isang address ay ipapakita sa status bar.
Aralin: Paano makahanap ng pabilog na mga link sa Excel
Ayusin ang mga cyclic na link
Tulad ng nabanggit sa itaas, sa napakaraming kaso, ang mga pagpapatakbo ng paikot ay masama na dapat itapon. Samakatuwid, ito ay natural na pagkatapos na matuklasan ang paikot na koneksyon, kinakailangan upang itama ito upang maihatid ang formula sa isang normal na anyo.
Upang iwasto ang pagpapakabit ng cyclic, kinakailangan upang masubaybayan ang buong pagkakabit ng mga selula. Kahit na ang tseke ay nagpapahiwatig ng isang tiyak na cell, ang error ay maaaring hindi kasinungalingan, ngunit sa isa pang elemento ng dependency chain.
- Sa aming kaso, sa kabila ng katotohanang ang programa ay tama ang itinuturo sa isa sa mga selula ng ikot (D6), ang tunay na pagkakamali ay nasa isa pang cell. Piliin ang item D6upang malaman kung aling mga selula ang kinukuha nito. Tinitingnan namin ang expression sa formula bar. Tulad ng makikita mo, ang halaga sa elementong ito ng sheet ay nabuo sa pamamagitan ng pagpaparami ng mga nilalaman ng mga cell B6 at C6.
- Pumunta sa cell C6. Piliin ito at tingnan ang formula bar. Tulad ng makikita mo, ito ay isang regular na static na halaga (1000), na hindi isang produkto ng pormula. Samakatuwid, ligtas na sabihin na ang tinukoy na sangkap ay hindi naglalaman ng isang error na nagiging sanhi ng paglikha ng mga operasyon ng cyclic.
- Pumunta sa susunod na cell (B6). Matapos piliin ang formula sa linya, nakita namin na naglalaman ito ng kinakalkula na expression (= D6 / C6), na kinukuha ang data mula sa iba pang mga elemento ng talahanayan, sa partikular, mula sa isang cell D6. Kaya ang cell D6 ay tumutukoy sa data ng item B6 at kabaliktaran, na nagiging sanhi ng pagkahumaling.
Dito, kinalkula namin ang relasyon nang pantay-pantay nang mabilis, ngunit sa katotohanan may mga kaso kapag ang proseso ng pagkalkula ay nagsasangkot ng maraming mga selula, at hindi tatlong elemento, tulad ng sa atin. Pagkatapos ng paghahanap ay maaaring tumagal ng masyadong mahabang panahon, dahil kailangan mong pag-aralan ang bawat elemento ng cycle.
- Ngayon kailangan namin upang maunawaan nang eksakto kung aling mga cell (B6 o D6) ay naglalaman ng isang error. Bagaman, pormal, hindi ito isang pagkakamali, kundi isang labis na paggamit ng mga link, na humahantong sa looping. Sa panahon ng proseso ng pagpapasya kung aling mga cell na i-edit, kailangan mong mag-aplay ng lohika. Walang malinaw na algorithm para sa pagkilos. Sa bawat kaso, magkaiba ang lohika na ito.
Halimbawa, kung sa aming mesa ang kabuuang halaga ay kinakalkula sa pamamagitan ng pagpaparami ng dami ng mga kalakal na talagang ibinebenta ng presyo nito, maaari naming sabihin na ang link na nagkakalkula ng halaga mula sa kabuuang halaga ng pagbebenta ay malinaw na labis. Samakatuwid, tinatanggal namin ito at pinalitan ito ng isang static na halaga.
- Isinasagawa namin ang isang katulad na operasyon sa lahat ng iba pang mga cyclic expression, kung nasa sheet ito. Matapos alisin ang lahat ng mga paikot na link mula sa aklat, ang mensahe tungkol sa pagkakaroon ng problemang ito ay dapat na mawala mula sa status bar.
Bilang karagdagan, kung ang mga cyclical na expression ay ganap na naalis, maaari mong malaman kung gumagamit ng error checking tool. Pumunta sa tab "Mga Formula" at i-click ang pamilyar na tatsulok sa kanan ng pindutan "Lagyan ng check para sa mga error" sa isang pangkat ng mga tool "Dependencies ng Formula". Kung nasa simulang menu item "Mga cyclic link" ay hindi magiging aktibo, nangangahulugan ito na tinanggal namin ang lahat ng naturang mga bagay mula sa dokumento. Sa kabaligtaran, kinakailangan na magamit ang pamamaraan ng pagtanggal sa mga elemento na nasa listahan sa parehong paraan na itinuturing na dati.
Pahintulot upang magsagawa ng mga pagpapatakbo ng paikot
Sa nakaraang bahagi ng aralin, higit sa lahat namin inilarawan kung paano haharapin ang mga pabilog na sanggunian, o kung paano hanapin ang mga ito. Ngunit, mas maaga ang pag-uusap ay tungkol din sa katotohanan na sa ilang mga kaso, sa kabaligtaran, maaari silang maging kapaki-pakinabang at sinasadya na ginagamit ng gumagamit. Halimbawa, medyo madalas ang paraan na ito ay ginagamit para sa mga pag-urong ng pag-uulit kapag nagtatayo ng mga modelo sa ekonomiya. Subalit ang problema ay na, anuman ang sinasadya o hindi alam ang paggamit ng cyclic expression, ang Excel sa pamamagitan ng default ay tatanggalin pa rin ang operasyon sa kanila, upang hindi humantong sa labis na labis na sistema. Sa kasong ito, ang isyu ng sapilitang hindi pagpapagana ng naturang lock ay maaaring may kaugnayan. Tingnan natin kung paano ito gagawin.
- Una sa lahat, lumipat sa tab "File" Mga application ng Excel.
- Susunod, mag-click sa item "Mga Pagpipilian"na matatagpuan sa kaliwang bahagi ng window na bubukas.
- Nagsisimula ang window ng Excel Parameters. Kailangan naming pumunta sa tab "Mga Formula".
- Ito ay nasa binuksan na window na posible upang makabuo ng pahintulot upang magsagawa ng mga pagpapatakbo ng paikot. Pumunta sa tamang bloke ng window na ito, kung saan matatagpuan ang mga setting ng Excel. Magtatrabaho kami sa block ng mga setting. "Parameter ng Pagkalkula"na matatagpuan sa itaas.
Upang paganahin ang paggamit ng mga cyclical expression, kailangan mong suriin ang kahon sa tabi ng parameter "Paganahin ang Iterative Calculations". Bilang karagdagan, sa parehong block, maaari mong i-configure ang limitadong bilang ng mga pag-ulit at kamag-anak na kamalian. Sa pamamagitan ng default, ang kanilang mga halaga ay 100 at 0.001, ayon sa pagkakabanggit. Sa karamihan ng mga kaso, ang mga parameter na ito ay hindi kailangang baguhin, bagaman kung kinakailangan o kung nais mo, maaari kang gumawa ng mga pagbabago sa ipinahiwatig na mga patlang. Ngunit narito ang kailangan upang isaalang-alang na ang napakaraming pag-ulit ay maaaring humantong sa isang malubhang pagkarga sa programa at sa sistema nang buo, lalo na kung nagtatrabaho ka sa isang file na naglalaman ng maraming cyclical na expression.
Kaya, magtakda ng isang lagyan ng tsek malapit sa parameter "Paganahin ang Iterative Calculations"at pagkatapos ay para magkabisa ang mga bagong setting, mag-click sa pindutan "OK"na matatagpuan sa ilalim ng window ng mga pagpipilian sa Excel.
- Pagkatapos nito, awtomatiko kaming pumunta sa sheet ng kasalukuyang libro. Tulad ng makikita mo, sa mga cell na kung saan matatagpuan ang mga pormularyo ng paikot, ngayon ang mga halaga ay kinakalkula nang wasto. Ang programa ay hindi nagbabawal sa mga kalkulasyon sa mga ito.
Ngunit pa rin ito ay nagkakahalaga ng noting na ang pagsasama ng mga pagpapatakbo ng cyclic ay hindi dapat abusuhin. Ang tampok na ito ay dapat gamitin lamang kapag ang gumagamit ay ganap na sigurado sa pangangailangan nito. Ang di-makatuwirang pagsasama ng mga pagpapatakbo ng paikot ay maaaring hindi lamang humantong sa labis na pag-load sa system at pabagalin ang mga kalkulasyon kapag nagtatrabaho sa dokumento, ngunit maaaring hindi sinasadyang ipakilala ng gumagamit ang isang maling pagpapahayag ng cyclic na sa pamamagitan ng default ay agad na hinarangan ng programa.
Tulad ng nakikita natin, sa napakaraming kaso, ang mga pabilog na sanggunian ay isang palatandaan na dapat harapin. Upang gawin ito, una sa lahat, dapat mong mahanap ang cyclical na relasyon mismo, pagkatapos ay kalkulahin ang cell na naglalaman ng error, at, sa wakas, alisin ito sa pamamagitan ng paggawa ng naaangkop na pagwawasto. Ngunit sa ilang mga kaso, ang mga pagpapatakbo ng cyclic ay maaaring maging kapaki-pakinabang sa mga kalkulasyon at ginagampanan ng sinasadya ng gumagamit. Ngunit kahit na pagkatapos, kapaki-pakinabang na lumapit sa kanilang paggamit nang may pag-iingat, maayos na pag-set up ng Excel at pag-alam ng panukalang-batas sa pagdaragdag ng mga link na, kung saan, kapag ginamit sa maraming dami, ay maaaring makapagpabagal sa system.
