Ang isa sa mga pinakakaraniwang babala ng error na nakakaharap ng mga gumagamit sa Excel ay ang 'Circular Reference'. Libu-libong user ang may parehong problema, at nangyayari ito kapag ang isang formula ay direktang tumukoy pabalik sa sarili nitong cell nang direkta o hindi, na nagiging sanhi ng walang katapusang loop ng mga kalkulasyon.
Halimbawa, mayroon kang dalawang value sa mga cell B1 at B2. Kapag ang formula =B1+B2 ay ipinasok sa B2, ito ay lumilikha ng isang pabilog na sanggunian; ang formula sa B2 ay paulit-ulit na muling kinakalkula ang sarili dahil sa bawat oras na ito ay nagkalkula, ang B2 na halaga ay nagbabago.
Karamihan sa mga pabilog na sanggunian ay hindi sinasadyang mga pagkakamali; Babalaan ka ng Excel tungkol sa mga ito. Gayunpaman, mayroon ding mga inilaan na pabilog na sanggunian, na ginagamit upang gumawa ng mga umuulit na kalkulasyon. Ang mga hindi sinasadyang pabilog na sanggunian sa iyong worksheet ay maaaring maging sanhi ng hindi tamang pagkalkula ng iyong formula.
Samakatuwid, sa artikulong ito, ipapaliwanag namin ang lahat ng kailangan mong malaman tungkol sa mga circular reference, at pati na rin kung paano maghanap, ayusin, alisin, at gamitin ang mga circular reference sa Excel.
Paano Maghanap at Pangasiwaan ang Circular Reference sa Excel
Kapag nagtatrabaho sa Excel, minsan ay nakakaranas kami ng mga circular reference error na nangyayari kapag nagpasok ka ng formula na kinabibilangan ng cell kung saan naninirahan ang iyong formula. Karaniwan, nangyayari ito kapag sinusubukan ng iyong formula na kalkulahin ang sarili nito.
Halimbawa, mayroon kang column ng mga numero sa cell A1:A4 at ginagamit mo ang SUM function (=SUM(A1:A5)) sa cell A5. Direktang tumutukoy ang cell A5 sa sarili nitong cell, na mali. Samakatuwid, makukuha mo ang sumusunod na pabilog na reference na babala:
Kapag nakuha mo na ang mensahe ng babala sa itaas, maaari mong i-click ang button na 'Tulong' upang malaman ang higit pa tungkol sa error, o isara ang window ng mensahe ng error sa pamamagitan ng pag-click sa alinman sa button na 'OK' o 'X' at makuha ang '0' bilang resulta.
Minsan ang mga circular reference loop ay maaaring maging sanhi ng pag-crash ng iyong kalkulasyon o pabagalin ang pagganap ng iyong worksheet. Ang pabilog na sanggunian ay maaari ding humantong sa ilang iba pang mga isyu, na hindi agad makikita. Kaya, pinakamahusay na iwasan ang mga ito.
Direkta at Di-tuwirang Pabilog na Sanggunian
Ang mga Circular na sanggunian ay maaaring ikategorya sa dalawang uri: Direktang Pabilog na Sanggunian at Di-tuwirang Pabilog na Sanggunian.
Direktang Sanggunian
Ang isang direktang pabilog na sanggunian ay medyo simple. Ang direktang pabilog na reference na mensahe ng babala ay lumalabas kapag ang formula ay direktang nagre-refer pabalik sa sarili nitong cell.
Sa halimbawa sa ibaba, ang formula sa cell A2 ay direktang tumutukoy sa sarili nitong cell (A2).
Sa sandaling mag-pop up ang mensahe ng babala, maaari kang mag-click sa 'OK', ngunit magreresulta lamang ito sa '0'.
Hindi Direktang Pabilog na Sanggunian
Ang isang hindi direktang pabilog na sanggunian sa Excel ay nangyayari kapag ang isang halaga sa isang formula ay tumutukoy pabalik sa sarili nitong cell, ngunit hindi direkta. Sa madaling salita, ang pabilog na sanggunian ay maaaring mabuo sa pamamagitan ng dalawang mga cell na tumutukoy sa isa't isa.
Ipaliwanag natin sa simpleng halimbawang ito.
Ngayon ang halaga ay nagsisimula sa A1 na may halagang 20.
Susunod, ang cell C3 ay tumutukoy sa cell A1.
Pagkatapos, ang cell A5 ay tumutukoy sa cell C3.
Ngayon palitan ang halaga 20 sa cell A1 ng formula tulad ng ipinapakita sa ibaba. Ang bawat iba pang cell ay nakasalalay sa cell A1. Kapag gumamit ka ng reference ng anumang iba pang nakaraang formula cell sa A1, magdudulot ito ng circular reference na babala. Dahil, ang formula sa A1 ay tumutukoy sa cell A5, na tumutukoy sa C3, at ang cell C3 ay tumutukoy pabalik sa A1, kaya ang pabilog na sanggunian.
Kapag na-click mo ang 'OK', nagreresulta ito sa isang halaga ng 0 sa cell A1 at ang Excel ay lumilikha ng isang naka-link na linya na nagpapakita ng Trace Precedents at Trace Dependents tulad ng ipinapakita sa ibaba. Magagamit namin ang feature na ito para madaling mahanap at ayusin/alisin ang mga circular reference.
Paano Paganahin / I-disable ang Mga Circular na Sanggunian sa Excel
Bilang default, ang mga umuulit na kalkulasyon ay naka-off (naka-disable) sa Excel. Ang mga umuulit na kalkulasyon ay mga paulit-ulit na kalkulasyon hanggang sa matugunan nito ang isang partikular na kundisyon. Kapag ito ay hindi pinagana, ang Excel ay nagpapakita ng isang Circular Reference na mensahe at nagbabalik ng 0 bilang resulta.
Gayunpaman, kung minsan ang mga pabilog na sanggunian ay kinakailangan upang makalkula ang isang loop. Upang gumamit ng pabilog na sanggunian, dapat mong paganahin ang mga umuulit na kalkulasyon sa iyong Excel at ito ay magbibigay-daan sa iyong gawin ang iyong mga kalkulasyon. Ngayon, hayaan mo kaming ipakita sa iyo kung paano mo paganahin o hindi paganahin ang mga umuulit na kalkulasyon.
Sa Excel 2010, Excel 2013, Excel 2016, Excel 2019, at Microsoft 365, pumunta sa tab na ‘File’ sa kaliwang sulok sa itaas ng Excel, pagkatapos ay i-click ang ‘Options’ sa kaliwang pane.
Sa window ng Excel Options, pumunta sa tab na 'Formula' at lagyan ng tsek ang check box na 'Enable iterative calculation' sa ilalim ng seksyong 'Calculation options'. Pagkatapos ay i-click ang 'OK' upang i-save ang mga pagbabago.
Ito ay magbibigay-daan sa umuulit na pagkalkula at sa gayon ay nagbibigay-daan sa pabilog na sanggunian.
Upang makamit ito sa mga pervious na bersyon ng Excel sundin ang mga hakbang na ito:
- Sa Excel 2007, mag-click sa pindutan ng Opisina > Mga opsyon sa Excel > Mga Formula > Lugar ng Pag-ulit.
- Sa Excel 2003 at mga naunang bersyon, kailangan mong pumunta sa Menu > Tools > Options > Calculation tab.
Pinakamataas na Mga Pag-ulit at Pinakamataas na Mga Parameter ng Pagbabago
Sa sandaling pinagana mo ang umuulit na pagkalkula, maaari mong kontrolin ang umuulit na pagkalkula, sa pamamagitan ng pagtukoy ng dalawang opsyon na available sa ilalim ng seksyong Paganahin ang umuulit na pagkalkula tulad ng ipinapakita sa screenshot sa ibaba.
- Pinakamataas na Pag-ulit – Tinutukoy ng numerong ito kung ilang beses dapat kalkulahin muli ang formula bago ibigay sa iyo ang huling resulta. Ang default na halaga ay 100. Kung babaguhin mo ito sa '50', uulitin ng Excel ang mga kalkulasyon ng 50 beses bago ibigay sa iyo ang huling resulta. Tandaan na kung mas mataas ang bilang ng mga pag-ulit, mas maraming mapagkukunan at oras ang kinakailangan upang makalkula.
- Pinakamataas na Pagbabago – Tinutukoy nito ang pinakamataas na pagbabago sa pagitan ng mga resulta ng pagkalkula. Tinutukoy ng halagang ito ang katumpakan ng resulta. Kung mas maliit ang numero, mas tumpak na resulta ang magiging resulta at mas magtatagal upang makalkula ang worksheet.
Kung naka-enable ang opsyon sa umuulit na pagkalkula, hindi ka makakatanggap ng anumang babala sa tuwing mayroong circular reference sa iyong worksheet. Paganahin lamang ang interactive na pagkalkula kapag ito ay talagang kinakailangan.
Maghanap ng Circular Reference sa Excel
Ipagpalagay na mayroon kang isang malaking dataset at nakuha mo ang pabilog na reference na babala, kakailanganin mo pa ring malaman kung saan (saang cell) naganap ang error upang ayusin ito. Upang makahanap ng mga pabilog na sanggunian sa Excel, sundin ang mga hakbang na ito:
Paggamit ng Error Checking Tool
Una, buksan ang worksheet kung saan nangyari ang circular reference. Pumunta sa tab na 'Formula', mag-click sa arrow sa tabi ng tool na 'Error Checking'. Pagkatapos ay i-hover lang ang cursor sa opsyon na 'Circular References', ipapakita sa iyo ng Excel ang listahan ng lahat ng mga cell na kasangkot sa circular reference tulad ng ipinapakita sa ibaba.
Mag-click sa alinmang cell address na gusto mo sa listahan at dadalhin ka nito sa cell address na iyon upang malutas ang isyu.
Gamit ang Status Bar
Maaari mo ring mahanap ang circular reference sa Status bar. Sa status bar ng Excel, ipapakita nito sa iyo ang pinakabagong cell address na may circular reference, gaya ng 'Circular References: B6' (tingnan ang screenshot sa ibaba).
Mayroong ilang mga bagay na dapat mong malaman kapag humahawak ng circular reference:
- Hindi ipapakita ng status bar ang circular reference cell address kapag pinagana ang Iterative Calculation option, kaya kailangan mong i-disable ito bago mo simulan ang pagtingin sa workbook para sa mga circular reference.
- Kung sakaling hindi makita ang pabilog na sanggunian sa aktibong sheet, ang status bar ay nagpapakita lamang ng 'Mga Pabilog na Sanggunian' na walang cell address.
- Makakakuha ka lamang ng isang pabilog na reference prompt nang isang beses at pagkatapos mong i-click ang 'OK', hindi na ito muling magpapakita ng prompt sa susunod na pagkakataon.
- Kung may mga circular reference ang iyong workbook, ipapakita nito sa iyo ang prompt sa tuwing bubuksan mo ito hanggang sa malutas mo ang circular reference o hanggang sa i-on mo ang umuulit na pagkalkula.
Mag-alis ng Circular Reference sa Excel
Ang paghahanap ng mga Circular na sanggunian ay madali ngunit ang pag-aayos nito ay hindi ganoon kadali. Sa kasamaang palad, walang opsyon sa Excel na hahayaan kang alisin ang lahat ng pabilog na sanggunian nang sabay-sabay.
Upang ayusin ang mga pabilog na sanggunian, kailangan mong hanapin ang bawat pabilog na sanggunian nang paisa-isa at subukang baguhin ito, alisin nang buo ang pabilog na formula, o palitan ito ng isa pa.
Minsan, sa mga simpleng formula, ang kailangan mo lang gawin ay muling ayusin ang mga parameter ng formula upang hindi ito bumalik sa sarili nito. Halimbawa, baguhin ang formula sa B6 sa =SUM(B1:B5)*A5 (pagpapalit ng B6 sa B5).
Ibabalik nito ang resulta ng pagkalkula bilang '756'.
Sa mga kaso kapag ang isang Excel circular reference ay mahirap hanapin, maaari mong gamitin ang mga tampok na Trace Precedent at Trace Dependents upang masubaybayan ito pabalik sa pinagmulan at malutas ito nang paisa-isa. Ipinapakita ng arrow kung aling mga cell ang apektado ng aktibong cell.
Mayroong dalawang paraan ng pagsubaybay na makakatulong sa iyo na tanggalin ang mga pabilog na sanggunian sa pamamagitan ng pagpapakita ng mga ugnayan sa pagitan ng mga formula at mga cell.
Upang ma-access ang mga paraan ng pagsubaybay, pumunta sa tab na 'Mga Formula', pagkatapos ay i-click ang alinman sa 'Trace Precedents' o 'Trace Dependents' sa grupo ng Pag-audit ng Formula.
Trace Precedents
Kapag pinili mo ang opsyong ito, sinusubaybayan nito pabalik ang mga cell na nakakaapekto sa halaga ng aktibong cell. Gumuhit ito ng asul na linya na nagpapahiwatig kung aling mga cell ang nakakaapekto sa kasalukuyang cell. Ang shortcut key para gumamit ng mga trace precedent ay Alt + T U T.
Sa halimbawa sa ibaba, ipinapakita ng asul na arrow ang mga cell na nakakaapekto sa halaga ng B6 ay B1:B6 at A5. Tulad ng makikita mo sa ibaba, ang cell B6 ay bahagi din ng formula, na ginagawa itong isang pabilog na sanggunian at nagiging sanhi ng formula na bumalik ng '0' bilang resulta.
Madali itong maayos sa pamamagitan ng pagpapalit ng B6 ng B5 sa argumento ng SUM: =SUM(B1:B5).
Trace Dependents
Ang tampok na trace dependents ay nagba-trace sa mga cell na nakadepende sa napiling cell. Ang tampok na ito ay gumuhit ng isang asul na linya na nagpapahiwatig kung aling mga cell ang apektado ng napiling cell. Ibig sabihin, ipinapakita nito kung aling mga cell ang naglalaman ng mga formula na tumutukoy sa aktibong cell. Ang shortcut key para magamit ang mga dependent ay Alt + T U D.
Sa sumusunod na halimbawa, ang cell D3 ay apektado ng B4. Ito ay nakasalalay sa B4 para sa halaga nito upang makagawa ng mga resulta. Samakatuwid, ang trace dependent ay gumuhit ng asul na linya mula B4 hanggang D3, na nagpapahiwatig na ang D3 ay nakadepende sa B4.
Sinasadyang Gumamit ng Mga Pabilog na Sanggunian sa Excel
Ang paggamit ng mga Circular na sanggunian na sadyang hindi inirerekomenda, ngunit maaaring may ilang mga bihirang kaso kung saan kailangan mo ng isang pabilog na sanggunian upang makuha mo ang output na gusto mo.
Ipaliwanag natin iyon sa pamamagitan ng paggamit ng isang halimbawa.
Upang magsimula sa, paganahin ang 'Iterative Calculation' sa iyong Excel workbook. Kapag na-enable mo na ang Iterative Calculation, maaari mong simulan ang paggamit ng mga circular reference para sa iyong kalamangan.
Ipagpalagay natin na bibili ka ng bahay at gusto mong magbigay ng 2% na komisyon sa kabuuang halaga ng bahay sa iyong ahente. Ang kabuuang gastos ay kakalkulahin sa cell B6 at ang porsyento ng komisyon (bayad sa ahente) ay kinakalkula sa B4. Ang komisyon ay kinakalkula mula sa kabuuang gastos at kasama sa kabuuang gastos ang komisyon. Dahil ang mga cell B4 at B6 ay nakasalalay sa isa't isa, lumilikha ito ng isang pabilog na sanggunian.
Ipasok ang formula upang kalkulahin ang kabuuang gastos sa cell B6:
=SUM(B1:B4)
Dahil kasama sa kabuuang gastos ang bayad sa ahente, isinama namin ang B4 sa formula sa itaas.
Para kalkulahin ang Agent Fee na 2%, ilagay ang formula na ito sa B4:
=B6*2%Ngayon ang formula sa cell B4 ay nakasalalay sa halaga ng B6 upang makalkula ang 2% ng kabuuang bayad at ang formula sa B6 ay nakasalalay sa B4 upang makalkula ang kabuuang gastos (kabilang ang bayad sa ahente), kaya ang circular reference.
Kung pinagana ang umuulit na pagkalkula, hindi ka bibigyan ng Excel ng babala o 0 sa resulta. Sa halip, ang resulta ng mga cell B6 at B4 ay kakalkulahin tulad ng ipinapakita sa itaas.
Ang opsyon sa umuulit na pagkalkula ay karaniwang hindi pinagana bilang default. Kung hindi mo ito na-on at kapag inilagay mo ang formula sa B4 na lilikha ng isang pabilog na sanggunian. Ibibigay ng Excel ang babala at kapag na-click mo ang 'OK', ipapakita ang tracer arrow.
Ayan yun. Ito lang ang kailangan mong malaman tungkol sa mga circular reference sa Excel.