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

Ранее я описал, как найти и исправить циклическую ссылку. Напомню, что циклическая ссылка появляется, если в ячейку Excel введена формула, содержащая ссылку на саму эту ячейку (напрямую или через цепочку других ссылок). Например (рис. 1), в ячейке С2 находится формула, ссылающаяся на саму ячейку С2.

Рис. 1. Пример циклической ссылки

Но!.. Не всегда циклическая ссылка является бедствием. Циклическую ссылку можно использовать для решения уравнений итерационным способом. Для начала нужно позволить Excel вести вычисления, даже при наличии циклической ссылки. В обычном режиме Excel, обнаружив циклическую ссылку, выдаст сообщение об ошибке, и потребует ее устранения. В обычном режиме Excel не может провести вычисления, так как циклическая ссылка порождает бесконечный цикл вычислений. Можно, либо устранить циклическую ссылку, либо допустить вычисления по формуле с циклической ссылкой, но ограничив число повторений цикла. Для реализации второй возможности щелкните на кнопке «Office» (в левом верхнем углу), а затем на «Параметры Excel» (рис. 2).

Скачать заметку в формате Word, примеры в формате Excel

Рис. 2. Параметры Excel

В открывшемся окне «Параметры Excel» перейдите на вкладку Формулы и отметьте «Включить итеративные вычисления» (рис. 3). Помните, что эта опция включается для приложения Excel  в целом (а не для одного файла), и будет действовать, пока вы ее не отключите.

Рис. 3. Включить итеративные вычисления

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

Решим уравнение третьей степени: х³ – 4х² – 4х + 5 = 0 (рис. 4). Для решения этого уравнения (и любого другого уравнения совершенно произвольного вида) понадобится всего одна ячейка Excel.

Рис. 4. График функции f(x)

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

(1) x = x – f(x)/f’(x), где

х – переменная;

f(x) – функция, задающая уравнение, корни которого мы ищем; f(x) = х³ – 4х² – 4х + 5

f’(x) – производная нашей функции f(x); f’(x) = 3х² – 8х – 4; производные основных элементарных функций можно посмотреть здесь.

Если вы заинтересовались, откуда взялась формула (1), можете почитать, например, здесь.

Итоговая рекуррентная формула имеет вид:

(2) х = x – (х³ – 4х² – 4х + 5)/(3х² – 8х – 4)

Выберем любую ячейку на листе Excel (рис. 5; в нашем примере это ячейка G19), присвоим ей имя х, и введем в нее формулу:

(3) =x-(x^3-4*x^2-4x+5)/(3*x^2-8*x-4)

Можно вместо х использовать адрес ячейки… но согласитесь, что имя х, смотрится привлекательнее; следующую формулу я ввел в ячейку G20:

(4) =G20-(G20^3-4*G20^2-4*G20+5)/(3*G20^2-8*G20-4)

Рис. 5. Рекуррентная формула: (а) для поименованной ячейки; (б) для обычного адреса ячейки

Как только мы введем формулу и нажмем Enter, в ячейке сразу же появится ответ – значение 0,77. Это значение соответствует одному из корней уравнения, а именно второму (см. график функции f(x) на рис. 4). Поскольку начальное приближение не задавалось, итерационный вычислительный процесс начинался со значения, по умолчанию хранимого в ячейке х и равного нулю. Как же получить остальные корни уравнения?

Для изменения стартового значения, с которого рекуррентная формула начинает свои итерации, предлагается использовать функцию ЕСЛИ: [1]

(5) =ЕСЛИ(x=0;-5;x-(x^3-4*x^2-4*x+5)/(3*x^2-8*x-4))

Здесь значение «-5» – начальное значение для рекуррентной формулы. Изменяя его, можно выйти на все корни уравнения:

Начальное значение	Корень уравнения
1	0,77
-5	-1,40
8	4,63

---

[1] Идея подсмотрена здесь