Добавление полученных расчетных значений на диаграмму.

Для этого на диаграмме выделяются экспериментальные значения, щелчком правой кнопки мыши вызывается контекстное меню и в нем выбирается команда Исходные данные. В появившемся окне, на вкладке Ряд, добавляется фраза Рассчитанные значения (Рис. 22.12). В результате диаграмма примет вид, изображенный на рис. 22.13. Аналогично с помощью линии тренда можно подобрать и параметры других типов зависимостей (линейной, логарифмической и экспоненциальной и т. д.).

 

 

Рис. 22.12

Рис. 22.13

ПРИМЕР 8.3. В результате эксперимента получена зависимость z(t):

t	0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
z	38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Подобрать коэффициенты зависимости z(t)=At⁴+Bt³+Ct²+Dt+K методом наименьших квадратов.

Эта задача эквивалентна задаче нахождения минимума функции пяти переменных

(22.13).

Вводится табличная зависимость в рабочий лист MS Excel и построится график функции (Рис.22.15).

Процесс решения задачи оптимизации.

Пусть значения А, В, С, D и К хранятся в ячейках K1:K5. В ячейку B23 введится значение функции At⁴+Bt³+Ct²+Dt+K в первой точке (ячейка B1):

B23=$K$1*B1^4 +$K$2*B1^3 +$K$3*B1^2 +$K$4*B1 +$K$5.

Получается ожидаемое значение (в начале 0) в точке B1. Затем методом копирования распространяется эта формула на весь диапазон B23:J23. В ячейку B24 введится формула, вычисляющая квадрат разности между экспериментальными и расчетными точками:

B24=(B23-B2)^2,

и копируется на диапазон B24:J24. Выделяется ячейка В25 для хранения суммарной квадратичной ошибки (см. формулу 22.13). В нее введится формула:

В25 = СУММ(B24:J24).

С помощью решающего блока (Сервис®Поиск решения) можно решить задачу оптимизации без ограничений, заполнив соответствующим образом появившееся диалоговое окно (рис. 22.14).

Рис. 22.14

Результатом работы решающего блока будет вывод в ячейки K1:K5 значений параметров функции At⁴+Bt³+Ct²+Dt+K. В ячейках B23:J23 получаются ожидаемые значения функции в исходных точках. Эти точки помещаются в виде отдельной линии на графике. В ячейке B25 будет храниться суммарная квадратичная ошибка. Рис. 22.15 отображает внешний вид рабочего листа MS Excel после проведенных вычислений.

Использование решающего блока – является одним из эффективных способов реализации метода наименьших квадратов с помощью MS Excel.

Рис. 22.15