Математика : Контрольная работа: Численные методы расчетов в Exel

Контрольная работа: Численные методы расчетов в Exel

Федеральное агентство по образованию

Государственное образовательное учреждение  высшего  профессионального образования

Северо-Западный государственный заочный

технический университет

Институт управления производственными и

инновационными программами

Кафедра  информатики

Контрольная   работа   по   дисциплине

 «Математика. Часть 2.»

Тема:    Численные методы и расчеты в  EXCEL.”

Задача  1.   Интерполяция   функции   с   равноотстоящими узлами.

Анализ  и прогнозирование в  EXCEL.

Задача  2.   Решение систем уравнений в EXCEL.

Задача  3.   Комплексные  числа.

Выполнила студентка: Шестакова Мария Дмитриевна

ИУПиИП

Курс:  II

Специальность: 80502.65

Шифр: 578030493

Преподаватель:  Ходоровская Валентина Сергеевна

Подпись преподавателя:

Санкт-Петербург

2007

Тема .

Численные методы и расчеты в  EXCEL.

Задача  1.

Интерполяция функции с равноотстоящими узлами.

Анализ  и прогнозирование в  EXCEL.

I.  Написать выражение для интерполяционного полинома Ньютона.

II. Составить программу для вычисления значения функции в заданных точках

x1    ;  x2   ;  x3   ;   x4    :

1)   при  помощи полинома Ньютона для реализации ее в  EXCEL ;

2)  при  помощи  функций, осуществляющих прогноз вычислений

 (ТЕНДЕНЦИЯ  и  ПРЕДСКАЗАНИЕ).

Функция задана таблицей с равноотстоящими узлами:

Основные понятия.

Цель  работы:  научиться пользоваться программой  EXCEL    для получения аналитической зависимости по экспериментальным данным  и изучение режимов экстраполяции данных в  EXCEL.

Задача интерполяции сводится к требованию точного совпадения в узловых

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

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

Экстраполяция  —  это процедура аналогичная интерполяции, но при условии,  что   x  лежит вне интервала (x0 ,  xn) . Происходит от  “экстра…”  и латинского  “polio”, что значит “приглаживаю, изменяю”.

Аппроксимация — это замена одних математических объектов (например,  чисел или

функций) другими,  более простыми  и в том или ином смысле близкими к исходным(например, кривых линий близкими к ним ломаными). Слово происходит  от латинского “approximo”, что значит  “приближаюсь”.

Графически задача интерполяции заключается в том, чтобы построить такую интерполирующую функцию, которая бы проходила через все узлы интерполяции. Чаще всего в качестве интерполирующей функции F (x) используются многочлены Pn (x).  Задача состоит в том, чтобы подобрать многочлен Pn (x), обеспечивающий требуемую интерполяцию е.

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

Термин  “полином”  имеет  то же значение,  что и  слово  “многочлен  и  происходит  от    “поли…” —  часть сложных слов, указывающая на множество, всесторонний охват или разнообразный состав чего-либо (от греческого “polys” – многий, многочисленный, обширный) и латинского “nomen”, т.е. имя.

Конечной разностью первого порядка называется разность:                                              

Дyi  = yi + 1   -  yi   ,   i = 0,1, .... , n – 1

Аналогично определяются конечные разности второго и более высоких  порядков.

Интерполяционный полином  Ньютона.

Интерполяционный многочлен Ньютона для равноотстоящих узлов  записывается в виде:

Pn (x) = y0 + (x-x0) · Дy0 /1!h + (x-x0)(x-x1) · ДІy0 /2!hІ+....+ (x-x0)(x-x1)…..(x-xn-1) · Дny0 / n!hn

Решение.

Выполнение задания I.

Напишем выражение для интерполяционного полинома Ньютона для экспериментальных данных, приведенных в вышеуказанной таблице. Конечные разности указаны в “Приложение 2”.   Из таблицы видно, что значения  x  являются равноотстоящими узлами, так как возрастают равномерно с шагом  h = 0,05.   Степень полинома определяется числом (порядком) конечных разностей  ( в данном случае их девять ).

