Реферат: Решение математических задач в среде Excel

1. 1.1.

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

<img src="/cache/referats/17663/image002.gif" v:shapes="_x0000_s1026">
Для вычисления производной в Excel будем использовать приведенную зависимость.

Рассмотримметодику вычисления производной на примере упражнения.

Упражнение 1

Допустимтребуется найти производную функции Y= 2x3 + x2 в точке x=3. Производная, вычисленнаяаналитическим методом, равна 60.

Длявычисления производной выполните следующие действия:

§

<img src="/cache/referats/17663/image004.jpg" v:shapes="_x0000_s1027">
табулируйтезаданную функцию в окрестности точки х=3 с достаточно малым шагом, например0,001 (см рис.)

§

в ячейку С2 введите формулу вычисления производной.Здесь ячейка В2 содержит значение хк+1, ячейка А2 — хк.

§

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

Для значения х =3 производная функции равна значению 60,019,что близко к значению, вычисленному аналитически.

1.2.

<img src="/cache/referats/17663/image006.gif" v:shapes="_x0000_s1033">
Для численноговычисления определенного интеграла методом трапеций используется формула:

Методикувычисления определенного интеграла в Excel с использованием приведенной формулырассмотрим на примере.

Упражнение 2

Пусть требуется вычислитьопределенный интеграл

Величинаинтеграла, вычисленная аналитически равна 9. Для численного вычисления величиныинтеграла с использованием приведенной формулы выполните следующие действия:

§

табулируйтеподинтегральную функцию в диапазоне изменения значений аргумента 0 – 3 (см.рис.).

§

в ячейку С3введите формулу =(A3-A2)*B2+(A3-A2)*(B3-B2)/2+C2, которая реализует подинтегральнуюфункцию.

§

<img src="/cache/referats/17663/image010.jpg" v:shapes="_x0000_s1032">
Скопируйтебуксировкой формулу, записанную в ячейке С3 до значения аргумента х = 3.Вычисленное значение в ячейке С17 и будет величиной заданного интеграла — 9.1.3.

Еслифункция F(x) непрерывна на отрезке [a, b] и имеет внутри этого отрезкалокальный экстремум, то его можно найти используя надстройку Excel Поиск решения.

Рассмотримпоследовательность нахождения экстремума функции на примере следующегоупражнения.

Упражнение 3

Пустьзадана неразрывная функция Y= X2+X+2. Требуется найти ее экстремум (минимальное значение).

Для решения задачи выполните действия:

§

В ячейку А2рабочего листа введите любое число принадлежащее области определения функции, вэтой ячейке будет находиться значение Х;

§

В ячейку В2введите формулу, определяющую заданную функцию. Вместо переменной Х в этойформуле должна быть ссылка на ячейку А2: =A2^2+ A2 +2

§

Выполните команду меню Сервис/Поиск решения;

§

Настройтепараметры инструмента Поиск решения:число итераций – 1000, относительная погрешность 0,00001.

§

в поле Установить целевую ячейку укажите адресячейки, содержащей формулу ( А2), установите переключатель Минимальномузначению, в поле Изменяя ячейкивведите адрес ячейки, содержащей Х (А2);

§

Щелкните накнопке Выполнить. В ячейке А2 будет помещено значение Х функции, при которомона имеет минимальное значение, а в ячейке В2 – минимальное значение функции.

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

1.4. 1.4.1.

В библиотеке Excel в разделе математических функций естьфункции для выполнения операций над матрицами (табл.1.1).

Таблица STYLEREF 1 s 1SEQ Таблица * ARABIC s 1 1

Русифицированное имя функции

Англоязычное имя функции

Выполняемое действие

МОБР (параметр)

MINVERSE (parametr)

обращение матрицы

МОПР (параметр)

MDETERM (parametr)

вычисление определителя матрицы

МУМНОЖ (список параметров)

MMULT (parametrlist)

Умножение матриц

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

МОБР (А1: B2) или МОПР (матрица_1).

1.4.2.

Известно,что система линейных уравнений в матричном представлении записывается в виде:

AX=B.

Решениетакой системы записывается в виде

X=A-1B,

ГдеA-1 –матрица, обратная по отношению к А.

1.4.3.

<img src="/cache/referats/17663/image013.gif" v:shapes="_x0000_s1035 _x0000_s1036 _x0000_s1037">
Пусть системауравнений задана матрицами:

Для решения задачи выполнитедействия:

·

Выделите диапазон размерностью 2 х 2 и присвойте емуимя А;

·

Выделите диапазон размерностью 1 х 2 и присвойте емуимя В;

·

Выделите диапазон размерностью 1 х 2 и присвойте емуимя Х;

·

Используя список имен выделите диапазон А и введите в него значения элементов матрицы А;

·

Используя список имен выделите диапазон В и введите внего значения элементов вектора В;

·

Используя список имен выделите диапазон Х дляпомещения результата решения системы;

·

В выделенный диапазон Х введите формулу

=МУМНОЖ(МОБР(А); В);

·

Укажите Excel, что выполняется операция над массивами,для этого нажмите комбинацию клавиш ++, вячейках диапазона Х будет получен результат: х1=2,16667, х2= — 1,33333

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

Упражнение 4

Решите систему уравнений вида AX=B и выполните проверку решения

1.5.

Используявозможности Excel можно находить корни нелинейного уравнения в допустимойобласти определения переменной. Последовательность операций нахождения корней следующая:

1.

Уравнение представляется в виде функции однойпеременной;

2.

Производится табулирование функции в диапазоневероятного существования корней;

3.

По таблице фиксируются ближайшие приближения кзначениям корней;

4.

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

Рассмотрим последовательность отыскания корнейнелинейного уравнения на примере.

Упражнение 5

Требуетсянайти все корни уравнения X3-0,01X2-0,7044X+0,139104=0на отрезке [-1; 1]. Правая часть уравнения представлена полиномом третьейстепени, следовательно, уравнение может иметь не более трех корней.

1.

представимуравнение в виде функции

Y = X3-0,01X2-0,7044X+0,139104

Известно, что корни исходногоуравнения находятся в точках пересечения графика функции с осью Х.

2.

Для локализации начальных приближений необходимо определитьинтервалы значений Х, внутри которых значение функции пересекает ось абсцисс,т.е. функция меняет знак. С этой целью табулируем функцию на отрезке [–1;+1] сшагом 0,2, получим табличные значения функции. Из полученной таблицы находим,что значение функции трижды пересекает ось Х, следовательно, исходное уравнениеимеет на заданном отрезке все три корня.

3.

Анализ таблицы показывает, что функция меняет знак вследующих интервалах значений аргументаХ: (-1;-0,8), (-0,2;0,4) и (0,6;0,8). Поэтому в качестве начальных приближений возьмем значения Х: -0,8; -0,2и 0,6 .

4.

На свободном участке рабочего листа, как показано нарисунке, в ячейки А15: A17 введите начальные приближения, а соответствующиеячейки столбца В скопируйте формулу.

5.

Выполните команду меню Сервис/Параметры, во вкладкеВычисления установите относительную погрешность вычислений E=0,00001, а число итераций N=1000, установитефлажок Итерации.

6.

Выполните команду меню Сервис/Подбор параметра. В диалоговом окне заполните следующиеполя:

Установить вячейке: в поле указывается адрес ячейки, в которой записана формулаправой части функции;

Значение: в полеуказывается значение, которое должен получить полином в результате вычислений,т.е. правая часть уравнения (в нашем случае 0);

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

Послещелчка на ОК получим значение первого корня: -0,92.

Выполняяпоследовательно операции аналогичные предыдущим, вычислим значения остальныхкорней: -0,209991 и 0,720002.

1.6.

Применяянадстройку Excel Поиск решения можно решать системы нелинейных уравнений.Предварительно система уравнений должна быть приведена к одному уравнению. Рассмотримпоследовательность решения на примере упражнения.

Упражнение 6

<img src="/cache/referats/17663/image023.gif" v:shapes="_x0000_s1030">
Дана система двухуравнений:

Требуется найти все корниприведенного уравнения для диапазона значений х и y [-3; 3].

