Циклические ссылки в excel как сделать

Как найти циклическую ссылку в Excel убрать

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

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

Простым примером такой ситуации является следующий вариант:
— ячейка C3 ссылается на B6
— ячейка B6 ссылается на D6
— ячейка D6 ссылается на C3

Тут найти проблему просто.

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

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

Выделение группы ячеек

Такой способ аудита в Excel, как выделения группы ячеек по заданным условиям, устанавливает ограничения для области поиска.

Эта функция расположена на вкладке «Home» в группе «Найти и выделить» — «Выделение группы ячеек».

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

Отслеживание связей ячейки

Чтобы воспользоваться этой функцией Excel, нужно различать два понятия: влияющие ячейки (те, на основании которых выполняются вычисления) и зависимые ячейки (те, которые вычисляются).

Для начала нужно идентифицировать влияющие ячейки.

— Самый простой способ – установить курсор в ячейку для анализа и нажать кнопку F2. Влияющие ячейки будут выделены тем же цветом, что и формула в активной ячейке.
— Обозначив активную ячейку, нажать сочетание клавиш Ctrl+[ — будут отмечены все задействованные ячейки
— Аналогичный вариант — сочетание клавиш Ctrl+Shift+[ — в этом случае на активном листе будут отмечены и прямо, и косвенно влияющие ячейки
— Выделение группы ячеек по формулам (как описано выше).
— Функция «Влияющие ячейки» на вкладке «Формула» показывает все задействованные в вычислениях ячейки стрелочками.

Проверка на ошибки

Можно воспользоваться штатной функцией Excel версии старше 2010.

В меню «Формула» есть проверка на наличие ошибок, включая поиск циклических ссылок.

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

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

Фоновый поиск ошибок

В параметрах Excel в группе настроек «Формулы» можно включить фоновый поиск ошибок. Это надстройка позволяет проводить автоматический пересчет формул на рабочем листе.

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

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

Это осуществляется в меню «Файл» — «Параметры» в группе настроек «Формулы».

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

В статье рассмотрены общие правила поиска циклических ссылок. В каждом конкретном случае может потребоваться комбинация алгоритмов.

Циклические ссылки в excel как сделать

На этом шаге мы рассмотрим циклические ссылки.

Иногда при вводе формул на экране может появиться сообщение, подобное показанному на рисунке 1.

Рис. 1. Excel сообщает о том, что в формуле содержится циклическая ссылка

Это говорит о том, что в формуле, которую Вы только что ввели, используется циклическая ссылка. Циклическая ссылка означает прямое или косвенное обращение формулы к самой себе. Например, если ввести в ячейку A3 формулу = A1 + A2 + A3, то возникает циклическая ссылка, так как в формуле, которая находится в ячейке A3, используется также ссылка на ячейку A3. Вычисления по этой формуле могут продолжаться бесконечно долго, поскольку значение в ячейке A3 будет постоянно изменяться. Другими словами, результат никогда небудет получен.

Если после ввода формулы Вы получили сообщение о циклической ссылке, то у Вас есть две возможности.

  • Щелкнуть на кнопке ОК, чтобы попытаться обнаружить циклическую ссылку.
  • Щелкнуть на кнопке Отмена, чтобы ввести формулу в том виде, в каком она есть.

Как правило, циклические ссылки являются ошибочными, поэтому нужно щелкнуть на кнопке ОК. В результате Excel отобразит панель инструментов Циклические ссылки (рис. 2).

Рис. 2. Панель инструментов Циклические ссылки

На этой панели щелкните на первой ячейке в раскрывающемся списке, а затем исследуйте находящуюся в этой ячейке формулу. Если Вы не можете определить, является ли эта ячейка причиной появления циклической ссылки, то щелкните на следующей ячейке в списке. Продолжайте просматривать формулы до тех пор, пока в строке состояния не исчезнет запись Цикл.

Если Вы решите игнорировать сообщение о циклической ссылке (щелкнув на кнопке Отмена), то Excel позволит Вам ввести данную формулу и отобразит в строке состояния сообщение, напоминающее о существовании циклической ссылки. В данном случае это сообщение будет выглядеть так: Цикл: АЗ. Если же Вы активизируете другую рабочую книгу, то сообщение будет состоять только из одного слова Цикл (без указания адреса ячейки).

Если активизирована опция Итерации, то Excel ничего не сообщит о циклической ссылке. Установить эту опцию можно во вкладке Вычисления диалогового окна Параметры (рис. 3).

Рис. 3. Вкладка Вычисления диалогового окна Параметры

При активизации описываемой опции Excel выполняет циклические вычисления столько раз (делает столько итераций), сколько задано в поле Предельное число итераций, или до тех пор, пока изменение значения не станет меньше, чем 0,001 (либо величины, заданной в поле Относительная погрешность). Бывают ситуации, когда циклические ссылки используются преднамеренно. В подобных случаях параметр Итерации должен быть выбран.

Простой пример такой ситуации показан на рисунке 4.

Рис. 4. Пример преднамеренной циклической ссылки

В данном примере рассматривается фирма, в политику которой входит перечисление пяти процентов чистого дохода на благотворительные цели. Но это пожертвование считается статьей расхода, и поэтому вычитается из чистого дохода. Таким образом, чистый доход уменьшается и благотворительные перечисления изменяются, что опять приводит к изменению чистого дохода и т.д., т.е. перед нами пример циклической ссылки. Для ее разрешения нужно активизировать опцию Итерации.

В ячейке с именем Пожертвования содержится следующая формула: = 5% * Чистый_доход

В ячейке с именем Чистый_доход находится следующая формула: = Прибыль — Расходы — Пожертвования

Эти формулы создают разрешимую циклическую ссылку. Excel продолжает вычисления до тех пор, пока результаты формул перестанут изменяться. Чтобы увидеть, как это происходит, введите некоторые значения в ячейки Прибыль и Расходы. Если опция Итерации не активизирована, то Excel выведет на экран сообщение о циклической ссылке, и правильный результат не будет получен. Если же опция Итерации активизирована, то Excel будет продолжать вычисления до тех пор, пока значение Пожертвования не будет составлять 5% от величины Чистый_доход.

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

На следующем шаге мы рассмотрим несколько примеров с использованием абсолютных, относительных и смешанных ссылок в формулах.

Поиск и исправление ошибок в вычислениях Excel

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

Н/Д – является сокращением термина Неопределённые данные. Помогает предотвратить использование ссылки на пустую ячейку

Функция с числовым аргументом использует неприемлемый аргумент

Ошибка в написании имени или используется несуществующее имя

Используется ссылка на несуществующую ячейку

Вместо числового или логического значения введён текст, и Excel не может преобразовать его к нужному типу данных

В качестве делителя используется ссылка на ячейку, в которой содержится нулевое или пустое значение (если ссылкой является пустая ячейка, то её содержимое интерпретируется как ноль)

Используется ошибочная ссылка на ячейку

Данные не помещаются в ячейку по ширине, следует увеличить ширину столбца или применить более компактное форматирование

Второй способ обнаружения ошибок – Excel отображает в левом верхнем углу ячейки зелёный треугольник (индикатор ошибки). При выборе такой ячейки появляется смарт-тег проверки ошибок.

Для проверки ошибок необходимо выполнить следующие шаги:

1. Выберите лист, который требуется проверить на наличие ошибок.

2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Проверка наличия ошибок. Откроется окно диалога Контроль ошибок.

3. В окне диалога Контроль ошибок просмотрите информацию о текущей ошибке в левой части окна.

4. Для просмотра более детального описания ошибки и возможных вариантов её исправления нажмите кнопку Справка по этой ошибке.

5. Нажмите кнопку Показать этапы вычисления. MS Excel откроет окно диалога Вычисление формулы, где вы сможете просмотреть значения различных частей вложенной формулы, вычисляемые в порядке расчёта формулы:

a) нажмите кнопку Вычислить, чтобы проверить значение подчёркнутой ссылки. Результат вычислений показан курсивом;

b) если подчёркнутая часть формулы является ссылкой на другую формулу, нажмите кнопку Шаг с заходом, чтобы отобразить другую формулу в поле Вычисление. Нажмите кнопку Шаг с выходом, чтобы вернуться в предыдущую ячейку и формулу;

