как найти, включить, использовать или удалить
В этом кратком руководстве объясняются основы циклических ссылок Excel и причины, по которым вам следует остерегаться их использования. Вы также узнаете, как проверять, находить и удалять циклические ссылки на листах Excel, а если ничего из вышеперечисленного не подходит, как включать и использовать циклические формулы.
Вы пытались ввести какую-то формулу на листе Excel, но по какой-то причине она не работает. Вместо этого он говорит вам что-то о циклическая ссылка. Вы так оказались на этой странице? 🙂
Тысячи пользователей ежедневно сталкиваются с одной и той же проблемой просто из-за того, что формуле Excel приходится вычислять собственную ячейку. Когда вы пытаетесь это сделать, Excel выдает следующее сообщение об ошибке:
«Осторожно, мы обнаружили в вашей книге одну или несколько циклических ссылок, которые могут привести к неправильному расчету формулы».
Проще говоря, Excel пытается сказать следующее: «Эй, я могу застрять на перекрестке с круговым движением. Вы уверены, что хотите, чтобы я все равно продолжил?»
Как вы понимаете, циклические ссылки в Excel неприятны, и здравый смысл подсказывает, что по возможности их следует избегать. Однако могут быть редкие случаи, когда циклическая ссылка Excel является единственным возможным решением поставленной задачи.
Что такое циклическая ссылка в Excel?
Вот очень прямое и краткое определение циклической ссылки, предоставленное Microsoft:
“Когда формула Excel прямо или косвенно ссылается на собственную ячейку, она создает циклическую ссылку.“
Например, если вы выберете ячейку A1 и введете в нее =A1, это создаст циклическую ссылку Excel. Ввод любой другой формулы или вычисления, относящегося к A1, будет иметь тот же эффект, например, =A1*5 или =IF(A1=1, “ОК”).
Как только вы нажмете Enter, чтобы завершить такую формулу, вы получите следующее предупреждающее сообщение:
Почему Microsoft Excel предупреждает вас? Поскольку циклические ссылки Excel могут бесконечно повторяться, создавая бесконечный цикл, что значительно замедляет вычисления в книге.
Получив указанное выше предупреждение, вы можете нажать Помощь для получения дополнительной информации или закройте окно сообщения, нажав OK или кнопку с крестиком. Когда вы закрываете окно сообщения, 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=””, СЕЙЧАС(), C2), “”)
Где B2 — это статус доставки, а C2 — это ячейка, в которой вы хотите отобразить отметку времени.
В приведенной выше формуле первая функция ЕСЛИ проверяет ячейку B2 на наличие “Да” (или любой другой текст, который вы вводите в формулу), и если указанный текст есть, он запускает второй ЕСЛИ, в противном случае возвращает пустую строку. И вторая функция ЕСЛИ представляет собой циклическую формулу, которая выбирает текущий день и время, если C2 еще не имеет значения, поэтому сохраняются все существующие метки времени.
Примечание. Чтобы эта круговая формула Excel работала, вы должны разрешить итерационные вычисления на своем листе, и это именно то, что мы собираемся обсудить далее.
Как включить/отключить циклические ссылки в Excel
Как отмечалось ранее, итерационные вычисления обычно отключены в Excel по умолчанию (в данном контексте итерация — это повторяющийся пересчет до тех пор, пока не будет выполнено определенное числовое условие). Чтобы круговые формулы работали, необходимо включить итерационные вычисления в книге Excel.
В Excel 2019, Excel 2016, Excel 2013 и Excel 2010 щелкните Файл > Опцииперейти к Формулыи выберите Включить итеративный расчет флажок под Варианты расчета раздел.
В Excel 2007 щелкните Кнопка офиса > Параметры Excel > Формулы > Область итерации.
В Excel 2003 и более ранних версиях параметр «Итеративный расчет» находится в разделе Меню > Инструменты > Опции > Расчет вкладка
При включении итерационных вычислений необходимо указать следующие два параметра:
- Поле «Максимум итераций» — указывает, сколько раз формула должна пересчитываться. Чем больше число итераций, тем больше времени занимает расчет.
- Поле «Максимальное изменение» — указывает максимальное изменение между результатами расчета. Чем меньше число, тем более точный результат вы получите и тем больше времени потребуется Excel для расчета рабочего листа.
Настройки по умолчанию: 100 для максимального количества итераций и 0,001 для максимального изменения. Это означает, что Microsoft Excel прекратит вычисление вашей круговой формулы после 100 итераций или после изменения менее 0,001 между итерациями, в зависимости от того, что наступит раньше.
Почему вам следует избегать использования циклических ссылок в Excel
Как вы уже знаете, использование циклических ссылок в Excel — скользкий и не рекомендуемый подход. Помимо проблем с производительностью и предупреждающего сообщения, отображаемого при каждом открытии книги (если не включены итерационные вычисления), циклические ссылки могут привести к ряду других проблем, которые не сразу бросаются в глаза.
Например, если вы выберете ячейку с циклической ссылкой, а затем случайно переключитесь в режим редактирования формулы (либо нажав F2, либо дважды щелкнув ячейку), а затем нажмете Enter, не внося никаких изменений в формулу, она вернуть ноль.
Итак, вот совет от многих уважаемых гуру Excel: по возможности старайтесь избегать циклических ссылок в своих таблицах.
Как найти циклические ссылки в Excel
Чтобы проверить книгу Excel на наличие циклических ссылок, выполните следующие действия:
- Перейти к Формулы нажмите на стрелку рядом с Проверка ошибоки укажите на Циклические ссылки Здесь отображается последняя введенная циклическая ссылка.
- Щелкните ячейку, указанную в разделе «Циклические ссылки», и Excel приведет вас именно к этой ячейке.
Как только вы это сделаете, строка состояния уведомит вас о том, что в вашей книге найдены циклические ссылки, и отобразит адрес одной из этих ячеек:
Если циклические ссылки обнаружены на других листах, в строке состояния отображается только “Циркулярные ссылки” без адреса сотового.
Примечание. Эта функция отключена, когда Вариант итеративного расчета включен, поэтому вам нужно отключить его, прежде чем вы начнете проверять книгу на наличие циклических ссылок.
Как удалить циклические ссылки в Excel
К сожалению, в Excel нет механизма, который позволил бы вам удалить все круговые формулы в книге одним нажатием кнопки. Чтобы избавиться от них, вам придется проверять каждую циклическую ссылку по отдельности, выполнив шаги вышеа затем либо полностью удалите заданную циклическую формулу, либо замените ее одной или несколькими простыми формулами.
Как проследить отношения между формулами и ячейками
В случаях, когда циклическая ссылка Excel не очевидна, Отследить прецеденты а также Отследить иждивенцев функции могут дать вам подсказку, нарисовав одну или несколько линий, которые показывают, какие ячейки влияют или на которые влияет выбранная ячейка.
Чтобы отобразить стрелки трассировки, перейдите к Формулы вкладка > Аудит формул группу и выберите один из вариантов:
Отслеживание прецедентов — отслеживает ячейки, которые предоставляют данные для формулы, т. е. рисует линии, указывающие, какие ячейки влияют на выбранную ячейку.
Trace Dependents (Отслеживание зависимостей) — отслеживает ячейки, которые зависят от активной ячейки, т. е. рисует линии, указывающие, на какие ячейки влияет выбранная ячейка. Другими словами, он показывает, какие ячейки содержат формулы, ссылающиеся на выбранную ячейку.
Кроме того, вы можете использовать следующие сочетания клавиш:
- Отследить прецеденты: Alt+TUT
- Зависимые от трассировки: Alt+TUD
Чтобы скрыть стрелки, нажмите кнопку «Удалить стрелки», которая находится прямо под Отследить иждивенцев.
В приведенном выше примере Отследить прецеденты стрелка показывает, какие ячейки непосредственно передают данные в B6. Как видите, ячейка B6 также включена, что делает ее циклической ссылкой и приводит к тому, что формула возвращает ноль. Конечно, это легко исправить, просто замените B6 на B5 в аргументе SUM: =SUM(B2:B5)
Другие циклические ссылки могут быть не такими очевидными и требуют дополнительных размышлений и расчетов.
Вот как вы работаете с циклическими ссылками Excel. Надеемся, что это краткое руководство пролило свет на это «слепое пятно», и теперь вы можете продолжить исследование, чтобы узнать больше. Я благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!
