Рассчитать показатели динамического ряда в эксель
Обновлено: 04.07.2024
Всем привет! Случайно наткнулся сегодня в сети на один интересный лайфхак, спешу сразу поделиться )) Но я не буду выкладывать пример файлика, специально, чтобы вы сами своими руками проделали все сами, так быстрее и легче запомнится. Нужно только желание.
Пригодится лайфхак в первую очередь тем кто постоянно анализирует в Excel, делает отчеты для руководства. Больше это нужно для наглядности. В общем довольно простой и креативный подход к созданию и представлению таблицы с помощью функции СИМВОЛ . Вот в таком виде у меня получилось за несколько минут построить динамику изменения данных в течении года:
1. Как найти символы?
Функция СИМВОЛ возвращает знак с заданным кодом. А все коды находятся, можно сказать, под боком, в самом Excel. Нужно зайти во вкладку ВСТАВКА и выбрать команду СИМВОЛ.
Откроется таблица со стандартными символами. Но если изменить шрифт на Wingdings, то нам предстанут совершенно иные символы. Они больше похожи на смайлики в первых мобильных чатах )) Но среди них есть и такие, с помощью которых можно неплохо оформить представление в таблицах и не только. В данном примере я покажу как можно использовать жирные стрелочки для визуализации изменения показателей, например, продаж.
2. Как вставить символ в таблицу?
Итак, для того чтобы построить динамику, в первую очередь установим значение для всего столбца с символами: нужно выбрать шрифт Wingdings и установить цвет шрифта красный . Не настроения, как в песне, а шрифта )). Выделяем диапазон от ячейки D2 и ниже и устанавливаем необходимое значение.
Затем прописываем несложную формулу при помощи функций ЕСЛИ и СИМВОЛ. Функция ЕСЛИ будет возвращать нам необходимое условие, а СИМВОЛ будет отображать результат. В нашем случае сравнивается помесячный результат продаж за 2016 и 2017 года. В каких-то месяц объем увеличился, а в каких то сократился. Но так как данное представление можно применять не только в тех случаях когда было изменение, возможно ведь что показатель остается на прежнем уровне, то я специально в первом месяце установил одинаковые итоги. Формула выглядит следующим образом:
Немного расшифрую формулу: Если в ячейке С2 значение больше чем в B2, то отображается символ с кодом 233, если они равны то символ 232, в остальных случаях, т.е. если меньше символ 234. Где взять номер символа? Все там же в таблице, которую мы просмотрели в п.1. У каждого символа есть свой код, он прописывается в нижней части окна.
Что у нас получилось? Мы записали в ячейку D2 формулу, предварительно установили необходимые значения по шрифту и цвету и получили результат в виде стрелочки стремящейся в право, так я отобразил что изменения не произошли.
3. Как установить цвет символа?
Если с кодами все понятно стало, то как установить нужный цвет когда по умолчанию стоит красный? Здесь тоже все просто. Нужно перейти во вкладку ГЛАВНАЯ и через команду УСЛОВНОЕ ФОРМАТИРОВАНИЕ создать ПРАВИЛО.
В открывшемся диалоговом окне , выбираем тип правила - использовать формулу. Еще одна формула, но совсем простая. Прописываем условие если наши данные равны.
Затем выбираем желаемый формат. Нам нужно установить цвет, поэтому кликаем на полосу выбора цвета и выбираем оранжевый. Вы можете выбрать какой вам угодно.
Нажимаем два раза ОК и создаем еще одно правило, но с условием что первое значение 2016 года больше аналогичного в 2017 году. Выбираем также цвет, раз у нас должно быть увеличение значит положительная динамика с зеленым цветом.
Опять нажимаем два раза ОК и получаем результат с оранжевой стрелочкой. Далее необходимо распространить наше условия на остальные ячейки. Просто протягиваем формулу вниз и любуемся нашей красотой. Теперь наглядно лучше понятно в какие месяца по сравнению с предыдущем годом продажи были больше.
Вот такая простая визуализация значительно повысит ваше мастерство владения Excel в глазах вашего руководителя )) Главное потом остаться на коне , а не под ним.
На этом у меня всё. Если вам понравился сегодняшний трюк, ставьте лайки и подписывайтесь на канал чтобы не пропустить еще более интересные материалы.
Если хотите посмотреть еще уроки загляните в СОДЕРЖАНИЕ , обязательно еще что-нибудь присмотрите )) Спасибо!
Практическое моделирование экономических ситуаций подразумевает разработку прогнозов. С помощью средств Excel можно реализовать такие эффективные способы прогнозирования, как: экспоненциальное сглаживание, построение регрессий, скользящее среднее. Рассмотрим подробнее использование метода скользящего среднего.
Использование скользящих средних в Excel
Метод скользящей средней – один из эмпирических методов для сглаживания и прогнозирования временных рядов. Суть: абсолютные значения ряда динамики меняются на средние арифметические значения в определенные интервалы. Выбор интервалов осуществляется способом скольжения: первые уровни постепенно убираются, последующие – включаются. В результате получается сглаженный динамический ряд значений, позволяющий четко проследить тенденцию изменений исследуемого параметра.
Временной ряд – это множество значений X и Y, связанных между собой. Х – интервалы времени, постоянная переменная. Y – характеристика исследуемого явления (цена, например, действующая в определенный период времени), зависимая переменная. С помощью скользящего среднего можно выявить характер изменений значения Y во времени и спрогнозировать данный параметр в будущем. Метод действует тогда, когда для значений четко прослеживается тенденция в динамике.
Например, нужно спрогнозировать продажи на ноябрь. Исследователь выбирает количество предыдущих месяцев для анализа (оптимальное число m членов скользящего среднего). Прогнозом на ноябрь будет среднее значение параметров за m предыдущих месяца.
Задача. Проанализировать выручку предприятия за 11 месяцев и составить прогноз на 12 месяц.
Сформируем сглаженные временные ряды методом скользящего среднего посредством функции СРЗНАЧ. Найдем средние отклонения сглаженных временных рядов от заданного временного ряда.
- По значениям исходного временного ряда строим сглаженный временный ряд методом скользящего среднего по данным за 2 предыдущих месяца. Формула скользящей средней в Excel. Используя маркер автозаполнения, копируем формулу на диапазон ячеек С6:С14.
- Аналогично строим ряд значений трехмесячного скользящего среднего. Формула:
- По такому же принципу формируем ряд значений четырехмесячного скользящего среднего.
- Построим график заданного временного ряда и рассчитанные относительно его значений прогнозы по данному методу. На рисунке видно, что линии тренда скользящего среднего сдвинуты относительно линии исходного временного ряда. Это объясняется тем, что рассчитанные значения сглаженных временных рядов запаздывают по сравнению с соответствующими значениями заданного ряда. Ведь расчеты базировались на данных предыдущих наблюдений.
- Рассчитаем абсолютные, относительные и средние квадратичные отклонения по сглаженным временным рядам. Абсолютные отклонения:
Средние квадратичные отклонения:
При расчете отклонений брали одинаковое число наблюдений. Это необходимо для того, чтобы провести сравнительный анализ погрешностей.
После сопоставления таблиц с отклонениями стало видно, что для составления прогноза по методу скользящей средней в Excel о тенденции изменения выручки предприятия предпочтительнее модель двухмесячного скользящего среднего. У нее минимальные ошибки прогнозирования (в сравнении с трех- и четырехмесячной).
Прогнозное значение выручки на 12 месяц – 9 430 у.е.
Применение надстройки «Пакет анализа»
Для примера возьмем ту же задачу.
На вкладке «Данные» находим команду «Анализ данных». В открывшемся диалоговом окне выбираем «Скользящее среднее»:
Установив флажок в поле «Стандартные погрешности», мы автоматически добавляем в таблицу столбец со статистической оценкой погрешности.
Точно так же находим скользящее среднее по трем месяцам. Меняется только интервал (3) и выходной диапазон.
Сравнив стандартные погрешности, убеждаемся в том, что модель двухмесячного скользящего среднего больше подходит для сглаживания и прогнозирования. Она имеет меньшие стандартные погрешности. Прогнозное значение выручки на 12 месяц – 9 430 у.е.
Составлять прогнозы по методу скользящего среднего просто и эффективно. Инструмент точно отражает изменения основных параметров предыдущего периода. Но выйти за пределы известных данных нельзя. Поэтому для долгосрочного прогнозирования применяются другие способы.
4. Заполните графу «Абс. Значение 1% прироста». Для этого введите в ячейку F4 формулу: =B3/100.
5. В графу «Показатель наглядности» заносится отношение (в%) каждого уровня к исходному уровню на 1985 г. Для этого введите в ячейку G3 формулу: =B4/65,8*100 или B4/$B$3*100. Знак $ включается в формулу, чтобы адрес ячейки B3 не изменялся, как обычно при копировании.
6. Скопируйте формулы из ячеек с уже вставленными формулами в пустые ячейки (например: копируете ячейку D4 и вставляете ее в ячейки D5-D9).
КОНТРОЛЬНЫЕ ВОПРОСЫ:
Дайте определение динамического ряда. Какие Вы знаете типы динамических рядов?
Что такое преобразование динамического ряда?
Какие Вы знаете методы выравнивания динамического ряда? Какой из методов выравнивания является более точным?
Какие показатели свидетельствуют о скорости изменений уровней динамического ряда?
ТЕСТОВЫЕ ЗАДАНИЯ:
1. 1. Динамический ряд — это:
а) значения количественного признака (варианты), расположенные в определенном порядке и отличающиеся друг от друга по своему значению; б) ряд, состоящий из однородных сопоставимых значений признака,
характеризующих изменение какого-либо явления (процесса) во времени; в) атрибутивные значения признака, характеризующие качественное
состояние явления в динамике.
2. Динамический ряд может быть представлен: а) абсолютными величинами; б) средними величинами; в) относительными величинами.
3. Способы выравнивания динамического ряда: а) укрупнение интервалов; б) расчет групповой средней;
в) вычисление скользящей величины; г) метод наименьших квадратов.
4. Основными показателями скорости изменений явления в динамическом ряду являются:
а) темп роста; б) абсолютный прирост;
в) темп прироста; г) значение 1% прироста;
д) средний темп прироста.
5. При сравнении нескольких динамических рядов с разными исходными уровнями необходимо рассчитывать показатель динамического ряда: а) темп роста; б) абсолютный прирост;
в) темп прироста; г) значение 1% прироста;
д) средний темп прироста.
6. С какой целью должно проводиться выравнивание динамического ряда:
а) для выявления частоты распространения явлений или событий; б) для установления тенденций при изучении явлений и процессов; в) для доказательства влияния факторов; г) для определения скорости изменения процесса.
7. Преобразование динамического ряда — это действия необходимые: а) для установления тенденций за каждый период времени; б) для установления тенденций по отношению к одному периоду, принятому за единицу (100%); в) для установления закономерностей динамики процесса;
г) для выявления влияния факторов.
Контрольные задания Задача 1. При анализе ежемесячной заболеваемости скарлатиной детей
в возрасте до 7 лет в городе Н. в изучаемом году были получены следующие показатели динамического ряда: абсолютный прирост = + 0,5, темп прироста = +8,0%; темп роста = 7,0%.
1. По каким из представленных показателей можно судить о скорости изменения заболеваемости во времени?
2. Достаточно ли представленных в условии задачи данных для Вашего заключения о необходимости срочного планирования мероприятий по снижению заболеваемости скарлатиной на следующий год?
Задача 2. За последнее десятилетие отмечается увеличение числа выпуска врачей различного профиля в медицинских вузах № 1 и № 2, особенно увеличился выпуск в позапрошлом и прошлом годах.Показатели
динамики выпуска врачей вуза № 1 за последний год составили: абсолютный прирост = 50 человек, темп прироста = +8%, а вуза № 2 за последний год — абсолютный прирост = 60 человек, темп прироста = +10%.
1. Можно ли сделать вывод, что вуз № 2 более быстро решает проблему недостаточной численности подготовленных врачей? Какими показателями Вы воспользовались?
2. Достаточно ли представленных данных в условии задачи для суждения о приоритете в тенденциях по подготовке врачей в вузах?
Задача 3. В условиях реформирования здравоохранения в районах А. и Б. было проведено сокращение коечного фонда с увеличением при этом объема внебольничной помощи. 1% снижения (убыли) коечного фонда в районе А. составил 2%, в районе Б. — 3%, а средний темп снижения (убыли)
— 5 и 7,5%, соответственно.
1. В каком из районов сокращение коечного фонда идет быстрее. На основании какого показателя Вы сделали этот вывод?
2. Какие еще показатели дополнят анализ процесса сокращения коечного фонда в 2 районах?
Задача 4. В городе Н. численность населения за последние 5 лет составляла в динамике: 1-й год - 100 000 человек, 2-й год - 90 000, 3-й год - 80 000, 4-й год — 70 000 и 5-й год — 60 000 человек. Обеспеченность врачами за этот же период составила соответственно: 25, 23, 24, 18 и 20 на 10 000 населения.
1. Являются ли исходные данные основой для составления динамического ряда и его последующего анализа?
2. Какие показатели динамического ряда необходимо рассчитать для углубленного анализа изменений численности населения и обеспеченности врачами?
1. Кобринский Б.А., Зарубина Т.В. Медицинская информатика: Учебник. М: изд. "Академия", 2009.
2. Применение методов статистического анализа для изучения общественного здоровья и здравоохранения: Учебное пособие для практических занятий / Под ред. В.З.Кучеренко. – М.:ГЭОТАР-МЕД, 2004. –
1. Зайцев В.М., Лифляндский В.Г. Маринкин В.И. Прикладная медицинская статистика. – Спб: ООО «Издательства ФОЛИАНТ», 2003. – 432 с.
2. Карась С.И. Информационные основы принятия решений в медицине: Учебное пособие. – Томск: Печатная мануфактура, 2003.- 145с.
3. Чернов В.И., Родионов О.В., Есауленко И.Э. и др. Медицинская информатика: Учебное пособие.- Воронеж, 2004. – 282с.: ил.
5. Гельман В.Я. Медицинская информатика: практикум. – СПб: Питер, 2001. -480с. – (Серия "Национальная медицинская библиотека").
6. Богданов А.К., Проценко В.Д. Практические применения современных методов анализа изображений в медицине: Учебное пособие. – М.: РУДН,
7. Санников А.Г., Егоров Д.Б., Скудных А.С., Рухлова С.А. Практикум по медицинской информатике: автоматизированное рабочее место врача и системы поддержки принятия врачебного решения. – Тюмень: П.П.Ш., 2009.
ВпрограммеMS Excel имеется целый ряд возможностей углубленной статистической обработки динамических рядов.
В качестве примера создадим в Excel таблицу «Динамика случаев заболеваний с временной утратой трудоспособности» (Рисунок 113).
После запуска Excel введите в ячейку A2 слово: Год и нажмите клавишу [Enter]. В ячейки A3-A9 введите года: 1985..1991.
Чтобы завершить создание таблицы запишите в ячейки B1-G2 названия столбцов, а также введите в ячейки B3-B9 числовые данные.(Рисунок 113)
Рисунок 113. Таблица динамики заболеваемости с временной утратой способности (ЗВУТ)
Заполните пустые столбцы таблицы. В графу "Абсолютный прирост" занесите разность между последующим и предыдущим уровнями. Для этого введите в ячейку С4 формулу: =В4-В3
В графу "Темп роста" заносится отношение (в %) каждого последующего уровня к предыдущему. Для этого введите в ячейку D4 формулу: =В4/В3*100
В ячейку графы "Темп прироста" Е5 занесите =D4-100
Заполните графу "Абс.значение 1% прироста". Для этого введите в ячейку F4 формулу: =B3/100
В графу "Показатель наглядности" заносится отношение (в %) каждого уровня к исходному уровню на 1985 г. Для этого введите в ячейку G3 формулу: = В4/$B$3*100. Знак $ включается в формулу, что бы адрес ячейки В3 не изменялся, как обычно при копировании.
Скопируйте формулы из ячеек C3:G3 в ячейки C4:G9 с помощью команды:
- выделите мышкой блок ячеек C3:G3;
- установите указатель мыши в левый нижний угол выделенного блока. Этот угол обычно отмечен небольшим квадратиком, когда Вы к нему подведете курсор мыши, стрелка курсора примет вид знака +. После этого, нажмите правую клавишу мыши и, не отпуская её, переместитесь в ячейку G9. В результате этой операции область ячеек С3:G9 окажется заполненной скопированными формулами, т.е. подготовленный ранее макет таблицы окажется наполненным результатами вычислений.
Как уже отмечалось, математическое выражение закономерностей динамики данных можно получить с помощью функции экспоненциального сглаживания.
Например: Необходимо провести сглаживание числового ряда, отражающего динамику травматизма за 1982-1993 гг.
Предварительно в таблице MS Excel необходимо разместить исходные данные. (Рисунок 114).
Затем строим линейный график с помощью функции «Добавить диаграмму. На диаграмме, для удобства счета, по оси Х вместо значения года выставляем номер года (1,2,3 и т.д.). Для повышения изобразительности диаграммы по вертикальной оси в качестве точки отсчета выбираем не 0, а 70.
Щелкнув правой кнопкой мыши по линии графика вызываем всплывающее меню, из которого выбираем функцию Добавить линию тренда.
Затем последовательно в открывшейся закладке выбираем Тип линии тренда Линейная (Рисунок 117). Затем, переключив ярлык закладки на Параметры, устанавливаем параметры Прогноз вперед на 1 период, устанавливаем флажки: Показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации R2 (Рисунок 118).
Рисунок 119 Диаграмма выравнивания динамического ряда с помощью линейной функции Y=-0,7867х +90,864.
После этого нажмите на клавишу [ОК]. На экране появится изображение графика исходного ряда данных, аппроксимирующей линии (линии сглаживания) и уравнения аппроксимирующей функции. По своему виду эта функция представляет собой уравнение регрессии. Параметр R 2 показывает насколько точно соответствует вычисленное уравнение регрессии истинной тенденции динамического ряда. Максимально возможное значение R 2 =1,0 или 100%. В данном примере, в случае использования аппроксимации (сглаживания) с помощью линейной функции, R 2 =0,24 или 24%. (См. раздел «Коэффициент линейной корреляции» «коэффициент детерминации»).
С помощью этого коэффициента можно подбирать функцию наиболее полно аппроксимирующую ту или иную тенденцию. Например, при анализе этих же исходных данных полином 3 степени дает более полную аппроксимацию, соответственно, R 2 будет равен 0,56.
С помощью рассмотренной функции мастера диаграмм можно производить прогнозирование значений Y: в данном случае, когда задавался период прогноза на 1 единицу, на экране изображение диаграммы приводится с отображением точки прогноза (13 по оси Х).
Читайте также: