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

Обновлено: 21.11.2024

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

Читайте Excel 2013 по запросу или более 24 000 других книг и видео в Safari Books Online. Начните бесплатную пробную версию сегодня.

Эта глава из книги

Эта глава из книги 

Понимание формул
Создание и редактирование формул
Ссылки на ячейки
Использовать абсолютные ссылки на ячейки
Используйте смешанные и трехмерные ссылки на ячейки
Название ячеек и диапазонов
Ввод имен и управление ими
Упрощение формулы с помощью диапазонов
Отображение вычислений в строке состояния
Расчет итогов с помощью автосуммы
Выполнить однократные вычисления
Преобразование формул и значений
Исправить ошибки в расчетах
Правильные формулы
Аудит рабочего листа
Найти циклические ссылки
Выполнение вычислений с использованием функций
Создание функций и вложенных функций
Расчет нескольких результатов
Использование констант и функций в именах

Введение

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

Поскольку Excel автоматически пересчитывает формулы, ваши листы остаются точными и актуальными независимо от того, как часто вы меняете данные. Использование абсолютных ссылок на ячейки привязывает формулы к конкретной ячейке. Excel предоставляет множество встроенных функций для добавления к вычислениям на листе. Такие функции, как СРЗНАЧ или СУММ, позволяют выполнять быстрый расчет по формуле.

Еще один способ упростить понимание формул – использовать в них диапазоны имен. Диапазоны имен — группа выбранных ячеек, называемых диапазоном, — могут помочь вам понять ваши более сложные формулы. Гораздо проще прочитать формулу, в которой используются диапазоны имен, чем посмотреть на формулу и попытаться ее расшифровать. Excel предлагает инструмент для аудита вашего рабочего листа. Глядя на «поток» вашей формулы, вы значительно уменьшаете количество ошибок в расчетах. Вы можете увидеть, как строится ваша формула, по одному уровню за раз с помощью ряда стрелок, которые указывают, откуда формула извлекает данные. По мере разработки формулы вы можете вносить в нее исправления.

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

Вы пытались ввести какую-то формулу на листе Excel, но по какой-то причине она не работает. Вместо этого он говорит вам что-то о циклической ссылке. Вы так оказались на этой странице? :)

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

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

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

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

Что такое циклическая ссылка в Excel?

Вот очень прямое и краткое определение циклической ссылки, предоставленное Microsoft:

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

Например, если выбрать ячейку A1 и ввести в ней =A1, будет создана циклическая ссылка Excel. Ввод любой другой формулы или расчета, относящегося к A1, будет иметь тот же эффект, например.=A1*5 или =IF(A1=1, "ОК") .

Как только вы нажмете Enter, чтобы ввести такую формулу, вы получите следующее предупреждающее сообщение:

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

После получения приведенного выше предупреждения вы можете нажать Справка для получения дополнительной информации или закрыть окно сообщения, нажав кнопку "ОК" или кнопку с крестиком. Когда вы закрываете окно сообщения, Excel отображает либо ноль (0), либо последнее вычисленное значение в ячейке. Да, в некоторых случаях формула с циклической ссылкой может завершиться успешно до того, как попытается вычислить себя, и когда это произойдет, Microsoft Excel вернет значение из последнего успешного вычисления.

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

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

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

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

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

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

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

Использование циклической ссылки Excel — пример формулы

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

Предположим, что у вас есть список элементов в столбце A, и вы указываете статус доставки в столбце B. Как только вы наберете "Да" в столбце B, вам нужны текущие дата и время. для автоматической вставки в ту же строку столбца C в качестве статической неизменяемой метки времени.

Использование тривиальной формулы NOW() недопустимо, поскольку эта функция Excel является изменчивой, то есть она обновляет свое значение каждый раз, когда рабочие листы повторно открываются или пересчитываются. Возможным решением является использование вложенных функций ЕСЛИ с циклической ссылкой во втором ЕСЛИ:

Где B2 — это статус доставки, а C2 — ячейка, в которой вы хотите отобразить отметку времени.

