Добавить материал и получить бесплатное свидетельство о публикации в СМИ
Эл. №ФС77-60625 от 20.01.2015
Инфоурок / Информатика / Другие методич. материалы / Решение различных математических задач, используя средства MS Excel.

Решение различных математических задач, используя средства MS Excel.


  • Информатика

Поделитесь материалом с коллегами:

Решение различных математических задач, используя надстройки «Подбор параметра» и «Поиск решения» в MS Excel.


Цель работы. Изучить:

  • надстройку «Подбор параметра» для нахождения корней нелинейных уравнений;

  • надстройку «Поиск решения» для нахождения корней систем уравнений.


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


Подбор параметра


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

F(x) = 0,

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


Пример 1. Решим уравнение x2 – 3 = 0, используя надстройку Подбор параметра.

В ячейку А1 вводится начальное приближение для поиска одного из корней уравнения. Лучше найти его графически, хотя можно подставить и произвольное значение (например, ноль). В ячейку В2 записывается в виде формулы левая часть решаемого уравнения. Диалоговое окно данного инструмента вызывается через меню Данные / Что-если / Подбор параметра и имеет следующий вид (рис. 2.7.1, 2.7.2):


hello_html_m5b646cc5.png


Рис. 2.7.1. Надстройка Подбор параметра


В поле Установить в ячейке вводится ссылка на ячейку, содержащую левую часть уравнения. В поле Значение непосредственно (т.е. без ссылок на ячейки) вводится правая часть уравнения. Причем правая часть уравнения должна обязательно представлять собой конкретное числовое значение. Если правая часть уравнения содержит переменную или какое-либо выражение, то такое уравнение должно быть предварительно преобразовано к равносильному виду (в общем случае, к каноническому виду F(x) = 0). Нажав кнопку ОК, получаем в ячейке А1 значение искомого корня: 1,731856.


hello_html_m6797730f.png


Рис. 2.7.2. Надстройка Подбор параметра


Поиск решения


Нелинейные уравнения также можно решать, используя надстройку Поиск решения. Для того чтобы ее подключить, следует в меню Office (рис. 2.7.3) выбрать пункт Параметры Excel (рис. 2.7.4) и в раскрывшемся списке войти в меню Надстройки, далее активировать Поиск решения, установив флажок против пункта Поиск решения (рис. 2.7.5).


hello_html_m34a396b4.png


Рис. 2.7.3 Кнопка Office


hello_html_136c57db.png


Рис. 2.7.4. Меню Office


hello_html_m1c7bb1b4.png


Рис. 2.7.5. Надстройки


После нажатия кнопки ОК соответствующий значок появится во вкладке Данные (рис. 2.7.6).


hello_html_m66b2fe7b.png


Рис. 2.7.6. значок Поиск решения


Пример 2. Решим уравнение x2 – 3 = 0, используя надстройку Поиск решения.

В ячейку А1 заносится начальное приближение корня, в ячейку В1 – левая часть уравнения в виде формулы. Для предыдущего примера она имеет вид =А1*А1-3.

Далее из вкладки меню Данные запускается надстройка Поиск решения.

В открывшемся диалоговом окне Поиск решения устанавливается целевая ячейка $B$1, равная нулевому значению. В текстовом поле Изменяя ячейки устанавливается адрес $А$1 и нажимается кнопка Выполнить (рис. 2.7.7).

hello_html_m1b3d3828.png


Рис. 2.7.7. Надстройка Поиск решения


В ячейке А1 получается значение корня 1,732051 (рис. 2.7.8).


hello_html_6b50a5b0.png


Рис. 2.7.8. Результаты работы надстройки Поиск решения


Как видим, оно совпало с точностью до 0,001 с найденным ранее значением.

Обращает на себя внимание неточность решения. Мы получаем очень близко приближающиеся к точным, но все же неточные корни уравнения. Это происходит потому, что решение уравнений на вычислительной технике происходит не аналитическими методами, как это делает человек, а специально разработанными методами, получившими название численных. В отличие от аналитических (точных) методов численные методы обладают определенной погрешностью. В Excel с целью повышения точности решения пользователь может уменьшить погрешность вычислений, но при этом может потребоваться увеличение количества итераций. При этом надо помнить, что тем самым увеличивается время на поиск решения. Установленные по умолчанию значения подходят для большинства практических задач, относительная погрешность вычислений составляет 0,001 (рис. 2.7.9).


