Как Построить Кумуляту в Excel по Данным Таблицы Статистика • Дополнительные сведения
Лабораторная работа по EXCEL — ГРАФИЧЕСКОЕ ИЗОБРАЖЕНИЕ СТАТИСТИЧЕСКИХ ДАННЫХ И ПРОГНОЗИРОВАНИЕ В
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.
33 конкурса для учеников 1–11 классов и дошкольников от проекта «Инфоурок»
ГРАФИЧЕСКОЕ ИЗОБРАЖЕНИЕ СТАТИСТИЧЕСКИХ ДАННЫХ И ПРОГНОЗИРОВАНИЕ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ.
Цель занятия. Изучение информационной технологии использования возможностей Excel для статистических расчетов, графического представления данных и прогнозирования.
Инструментарий. ПЭВМ IBM PC, программа MS Excel.
Литература.
1. Информационные технологии в профессиональной деятельности : учебное пособие/ Елена Викторовна Михеева. – М.: Образовательно-издательский центр «Академия», 2004.
Задание 1. С помощью диаграммы (обычная гистограмма) отобразить данные о численности населения России (млн. чел.) за 1970- 2005 гг.
Исходные данные представлены на рис.1, результаты работы на рис.3
Рис.1.
1. Откройте редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу (при стандартной установке MS Office выполните Пуск/Все программы/ Microsoft Excel ).
2. Создайте на листе 1 таблицу численности населения по образцу (рис.1). Для ввода значений лет создайте ряд чисел с интервалом в 7 лет (введите первые два значения даты — 1970 и 1977, выделите обе ячейки и протяните вправо за маркер автозаполнения до нужной конечной даты).
Рис.2.
Задание 2. Осуществить прогноз численности населения России на 2012 г. добавлением линии тренда к ряду данных графика.
1. Добавьте линию тренда к диаграмме, построенной в Задании 1. Для этого сделайте диаграмму активной щелком мыши по ней и в меню Диаграмма выберите команду Добавить линию тренда.
2. В открывшемся окне Линия тренда (рис.3) на вкладке Тип выберите вид тренда полиномиальный 4-й степени;
Рис.3
3. Для осуществления прогноза на вкладке Параметры введите название кривой «Линия тренда» и установите параметр прогноза — вперед на один период (рис.4).
Рис.4
4. На диаграмме будет показана линия тренда и прогноз на один период вперед (рис.5).
5. Добавьте линии сетки на диаграмме ( Диаграмма/ Параметры диаграммы/ Линии сетки ).
Измените цену одного деления оси Y с 5 на 1 (Выполните двойной щелчок по оси и на вкладке «Шкала» задайте новые значения).
Средствами рисования проведите линию красного цвета (на ось Y) для определения значения прогноза на 2012 г.
Если вы все сделали правильно, то прогноз численности населения России по линии тренда составит 131 млн.чел.
Внесите численное значение прогноза на 2012 г. в исходную таблицу.
Задание 3. Построить график о числе заключенных браков населением России. Добавить линейную линию тренда и составить прогноз на три периода вперед.
Исходные данные представлены на рис. 6, результаты работы на рис. 7.
Рис. 6
Рис. 7
Создание прогноза в Excel для Windows
Как видно из рисунка, первый интервал включает только одно минимальное значение 113 (точнее, включены все значения меньшие или равные минимальному). Если бы в массиве было 2 или более значения 113, то в первый интервал попало бы соответствующее количество чисел (2 или более).
Анализ временных рядов и прогнозирование в Excel на примере
Анализ временных рядов позволяет изучить показатели во времени. Временной ряд – это числовые значения статистического показателя, расположенные в хронологическом порядке.
Подобные данные распространены в самых разных сферах человеческой деятельности: ежедневные цены акций, курсов валют, ежеквартальные, годовые объемы продаж, производства и т.д. Типичный временной ряд в метеорологии, например, ежемесячный объем осадков.
Временные ряды в Excel
Если фиксировать значения какого-то процесса через определенные промежутки времени, то получатся элементы временного ряда. Их изменчивость пытаются разделить на закономерную и случайную составляющие. Закономерные изменения членов ряда, как правило, предсказуемы.
Сделаем анализ временных рядов в Excel. Пример: торговая сеть анализирует данные о продажах товаров магазинами, находящимися в городах с населением менее 50 000 человек. Период – 2012-2015 гг. Задача – выявить основную тенденцию развития.
На вкладке «Данные» нажимаем кнопку «Анализ данных». Если она не видна, заходим в меню. «Параметры Excel» — «Надстройки». Внизу нажимаем «Перейти» к «Надстройкам Excel» и выбираем «Пакет анализа».
Подключение настройки «Анализ данных» детально описано здесь.
Из предлагаемого списка инструментов для статистического анализа выбираем «Экспоненциальное сглаживание». Этот метод выравнивания подходит для нашего динамического ряда, значения которого сильно колеблются.
Заполняем диалоговое окно. Входной интервал – диапазон со значениями продаж. Фактор затухания – коэффициент экспоненциального сглаживания (по умолчанию – 0,3). Выходной интервал – ссылка на верхнюю левую ячейку выходного диапазона. Сюда программа поместит сглаженные уровни и размер определит самостоятельно. Ставим галочки «Вывод графика», «Стандартные погрешности».
Закрываем диалоговое окно нажатием ОК. Результаты анализа:
Для расчета стандартных погрешностей Excel использует формулу: =КОРЕНЬ(СУММКВРАЗН(‘диапазон фактических значений’; ‘диапазон прогнозных значений’)/ ‘размер окна сглаживания’). Например, =КОРЕНЬ(СУММКВРАЗН(C3:C5;D3:D5)/3).
Прогнозирование временного ряда в Excel
Составим прогноз продаж, используя данные из предыдущего примера.
На график, отображающий фактические объемы реализации продукции, добавим линию тренда (правая кнопка по графику – «Добавить линию тренда»).
Выбираем полиномиальный тренд, что максимально сократить ошибку прогнозной модели.
R2 = 0,9567, что означает: данное отношение объясняет 95,67% изменений объемов продаж с течением времени.
Уравнение тренда – это модель формулы для расчета прогнозных значений.
Большинство авторов для прогнозирования продаж советуют использовать линейную линию тренда. Чтобы на графике увидеть прогноз, в параметрах необходимо установить количество периодов.
В нашем примере все-таки экспоненциальная зависимость. Поэтому при построении линейного тренда больше ошибок и неточностей.
Для прогнозирования экспоненциальной зависимости в Excel можно использовать также функцию РОСТ.
При составлении прогнозов нельзя использовать какой-то один метод: велика вероятность больших отклонений и неточностей.
Как быстро посчитать итоги в таблице Excel
График нормального распределения имеет форму колокола и симметричен относительно среднего значения. Получить такое графическое изображение можно только при огромном количестве измерений. В Excel для конечного числа измерений принято строить гистограмму.
Публикуя свою персональную информацию в открытом доступе на нашем сайте вы, даете согласие на обработку персональных данных и самостоятельно несете ответственность за содержание высказываний, мнений и предоставляемых данных. Мы никак не используем, не продаем и не передаем ваши данные третьим лицам.