c) Выполняйте указанные действия, пока не будет вычислена каждая часть формулы;

d) Чтобы снова увидеть вычисления, нажмите кнопку Заново;

e) Чтобы завершить вычисления, нажмите кнопку Закрыть.

6. Для изменения формулы в строке формул нажмите кнопку Изменить в строке формул.

7. Если ошибка является несущественной, вы можете нажать кнопку Пропустить ошибку. Помеченная ошибка при последующих проверках будет пропускаться.

8. Для перехода к следующей ошибке нажмите кнопку Далее. Для возврата к предыдущей – кнопку Назад.

9. Доведите до конца проверку ошибок и закройте окно диалога Контроль ошибок.

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

Для отображения ячеек, входящих в формулу в качестве аргументов, необходимо выделить ячейку с формулой и нажать кнопку Влияющие ячейки в группе Зависимости формул вкладки Формулы.

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

Для отображения ячеек, в формулы которых входит какая-либо ячейка, её следует выделить и нажать кнопку Зависимые ячейки в группе Зависимости формул вкладки Формулы.

Один щелчок по кнопке Зависимые ячейки отображает связи с ячейками, непосредственно зависящими от выделенной ячейки. Если эти ячейки также влияют на другие ячейки, то следующий щелчок отображает связи с зависимыми ячейками. И так далее.

Связи в пределах текущего листа отображаются синими стрелками. Связи с ячейками других листов и книг отображаются чёрными пунктирными линиями и значком листа. Красные стрелки показывают ячейки, вызывающие ошибки.

Для скрытия стрелок связей следует нажать кнопку Убрать все стрелки в группе Зависимости формул вкладки Формулы. Использование окна контрольных значений.

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

