циклічні посилання в Microsoft Excel

Зміст

• Використання циклічних посилань
  • Створення циклічного посилання
  • Пошук циклічних посилань
  • Виправлення циклічних посилань
  • Дозвіл виконання циклічних операцій
• Питання та відповіді

Прийнято вважати, що циклічні посилання в Екселі є помилковим виразом. Дійсно, досить часто це саме так, але все-таки не завжди. Іноді вони застосовуються цілком усвідомлено. Давайте з'ясуємо, чим же є циклічні посилання, як їх створювати, як відшукати вже існуючі в документі, як працювати з ними або як при необхідності видалити.

Використання циклічних посилань

Перш за все, з'ясуємо, що ж являє собою циклічна посилання. По суті, це вираз, який за допомогою формул в інших клітинках посилається саме на себе. Так само нею може бути посилання, розташована в елементі листа, на який вона сама посилається.

Потрібно відзначити, що за замовчуванням сучасні версії Excel автоматично блокують процес виконання циклічної операції. Це пов'язано з тим, що такі вирази в переважній більшості помилкові, а зациклення виробляє постійний процес перерахунку і обчислення, що створює додаткове навантаження на систему.

Створення циклічного посилання

Тепер давайте подивимося, як створити найпростіший циклічний вираз. Це буде посилання, розташоване в тій самій комірці, на яку воно посилається.

1. Виділяємо елемент листа A1 і записуємо в ньому наступний вираз:

   =A1

   Далі тиснемо на кнопку Enter на клавіатурі.


2. Після цього з'являється діалогове вікно попередження про циклічний вираз. Клацаємо в ньому по кнопці «OK» .

3. Таким чином, ми отримали циклічну операцію на аркуші, в якій клітинка посилається сама на себе.

Трохи ускладнимо завдання і створимо циклічне вираз з декількох осередків.

1. В будь-який елемент листа записуємо число. Нехай це буде клітина A1 , а число 5 .

2. В іншу клітинку ( B1 ) записуємо вираз:

   =C1


3. В наступний елемент ( C1 ) виробляємо запис такої формули:

   =A1


4. Після цього повертаємося в клітинку A1 , в якій встановлено число 5 . Посилаємося в ній на елемент B1 :

   =B1

   Тиснемо на кнопку Enter .


5. Таким чином, цикл замкнувся, і ми отримали класичну циклічну посилання. Після того, як вікно попередження закрито, ми бачимо, що програма позначила циклічний зв'язок синіми стрілками на аркуші, які називаються стрілками трасування.

Тепер перейдемо до створення циклічного виразу на прикладі таблиці. У нас є таблиця реалізації продуктів харчування. Вона складається з чотирьох колонок, в яких зазначено найменування товару, кількість проданої продукції, ціна і сума виручки від продажу всього обсягу. У таблиці в останньому стовпці вже є формули. У них проводиться розрахунок виручки шляхом множення кількості на ціну.

1. Щоб зациклити формулу в першому рядку, виділяємо елемент листа з кількістю першого за рахунком товару ( B2 ). Замість статичного значення ( 6 ) вписуємо туди формулу, яка буде вважати кількість товару шляхом ділення загальної суми ( D2 ) на ціну ( C2 ):

   =D2/C2

   Клацаємо по кнопці Enter .


2. У нас вийшла перша циклічна посилання, взаємозв'язок в якій звично позначена стрілкою трасування. Але як бачимо, результат помилковий і дорівнює нулю, так як вже було сказано раніше, Excel блокує виконання циклічних операцій.

3. Скопіюємо вираз в усі інші осередки стовпця з кількістю продукції. Для цього встановлюємо курсор в нижній правий кут того елемента, який вже містить формулу. Курсор перетворюється в хрестик, який прийнято називати маркером заповнення. Затискаємо ліву кнопку миші і тягнемо цей хрестик до кінця таблиці вниз.

4. Як бачимо, вираз було скопійовано в усі елементи стовпця. Але, позначена стрілкою трасування тільки одна взаємозв'язок. Зауважимо це на майбутнє.

Пошук циклічних посилань

