Excel

Циклични препратки в Microsoft Excel

Счита се, че цикличните препратки в Excel са грешен израз. Всъщност, доста често това е така, но все пак не винаги. Понякога те се използват съвсем съзнателно. Нека да разберем какви са циркулярните референции, как да ги създадем, как да открием съществуващите в документа, как да работим с тях или как да ги изтрием, ако е необходимо.

Използване на циркулярни препратки

На първо място, нека да разберем каква е цикличната референция. Всъщност това е израз, който чрез формули в други клетки се отнася до себе си. Също така, тя може да бъде връзка, разположена в листовия елемент, към който се отнася.

Трябва да се отбележи, че по подразбиране модерните версии на Excel автоматично блокират процеса на извършване на циклична операция. Това се дължи на факта, че такива изрази са преобладаващо погрешни, а цикълът създава постоянен процес на преизчисляване и изчисление, което създава допълнително натоварване на системата.

Създаване на кръгова справка

Сега нека видим как да създадем най-простия цикличен израз. Това ще бъде връзка, разположена в същата клетка, към която се отнася.

  1. Изберете елемента на лист A1 и напишете в него следния израз:

    =A1

    След това кликнете върху бутона Enter на клавиатурата.

    2. Създаване на обикновена кръгова препратка в Microsoft Excel

  2. След това се появява диалоговият прозорец за предупреждение за цикличния израз. Кликнете върху бутона "OK" в него.

    3. Диалог за известието за кръгова връзка в Microsoft Excel

  3. По този начин получихме циклична операция на лист, в който клетката се отнася към себе си.

Клетката се препраща към Microsoft Excel

Нека малко усложняваме задачата и да създадем цикличен израз от няколко клетки.

  1. Въведете номер на всеки елемент от листа. Нека да бъде клетка А1 и номер 5 .

    2. Номер 5 в клетка в Microsoft Excel

  2. В друга клетка ( В1 ) пишем израза:

    =C1

    3. Връзка в клетка в Microsoft Excel

  3. Към следващия елемент ( C1 ) написваме следната формула:

    =A1

    4. Една клетка препраща към друга в Microsoft Excel

  4. След това се връщаме в клетка А1 , в която е зададено числото 5 . Позоваваме се на него в елемент B1 :

    =B1

    Натиснете бутона Enter .

    5. Настройване на връзка в клетка в Microsoft Excel

  5. По този начин цикълът е затворен и ние получихме класическа циклична справка. След като предупредителният прозорец е затворен, виждаме, че програмата е означила циклична връзка чрез сини стрелки на листа, които се наричат ​​стрелки за проследяване.

Маркиране на циклични връзки в Microsoft Excel

Сега нека продължим да създаваме цикличен израз, използвайки таблицата с примери. Имаме маса за продажба на храна. Той се състои от четири колони, които посочват името на продукта, броя продадени продукти, цената и размера на приходите от продажбата на целия обем. В таблицата в последната колона вече има формули. В тях приходите се изчисляват, като количеството се умножи по цената.

Изчисляване на приходите в таблица в Microsoft Excel

  1. За да фиксирате формулата в първия ред, изберете елемента на листа с количеството на първия продукт ( В2 ). Вместо статичната стойност ( 6 ) въвеждаме формула, която ще вземе количеството стоки, като раздели общата сума ( D2 ) на цената ( C2 ):

    =D2/C2

    Кликваме върху бутона Enter .

    2. Вмъкване на кръгова препратка в таблица в Microsoft Excel

  2. Имаме първата циклична връзка, връзката, в която обикновено се обозначава със стрелката на следата. Но както виждате, резултатът е грешен и равен на нула, тъй като вече е казано преди, Excel блокира изпълнението на циклични операции.

    3. Циклична справка в таблицата в Microsoft Excel

  3. Копирайте израза във всички останали клетки в колоната с броя продукти. За да направите това, задайте курсора в долния десен ъгъл на елемента, който вече съдържа формулата. Курсорът се преобразува в кръст, който обикновено се нарича маркер за попълване. Задръжте левия бутон на мишката и преместете кръста до края на масата.

    4. Филтър в Microsoft Excel

  4. Както можете да видите, изразът бе копиран във всички елементи на колоната. Но има само една връзка, маркирана със стрелка за проследяване. Нека да отбележим това за в бъдеще.

Цикличните връзки се копират в таблица в Microsoft Excel

Намиране на кръгли препратки

