Циклічні посилання в 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 і 0001 відповідно. У більшості випадків дані параметри змінювати не потрібно, хоча при необхідності або при бажанні можна внести зміни в зазначені поля. Але тут потрібно врахувати, що занадто велика кількість ітерацій може привести до серйозної навантаженні на програму і систему в цілому, особливо якщо ви працюєте з файлом, в якому розміщено багато циклічних виразів.

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

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

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

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

• ← Форми вводу даних в Microsoft Excel
• Відновлення зниклих листів в Microsoft Excel →