2.2. Построение линии тренда расходов на продукты питания в зависимости от уровня дохода семьи Задание 1. Построение линии тренда
Для заданий 1, 3, 4, 11,12, 13, 14, 15, 16 и задания №3 для самостоятельной работы имеются исходные таблицы в файле Excel «Прогноз и поиск (для студентов)», а для заданий 17, 18, 19, 20 в файле «Экспертный опрос».
Выполнение задания
В таблице приведены статистические данные опроса (январь 2010 года) населения по их затратам на приобретение продуктов питания.
Для анализа приведенных данных построим диаграмму рассеяния в корреляционном поле - Затраты семьи на приобретение продуктов - доходы семьи (рис. 2.8).
Рис. 2. 8. Таблица доходов и затрат на питание семьи
Выделите таблицу данных (блок ячеек А1:В12).
На вкладке Вставка в группе Диаграмма выберите тип диаграммы Точечная с маркерами (рис. 2.9).
Рис. 2. 9. Выбор типа диаграммы для показа затрат семьи на питание
Получится диаграмма затрат семьи на продукты питания в зависимости от дохода следующего вида (рис. 2.10).
Рис. 2. 10. Первоначальный вид диаграммы для показа затрат семьи на питание
Для лучшей наглядности выполните ее форматирование: напишите заголовок, уберите горизонтальные линии сетки и измените масштаб осей Х (Доход на члена семьи) и Y (Затраты на питание).
Напомним, как изменить масштаб оси.
Щелкните по оси правой кнопкой мыши и выделите команду Формат оси.
Установите минимальное значение параметра оси равным 2000 руб. (рис. 2.11).
Рис. 2. 11. Задание минимального значения параметра оси Х
Для оси Y задайте минимальное значение равным 1000 руб.
Теперь диаграмма примет следующий вид (рис. 2.12).
Рис. 2. 12. Вид диаграммы для показа затрат семьи на питание после редактирования
Для построения линии тренда:
Выделите кривую (после щелчка мышью на кривой на ней должны появиться маркеры выделения).
Щелкните правой кнопкой мыши на любую точку графика и выберите команду Добавить линию тренда (рис. 2.13).
Рис. 2. 13. Добавление линии тренда
В окне Формат линии тренда (рис. 2.14) выполните следующие действия:
Рис. 2. 14. Диалоговые окна "Линия тренда"
Выберите подходящий тип кривой аппроксимации, например, «Линейная».
Установите флажки «показывать уравнение на диаграмме» и «поместить на диаграмме величину достоверности аппроксимации R2»
В группе списков «Прогноз» установите необходимую величину шага прогноза – 250 руб.
Выберите другой, более подходящий тип кривой аппроксимации. В данном случае, это будет «Логарифмическая». В результате может получиться диаграмма следующего вида (рис. 2.15.).
Рис. 2. 15. Диаграмма расходов семьи на продукты питания со вставленными линиями и уравнениями тренда (логарифмической и линейной) после ее редактирования
После того как построены линии тренда и соответствующие им уравнения, не составляет труда вычислить новое значение Y (зависимой переменной) для нового значения Х (независимой переменной).
Для этого установите курсор в ячейку В13 и введите в нее формулу с большим значением R2 - y = 1987ln(x) - 13729. Значение Х лучше указать адресом ячейки, а не константой
Таким образом, вы сможете выполнять прогнозы для любого другого значения дохода на одного члена семьи.
Примечание 1. Следует очень внимательно относиться к выбору типа линии тренда для аппроксимации данных наблюдений и учитывать то, что прогноз делается в предположении, что в дальнейшем события будут развиваться также как и в период наблюдения.
Примечание 2. Показателем точности аппроксимации данных наблюдений является величина R2 (чем ближе эта величина к единице, тем точнее выбранная линия тренда приближена к данным наблюдений – выше точность аппроксимации).
Однако прежде всего вы должны понимать (и уметь объяснить) смысл переменных входящих в уравнение аппроксимации (линии тренда) и характер исследуемого вами процесса.
Так, например, при аппроксимации ряда наблюдений за динамикой продаж уравнением типа Y= a+b*X и прогнозируя их развитие на достаточно продолжительный период, трудно ожидать, что за это время рынок не окажется насыщенным вашими товарами или упадет покупательная способность населения.
Наиболее часто ошибки прогноза возникают при использовании в качестве уравнения аппроксимации степных полиномов типа
Y= a+ b*X + c*X2+ d*X3 +… (2.7)
несмотря на то, что величина R2 может иметь достаточно высокое значение.
Использование в качестве линии тренда линейной фильтрации (скользящей средней) позволяет получить достаточно хорошее приближение линии тренда к данным наблюдений, но не позволяет прогнозировать значения ряда и, соответственно, не дает уравнение прогноза для процессов, поведение которых, в будущем может резко измениться[2, 8].