Як ми вже бачили вище, не у всіх випадках програма позначає взаємозв'язок циклічної посилання з об'єктами, навіть якщо вона є на аркуші. З огляду на той факт, що в переважній більшості циклічні операції шкідливі, їх слід видалити. Але для цього їх потрібно спочатку відшукати. Як же це зробити, якщо вирази не позначені лінією зі стрілками? Давайте розберемося з цим завданням.

1. Отже, якщо при запуску файлу Excel у вас відкривається інформаційне вікно про те, що він містить циклічну посилання, то її бажано відшукати. Для цього переміщаємося у вкладку «Формули» . Тиснемо на стрічці на трикутник, який розміщений праворуч від кнопки " Перевірка наявності помилок» , розташованої в блоці інструментів " залежності формул» . Відкривається меню, в якому слід навести курсор на пункт " циклічні посилання» . Після цього в наступному меню відкривається список адрес елементів листа, в яких програма виявила циклічні вирази.

2. При кліці на конкретну адресу відбувається виділення відповідної комірки на аркуші.

Існує ще один спосіб дізнатися, де розташовується циклічна посилання. Повідомлення про дану проблему і адреса елемента, що містить подібний вираз, розташовується в лівій частині рядка стану, яка знаходиться внизу вікна Excel. Правда, на відміну від попереднього варіанту, на рядку стану відображатися будуть адреси не всіх елементів, що містять циклічні посилання, якщо їх багато, а тільки одного з них, який з'явився раніше інших.

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

Урок: як знайти циклічні посилання в Excel

Виправлення циклічних посилань

Як вже говорилося вище, в переважній більшості випадків циклічні операції – це зло, від якого слід позбавлятися. Тому, закономірно, що після того, як циклічна зв'язок виявлена, потрібно її виправити, щоб привести формулу до нормального вигляду.

Для того, щоб виправити циклічну залежність, потрібно простежити всю взаємозв'язок осередків. Навіть якщо перевірка вказала конкретну комірку, то помилка може критися не в ній самій, а в іншому елементі ланцюжка залежності.

1. У нашому випадку, незважаючи на те, що програма вірно вказала на одну з комірок циклу ( D6 ), реальна помилка криється в іншій комірці. Виділяємо елемент D6 , щоб дізнатися, з яких клітинок він підтягує значення. Дивимося на вираз в рядку формул. Як бачимо, значення в цьому елементі листа формується шляхом множення вмісту осередків B6 і C6 .

2. Переходимо до осередку C6 . Виділяємо її і дивимося на рядок формул. Як бачимо, це звичайне статичне значення ( 1000 ), що не є продуктом обчислення формули. Тому можна з упевненістю сказати, що зазначений елемент не містить помилки, що викликає створення циклічних операцій.

3. Переходимо до наступної комірки ( B6 ). Після виділення в рядку формул ми бачимо, що вона містить обчислюваний вираз ( =D6/C6 ), яке підтягує дані з інших елементів таблиці, зокрема, з комірки D6 . Таким чином, осередок D6 посилається на дані елемента B6 і навпаки, що викликає зацикленість.

   Тут взаємозв'язок ми вирахували досить швидко, але в реальності бувають випадки, коли в процесі обчислення бере участь безліч осередків, а не три елементи, як у нас. Тоді пошук може зайняти досить багато часу, адже доведеться вивчити кожен елемент циклічності.

4. Тепер нам потрібно зрозуміти, в якій саме комірці ( B6 або D6 ) міститься помилка. Хоча, формально це навіть не помилка, а просто надмірне використання посилань, яке призводить до зациклення. Під час процесу вирішення того, яку клітинку слід відредагувати, потрібно застосовувати логіку. Тут немає чіткого алгоритму дій. У кожному конкретному випадку ця логіка буде своя.

   Наприклад, якщо в нашій таблиці загальна сума повинна обчислюватися шляхом множення кількості фактично проданого товару на його ціну, то можна сказати, що посилання, що розраховує кількість від загальної суми продажу, тут явно зайва. Тому ми її видаляємо і замінюємо на статичне значення.


