Графическое изображение статистических данных и прогнозирование в MS Excel

Тема: Графическое изображение статистических данных и прогнозирование в Microsoft Excel.

Цель занятия: Изучение информационных технологий использования возможностей Microsoft excel для статистических расчетов графического представления данных и прогнозирования.

Задание 1. С помощью диаграммы (обычная гистограмма) отобразите данные о численности населения России (млн. чел.) за 1970-2005 гг.

Исходные данные представлены на рис.1, результаты работы на рис. 3.

Порядок работы

1.     Откройте редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу (при стандартной установке Microsoft Office выполните Пуск – Все программы-Microsoft Office – Microsoft Office Excel).

Рисунок 1 – Исходные данные для задания 1

2.     Создайте на листе 1 таблицу численности населения по образцу (см. рис.1). Для ввода значений лет создайте ряд чисел с интервалом в 7 лет (введите первые два значения даты – 1970 и 1977 г., выделите обе ячейки и протяните вправо за маркер автозаполнения до нужной конечной даты).

3.     Постройте диаграмму (обычная гистограмма) по данным таблицы. Для этого выделите интервал ячеек с данными численности населения A3:G3 и выберите команду Вставка – Гистограмма – Обычная гистограмма. Щелкните правой кнопкой мыши по построенной диаграмме и выберите команду Выбрать данные… в открывшемся диалоговом окне измените данные так как показано на рисунке 3 и нажмите ОК.

Рисунок 2 – Выбор типа диаграммы

Рисунок 3 – Второй этап построения диаграммы

Задание 2. Осуществить прогноз численности населения России на 2012 г. Добавлением линии тренда к ряду данных графика.

Краткая справка. Для наглядного показа тенденции изменения некоторой переменной целесообразно на график вывести линию тренда. Это возможно не для всех типов диаграмм, а только для гистограмм, линейчатых диаграмм, диаграмм с областями, графиков. Введенная линия тренда сохраняет связь с исходным рядом, т. е. при изменении данных соответственно изменяется линия тренда. Линию тренда можно использовать для прогноза данных.

Порядок работы

1. Добавьте линию тренда к диаграмме, построенной в задании 1. Для этого сделайте диаграмму активной, затем щелкните правой кнопкой мыши по диаграмме и выберите команду Изменить тип диаграммы. Измените, тип диаграммы выберите График.

Рисунок 4 – Изменения типа тиаграммы

Затем перейдите на вкладку Макет – Линия тренда – Дополнительные параметры линии тренда… - в диалоговом окне Формат линии тренда выберите Полиномиальная 4-й степени затем Закрыть.

Рисунок 5 – Формат линии тренда

Далее измените тип диаграммы на обычную гистограмму.

2. Для осуществления прогноза в области Название аппроксимирующей (сглаженной) кривой выберите Другое и введите с клавиатуры Линия тренда.
3. На диаграмме будет показана линия тренда и прогноз на один период вперед (рис. 6).

Рисунок 6 – конечный вид диаграммы и линии тренда задания 1

4. Внесите численное значение прогноза на 2012 г. в исходную таблицу. Если вы все сделали правильно, то прогноз численности населения России по линии тренда составит 131 млн. чел.

Задание 3. Построить график о числе заключенных населением России браков. Добавить линию тренда и составить прогноз на три периода вперед.

Исходные данные представлены на рис. 7, результаты работы на рис. 8.

Рисунок 7 – Исходные данные для задания 3

Рисунок 8 – Конечный вид графика и линии тренда задания 3

Задание 4. Построить линейчатую диаграмму изменения стоимости автомобилей в зависимости от года выпуска. Добавить линейную и логарифмическую линии тренда. Определить, какой вид линии тренда дает более реальный прогноз. Определить примерную стоимость автомобилей 1998 и 1997 гг. выпуска.

Исходные данные представлены на рис. 9, результаты работы на рис. 10.

Рисунок 9 – Исходные данные для задания 4

Рисунок 10 – Конечный вид диаграммы и линий тренда задания 4

Задание 5. По данным о численности работников научных организаций (тыс. чел.) в России за 2000-2005 гг. произвести расчеты и построить графики численности работников по категориям

Формула для расчета столбца «Прочие»:

Прочие = Всего работников - Специалисты – Вспомогательный персонал.

Добавить линию тренда и составить прогноз изменений численности специалистов на три года вперед.

Исходные данные представлены на рис. 11.

Рисунок 11 – Исходные данные для задания 5

Задание 6. Произвести прогнозирование спроса товаров народного потребления торгового центра на декабрь текущего года добавлением к графику полиномиальной линии тренда

Исходные данные представлены на рис. 12.

Рисунок 12 – Исходные данные для задания 6