Занятие 8 Аппроксимация. Линия эмпирической регрессии

Найдите эмпирическую аппроксимирующую формулу = f(X) для следующего двумерного множества точек:

Xi	Yi
2	5,5
4	6,3
6	7,2
8	8
10	8,6

1. Загрузите приложение MS Excel (Пуск  Программы  Microsoft Excel).

2. Создайте расчетную таблицу по образцу, представленному на рисунке:

Внесите исходные значения X_i и Y_i в соответствующие столбцы.

3. Ниже посчитайте n – число пар случайных величин X_i и Y_i с помощью функции СЧЕТ из Мастера функций, категория Статистические.

4. Используя Мастер диаграмм представьте двумерное распределение величин X_i и Y_i графически в виде точечной диаграммы (точки без соединительных линий). Установите из диаграммы форму и направление связи между величинами.

5. На основе графического представления точек сделайте предположение, что искомая аппроксимирующая формула есть уравнение прямой линии = a + bX (линии регрессии).

6. Заполните расчетную таблицу. Определите Х_i, Y_i.

7. В следующем столбце рассчитайте значения Х_i². Просуммируйте их.

8. В следующем столбце рассчитайте значения X_i Y_i. Просуммируйте их.

9. Определите коэффициенты линии регрессии по методу наименьших квадратов, используя формулы

,

10. Рядом с рассчитанными по формулам значениями коэффициентов линии регрессии a и b рассчитайте эти коэффициенты, используя функцию ЛИНЕЙН из Мастера функций, категория Статистические.

Примечание 1: Функции, которые возвращают массивы, должны быть введены как формулы массивов.

Создание формулы массива

• При вводе формулы массива Microsoft Excel автоматически заключает ее в фигурные скобки { }.

• Если формула массива возвращает несколько значений, выделите диапазон ячеек, в которые необходимо ввести формулу.

• Наберите формулу.

• Нажмите клавиши CTRL+SHIFT+ENTER.

Примечание 2: Функция ЛИНЕЙН возвращает массив из двух значений (коэффициенты a и b), который описывает полученную с применением метода наименьших квадратов прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные известные_значения_x и известные_значения_y. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива (см. примечание выше). Для этого выделить в строке две стоящие рядом ячейки, куда и будут помещены вычисленные значения коэффициентов.

11. Продолжите заполнение расчетной таблицы. В следующем столбце вычислите для каждого значения X_i расчетное значение , используя полученную аппроксимирующую формулу.

12. Используя Мастер диаграмм, добавьте на уже построенную точечную диаграмму график линии регрессии.

13. В следующем столбце таблицы рассчитайте значения , используя функцию ТЕНДЕНЦИЯ из Мастера функций, категория Статистические.

14. Посчитайте отклонения расчетных значений от исходных Y_i.

15. Посчитайте квадраты отклонений. Просуммируйте их.

16. Рассчитайте и σ.

Примечание 3: Функция ТЕНДЕНЦИЯ возвращает массив значений в соответствии с линейным трендом. Аппроксимирует прямой линией (по методу наименьших квадратов) массивы известные_значения_y и известные_значения_x. Возвращает значения y, в соответствии с этой прямой для заданного массива новые_значения_x (или если массив новые_значения_x не задан, то для массива известные_значения_x).

17. Рассчитайте значения , используя функцию ПРЕДСКАЗ из Мастера функций, категория Статистические, для Х = 15.

Примечание 4: Функция ПРЕДСКАЗ вычисляет (или предсказывает) будущее значение y по существующим данным известные_значения_x и известные_значения_y для любого значения x.

15