Файл: Лабораторная работа 1 Некоторые специальные возможности Excel Цель работы.pdf

Лабораторная работа № 1
Некоторые специальные возможности Excel
Цель работы:
Изучить возможности надстроек: поиска решения и подбор параметра.
Решить уравнение с помощью подбора параметра и поиска решения.
Решить систему уравнений с помощью поиска решения.
1.1
Автоформат
Команда Автоформат позволяет быстро оформить готовую таблицу воспользовавшись готовым Списком форматов.
Для того что бы пользоваться данной командой, необходимо добавить кнопку "
Автоформат" на панель быстрого доступа:
1)
Щелкните стрелку на панели быстрого доступа и выберите элемент Другие команды.
2)
В открывшемся окне Параметры Excel в поле "Выбрать команды из" выберете из списка "Все команды".
3)
В списке выберите кнопку "Автоформат" и щелкните элемент "Добавить".
Рис.1. Автоформат
1.2.
Решение уравнений
1
.2.1. Решение уравнений с помощью функции «Поиск решения»
Добавить надстройку Поиск решения можно следующим образом: Кнопка

«Office» / Параметры Excel. В появившемся диалоговом окне выбрать пунк
Надстройки/ Поиск решения/Перейти,затем установить флажек напротив надстройки поиск решения и подтвердить ОК.
Одной из интереснейших особенностей Excel является возможность нахождения корней уравнений типа f(x)=a. Для этого необходимо в какой-либо из ячеек ввести предполагаемое (начальное) значение корня уравнения, в другой ячейке записать само уравнение f(x), выполнить команду Анализ | Поиск решения....В окне Поиск
решения,в поле Установить целевую ячейку записать адрес ячейки где записано уравнение f(x), в поле Равной: | Значению ввести значение a, в поле Изменяя
ячейки ввести адрес ячейки где находится предполагаемое(начальное)значение корня уравнения и нажать кнопку Выполнить.
После этого в ячейке, где находилось предполагаемое (начальное) значение корня уравнения, появится вычисленное значение корня уравнения с точностью заданной в разделе Параметры.
Если уравнение имеет не один, а множество корней, то остальные корни можно получить изменяя начальные значения корня уравнений.
Примечание: Начальное значение необходимо подбирать с определенной осторожностью,если функция, в которой ищутся корни, имеет различные особенности, так как при поиске решения начальное значение может находиться в области отрицательных градиентов, в результате чего«спираль» поиска может быть не сходящейся, а расходящейся.
Рис.2. Решение уравнения в Excel
1
.2.2. Решение уравнений с помощью функции «Подбор
параметра»
При моделировании экономических ситуаций часто приходится решать уравнение вида: f (x, p
1
, p
2
,…, p n
)=0,
(1) где f-заданная функция; х – неизвестная переменная; p
1
, p
2
,…, p n
– параметры модели.
Решение таких уравнений может быть как самостоятельной, так и частью более сложных задач. Как правило, исследователя интересует поведение решения в зависимости от параметров p k
, k=1,n
Решениями или корнями уравнения (1) называют такие значения переменной х, которые при подстановке в уравнение обращают его в тождество.
Только для линейных или простейших нелинейных уравнений удается найти

решение в аналитической форме, т.е. записать формулу, выражающую искомую величину х в явном виде через параметры pk (например формула корней квадратного уравнения).
В большинстве же случаев приходится решать уравнение (1) численными методами, в которых процедура решения задается в виде многократного применения некоторого алгоритма. Полученное решение всегда является приближенным, хотя может быть сколь угодно близко к точному.
Рассмотрим последовательность действий для получения решения нелинейного уравнения в среде электронной таблицы.
Пусть надо решить уравнение вида:
(x
2
/(x
2
+16))=1
(2)
Сформируем лист электронной таблицы, как показано на рисунке. Уравнение (2) запишем в клетку B2, начиная со знака равенства, а вместо переменной x укажем адрес клетки A2, которая содержит значение начального приближения решения.
Рис. 3.
Метод, применяемый в EXCEL для решения таких уравнений
– модифицированный конечными разностями метод Ньютона, который позволяет не сильно заботится о начальном приближении, как этого требуют другие численные методы решения уравнений (метод хорд, дихотомии и др.) Единственно, что следует учесть – это то, что будет найдено решение ближайшее к выбранному начальному приближению.
Для получения решения уравнения (2) надо выполнить следующую последовательность действий:
1.
Выполнить команду Данные/Работа с данными/Анализ «что-если»/Подбор
параметра (рис. 4);
Рис.4. Окно «Подбор параметра»
2.
Заполнить диалоговое окно Подбор параметра...:
2.1.
Щелкнуть левой клавишей мыши в поле «Установить в ячейке»,после появления в нем курсора, переместить указатель мыши и щелкнуть на клетке с формулой, в нашем случае это клетка B2, абсолютный адрес которой $B$2.
Этот адрес можно было бы набрать на клавиатуре, после появления курсора в поле.
2.2.
В поле «Значение» установить0.