Pn(x) = P9(x)= y0  +  (x-x0) Дy0 / 1!h  +  (x-x0) (x-x1) ДІy0 /2!h2+..

..+ (x-x0)(x-x1) (x-x2) (x-x3) (x-x4) (x-x5) (x-x6) (x-x7) (x-x8) (x-x9) Д9y0 / 9!h9 =

0,860 +  (x- 0,15)    (-0,041) / 1!  · 0,05  + (x- 0,15)  (x- 0,20)  · 0,001 / 2! ·  0,05 2   + 

 (x- 0,15)  (x- 0,20) (x- 0,25)  · 0,001 / 3!  · 0,05 3 +(x- 0,15)  (x- 0,20) (x- 0,25) (x- 0,30)   ·  (-0,001)  / 4! ·  0,05 4    +                 

 (x- 0,15)  (x- 0,20) (x- 0,25) (x- 0,30)(x- 0,35)  ·  0 / 5!  ·  0,05 5 +              

 (x- 0,15)  (x- 0,20) (x- 0,25) (x- 0,30)(x- 0.35)(x- 0,40) · 0,004 / 6! · 0,05 6 +                                                                                     

 (x- 0,15)  (x- 0,20) (x- 0,25) (x- 0,30) (x- 0,35) (x- 0,40) (x- 0,45) ·(-0,016) / 7! 0,05 +                                                                                                    

 (x- 0,15)  (x- 0,20) (x- 0,25) (x- 0,30)  (x- 0,35)  (x- 0,40)  (x- 0,45)    ( x- 0,50)  ·   0,047   /  8!  ·  0,05 8  +                                                                            

(x- 0,15)  (x- 0,20)  (x- 0,25)  (x- 0,30)  (x- 0,35) (x- 0,40) (x- 0,45) (x- 0,50) (x- 0,55)  ·  (-0,119) / 9! · 0,05 9.

Выполнение задания II.

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

Шаг первый:

Подготовка исходных данных электронной таблицы    в    EXCEL:

а) Введем текстовые и числовые константы (ячейки  A1 : N4).

б) Введем номера по порядку в ячейки  A5 : A14.

в) Введем исходные данные  в ячейки  B5 : C14.

Таким образом подготовлена таблица  для  выполнения работы.

Шаг второй:

Ввод формул:

а) Ввод формул для вычисления конечных разностей первого порядка:

   а.1) в ячейку  D5  введем формулу для вычисления  Дy0 = y1 y0, которая  примет вид:  =C6–C5;

   a.2)  копируем эту формулу в ячейки  D6 : D13. В результате в ячейке  D6

   получаем формулу =C7-C6 (т.е.Дy1 =y2 -  y1 = 0,779 – 0,819 = -0,040),в ячейке D7

   получаем формулу =C8-C7 (т.е. Дy2 = y3 – y2 = 0,741 – 0,779= -0,038) и т.д. до ячейки D13, где

   получаем   формулу

   =C14-C13 (т.е. Дy8  = y9 – y8 = 0,549 – 0,577= -0,028)

б) Ввод формул для вычисления конечных разностей второго порядка:

  б.1)  в ячейку   E5   копируем формулу из ячейки  D5.  В ячейке  E5  появится формула

  =D6-D5 (т.е.  ДІy0  =  Дy1 -  Дy0 =   -0,040  - ( -0,041) = 0,001).  Копируем эту формулу  в ячейки  E6 : E12.

  В ячейке   E12  получаем формулу      =D13 - D1 (т.е.  ДІy7  =  Дy8   -  Дy7=  - 0,028 - ( -0,029) = 0,001).

в) Ввод формул для вычисления конечных разностей  вплоть до девятого  порядка:

  для вычисления всех конечных разностей необходимо ввести только одну формулу(в  ячейке D5),  все                  

  остальные будут получены копированием, т.е. из ячейки E5 копируем формулу в ячейку F5, из F5  в G5 и т.д.

  Отображение в режиме  формул  см. в “Приложении 1”.

  Отображение в режиме значений см. в “Приложении 2”.

Шаг  третий:

Ввод формул:

