 |
Тема 3: «Табличный процессор MS Excel. Анализ данных».
|
|
|
|
При выполнении данного задания таблицу развернуть по вертикали и отсортировать по месяцам. Графики прогнозов строить отдельно. Сделать соответствующие выводы относительно прогнозной стоимости товаров.
Известна величина дохода ($), полученная от реализации одного изделия за последние 7 месяцев (рис. 30):
Рис. 30. Исходная таблица данных
Построить графики изменения стоимости каждого вида продукции и спрогнозировать их стоимости на 9-й месяц.
Этапы расчётов.
1. Чтобы развернуть таблицу по вертикали и отсортировать по месяцам, вначале необходимо выделить всю таблицу.
2. Скопировать таблицу в буфер.
3. Курсор устанавливаем в любую пустую ячейку.
4. В меню Правка выделить команду Сп е циальная вставка (рис. 31). Подобные действия могут быть выполнены нажатием левой кнопкой мыши.
Рис. 31. Команда Сп е циальная вставка в контекстном меню
5. Нажимаем левую кнопку мыши, вызываем диалоговое окно Сп е циальная вставка (рис. 32). В диалоговом окне Сп е циальная вставка выделяем команду тр а нспонировать (рис. 32) и ОК.
Рис. 32. Диалоговое окно Сп е циальная вставка
6. В выделенной ячейке появляется вертикальная матрица (рис. 33). Теперь её необходимо отсортировать по месяцам.
Рис. 33. Транспонированная вертикальная матрица
7. Курсор устанавливаем в ячейке В8 и в строке меню выбираем команду 
.
8. Получаем таблицу, в которой результаты расположены по вертикали и отсортированы по месяцам (рис. 34).
Рис. 34. Результаты в таблице отсортированы по вертикали
9. Дальнейшие действия связаны с построением графиков прогноза по каждому виду продукции. Используется подбор кривой по точкам.
Выполняются следующие действия:
1. Мастер диаграмм è Точечная (рис. 35). Выполняем 4 шага Мастера диаграмм (рис. 36-38).
|
|
|
Рис. 35. Мастер диаграмм: (шаг 1 из 4): вид диаграммы Точечная
Рис. 36. Мастер диаграмм: (шаг 2 из 4): источник данных диаграммы
Рис. 37. Мастер диаграмм: (шаг 3 из 4): параметры диаграммы
2. Разместить на отдельном листеè Готово (рис. 33).
Рис. 38. Мастер диаграмм: (шаг 1 из 4): размещение диаграммы
3. На диаграмме щелкнуть на любой точке ряда данных (выделится весь ряд данных). В меню Диаграмма выбираем команду Добавить линию тренда. В расчетах выбрана С т епенная линия тренда (рис. 39).
Рис. 39. Выбор степенной линии тренда
4. В меню Параметры выбираем команду Прогноз вперёд на: 2 единиц, и команды: показать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (
) – это коэффициент детерминации (рис. 40). Нажимаем ОК и получаем график (рис. 41).
Рис. 40. Выбор периодов прогноза, вида уравнения и коэффициента
Рис. 41. Прогноз стоимости продукции 1-го вида
Уравнение степенной линии тренда имеет вид:
.
Коэффициент детерминации 
имеет довольно высокое значение и показывает, что в расчётах учтены 84,38% значимых факторов. На долю неучтенных факторов приходится 15,62%. Это свидетельствует о высокой точности расчётов.
Результаты расчётов прогноза стоимости продукции 1-го вида.
1. Прогноз на два периода планирования вперёд показал, что на 8-м периоде стоимость продукции 1-го вида увеличится до величины 6,12.
2. На 9-м периоде стоимость продукции 1-го вида увеличится до величины 6,16.
3. В целом вид кривой характеризует о тенденции постепенного роста стоимости продукции 1-го вида.
Прогноз стоимости продукции 2-го и 3-го вида производится аналогично, поэтому по каждому виду продукции 2-го и 3-го вида приведём графики прогнозов и соответствующие выводы относительно прогнозной стоимости товара каждого вида.
Прогноз стоимости продукции 2-го вида на основании степенной линии тренда показан на рис. 42.
|
|
|
Рис. 42. Прогноз стоимости продукции 2-го вида
Уравнение степенной линии тренда имеет вид:
.
Коэффициент детерминации 
имеет довольно высокое значение и показывает, что в расчётах учтены 90,27% значимых факторов. На долю неучтенных факторов приходится 9,73%. Это свидетельствует о высокой точности расчётов.
Результаты расчётов прогноза стоимости продукции 2-го вида.
1. Прогноз на два периода планирования вперёд показал, что на 8-м периоде стоимость продукции 2-го вида увеличится до величины 6,86.
2. На 9-м периоде стоимость продукции 2-го вида увеличится до величины 6,95.
4. В целом вид кривой характеризует о тенденции постепенного роста стоимости продукции 2-го вида.
Рассмотрим прогноз стоимости продукции 3-го вида.
Предварительные расчёты показали, что все рассчитанные коэффициенты детерминации, кроме полиномиальной линии тренда имеют очень низкое значение:
- линейная линия тренда 
;
- логарифмическая линия тренда 
;
- экспоненциальная линия тренда 
;
- степенная линия тренда 
.
В расчётах для прогноза стоимости 3-го вида продукции принята полиномиальная линия тренда.
Прогноз стоимости продукции 3-го вида показан на рис. 43.
Рис. 43. Прогноз стоимости продукции 3-го вида
Уравнение полиномиальной линии тренда имеет вид:
.
Коэффициент детерминации 
имеет высокое значение и показывает, что в расчётах учтены 79,87% значимых факторов. На долю неучтенных факторов приходится 20,13%. Это свидетельствует о достаточной точности расчётов.
Результаты расчётов прогноза стоимости продукции 3-го вида.
3. Прогноз на два периода планирования вперёд показал, что на 8-м периоде стоимость продукции 3-го вида увеличится до величины 4,64.
4. На 9-м периоде стоимость продукции 3-го вида увеличится до величины 5,24.
5. В целом вид кривой характеризует о тенденции постепенного роста стоимости продукции 3-го вида.
|
|
|
