На этом шаге мы рассмотрим циклические ссылки.
Иногда при вводе формул на экране может появиться сообщение, подобное показанному на рисунке 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% от величины Чистый_доход.
Но в обычных ситуациях лучше всего не выбирать параметр Итерации, чтобы можно было получать предупреждения о циклических ссылках, т.к. в большинстве случаев циклическая ссылка указывает на ошибку, которая должна быть исправлена.
На следующем шаге мы рассмотрим несколько примеров с использованием абсолютных, относительных и смешанных ссылок в формулах.
