Начала программирования в Excel

Задача 2

Постановка задачи:

Вычислить e^x, используя разложение в ряд:

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

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

увеличить изображение
Рис. 1.2. Решение задачи 2

Приведем краткие комментарии:

• На листе 2 рабочей книги я начал с того, что задал как обычно, постановку задачи.
• Ввел исходные данные, дав имена x и A0 ячейкам, хранящим значения этих переменных.
• Построил три ряда значений: k - порядковые номера членов ряда, Ak - значения k -го члена ряда, Sum(Ak) - сумма первых k членов ряда.
• При построении ряда порядковых номеров в ячейку C9 занес 0, в ячейку C10 формулу " =C9+1 ". Затем скопировал эту формулу, получив необходимое множество значений.
• При построении ряда значений Ak поступил аналогично, - в ячейку D9 занес начальное значение 1, а в соседнюю ячейку D10 - формулу " =D9*x/C10 ". Затем формула была скопирована.
• При построении ряда, задающего накапливающуюся сумму, в ячейку E9 ввели формулу: " SUM(A0:D9) ". Обратите внимание, здесь A0 - это абсолютная ссылка - имя переменной, а D9 - это относительная ссылка. Последняя будет изменяться при копировании, позволяя тем самым накапливать сумму значений.
• В завершение, Мастер диаграмм помог построить график, демонстрирующий сходимость Ak к нулю и Sum(Ak) к e^x.
• Заметьте, в данном решении появляется совершенно новое качество, крайне важное для обучения - здесь показан процесс решения, а не только конечный результат. Данное решение позволяет наглядно видеть, как сходится процесс вычислений, изучать влияние аргумента на скорость сходимости. Такое решение способствует тому, чтобы стать исследователем, вместо того, чтобы оставаться простым наблюдателем.

Задача 3

Постановка задачи:

Найти корень уравнения 4-й степени:

F(x) = ax4  + bx3 +cx2 +dx +e = 0,

используя метод Ньютона.

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

В этом соотношении F1(X) - производная функции, которая для нашего уравнения задается соотношением:

F1(x) = 4ax3 + 3bx2 + 2cx + d

Константа h задает шаг, позволяющий регулировать процесс сходимости. Обычно выбирается в пределах от 0,5 до 1,5. Замечу, что скорость сходимости во многом зависит также от выбора начального приближения X₀.

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

Вот мои действия по решению этой задачи:

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

• На следующем шаге я записал рекуррентные соотношения позволяющие вычислить функцию на некотором интервале с фиксированным шагом. Реализуется все достаточно просто, как это уже описывалось для ранее рассмотренных подобных задач. В ячейку A12 я занес начальное значение аргумента функции - левый конец исследуемого интервала поведения функции. В ячейку A13 занес формулу " = A12 + DeltaX " и затем скопировал ее так, чтобы получить нужный мне интервал. В ячейку B13 я записал формулу, вычисляющую функцию F(x) по схеме Горнера - " = (((Cof1*A12 + Cof2)*A12 + Cof3)*A12 + Cof4)*A12 + Cof5 "

  Скопировав эту формулу, я получил два ряда данных - аргументов и значений функции. Заметьте, DeltaX, Cof1, Cof2, …Cof5 - это имена, которые я дал исходным данным нашей задачи. Имена Cof1 - Cof5 заменяют имена коэффициентов функции - a, b, c, d, e.

• Имея источник данных, я построил график функции в заданном интервале, вызвав Мастера построения диаграмм. Варьируя параметрами X0 и DeltaX, я подобрал для моей функции интервал так, чтобы на нем был хотя бы один корень. Благодаря тому, что все изменения мгновенно отражаются на графике, для функции, с которой я работал:

  F(x) = 2x4 -3x3 -4x2  + 5x - 6

  достаточно быстро удалось подобрать интервал - (-2, 3), на котором функция имеет два действительных корня. Более того, из графика видно, что других действительных корней у этой функции больше нет. Вы сами можете в этом убедиться, взглянув на график:

  

  
  увеличить изображение
  Рис. 1.3. График функции F(x) в задаче 3

• Действия на втором этапе немногим отличаются от действий первого этапа. Отличие чисто техническое - создаются три ряда значений: Xk, F(Xk), F1(Xk) и для определения Xk используется другая формула.

• При создании ряда Xk в ячейку A26 я ввел начальное значение X0, а в ячейку A27 формулу: " =A26 - Step*B26/C26 ", задающую изменение X в схеме Ньютона. Затем эта формула была скопирована так, чтобы получить достаточное число приближений.

• Для получения ряда значений функции F(Xk) в ячейку B26 была введена формула, задающая функцию: " =(((Cof1*A26 + Cof2)*A26 + Cof3)*A26 + Cof4)*A26 + Cof5 "

  Затем эта формула была скопирована параллельно значениям аргументов.

• Аналогичные операции были проделаны для получения значений ряда производных F'(Xk). Формула, введенная в ячейку C26, имела вид: " =((4*Cof1*A26 +3*Cof2)*A26 + 2*Cof3)*A26 + Cof4 "

  Затем и эта формула была скопирована параллельно значениям аргументов.

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

  

  
  увеличить изображение
  Рис. 1.4. Зависимость ячеек

• Все завершается, как всегда, построением графика. На следующем рисунке можно видеть, как для выбранного начального приближения сходится итерационный процесс, X_k - стремится к значению корня, F(X_k) - к нулю. Здесь же показано и поведение производной, которая тоже в данном случае стремится к постоянному значению. Оцените удобство Excel для решения подобных задач. Известно, что для итерационных процессов успех во многом зависит от правильного выбора начального приближения. Здесь подобрать начальное приближение легко и просто. Заодно отметим, что данное решение позволяет найти поочередно все корни уравнения. Известно, что с ростом степени уравнения итерационные методы начинают "спотыкаться" и перестают сходиться. Нам удавалось, однако, найти решение этого и других подобных уравнений, используя даже такой плохо сходящийся метод, как метод простой итерации. Часто процесс приближения расходился, но найти корень уравнения с приемлемой точностью за сч ет подбора начального приближения все же удавалось.

  

  
  увеличить изображение
  Рис. 1.5. Нахождение корня уравнения методом Ньютона