Както видяхме по-горе, не винаги програмите означават връзката на циркулярно позоваване на обекти, дори и да съществува върху лист. Предвид факта, че огромното мнозинство от цикличните операции са вредни, те трябва да бъдат премахнати. Но за това първо трябва да бъдат намерени. Как може да се направи това, ако изразите не са маркирани с стрелка? Нека се заемем с тази задача.

  1. Така че, ако отворите информационен прозорец, когато стартирате файл в Excel, който съдържа кръгова връзка, трябва да го намерите. За тази цел преместваме раздела "Формули" . Кликнете върху лентата на триъгълника, който се намира отдясно на бутона "Проверка за грешки" , намиращ се в кутията с инструменти "Формуляри за зависимости" . Отваря се меню, в което да се придвижи курсорът към елемента "Циклични връзки" . След това следващото меню отваря списъка с адреси на листовите елементи, в които програмата е намерила цикличните изрази.

    2. Търсене на циркулярни препратки в Microsoft Excel

  2. Когато кликнете върху конкретен адрес, се избира съответната клетка в листа.

Отидете на клетка с кръгла референция в Microsoft Excel

Има и друг начин да разберете къде е разположена кръговата връзка. Съобщението за този проблем и адресът на елемента, съдържащ такъв израз, се намират в лявата част на лентата на състоянието, която се намира в долната част на прозореца на Excel. За разлика от предишния вариант, обаче, в лентата на състоянието ще се показват адресите на не всички елементи, съдържащи кръгови връзки, ако има много от тях, но само един от тях, който се появи пред останалите.

Съобщение за кръговата връзка в лентата на състоянието в Microsoft Excel

Освен това, ако сте в книга, съдържаща цикличен израз, а не в листа, в който се намира, но от друга страна, в този случай в лентата на състоянието ще се показва само съобщение за грешка без адрес.

Циклична връзка към друг лист в Microsoft Excel

Урокът: Как да намерите циркулярни препратки в Excel

Коригиране на цикличните връзки

Както вече беше споменато по-горе, в повечето случаи цикличните операции са зло, от което човек трябва да се отърве. Ето защо е естествено, след като се открие цикличната комуникация, е необходимо да се коригира, за да се доведе формулата в нормална форма.

За да се коригира цикличната зависимост, е необходимо да се проследи цялостното свързване на клетките. Дори ако проверката указва определена клетка, грешката може да не е сама по себе си, а в друг елемент на веригата на зависимостта.

  1. В нашия случай, независимо от факта, че програмата правилно сочи към една от клетките на бримката ( D6 ), истинската грешка се крие в друга клетка. Изберете елемента D6, за да разберете от кои клетки той изважда стойността. Погледнете израза във формулата. Както можете да видите, стойността в този листов елемент се формира чрез умножаване на съдържанието на клетките B6 и C6 .

    2. Изразяване в програмата в Microsoft Excel

  2. Отидете в клетка C6 . Изберете го и погледнете реда на формулата. Както можете да видите, това е обичайната статична стойност ( 1000 ), която не е продукт за изчисляване на формулата. Ето защо можем със сигурност да кажем, че посоченият елемент не съдържа грешка, която причинява създаването на циклични операции.

    3. Статична стойност в Microsoft Excel

  3. Отидете на следващата клетка ( B6 ). След избора в формулата за формули, виждаме, че тя съдържа изчислен израз ( = D6 / C6 ), който извлича данни от други елементи от таблицата, по-специално от клетка D6 . По този начин клетката D6 се отнася до данните на елемент В6 и обратно, което води до фиксиране.

    Циклична референция в таблица клетка в Microsoft Excel

    Тук изчислихме връзката доста бързо, но в действителност има случаи, когато в изчислителния процес има много клетки, а не три елемента, както го правим. Тогава търсенето може да отнеме доста време, защото трябва да изучавате всеки елемент на цикличност.

  4. Сега трябва да разберем коя клетка ( B6 или D6 ) съдържа грешката. Въпреки че, формално, това дори не е грешка, а просто прекомерно използване на референции, което води до цикъл. По време на процеса на вземане на решение коя клетка да редактирате, трябва да приложите логика. Няма ясен алгоритъм за действие. Във всеки случай тази логика ще бъде нейната.

    Например, ако в нашата таблица общата сума трябва да се изчисли, като се умножи количеството на действително продадените стоки по нейната цена, тогава можем да кажем, че връзката, която изчислява сумата от общата сума за продажба, очевидно е излишна. Затова го изтриваме и го заместваме със статична стойност.

    5. Справката се заменя със стойности в Microsoft Excel

  5. Извършваме подобна операция на всички други циклични изрази, ако те съществуват върху лист. След като абсолютно всички циркулярни препратки са премахнати от книгата, съобщението за наличието на този проблем трябва да изчезне от лентата на състоянието.

    Освен това, ако цикличните изрази бяха напълно изтрити, можете да ги откриете, като използвате инструмента за проверка на грешки. Отидете в раздела "Формули" и кликнете върху триъгълника, който вече знаем, вдясно от бутона "Проверка за грешки" в лентата с инструменти "Формуляри за зависимости" . Ако елементът "Циклични връзки" не е активен в менюто, тогава сме премахнали всички такива обекти от документа. В противен случай ще трябва да приложите процедурата за изтриване на елементите, които са в списъка по същия начин както преди.

