Лабораторная работа №9. Excel. Надстройка «Поиск решения».

В Excel имеется надстройка " Поиск решения" , которая позволяет решать задачи отыскания наибольших и наименьших значений', а также решать уравнения. Сначала убедитесь, что Excel использует надстройку " Поиск решения". В меню " Сервис" найдите пункт " Поиск решения". Если он есть— все в порядке. Если его нет, нужно установить эту надстройку. Для этого выберите в меню пункт " Сервис ► Надстройки ". В диалоговом окне найдите в списке надстроек " Поиск решения " и установите, слева от него флажок. (Посмотрите заодно, все ли установленные надстройки Вам нужны, и снимите флажки у лишних). Загрузится Решатель.

Имена ячеек.

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

1. Выделите нужную ячейку или диапазон, затем щелкните на поле Имя (в левой части строки формул) и введите имя. Нажмите Enter.

2. Выберите Вставка ► Имя ► Присвоить или нажмите комбинацию клавиш < Ctrl+F3>. Появится диалоговое окно Присвоение имени. Адрес выделенной ячейки или диапазона появится в поле Формула. Затем щелкните на кнопке ОК.

Если такое имя уже существует, поле Имя нельзя использовать для переназначения этого имени другому диапазону или ячейке.

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

Решение уравнений.

Графический метод.

Решить уравнение . Запишем уравнение в виде .

11 В поиске решения подставляем различные значения переменной x, вычисляем значение функции f(x) и строим график зависимости f(x) от x. Корнями будут значения переменной x, при которых f(x)=0.

12 Сначала введем в электронную таблицу столбец значений x в интервале 0 ≤ x ≤ 5, шаг между значениями выберем равным 0, 4.

Таблица 8. Исходные данные для построения графика.

x	f(x)
0, 0	12, 00
0, 4	5, 26
0, 8	-1, 09
1, 2	-6, 67
1, 6	-11, 10
2, 0	-14, 00
2, 4	-14, 98
2, 8	-13, 65
3, 2	-9, 63
3, 6	-2, 54
4, 0	8, 00
4, 4	22, 38
4, 8	40, 99

13 Построим точечную диаграмму.

Рис. 15. График функции.

14 Из графика видно, что корни уравнения находятся возле значений x ≈ 0, 8 и x ≈ 3, 7. Для получения более точных результатов нужно сузить интервал значений x. Например, чтобы с большей точностью найти значение корня возле точки x=0, 8, можно вычислить значение функции при значениях аргумента в интервале от 0, 6 до1, 0.

15 В полиноме третьей степени может быть три корня, а мы вычислили только два. Расширим область значений аргумента.

Таблица 9.

x	f(x)
-5, 0	-28, 00
-4, 6	-7, 14
-4, 2	9, 31
-3, 8	21, 73
-3, 4	30, 50
-3, 0	36, 00
-2, 6	38, 62
-2, 2	38, 75
-1, 8	36, 77
-1, 4	33, 06
-1, 0	28, 00
-0, 6	21, 98
-0, 2	15, 39
0, 2	8, 61
0, 6	2, 02
1, 0	-4, 00
1, 4	-9, 06
1, 8	-12, 77
2, 2	-14, 75
2, 6	-14, 62
3, 0	-12, 00
3, 4	-6, 50
3, 8	2, 27
4, 2	14, 69
4, 6	31, 14
5, 0	52, 00

Рис. 16. Уточненный график.

17 Значение третьего корня x ≈ -4, 5.

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

18 В ячейку С2 введите начальное приближение корня 3, 6.

19 В ячейку С3 введите формулу = A2^3-17* A2+12.

A	B	C

Начальное значение	3, 6
Формула	-2, 544

20 Запустите надстройку Поиск решения, дав команду Сервис ► Поиск решения.

21 В диалоговом окне Поиск решения, в поле Установить целевую ячейку выбрана ячейка С2, содержащая формулу. Установить переключатель Равной 0. Нажать кнопку Предположить. В окне предположения установить $С$3.

22 Нажать кнопку Выполнить. Откроется окно Результаты поиска решения. Нажать кнопку Сохранить сценарий.

23 В открывшемся окне задать имя сценария Корень.

24 Получить отчеты.

Microsoft Excel 10.0 Отчет по результатам
Рабочий лист: [Поиск решения.xls]Методы решения уравнений
Отчет создан: 19.04.2006 12: 46: 44


Целевая ячейка (Значение)
	Ячейка	Имя	Исходное значение	Результат
	$C$3	Формула f(x)	-2, 544	-1, 555E-07


Изменяемые ячейки
	Ячейка	Имя	Исходное значение	Результат
	$C$3	Начальное значение f(x)	3, 6	3, 7102136


Ограничения
	НЕТ


Microsoft Excel 10.0 Отчет по устойчивости
Рабочий лист: [Поиск решения.xls]Методы решения уравнений
Отчет создан: 19.04.2006 12: 46: 44


Изменяемые ячейки
			Результ.	Нормир.
	Ячейка	Имя	значение	градиент
	$C$2	Начальное значение f(x)	3, 7102136
Ограничения
	НЕТ

Самостоятельная работа.

1.
2.
3.
4.
5.
6.

8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.

1 2 3 456