- •Методические рекомендации для студентов по самоподготовке и проведению практического занятия №7 по информатике.
- •«Решение задач математической статистики в табличном редакторе Microsoft Excel. Статистический анализ данных. Подбор параметра»
- •Исходный вид таблицы
- •Результирующий вид таблицы
- •Результирующий вид таблицы
- •Исходный вид таблицы
- •Выполнение:
- •Результирующий вид таблицы
- •Исходный вид таблицы
- •Математический метод решения:
- •Решение с помощью ms Excel
- •Результирующий вид таблицы
- •Гистограмма
- •Подбор параметра
- •Сглаживание временных рядов.
Математический метод решения:
Решим систему линейных уравнений:
a=-24,023; b1=0.3829; b2=1.6774
Т.о. получили уравнение
y=0.3829x1+1.6774x2 – 24,023
Решение с помощью ms Excel
Встроенная статистическая функция ЛИНЕЙН определяет параметры линейной регрессии y=a+bx. Порядок вычисления следующий:
Зарезервируйте область К2:М6 – залейте цветом - для получения только оценок коэффициентов регрессии;
Выделите ячейку К2;
Активизируйте Мастер функций и в категории Статистические выберите функцию ЛИНЕЙН;
Заполните аргументы функции:
известные значения у – диапазон, содержащий данные результативного признака;
известные значения х – диапазон, содержащий данные независимого признака
константа – логическое значение, которое указывает на наличие или отсутствие свободного члена в уравнении; если константа=1, то свободный член рассчитывается обычным образом, если константа=0, то свободный член равен 0;
статистика – логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу или нет. Если статистика=1, то дополнительная информация выводится, если статистика=0, то выводятся только параметры уравнения;
Т.о. аргументы функции ЛИНЕЙН примут вид:
=ЛИНЕЙН(B2:B13;C2:D13;ИСТИНА;ИСТИНА)
В левой верхней ячейке зарезервированной области появится первый элемент итоговой таблицы;
Выделите зарезервированную область;
Чтобы раскрыть всю таблицу, нажмите на клавишу <F2>, а затем на комбинацию клавиш <CTRL>+<SHIFT>+<ENTER>.
5. Сохраните книгу.
Дополнительная регрессионная статистика будет выводиться в порядке, указанном в следующей схеме:
Значение коэффициента bm |
Значение коэффициента bm-1 |
… |
Значение коэффициента a |
Среднеквадратическое отклонение bm |
Среднеквадратическое отклонение bm-1 |
… |
Среднеквадратическое отклонение a |
Коэффициент детерминации R2 |
Среднеквадратическое отклонение y |
|
|
F-статистика |
Число степеней свободы |
|
|
Регрессионная сумма квадратов |
Остаточная сумма квадратов |
|
|
Вывод: Таким образом, y=-24.023+0.382904x1+1.677398x2, что совпадает с результатом, полученным ранее. Очевидно, что полученное уравнение достаточно хорошо аппроксимирует исходные данные.
Результирующий вид таблицы
Ориентировочные основы действий (ООД) для практической работы:
Прочитайте краткие теоретические сведения по данной теме.
Ответьте на вопросы для самоконтроля.
Сделайте основные задания для самоподготовки.
Перечень знаний, навыков и умений, которыми студент должен овладеть в результате изучения данной темы:
Пользоваться табличным редактором.
Производить расчеты по статистическому анализу данных при помощи пакета анализа.
Проводить анализ данных при помощи основных форма стат.анализа.
ПРИЛОЖЕНИЕ:
Краткие теоретические сведения.