Электронная таблица Excel (стр. 8 )

2.  В чем заключается суть метода наименьших квадратов?

3.  Запишите уравнения линейной и квадратичной зависимостей.

4.  Как построить графики функций линейной и квадратичной зависимостей?

5.  Как построить линию тренда и вывести на график уравнение регрессии и значение коэффициента детерминированности?

6.  Какие функции Excel могут использоваться для определения коэффициентов эмпирических формул.

7.  Запишите уравнения регрессии для линейной и логарифмической функций.

8.  Запишите уравнение квадратичной (степенной) регрессии и поясните как ее можно реализовать с помощью функции ЛИНЕЙН.

6.6. Решение алгебраических и

нелинейных уравнений

Нелинейные уравнения вида F(х)=0 принято называть алгебраическими, если они содержат только алгебраические функции и трансцендентными, если они содержат другие функции (тригонометрические, показательные, логарифмические и т. д.).

Корнем уравнения f(х)=0, где f(х) - функция непрерывная и дифференцируемая на отрезке [а, b] и в граничных точках, называется всякое число c, принадлежащее отрезку [а, b], такое, что f(с)=0.

Процесс нахождения корней состоит из двух этапов: отделения корней и уточнения значения корней на отрезках отделения с заданной точностью.

Отделением корней называется процесс выделения из области определения функции f отрезков [а, b], в каждом из которых содержится один и только один корень уравнения f(х)=0.

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

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

Под уточнением значения корня с заданной точностью h понимают сужение границ отрезка [а, b] до длины, не превосходящей h. Уточнение значения корня на отрезке отделения осуществляется различными методами одномерной поисковой оптимизации: простых итераций, деления отрезка пополам, касательных, хорд, наискорейшего спуска и др.

Для уточнения значения корня в Excel могут применяться все названные методы. В настоящем пособии рассмотрены три метода:

- простое табулирование;

- метод простых итераций;

- метод Ньютона (метод касательных).

Метод простого табулирования не имеет ограничений, но малоэффективен, требует большого числа ручных операций. Условием окончания процедуры поиска является достижение функцией f(х) заданного значения: f(xi)<е, - где е - заданные требования к точности поиска корня.

Метод простых итераций более эффективен. Он сходится, если f'(х)<0. Для его реализации необходимо функцию f(x)=0 преобразовать к рекуррентному виду

xi+1=j(xi ) (6.16)

Табулировать необходимо правую часть выражения (2.1). Для первой формулы в качестве аргумента используется начальное приближение Хо (как правило, одна из границ отрезка отделения), для последующих формул - значение корня на предыдущем шаге, т. е. f(xi). Начальное приближение выбирается произвольно. Условие окончания процедуры вычисления j(xi+1)- j(xi)<e.

Метод Ньютона самый эффективный метод. Он обеспечивает сходимость за минимальное число шагов. Однако этот метод накладывает серьезные ограничения на вид функции. Функция должна быть дважды дифференцируема. Для поиска корня в этом методе, также как и в методе простых итераций, составляется рекуррентная формула:

xi+1=xi - f(xi)/f'(xi). (6.17)

Табулировать необходимо правую часть выражения. Начальное приближение выбирается на одной из границ отрезка отделения корня. В качестве начального приближения xо выбирается граница b, если f'(x)*f"(x)>0, и граница а, если f'(x)*f"(x)<0. Для первой формулы в качестве аргумента используется начальное приближение xо, для последующих формул - значение корня на предыдущем шаге, т. е. f(xi). Условием окончания процедуры уточнения корня является достижение функцией значения меньше заданного - f(xi+1)<=e.

Приближенное значение производных можно вычислить численно с помощью формул (6.6), (6.7):

Значение Dх можно принять в интервале от 0.0001 до 0.00001

Все указанные методы могут быть реализованы с помощью функций пользователя, разработанных с помощью встроенного языка программирования Visual Basic for Application (VBA).

Пример 6.6. Пример отделения и уточнения корня

Пусть требуется найти корни уравнения y=2^x+2x-5 с точностью 0,001.

Загрузите электронную таблицу.

1. Отделите корни уравнения. Для этой цели протабулируйте функцию на значительном отрезке с большим шагом и зафиксируйте границы смены знака функции. После нескольких шагов получим отрезок отделения [1.28 – 1.285]. Выберите шаг 0,0005 и протабулируейте функцию вновь. Получен новый интервал где функция меняет свой знак [1,283; 1,2835] (Листинг 6.15). На границе отрезка при x=1.283 значение функции равно 0,000555. То есть функция меньше заданной точности. Эту точку и примем в качестве значения корня заданной функции. Результат: х=1.283, f(x)=0,000555

Листинг 6.15. Решение алгебраических и нелинейных уравнений
	А	B	C	D	E	F