2.3.
В поле «Изменяя значение ячейки» ввести адрес клетки,где задано начальное приближение решения, в нашем случае это клетка A2 (абсолютный адрес которой $A$2 появится в поле после щелчка левой клавиши мыши на клеткеA2).
После нажатия на кнопке ОК появится окно «Результат подбора параметра»
(рис.5).
Рис.5. Окно «Результат подбора»
1.3.
Создание сценариев
Часто встречается необходимость одним и тем же ячейкам присвоить разные значения, причем так, чтобы не потерять предыдущие значения. В Excel это выполняется за счет механизма сценариев, вызов которого производится через меню
Данные/Работа с данными/Анализ «что-если»/Диспетчер сценариев.Откроется окно в котором будет список всех имеющихся сценариев (вначале этот список пуст). Нажмите кнопку "Добавить", появится окно.
Рис.6. Окно создания сценария
В этом окне надо:
1. дать имя сценарию;
2. указать те ячейки, которые будут изменяться данным сценарием;
3. написать комментарии к данному сценарию в поле Примечание;
4. нажать кнопку ОК.

Рис.7. Диспетчер сценариев
После этого появится новое окно, где нужно ввести значения для указанных ячеек.
Описанную операцию нужно повторить несколько раз для создания нескольких. Для того, чтобы заполнить ячейки значениями из конкретного сценария, нужно выбрать его в списке и нажать кнопку Вывести.
1.4.
Отчет по сценариям.
После того, как сценарии созданы, можно создать отчет по этим сценариям. Для этого используется кнопка Отчет.
В окне Отчет по сценарию укажите: Тип отчета | Структура, а в поле Ячейки
результата -укажите адреса ячеек(разделенные знаком"точка с запятой")в которых содержатся итоги (например: I13;I19;I25;I31;I38).
После этого будет автоматически создана новая страница с отчетом по исходным данным сценариев и ячейкам результатов.

Экспериментальная часть
С помощью надстроек “Подбор параметра” и “Поиск решения” решить следующие уравнения и создать сценарий:
1.
2 2
5 0
kx
kx
2. sin( )cos( ) 2
x
x
kx
k
3.
3 2
cos( )
2
x
x
kx
k
4.
5 2
2
kx
x
x
k
5.
20 sin cos
x
x
k
6.
6 2
5 2
3
x
x
k
7.
2 2
sin cos
x
x
k
8.
2 3
1 k
x
9. sin cos
22
k
x
x
10.
2 1
3 5
4
k
x
x
11.
1 2cos2x
k
12.
2 2
5 1
x
k
x
13.
1 1 sin 2
k
x
14.
2 2
4 5
9
x
x
k
x
15.
2
sin cos
x
x
k
x
16.
2 4
x
y
k
xy
k
17.
2 5
17 2
x
y
y x
18.
15 34
x
y
xy
19.
2,1 4,5 13,5 3,7 0,1 0,3
x
у
х
у
20.
2 2
4 5
9
x
x
k
x
21.
k
x
x
x
2 2
9 2
3 22.
k
x
x
x
2 2
4 3
4 23.
2 8
y
x
y
x
24.
1 4
2
y
x
y
x
25.
1 11 5
6
xy
y
x
26.
k
x
1 4
2 27.
k
x
x
x
2 3
2 28.
k
x
x
2 2
2 4
29.
k
x
2
cos
1 1
30.
k
x
x
xcos sin
2
где k – номер варианта.
Таблица 1 – Варианты заданий
Номер варианта Номер формулы
Номер варианта
Номер формулы
1 1
15 11 11 25 2
2 16 12 12 26 3
3 17 13 13 27 4
4 18 14 14 28 5
5 19 15 15 29 6
6 20 16 16 30 7
7 21 17 1
18

Номер варианта Номер формулы
Номер варианта
Номер формулы
8 8
22 18 2
19 9
9 23 19 3
17 10 10 24 20 4
16