Одно из наиболее частых предупреждений об ошибках, с которыми пользователи сталкиваются в Excel, - это «Циркулярная ссылка». Тысячи пользователей сталкиваются с той же проблемой, и она возникает, когда формула прямо или косвенно ссылается на свою собственную ячейку, вызывая бесконечный цикл вычислений.
Например, у вас есть два значения в ячейках B1 и B2. Когда формула = B1 + B2 вводится в B2, создается круговая ссылка; формула в B2 многократно пересчитывает себя, потому что каждый раз при вычислении значение B2 изменялось.
Большинство циклических ссылок являются непреднамеренными ошибками; Excel предупредит вас об этом. Однако существуют также предполагаемые циклические ссылки, которые используются для выполнения итерационных вычислений. Непреднамеренные циклические ссылки на вашем листе могут привести к неправильному вычислению формулы.
Поэтому в этой статье мы объясним все, что вам нужно знать о циклических ссылках, а также о том, как найти, исправить, удалить и использовать циклические ссылки в Excel.
Как найти и обработать круговую ссылку в Excel
При работе с Excel мы иногда сталкиваемся с ошибками циклических ссылок, которые возникают, когда вы вводите формулу, которая включает ячейку, в которой находится ваша формула. Обычно это происходит, когда ваша формула пытается вычислить сама себя.
Например, у вас есть столбец чисел в ячейке A1: A4, и вы используете функцию СУММ (= СУММ (A1: A5)) в ячейке A5. Ячейка A5 напрямую относится к своей собственной ячейке, что неверно. Следовательно, вы получите следующее предупреждение о циклической ссылке:
Получив указанное выше предупреждающее сообщение, вы можете нажать кнопку «Справка», чтобы узнать больше об ошибке, или закрыть окно с сообщением об ошибке, нажав кнопку «ОК» или «X», получив в результате «0».
Иногда циклы круговых ссылок могут привести к сбою в вычислениях или снижению производительности рабочего листа. Циркулярная ссылка может также привести к ряду других проблем, которые не сразу станут очевидны. Так что лучше избегать этого.
Прямые и косвенные циркулярные ссылки
Циркулярные ссылки можно разделить на два типа: Прямые Циркулярные ссылки и Косвенные Циркулярные ссылки.
Прямая ссылка
Прямая круговая ссылка довольно проста. Предупреждающее сообщение о прямой циклической ссылке появляется, когда формула обращается напрямую к собственной ячейке.
В приведенном ниже примере формула в ячейке A2 напрямую ссылается на ее собственную ячейку (A2).
Когда появится предупреждающее сообщение, вы можете нажать «ОК», но результатом будет только «0».
Косвенная круговая ссылка
Косвенная циклическая ссылка в Excel возникает, когда значение в формуле ссылается на собственную ячейку, но не напрямую. Другими словами, круговая ссылка может быть сформирована двумя ячейками, ссылающимися друг на друга.
Давайте объясним на этом простом примере.
Теперь значение начинается с A1, имеющего значение 20.
Затем ячейка C3 относится к ячейке A1.
Тогда ячейка A5 относится к ячейке C3.
Теперь замените значение 20 в ячейке A1 формулой, как показано ниже. Все остальные ячейки зависят от ячейки A1. Когда вы используете ссылку на любую другую предыдущую ячейку формулы в A1, это вызовет предупреждение о циклической ссылке. Поскольку формула в A1 ссылается на ячейку A5, которая ссылается на C3, а ячейка C3 ссылается на A1, следовательно, на круговую ссылку.
Когда вы нажимаете «ОК», в ячейке A1 появляется значение 0, и Excel создает связанную строку, показывающую прецеденты трассировки и зависимые элементы трассировки, как показано ниже. Мы можем использовать эту функцию, чтобы легко находить и исправлять / удалять циклические ссылки.
Как включить / отключить циклические ссылки в Excel
По умолчанию итерационные вычисления отключены (отключены) в Excel. Итерационные вычисления - это повторяющиеся вычисления до тех пор, пока не будет выполнено определенное условие. Когда он отключен, Excel показывает сообщение о круговой ссылке и возвращает 0 в качестве результата.
Однако иногда для вычисления цикла необходимы циклические ссылки. Чтобы использовать циклическую ссылку, вы должны включить итерационные вычисления в вашем Excel, и это позволит вам выполнять свои вычисления. Теперь позвольте нам показать вам, как можно включить или отключить итерационные вычисления.
В Excel 2010, Excel 2013, Excel 2016, Excel 2019 и Microsoft 365 перейдите на вкладку «Файл» в верхнем левом углу Excel, затем нажмите «Параметры» на левой панели.
В окне параметров Excel перейдите на вкладку «Формула» и установите флажок «Включить итеративный расчет» в разделе «Параметры расчета». Затем нажмите «ОК», чтобы сохранить изменения.
Это обеспечит итеративный расчет и, таким образом, обеспечит круговую ссылку.
Чтобы добиться этого в предыдущих версиях Excel, выполните следующие действия:
- В Excel 2007 нажмите кнопку «Офис»> «Параметры Excel»> «Формулы»> «Итерация».
- В Excel 2003 и более ранних версиях вам нужно перейти в Меню> Инструменты> Параметры> вкладка Расчет.
Максимальное количество итераций и максимальное изменение параметров
После включения итерационных вычислений вы можете управлять итерационными вычислениями, указав две опции, доступные в разделе «Включить итерационные вычисления», как показано на снимке экрана ниже.
- Максимальное количество итераций - Это число указывает, сколько раз формула должна быть пересчитана, прежде чем вы получите окончательный результат. Значение по умолчанию - 100. Если вы измените его на «50», Excel повторит вычисления 50 раз, прежде чем выдаст вам окончательный результат. Помните, что чем больше количество итераций, тем больше ресурсов и времени уходит на вычисления.
- Максимальное изменение - определяет максимальное изменение результатов расчета. Это значение определяет точность результата. Чем меньше число, тем точнее будет результат и тем больше времени потребуется для расчета рабочего листа.
Если включен параметр итеративных вычислений, вы не будете получать никаких предупреждений, если на вашем листе есть циклическая ссылка. Включайте интерактивный расчет только тогда, когда это абсолютно необходимо.
Найти циркулярную ссылку в Excel
Предположим, у вас есть большой набор данных и вы получили предупреждение о циклической ссылке, вам все равно нужно будет выяснить, где (в какой ячейке) произошла ошибка, чтобы исправить ее. Чтобы найти циклические ссылки в Excel, выполните следующие действия:
Использование инструмента проверки ошибок
Сначала откройте рабочий лист, на котором есть циклическая ссылка. Перейдите на вкладку «Формула», нажмите стрелку рядом с инструментом «Проверка ошибок». Затем просто наведите курсор на опцию «Циклические ссылки», и Excel покажет вам список всех ячеек, участвующих в циклической ссылке, как показано ниже.
Щелкните любой адрес ячейки в списке, и вы перейдете к адресу этой ячейки, чтобы решить проблему.
Использование строки состояния
Вы также можете найти круговую ссылку в строке состояния. В строке состояния Excel будет показан последний адрес ячейки с циклической ссылкой, например «Циклические ссылки: B6» (см. Снимок экрана ниже).
При работе с циклической ссылкой вы должны знать следующее:
- В строке состояния не будет отображаться адрес ячейки циклической ссылки, когда включен параметр Итеративное вычисление, поэтому вам необходимо отключить его, прежде чем вы начнете искать в книге циклические ссылки.
- Если циклическая ссылка не найдена на активном листе, в строке состояния отображается только «Циклические ссылки» без адреса ячейки.
- Вы получите подсказку с циклической справкой только один раз, и после того, как вы нажмете «ОК», она больше не будет показывать подсказку в следующий раз.
- Если в вашей книге есть циклические ссылки, она будет отображать подсказку каждый раз, когда вы ее открываете, пока вы не разрешите циклическую ссылку или пока вы не включите итерационные вычисления.
Удалить круговую ссылку в Excel
Найти круговые ссылки легко, но исправить это не так-то просто. К сожалению, в Excel нет опции, позволяющей удалить все циклические ссылки сразу.
Чтобы исправить циклические ссылки, вы должны найти каждую циклическую ссылку по отдельности и попытаться изменить ее, полностью удалить циклическую формулу или заменить ее другой.
Иногда в простых формулах все, что вам нужно сделать, - это изменить параметры формулы, чтобы она не ссылалась на себя. Например, измените формулу в B6 на = СУММ (B1: B5) * A5 (изменив B6 на B5).
Результатом вычисления будет «756».
В случаях, когда трудно найти циклическую ссылку Excel, вы можете использовать функции «Отслеживать прецеденты» и «Отслеживать зависимые», чтобы отследить ее до источника и решить ее один за другим. Стрелка показывает, на какие ячейки действует активная ячейка.
Есть два метода трассировки, которые могут помочь вам удалить циклические ссылки, показывая отношения между формулами и ячейками.
Чтобы получить доступ к методам трассировки, перейдите на вкладку «Формулы», затем нажмите «Трассировать прецеденты» или «Трассировать зависимые» в группе аудита формул.
Прецеденты трассировки
Когда вы выбираете этот параметр, он отслеживает ячейки, которые влияют на значение активной ячейки. Он рисует синюю линию, указывающую, какие ячейки влияют на текущую ячейку. Сочетание клавиш для использования прецедентов трассировки: Alt + T U T.
В приведенном ниже примере синяя стрелка показывает, что на значение B6 влияют ячейки B1: B6 и A5. Как вы можете видеть ниже, ячейка B6 также является частью формулы, что делает ее циклической ссылкой и приводит к тому, что формула возвращает «0» в качестве результата.
Это легко исправить, заменив B6 на B5 в аргументе SUM: = SUM (B1: B5).
Следить за зависимыми
Функция трассировки зависимых отслеживает ячейки, которые зависят от выбранной ячейки. Эта функция рисует синюю линию, показывающую, какие ячейки затронуты выбранной ячейкой. Другими словами, он отображает, какие ячейки содержат формулы, которые ссылаются на активную ячейку. Сочетание клавиш для использования иждивенцев: Alt + T U D.
В следующем примере на ячейку D3 влияет B4. Его ценность зависит от B4 для получения результатов. Следовательно, функция зависимого от трассы рисует синюю линию от B4 до D3, указывая на то, что D3 зависит от B4.
Умышленное использование круговых ссылок в Excel
Умышленное использование круговых ссылок не рекомендуется, но в некоторых редких случаях может потребоваться циклическая ссылка, чтобы вы могли получить желаемый результат.
Поясним это на примере.
Для начала включите «Итеративный расчет» в своей книге Excel. После того, как вы включили итеративное вычисление, вы можете начать использовать циклические ссылки в своих интересах.
Предположим, вы покупаете дом и хотите дать своему агенту комиссию в размере 2% от общей стоимости дома. Общая стоимость будет рассчитана в ячейке B6, а процент комиссии (агентское вознаграждение) - в ячейке B4. Комиссия рассчитывается от общей стоимости и включает в себя комиссию. Поскольку ячейки B4 и B6 зависят друг от друга, создается круговая ссылка.
Введите формулу для расчета общей стоимости в ячейку B6:
= СУММ (B1: B4)
Поскольку общая стоимость включает агентское вознаграждение, мы включили B4 в приведенную выше формулу.
Чтобы рассчитать агентское вознаграждение в размере 2%, вставьте эту формулу в B4:
= B6 * 2%Теперь формула в ячейке B4 зависит от значения B6 для расчета 2% от общей комиссии, а формула в B6 зависит от B4 для расчета общей стоимости (включая комиссию агента), отсюда и круговая ссылка.
Если включен итеративный расчет, Excel не выдаст предупреждение или 0 в результате. Вместо этого результат ячеек B6 и B4 будет вычисляться, как показано выше.
Опция итерационных вычислений по умолчанию обычно отключена. Если вы не включали его, и когда вы вводите формулу в B4, будет создана круговая ссылка. Excel выдаст предупреждение, и когда вы нажмете «ОК», отобразится стрелка-индикатор.
Вот и все. Это все, что вам нужно знать о циклических ссылках в Excel.