Файл: пособие по информатике(Часть2, EXCEL.doc

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

Контрольные вопросы

Создание макросов в Excel.
Построение графиков функций в Excelи запись этой процедуры в макрос.

ЛАБОРАТОРНАЯ РАБОТА № 2.6

Решение математических задач в EXCEL. Надстройки «подбор параметра» и «Поиск решения»

Цель работы: Научиться использовать надстройки «Подбор параметра» и «Поиск решения» для нахождения корней нелинейных уравнений и систем уравнений.

Теоретическая часть

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

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

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

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

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

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

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

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

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

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

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

Рис. 45.

Рис. 46. меню Office

и в раскрывшемся списке войти в меню Надстройки, далее активироватьПоиск решения,установив флажок против пунктаПоиск решения(рис. 47).

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

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

Рис. 48. значок «Поиск решения»

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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