Вставка | столбцы

Значения цены из бывшего столбца C переместятся в столбец F.

Новый вид электронной таблицы требует следующих действий пользователя. В ячейку C2 вписать формулу = A2 ^ 2, в ячейку D2  формулу = B2 ^ 2, в ячейку E2  формулу = A2 * B2. Значения x1^2, x2^2, x1*x2 будут теперь рассматриваться как аргументы обычной линейной регрессии (с учётом обозначений формулы, начинающейся со знака =, символов произведения * и возведения в степень ^, знакомых их информатики).

В результате решения получаем искомое уравнение регрессии в форме при следующих данных

R² = 0,9704

F = 39,4116

α = 0,000164

b ₀ = 318,83 α ₀ = 0,6740 β ₀ = 0,3260

b ₁ =  3,6709 α ₁ = 0,1145 β ₁ = 0.8855

b ₂ =3,4424; α ₂ = 0,2650 β ₂ = 0.7350

b ₃ =  0,00097 α ₃ = 0,6699 β ₃ = 0.3301

b ₄ = 0,001678 α ₄ = 0,5449 β ₄ = 0.4551

b₅ = 0,00044 α₅ = 0,9140 β₅ = 0.0860

Как следует из приведенных данных, ценой существенного усложнения математической модели здесь удалось несколько повысить значение критерия R², однако доверительные вероятности

_i = 1 - _i

определения коэффициентов заметно снизились.

Тот же подход может быть использован при определении уравнения регрессии при поиске оптимальной степени к

Ŷ = b₀ + b₁x +b₂x² + b₃x³ + … + b_Kx^K,

где x², x³, … x^K формально рассматриваются как линейные аргументы (факторы), и для них отводятся соответствующие столбцы в электронной таблице обработки данных.

Заметим, что при исследовании двухфакторного объекта y = f (x₁, x₂)

его математическая модель в форме уравнения

Ŷ = b₀ + b₁x₁ + b₂x₂ + b₃x₁² + b₄x₂² + b₅x₁x₂

позволяет средствами Excel получить графическое представление поверхности Ŷ в функции определяющих её факторов [4], ч.1, c.99 … 107. Файл подобного графического анализа экспериментальных данных под названием Graph_An.xls (на диске с программами) иллюстрирует такую возможность при значениях коэффициентов b₀ = 40,17; b₁ = 56,28; b₂ = 22,91;; b₃ = -10,41; b₄ = -1,835; b₅ = -1,79. Отсюда следует, что графический анализ экспериментальных данных позволяет оценить форму поверхности отклика и установить наличие её эстремумов при соответствующих значениях коэффициентов, найденных комплексным статистическим анализом.

Рекомендуемая литература: [4], ч.1, с.76 … 88.

Вопросы для самопроверки

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

2. Какие частные задачи решает комплексный анализ данных, реализуемый, например, с помощью системы электронных таблиц Microsoft Excel?

3. Можно ли в среде Microsoft Excel выполнить корреляционный, регрессионный и дисперсионный анализ экспериментальных данных?

4. Какие суммы квадратов используются при комплексном статистическом анализе?

5. По каким суммам квадратов вычисляется экспериментальное значение критерия Фишера?

6. Что представляет собой критерий “R – квадрат” при комплексном анализе результатов статистических данных средствами Excel?

7. Каким образом критерий R² может характеризовать адекватность полученной математической модели?

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

9. Чем отличается способ оценки значимости коэффициентов уравнения регрессии по принятому в Excel критерию “Р – значение” (то есть по уровню значимости ошибки его определения ) от оценки по критерию Стьюдента?

10. Что представляет термин “t – статистика” при выводе итогов комплексного статистического анализа данных в среде Excel?

11. Объясните сущность выводимого в итогах расчетов (Excel) термина “значимость “.

После окончания работы над теоретическим материалом настоящей темыы студенту необходимо выполнить лабораторную работу №4 (разделы 3.4 и 3.5), а затем пройти тестирование согласно данным раздела 4.3. Тесты).