а) Ввод формул для вычисления промежуточных коэффициентов:

  а.1) для вычисления первого промежуточного коэффициента (x-x0/1!h) в  ячейку M5  введем      формулу    

=($N$2  -  B5) / (A5 + 1) / $F$2.  В ячейке N2 находится текущее значение x.  При копировании     адрес этой  ячейки изменять нельзя, поэтому мы используем абсолютный адрес (значок $). В ячейке F2 находится шаг  интерполяции,    адрес этой ячейки тоже абсолютный (значок $).

  а.2) для вычисления второго промежуточного коэффициента

      (x-x0) (x- x1)/2!hІ =  (x-x0)/1·h  ·  (x-x1)/ 2·h = a · b,

где        a  коэффициент в ячейке  M5,  a = (x-x0)/1h,

b коэффициент, на который нужно умножить M5,  b = (x-x1) / 2h,

  вводим в M6  формулу:   =M5*($N$2 – B6) / (A6 + 1) / $F$2.

а.3)  после ввода данных в M5 и M6, для вычисления остальных промежуточных  коэффициентов    

копируем  формулу из M6 в остальные 7 нижестоящие ячейки. Вячейке M7 мы увидим формулу:  

=M6*($N$2 B7) / (A7 + 1) / $F$2 ,  в ячейке  M8  мы  увидим формулу:   =M7*($N$2 – B8) / (A8 + 1) / $F$2  и 

т.д. 

Шаг  четвертый:

Ввод  формул:

а) Ввод формул для вычисления полинома Ньютона:

  а.1)  для вычисления первого полинома Ньютона, который равен (x-x0) · Дy0 / 1!h = (x-x0) / 1h ·Дy0,  содержимое ячейки M5  надо   умножить на  содержимое  ячейки   D5,  где   хранятся   конечные    разности первого порядка.  Вводим в ячейку N5 формулу   =M5*D$5. Знак $ перед номером строки необходим,  т.к.  в полиноме  Ньютона находятся только конечные разности с  индексом  ноль,  т.е.  все конечные разности берутся только из строки с номером 5;

  а.2)  для ввода остальных членов полинома Ньютона копируем формулу из N5 в остальные 8 нижестоящих ячеек (включительно по N13). Получаем  в N6  формулу  =M6*E$5,  в  N7  формулу    =M7*F$5,  в  N8  формулу    =M8*G$5  и т.д.  до ячейки  N13.

Шаг    пятый:

Ввод формул:

а)  Ввод формул для вычисления суммы коэффициентов  полинома Ньютона:

   а.1) объединим ячейки  A16 : M16, затем в объединенные ячейки введем комментарий

   "Сумма коэффициентов полинома”;

   а.2)  в ячейку  N16  вводим формулу    =СУММ(N5:N13). Теперь в  N16  будет сумма  всех членов полинома  Ньютона, кроме  y0. При  x  = 0,149  в ячейке  N16  получается  число  0,001.

Шаг шестой:

Ввод формул:

а)  Ввод формул для вычисления значения полинома:

  а.1)  объединим ячейки   A18 : M18, затем в объединенные ячейки введем  комментарий    "Значение  полинома";

  а.2) в  ячейку  N18  вводим  формулу    =N16+C5.   В  ячейке  N18   появится  число  0,861 ,  которое  и  есть  значение полинома, вычисленное в точке  x = 0,149

  Шаг седьмой:

Вычисление сумм коэффициентов полинома и значений полинома

при   x = 0,240;      x = 0,430;     x = 0,560.

а) в ячейку  N2  вводим  0,240.   Результат:

в ячейке  N16 —   (-0,073);           в ячейке  N18 —  (0.787);

б) в ячейку  N2  вводим  0,430.   Результат:

в ячейке  N16 —   (-0,209);           в ячейке  N18 —   (0,651);

в) в ячейку  N2  вводим  0.560.   Результат:

в ячейке  N16  —  (-0,287);           в ячейке  N18  —  (0,573).

Шаг  восьмой:

  Для удобства  полученные  данные занесем в нашу таблицу.

Таблицы прилагаются.  Режим    формул   —  “Приложение 1”.  Режим  значений   —  “Приложение 2.

2)Составление программы для вычисления значений функции в заданных точках при помощи функций, осуществляющих прогноз вычислений (ТЕНДЕНЦИЯ  и  ПРЕДСКАЗАНИЕ).

Экстраполяция (прогнозирование) с помощью   функции аппроксимации кривой.

Табличный процессор EXCEL предоставляет возможность  аппроксимации с использованием “функций аппроксимации кривой”

Пусть в узлах  x0 ,  x1, …, x n   известны значения  f(x0),  f(x1), … ,f(x n). Необходимо  осуществить  экстраполяцию (прогнозирование), т.е. вычислить значения  f(x n+1), f(x n+2),   .                                                         

В категории  Статистические функции  EXCEL для этого используются   две функции:  ТЕНДЕНЦИЯ  и  ПРЕДСКАЗАНИЕ,  осуществляющие линейную аппроксимацию кривой для данных массивов

x (x0 , x1 , … , x n)  и  y (y0 ,y1 , … , y n)  методом наименьших квадратов.

Функция  ТЕНДЕНЦИЯ  имеет структуру:

ТЕНДЕНЦИЯ  (y массив, x массив, x список)

y массив ,  x массив  —  даны из условия.

x список  -- это те значения    x,  для которых требуется сосчитать значения  функции  f(x).

Функция  ПРЕДСКАЗАНИЕ  имеет структуру:

ПРЕДСКАЗАНИЕ ( x; y массив; x массив)

После аппроксимации эта функция возвращает только одно прогнозируемое значение  y (для одного из заданных значений аргументов.

Работа с функцией   ТЕНДЕНЦИЯ.

Шаг     первый:

Создадим электронную таблицу в EXCEL , используя исходные данные.

Шаг    второй:

Для того, чтобы поместить результат в список итоговых  ячеек  C6:F6,   выделим эти ячейки.

Шаг    третий:

Далее необходимо щелкнуть по пиктограмме  Мастер функций.

Шаг четвертый:

  а)  В  первом окне выберем категорию  Статистические,  функцию ТЕНДЕНЦИЯ,

    затем щелкнем по  OK.

  б) В окне  “Известные значения  y”  введем адрес блока ячеек  C3:L3.

  в) В окне  “Известные значения  x”  введем адрес блока ячеек  C2:L2.

  г)  В окне  “Новые значения x”     укажем адрес блока ячеек   C5:F5.

Шаг     пятый:

  Для  подтверждения этой  функции одновременно нажмем клавиши SHIFT / CTRL  и   ENTER.  В ячейках  C6:F6  мы увидим прогноз.

В режиме формул:в ячейке C6   —    =ТЕНДЕНЦИЯ(C3:L3;C2:L2;C5)

в ячейке D6    —    =ТЕНДЕНЦИЯ(C3:L3;C2:L2;D5)

в ячейке E6    —    =ТЕНДЕНЦИЯ(C3:L3;C2:L2;E5)

в ячейке F6     —    =ТЕНДЕНЦИЯ(C3:L3;C2:L2;F5)

  В режиме значений: в ячейке C6     —     0,8610

                                     в ячейке D6     —     0,7951

                                     в ячейке  E6    —     0,6576

                                     в ячейке F6     —     0,5635

  Таблицы  прилагаются.

Режим формул — “Приложение 3”. Режим значений  “Приложение 4”.

               Работа с функцией  ПРЕДСКАЗАНИЕ.

Шаг   первый:

  Создадим электронную таблицу в EXCEL,  используя исходные данные.

Шаг   второй:

  Для размещения результата активизируем ячейку  С6.

Шаг   третий:

  а) При помощи  Мастера функций  вызовем функцию  ПРЕДСКАЗАНИЕ,

категория  Статистические.

б)  В окне  “x” укажем адрес ячейки  C6.

в)  В окне  “Известные значения  y”  укажем адрес блока ячеек  C3:L3.

г)  В окне  “Известные значения  x”  укажем адрес блока ячеек  C2:L2.

Шаг   четвертый:

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