Цикличните референции в книгата не са в Microsoft Excel

Активиране на циклични операции

В предишната част на урока ние главно говорихме за това как да се справим с цикличните референции или как да ги намерим. Но по-рано разговорът се отнася и до факта, че в някои случаи те, напротив, могат да бъдат полезни и съзнателно използвани от потребителя. Например, този метод често се използва за итеративни изчисления при изграждането на икономически модели. Но проблемът е, че независимо дали съзнателно или несъзнателно използвате цикличен израз, по подразбиране Excel ще блокира операцията върху тях, за да не причинява прекомерно претоварване на системата. В този случай въпросът за насилствено деактивиране на такава ключалка става актуален. Да видим как да го направим.

Блокирайте цикличните връзки в Microsoft Excel

  1. Първо, преместваме раздела "Файл" на приложението Excel.

    2. Преместване в раздела Файл в Microsoft Excel

  2. След това кликнете върху елемента "Опции" , разположен в лявата част на прозореца, който се отваря.

    3. Отворете прозореца с опции в Microsoft Excel

  3. Прозорецът с опции в Excel започва. Трябва да отидем в раздела "Формули" .

    4. Отворете раздела Формули в Microsoft Excel

  4. В прозореца се отваря, че ще ви бъде позволено да извършвате циклични операции. Преминаваме към десния блок на този прозорец, където се намират настройките на Excel. Ще работим с конфигурационния блок "Опции за изчисление" , който се намира в най-горния край.

    За да позволите използването на циклични изрази, трябва да поставите отметка в квадратчето до Активиране на итерационните изчисления . Освен това в същия блок можете да зададете максималния брой итерации и относителната грешка. По подразбиране техните стойности са съответно 100 и 0.001. В повечето случаи не е необходимо да променяте тези параметри, въпреки че можете да направите промени в посочените полета, ако е необходимо или по желание. Но тук е необходимо да се има предвид, че твърде много повторения могат да доведат до сериозно натоварване на програмата и системата като цяло, особено ако работите с файл, съдържащ много циклични изрази.

    Така че, проверете опцията "Позволявате итеративни изчисления" и след това кликнете върху бутона "OK", разположен в долната част на прозореца на опциите на Excel, за да влязат в сила новите настройки.

    5. Активиране на итерационните изчисления в Microsoft Excel

  5. След това автоматично отиваме в листа на текущата книга. Както можете да видите, в клетките, в които са разположени цикличните формули, сега стойностите се изчисляват правилно. Програмата не блокира изчисленията в тях.

Клетките с циклични формули показват правилните стойности в Microsoft Excel

Но все пак си струва да се отбележи, че включването на циклични операции не бива да се злоупотребява. Използвайте тази функция само ако потребителят е напълно уверен в нуждата си. Неразумното включване на циклични операции може не само да доведе до прекомерно натоварване на системата и да забави изчислението при работа с документа, но потребителят може неволно да въведе грешен цикличен израз, който по подразбиране ще бъде блокиран от програмата.

Както виждаме, в преобладаващото мнозинство от случаите цикличните препратки са феномен, който трябва да бъде разгледан. За да направите това, първо трябва да намерите най-цикличната връзка, след това да изчислите клетката, в която се намира грешката, и накрая, да я премахнете, като направите съответните корекции. Но в някои случаи цикличните операции могат да бъдат полезни при изчисления и съзнателно направени от потребителя. Но дори и тогава, си струва да ги използвате с повишено внимание, правилно настройване на Excel и знаейки степента на добавяне на такива връзки, които, когато се използват в насипно състояние, могат да забавят системата.