 |
Результаты коэффициентов линейной аппроксимации.
|
|
|
|
В таблице 3 в ячейках A37:B38 записана формула {=МОБР(A33:B34)}.
В ячейках D37:D38 записана формула {=МУМНОЖ(A37:B38;C33:C34)}.
Далее аппроксимируем функцию квадратичной функцией. Для определения коэффициентов, и воспользуемся системой
Используя итоговые суммы таблицы 2,
расположенные в ячейках A27, B27, C27, D27, E27, F27 и G27 запишем систему в виде
решив которую, получим 
, 
и 
.
Таким образом, квадратичная аппроксимация имеет вид
.
Решение системы проводили, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 4.
 |
Таблица 4
Результаты коэффициентов квадратичной аппроксимации.
В таблице 4 в ячейках E38:G40 записана формула {=МОБР(E33:G35)}.
В ячейках I38:I40 записана формула {=МУМНОЖ(E38:G40;H33:H35)}.
Теперь аппроксимируем функцию экспоненциальной функцией. Для определения коэффициентов и прологарифмируем значения и используя итоговые суммы таблицы 2, расположенные в ячейках A27, C27, H27 и I27 получим систему
где.
Решив систему, найдем 
, 
.
После потенцирования получим 
.
Таким образом, экспоненциальная аппроксимация имеет вид
.
Решение системы проводили, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 5.
Таблица 5
 |
Результаты коэффициентов экспоненциальной аппроксимации.
В таблице 5 в ячейках D45:E46 записана формула {=МОБР(D42:943)}.
В ячейках G45:G46 записана формула {=МУМНОЖ(D45:E46;F42:F43)}.
В ячейке G47 записана формула =EXP(G45).
Вычислим среднее арифметическое и по формулам:
Результаты расчета и средствами Microsoft Excel представлены в таблице 6.
Таблица 6
 |
Вычисление средних значений X и Y.
В ячейке F49 записана формула =A26/25.
В ячейке F50 записана формула =B26/25.
Для того, чтобы рассчитать коэффициент корреляции и коэффициент детерминированности данные целесообразно расположить в виде таблицы 7, которая является продолжением таблицы 2.
|
|
|
Таблица 7
Вычисление остаточных сумм.
Поясним как таблица 7 составляется.
Ячейки A2:A27 и B2:B27 уже заполнены (см. табл. 2).
Далее делаем следующие шаги.
Шаг 1. В ячейку J2 вводим формулу =(A2-$F$49)*(B2-$F$50).
Шаг 2. В ячейки J3:J26 эта формула копируется.
Шаг 3. В ячейку K2 вводим формулу =(A2-$F$49)^2.
Шаг 4. В ячейки K3:K26 эта формула копируется.
Шаг 5. В ячейку L2 вводим формулу =(B2-$F$50)^2.
Шаг 6. В ячейки L3:L26 эта формула копируется.
Шаг 7. В ячейку M2 вводим формулу =($D$37+$D$38*A2-B2)^2.
Шаг 8. В ячейки M3:M26 эта формула копируется.
Шаг 9. В ячейку N2 вводим формулу
=($I$38+$I$39*A2+$I$40*A2^2-B2)^2.
Шаг 10. В ячейки N3:N26 эта формула копируется.
Шаг 11. В ячейку O2 вводим формулу
=($G$47*EXP($G$46*A2)-B2)^2.
Шаг 12. В ячейки O3:O26 эта формула копируется.
Последующие шаги делаем с помощью автосуммирования.
Шаг 13. В ячейку J27 вводим формулу =СУММ(J2:J26).
Шаг 14. В ячейку K27 вводим формулу =СУММ(K2:K26).
Шаг 15. В ячейку L27 вводим формулу =СУММ(L2:L26).
Шаг 16. В ячейку M27 вводим формулу =СУММ(M2:M26).
Шаг 17. В ячейку N27 вводим формулу =СУММ(N2:N26).
Шаг 18. В ячейку O27 вводим формулу =СУММ(O2:O26).
Теперь проведем расчеты коэффициента корреляции по формуле
(только для линейной аппроксимации)
и коэффициента детерминированности по формуле. Результаты расчетов средствами Microsoft Excel представлены в таблице 8.
Таблица 8
 |
Результаты расчета.
В таблице 8 в ячейке D53 записана формула =J27/(K27*L27)^(1/2).
В ячейке D54 записана формула =1- M27/L27.
В ячейке D55 записана формула =1- N27/L27.
В ячейке D56 записана формула =1- O27/L27.
Анализ результатов расчетов показывает, что квадратичная аппроксимация наилучшим образом описывает экспериментальные данные.
Построение графиков в Excel и использование функции ЛИНЕЙН.
Рассмотрим результаты эксперимента, приведенные в исследованном выше примере.
|
|
|
Исследуем характер зависимости в три этапа:
· Построим график зависимости.
· Построим линию тренда (
, 
, 
).
· Получим числовые характеристики коэффициентов этого уравнения.
Рис.4.1. График зависимости y от x
Рис.4.2. График линейной аппроксимации
Рис.4.3. График квадратичной аппроксимации.
Рис.4.4. График экспоненциальной аппроксимации.
Примечание: Полученное при построении линии тренда значение коэффициента детерминированности для экспоненциальной зависимости 
не совпадает с истинным значением 
, поскольку при вычислении коэффициента детерминированности используются не истинные значения, а преобразованные значения с дальнейшей линеаризацией.
Таблица 9
|
|
|
