Как найти циклические ссылки в Excel 4 способами

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

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

Когда кто-то изменяет значения ячеек ссылок на ячейки, рабочий лист автоматически обновляется. Это называется автоматизацией анализа данных, и аналитики данных любят играть со ссылками на ячейки.

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

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

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

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

Вы пытаетесь умножить 10 из сотового Б1 и поместите результат в ячейку Б1 сам.

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

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

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

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

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

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

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

Чтобы избежать каких-либо повреждений, вызванных циклическими ссылками в формулах Excel, вы должны знать, когда они появляются, а когда нет. Найдите ниже экземпляры:

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

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

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

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

• Предполагаемые циклические ссылки возникают, когда вы намеренно используете их для итерационных расчетов. Например, вам нужно ввести отметку времени в другую ячейку, когда кто-то вводит значение в ячейку, на которую указывает ссылка, используя изменчивые и логические функции, такие как ЕСЛИ, ИЛИ, СУММЕСЛИ и т. д.

• Непреднамеренная циклическая ссылка, когда вы пытаетесь работать с формулой Excel быстрее и ошибочно ссылаетесь на ячейку, содержащую формулу. Например, необходимо применить СУММА функция из клетки А1 к А9 и получить результат в ячейке А10. Но по ошибке вы вводите такую ​​формулу в ячейку A10:

=СУММ(A2:A10)

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

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

Теперь, когда вы поняли основы циклических ссылок в Excel, давайте узнаем, как найти циклические ссылки в Excel ниже:

При условии, что функция итеративного расчета отключена, вы можете увидеть следующие уведомления о циклических ссылках в строке состояния Excel:

• Циркулярные ссылки: C3 (или любую другую ссылку на ячейку), когда вы находитесь на листе, содержащем циклическую формулу в книге.

• Вы увидите только Циркулярные ссылки если вы находитесь на рабочем листе, на котором их нет, но в рабочей книге есть несколько таких круговых формул.

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

• Откройте рабочий лист, который, как вы подозреваете, содержит циклические формулы.
• Нажмите на Формулы меню на ленте Excel.

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

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

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

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

Этот метод особенно важен, когда на листе Excel активны функции итеративного расчета.

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

• Нажмите ХОРОШО чтобы сохранить изменения.
• Теперь рабочая книга или рабочий лист автоматически отобразит предупреждение о циклической ссылке.
• Если оно не отображается сразу, сохраните книгу, закройте ее и снова откройте, чтобы увидеть предупреждение.
• Как только вы увидите предупреждение, проверьте Строка состояния Excel чтобы узнать, какая ячейка находится в круговой ссылке.

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

Другой простой способ — удалить формулу из текущей ячейки и поместить ее в другое место на том же рабочем листе.

Как и Excel, Google Таблицы не допускают циклических ссылок в одной или нескольких формулах, если вы не активировали функцию итеративных вычислений. Следовательно, Google Таблицы сразу же отобразят #REF! ошибка в ячейке, содержащей циклическую формулу.

Таким образом, вы можете просто выделить все ячейки, содержащие #ССЫЛКА! предупреждение о поиске и разрешении циклических ссылок в Google Таблицах.

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

Далее, лучшие шаблоны календаря Excel на любой год и лучшие шаблоны бюджета Excel.