Viens no visbiežāk sastopamajiem kļūdu brīdinājumiem, ar kuriem lietotāji saskaras programmā Excel, ir “Circular Reference”. Tūkstošiem lietotāju ir tāda pati problēma, un tā rodas, ja formula tieši vai netieši atsaucas uz savu šūnu, izraisot nebeidzamu aprēķinu cilpu.
Piemēram, šūnās B1 un B2 ir divas vērtības. Kad formulā =B1+B2 tiek ievadīta B2, tiek izveidota apļveida atsauce; formula B2 atkārtoti pārrēķina sevi, jo katru reizi, kad tā aprēķina, B2 vērtība ir mainījusies.
Lielākā daļa apļveida atsauču ir neparedzētas kļūdas; Excel jūs par tiem brīdinās. Taču ir paredzētas arī apļveida atsauces, kuras izmanto iteratīvu aprēķinu veikšanai. Netīšas apļveida atsauces darblapā var izraisīt nepareizu formulas aprēķinu.
Tāpēc šajā rakstā mēs izskaidrosim visu, kas jums jāzina par apļveida atsaucēm, kā arī to, kā programmā Excel atrast, labot, noņemt un izmantot apļveida atsauces.
Kā programmā Excel atrast un apstrādāt apļveida atsauces
Strādājot ar programmu Excel, mēs dažkārt sastopamies ar apļveida atsauces kļūdām, kas rodas, ievadot formulu, kas ietver šūnu, kurā atrodas jūsu formula. Būtībā tas notiek, kad jūsu formula mēģina aprēķināt pati.
Piemēram, šūnā A1:A4 ir skaitļu kolonna, un šūnā A5 izmantojat funkciju SUM (=SUM(A1:A5)). Šūna A5 tieši attiecas uz savu šūnu, kas ir nepareizi. Tādējādi jūs saņemsit šādu apļveida atsauces brīdinājumu:
Kad esat saņēmis iepriekš minēto brīdinājuma ziņojumu, varat noklikšķināt uz pogas “Palīdzība”, lai uzzinātu vairāk par kļūdu, vai aizvērt kļūdas ziņojuma logu, noklikšķinot uz pogas “Labi” vai “X”, un rezultātā iegūstiet “0”.
Dažreiz apļveida atsauces cilpas var izraisīt aprēķina avāriju vai palēnināt darblapas veiktspēju. Apļveida atsauce var izraisīt arī vairākas citas problēmas, kas nebūs pamanāmas uzreiz. Tāpēc vislabāk no tiem izvairīties.
Tiešas un netiešas cirkulāras atsauces
Apļveida atsauces var iedalīt divos veidos: tiešās cirkulārās atsauces un netiešās cirkulārās atsauces.
Tiešā atsauce
Tieša apļveida atsauce ir diezgan vienkārša. Tiešās apļveida atsauces brīdinājuma ziņojums tiek parādīts, kad formula tieši atsaucas atpakaļ uz savu šūnu.
Tālāk esošajā piemērā formula šūnā A2 tieši attiecas uz savu šūnu (A2).
Kad tiek parādīts brīdinājuma ziņojums, varat noklikšķināt uz “OK”, taču tas parādīs tikai “0”.
Netiešā cirkulārā atsauce
Netieša apļveida atsauce programmā Excel rodas, ja formulas vērtība atsaucas uz savu šūnu, bet ne tieši. Citiem vārdiem sakot, apļveida atsauci var veidot divas šūnas, kas atsaucas viena uz otru.
Paskaidrosim ar šo vienkāršo piemēru.
Tagad vērtība sākas no A1, kuras vērtība ir 20.
Tālāk šūna C3 attiecas uz šūnu A1.
Tad šūna A5 attiecas uz šūnu C3.
Tagad nomainiet vērtību 20 šūnā A1 ar formulu, kā parādīts tālāk. Katra otrā šūna ir atkarīga no šūnas A1. Ja izmantojat atsauci uz jebkuru citu iepriekšējo formulas šūnu A1, tas izraisīs apļveida atsauces brīdinājumu. Tā kā A1 formula attiecas uz šūnu A5, kas attiecas uz C3, un šūna C3 attiecas uz A1, tāpēc apļveida atsauce.
Noklikšķinot uz “OK”, šūnā A1 tiek parādīta vērtība 0, un programma Excel izveido saistītu rindiņu, kurā parādīti izsekošanas precedenti un atkarīgie izsekošana, kā parādīts tālāk. Mēs varam izmantot šo funkciju, lai viegli atrastu un labotu/noņemtu apļveida atsauces.
Kā iespējot/atspējot apļveida atsauces programmā Excel
Pēc noklusējuma iteratīvie aprēķini programmā Excel ir izslēgti (atspējoti). Iteratīvie aprēķini ir atkārtoti aprēķini, līdz tie atbilst noteiktam nosacījumam. Kad tas ir atspējots, programmā Excel tiek parādīts cirkulāras atsauces ziņojums un kā rezultāts tiek atgriezts 0.
Tomēr dažreiz ir vajadzīgas apļveida atsauces, lai aprēķinātu cilpu. Lai izmantotu apļveida atsauci, programmā Excel ir jāiespējo iteratīvie aprēķini, un tas ļaus jums veikt aprēķinus. Tagad parādīsim, kā iespējot vai atspējot iteratīvos aprēķinus.
Programmā Excel 2010, Excel 2013, Excel 2016, Excel 2019 un Microsoft 365 atveriet cilni Fails programmas Excel augšējā kreisajā stūrī un pēc tam kreisajā rūtī noklikšķiniet uz Opcijas.
Excel opciju logā atveriet cilni Formula un sadaļā "Aprēķinu opcijas" atzīmējiet izvēles rūtiņu Iespējot iteratīvo aprēķinu. Pēc tam noklikšķiniet uz "OK", lai saglabātu izmaiņas.
Tas nodrošinās iteratīvu aprēķinu un tādējādi apļveida atsauci.
Lai to panāktu iepriekšējās Excel versijās, rīkojieties šādi:
- Programmā Excel 2007 noklikšķiniet uz pogas Office > Excel opcijas > Formulas > Iterācijas apgabals.
- Programmā Excel 2003 un vecākās versijās atveriet Izvēlne > Rīki > Opcijas > cilne Aprēķins.
Maksimālās iterācijas un maksimālās izmaiņu parametri
Kad esat iespējojis iteratīvos aprēķinus, varat kontrolēt iteratīvos aprēķinus, sadaļā Iespējot iteratīvos aprēķinus norādot divas pieejamās opcijas, kā parādīts tālāk esošajā ekrānuzņēmumā.
- Maksimālās iterācijas – Šis skaitlis norāda, cik reižu formulai ir jāpārrēķina pirms gala rezultāta sniegšanas. Noklusējuma vērtība ir 100. Ja maināt to uz 50, programma Excel atkārtos aprēķinus 50 reizes, pirms parādīsiet gala rezultātu. Atcerieties, ka jo lielāks ir iterāciju skaits, jo vairāk resursu un laika ir nepieciešams aprēķināšanai.
- Maksimālās izmaiņas – Tas nosaka maksimālo izmaiņu starp aprēķinu rezultātiem. Šī vērtība nosaka rezultāta precizitāti. Jo mazāks skaitlis, jo precīzāks būs rezultāts un jo ilgāks laiks nepieciešams darblapas aprēķināšanai.
Ja ir iespējota iteratīvo aprēķinu opcija, jūs nesaņemsit nekādu brīdinājumu, ja jūsu darblapā būs cirkulāra atsauce. Iespējojiet interaktīvo aprēķinu tikai tad, kad tas ir absolūti nepieciešams.
Atrodiet apļveida atsauci programmā Excel
Pieņemsim, ka jums ir liela datu kopa un jūs saņemat apļveida atsauces brīdinājumu, jums joprojām būs jānoskaidro, kur (kurā šūnā) ir radusies kļūda, lai to labotu. Lai programmā Excel atrastu apļveida atsauces, veiciet šīs darbības:
Izmantojot kļūdu pārbaudes rīku
Vispirms atveriet darblapu, kurā ir notikusi apļveida atsauce. Dodieties uz cilni "Formula", noklikšķiniet uz bultiņas blakus rīkam "Kļūdu pārbaude". Pēc tam vienkārši virziet kursoru virs opcijas “Apļveida atsauces”, programma Excel parādīs visu šūnu sarakstu, kas ir iesaistītas apļveida atsaucē, kā parādīts tālāk.
Noklikšķiniet uz jebkuras šūnas adreses, kuru vēlaties sarakstā, un tas aizvedīs uz šo šūnas adresi, lai atrisinātu problēmu.
Statusa joslas izmantošana
Apļveida atsauci varat atrast arī statusa joslā. Programmas Excel statusa joslā tiks parādīta jaunākā šūnas adrese ar apļveida atsauci, piemēram, “Circular References: B6” (skatiet tālāk redzamo ekrānuzņēmumu).
Apstrādājot cirkulārās atsauces, jums jāzina dažas lietas:
- Statusa joslā netiks rādīta apļveida atsauces šūnas adrese, ja ir iespējota opcija Iteratīvais aprēķins, tāpēc tā ir jāatspējo, pirms sākat skatīt apļveida atsauces darbgrāmatā.
- Ja aktīvajā lapā netiek atrasta apļveida atsauce, statusa joslā tiek rādītas tikai “Circular References” bez šūnas adreses.
- Apļveida atsauces uzvedne tiks parādīta tikai vienu reizi, un pēc noklikšķināšanas uz Labi, nākamajā reizē uzvedne vairs netiks rādīta.
- Ja darbgrāmatā ir apļveida atsauces, tā parādīs uzvedni katru reizi, kad to atverat, līdz būsiet atrisinājis apļveida atsauci vai ieslēdziet iteratīvo aprēķinu.
Noņemiet cirkulāro atsauci programmā Excel
Apļveida atsauču atrašana ir vienkārša, taču to labošana nav tik vienkārša. Diemžēl programmā Excel nav tādas iespējas, kas ļautu noņemt visas apļveida atsauces uzreiz.
Lai labotu apļveida atsauces, katra apļveida atsauce ir jāatrod atsevišķi un jāmēģina to modificēt, noņemt apļveida formulu pavisam vai aizstāt ar citu.
Dažreiz vienkāršās formulās viss, kas jums jādara, ir pārregulēt formulas parametrus, lai tā neatsauktos uz sevi. Piemēram, mainiet B6 formulu uz =SUM(B1:B5)*A5 (mainot B6 uz B5).
Tas atgriezīs aprēķina rezultātu kā “756”.
Gadījumos, kad Excel apļveida atsauci ir grūti atrast, varat izmantot Precedentu izsekošanas un Atkarīgo izsekošanas līdzekļus, lai izsekotu tai atpakaļ līdz avotam un atrisinātu to pa vienam. Bultiņa parāda, kuras šūnas ietekmē aktīvā šūna.
Ir divas izsekošanas metodes, kas var palīdzēt dzēst apļveida atsauces, parādot attiecības starp formulām un šūnām.
Lai piekļūtu izsekošanas metodēm, atveriet cilni Formulas un pēc tam Formulu auditēšanas grupā noklikšķiniet uz Trace Precedents (Izsekošanas precedenti) vai Izsekojiet atkarīgos.
Izsekot precedentiem
Atlasot šo opciju, tā izseko atpakaļ šūnas, kas ietekmē aktīvās šūnas vērtību. Tas novelk zilu līniju, norādot, kuras šūnas ietekmē pašreizējo šūnu. Īsinājumtaustiņš, lai izmantotu izsekošanas precedentus, ir Alt + T U T.
Tālāk esošajā piemērā zilā bultiņa parāda šūnas, kas ietekmē B6 vērtību, ir B1:B6 un A5. Kā redzat tālāk, šūna B6 ir arī daļa no formulas, kas padara to par apļveida atsauci un liek formulai kā rezultātu atgriezt “0”.
To var viegli salabot, aizstājot B6 ar B5 SUM argumentā: =SUM(B1:B5).
Izsekot apgādājamajiem
Atkarīgo izsekošanas līdzeklis izseko šūnas, kas ir atkarīgas no atlasītās šūnas. Šis līdzeklis zīmē zilu līniju, norādot, kuras šūnas ietekmē atlasītā šūna. Tas nozīmē, ka tas parāda, kurās šūnās ir formulas, kas atsaucas uz aktīvo šūnu. Īsinājumtaustiņš, lai izmantotu apgādājamos, ir Alt + TU D.
Nākamajā piemērā šūnu D3 ietekmē B4. Tas ir atkarīgs no B4, lai tā vērtība radītu rezultātus. Tādējādi izsekošanas atkarīgais velk zilu līniju no B4 līdz D3, norādot, ka D3 ir atkarīga no B4.
Apzināta apļveida atsauces izmantošana programmā Excel
Nav ieteicams apzināti izmantot apļveida atsauces, taču var būt daži reti gadījumi, kad jums ir nepieciešama apļveida atsauce, lai iegūtu vēlamo rezultātu.
Paskaidrosim to, izmantojot piemēru.
Vispirms savā Excel darbgrāmatā iespējojiet “Iteratīvo aprēķinu”. Kad esat iespējojis iteratīvo aprēķinu, varat sākt izmantot apļveida atsauces savā labā.
Pieņemsim, ka jūs pērkat māju un vēlaties savam aģentam piešķirt 2% komisijas maksu par mājas kopējām izmaksām. Kopējās izmaksas tiks aprēķinātas šūnā B6, bet komisijas maksa (aģenta honorārs) tiek aprēķināta B4. Komisija tiek aprēķināta no kopējām izmaksām un kopējās izmaksas ietver komisijas maksu. Tā kā šūnas B4 un B6 ir atkarīgas viena no otras, tiek izveidota apļveida atsauce.
Šūnā B6 ievadiet formulu, lai aprēķinātu kopējās izmaksas:
=SUMMA(B1:B4)
Tā kā kopējās izmaksās ir iekļauta aģenta maksa, mēs iekļāvām B4 iepriekš minētajā formulā.
Lai aprēķinātu aģenta honorāru 2%, ievietojiet šo formulu B4:
=B6*2%Tagad formula šūnā B4 ir atkarīga no B6 vērtības, lai aprēķinātu 2% no kopējās maksas, un formula B6 ir atkarīga no B4, lai aprēķinātu kopējās izmaksas (ieskaitot aģenta maksu), tāpēc apļveida atsauce.
Ja iteratīvais aprēķins ir iespējots, programma Excel nerādīs brīdinājumu vai 0 rezultātu. Tā vietā šūnu B6 un B4 rezultāts tiks aprēķināts, kā parādīts iepriekš.
Iteratīvo aprēķinu opcija parasti ir atspējota pēc noklusējuma. Ja jūs to neieslēdzāt un ievadot formulu B4, tiks izveidota apļveida atsauce. Excel izdos brīdinājumu, un, noklikšķinot uz Labi, tiks parādīta izsekotāja bultiņa.
Tieši tā. Tas bija viss, kas jums jāzina par apļveida atsaucēm programmā Excel.