Перейти к содержимому

Как показать прирост на графике в excel

  • автор:

Показ тенденций изменения данных с помощью спарклайнов

Спарклайны — это небольшие диаграммы в ячейках листа, визуально представляющие данные. С помощью спарклайнов можно показывать тенденции в рядах значений (например, сезонные повышения и спады или экономические циклы) и выделять максимальные и минимальные значения. Чтобы добиться максимального эффекта, спарклайны следует располагать рядом с соответствующими данными.

Браузер не поддерживает видео.

Добавление спарклайна

  1. Выделите пустую ячейку в конце строки данных.
  2. На вкладке Вставка выберите тип спарклайна, например График или Гистограмма.
  3. Выделите ячейки в строке и нажмите кнопку ОК.
  4. Другие строки данных? Перетащите его, чтобы добавить спарклайн для каждой строки.

Форматирование спарклайн-диаграммы

  1. Выделите спарклайн-диаграмму.
  2. Выберите Спарклайн и выберите один из вариантов.
    • Нажмите кнопку График, Гистограмма или Выигрыш/проигрыш, чтобы изменить тип диаграммы.
    • Установите флажок Маркеры, чтобы выделить отдельные значения в спарклайн-диаграмме.
    • Выберите стиль спарклайна в группе Стиль.
    • Нажмите кнопку Цвет спарклайна и выберите цвет.
    • Нажмите кнопку Цвет спарклайна и выберите пункт Толщина, чтобы выбрать ширину спарклайна.
    • Нажмите кнопку Цвет маркера, чтобы изменить цвет маркеров.
    • Если данные содержат положительные и отрицательные значения, нажмите кнопку Ось для отображения оси.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Как построить диаграмму «водопад» (waterfall)

Каскадная диаграмма водопад (waterfall)

Все чаще и чаще встречаю в отчетности разных компаний и слышу просьбы от слушателей на тренингах объяснить как строится каскадная диаграмма отклонений — она же «водопад», она же «waterfall», она же «мост», она же «bridge» и т.д. Выглядит она примерно так:
Издали действительно похожа на каскад водопадов на горной реке или навесной мост — кто что видит 🙂
Особенность такой диаграммы том, что:

  • Мы наглядно видим начальное и конечное значение параметра (первый и последний столбцы).
  • Положительные изменения (рост) отображаются одним цветом (обычно зеленым ), а отрицательные (спад) — другим (обычно красным ).
  • Иногда в диаграмме могут присутствовать ещё и столбцы промежуточных итогов ( серые , приземленные на ось Х столбцы).

В повседневной жизни такие диаграммы используются обычно в следующих случаях:

  • Наглядное отображение динамики какого-либо процесса во времени: потока наличности (cash-flow), инвестиций (вкладываем деньги в проект и получаем от него прибыль).
  • Визуализация выполнения плана (крайний левый столбик в диаграмме — факт, крайний правый — план, вся диаграмма отображает наш процесс движения к желаемому результату)
  • Когда нужно наглядно показать факторы, влияющие на наш параметр (факторный анализ прибыли — из чего она складывается).

Есть несколько способов построения такой диаграммы — всё зависит от вашей версии Microsoft Excel.

Способ 1. Самый простой: встроенный тип в Excel 2016 и новее

Если у вас Excel 2016, 2019 или новее (или Office 365), то построение такой диаграммы не составит труда — в этих версиях Excel такой тип уже встроен по умолчанию. Нужно будет лишь выделить таблицу с данными и выбрать на вкладке Вставка (Insert) команду Каскадная (Waterfall) :

Вставка диаграммы водопад в Excel 2016-2019

В результате мы получим практически готовую уже диаграмму:

Готовая диаграмма

Сразу же можно настроить желаемые цвета заливки для положительных и отрицательных столбцов. Удобнее всего это сделать, выделив соответствующие ряды Увеличение и Уменьшение прямо в легенде и, щёлкнув по ним правой кнопкой мыши, выбрать команду Заливка (Fill) :

Выделение рядов через легенду