В приведенной выше формуле первая функция ЕСЛИ проверяет ячейку B2 на наличие "Да" (или любого другого текста, который вы указываете в формуле), и, если указанный текст есть, она запускает вторую ЕСЛИ, иначе возвращает пустую строку.А вторая функция ЕСЛИ представляет собой циклическую формулу, которая извлекает текущий день и время, если в C2 еще нет значения, таким образом сохраняя все существующие метки времени.

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

Как включить/отключить циклические ссылки в Excel

Как отмечалось ранее, итерационные вычисления обычно отключены в Excel по умолчанию (в этом контексте итерация — это повторяющийся пересчет до тех пор, пока не будет выполнено определенное числовое условие). Чтобы циклические формулы работали, необходимо включить итерационные вычисления в книге Excel.

В Excel 2019, Excel 2016, Excel 2013 и Excel 2010 нажмите Файл > Параметры, перейдите в раздел Формулы и выберите Установите флажок Включить итеративный расчет в разделе Параметры расчета.

В Excel 2007 нажмите кнопку Office > Параметры Excel > Формулы > Область итерации.

В Excel 2003 и более ранних версиях параметр "Итеративный расчет" находится в разделе Меню > Инструменты > Параметры > Расчет. вкладка.

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

Поле "Максимум итераций" – указывает, сколько раз формула должна пересчитываться. Чем больше число итераций, тем больше времени занимает вычисление.
Поле «Максимальное изменение» — указывает максимальное изменение между результатами расчета. Чем меньше число, тем более точный результат вы получите и тем больше времени потребуется Excel для расчета рабочего листа.

Значения по умолчанию: 100 для максимального количества итераций и 0,001 для максимального изменения. Это означает, что Microsoft Excel перестанет вычислять вашу циклическую формулу после 100 итераций или после изменения менее 0,001 между итерациями, в зависимости от того, что наступит раньше.

Почему не следует использовать циклические ссылки в Excel

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

Например, если вы выберете ячейку с циклической ссылкой, а затем случайно переключитесь в режим редактирования формулы (нажав клавишу F2 или дважды щелкнув ячейку), а затем нажмете клавишу ВВОД, не внося никаких изменений в формулу , он вернет ноль.

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

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

Чтобы проверить книгу Excel на наличие циклических ссылок, выполните следующие действия:

Как только вы это сделаете, строка состояния уведомит вас о том, что в вашей книге найдены циклические ссылки, и отобразит адрес одной из этих ячеек:

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

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

Как удалить циклические ссылки в Excel

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

Как проследить связи между формулами и ячейками

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

Чтобы отобразить стрелки трассировки, перейдите на вкладку Формулы > группу Аудит формул и выберите один из вариантов:

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

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

В качестве альтернативы вы можете использовать следующие сочетания клавиш:

Отслеживание прецедентов: Alt+T U T
Зависимые от трассировки: Alt+T U D

Чтобы скрыть стрелки, нажмите кнопку «Удалить стрелки», которая находится прямо под Зависимые от трассировки.

В приведенном выше примере стрелка Отслеживать прецеденты показывает, какие ячейки непосредственно передают данные в B6. Как видите, ячейка B6 также включена, что делает ее циклической ссылкой и приводит к тому, что формула возвращает ноль. Конечно, это легко исправить, просто замените B6 на B5 в аргументе SUM: =SUM(B2:B5)

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

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

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

Это просто означает, что ваша формула включает ячейку, на которую распространяется формула. Ниже приведен простой пример: Циклический справочник в Excel 2006, Excel 2007, Excel 2010, Excel 2013, Excel 2016 и Excel 2019.

Циклическая ссылка Excel — это формула в ячейке Excel, которая прямо или косвенно ссылается на собственные ячейки. Итак, в этом учебнике по Excel мы увидим, как найти циклическую ссылку в Excel.

В приведенных выше ячейках видно, что формула вычисляет сумму в ячейке D15, но формула включает D15 в свой диапазон.

Что такое найти циклическую ссылку в Excel?

Предупреждение о циклической ссылке:

После создания циклической ссылки появляется окно с предупреждением, в котором можно либо обратиться за помощью для исправления формулы, либо нажать "ОК", чтобы принять ссылку. Если вы нажмете «ОК», в ячейке будет отображаться «-» или «0», как показано ниже.

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

Как найти циклическую ссылку на листе Excel:

Чтобы найти циклическую ссылку в Excel, выполните следующие действия

Нажмите в строке меню, перейдите на вкладку "Формулы", чтобы найти циклическую ссылку в Excel.
Выберите «Проверка ошибок» и укажите на Циклические ссылки, после чего откроется раскрывающийся список.
Нажмите «Циклические ссылки» в раскрывающемся списке, и Excel выдаст ячейку с циклическими ссылками.

Это найдет функцию Excel и удалит циклические ссылки.

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

Исправить предупреждение о циклической ссылке, включив итеративный расчет:

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

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

Использование циклической ссылки в Excel в ваших интересах:

Итак, вы, должно быть, думаете, что циклические ссылки бесполезны, и ваша цель — найти циклическую ссылку и исправить ее. Ты не прав!. Существуют формулы и уравнения, которые могут понадобиться вам для создания циклической ссылки. Позвольте мне привести вас через один пример. Кроме того, вы можете ознакомиться с нашими бесплатными онлайн-советами по Excel, PowerPoint и VBA.

Вы строите здание и решили платить своему застройщику комиссию в размере 2 % от общей стоимости здания. Просто… верно? Нет, если 2% включают в себя стоимость вознаграждения застройщика! Если вы хотите сделать это с помощью формулы, вы можете это сделать. Но представьте, что у вас есть сотни таких факторов и итераций. Просто выполните следующие простые действия: Введите формулу общей стоимости, которая включает ячейку C3.

Затем, в отношении комиссии, введите приведенную ниже формулу, которая создаст циклическую ссылку, и стрелка трассировщика покажет ее. Что касается окна предупреждения о круговой ссылке, просто нажмите «ОК».

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

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

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

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

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

Не знаю, сработает ли это во всех случаях этой ошибки, но в данном конкретном случае сработало.

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

Инструкция под "Zoek en zoek de cel in Circular Reference" была очень-очень-очень полезной!

MS-Help не дает этого решения.

Расположение ячейки в круговой ссылке было хорошо объяснено с пошаговыми инструкциями. Я исправил ошибку. большое спасибо

Спасибо! Простое и краткое объяснение, без написания тонны абзацев о том, почему поиск циклических ссылок важен :)

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

Спасибо. Я искал ошибку два часа и нашел ее за 10 минут с вашей помощью!

Здравствуйте, спасибо за инструкции, но нижняя запись (Циклические ссылки) в списке проверки ошибок неактивна. Не удалось сделать его доступным для выбора - никогда.

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

Выполнение команды calculate теперь занимает 5 минут, затем начинается невидимая проверка циклических ссылок — и еще 2–3 минуты. Затем вы получите одну циклическую ссылку, указанную в строке состояния.Изучив эту формулу и отследив ее, Excel говорит, что формула смещения является циклической ссылкой, но прямая ссылка на результирующий адрес допустима!

Теперь я потратил недели, пытаясь выследить эти привидения циклических ссылок, которые полностью перегружают мой компьютер (20 минут, чтобы выполнить команду "Сохранить как").

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

[i]Пока вы ищете, проверьте наличие косвенных ссылок. Они возникают, когда вы помещаете формулу в ячейку A1, и она использует другую формулу в ячейке B1, которая, в свою очередь, ссылается на ячейку A1. [u]Если вас это смущает, представьте, что это делает с Excel.[/u][/i]

Плохой Excel, плохие пользователи.

Если только у меня не получается решить проблемы с циклическими ссылками - то рад за вас всех . В противном случае, немного утешения для вас, продолжайте бороться - должен быть способ справиться с этим ухудшением управления ошибками в Excel 2013. Но использование Microsoft Office открыло еще один счет затрат для моей работы.

Читайте также: