Как Построить Кумуляту в Excel по Данным Таблицы Статистика • Дополнительные сведения

Лабораторная работа по EXCEL — ГРАФИЧЕСКОЕ ИЗОБРАЖЕНИЕ СТАТИСТИЧЕСКИХ ДАННЫХ И ПРОГНОЗИРОВАНИЕ В

Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.

33 конкурса для учеников 1–11 классов и дошкольников от проекта «Инфоурок»

ГРАФИЧЕСКОЕ ИЗОБРАЖЕНИЕ СТАТИСТИЧЕСКИХ ДАННЫХ И ПРОГНОЗИРОВАНИЕ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ.

Цель занятия. Изучение информационной технологии использования возможностей Excel для статистических расчетов, графического представления данных и прогнозирования.
Инструментарий. ПЭВМ IBM PC, программа MS Excel.

Литература.
1. Информационные технологии в профессиональной деятельности : учебное пособие/ Елена Викторовна Михеева. – М.: Образовательно-издательский центр «Академия», 2004.

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

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

hello_html_546c5e3.png

Рис.1.

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

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

hello_html_4da0fe74.png

Рис.2.

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

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

2. В открывшемся окне Линия тренда (рис.3) на вкладке Тип выберите вид тренда полиномиальный 4-й степени;

hello_html_3d6aa299.png

Рис.3

3. Для осуществления прогноза на вкладке Параметры введите название кривой «Линия тренда» и установите параметр прогноза — вперед на один период (рис.4).

hello_html_m6165e236.png

Рис.4

4. На диаграмме будет показана линия тренда и прогноз на один период вперед (рис.5).

hello_html_m401010a8.png

5. Добавьте линии сетки на диаграмме ( Диаграмма/ Параметры диаграммы/ Линии сетки ).

hello_html_m2358ed1e.png

Измените цену одного деления оси Y с 5 на 1 (Выполните двойной щелчок по оси и на вкладке «Шкала» задайте новые значения).

hello_html_6a1b0281.png

Средствами рисования проведите линию красного цвета (на ось Y) для определения значения прогноза на 2012 г.

hello_html_7d4346a6.png

Если вы все сделали правильно, то прогноз численности населения России по линии тренда составит 131 млн.чел.

Внесите численное значение прогноза на 2012 г. в исходную таблицу.

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

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

hello_html_mc72a57c.png

Рис. 6

hello_html_m6165e236.png

Рис. 7

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Единственный недостаток функции Автосумма с ее помощью невозможно считать итоги отдельных ячеек или столбцов, которые расположены далеко друг от друга. Если же вы хотите что-то уточнить, я с радостью помогу!
два столбца, представляющие доверительный интервал (вычисленные с помощью функции ПРЕДСКАЗ.ЕTS.ДОВИНТЕРВАЛ). Эти столбцы отображаются только при проверке доверительный интервал в разделе Параметры.
Как Построить Кумуляту в Excel по Данным Таблицы Статистика • Дополнительные сведения

Создание прогноза в 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% изменений объемов продаж с течением времени.

Уравнение тренда – это модель формулы для расчета прогнозных значений.

Большинство авторов для прогнозирования продаж советуют использовать линейную линию тренда. Чтобы на графике увидеть прогноз, в параметрах необходимо установить количество периодов.

Прогноз.

Пример1.

В нашем примере все-таки экспоненциальная зависимость. Поэтому при построении линейного тренда больше ошибок и неточностей.

Для прогнозирования экспоненциальной зависимости в Excel можно использовать также функцию РОСТ.

РОСТ.

При составлении прогнозов нельзя использовать какой-то один метод: велика вероятность больших отклонений и неточностей.

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Если выбор количества интервалов или их диапазонов не устраивает, то можно в диалоговом окне указать нужный массив интервалов если интервал карманов включает текстовый заголовок, то нужно установить галочку напротив поля Метка. Если же вы хотите что-то уточнить, я с радостью помогу!
Примечание : Похоже, что инструмент Гистограмма для подсчета общего количества интервалов (с учетом первого) использует формулу =ЦЕЛОЕ(КОРЕНЬ(СЧЕТ( Исходные_данные )))+1

Как быстро посчитать итоги в таблице Excel

График нормального распределения имеет форму колокола и симметричен относительно среднего значения. Получить такое графическое изображение можно только при огромном количестве измерений. В Excel для конечного числа измерений принято строить гистограмму.

Оставить отзыв

Публикуя свою персональную информацию в открытом доступе на нашем сайте вы, даете согласие на обработку персональных данных и самостоятельно несете ответственность за содержание высказываний, мнений и предоставляемых данных. Мы никак не используем, не продаем и не передаем ваши данные третьим лицам.