Как найти циклические ссылки в Excel

При работе с Microsoft Excel вы можете иногда сталкиваться с так называемой циклической ссылкой. Это когда формула в ячейке ссылается сама на себя, вызывая бесконечный цикл в процессе вычисления.

Чтобы найти циклические ссылки в Excel, используйте инструмент «Проверка ошибок» на вкладке «Формулы», чтобы определить ячейки с циклической зависимостью.

В этой статье показано, как найти циклические ссылки в Excel и исправить их. Вы также узнаете, как предотвратить их при работе с большими или сложными листами.

Оглавление

• Что такое циклические ссылки
• Окно сообщения об ошибке циклической ссылки
• Как найти циклические ссылки в Excel
• Как найти циклические ссылки с помощью строки состояния Excel
• Как найти скрытые циклические ссылки в Excel
  • Как использовать инструмент, зависящий от трассировки
  • Инструмент отслеживания прецедентов
• 2 способа удаления циклических ссылок в Excel
  • 2. Используйте альтернативные формулы
• 5 советов по предотвращению циклических ссылок
  • 1. Будьте осторожны при выборе диапазонов
  • 2. Тщательно планируйте свои формулы
  • 3. Используйте альтернативные формулы
  • 4. Разбейте сложные формулы на более мелкие части
  • 5. Решайте проблемы по ходу дела
• Как включить итеративный расчет и зачем?

Что такое циклические ссылки

Циклические ссылки возникают в Excel, когда вычисление формулы в ячейке зависит от собственного значения ячейки. Это создает петлю и приводит к неправильным или непредсказуемым результатам.

Это легче понять на простом примере.

Предположим, у вас есть два числа в ячейках A1 и B1. Вы хотите использовать формулу в ячейке A3, которая складывает два числа вместе. Формула в ячейке A3 должна выглядеть так:

=А1 + В1

Однако, если вы допустили ошибку и включили C1 в расчеты, теперь у вас есть циклическая ссылка:

=А1 + В1 + С1

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

К счастью, Excel предназначен для обработки таких ситуаций, отображая предупреждающее сообщение при обнаружении циклической ссылки.

Однако в некоторых случаях вы можете столкнуться с циклической ссылкой без получения предупреждения. Поэтому важно знать, как найти и решить эти проблемы вручную.

Окно сообщения об ошибке циклической ссылки

По умолчанию Excel отслеживает циклические ссылки при вводе формул в ячейки. Когда вы нажимаете клавишу ввода в ячейке с проблемой, во всплывающем окне отображается сообщение об ошибке циклической ссылки.

Это пояснительный текст:

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

Когда вы увидите это диалоговое окно, вы должны нажать «ОК» и использовать методы, описанные в этой статье, для устранения проблемы.

Однако вам не нужно сразу исправлять ошибку. Вы можете продолжить работу с электронной таблицей, чтобы решить проблему позже.

В этом случае вам нужно знать, как найти циклические ссылки в Excel после того, как вы закрыли окно с предупреждением.

Как найти циклические ссылки в Excel

В Excel есть встроенный инструмент проверки ошибок, который находит несколько типов ошибок, включая циклические ссылки. Вы получаете доступ к инструменту на вкладке Формулы.

На этом рисунке показано расположение в группе аудита формул.

Теперь вы знаете, как найти инструмент, вот подробные шаги по его использованию:

1. Перейдите на вкладку Формулы.

1. Щелкните раскрывающийся список «Проверка ошибок» в группе «Аудит формул».

2. Выберите «Циклические ссылки» из выпадающего списка.

3. Щелкните ссылку, чтобы переместить курсор на первую ячейку с проблемой.

На рисунке ниже показано, что инструмент обнаружил круговую ошибку в ячейке G1. Отображаемая ячейка является гиперссылкой.

При нажатии на ссылку курсор перемещается в ячейку. Это очень удобно при большом объеме данных.

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

