Лабораторный практикум №2Известны данные о величине валового внутреннего продукта (ВВП) России в 2000-2008 гг. (рис. 16).
Рис. 16. Целью данной работы является построение прогноза ВВП России на ближайшие 3 года (2009, 2010, 2011 годы). Для этого необходимо решить ряд задач: 1) На основе значений ВВП России в 2000-2005 гг. построить линейный и экспоненциальный прогноз на 2006-2008 гг. 1 способ (с помощью маркера заполнения): а)Cкопируйте значения ВВП за 2000-2005 гг. (диапазон ячеек В4:G4) и дважды вставьте их в разные строки рабочего листа Excel, например в ячейки В8 и В13. (Важно, чтобы новые массивы значений находились строго под исходными данными и соответствующими датами (рис. 17)). b)Затем выделите диапазон B8:G8, наведите мышку на правый нижний угол ячейки G8 (при этом крестик-курсор станет черным) и, нажав на левую кнопку мыши, протяните диапазон до ячейки J8. В результате, ячейки H8:J8 заполнятся значениями, соответствующими линейному прогнозу. После этого выделите диапазон B13:G13, наведите мышку на правый нижний угол ячейки G13 (крестик-курсор снова станет черным), и, нажав правую кнопку мыши, протяните диапазон до ячейки J13. Появится контекстное меню, в котором выберите пункт «Экспоненциальное приближение». Ячейки H13:J13 заполнятся значениями, соответствующими экспоненциальному прогнозу. 2 способ (с помощью функций ПРЕДСКАЗ и РОСТ): а) Произведите повторное копирование диапазона В4:G4 и дважды вставьте его в разные строки рабочего листа Excel (например, в ячейки B23 и B28), так чтобы исходные данные и скопированные значения находились одно под другим. b)Выделите ячейку H23 и вставьте в нее функцию ПРЕДСКАЗ (H3;$B$4:$G$4;$B$3:$G$3). После чего растиражируйте формулу из ячейки H23 на 2 ближайшие ячейки. В результате, ячейки заполнятся значениями, прогнозируемыми на основе линейной функции. Убедитесь, что в ячейках H23:J23 получены те же значения, что и в H8:J8. c) В ячейку H28 вставьте функцию РОСТ. Данная функция прогнозирует значения по экспоненте (экспоненциальное приближение). Формат этой функции: РОСТ (известные_ значения_у; известные значения_х; неизвестные значения_x; константа). В данном случае известными значениями у являются значения ВВП в 2000-2005 гг. (ячейки B4:G4), известными значениями х являются соответствующие годы: 2000-2005 (ячейки B3:G3), неизвестными значениями х являются годы 2006-2008 (ячейки H3:J3), значение константы остается пустым. Таким образом, в ячейке H28 должна быть формула: =РОСТ($B$4:$G$4;$B$3:$G$3;H3). Затем растиражируйте эту формулу на ближайшие 2 ячейки, т.е. для 2007 и 2008 года. Должен получиться массив прогнозных ячеек ВВП (H28:J28), соответствующих экспоненциальному прогнозу. Убедитесь, эти значения в ячейках H28:J28 совпадают со значениями H13:J13. 2) Выбрать какой из прогнозов (линейный или экспоненциальный) наиболее точно описывает реальные данные (значения ВВП в 2006-2008 гг.). Для этого необходимо найти отклонения прогнозных значений от реальных. Отклонения рассчитываются по формуле:
Введите в ячейку H9 формулу: =ABS(H8-H4). (ABS () – это функция, которая возвращает модуль числа. Ее можно найти в Мастере функций Excel). Растиражируйте формулу на ячейки I9 и J9. В ячейке K9 вычислите сумму отклонений линейного прогноза: =СУММ(H9:J9). (рис. 18). Самостоятельно рассчитайте отклонения и сумму отклонений для экспоненциального прогноза. Определите, какой из прогнозов является более точным, т.е. сумма отклонений которого является наименьшей. Постройте точечную диаграмму, которая будет содержать 3 графика: 1) график реальных значений ВВП в 2000-2008 гг. (исходные значения: диапазоны B3:J3 и B4:J4), 2) график, состоящий из реальных значений ВВП в 2000-2005 гг. и прогнозных значений ВВП в 2006-2008 гг. (линейный прогноз, исходные данные: диапазоны B3:J3 и B8:J8), 3) график, состоящий из реальных значений ВВП в 2000-2005 гг. и прогнозных значений ВВП в 2006-2008 гг. (экспоненциальный прогноз, исходные данные: диапазоны B3:J3 и B13:J13). См. рис. 19.
Рис. 17
Рис.18
Рис. 19 3) Построить прогноз ВВП России на 2009-2011 гг., используя выбранную модель (линейную или экспоненциальную). Значения лет (2009-2011) введите методом заполнения. Для этого выделите диапазон В3:J3, наведите курсор на правый нижний угол ячейки J3 (курсор превратится в «черный крестик») и, нажав правую кнопку мыши, протяните диапазон на ячейки K3:M3. В появившемся меню выберите «Прогрессия», далее шаг=1 и нажмите ОК. Прогнозные значения получите одним из описанных способов (с помощью маркера заполнения или с помощью функции).
Рис.20. Результат прогноза
|