Шаг 1. Приведемсистему к одному уравнению. Пара (x, y) является решением системы тогда итолько тогда, когда она является решением следующего уравнения с двумя неизвестными:

(x2 + y2– 3)2 + (2x + 3y – 1)2 = 0

Шаг 2.Для решения последнего уравнениянеобходимо найти начальные приближения, для этого табулируем выражение, стоящеев левой части как функцию по двум переменным x и y. Для табуляции функциивыполните следующие действия:

§

В столбец Авведите последовательность значений Х с шагом 0,5, а строку 3 – последовательностьзначений У также с шагом 0,5.

§

Присвойтедиапазонам значений Х и У имена Х и У, соответственно.

§

Выделитедиапазон ячеек, в котором будут вычисляться значения функции (B4:N16).

§

Ввыделенный диапазон введите формулу

=(Х^2+Y^2-3)^2+(2*Х+3*Y-1)^2.

§

Нажавкомбинацию клавиш [Ctrl]+[Shift]+[Enter] выполните операцию над выделенныммассивом. В выделенном диапазоне появятся вычисленные значения функции.

Шаг 3. Найдем начальные приближения.Поскольку табулируемая функция задает поверхность, то начальные приближенияследует искать во впадинах, т.е. в точках, где функция принимаетнаименьшие значения. На рисунке эти точки затемнены. Начальными приближениямиявляются пары (-1;1) и (1,5; -0,5).

Введите значения найденных приближений в смежные ячейкирабочего листа ( см. рис.). Над столбцами сделайте надписи XX и YY, которыебудут выполнять в формулах роль меток. Обратите внимание, что мы ужеиспользовали имена Х и Y, поэтому имена новых меток должны отличаться.

Шаг 4. В ячейку строки, в которойзаписана первая пара Х и У введите формулу, вычисляющую значение функции:

=(XX^2+YY^2-3)^2+(2*XX+3*YY-1)^2

и скопируйте ее в следующую строку.

Шаг 4. Установите курсор на ячейку, вкоторой записана формула и выполните команду меню Сервис/Поиск решения. Выполните настройку параметров инструментаПоиск решения: Предельное число итераций – 1000, относительная погрешность0,000001.

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

Повторите такие же операции для второй пары приближений.

<img src="/cache/referats/17663/image025.jpg" v:shapes="_x0000_s1031">
Решениемсистемы являются пары (-1,269; 1,1791) и (1,5764; -0,718).

Задания для самостоятельнойработы

1.

Найти корни уравнения:Вариант

Уравнение

Ответ

Sin(x)e-2x = 0 для значений х [-2;2]

Х = 0

X3-2,56x2-1,3251x+4,395006=0

X=-0,94644

X3-2,92x2+1,4355x+0,791136=0 для х [-3;3]

-0,32; 1,229997; 2,010001

x3-2,84x2-5,6064x-1476336 = 0

4,700766

X3+1,41x2-5,4724x-7,380384 = 0

3,542723

2.

Найти корни линейногоуравнения вида Ах=В и выполнитьпроверку: Вариант1 Вариант2 <img src="/cache/referats/17663/image029.gif" v:shapes="_x0000_s1045 _x0000_s1046 _x0000_s1047">
Вариант 3 Вариант 4

3.

Найти производную функции:

a)

Y = 2x2 при х = 3

b)

Y= Sin(x) для х= 0

c)

Y = Cos(x) для х = 0

d)

Y= Sin(x) для х= Пи/2

e)

Y = Cos(x) для х = Пи/2

f)

Y= Tg(x) для х= 0

4.

<img src="/cache/referats/17663/image034.gif" align=«left» hspace=«12» v:shapes="_x0000_s1051"><img src="/cache/referats/17663/image036.gif" align=«left» hspace=«12» v:shapes="_x0000_s1052">Вычислить определенный интеграл:

А) В)

<img src="/cache/referats/17663/image038.gif" align=«left» hspace=«12» v:shapes="_x0000_s1054">С) D)

5.

Найти экстремум функции:

a)

Y = (2 – x)2

b)

Y = x2 + y2 – 3

c)

Y = (x-2)2 +(y+3)2-6

d)

Y = sin(2x) длях [0; Пи/2]