Построение различных аппроксимирующих зависимостей в MS Excel реализовано в виде свойства диаграммы - линия тренда

ПРИМЕР 9.2. В результате эксперимента была определена некоторая табличная зависимость. Построить график экспериментальных значений. Выбрать и построить график аппроксимирующей зависимости (линия тренда). С помощью формулы, подобранной аппроксимирующей зависимостью вычислить ожидаемое значение в указанных точках и добавить рассчитанные значения на диаграмму. Настроить график.

x1 = 0,1539; x2 = 0,1769; x3 = 0,1825; x4=0,1911; x5=0,22
X	0,15	0,16	0,17	0,18	0,19	0,20
Y	4,4817	4,4930	5,4739	6,0496	6,6859	7,3891

Решение задачи можно разбить на следующие этапы:

1. Ввод исходных данных и построение точечного графика. Строим и настраиваем график, как показано на рис. 9.9. Обратите внимание, что мы изменили минимальное и максимальное значение для оси OX.

Рис. 9.9

Добавление к этому графику линии тренда. Рассмотрим этот процесс подробно:

· Выделим экспериментальные точки на графике, щелкнем правой кнопкой мыши и воспользуемся командой Добавить линию тренда. Появившееся диалоговое окно (см. рис. 9.10) позволяет построить аппроксимирующую зависимость.

· На вкладке этого окна указывается вид аппроксимирующей зависимости (в нашем случае необходимо выбрать полиномиальную зависимость второй степени) и определяются параметры построения:

o Название аппроксимирующей зависимости (Квадратичная зависимость).

o Прогноз вперед (назад) на n единиц (этот параметр определяет, на какое количество единиц вперед (назад) необходимо продлить линию тренда).

o Показывать ли точку пересечения кривой с прямой Y = const (нет).

o Показывать аппроксимирующую функцию на диаграмме или нет (параметр показывать уравнение на диаграмме).

o Помещать ли на диаграмму величину среднеквадратичного отклонения или нет (параметр поместить на диаграмму величину достоверности аппроксимации).

· На рис. 9.11 изображена полученная диаграмма с формулой.

Рис. 9.10

 

Рис. 9.11

· Для расчета ожидаемых значений в точках x₁ = 0,1539; x₂ = 0,1769; x₃ = 0,1825; x₄=0,1911; x₅=0,22 введем эти значения в ячейки D2:D6. В ячейку E2 введем формулу подобранной аппроксимирующей зависимости (=371.6*D2^2-68.093*D2+6.189) и скопируем ее в ячейки E3:E6. Фрагмент рабочего листа примет вид (рис. 9.11а).

Рис. 9.11

· Добавим полученные расчетные значения на диаграмму. Для этого на диаграмме выделим экспериментальные значения, щелкнем правой кнопкой мыши и выберем команду Выбрать данные…. и кнопка «Добавить», появится окно Изменение ряда (рис. 9.12). Добавим туда имя ряда Рассчитанные значения и введем значения для X и Y.

Рис. 9.12

· Настраиваем последний график, как это делали в Примере 9.1. В результате диаграмма примет вид, изображенный на рис. 9.13. Аналогично с помощью линии тренда можно подобрать и параметры других типов зависимостей (линейной, логарифмической и экспоненциальной и т. д.).

Рис. 9.13

ПРИМЕР 9.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 методом наименьших квадратов, сведя задачу к задаче оптимизации. Определить суммарную ошибку, рассчитать точки для построения и построить график. Настроить график.

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

(9.13)

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

Рис. 9.13а

· Рассмотрим процесс решения задачи оптимизации (формула 9.13). Пусть значения А, В, С, D и К хранятся в ячейках F1:F5 (рис. 9.13б). В ячейку C2 введем значение функции At⁴+Bt³+Ct²+Dt+K для первой точки (ячейка A2): C2 = $F$1* A2^4 + $F$2* A2^3 + $F$3* A2^2 + $F$4* A2 + $F$5.

Рис. 9.13б

· Получим ожидаемое значение (в начале 0) в точке C2. Затем растянем эту формулу на весь диапазон C2:C10. У нас получились нули, т.к. коэффициенты зависимости еще не определены. В ячейку D2 введем формулу, вычисляющую квадрат разности между экспериментальными и расчетными точками:

D2 = (B2-C2)^2,

и растянем ее на диапазон D2:D10. В ячейке В12 будем хранить суммарную квадратичную ошибку (см. формулу 9.13). Для этого введем формулу: В12 = СУММ(D2:D10)

· В идеальном варианте это ошибка должна быть минимальной. Теперь осталось с помощью решающего блока (Данные Поиск решения) решить задачу оптимизации без ограничений, заполнив соответствующим образом появившееся диалоговое окно (рис. 9.14).

Рис. 9.14

· Результатом работы решающего блока будет вывод в ячейки F1:F5 значений параметров A, B, C, D, K функции At⁴+Bt³+Ct²+Dt+K. В ячейках C2:C10 получим ожидаемые значение функции в исходных точках. В ячейке B12 будет храниться суммарная квадратичная ошибка.

· Поместим эти точки в виде отдельной линии на графике. Для этого на диаграмме выделим экспериментальные значения, щелкнем правой кнопкой мыши и выберем команду Выбрать данные…. и кнопка «Добавить», появится окно Изменение ряда (рис. 9.15).

Рис. 9.15

· После заполнения окна Изменение ряда, нажимаем ОК, выделяем полученный график и настраиваем его. На рис. 9.16 отображает внешний вид рабочего листа MS Excel после проведенных вычислений.

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

Рис. 9.15

ВАРИАНТЫ ЗАДАНИЙ

1. На первом рабочем листе ввести табличную зависимость и средствами MS Excel построить график экспериментальных значений. Вычислить коэффициенты регрессии и коэффициент корреляции. Вычислить ожидаемые значения в заданных точках и построить линию регрессии. Настроить диаграмму.

 

2. На втором рабочем листе ввести табличную зависимость и средствами MS Excel построить график экспериментальных значений. Добавить к этому графику график аппроксимирующей зависимости (линия тренда). С помощью формулы, подобранной аппроксимирующей зависимостью, вычислить ожидаемое значение в заданных точках и добавить рассчитанные значения на диаграмму. Настроить диаграмму.

 

3. На третьем рабочем листе ввести табличную зависимость и средствами MS Excel построить график экспериментальных значений. Подобрать для своего варианта уравнения коэффициенты зависимости, методом наименьших квадратов, сведя задачу к задаче оптимизации. Определить суммарную ошибку. Рассчитать точки для построения и построить график. Настроить диаграмму.