ПРИМЕР 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 |
Решение задачи можно разбить на следующие этапы:
- Ввод исходных данных и построение точечного графика. Строим и настраиваем график, как показано на рис. 9.9. Обратите внимание, что мы изменили минимальное и максимальное значение для оси OX.
Рис. 9.9
|
· Выделим экспериментальные точки на графике, щелкнем правой кнопкой мыши и воспользуемся командой Добавить линию тренда. Появившееся диалоговое окно (см. рис. 9.10) позволяет построить аппроксимирующую зависимость.
· На вкладке этого окна указывается вид аппроксимирующей зависимости (в нашем случае необходимо выбрать полиномиальную зависимость второй степени) и определяются параметры построения:
o Название аппроксимирующей зависимости (Квадратичная зависимость).
o Прогноз вперед (назад) на n единиц (этот параметр определяет, на какое количество единиц вперед (назад) необходимо продлить линию тренда).
o Показывать ли точку пересечения кривой с прямой Y = const (нет).
o Показывать аппроксимирующую функцию на диаграмме или нет (параметр показывать уравнение на диаграмме).
o Помещать ли на диаграмму величину среднеквадратичного отклонения или нет (параметр поместить на диаграмму величину достоверности аппроксимации).
· На рис. 9.11 изображена полученная диаграмма с формулой.
| Рис. 9.10 |
|
| Рис. 9.11 |
· Для расчета ожидаемых значений в точках x1 = 0,1539; x2 = 0,1769; x3 = 0,1825; x4=0,1911; x5=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)=At4+Bt3+Ct2+Dt+K методом наименьших квадратов, сведя задачу к задаче оптимизации. Определить суммарную ошибку, рассчитать точки для построения и построить график. Настроить график.
Эта задача эквивалентна задаче нахождения минимума функции пяти переменных:
| (9.13) |
· Введем табличную зависимость в рабочий лист MS Excel и построим график функции (см. рис.9.13а)
Рис. 9.13а
· Рассмотрим процесс решения задачи оптимизации (формула 9.13). Пусть значения А, В, С, D и К хранятся в ячейках F1:F5 (рис. 9.13б). В ячейку C2 введем значение функции At4+Bt3+Ct2+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 функции At4+Bt3+Ct2+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 построить график экспериментальных значений. Подобрать для своего варианта уравнения коэффициенты зависимости, методом наименьших квадратов, сведя задачу к задаче оптимизации. Определить суммарную ошибку. Рассчитать точки для построения и построить график. Настроить диаграмму.