hello_html_m7990dc99.png


Рис. 2.7.9. Изменение погрешности


Следует отметить, что найден только один из двух корней данного уравнения. Для нахождения второго корня, следует в ячейку А1 ввести новое приближение, близкое ко второму корню, и повторить поиск решения.


Пример 3. Решим систему уравнений, используя надстройку Поиск решения.

Для того, чтобы использовать рассматриваемую надстройку Поиск решения для нахождения решения системы линейных алгебраических уравнений, следует ввести в столбец А начальное приближение для значений всех неизвестных. Пусть это будут нули. В столбец В ввести формулы, описывающие левые части уравнений. В столбец С вводят значения правых частей уравнений. Курсор ставят на ячейку В1 и запускают надстройку Поиск решения. Значение целевой ячейки $B$1устанавливают равным значению ячейки С1. Изменяют значения ячеек столбца А. К ограничениям добавляют все уравнения, кроме первого. Для системы уравнений:

hello_html_m5fb98ada.gif

настроенный на показ формул лист Excel с диалоговым окном Поиск решения будут выглядеть так, как это показано на рисунках 2.7.10, 2.7.11.


hello_html_72871e0.png


Рис. 2.7.10. добавление ограничения


hello_html_6e3d679b.png


Рис. 2.7.11. Поиск решения системы уравнений


Нажав кнопку Выполнить, получается в столбце А значение неизвестных (рис. 2.7.12):

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



hello_html_7cf5881.gif.

hello_html_4410898d.png

Рис. 2.7.12. Результаты работы с надстройкой Поиск решения




Задания для выполнения


Варианты заданий для работы приведены в таблице 2.7.1, 2.7.2.


Задание1.

  1. Используя надстройку «Подбор параметра», найти все корни уравнения (по вариантам) на отрезке [-2; +2] (табл. 2.7.1).

Таблица 2.7.1

Варианты заданий

№ варианта

Задание

№ варианта

Задание

1

hello_html_3a960f75.gif

11

hello_html_6c662d36.gif

2

hello_html_203fd0b4.gif

12

hello_html_47be3470.gif

3

hello_html_m7b491fbf.gif

13

hello_html_m709ac3b2.gif

4

hello_html_6fdd6773.gif

14

hello_html_m408e7e39.gif

5

hello_html_1bd4a6ae.gif

15

hello_html_5ad7e0b.gif

6

hello_html_m63920348.gif

16

hello_html_m6add3cfd.gif

7

hello_html_63bccaf3.gif

17

hello_html_b881835.gif

8

hello_html_a9d494.gif

18

hello_html_m3cee0a5.gif

9

hello_html_725edd7d.gif

19

hello_html_m483912e1.gif

10

hello_html_49872245.gif

20

hello_html_m1e1386d3.gif


Задание 2.

  1. Используя надстройку «Поиск решения», решить систему линейных уравнений AX = B (по вариантам) (табл. 2.7.2) и проверить правильность решения в Excel, подставив найденные значения неизвестных в систему уравнений. A – матрица коэффициентов при x1, x2, x3, x4. В- матрица свободных членов уравнений.


Таблица 2.7.2

Варианты заданий

№ варианта

Задание

№ варианта

Задание

1

2

3

4

1

hello_html_mce438ca.gif

9

hello_html_m589fe5b8.gif

2

hello_html_59bc9e7c.gif

10

hello_html_124097c4.gif

3

hello_html_1fb913ea.gif

11

hello_html_m950709b.gif

4

hello_html_m39dc78a1.gif

12

hello_html_m4037887.gif

5

hello_html_22316f3b.gif

13

hello_html_76a8eeae.gif

6

hello_html_4475b2a2.gif

14

hello_html_6a003099.gif

7

hello_html_m33f4b6e.gif

15

hello_html_m9f2a1a7.gif

8

hello_html_75216196.gif

16

hello_html_m67f28cf7.gif




Автор
Дата добавления 27.01.2016
Раздел Информатика
Подраздел Другие методич. материалы
Просмотров614
Номер материала ДВ-383326
Получить свидетельство о публикации

Похожие материалы

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