Преднамеренные циклические ссылки

Многие циклические ссылки могут быть разрешены. Формулы, представленные на рис. 24, являются циклическими, так как формула в ячейке А1 зависит от значения в A3, а формула в A3 зависит от значения в А1. Ячейки А1: АЗ отформатированы таким образом, что в них отображаются формулы.

Рисунок 24 -Этот лист содержит циклическую ссылку

СОВЕТ

Для переключения между отображением в листе возвращаемых значений (по умолчанию) и формул нажмите клавиши Ctrl+' (обратный апостроф на клавише тильды).

Чтобы разрешить циклическую ссылку этого типа, установите флажок Итерации (Iteration) на вкладке Вычисления (Calculation) окна диалога Параметры (Options). В этом случае Excel пересчитывает заданное число раз все ячейки во всех открытых листах, которые содержат циклическую ссылку. При каждом пересчете формул результаты в ячейках приближаются к правильным значениям.

Если установлен флажок Итерации, Excel принимает по умолчанию Предельное число итераций (Maximum Iterations) равным 100 и в поле Относительная погрешность (Maximum Change) задает значение 0, 001. Таким образом, Excel выполняет пересчет до 100 раз или пока изменение значений между итерациями не станетменьше 0, 001. Для формул, показанных на рис. 24, мы установили флажок Итерации и приняли используемые по умолчанию значения для параметров Предельное число итераций и Относительная погрешность. В результате после первого пересчета с этими параметрами формула в ячейке A3 возвращает 999, 9995, а сообщение Вычислить в строке состояния говорит о том, что можно выполнить дополнительные итерации. После нажатия клавиши F9 значение пересчитывается и становится все более близким к 1000. Так, после следующего пересчета значение в ячейке A3 становится равным 999, 9998, поскольку зада- на относительная погрешность 0, 001. Если бы относительная погрешность была установлена равной 0, 0001, сразу бы появилось значение 1000.

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

Только что рассмотренный процесс называется сходимостью (convergence) — разность между последовательно получаемыми результатами уменьшается с каждым итерационным вычислением. При противоположном процессе, расходимости (divergence), разность между результатами с каждой итерацией становится больше. В этом случае Excel продолжает вычисления, пока не выполнит заданное число итераций. Хотя Excel может выполнять порядка 100 итераций в секунду, следует установить ручной режим вычислений. В противном случае программа будет пересчитывать циклические ссылки при каждом изменении значений в ячейках.