Определить минимально необходимый тираж ежемесячного журнала "Cosmopolitan" и возможный доход от размещения в нем рекламы в следующем месяце, если известны данные об объемах продаж этого журнала и доходах от размещения рекламы за прошедшие 12 месяцев (считать, что расценки на рекламу не менялись).
Для выполнения задания нужно составить таблицу вида
Месяц 1 2 . . . 12
Тираж 100000 120000 . . . 145000
Доход 128000 135000 . . . 172000
и заполнить ячейки за 12 месяцев условными данными. По этим данным нужно сделать линейный и экспоненциальный прогноз и др. (см. Задание).
Регрессионный анализ данных
Согласно варианту задания необходимо в табличном процессоре MS Excel составить, заполнить и определить:
1. Таблицу исходных данных, а также значений, полученных методами линейной и экспоненциальной регрессии.
2. Коэффициенты в уравнениях прямой и экспоненциальной кривой (функции ЛИНЕЙН и ЛГРФПРИБЛ). Запишите уравнения прямой и экспоненциальной кривой для одномерной регрессии.
3. Погрешности (ошибки) прямой и экспоненциальной кривой. вычислений для коэффициентов и функций, коэффициенты детерминированности. Оценить, какой тип регрессии наилучшим образом подходит для вашего варианта задания.
4. Прогноз изменения данных, выполненный с использованием линейной и экспоненциальной регрессии (функции ТЕНДЕНЦИЯ, РОСТ).
5. Построить гистограмму (или график) исходных данных для задачи 1 (одномерная регрессия), отобразить на ней линию тренда, а также соответствующее ей уравнение и коэффициент детерминированности.
Решение
Таблица исходных данных (рисунок 17).
Рисунок 17
Коэффициенты в уравнениях прямой (функции ЛИНЕЙН) для Тираж.
Выделим ячейки А24:В28 для вывода результата (в соответствии с исходной таблицей), введём в строку ввода формулу =ЛИНЕЙН(B6:B17;А6:А17;1;1), нажмём клавиши Ctrl+Shift+Enter, в выделенных ячейках появится результат:
Рисунок 18
Таким образом, коэффициент m = 3269,23 со стандартной ошибкой 455,39, а свободный член b = 110666,67 со стандартной ошибкой 3351,57, т.е. функция, описывающая данные рисунке 18, имеет вид
у = 3269,23·х +110666,67
Стандартные ошибки показывают максимально возможное отклонение параметра от рассчитанной величины. Для у оно составляет 5445,65, т.е. реальное значение у может лежать в пределах у±5445,65.
Точность приближения к табличным данным (коэффициент детерминированности r2) составляет 0,837 или 83,7%, что является высоким показателем. При х = 13 получим: у = 3269,23·13+110666,67 = -68166,68.
Коэффициенты в уравнениях прямой (функции ЛИНЕЙН) для Доход
.
Выделим ячейки D24:E28 для вывода результата (в соответствии с исходной таблицей), введём в строку ввода формулу =ЛИНЕЙН(C6:C17;А6:А17;1;1), нажмём клавиши Ctrl+Shift+Enter, в выделенных ячейках появится результат:
Рисунок 19
Таким образом, коэффициент m = 3940,56 со стандартной ошибкой 100,39, а свободный член b = 126469,7 со стандартной ошибкой 738,81, т.е. функция, описывающая данные рисунке 18, имеет вид
у = 3940,56·х -126469,7
Стандартные ошибки показывают максимально возможное отклонение параметра от рассчитанной величины. Для у оно составляет 1200,48, т.е. реальное значение у может лежать в пределах у±1200,48.
Точность приближения к табличным данным (коэффициент детерминированности r2) составляет 0,994 или 99,4%, что является высоким показателем. При х = 13 получим: у = 3940,56·13 -126469,7 =-75242,45.
Коэффициенты в уравнениях экспоненциальной кривой (ЛГРФПРИБЛ) для Тираж.
Выделим для результата блок ячеек G24:H28, введём в строку формул функцию =ЛГРФПРИБЛ(B6:B17;A6:A17;1;1), нажмём клавиши Ctrl+Shift+Enter, в выделенных ячейках появится результат:
Рисунок 20
Таким образом, коэффициент m = 1,026, а b = 110966,2, т.е
Задать вопрос
на новый заказ в Автор24.
