Как найти и использовать циклические ссылки в Excel
8 апреля 2022 г.
Многие специалисты используют электронные таблицы Excel для хранения данных и выполнения расчетов. Когда вы используете формулы Excel для наборов данных, вы можете получать сообщения, указывающие на наличие ошибок, таких как циклические ссылки. Если вы используете формулы Excel для вычисления сумм, средних значений или других показателей для наборов числовых значений, изучение циклических ссылок может помочь вам исправить ошибки и использовать функции Excel в полной мере.
В этой статье мы объясним, что такое циклические ссылки в Excel, какие типы циклических ссылок вы можете найти в своих наборах данных, как обнаружить и исправить ошибки циклических ссылок и как разрешить циклические ссылки для итерационных вычислений.
Что такое циклические ссылки в Excel?
Циклическая ссылка — это ситуация на листе Excel, когда формула, примененная к ячейке, ссылается на значение ячейки. Например, вы можете ввести значения в ячейки B1, B2 и B3. Вы можете щелкнуть ячейку B4 и ввести формулу =СУММ(B1:B4), надеясь получить сумму значений в столбце. Поскольку формула включает ячейку, к которой она применяется, она становится циклической ссылкой. Как правило, Excel помечает эти ошибки, а не вычисляет их, потому что циклический характер функции означает, что значение постоянно изменяется. Выполнение постоянных вычислений может замедлить работу вашего компьютера.
Типы циклических ссылок в Excel
Вот три основных типа циклических ссылок в Excel:
Непреднамеренные циклические ссылки в Excel
Большинство циклических ссылок являются непреднамеренными. Пользователи Excel могут случайно включить ячейку результатов, когда пишут уравнения для нахождения суммы набора чисел или других показателей. Разрешение этих циклических ссылок может позволить пользователю успешно завершить расчет. Инструменты формул Excel могут легко обнаруживать большинство непреднамеренных циклических ссылок.
Скрытые циклические ссылки в Excel
Скрытая циклическая ссылка в электронной таблице Excel возникает, когда условное действие вызывает циклическую ссылку. Например, если вы добавите в ячейку условное уравнение с помощью формулы ЕСЛИ, его значение может зависеть от значения в другой ячейке. Иногда эти формулы могут вызывать циклические ссылки, которые трудно обнаружить с помощью инструментов Excel, поскольку значения ячеек могут измениться. В этих случаях вы можете увидеть красную метку в ячейке, но Excel может не указать, что это циклическая ссылка.
Преднамеренные циклические ссылки в Excel
Хотя большинство циклических ссылок являются непреднамеренными, опытные пользователи Excel могут использовать циклические ссылки для итерационных вычислений. Эти формулы используют значения из предыдущих итераций или расчетных циклов для вычисления результатов. Поскольку эти вычисления замедляют скорость обработки компьютера, средства проверки ошибок Excel обычно помечают преднамеренные циклические ссылки как ошибки, если только пользователь не укажет, что уравнение правильное, и не укажет параметры, когда следует заканчивать вычисления.
Как найти циклические ссылки в Excel
Если вы создаете электронную таблицу с использованием формул без преднамеренных итерационных вычислений, вы можете использовать несколько методов для обнаружения и устранения циклических ссылок. Вот два основных способа найти эти вхождения:
Оповещения Excel
Во многих случаях фоновые средства проверки ошибок Excel предупреждают пользователя, когда программа обнаруживает циклическую ссылку. Вы можете увидеть текстовое поле, указывающее на ошибку, или красную метку в ячейке, где вы ввели формулу. Если после ввода формулы в ячейку вы получаете предупреждение о циклической ссылке, вы можете выполнить следующие действия, чтобы исправить формулу:
Нажмите на ячейку, в которую вы ввели формулу.
Выберите вкладку «Формула», чтобы изменить формулу.
Проверьте формулу, чтобы увидеть, включает ли она выделенную ячейку.
Если она включает выделенную ячейку, перепишите формулу, исключив эту ячейку.
Нажмите «Ввод», чтобы применить изменения к ячейкам в формуле.
Например, вы можете создать набор значений от B1 до B6. Если вы введете формулу =SU**M(B1:B7) в ячейку B7, это может создать циклическую ссылку и привести к тому, что Excel отправит вам предупреждение. Чтобы исправить формулу и получить сумму набора значений, вы можете открыть формулу и изменить ее на =СУММ(B1:B6).
Инструмент проверки ошибок Excel
Чтобы проверить всю электронную таблицу на наличие циклических ссылок, вы можете использовать инструмент обнаружения ошибок Excel. Вот как использовать эту функцию для выявления непреднамеренных циклических ссылок в Excel:
Нажмите на вкладку «Формула», чтобы отобразить меню.
Щелкните стрелку рядом с меткой «Проверка ошибок», чтобы открыть меню инструментов, в котором показаны параметры «Отслеживание ошибок» и «Циклические ссылки».
Нажмите «Циклические ссылки», чтобы открыть подменю, в котором отображаются все ячейки с предполагаемыми циклическими ссылками.
Нажмите на каждую ячейку, указанную в подменю, и проверьте формулу ячейки, чтобы определить, вызывает ли она циклическую ссылку.
Исправьте любую циклическую ссылку, которую вы найдете в ячейках.
Некоторые ячейки, перечисленные в подменю «Проверка ошибок», могут быть косвенными циклическими ссылками, что означает, что их значения являются результатом циклической ссылки в другой ячейке. Например, циклическая ссылка в сумме столбца может повлиять на результаты в других столбцах, использующих сумму для формул. Когда вы используете инструмент «Проверка ошибок», вы можете проверить несколько отмеченных ячеек, прежде чем найдете ту, которая отвечает за циклическую ссылку.
Когда использовать циклические ссылки в Excel
Вы можете использовать циклическую ссылку в Excel, если вы моделируете циклическое действие или процесс. Например, некоторые промышленные процессы используют продукт одного производственного цикла в качестве основы для следующего цикла. В этом случае результаты каждого цикла могут зависеть от результатов предыдущего. Если пользователь указывает, что циклическая ссылка верна, Excel позволяет повторить процесс 100 раз или до тех пор, пока разница между продуктами каждого цикла не упадет ниже 0,001. Пользователи также могут устанавливать собственное количество циклов и изменять значения.
Как разрешить циклическую ссылку в Excel
Проверка циклической ссылки в Excel позволяет программе выполнять итерационные вычисления, что может замедлить скорость обработки вашего компьютера, пока Excel завершает вычисления. Вот как разрешить циклическую ссылку в Excel:
Если вы используете ПК, выберите «Файл», а затем нажмите «Параметры», а затем «Формулы». Для пользователей Mac эта опция находится в меню Excel. Нажмите «Настройки», затем «Расчеты».
На ПК нажмите «Параметры расчета», затем выберите «Включить итерационные расчеты». Если вы используете Mac, выберите «Использовать итерационные вычисления». Это действие гарантирует, что Excel примет циклическую ссылку.
Максимальное количество итераций Excel по умолчанию составляет 100 вычислений. Чтобы изменить это значение, введите предпочтительное количество итераций в поле «Максимум итераций». Чем больше число, тем больше времени может занять расчет.
Минимальное изменение Excel по умолчанию составляет 0,001 за итерацию, после чего расчет останавливается. Чтобы установить другое значение, введите предпочтительное значение в поле «Минимальное изменение».
Сохранить изменения. Когда формула достигает либо максимального числа итераций, либо минимального изменения, вычисления останавливаются.
Обратите внимание, что ни одна из компаний, упомянутых в этой статье, не связана с компанией Indeed.