ты хочешь понять Что такое циклическая ссылка в Excel? и как его найти и удалить? Значит, этот пост для вас! Если вы открыли электронную таблицу Excel и увидели сообщение об ошибке, что существует циклическая ссылка, то, вероятно, вы пришли к этому продукту, потому что не знаете, что это значит, не говоря уже о том, что с этим делать.
В этой статье мы объясним, что такое циклические ссылки в Excel и как их решить. Контрастировать!
В конце концов, что такое циклическая ссылка в Excel?
Циклическая ссылка возникает, когда формула Excel прямо или косвенно связана со своей ячейкой. Например, ячейка A связана с ячейкой B, а ячейка B связана с ячейкой A. Циклические ссылки обычно не только думают о двух ячейках, но и следуют огромной цепочке ячеек.
Используйте формулу циклической ссылки
Как правило, циклические ссылки не создаются отдельными лицами намеренно, то есть они, как правило, являются результатом ошибок в формулах. Однако в ряде случаев они точны. Давайте используем примерный случай на практике, чтобы вы могли понять.
Лекция 6: Основы вычислений
Предполагая, что у вас есть список пакетов в столбце A, и вы также вводите статус распространения в столбце B. В тот момент, когда вы набираете «Да» в столбце B, вы хотите, чтобы недавняя дата и время были введены автоматически точно в ту же строку в столбце. C как статическая метка времени.
Использование формулы NOW() может быть достойным вариантом, но эта функция Excel нестабильна, то есть она обновляет свое значение каждый раз, когда рабочие листы повторно открываются или пересчитываются. Жизнеспособным решением является использование функций SI вложенный циклической ссылкой во второй SE: =ЕСЛИ(B2=»Да»;ЕСЛИ(C2=»»;СЕЙЧАС();C2);»») Где B2 — это статус распределения, а C2 — это ячейка, в которой вы хотите отобразить отметку времени. В приведенной выше формуле первая функция ЕСЛИ проверяет ячейку B2 на наличие «Да» (или какой-либо другой элемент, который вы указываете в формуле), и если указанный элемент есть, он выполнит второй ЕСЛИ; в противном случае будет возвращена пустая строка. А вторая функция SE представляет собой круговую формулу, которая получает последний день и время, если C2 еще не имеет значения, сохраняя все без исключения счетчики времени, которые уже есть. Важно: Чтобы эта круговая формула Excel работала, вы должны принять итерационные расчеты в вашей электронной таблице, и это именно то, что мы сейчас обсудим.
Мы рекомендуем:
Google Фото получает 3D-фотографии и виджет для оценки домашних животных и друзей
Включить/выключить итерационные вычисления
Как отмечалось ранее, итерационные вычисления часто отключены в Excel по умолчанию (в этом контексте итерация повторяется до тех пор, пока не будет выполнено определенное числовое условие). Чтобы круговые формулы работали, вы должны включить итерационные вычисления в своей книге Excel. В 2016 первенствуйте, Excel 2013 y Excel 2010перейти на вкладку Целесообразный >opcionesir формулы и установите флажок Активировать итеративный расчет в разделе Варианты расчета. В Excel 2007Осуществить нажатие на кнопку семинар >параметры Excel >формулы >область итерации. В Excel 2003 и более ранних версиях параметр Итеративный расчет находится в меню >Инструменты >opciones > руководство расчет. При включении итерационные расчеты Необходимо указать следующие 2 параметра:
- Максимальное количество итераций: Подробно о том, сколько раз необходимо пересчитать формулу. Чем больше число итераций, тем больше времени займет вычисление.
- Максимальное количество изменений: детализирует максимальное изменение между результатами в расчете. Чем меньше число, тем точнее будет результат, и тем больше времени потребуется Excel для расчета расчетной бумаги.
Значение по умолчанию — 100 для максимального количества итераций и 0,001 для максимального изменения. Это означает, что Excel прекратит вычисление вашей циклической формулы после 100 итераций или изменения менее 0,001 между итерациями, в зависимости от того, что наступит раньше.
Циклические ссылки в EXCEL (как создать, найти, отключить, где применить, итеративные вычисления)
По какой причине обходят циклические ссылки?
Помимо проблем с производительностью и сообщения о наблюдении, отображаемого на каждой вкладке книги (если не включены итерационные вычисления), циклические ссылки могут вызвать множество других проблем, которые не сразу бросаются в глаза.
Например, если вы выберете ячейку с циклической ссылкой и случайно переключитесь в режим редактирования формулы (нажав F2 или дважды щелкнув ячейку) и нажав Enter, не внося никаких изменений в формулу, она вернет ноль.
Итак, совет от нескольких уважаемых лидеров Excel: по возможности старайтесь избегать циклических ссылок в своих электронных таблицах.
Как найти циклические ссылки?
Чтобы получить циклические ссылки в Excel, выполните следующие действия, чтобы определить и удалить их:
1. войти в руководство формулы.
2. В наборе «Аудит формулы” выберите вариант обзор ошибок > циклические ссылки. Если кнопка «Циклические ссылки» неактивна и вы не можете нажать на нее, значит, в вашей книге не будет циклических ссылок. С другой стороны, если он не выделен серым цветом, ваш файл будет иметь циклические ссылки.
Источник: www.tecnobreak.com
Циклические ссылки в excel
Среди пользователей Excel широко распространено мнение, что циклическая ссылка в excel является разновидностью ошибки, и от нее нужно непременно избавляться.
Между тем, именно циклические ссылки в excel способны облегчить нам решение некоторых практических экономических задач и финансовом моделировании.
Эта заметка как раз и будет призвана дать ответ на вопрос: а всегда ли циклические ссылки – это плохо? И как с ними правильно работать, чтобы максимально использовать их вычислительный потенциал.
Для начала разберемся, что такое циклические ссылки в excel 2010.
Циклические ссылки возникают, когда формула, в какой либо ячейке через посредство других ячеек ссылается сама на себя.
Например, ячейка С4 = Е 7, Е7 = С11, С11 = С4. В итоге, С4 ссылается на С4.
Наглядно это выглядит так:
На практике, зачастую, не такая примитивная связь, а более сложная, когда результаты вычисления одной формулы, порою весьма замысловатые, влияют на результат вычисления другой формулы, которая в свою очередь влияет на результаты первой. Возникает циклическая ссылка.
Предупреждение о циклической ссылке
Появление циклических ссылок очень легко определить. При их возникновении или наличии в уже созданной книге excel сразу же появляется предупреждение о циклической ссылке, которое по большому счету и описывает суть явления.
При нажатии на кнопку ОК, сообщение будет закрыто, а в ячейке содержащей циклическую ссылку в большинстве случаев появиться 0.
Предупреждение, как правило, появляется при первоначальном создании циклической ссылки, или открытии книги содержащей циклические ссылки. Если предупреждение принято, то при дальнейшем возникновении циклических ссылок оно может не появляться.
Как найти циклическую ссылку
Циклические ссылки в excel могут создаваться преднамеренно, для решения тех или иных задач финансового моделирования, а могут возникать случайно, в виде технических ошибок и ошибок в логике построения модели.
В первом случае мы знаем об их наличии, так как сами их предварительно создали, и знаем, зачем они нам нужны.
Во втором случае, мы можем вообще не знать где они находятся, например, при открытии чужого файла и появлении сообщения о наличии циклических ссылок.
Найти циклическую ссылку можно несколькими способами. Например, чисто визуально формулы и ячейки участвующие в образовании циклических ссылок в excel отмечаются синими стрелками, как показано на первом рисунке.
Если циклическая ссылка одна на листе, то в строке состояния будет выведено сообщение о наличии циклических ссылок с адресом ячейки.
Если циклические ссылки есть еще на других листах кроме активного, то будет выведено сообщение без указания ячейки.
Если или на активном листе их более одной, то будет выведено сообщение с указанием ячейки, где циклическая ссылка появляется в первый раз, после ее удаления – ячейка, содержащая следующую циклическую ссылку и т.д.
Найти циклическую ссылку можно также при помощи инструмента поиска ошибок.
На вкладке Формулы в группе Зависимости формул выберите элемент Поиск ошибок и в раскрывающемся списке пункт Циклические ссылки.
Вы увидите адрес ячейки с первой встречающейся циклической ссылкой. После ее корректировки или удаления – со второй и т.д.
Теперь, после того как мы выяснили как найти и убрать циклическую ссылку, рассмотрим ситуации, когда делать этого не нужно. То есть когда циклическая ссылка в excel приносит нам определенную пользу.
Использование циклических ссылок
Рассмотрим пример использования циклических ссылок в финансовом моделировании. Он поможет нам понять общий механизм итеративных вычислений и дать толчок для дальнейшего творчества.
В финансовом моделировании не часто, но все же возникает ситуация, когда нам необходимо рассчитать уровень затрат по статье бюджета, зависящей от финансовый результата, на который эта статья влияет. Это может быть, например статья расходов на дополнительное премирование персонала отдела продаж, зависящая от прибыли от продаж.
После введения всех формул, у нас появляется циклическая ссылка:
Однако, ситуация не безнадежная. Нам достаточно изменить некоторые параметры Excel и расчет будет осуществлен корректно.
Еще одна ситуация когда могут быть востребованы циклические ссылки – это метод взаимных или обратных распределений косвенных затрат между непроизводственными подразделениями.
Подобные распределения делаются при помощи системы линейных уравнений, и могут быть реализованы в Excel с применением циклических ссылок.
На тему методов распределения затрат в ближайшее время появиться отдельная статья.
Пока же нас интересует сама возможность таких вычислений.
Итеративные вычисления
Для того чтобы корректный расчет был возможен, мы должны включить итеративные вычисления в параметрах Excel.
Итеративные вычисления – это вычисления повторяемые множество раз, пока не будет достигнут результат соответствующий заданным условиям (условию точности или условию количества осуществленных итераций).
Включить итеративные вычисления можно через вкладку Файл → раздел Параметры → пункт Формулы. Устанавливаем флажок «Включить итеративные вычисления».
Как правило, установленных по умолчанию предельного числа итераций и относительной погрешности достаточно для наших вычислительных целей.
Следует иметь ввиду, что слишком большое количество вычислений может существенно загружать систему и снижать производительность.
Также, говоря об итеративных вычислениях, следует отметить, что возможны три варианта развития событий.
Решение сходится, что означает получение надежного конечного результата.
Решение расходится, т. е. при каждой последующей итерации разность между текущим и предыдущим результатами увеличивается.
Решение колеблется между двумя значениями, например, после первой итерации получается значение 1, после второй — значение 10, после третьей — снова 1 и т. д.
Comments are currently closed.
Источник: excel-training.ru
Циклическая ссылка в Excel. Как найти и удалить — 2 способа
В большинстве случаев циклические ссылки воспринимаются пользователями как ошибочные выражения. Это обуславливается тем, что сама программа перегружается от их наличия, предупреждая об этом специальным оповещением. Чтобы снять ненужную нагрузку с программных процессов и устранить конфликтные ситуации между ячейками, необходимо найти проблемные места и удалить их.
Что такое циклическая ссылка
Циклическая ссылка представляет собой выражение, которое через формулы, расположенные в других ячейках, ссылается на самое начало выражения. При этом в данной цепочке может быть огромное количество ссылок, из которых образуется замкнутый круг. Чаще всего это ошибочное выражение, которое перегружает систему, не дает программе работать корректно. Однако в некоторых ситуациях циклические ссылки пользователи добавляют осознанно, чтобы выполнить определенные расчетные операции.
Если циклическая ссылка – ошибка, которую пользователь допустил случайно при заполнении таблицы, введении определенных функций, формул, необходимо найти ее и удалить. На этот случай существует несколько эффективных способов. Стоит подробно рассмотреть 2 наиболее простых и проверенных на практике.
Важно! Раздумывать о том есть ли в таблице циклические ссылки или нет не нужно. Если подобные конфликтные ситуации присутствуют, современные версии Excel сразу уведомляют пользователя об этом предупреждающим окном с соответствующей информацией.
Визуальный поиск
Самый простой метод поиска, который подойдет при проверке небольших таблиц. Порядок действий:
- Когда появится предупреждающее окно, необходимо закрыть его нажатием кнопки «ОК».
- Программа автоматически обозначит те ячейки, между которыми возникла конфликтная ситуация. Они будет подсвечены специальной стрелкой трассировки.
- Чтобы убрать цикличность, необходимо зайти в обозначенную ячейку и исправить формулу. Для этого необходимо убрать координаты конфликтной клетки из общей формулы.
- Останется перевести курсор мыши на любую свободную ячейку таблицы, нажать ЛКМ. Циклическая ссылка будет удалена.
Использование инструментов программы
В тех случаях, когда стрелки трассировки не указывают на проблемные места в таблице, необходимо воспользоваться встроенными инструментами Excel для поиска и удаления циклических ссылок. Порядок действий:
- В первую очередь нужно закрыть окно с предупреждением.
- Перейти на вкладку «Формулы» на основной панели инструментов.
- Зайти в раздел «Зависимости формул».
- Найти кнопку «Проверка ошибок». Если окно программы находится в сжатом формате, данная кнопка будет обозначена восклицательным знаком. Рядом с ней должен находиться маленький треугольник, который направлен вниз. Нужно нажать на него, чтобы появился список команд.
- Из списка выбрать «Циклические ссылки».
- Выполнив все описанные выше действия, перед пользователем появится полный список с ячейками, которые содержат циклические ссылки. Для того чтобы понять, где точно находится данная клетка, нужно найти ее в списке, кликнуть по ней левой кнопкой мыши. Программа автоматически перенаправит пользователя в то место, где возник конфликт.
- Далее необходимо исправить ошибку для каждой проблемной ячейки, как описывалось в первом способе. Когда конфликтные координаты будут удалены из всех формул, которые есть в списке ошибок, необходимо выполнить заключительную проверку. Для этого возле кнопки «Проверка ошибок» нужно открыть список команд. Если пункт «Циклические ссылки» не будет показан как активный – ошибок нет.
Отключение блокировки и создание циклических ссылок
Разобравшись с тем, как находить и исправлять циклические ссылки в таблицах Excel, необходимо рассмотреть ситуации, когда данные выражения можно использовать с пользой. Однако перед этим нужно узнать, как отключить автоматическую блокировку подобных ссылок.
Чаще всего циклические ссылки осознанно применяются во время построения экономических моделей, для выполнения итеративных вычислений. Однако, даже если подобное выражение используется осознанно, программа все равно будет блокировать его в автоматическом режиме. Для запуска выражения в работу, необходимо отключить блокировку. Для этого нужно выполнить несколько действий:
- Зайти во вкладку «Файл» на главной панели.
- Выбрать пункт «Параметры».
- Перед пользователем должно появиться окно настройки Excel. Из меню в левой части выбрать вкладку «Формулы».
- Перейти к разделу «Параметры вычислений». Установить галочку напротив функции «Включить итеративные вычисления». Дополнительно к этому в свободных полях чуть ниже можно установить максимальное количество подобных вычислений, допустимую погрешность.
Важно! Без особой необходимости лучше не изменять максимальное количество итеративных вычислений. Если их будет слишком много, программа будет перегружена, могут появиться сбои с ее работой.
- Чтобы изменения вступили в силу, необходимо нажат на кнопку «ОК». После этого программа перестанет автоматически блокировать вычисления в ячейках, которые связаны циклическими ссылками.
Самый простой вариант создания циклической ссылки – выделить любую клетку таблицы, в нее вписать знак «=», сразу после которого добавить координаты этой же ячейки. Чтобы усложнить задачу, расширить циклическую ссылку на несколько ячеек, нужно выполнить следующий порядок действий:
- В клетку А1 добавить цифру «2».
- В ячейку В1 вписать значение «=С1».
- В клетку С1 добавить формулу «=А1».
- Останется вернуться в самую первую ячейку, через нее сослаться на клетку В1. После этого цепь из 3 ячеек замкнется.
Заключение
Найти циклические ссылки в таблице Excel достаточно просто. Значительно упрощает данную задачу автоматическое оповещение самой программы о наличии конфликтующих выражений. После этого останется всего лишь воспользоваться одним из двух описанных выше способов, чтобы избавиться от ошибок.
Источник: office-guru.ru