Выполните действия, описанные в последующих разделах этой статьи, чтобы устранить проблему и использовать средство для перехода к следующей проблеме.

Если циклических проблем больше нет, пункт меню «Циклические ссылки» в раскрывающемся меню будет недоступен. У вас больше нет работы.

Как найти циклические ссылки с помощью строки состояния Excel

Еще один способ обнаружить циклическую ссылку — посмотреть на строку состояния в нижней части рабочего листа Excel.

Когда на листе есть одна или несколько проблем, в строке состояния отображается расположение одной ссылки на проблемную ячейку.

Например, если в ячейке C1 есть циклическая ссылка и нет других проблем с электронной таблицей, в строке состояния будет указано: «Циклические ссылки: C1».

На этом рисунке показано, где можно увидеть дисплей.

Как найти скрытые циклические ссылки в Excel

Скрытые циклические ссылки в Excel — это циклические ссылки, которые не обнаруживаются сразу встроенными функциями ошибок. Другими словами, Excel не отображает стандартное предупреждение.

Они происходят, когда ячейка косвенно ссылается на другую ячейку, через серию промежуточных ячеек, и в конечном итоге ссылается на исходную ячейку.

В Excel есть два инструмента, которые помогают отслеживать отношения и исследовать эти проблемы:

1. Инструмент «Отслеживание зависимостей»

2. Инструмент отслеживания прецедентов

Как использовать инструмент, зависящий от трассировки

Опытные пользователи Excel используют инструмент Trace Dependents для исследования этих проблем и исправления циклических ссылок. Выбрав одну или несколько ячеек и используя этот инструмент, Excel покажет вам зависимые ячейки.

Инструмент доступен в разделе «Аудит формул» на вкладке «Формулы».

Чтобы использовать инструмент, выполните следующие действия:

1. Щелкните ячейку или ячейки, для которых вы хотите отследить иждивенцев.

2. Перейдите на вкладку «Формулы» на ленте Excel.

3. Нажмите кнопку «Отслеживание зависимостей» в группе «Аудит формул».

4. Просмотрите стрелку трассировщика или стрелки, показывающие зависимости.

Excel показывает стрелки от активного значения ячейки к любым ячейкам, которые зависят от него. Синяя стрелка на рисунке ниже показывает, что ячейка D6 зависит от ячейки B2.

Инструмент отслеживания прецедентов

Прецеденты противоположны иждивенцам. Инструмент «Отслеживание прецедентов» позволяет выбрать ячейку с формулой и просмотреть все ячейки, от которых она зависит, т. е. прецеденты.

Инструмент доступен в разделе «Аудит формул» на вкладке «Формулы».

Чтобы использовать инструмент, выполните следующие действия:

1. Нажмите на ячейку, содержащую формулу, которую вы хотите исследовать.

2. Перейдите на вкладку Формула на ленте Excel.

3. Нажмите кнопку «Отследить прецеденты» в группе «Аудит формул».

4. Просмотрите стрелки трассировки, отображаемые в Excel.

Синие стрелки на рисунке ниже показывают, что ячейка D6 зависит от диапазона ячеек A2:B5.

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

2 способа удаления циклических ссылок в Excel

Существует два основных метода разрешения циклических ссылок:

• нарушение циклической ссылки

• используя альтернативные формулы

Давайте объясним оба способа.

1. Нарушение круговой ссылки

Разрыв циклической ссылки в Excel — эффективный способ решения проблемы. Это требует, чтобы вы определили ячейки, участвующие в циклической ссылке, и соответствующим образом изменили их формулы.

Для этого выполните шаги, описанные ранее, чтобы найти адрес ячейки.

Затем вы должны изучить формулу в строке формул. Найдите ссылку на саму ячейку.

В нашем первом примере циклической ссылки формула сложения в ячейке C1 включала ссылку на саму ячейку:

=А1 + В1 + С1