Если нужно добавить в диаграмму столбцы с промежуточными итогами или финальный столбец-итог, то удобнее всего это сделать с помощью функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) или АГРЕГАТ (AGGREGATE) . Они посчитает накопленную с начала таблицы сумму, исключив при этом из нее выше расположенные аналогичные итоги:

Функция АГРЕГАТ для промежуточных итогов

В данном случае, первый аргумент (9) — это код математической операции суммирования, а второй (0) заставляет функцию не учитывать в результатах уже вычисленные итоги за предыдущие кварталы.

После добавления строк с итогами останется выделить на диаграмме появившиеся итоговые колонки (сделать два последовательных одиночных щелчка по столбцу) и, щёлкнув правой кнопкой мыши, выбрать команду Установить в качестве итога (Set as total) :

Установка итоговых столбцов

Выбранный столбец «приземлится» на ось Х и автоматически поменяет цвет на серый.

Вот, собственно, и всё — диаграмма-водопад готова:

Готовая диаграмма с таблицей

Способ 2. Универсальный: невидимые столбцы

Если у вас Excel 2013 или более древние версии (2010, 2007 и т.д.), то описанный выше способ вам не подойдёт. Придется идти обходным путем и выпиливать недостающую каскадную диаграмму из обычной гистограммы с накоплением (суммированием столбиков друг на друга).

Хитрость тут заключается в использовании прозрачных столбцов-подпорок, приподнимающих наши красные и зеленые ряды данных на нужную высоту:

Диаграмма водопад с прозрачными столбцами

Для построения такой диаграммы нам потребуется добавить к исходным данным еще несколько вспомогательных колонок с формулами:

Дополнительные столбцы с формулами

  • Во-первых, нужно разделить наш исходный столбец, выделив положительные и отрицательные значения в разные колонки с помощью функции ЕСЛИ (IF) .
  • Во-вторых, нужно будет добавить перед сделанными столбцами колонку Пустышки, где первое значение будет 0, а начиная со второй ячейки формулой будет вычисляться высота тех самых прозрачных подпирающих столбцов.

После этого останется выделить всю таблицу кроме исходного столбца Поток и создать обычную гистограмму с накоплением через Вставка — Гистограмма (Insert — Column Chart) :

Почти готовая диаграмма водопад

Если теперь выделить синие столбцы и сделать их невидимыми (по ним правой кнопкой мыши — Формат ряда — Заливка — Нет заливки), то мы как раз и получим то, что требуется.

В плюсах подобного способа — простота. В минусах — необходимость считать вспомогательные колонки.

Способ 3. Если уходим в минус — всё сложнее

К сожалению, предыдущий способ адекватно работает только для положительных значений. Если хотя бы на каком-то участке наш водопад уходит в отрицательную область, то сложность задачи возрастает в разы. В этом случае необходимо будет формулами просчитать каждый ряд (пустышки, зеленые и красные) отдельно для отрицательной и положительной частей:

waterfall4.png

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

Способ 4. Экзотический: полосы повышения-понижения

Этот способ основан на использовании специального малоизвестного элемента плоских диаграмм (гистограмм и графиков) — Полос повышения-понижения (Up-Down Bars) . Эти полосы попарно соединяют точки двух графиков, чтобы наглядно показать какая из двух точек выше-ниже, что активно используется при визуализации план-факта:

waterfall5.png

Легко сообразить, что если убрать линии графиков и оставить на диаграмме только полосы повышения-понижения, то мы получим все тот же «водопад».

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

waterfall6.png

Для создания «водопада» нужно выделить столбец с месяцами (для подписей по оси Х) и два дополнительных столбца График 1 и График 2 и построить для начала обычный график через Вставка — График (Insert — Line Сhart) :

waterfall7.png

Теперь добавим к нашей диаграмме полосы повышения-понижения:

  • В Excel 2013 и новее для этого необходимо выбрать на вкладке Конструктор команду Добавить элемент диаграммы— Полосы повышения-понижения (Design — Add Chart Element — Up-Down Bars)
  • В Excel 2007-2010 — перейти на вкладку Макет — Полосы повышения-понижения (Layout — Up-Down Bars)

