CKT_l.r.05_Prognozirovanie / Решение задач аппроксимации средствами Excel
1. На основе данных, приведенных в таблице, построить диаграмму.
2. В диаграмму добавить линейную и полиномиальную (квадратичную и куби-
3. Вывести уравнения полученных линий тренда, а также величины достовер-
4. Используя уравнения линий тренда, получить табличные данные по прибы-
ли предприятия для каждой линии тренда за 1995–2002 г.г.
5. Используя названные линии тренда, составить прогноз по прибыли пред-
1). В диапазоне ячеек A3:C13 рабочего листа Excel составляем рабочую таб-
2). Excel позволяет создавать с помощью средства Мастер диаграмм высоко-
качественные, информативные диаграммы. На начальном этапе построения диаграммы следует на рабочем листе Excel выделить данные, по которым бу-
дет строиться диаграмма. В нашем случае это диапазон ячеек В 4: С 11 . На стан-
дартной панели инструментов нажимаем кнопку Мастер диаграмм или выби-
раем в меню Вставка команду Диаграмма (методика построения диаграмм подробно описана в [8, 9]).
3). Активизируем построенную диаграмму и по описанной выше методике после выбора типа линии тренда в диалоговом окне Линия тренда (рис.1) пооче-
редно добавляем в диаграмму линейную, квадратичную и кубическую линии тренда. В этом же диалоговом окне открываем вкладку Параметры (рис.2) и в окне опции Название аппроксимирующей ( сглаженной ) кривой вводим на-
именование добавляемого тренда. Далее в окне опции «Прогноз вперед на : периодов» задаем значение 2, так как планируется сделать прогноз по прибыли на два года вперед. Для вывода в области диаграммы уравнения регрессии и значения достоверности аппроксимации R 2 отмечаем флажками опции «пока —
зывать уравнение на экране» и «поместить на диаграмму величину досто —
верности аппроксимации (R^2) ». Для лучшего визуального восприятия изме-
няем тип, цвет и толщину построенных линий тренда, для чего воспользуемся вкладкой Вид диалогового окна Формат линии тренда (рис.3). Полученная диаграмма с добавленными линиями тренда представлена на рис.5.
4). Для получения табличных данных по прибыли предприятия для каждой ли-
нии тренда за 1995–2004 г.г. воспользуемся уравнениями линий тренда, пред-
ставленные на рис.5. Для этого в диапазон ячеек D3:F3 вводим текстовую ин-
формацию о типе выбранной линии тренда: Линейный тренд, Квадратичный тренд, Кубический тренд. Далее вводим в ячейку D4 формулу линейной рег-
рессии и, используя маркер заполнения, копируем эту формулу c относитель-
ными ссылками в диапазон ячеек D5:D13 . Следует отметить, что каждой ячей-
ке с формулой линейной регрессии из диапазона ячеек D4:D13 в качестве ар-
гумента стоит соответствующая ячейка из диапазона A4:A13 . Аналогично для квадратичной регрессии заполняется ряд значений в диапазоне ячеек E4:E13 , а
также для кубической регрессии – в диапазоне ячеек F4:F13 . Таким образом,
составлен прогноз по прибыли предприятия на 2003 и 2004 г.г. с помощью трех вышеперечисленных трендов. Полученная таблица значений представлена на рис.6.
Задача 2. Для таблицы данных о прибыли автотранспортного предприятия за 1995–2002 г.г., приведенной в задаче 1, необходимо выполнить следующие
1. На основе данных, приведенных в таблице, построить диаграмму.
2. В диаграмму добавить логарифмическую, степенную и экспоненциальную линии тренда.
3. Вывести уравнения полученных линий тренда, а также величины достовер-
4. Используя уравнения линий тренда, получить табличные данные о прибыли предприятия для каждой линии тренда за 1995–2002 г.г.
5. Составить прогноз о прибыли предприятия на 2003 и 2004 г.г., используя
Следуя методике, приведенной при решении задачи 1, получаем диаграм-
му с добавленными в нее логарифмической, степенной и экспоненциальной линиями тренда (рис.7). Далее, используя полученные уравнения линий тренда,
заполняем таблицу значений по прибыли предприятия, включая прогнозируе-
Из рис.5 и рис.7 можно отметить, что модели, реализованной логарифми-
ческим трендом, соответствует наименьшее значение достоверности аппрокси-
мации R 2 = 0,8659. Наибольшие же значения R 2 соответствуют моделям, реали-
зованным полиномиальным трендом: квадратичным ( R 2 = 0,9263) и кубиче-
Задача 3. Для таблицы данных о прибыли автотранспортного предприятия за 1995–2002 г.г., приведенной в задаче 1, необходимо выполнить следующие действия:
1. На основе данных, приведенных в таблице, получить ряды данных для ли-
нейной и экспоненциальной линии тренда с использованием функций
2. Используя функции ТЕНДЕНЦИЯ и РОСТ, составить прогноз о прибыли предприятия на 2003 и 2004 г.г.
3. Для исходных данных и полученных рядов данных построить диаграмму.
Воспользуемся рабочей таблицей задачи 1, созданной на листе Excel (рис.4). Для получения ряда данных с помощью функции ТЕНДЕНЦИЯ необ-
∙ Выделить диапазон ячеек D4:D11 , который следует заполнить значениями функции ТЕНДЕНЦИЯ, соответствующими известным данным о прибыли предприятия.
∙ На стандартной панели инструментов нажимаем кнопку Вставка и выбираем команду Функция. В появившемся диалоговом окне Мастер функций выделяем функцию ТЕНДЕНЦИЯ из категории Статистические, после чего на-
жимаем кнопку ОК. Быстрее эту операцию можно осуществить нажатием кнопки f x (Вставка функции).
В появившемся диалоговом окне Аргументы функции указываем: для
опции «Известные_значения_y» – диапазон ячеек C4:C11 ; для опции «Извест-
∙ Вводим эту формулу как формулу массива, используя комбинацию клавиш
В строке формул рабочего листа Excel введенная нами формула будет иметь вид: =.
В результате указанный диапазон ячеек D4:D11 заполняется соответст-
Для составления прогноза о прибыли предприятия на 2003 и 2004 г.г. не-
∙ Выделить диапазон ячеек D12:D13 , куда будут заноситься прогнозируемые функцией ТЕНДЕНЦИЯ значения.
∙ Вызвать функцию ТЕНДЕНЦИЯ и в появившемся диалоговом окне Аргу —
менты функции указать: для опции «Известные_значения_y» – диапазон ячеек
C4:C11 ; для опции «Известные_значения_х» – диапазон ячеек B4:B11 ; а для
∙ Ввести эту формулу как формулу массива, используя комбинацию клавиш
В строке формул рабочего листа Excel введенная формула будет иметь вид: =.
В результате указанный диапазон ячеек D12:D13 заполнится прогнозируе-
Аналогично заполняется ряд данных с помощью функции РОСТ. Следует напомнить, что функция РОСТ используется при анализе нелинейных зависи-
мостей и работает точно так же, как ее линейный аналог ТЕНДЕНЦИЯ.
На рис.10 представлена таблица с формулами, где можно просмотреть вид записанных нами функций с необходимыми диапазонами ячеек. Полезно на-
помнить, что вывод используемых функций на рис.10 в пакете Excel реализует-
Для исходных данных и полученных рядов данных построена диаграмма,
Задача 4. Для таблицы данных о поступлении в диспетчерскую службу ав-
тотранспортного предприятия заявок на услуги за период с 1 по 11 число теку-
щего месяца необходимо выполнить следующие действия:
1. На основе данных, приведенных в таблице, получить ряды данных для ли-
2. На основе данных, приведенных в таблице, получить ряд данных для экспо-
ненциальной регрессии с использованием функции ЛГРФПРИБЛ.
3. Используя вышеназванные функции , составить прогноз о поступлении заявок в диспетчерскую службу на период с 12 по 14 число текущего месяца.
[expert_bq id=»1570″]Иными словами, если наши данные прыгают за три отчетных точки с -5 на 0 , а следом на 5 , в итоге мы получим почти ровную линию плюсы ситуации очевидно уравновешивают минусы. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Эта таблица показывает среднее число посетителей некого сайта в сутки по месяцам, а также количество просмотров страниц на одного посетителя. Логично, что просмотров страниц всегда должно быть больше, чем посетителей, так как один пользователь может просмотреть сразу несколько страниц.
Как построить линию тренда в MS Excel — Вектор развития. Офисные системы для бизнеса
Выделяем анализируемый объём продаж и строим график, где по оси Х — наш временной ряд (1, 2, 3… — январь, февраль, март …), по оси У — объёмы продаж. Добавляем линию тренда и уравнение тренда на график. Получаем уравнение тренда y=135134x+4594044