Это можно исправить, удалив последнее дополнение. Как только эта проблема решена, вы переходите к поиску других циклических ссылок и их устранению.

2. Используйте альтернативные формулы

Некоторые из наиболее распространенных причин циклических ссылок связаны с суммированием или усреднением ячеек. Ошибка заключается в включении ячейки результатов в формулу.

В нашем предыдущем примере формула использовала операторы сложения для вычисления суммы диапазона ячеек. Альтернативный метод заключается в использовании функции СУММ Excel.

Чтобы исправить пример, вы замените формулу на:

=СУММ(A1:B1)

Точно так же вы можете усреднить диапазон с помощью формулы =(A1+B1)/2. Однако функция СРЗНАЧ с меньшей вероятностью приведет к ошибке.

=СРЕДНЕЕ(A1:B1)

5 советов по предотвращению циклических ссылок

Чтобы предотвратить циклические ссылки в Microsoft Excel, важно иметь четкое представление о том, как ваши формулы взаимодействуют друг с другом на листе Excel.

Определив отношения между ячейками и зная, как эти связи работают вместе, вы можете избежать создания циклических формул.

Вот наши пять лучших советов, которые помогут вам предотвратить циклические ссылки.

1. Будьте осторожны при выборе диапазонов

По моему опыту, одной из наиболее распространенных причин циклических ссылок является то, что пользователи быстро выбирают диапазоны для включения в свои формулы.

Прокручивая вертикально и/или горизонтально для группировки ячеек, легко выбрать еще одну ячейку, которая находится рядом с правильным диапазоном. Если эта ячейка также является ячейкой результатов, вы случайно создали циклическую ссылку.

Эта ошибка обычно совершается при работе с большими наборами данных, например при выполнении тестов хи-квадрат. Решение состоит в том, чтобы быть осторожным с выбором диапазона.

2. Тщательно планируйте свои формулы

Прежде чем вводить формулы, подумайте о логике ваших вычислений и о том, как они должны проходить по ячейкам.

Вы можете наметить расчеты заранее, используя инструменты построения диаграмм или доску.

Это может помочь вам определить любые возможные циклы, которые могут привести к циклическим ссылкам.

3. Используйте альтернативные формулы

Некоторые функции, такие как INDIRECT или OFFSET , могут непреднамеренно создавать циклические ссылки при неправильном использовании.

Рассмотрите возможность использования альтернативных функций, которые обеспечивают аналогичную функциональность без риска циклических ссылок.

4. Разбейте сложные формулы на более мелкие части

Вместо того, чтобы создавать единую сложную формулу с несколькими вложенными функциями, рассмотрите возможность разделения вычислений на более мелкие и более управляемые части.

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

5. Решайте проблемы по ходу дела

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

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

Это упрощает их исправление и предотвращает их влияние на вашу книгу.

Как включить итеративный расчет и зачем?

Обычно вы хотите избежать циклических ссылок на листе. Однако есть ситуации, когда они необходимы.

Например, для некоторых инженерных или научных расчетов необходимо использовать результат предыдущего расчета. Это известно как итеративное решение.

Каждый цикл уточняет результаты вычислений, пока не будет достигнут определенный порог или цель.

В этих случаях важно включить итерационные вычисления в настройках Excel, чтобы разрешить предполагаемые циклические ссылки. Это позволяет программному обеспечению повторять вычисления заданное количество раз, сходясь к решению.

Выполните следующие действия, чтобы циклическая ссылка работала в вашем файле Excel:

1. Перейдите на вкладку «Файл».

2. Выберите Параметры.

3. Выберите категорию Формулы.

4. В разделе «Параметры расчета» установите флажок «Включить итеративный расчет».

5. Измените число в поле максимального количества итераций или оставьте значение по умолчанию, равное 100.

6. Используйте поле максимального изменения, чтобы уменьшить количество итераций в соответствии с вашими требованиями.