Диаграмма после этого начнёт выглядеть примерно так:

waterfall8.png

Осталось выделить графики и сделать их прозрачными, щелкнув по ним по очереди правой кнопкой мыши и выбрав команду Формат ряда данных (Format series) . Аналогичным образом можно изменить и стандартные, весьма убого выглядящие, чёрно-белые цвета полос на зелёные и красные, чтобы получить в итоге более приятную картинку:

waterfall9.png

В последних версиях Microsoft Excel ширину полос можно изменить, щёлкнув по одному из прозрачных графиков (не по полосам!) правой кнопкой мыши и выбрав команду Формат ряда данных — Боковой зазор (Format series — Gap width) .

В старых версиях Excel для такого исправления приходилось использовать команду на Visual Basic:

  1. Выделите построенную диаграмму
  2. Нажмите сочетание клавиш Alt + F11 , чтобы попасть в редактор Visual Basic
  3. Нажмите сочтетание клавиш Ctrl + G , чтобы открыть панель прямого ввода команд и отладки Immediate (обычно она расположена внизу).
  4. Скопируйте и вставьте туда вот такую команду: ActiveChart.ChartGroups(1).GapWidth = 30 и нажмите Enter :

waterfall10.png

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

waterfall11.png

Ссылки по теме

  • Как в Excel построить диаграмму-шкалу (bullet chart) для визуализации KPI
  • Новые возможности диаграмм в Excel 2013
  • Как в Excel создать интерактивную «живую» диаграмму

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

Диаграмма динамики

Как в Excel создать диаграмму с динамикой темпов роста, где изменения данных показаны стрелками?

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

Подготовили для вас подробные пошаговые инструкции. Решили, что вам будет удобнее сразу на листе Excel повторять все шаги и строить диаграмму.
Скачивайте и применяйте!

Пошаговая инструкция

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

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

Если тема про диаграммы вам интересна, смотрите статьи:

  • Диаграммы Excel для презентаций
  • Необычные диаграммы с точками
  • Необычные диаграммы с областями
  • Необычные пузырьковые и точечные диаграммы
  • Диаграммы с группировкой
  • Динамика плана и факта
  • Нестандартные диаграммы Excel

Шаг 1. Добавить вычисления.

Добавим в таблицу строку «подписи» — с суммой немного больше исходного значения.

Вычисления для диаграммы

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

Вычисления для диаграммы

Шаг 2. Создать диаграмму.

Чтобы создать диаграмму, выделите таблицу и выберите в меню Вставка → Гистограмма → Гистограмма с накоплением.

Создать диаграмму

Шаг 3. Рост и подписи превратить в график

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

Настройки комбинированной диаграммы

Шаг 4. Добавить подписи для линии роста

Добавляем подписи для линии роста: выделите на диаграмме линию роста.

Метки данных

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

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

Настройка линий

Шаг 5. Добавить линии ряда данных

Чтобы добавить линии между столбцами, выделите столбцы гистограммы.

Перейдите в меню Конструктор → Добавить элемент диаграммы → Линии → Линии ряда данных.

* Если такая линия не появилась, проверьте, правильный ли у вас тип диаграммы — должна быть Гистограмма с накоплением.

Как создать диаграмму темпов роста в Microsoft Excel

Как создать диаграмму темпов роста в Microsoft Excel

Диаграмма с темпами роста показателей позволяет пользователю электронной таблицы Microsoft Excel отследить последние изменения и их качество. Визуальная демонстрация считается куда более наглядной, чем текстовая в виде функций, поэтому и пользуется такой популярностью. В этой статье я покажу два метода формирования соответствующей диаграммы на основе простой таблицы.

Создание таблицы с данными

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

Формирование таблицы для создания диаграммы темпов и динамики роста в Microsoft Excel

Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей

Вариант 1: Создание рекомендуемой диаграммы