5. Подібну операцію проводимо і над усіма іншими циклічними виразами, якщо вони є на аркуші. Після того, як абсолютно всі циклічні посилання були видалені з книги, повідомлення про наявність даної проблеми має зникнути з рядка стану.

   Крім того, чи повністю були видалені циклічні вирази, можна дізнатися, скориставшись інструментом перевірки наявності помилок. Переходимо у вкладку «Формули» і тиснемо вже знайомий нам трикутник праворуч від кнопки «перевірка помилок» у групі інструментів " залежності формул» . Якщо в запустився меню пункт " циклічні посилання» не буде активний, то, значить, ми видалили всі подібні об'єкти з документа. У зворотному випадку, потрібно буде застосувати процедуру видалення до елементів, які знаходяться в списку, тим же розглянутим раніше способом.

Дозвіл виконання циклічних операцій

У попередній частині уроку ми розповідали, в основному, як боротися з циклічними посиланнями, або як їх знайти. Але, раніше розмова йшла також про те, що в деяких випадках вони, навпаки, можуть бути корисними і усвідомлено використовуватися Користувачем. Наприклад, досить часто даний метод застосовується для ітеративних обчислень при побудові економічних моделей. Але біда полягає в тому, що, незалежно від того, усвідомлено або несвідомо ви використовуєте циклічне вираз, Excel за замовчуванням все одно буде блокувати операцію по ним, щоб не привести до зайвої перевантаження системи. В такому випадку актуальним стає питання примусового відключення такого блокування. Давайте подивимося, як це зробити.

1. Перш за все, переміщаємося у вкладку»Файл" програми Excel.

2. Далі клацаємо по пункту»Параметри" , розташованому в лівій частині вікна, що відкрилося.

3. Відбувається запуск вікна параметрів Ексель. Нам потрібно перейти у вкладку «Формули» .

4. Саме у вікні, можна буде зробити дозвіл виконання циклічних операцій. Переходимо в правий блок цього вікна, де знаходяться безпосередньо самі настройки Excel. Ми будемо працювати з блоком налаштувань " параметри обчислень» , який розташований у самому верху.

   Щоб дозволити застосування циклічних виразів, потрібно встановити галочку біля параметра " включити ітеративні обчислення» . Крім того, в цьому ж блоці можна налаштувати граничне число ітерацій і відносну похибку. За замовчуванням їх значення дорівнюють 100 і 0,001 відповідно. У більшості випадків дані параметри змінювати не потрібно, хоча при необхідності або при бажанні можна внести зміни в зазначені поля. Але тут потрібно врахувати, що занадто велика кількість ітерацій може привести до серйозного навантаження на програму і систему в цілому, особливо якщо ви працюєте з файлом, в якому розміщено багато циклічних виразів.

   Отже, встановлюємо галочку біля параметра " включити ітеративні обчислення» , а потім, щоб нові настройки вступили в силу, тиснемо на кнопку «OK» , розміщену в нижній частині вікна параметрів Excel.


5. Після цього ми автоматично переходимо на аркуш поточної книги. Як бачимо, в осередках, в яких розташовуються циклічні формули, тепер значення розраховуються коректно. Програма не блокує обчислення в них.

Але все-таки варто зауважити, що включенням циклічних операцій зловживати не варто. Застосовувати дану можливість слід тільки тоді, коли користувач повністю впевнений в її необхідності. Необгрунтоване включення циклічних операцій може не тільки привести до надмірного навантаження на систему і уповільнити обчислення при роботі з документом, але користувач може ненавмисно внести помилкове циклічне вираз, яке за замовчуванням тут же було б заблоковано програмою.

Як ми бачимо, в переважній більшості випадків, циклічні посилання являють собою явище, з яким потрібно боротися. Для цього, перш за все, слід виявити саму циклічну взаємозв'язок, потім обчислити осередок, де міститься помилка, і, нарешті, усунути її, внісши відповідні корективи. Але в окремих випадках циклічні операції можуть бути корисні при обчисленнях і проводитися Користувачем усвідомлено. Але навіть тоді варто до їх використання підходити з обережністю, правильно налаштувавши Excel і знаючи міру в додаванні подібних посилань, які при використанні в масовій кількості здатні уповільнити роботу системи.