В этом случае вашим помощником может выступать панель инструментов Окно контрольного значения.

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

Добавление ячеек в окно контрольных значений

Для добавления на панель контрольных значений выполните следующие действия:

1. Выделите ячейки, контрольные значения которых нужно поместить на панель.

Чтобы выделить все ячейки листа с формулами, на вкладке Главная в группе Правка нажмите кнопку Найти и выделить и выберите команду Формулы.

2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Окно контрольного значения.

3. На панели Окно контрольного значения нажмите кнопку Добавить контрольное значение .

4. В окне диалога Добавление контрольного значения проверьте правильность выбранного диапазона и нажмите кнопку Добавить.

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

Например, ячейка С4 = Е7, Е7 = С11, С11 = С4. В итоге С4 ссылается на С4.

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

При нажатии на кнопку OK сообщение будет закрыто, а в ячейке, содержащей циклическую ссылку, в большинстве случаев появится 0.

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

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

В первом случае мы знаем об их наличии, так как сами их предварительно создали, и знаем, зачем они нам нужны.

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

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

Если циклическая ссылка – одна на листе, то в строке состояния будет выведено сообщение о наличии циклических ссылок с адресом ячейки.

Если циклические ссылки есть ещё и на других листах кроме активного, то будет выведено сообщение без указания ячейки.

Найти циклическую ссылку можно также при помощи инструмента поиска ошибок.

На вкладке Формулы в группе Зависимости формул выберите элемент Поиск ошибок и в раскрывающемся списке пункт Циклические ссылки.

Вы увидите адрес ячейки с первой встречающейся циклической ссылкой. После её корректировки или удаления – со второй и т. д.

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

Что такое циклические ссылки и как они влияют на работу сайта

6 ноября 2017 года. Опубликовано в разделах: Азбука терминов. 4715

Больше видео на нашем канале — изучайте интернет-маркетинг с SEMANTICA

Что такое циклические ссылки

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

Присутствие циклических ссылок на сайте – это техническая ошибка, требующая устранения. Циклические ссылки не дают пользы продвижению сайта в поисковых системах. Напротив, поисковики Яндекса и Google негативно относятся к подобным неисправностям сайта, поскольку:

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

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

Месторасположение циклических ссылок

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

  1. Логотип или название веб-ресурса. При нажатии на логотип происходит обновление текущей страницы.
  2. Меню сайта. Обычно там помещается список ссылок на ключевые страницы сайта. Циклические ссылки есть практически на всех пунктах главного меню.
  3. Заголовки первого уровня. Здесь можно нажать на H1 и оказаться на той же самой странице. Это является технической ошибкой, поскольку заголовок не должен быть кликабельным. В этом случае необходимо провести верстку.
  4. Фотографии, графические изображения. Часто веб-мастера наделяют главное изображение функцией перехода на текущую страницу. Это грубая ошибка, порождающая цикличность. Ссылочный атрибут также нельзя устанавливать на прочие графические элементы сайта.
  5. Навигационная цепочка. Просмотрев множество веб-ресурсов можно обнаружить тенденцию: последняя ссылка в механизме следования к рабочему каталогу часто является циклической. Это является ошибкой и требует исправления.

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

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

Зачем удалять циклические ссылки

Существует несколько причин, требующих удаления ссылок:

  • Пользователи, пришедшие на сайт со страниц поисковой выдачи по ключевым словам, с рекламных блоков или из социальных сетей, могут запутаться в навигации, пролистав несколько страниц. Для того чтобы исключить подобную ошибку принято использовать хлебные крошки – навигационную цепочку веб-ресурса.
  • Нажимая на определенную кнопку интерфейса, человек рассчитывает увидеть новую информацию. Циклические ссылки не позволяют этого сделать, постоянно возвращая его на то же самое место. В итоге ресурс рискует потерять часть аудитории.
  • Роботы Яндекс и Google не одобряют такие ссылки, поскольку они, также как и пользователи, могут перемещаться по кругу. И если человек быстро разберется в ситуации, то поисковая машина при индексировании страниц не сможет продвинуться дальше. Естественно, сайт не попадет из-за этого под какие-либо санкции поисковиков, однако его рейтинг будет снижен.

Как удалить циклические ссылки с веб-ресурса

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

Для начала их следует обнаружить. Делается это посредством онлайн-сервисов. Они дают возможность провести полную диагностику сайта. В частности, выделяют программу СайтРепорт или WebSite Auditor . Для удаления циклических ссылок в WordPress используется плагин Remove Redundant Links . С его помощью веб-мастер может обнаружить все ненужные ссылки и произвести исправление на части гипертекстового документа, ссылающиеся на другие страницы сайта.

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

Специалисты студии SEMANTICA проведут комплексный анализ сайта по следующему плану:

– Технический аудит.
– Оптимизация.
– Коммерческие факторы.
– Внешние факторы.

Мы не просто говорим, в чем проблемы. Мы помогаем их решить

Читать еще:  Кроссворды в excel как сделать
Ссылка на основную публикацию
Adblock
detector