Green-sell.info

Новые технологии
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как посчитать динамику в excel

Диаграмма темпов роста в Excel

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


1. Исходные данные

Предположим, нам нужно показать динамику темпов роста выручки:

Добавим в таблицу строку «подписи» — с суммой немного больше исходного значения. И строку «рост», где будет рассчитан прирост выручки к предыдущему периоду. В первой колонке проставляем #Н/Д для того, чтобы не значения этого столбца не выводились в диаграмме.


2. Вставляем гистограмму с накоплением

Выделяем таблицу с выручкой и новыми строками. Добавляем гистограмму с накоплением: меню Вставка → Гистограмма → Гистограмма с накоплением.


3. Рост и подписи превращаем в график с накоплением

Выделяем гистограмму мышкой, переходим в меню Конструктор → Изменить тип диаграммы → выбираем вид диаграммы Комбинированная, для данных «подписи» и «рост» выбираем тип диаграммы — график с накоплением. Благодаря этому график с малыми значениями «наложится» на график с большими значениями.


4. Добавляем подписи линии роста

Добавляем подписи для линии роста: выделяем на диаграмме линию роста, в меню переходим на вкладку Конструктор → Добавить элемент диаграммы → Метки данных → выбираем Слева.

Делаем линию роста на диаграмме невидимой: выделяем линию правой кнопкой мышки, нажимаем Формат ряда данных, назначаем тип линии = Нет линий.


5. Добавляем линии ряда данных, удаляем легенду

Выделяем столбцы гистограммы, переходим в меню Конструктор → Добавить элемент диаграммы → Линии → Линии ряда данных (если такая линия не появилась, проверьте, правильный ли у вас тип диаграммы — должна быть Гистограмма с накоплением).
Удаляем легенду.


6. Задаем тип стрелки

Задаем тип стрелки — выделяем линию правой кнопкой мышки → Формат линий ряда → задаем тип стрелки.
Готово! Подписи и эффекты добавлять по вкусу.

Расчёт динамики с отрицательными значениями

Расчёт динамики с отрицательными значениями

Финансисты периодически сталкиваются с расчетом процентного изменения от периода к периоду. Рассчитать такое изменение, что может быть проще?

Стандартная формула расчета динамики

Нужно вбить одну из двух формул:

  1. (Новое_значение – Старое_значение) / Старое_значение
  2. (Новое_значение / Старое_значение) -1

Обе формулы приводят к одному и тому же результату только когда все числа положительные. В этом случае совсем не важно какую формулу выбирать.

Казалось бы, всё просто!

Все трудности начинаются в тот момент, когда нужно посчитать изменение на отрицательных данных. Классическая ситуация чистая прибыль сменяется убытком и наоборот. Для публичных компаний это оборачивается газетными заголовками о том, что чистая прибыль компании по МСФО упала на 500%(. ) и так далее и тому подобное.

Да, действительно при смене значений с положительных на отрицательные Excel обычно «буксует» и начинает выдавать не корректные значения. Ещё бы, ведь Excel парень простой, ему вбили одну из двух формул, и он по ним считает. Приведу пример, чтобы было понятно о чём идёт речь:

Стандартная формула работает почти безотказно, но есть как минимум несколько проблем:

  1. В первом периоде 20Х1 ошибка #VALUE!, т.к. нет базы для расчета.
  2. В третьем периоде 20Х3 формула выдала падение на 100%, хотя по смыслу прибыль явно выросла
  3. В четвертом периоде формула выдает ошибку деления на ноль #DIV/0!

Получается, что наиболее часто используемая формула фактически работает только с значениями больше 0.

Как быть в такой ситуации?

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

Метод абсолютных значений

Метод абсолютных значений заключается в том, что в в качестве делителя используют значение по модулю.

Таким образом формула становится примерно такой:

(Новое_значение – Старое_значение) / ABS ( Старое_значение )

Формула ABS() в Excel используется для расчета модуля.

Из открыых источников мне даже удалось узнать, что авторитетнейшее финансовое издание The Wall Street Journal использует этот метод. Почему бы не взглянуть на него?

Итак, я рассчитал изменение с учётом метода абсолютных значений.

Соответственно применение данной формулы даёт некое улучшение в периоде 20Х3, но особо легче не стало. Как и раньше остаются проблемы с первым периодом и периодом 20х4.

При более деальном рассмотрении данной формулы вообще становится понятно, что она тоже не надежна. Сейчас поясню на примерах.