Алгоритмы, присутствующие в Экселе, чаще всего корректно распознают диапазоны данных и отталкиваются от них при создании различных диаграмм. Я рекомендую воспользоваться как раз предложенным вариантом, если вы не хотите тратить время на ручную настройку графиков.

  1. Сначала выделите необходимую таблицу, затем перейдите на вкладку «Вставка». Переход на вкладку Вставка для создания диаграммы темпов и динамики роста в Microsoft Excel
  2. На ней нажмите кнопку «Рекомендуемые диаграммы». Открытие рекомендуемых графиков для создания диаграммы темпов и динамики роста в Microsoft Excel
  3. Если у вас примерно такая же таблица, появится первый рекомендуемый вариант с названием «Гистограмма с группировкой». Она вполне подойдет для того, чтобы сравнить показатели за несколько месяцев и узнать прирост или убыток в процентах. Выбор рекомендуемого варианта для создания диаграммы темпов и динамики роста в Microsoft Excel
  4. После двойного щелчка диаграмма автоматически добавляется на лист и доступна для дальнейшего взаимодействия. Как видно, практически все значения по умолчанию отображаются корректно, нужно только поменять название самой диаграммы. Добавление рекомендуемого варианта для создания диаграммы темпов и динамики роста в Microsoft Excel
  5. Однако не видно точного числа процентов, что не позволяет полностью оценить динамику. Для исправления ситуации нажмите по графику с процентами правой кнопкой мыши и из контекстного меню выберите пункт «Добавить подписи данных». Добавление подписей для рекомендуемого варианта для создания диаграммы темпов и динамики роста в Microsoft Excel
  6. Вы увидите, что каждая точка теперь будет отмечена процентами, даже если они имеют минусовое значение. Теперь в полной мере можно получить визуальное представление о том, насколько характерной является динамика роста за конкретный промежуток времени по разным показателям. Результат использования рекомендуемого варианта для создания диаграммы темпов и динамики роста в Microsoft Excel

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

Вариант 2: Ручное создание диаграммы

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

  1. Как обычно, сначала выделите таблицу для создания диаграммы из нее, затем перейдите на вкладку «Вставка» и нажмите кнопку «Рекомендуемые диаграммы». Открытие списка графиков для создания диаграммы темпов и динамики роста в Microsoft Excel
  2. В новом окне перейдите на вкладку «Все диаграммы» и выберите последний вариант – «Комбинированная». Выбор комбинированного графика для создания диаграммы темпов и динамики роста в Microsoft Excel
  3. Если у вас есть суммы чисел для сравнения, понадобится сделать эти ряды гистограммами с накоплением, чтобы обеспечить максимально правильное отображение значений. Выбор первых гистограмм для создания диаграммы темпов и динамики роста в Microsoft Excel
  4. Сделайте это для всех рядов, а затем посмотрите, как это будет представлено на самой диаграмме. Как видно, общая сумма и соотношение для каждого месяца предельно наглядны. Результат настройки первых гистограмм для создания диаграммы темпов и динамики роста в Microsoft Excel
  5. Для динамики, которая отражается в процентах, нужно выбрать линейный график. В идеале – «График с маркерами», чтобы возле каждого значения в процентах была точка для большей наглядности. Выбор графика процентов для создания диаграммы темпов и динамики роста в Microsoft Excel
  6. Если проценты не видны так, как это показано на скриншоте ниже, поставьте напротив имени ряда галочку «Вспомогательная ось», после чего подтвердите добавление диаграммы в таблицу. Создание вспомогательной оси для создания диаграммы темпов и динамики роста в Microsoft Excel
  7. Ознакомьтесь с ней и решите, подходит ли она для отображения темпов и динамики роста. Добавление комбинированного графика для создания диаграммы темпов и динамики роста в Microsoft Excel
  8. Для отображения процентов возле каждой точки динамики нужно добавить подписи данных, выбрав соответствующий вариант в контекстном меню. Добавление подписей процентов для создания диаграммы темпов и динамики роста в Microsoft Excel
  9. На этом формирование диаграммы завершено. Она должна иметь примерно такой вид, как на следующем изображении. Результат ручной настройки для создания диаграммы темпов и динамики роста в Microsoft Excel

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *