- •Статистический анализ в Excel Выборочная функция распределения Задание 1
- •Задание 2
- •Определение основных статистических характеристик Задание 3
- •Задание 4
- •Построение доверительных интервалов для среднего Задание 5
- •Задание 6
- •Анализ двух выборок Задание 7
- •Задание 8
- •Задание 9
- •Получение случайных чисел Задание 10
- •Задание 11
- •Задание 12
- •Задание 13
- •Упражнения
- •Корреляционный анализ Задание 14
- •Задание 15
- •Регрессионный анализ Задание 16
- •Задание 17
- •Задание 18
- •Задание 19
- •Задание 20
Регрессионный анализ Задание 16
Дан набор точек: (0,3) , (1,1) , (2,6) , (3,3) , (4,7). Найти коэффициенты а и b прямой линии Y=aX+b , наилучшим образом аппроксимирующей эти данные.
Решение
Введите координаты точек в диапазон А2:В6 .
-
A
B
X
Y
0
3
1
1
2
6
3
3
4
7
Для того, чтобы найти коэффициенты а и b, выделите диапазон А7:В7, вызовите Мастер функций, категория Статистические, функция ЛИНЕЙН, в поле Известные значения Y выделите диапазон В2:В6, в поле Известные значения Х выделите диапазон А2:А6, поля Конст и Статистика оставите пустыми и нажмите комбинацию клавиш Ctrl+Shift+Enter.
В ячейке А7 получим значение коэффициента а, в ячейке В7 – значение коэффициента b.
Искомое уравнение прямой линии будет иметь вид Y=X+2.
Для того чтобы вывести полную статистику, выделите блок из 5 строк и 2 столбцов - D2:E6, вызовите Мастер функций, выберите Статистические, ЛИНЕЙН. Первым аргументом укажите блок В2:В6, вторым аргументом – блок А2:А6, в третьем и четвертом поле поставьте 1, нажмите Ctrl+Shift+Enter.
Получите таблицу, в которой в D2 записан коэффициент – а; в E2 – коэффициент b; в D3 и E3 – стандартные отклонения для этих коэффициентов; в D4 – коэффициент детерминации R2, чем ближе он к 1, тем лучше регрессионное уравнение описывает зависимость; в E4 – стандартная ошибка для оценки y; в D5 – значение F-статистики; в E5 – количество степеней свободы; в D6 – регрессионная сумма квадратов; в E6 – остаточная сумма квадратов.
Задание 17
Для заданного набора пар значений независимой переменой и функции определить наилучшее линейное приближение в виде прямой с уравнением Y=aX+b и показательное приближение в виде линии с уравнением Y=b·aX .
Решение
В столбец А, начиная с ячейки А1, введите 10 произвольных значений независимой переменой.
В столбец В, начиная с В1, введите 10 произвольных значений функции.
В диапазоне С1:D1 найдите коэффициенты а и b искомой прямой, с помощью функции ЛИНЕЙН.
Чтобы из полученного массива извлечь значения в отдельные ячейки, используйте функцию ИНДЕКС. Для этого выделите ячейку С3, вызовите Мастер функций, категория Ссылки и массивы, функция ИНДЕКС, в поле Массив выделите С1:D1, в поле Номер строки укажите число 1, получите значение коэффициента а.
Для извлечения коэффициента b, выделите ячейку D3, выполните действия аналогично предыдущему пункту, только в поле Номер строки укажите число 2.
В диапазоне Е1:F1 найдите коэффициенты а и b показательного приближения. Для этого выделите диапазон Е1:F1,вызовите мастер функций, категория Статистические, функция ЛГРФПРИБЛ, в поле Известные значения Y выделите диапазон В1:В10, в поле Известные значения Х выделите диапазон А1:А10, поля Конст и Статистика оставите пустыми и нажмите комбинацию клавиш Ctrl+Shift+Enter.
В ячейки Е3 и F3 извлеките коэффициенты а и b с помощью функции ИНДЕКС.