Пример №1. Переход от отрицательного к положительному значению

В примере наглядно видно, что чем меньше отрицательное значение (имеется в виду что -60 меньше -10), тем меньше % изменение. Не знаю как вам, но мне кажется это немного странным. Получается, что в абсолютных величинах изменение больше, а в процентах меньше. Мистика!

Читать еще:  Как убрать разделитель в excel

Пример №2. Переход от положительного к отрицательному значению

В этом примере также получается немного странно. Большее отрицательное изменение не означает большего процентного изменения. Всё это нужно учитывать работая с Excel, ведь не всегда можно получить релевантные результаты. И с этим можно жить, если держать это в уме.

Казалось бы, на этом можно остановиться, но есть ещё одно улучшение которым можно воспользоваться – это отображение положительных и отрицательных изменений.

Отображение положительных и отрицательных изменений

Альтернативное решение методу абсолютных значений – это отображение положительного и отрицательного изменения в случае, если одно из значений будет положительным.

Применив такой подход получились вполне приемлемые результаты вычислений.

Для отображения положительного и отрицательного изменений пришлось применить достаточно комплексную формулу:

= ЕСЛИОШИБКА ( ЕСЛИ ( МИН (Старое_значение ; Новое_значение ) 0; «Позит.»; «Отриц.» ); (Новое_значение / Старое_значение) -1; «н.п.»)

Что делает формула:

  1. Проверяет выдает ли формула ошибку и выдает «н.п.» (не применимо). В частности деление на ноль и ошибка первого периода.
  2. В случае если одно из значений отрицательно, то формула рассчитывает положительное или отрицательное изменение и выдает значения «Позит.» или «Отриц.»
  3. Если отрицательных значений нет, то применяется стандартная формула расчета динамики.

Вывод

Вывод в том, что даже в простых расчетах могут быть сложности и нужно внимательно смотреть на результаты расчетов (хоть и не всегда на это есть время и желание).

Только вам, уважаемый читатель решать хотите ли вы использовать предложенные в этой статье подходы, но если вам понравилось или есть что добавить – оставляйте свои комментарии или делитесь статьёй в социальных сетях.

No Comments

Post a Reply Отменить ответ

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.

Диаграмма темпов роста в Excel

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


1. Исходные данные

Предположим, нам нужно показать динамику темпов роста выручки:

Добавим в таблицу строку «подписи» — с суммой немного больше исходного значения. И строку «рост», где будет рассчитан прирост выручки к предыдущему периоду. В первой колонке проставляем #Н/Д для того, чтобы не значения этого столбца не выводились в диаграмме.


2. Вставляем гистограмму с накоплением

Выделяем таблицу с выручкой и новыми строками. Добавляем гистограмму с накоплением: меню Вставка → Гистограмма → Гистограмма с накоплением.


3. Рост и подписи превращаем в график с накоплением

Выделяем гистограмму мышкой, переходим в меню Конструктор → Изменить тип диаграммы → выбираем вид диаграммы Комбинированная, для данных «подписи» и «рост» выбираем тип диаграммы — график с накоплением. Благодаря этому график с малыми значениями «наложится» на график с большими значениями.


4. Добавляем подписи линии роста

Добавляем подписи для линии роста: выделяем на диаграмме линию роста, в меню переходим на вкладку Конструктор → Добавить элемент диаграммы → Метки данных → выбираем Слева.

Делаем линию роста на диаграмме невидимой: выделяем линию правой кнопкой мышки, нажимаем Формат ряда данных, назначаем тип линии = Нет линий.


5. Добавляем линии ряда данных, удаляем легенду

Выделяем столбцы гистограммы, переходим в меню Конструктор → Добавить элемент диаграммы → Линии → Линии ряда данных (если такая линия не появилась, проверьте, правильный ли у вас тип диаграммы — должна быть Гистограмма с накоплением).
Удаляем легенду.


6. Задаем тип стрелки

Задаем тип стрелки — выделяем линию правой кнопкой мышки → Формат линий ряда → задаем тип стрелки.
Готово! Подписи и эффекты добавлять по вкусу.

Трюки и хитрости в Excel — Как красиво визуализировать динамику в таблице.

Всем привет! Случайно наткнулся сегодня в сети на один интересный лайфхак, спешу сразу поделиться )) Но я не буду выкладывать пример файлика, специально, чтобы вы сами своими руками проделали все сами, так быстрее и легче запомнится. Нужно только желание.

Пригодится лайфхак в первую очередь тем кто постоянно анализирует в Excel, делает отчеты для руководства. Больше это нужно для наглядности. В общем довольно простой и креативный подход к созданию и представлению таблицы с помощью функции СИМВОЛ . Вот в таком виде у меня получилось за несколько минут построить динамику изменения данных в течении года:

Читать еще:  Excel доля от числа в процентах

1. Как найти символы?

Функция СИМВОЛ возвращает знак с заданным кодом. А все коды находятся, можно сказать, под боком, в самом Excel. Нужно зайти во вкладку ВСТАВКА и выбрать команду СИМВОЛ.

Откроется таблица со стандартными символами. Но если изменить шрифт на Wingdings, то нам предстанут совершенно иные символы. Они больше похожи на смайлики в первых мобильных чатах )) Но среди них есть и такие, с помощью которых можно неплохо оформить представление в таблицах и не только. В данном примере я покажу как можно использовать жирные стрелочки для визуализации изменения показателей, например, продаж.

2. Как вставить символ в таблицу?

Итак, для того чтобы построить динамику, в первую очередь установим значение для всего столбца с символами: нужно выбрать шрифт Wingdings и установить цвет шрифта красный . Не настроения, как в песне, а шрифта )). Выделяем диапазон от ячейки D2 и ниже и устанавливаем необходимое значение.

Затем прописываем несложную формулу при помощи функций ЕСЛИ и СИМВОЛ. Функция ЕСЛИ будет возвращать нам необходимое условие, а СИМВОЛ будет отображать результат. В нашем случае сравнивается помесячный результат продаж за 2016 и 2017 года. В каких-то месяц объем увеличился, а в каких то сократился. Но так как данное представление можно применять не только в тех случаях когда было изменение, возможно ведь что показатель остается на прежнем уровне, то я специально в первом месяце установил одинаковые итоги. Формула выглядит следующим образом:

Немного расшифрую формулу: Если в ячейке С2 значение больше чем в B2, то отображается символ с кодом 233, если они равны то символ 232, в остальных случаях, т.е. если меньше символ 234. Где взять номер символа? Все там же в таблице, которую мы просмотрели в п.1. У каждого символа есть свой код, он прописывается в нижней части окна.

Что у нас получилось? Мы записали в ячейку D2 формулу, предварительно установили необходимые значения по шрифту и цвету и получили результат в виде стрелочки стремящейся в право, так я отобразил что изменения не произошли.

3. Как установить цвет символа?

Если с кодами все понятно стало, то как установить нужный цвет когда по умолчанию стоит красный? Здесь тоже все просто. Нужно перейти во вкладку ГЛАВНАЯ и через команду УСЛОВНОЕ ФОРМАТИРОВАНИЕ создать ПРАВИЛО.

В открывшемся диалоговом окне , выбираем тип правила — использовать формулу. Еще одна формула, но совсем простая. Прописываем условие если наши данные равны.

Затем выбираем желаемый формат. Нам нужно установить цвет, поэтому кликаем на полосу выбора цвета и выбираем оранжевый. Вы можете выбрать какой вам угодно.

Нажимаем два раза ОК и создаем еще одно правило, но с условием что первое значение 2016 года больше аналогичного в 2017 году. Выбираем также цвет, раз у нас должно быть увеличение значит положительная динамика с зеленым цветом.

Опять нажимаем два раза ОК и получаем результат с оранжевой стрелочкой. Далее необходимо распространить наше условия на остальные ячейки. Просто протягиваем формулу вниз и любуемся нашей красотой. Теперь наглядно лучше понятно в какие месяца по сравнению с предыдущем годом продажи были больше.

Вот такая простая визуализация значительно повысит ваше мастерство владения Excel в глазах вашего руководителя )) Главное потом остаться на коне , а не под ним.

На этом у меня всё. Если вам понравился сегодняшний трюк, ставьте лайки и подписывайтесь на канал чтобы не пропустить еще более интересные материалы.

Если хотите посмотреть еще уроки загляните в СОДЕРЖАНИЕ , обязательно еще что-нибудь присмотрите )) Спасибо!

Решение статистических задач в EXCEL: Практикум , страница 4

2. Рассчитать квадраты отклонения индивидуальных значений от средних (xi-)2.

Для этого в ячейку первого значения устанавливается формула:

где вычисляется разность значения xi (в ячейке AT46) и среднего (в ячейке $AW$49), возведенная в степень 2 оператором «^».

Координата средней задана как неизменяемая (абсолютная).

1. Формула квадрата разности копируется в соседние ячейки строки (или столбца).

2. В ячейку результата дисперсии (например «AW50») установить функцию
«=СУММПРОИЗВ(AT48:AY48;AT47:AY47)/СУММ(AT47:AY47)»

где «СУММПРОИЗВ (AT48:AY48;AT47:AY47)» реализует сумму попарных произведений (xi-)2 (в ячейках «AT48:AY48») на fi (в ячейках «AT47:AY47»), т.е. числитель

;

СУММ(AT47:AY47) — сумма частот fi (в ячейках «AT47:AY47»);

«/» — оператор деления.

1. В ячейку результата СКО (например «AW51») установить функцию «=(AW50)^0,5»,

где вычисляется корень квадратный «^0.5» дисперсии в ячейке «AW50».

Коэффициент роста Кр, темп роста Тр, темп прироста, абсолютный прирост Δ, абсолютное значение 1% прироста А цепные вычисляются с использованием относительных ссылок, базисные — абсолютных и относительных.

Читать еще:  Создание и форматирование таблиц в excel

Цепные показатели динамики.

Пример расчета цепных показателей.

1. Исходные значения признака хi надо записать в массив ячеек расположенных в строке или столбце в примере в строке (S8:X8).

Количественные данные следует определить как «Числовые».

2. Формула коэффициента роста Кр=Хi+1/Хi вводится для первого 2001г. В ячейку «Т9» записывается результат деления содержимого ячейки «Т8» на содержимое ячейки «S8».

1. Формула копируется в соседние ячейки U8-X8.

2. Расчет темпа роста Тр=(Хi+1/Хi)*100% выполняется по той же формуле – для 2001г. «=T8/S8» с определением формата результата «Процентный». Затем формула копируется в соседние ячейки.

3. Расчет темпа прироста Тп=(Хi+1/Хi)*100% -100% реализуется для 2001г. формулой «=T8/S8-1» с определением формата результата «Процентный». Затем формула копируется в соседние ячейки.

4. Абсолютный прирост Δ= Хi+1-Хi для 2001г. — «=T8-S8» в «Числовом» формате. Затем формула копируется в соседние ячейки.

5. Абсолютное значение 1% прироста А= Δ/ Тп=(Хi+1-Хi)/( (Хi+1/Хi)*100% -100%) реализуется для 2001г. формулой «=(T8-S8)/((T8/S8)*100-100)» в «Числовом» формате. Затем формула копируется в соседние ячейки.

Базисные показатели динамики.

Пример расчета базисных показателей.

1. Исходные значения признака хi надо записать в массив ячеек расположенных в строке или столбце в примере в строке (S16:X16).

2. Количественные данные следует определить как «Числовые».

3. Формула коэффициента роста Кр=Хi/Х0 вводится для первого 2001г. В ячейку «Т17» записывается результат деления содержимого ячейки «Т16» на содержимое ячейки «$S$16» (базисное неизменное значение Х0 в ячейке «$S$16» задается как абсолютная ссылка).

1. Формула копируется в соседние ячейки U17-X17.