1	Решение нелинейных уравнений y=2^x+2x-5
2
3	1. Простое табулирование	2. Метод итераций	3. Метод Ньютона
4	Начальное значение	0	Нач. знач.	1,28	Нач. знач.	1,28
5	Шаг табуляции	0,0005	Шаг таб.	нет	Шаг таб.	нет
6	Точность	0,001	Точность	0,001	Точность	0,001
7	Формула y=2^x+2x-5	y=2^x+2x-5	y=2^x+2x-5
9		Рекуррентная формула x=(5-2^x)/2	x=x-(2^x+2x-5)/ (2* 2^x* LN(2)+2)
10	Х	Функция f(x)	Функция j(xi)	Разность ABS(Xi+1 - xi)	Функция xi - f(xi)/f’(xi)
11	1.28	-.011610	1,285805		1,282163	-0,00364
12	1.2805	-.009768	1,28091	0,0048955	1,282841	-0,00114
13	1.281	-.007926	1,285039	0,0041297	1,283053	-0,00036
14	1.2815	-.006084	1,281557	0,0034828	1,28312	-0,00011
15	1.282	-.004241	1,284494	0,0029379	1,283141	-3,5E-05
16	1.2825	-.002399	1,282017	0,0024778
17	1.283	-.000555	1,284107	0,0020901
18	1.2835	.0012882	1,282344	0,0017628
19	1.284	.0031320	1,283831	0,0014869
20	1.2845	.0049761	1,282577	0,0012541
21	1.285	.0068205	1,283635	0,0010578
22			1,282742	0,0008922

2. Уточним значение корня на отрезке отделения [1.283; 1.2835] методом итераций:

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

x=(5-2^x)/2 (6.18)

- запишите в ячейку C11 формулу (6.18). В качестве х примите значение функции на левой границе отрезка отделения, то есть 1.28 (ячейка D4);

- запишите в ячейку С12 формулу (6.18), но в качестве х примите значение ячейки С11, то есть значение корня на предыдущем шаге;

- запишите в ячейку D12 формулу ABS(xi-xi+1);

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

Результат: x=1. f(x)= 0,001505.

3. Уточним значение корня на отрезке отделения [1.283; 1.2835] методом Ньютона:

- найдем первую производную для заданного выражения

f’(x)=(2* 2^x* LN(2)+2) (6.19)

xi+1=xi-(2^xi+2xi-5)/ (2* 2^xi* LN(2)+2) (6.20)

- запишите в ячейку E11 формулу (6.20). В качестве х примите значение функции на левой границе отрезка отделения, то есть 1,28 (ячейка F4);

- запишите в ячейку E12 формулу (6.20), но в качестве х примите значение ячейки E11, то есть значение корня на предыдущем шаге;

- запишите в ячейку F11 формулу (f(xi), со ссылкой на ячейку Е11;

- скопируйте формулы из ячеек Е12 и F11 в нижележащие ячейки, пока в колонке F не будет выполнено требование к точности значения функции.

Результат: x=1,283141; f(x)= -3,5E-05

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

Оптимизация

Другим способом решения линейных и нелинейных уравнений является использование возможностей программы Excel по оптимизации решений. Для этой цели служат команды Подбор параметра и Поиск решения меню Сервис. Если команды Поиск решения нет в меню Сервис, то ее необходимо загрузить командой Сервис, Надстройки. Функция Поиск решения может использоваться для решения различных задач оптимизации в том числе и задач линейного программирования.

Использование возможностей Excel по оптимизации рассмотрим на примерах.

Пример 6.7. Решим квадратное уравнение 3*х2 +2x -15 с точностью 0,0001.

Результаты решения обоими методами приведены на Листинге 6.16.

Листинг 6.16. Решение уравнений
	A	B	C	В	E	F
9		Подбор параметра
10		Функция	Корень		Функция	Корень
11	1	-4,0000E+00	-1,0000		-8,4213E-04	-1,6666
12	2	6,0000E+01	-5,0000		4,9306E-04	-1,6667
13	3	8,0000E+01	5,0000		1,5199E-05	1,0000
14
15		Поиск решения
16		Функция	Корень		Функция	Корень
17	1	-4,0000E+00	-1,0000		3,2259E-07	-1,6667
18	2	6,0000E+01	-5,0000		-4,1624E-07	-1,6667
19	3	8,0000E+01	5,0000		0,0000E+00	1,0000
20	4	6,0000E+01	-5,0000		0,0000E+00	1,0000

Порядок решения уравнения с использованием

функции Подбор параметра

Введем в ячейку В11 формулу 3*С11^2+2*C11-15. В ячейку С11 введем начальное значение х=-1. Результат приведен на листинге в столбцах В и С.