Strādājot ar Excel formulām, dažkārt var tikt parādīta šāda brīdinājuma uzvedne.
Šī uzvedne norāda, ka jūsu darblapā ir apļveida atsauce, un tas var izraisīt vai nepareizu aprēķinu pēc formulām. Tā arī lūdz jūs risināt šo apļveida atsauces jautājumu un sakārtot to.
Šajā apmācībā es apskatīšu visu, kas jums jāzina par apļveida atsauci, kā arī kā atrast un noņemt apļveida atsauces programmā Excel.
Tātad, sāksim!
Kas ir apļveida atsauce programmā Excel?
Vienkārši sakot, apļveida atsauce notiek, kad šūnā ir formula, kas aprēķināšanai pati par sevi izmanto šūnu (kurā tā ir ievadīta).
Ļaujiet man mēģināt to izskaidrot ar vienkāršu piemēru.
Pieņemsim, ka datu kopa ir šūnā A1: A5 un šūnā A6 izmantojat tālāk norādīto formulu.
= SUMMA (A1: A6)
Tādējādi tiks parādīts apļveida atsauces brīdinājums.
Tas ir tāpēc, ka vēlaties summēt vērtības šūnā A1: A6, un rezultātam jābūt šūnā A6.
Tas rada cilpu, jo Excel tikai turpina pievienot jaunu vērtību šūnā A6, kas turpina mainīties (tātad apļveida atsauces cilpa).
Kā programmā Excel atrast apļveida atsauces?
Lai gan apļveida atsauces brīdinājuma uzvedne ir pietiekami laipna, lai pateiktu, ka tā pastāv jūsu darblapā, tā nenorāda, kur tas notiek un kādas šūnu atsauces to izraisa.
Tātad, ja darblapā mēģināt atrast un apstrādāt apļveida atsauces, jums jāzina veids, kā tās kaut kā atrast.
Tālāk ir norādītas darbības, lai programmā Excel atrastu apļveida atsauci:
- Aktivizējiet darblapu ar apļveida atsauci
- Noklikšķiniet uz cilnes Formulas
- Grupā Formulu rediģēšana noklikšķiniet uz nolaižamās ikonas Kļūdu pārbaude (nedaudz lejup vērstas bultiņas labajā pusē)
- Virziet kursoru virs opcijas Apļveida atsauces. Tas parādīs šūnu, kurai darblapā ir apļveida atsauce
- Noklikšķiniet uz šūnas adreses (kas tiek parādīta), un tā jūs aizvedīs uz šo šūnu darblapā.
Kad esat atrisinājis problēmu, varat vēlreiz veikt tās pašas darbības, un tajā tiks parādītas vairāk šūnu atsauces ar apļveida atsauci. Ja tādas nav, jūs neredzēsit nevienu šūnu atsauci,
Vēl viens ātrs un vienkāršs apļveida atsauces atrašanas veids ir statusa josla. Tās kreisajā pusē tiks parādīts teksts Apļveida atsauce kopā ar šūnas adresi.
Strādājot ar apļveida atsaucēm, jums jāzina dažas lietas:
- Ja ir iespējots iteratīvais aprēķins (aplūkots vēlāk šajā apmācībā), statusa joslā netiks rādīta apļveida atsauces šūnas adrese
- Ja apļveida atsauce nav aktīvajā lapā (bet citās tās pašas darbgrāmatas lapās), tā parādīs tikai apļveida atsauci, nevis šūnas adresi
- Ja vienreiz saņemat apļveida atsauces brīdinājuma uzvedni un to noraidāt, nākamajā reizē tā vairs netiks rādīta.
- Ja atverat darbgrāmatu ar apļveida atsauci, tā parādīs uzvedni, tiklīdz tiks atvērta darbgrāmata.
Kā noņemt apļveida atsauci programmā Excel?
Kad esat noskaidrojis, ka jūsu lapā ir apļveida atsauces, ir pienācis laiks tās noņemt (ja vien nevēlaties, lai tās būtu kāda iemesla dēļ).
Diemžēl tas nav tik vienkārši, kā nospiest dzēšanas taustiņu. Tā kā tie ir atkarīgi no formulām un katra formula ir atšķirīga, jums tas jāanalizē katrā gadījumā atsevišķi.
Ja jautājums ir tikai par to, ka kļūdas dēļ problēma rodas šūnas atsaucē, varat to labot, pielāgojot atsauci.
Bet dažreiz tas nav tik vienkārši.
Apļveida atsauci var izraisīt arī, pamatojoties uz vairākām šūnām, kas daudzos līmeņos tiek ievadītas viena otrai.
Ļaujiet man parādīt piemēru.
Zemāk šūnā C6 ir riņķveida atsauce, taču tas nav tikai vienkāršs pašnovērtējuma gadījums. Tas ir daudzlīmeņu, kur šūnas, ko tas izmanto aprēķinos, arī atsaucas viena uz otru.
- Formulas šūnā A6 ir = SUM (A1: A5)+C6
- Formula ir šūna C1 ir = A6*0,1
- Formula šūnā C6 ir = A6+C1
Iepriekš minētajā piemērā rezultāts šūnā C6 ir atkarīgs no vērtībām šūnā A6 un C1, kas savukārt ir atkarīgas no šūnas C6 (tādējādi radot apļveida atsauces kļūdu)
Un atkal es esmu izvēlējies patiešām vienkāršu piemēru tikai demonstrācijas nolūkos. Patiesībā tos varētu būt diezgan grūti izdomāt un varbūt tālu vienā un tajā pašā darblapā vai pat izkaisīti vairākās darblapās.
Šādā gadījumā ir viens veids, kā identificēt šūnas, kas izraisa apļveida atsauci, un pēc tam apstrādāt tās.
Tas tiek darīts, izmantojot opciju Trace Precedents.
Tālāk ir norādītas darbības, lai izmantotu izsekošanas precedentus, lai atrastu šūnas, kas barojas ar šūnu, kurai ir apļveida atsauce.
- Atlasiet šūnu, kurai ir apļveida atsauce
- Noklikšķiniet uz cilnes Formulas
- Noklikšķiniet uz Trace Precedents
Iepriekš minētās darbības parādīs zilas bultiņas, kas jums pateiks, kuras šūnas tiek ievadītas atlasītās šūnas formulā. Tādā veidā jūs varat pārbaudīt formulas un šūnas un atbrīvoties no apļveida atsauces.
Ja strādājat ar sarežģītiem finanšu modeļiem, iespējams, ka šie precedenti ir arī vairākos līmeņos.
Tas darbojas labi, ja jums ir visas formulas, kas attiecas uz vienas darblapas šūnām. Ja tā ir vairākās darblapās, šī metode nav efektīva.
Kā iespējot/atspējot atkārtotus aprēķinus programmā Excel
Ja šūnā ir apļveida atsauce, vispirms tiek parādīts brīdinājuma uzvedne, kā parādīts zemāk, un, aizverot šo dialoglodziņu, šūnā tiks parādīts 0.
Tas ir tāpēc, ka, ja ir apļveida atsauce, tā ir bezgalīga cilpa, un Excel nevēlas tajā iekļūt. Tātad tas atgriež 0.
Bet dažos gadījumos jūs patiešām vēlaties, lai apļveida atsauce būtu aktīva un veiktu pāris atkārtojumus. Šādā gadījumā bezgalīgas cilpas vietā jūs varat izlemt, cik reizes cilpa jāpalaiž.
To sauc iteratīvs aprēķins programmā Excel.
Tālāk ir norādītas darbības, lai iespējotu un konfigurētu iteratīvos aprēķinus programmā Excel:
- Noklikšķiniet uz cilnes Fails
- Noklikšķiniet uz Opcijas. Tiks atvērts dialoglodziņš Excel opcijas
- Kreisajā rūtī atlasiet Formula
- Sadaļā Aprēķina opcijas atzīmējiet izvēles rūtiņu “Iespējot atkārtotu aprēķinu”. Šeit jūs varat norādīt maksimālo atkārtojumu un maksimālo izmaiņu vērtību
Tieši tā! Iepriekš minētās darbības ļautu atkārtot aprēķinus programmā Excel.
Ļaujiet man arī ātri izskaidrot divas iteratīvā aprēķina iespējas:
- Maksimālās atkārtošanās: Šis ir maksimālais reižu skaits, ko Excel vēlas aprēķināt pirms gala rezultāta sniegšanas. Tātad, ja jūs to norādīsit kā 100, programma Excel izpildīs cilpu 100 reizes, pirms sniegs jums gala rezultātu.
- Maksimālās izmaiņas: Šīs ir maksimālās izmaiņas, kuras, ja tās netiek sasniegtas starp atkārtojumiem, aprēķins tiks pārtraukts. Pēc noklusējuma vērtība ir .001. Jo zemāka šī vērtība, jo precīzāks būs rezultāts.
Atcerieties, ka, jo vairāk atkārtojumu tiek izpildīts, jo vairāk laika un resursu nepieciešams, lai Excel to paveiktu. Ja saglabājat maksimālo atkārtojumu skaitu augstu, tas var izraisīt jūsu Excel palēnināšanos vai avāriju.
Piezīme. Ja ir iespējoti iteratīvie aprēķini, programma Excel nerādīs apļveida atsauces brīdinājuma uzvedni un tagad parādīs to arī statusa joslā.Apzināti izmantojot apļveida atsauces
Vairumā gadījumu apļveida atsauces klātbūtne jūsu darblapā būtu kļūda. Tāpēc Excel parāda uzvedni, kurā teikts: “Mēģiniet noņemt vai mainīt šīs atsauces vai pārvietot formulas uz dažādām šūnām.”
Bet var būt daži īpaši gadījumi, kad jums ir nepieciešama apļveida atsauce, lai jūs varētu iegūt vēlamo rezultātu.
Vienu šādu konkrētu gadījumu es jau esmu rakstījis par laika zīmoga iegūšanu šūnā šūnā programmā Excel.
Piemēram, pieņemsim, ka vēlaties izveidot formulu, lai katrs ieraksts tiktu veikts A slejas šūnā, laika zīmogs parādās B slejā (kā parādīts zemāk):
Lai gan jūs varat viegli ievietot laika zīmogu, izmantojot zemāk esošo formulu:
= IF (A2 "", IF (B2 "", B2, NOW ()), "")
Iepriekš minētās formulas problēma ir tāda, ka tā atjaunina visus laika zīmogus, tiklīdz tiek veiktas kādas izmaiņas darblapā vai ja darba lapa tiek atkārtoti atvērta (jo formula TAGAD ir nepastāvīga)
Lai apietu šo problēmu, varat izmantot apļveida atsauces metodi. Izmantojiet to pašu formulu, bet iespējojiet iteratīvo aprēķinu.
Ir arī daži citi gadījumi, kad ir vēlama iespēja izmantot apļveida atsauci (vienu piemēru varat atrast šeit).
Piezīme. Lai gan dažos gadījumos varat izmantot apļveida atsauci, es uzskatu, ka vislabāk ir to neizmantot. Apļveida atsauces var arī ietekmēt jūsu darbgrāmatas darbību un var palēnināt tās darbību. Retos gadījumos, kad jums tas ir nepieciešams, es vienmēr izvēlos izmantot VBA kodus, lai paveiktu darbu.Es ceru, ka šī apmācība jums šķita noderīga!
Citas Excel apmācības, kas jums varētu noderēt:
- #REF! Kļūda programmā Excel; Kā labot atsauces kļūdu!
- Excel VBA kļūdu apstrāde
- Izmantojiet IFERROR kopā ar VLOOKUP, lai atbrīvotos no #N/A kļūdām
- Kā atsaukties uz citu lapu vai darbgrāmatu programmā Excel (ar piemēriem)
- Absolūtās, relatīvās un jaukto šūnu atsauces programmā Excel