2. Расчет темпа роста Тр=(Хi/Х0)*100% выполняется по той же формуле – для 2001г. «=T16/$S$16» с определением формата результата «Процентный». Затем формула копируется в соседние ячейки.

  • АлтГТУ 419
  • АлтГУ 113
  • АмПГУ 296
  • АГТУ 266
  • БИТТУ 794
  • БГТУ «Военмех» 1191
  • БГМУ 171
  • БГТУ 602
  • БГУ 153
  • БГУИР 391
  • БелГУТ 4908
  • БГЭУ 962
  • БНТУ 1070
  • БТЭУ ПК 689
  • БрГУ 179
  • ВНТУ 119
  • ВГУЭС 426
  • ВлГУ 645
  • ВМедА 611
  • ВолгГТУ 235
  • ВНУ им. Даля 166
  • ВЗФЭИ 245
  • ВятГСХА 101
  • ВятГГУ 139
  • ВятГУ 559
  • ГГДСК 171
  • ГомГМК 501
  • ГГМУ 1966
  • ГГТУ им. Сухого 4467
  • ГГУ им. Скорины 1590
  • ГМА им. Макарова 299
  • ДГПУ 159
  • ДальГАУ 279
  • ДВГГУ 134
  • ДВГМУ 408
  • ДВГТУ 936
  • ДВГУПС 305
  • ДВФУ 949
  • ДонГТУ 497
  • ДИТМ МНТУ 109
  • ИвГМА 488
  • ИГХТУ 130
  • ИжГТУ 143
  • КемГППК 171
  • КемГУ 507
  • КГМТУ 269
  • КировАТ 147
  • КГКСЭП 407
  • КГТА им. Дегтярева 174
  • КнАГТУ 2909
  • КрасГАУ 345
  • КрасГМУ 629
  • КГПУ им. Астафьева 133
  • КГТУ (СФУ) 567
  • КГТЭИ (СФУ) 112
  • КПК №2 177
  • КубГТУ 138
  • КубГУ 107
  • КузГПА 182
  • КузГТУ 789
  • МГТУ им. Носова 367
  • МГЭУ им. Сахарова 232
  • МГЭК 249
  • МГПУ 165
  • МАИ 144
  • МАДИ 151
  • МГИУ 1179
  • МГОУ 121
  • МГСУ 330
  • МГУ 273
  • МГУКИ 101
  • МГУПИ 225
  • МГУПС (МИИТ) 636
  • МГУТУ 122
  • МТУСИ 179
  • ХАИ 656
  • ТПУ 454
  • НИУ МЭИ 640
  • НМСУ «Горный» 1701
  • ХПИ 1534
  • НТУУ «КПИ» 212
  • НУК им. Макарова 542
  • НВ 778
  • НГАВТ 362
  • НГАУ 411
  • НГАСУ 817
  • НГМУ 665
  • НГПУ 214
  • НГТУ 4610
  • НГУ 1992
  • НГУЭУ 499
  • НИИ 201
  • ОмГТУ 301
  • ОмГУПС 230
  • СПбПК №4 115
  • ПГУПС 2489
  • ПГПУ им. Короленко 296
  • ПНТУ им. Кондратюка 119
  • РАНХиГС 186
  • РОАТ МИИТ 608
  • РТА 243
  • РГГМУ 117
  • РГПУ им. Герцена 123
  • РГППУ 142
  • РГСУ 162
  • «МАТИ» — РГТУ 121
  • РГУНиГ 260
  • РЭУ им. Плеханова 122
  • РГАТУ им. Соловьёва 219
  • РязГМУ 125
  • РГРТУ 666
  • СамГТУ 130
  • СПбГАСУ 315
  • ИНЖЭКОН 328
  • СПбГИПСР 136
  • СПбГЛТУ им. Кирова 227
  • СПбГМТУ 143
  • СПбГПМУ 146
  • СПбГПУ 1598
  • СПбГТИ (ТУ) 292
  • СПбГТУРП 235
  • СПбГУ 577
  • ГУАП 524
  • СПбГУНиПТ 291
  • СПбГУПТД 438
  • СПбГУСЭ 226
  • СПбГУТ 193
  • СПГУТД 151
  • СПбГУЭФ 145
  • СПбГЭТУ «ЛЭТИ» 379
  • ПИМаш 247
  • НИУ ИТМО 531
  • СГТУ им. Гагарина 113
  • СахГУ 278
  • СЗТУ 484
  • СибАГС 249
  • СибГАУ 462
  • СибГИУ 1654
  • СибГТУ 946
  • СГУПС 1473
  • СибГУТИ 2083
  • СибУПК 377
  • СФУ 2423
  • СНАУ 567
  • СумГУ 768
  • ТРТУ 149
  • ТОГУ 551
  • ТГЭУ 325
  • ТГУ (Томск) 276
  • ТГПУ 181
  • ТулГУ 553
  • УкрГАЖТ 234
  • УлГТУ 536
  • УИПКПРО 123
  • УрГПУ 195
  • УГТУ-УПИ 758
  • УГНТУ 570
  • УГТУ 134
  • ХГАЭП 138
  • ХГАФК 110
  • ХНАГХ 407
  • ХНУВД 512
  • ХНУ им. Каразина 305
  • ХНУРЭ 324
  • ХНЭУ 495
  • ЦПУ 157
  • ЧитГУ 220
  • ЮУрГУ 306

Полный список ВУЗов

Чтобы распечатать файл, скачайте его (в формате Word).

Ссылка на основную публикацию
Adblock
detector