Циклическая ссылка это формула, которая ссылается на свою же ячейку, прямо или через другие формулы. Если ввести =СУММ(B2:B7) в B7, она появится: итог включает сам себя. Excel показывает предупреждение, ставит в ячейку 0 и называет её в Формулы > Проверка наличия ошибок > Циклические ссылки. Исправление: изменить диапазон так, чтобы он заканчивался перед ячейкой с формулой, здесь =СУММ(B2:B6). В таблицах формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой.
B7: =SUM(B2:B7) circular: B7 is inside its own range, Excel shows 0
B7: =SUM(B2:B6) fixed: the range stops above the total
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | 120 |
| 3 | Feb | 95 |
| 4 | Mar | 140 |
| 5 | Apr | 110 |
| 6 | May | 130 |
| 7 | Total | 595 |
=СУММ(B2:B6)Щёлкните B7: цветная рамка охватывает B2:B6 и заканчивается над итогом. Это самая частая циклическая ссылка из всех. Обычно она появляется, когда прямо над итогом вставляют строку, а диапазон СУММ вручную расширяют на строку дальше, чем нужно, или когда диапазон тянут мышью через ячейку итога.
Что Excel делает с циклической ссылкой
Когда вы вводите формулу, Excel показывает сообщение о том, что найдены одна или несколько циклических ссылок, где формула прямо или косвенно ссылается на свою ячейку, и что из-за этого вычисления могут быть неверными. Нажмите ОК, и формула останется, показывая 0 (или последнее значение, которое у неё было). Дальше:
- Строка состояния внизу окна показывает Циклические ссылки: B7 (адрес одной из ячеек цикла), пока этот лист активен.
- Пока вы работаете, Excel не повторяет сообщение, поэтому циклическая ссылка может незаметно оставаться в книге, и напоминает о ней только строка состояния.
- Другие формулы, зависящие от этой ячейки, используют 0, поэтому итоги дальше получаются неверными без всякой ошибки.
Та же книга в Google Таблицах показывает #REF! с пояснением "Circular dependency detected" (обнаружена циклическая зависимость).
Как найти циклические ссылки в Excel
- Посмотрите на строку состояния. Там указана ячейка на активном листе. Если написано только Циклические ссылки без адреса, цикл на другом листе.
- Откройте Формулы > Проверка наличия ошибок, нажмите маленькую стрелку рядом и наведите указатель на Циклические ссылки. В подменю перечислены ячейки в циклах. Щёлкните одну, чтобы её выделить.
- Выделив ячейку, выберите Формулы > Влияющие ячейки, чтобы нарисовать стрелки от ячеек, которые она читает. Идите по ним, пока одна не приведёт обратно к началу. Убрать стрелки их стирает.
- На Mac команды на том же месте: вкладка Формулы, Проверка наличия ошибок, затем Циклические ссылки.
Исправьте ячейку, которую показывает список, а затем снова проверьте список: в книге может быть несколько циклов, и Excel покажет следующий, когда первого не станет.
Косвенные циклические ссылки
Цикл через две или больше ячеек заметить труднее, потому что ни одна формула не упоминает свою ячейку.
C2: =B2*10% tax on the net price in B2
B2: =D2-C2 net price = total minus tax
D2: =B2+C2 total = net plus tax
Каждая формула выглядит разумно, но B2 нужна C2, C2 нужна B2, а D2 нужны обе. Одно из трёх значений должно быть исходным. Решите, какое число вы действительно знаете, введите его и вычисляйте из него остальные:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Net price | Tax (10%) | Total |
| 2 | Desk | $240.00 | $24.00 | $264.00 |
| 3 | Chair | $85.00 | $8.50 | $93.50 |
=B2*10%Цены без налога введены вручную, а налог и итог вычисляются из них: стол стоит 24.00 налога. Если вместо этого вы знаете итог, цена без налога равна =D2/(1+10%): формула решена относительно неизвестного, и ничто не ссылается на себя.
Доля от итога, который включает сам себя
Столбец долей от итога становится циклическим, когда итог суммирует и столбец долей, или когда строка итога попадает в диапазон, на который делятся доли.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Sales | Share |
| 2 | North | 420 | 42% |
| 3 | South | 310 | 31% |
| 4 | East | 180 | 18% |
| 5 | West | 90 | 9% |
| 6 | Online | 0 | 0% |
| 7 | Total | 1000 | 100% |
=B2/$B$7Каждая доля делится на B7, а B7 складывает только B2:B6. Online ничего не продал, поэтому его доля 0%, а C7 складывает доли в 100%. Если бы в B7 стояла =СУММ(B2:B7) или =СУММ(B2:C6), каждая доля зависела бы от себя. $ в $B$7 закрепляет итог, когда формула протягивается вниз; см. проценты.
Нарастающий остаток, который указывает на свою строку
Нарастающий итог прибавляет каждую новую сумму к остатку в строке выше. Ссылка на остаток в той же строке это цикл.
C3: =C3+B3 circular
C3: =C2+B3 previous balance plus this row's amount
| A | B | C | |
|---|---|---|---|
| 1 | Date | Amount | Balance |
| 2 | 2026-03-01 | 500 | 500 |
| 3 | 2026-03-04 | -120 | 380 |
| 4 | 2026-03-09 | -80 | 300 |
| 5 | 2026-03-15 | 250 | 550 |
| 6 | 2026-03-22 | -60 | 490 |
=C2+B3Первый остаток это просто первая сумма; каждая следующая строка прибавляет свою сумму к строке выше. Итоговый остаток 490. Щёлкните C4, и цветные рамки покажут C3 и B4, но никогда саму C4.
Комиссия от прибыли после комиссии
Некоторые циклические ссылки не опечатки, а вычисление, которое действительно зависит от собственного результата: комиссия 10% от прибыли, где прибыль это то, что остаётся после выплаты комиссии.
B5 (commission): =B6*B4 10% of profit
B6 (profit): =B2-B3-B5 revenue minus cost minus commission
Если включить итеративные вычисления (Файл > Параметры > Формулы > Включить итеративные вычисления или на Mac Excel > Параметры > Вычисление), Excel будет повторять цикл, пока числа не установятся. Здесь это работает, но заодно прячет все случайные циклы в книге, а некоторые циклы никогда не сходятся. Лучше решить уравнение. Если комиссия равна ставке, умноженной на (выручка минус затраты минус комиссия), то комиссия равна (выручка минус затраты), умноженной на ставку и делённой на (1 + ставка).
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Revenue | $50,000.00 |
| 3 | Cost | $30,000.00 |
| 4 | Rate | 10% |
| 5 | Commission | |
| 6 | Profit | $20,000.00 |
| 7 | Rate of profit | $2,000.00 |
Ваша очередь: Запишите комиссию в B5, не ссылаясь на B5 или B6: это 10% от прибыли после комиссии, то есть (выручка минус затраты), умноженная на ставку и делённая на 1 плюс ставка.
Когда B5 верна, B7 (10% от прибыли) равна комиссии в B5: обе показывают $1,818.18. Это равенство и есть условие, к которому пыталась прийти циклическая версия.
Часто задаваемые вопросы
Что такое циклическая ссылка в Excel?
Это формула, которой для вычисления нужен её собственный результат. Она может ссылаться на свою ячейку, как =СУММ(B2:B7) в B7, или добираться до неё через другие ячейки, как =B1+1 в A1 при =A1*2 в B1. Excel не может закончить вычисление, поэтому предупреждает вас и показывает 0 или последнее значение.
Как найти циклическую ссылку в Excel?
Посмотрите на строку состояния внизу окна: там написано Циклические ссылки и адрес ячейки. Или откройте Формулы > Проверка наличия ошибок (стрелка рядом с кнопкой) > Циклические ссылки: там перечислены ячейки, и щелчок по любой переводит к ней.
Почему Excel говорит о циклической ссылке, а я не могу её найти?
Строка состояния показывает циклическую ссылку только на активном листе, поэтому переключайтесь между листами и проверяйте Формулы > Проверка наличия ошибок > Циклические ссылки на каждом. Цикл может проходить и через определённое имя или ячейку на другом листе, поэтому от указанной ячейки идите по стрелкам Влияющие ячейки.
Нужно ли включать итеративные вычисления, чтобы исправить циклическую ссылку?
Только если цикл задуман, например в модели, которая сходится к значению. Файл > Параметры > Формулы > Включить итеративные вычисления заставляет Excel повторять вычисление до 100 раз вместо предупреждения. Для случайного цикла это прячет ошибку, и результат может оказаться неверным.
