Циклическая ссылка в программе Microsoft excel это

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

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

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

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

Нахождение циклических ссылок

Смотрите также: “Как построить график в Excel”

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

Циклическая ссылка в Excel

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

Метод 1. Визуальный поиск циклической ссылки

как найти, включить, использовать или удалить

В этом кратком руководстве объясняются основы циклических ссылок 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 могут бесконечно повторяться, создавая бесконечный цикл, что значительно замедляет вычисления в книге.

Получив указанное выше предупреждение, вы можете нажать Помощь для получения дополнительной информации или закройте окно сообщения, нажав 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 на наличие циклических ссылок, выполните следующие действия:

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

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

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

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

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

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

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

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

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

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

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

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

• Отследить прецеденты: Alt+TUT
• Зависимые от трассировки: Alt+TUD

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

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

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

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

Источник: snaptik.ru

Циклическая ссылка в Excel

Циклическая ссылка в Excel относится к той же ячейке, над которой мы работаем. Циклическая ссылка — это тип всплывающего окна или предупреждения, отображаемого Excel, о том, что мы используем циклическую ссылку в нашей формуле, и расчет может быть неверным. Например, в ячейке A1, если мы напишем формулу =A1*2, это будет циклическая ссылка, так как внутри ячейки A1 мы использовали ссылку на ячейку самой A1.

Оглавление

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

• Циклический справочник Excel

• Объяснение
• Как включить/отключить циклические ссылки в Excel?
• Как использовать циклическую ссылку в Excel?
• Как найти циклические ссылки в Excel?
• Почему циклическая ссылка не рекомендуется?
• Результаты
• Очки, чтобы помнить
• Рекомендуемые статьи

Объяснение

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

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

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

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

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

1. Во-первых, мы должны перейти на вкладку «Файл».
2. Затем мы должны увидеть последний щелчок «Параметры».
3. Далее мы должны перейти в «Формулы», посмотреть «Параметры расчета» вверху и включить «Итеративный расчет». Затем нажмите «ОК».
4. После включения параметра «Итеративный расчет» на вкладке «Формулы» проверьте и укажите параметры, как указано ниже:Максимальное количество итераций: Он указывает, как часто уравнение должно пересчитываться. Чем больше число акцентов, тем больше времени занимает вычисление. Максимальное изменение: Он определяет наиболее резкое изменение между результатами расчета. Чем меньше число, тем точнее результат, который мы получаем, и тем больше времени требуется Excel для проверки рабочего листа. Мы можем изменить максимальную итерацию и максимальное изменение по мере необходимости. Все зависит от вас, чтобы сделать его индивидуальным.

Как использовать циклическую ссылку в Excel?

Ниже приведены приведенные данные.

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

• Шаг 2: Ячейка, содержащая общее значение, вернулась к 41 300 вместо 413, поскольку мы включили итеративный расчет.

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

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

• Затем мы должны нажать на ячейку, показанную в разделе «Циклические ссылки». Опять же, Excel перенесет вас именно в эту ячейку.

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

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

Почему циклическая ссылка не рекомендуется?

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