Как построить каскадную диаграмму в Excel
Обновлено: 21.11.2024
Создайте каскадную диаграмму Excel, чтобы показать, как положительные и отрицательные суммы повлияли на общую сумму на основе начального значения. Получите бесплатную рабочую тетрадь, посмотрите видео и ознакомьтесь с пошаговыми письменными инструкциями
Каскадная диаграмма Excel
Вы можете использовать каскадную диаграмму Excel, чтобы показать кумулятивный эффект положительных и отрицательных сумм на основе начального значения. Например, покажите ежемесячные суммы чистого денежного потока на каскадной диаграмме и быстро узнайте, какие месяцы имели положительные и отрицательные результаты.
В Excel 2013 и более ранних версиях нет каскадной диаграммы, но вы можете создать ее, следуя приведенным ниже инструкциям.
Видео: создание каскадной диаграммы
Посмотрите это короткое видео, чтобы узнать, как создать каскадную диаграмму на основе ваших данных. Письменные инструкции находятся под видео.
Настройте данные
Чтобы создать собственную каскадную диаграмму, первым делом необходимо настроить данные.
В этом примере:
- Начальная сумма вводится в ячейку B3.
- Названия месяцев перечислены в ячейках A3:A14.
- Суммы ежемесячных денежных потоков вводятся в ячейки B3:B14.
К данным будут добавлены дополнительные столбцы и строки, как описано ниже.
Добавить столбцы вычислений
Далее выполните следующие действия, чтобы добавить столбцы расчета:
- Вставьте 5 столбцов между списком названий месяцев и списком денежных потоков.
- Добавьте эти заголовки к 5 новым столбцам: "Основной", "Конечный", "Вниз", "Вверх" и "Начало".
Добавить строки в данные
Далее выполните следующие действия, чтобы добавить 3 строки в диапазон данных:
- Вставьте строку над строкой Start. Это создаст интервал слева от диаграммы
- (необязательно) Введите любое значение в ячейку B2. Это просто напоминание о том, что строку нельзя удалять.
Вот измененный макет данных с добавленными строками
Введите формулы каскадной диаграммы
Следующий шаг — ввести следующие формулы, которые будут использоваться в каскадной диаграмме.
Начать
Формула Start возвращает начальное значение из столбца чистого денежного потока.
База – это рассчитанная сумма для серии, которая будет скрыта в заполненной диаграмме.
Базовая сумма создает начальную точку для серий вверх и вниз на диаграмме
- Поместите эту формулу в ячейку B4: =СУММ(B3,E3:F3)-D4.
- Скопируйте формулу до B16.
ПРИМЕЧАНИЕ. Пока все базовые результаты будут одинаковыми, пока не будут добавлены другие формулы столбцов.
Далее вы создадите формулу Down. Внизу отображается список отрицательных чисел в столбце чистого денежного потока.
- Поместите эту формулу в ячейку D4: = - MIN(G4,0)
- Скопируйте формулу до D15.
Далее вы создадите формулу Up. Up – это список положительных чисел из столбца чистого денежного потока.
- Поместите эту формулу в ячейку E4: =MAX(G4,0)
- Скопируйте формулу до E15.
Конец — это последний столбец диаграммы. Его формула уже есть на листе, и ее нужно просто переместить в другое место.
- Выберите ячейку B16.
- Перетащите ячейку с формулой "Конец" вправо, поместив ее в ячейку C16.
Создайте каскадную диаграмму
Чтобы создать каскадную диаграмму:
- Выберите ячейки A1:F17 — ячейки заголовков, ячейки данных и ячейки строк-разделителей.
- НЕ включайте столбец с цифрами чистого денежного потока.
- На ленте Excel перейдите на вкладку "Вставка".
- Нажмите "Столбчатая диаграмма", затем нажмите "Сгруппированные столбцы".
Чтобы отформатировать серию Base, выполните следующие действия
- Нажмите правой кнопкой мыши на базовую серию и выберите "Форматировать серию данных".
- На панели "Формат ряда данных" нажмите вкладку "Заливка и линия" (значок банки с краской).
- Отформатируйте его без заливки и границ, чтобы он не отображался на диаграмме.
Чтобы отформатировать серию Down, выполните следующие действия
- Нажмите правой кнопкой мыши на ряд "Вниз" и выберите "Форматировать ряд данных".
- Перейдите на вкладку "Заливка и линия" (значок банки с краской).
- Отформатируйте его, используя сплошную заливку, и выберите красный цвет заливки.
Чтобы отформатировать серию Up, выполните следующие действия
- Нажмите правой кнопкой мыши на серию "Вверх" и выберите "Форматировать серию данных".
- Перейдите на вкладку "Заливка и линия" (значок банки с краской).
- Отформатируйте его, используя сплошную заливку, и выберите зеленый цвет заливки.
Чтобы завершить форматирование диаграммы, выполните следующие действия
- Отформатируйте начальный и конечный столбцы, заполнив их серым цветом.
- Нажмите на любой столбец, чтобы выбрать его
- На панели "Формат ряда данных" нажмите вкладку "Параметры ряда" (столбчатая диаграмма).
- В разделе "Параметры серии" уменьшите ширину разрыва до небольшого значения, примерно 10 %.
- Закройте панель "Формат ряда данных".
- Чтобы удалить легенду, нажмите на нее, а затем нажмите клавишу DELETE.
- Измените текст в заголовке диаграммы по умолчанию на "Денежный поток"
Диаграмма водопада карманных цен
Разновидностью каскадной диаграммы является диаграмма Pocket Price Waterfall, которую вы можете получить в разделе файлов с образцами ниже.
Вместо двух выделенных столбцов (начало и конец), как на обычной каскадной диаграмме, диаграмма Pocket Price Waterfall имеет несколько выделенных столбцов.
Чтобы создать эти выделенные столбцы, я создал новую формулу в столбце "Конец" (ячейки C4:C14) для проверки столбца "Чистый денежный поток" (G). Если эта ячейка пуста, покажите сумму предыдущих сумм.
=ЕСЛИ(ДЛСТР(G4)=0,СУММ(B3,E3:F3)-D4,"")
Столбец Base является обратным и показывает сумму, если ячейка в столбце G НЕ пуста.
=ЕСЛИ(ДЛСТР(G4)=0,"",СУММ(B3:C3,E3:F3)-D4)
Вот пересмотренная каскадная диаграмма с выделенным столбцом для каждого квартального итога, заканчивающегося в сентябре.
В примере рабочей тетради я использовал те же формулы с некоторыми поддельными данными Pocket Price, чтобы создать каскадную диаграмму Pocket Price. Зеленых столбцов нет, так как все значения являются отрицательными, что снижает цену.
Получить образцы файлов
Каскадная диаграмма. Чтобы следовать видео и письменным инструкциям, скачайте образец файла каскадной диаграммы. Файл заархивирован и имеет формат xlsx. Файл не содержит макросов.
Диаграмма водопада карманных цен. Чтобы просмотреть диаграмму водопада карманных цен и данные, скачайте образец файла диаграммы водопада карманных цен. Заархивированный файл имеет формат xlsx и не содержит макросов.
Excel для Microsoft 365 Word для Microsoft 365 Outlook для Microsoft 365 PowerPoint для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Word 2021 Outlook 2021 PowerPoint 2021 Excel 2019 Word 2019 Outlook 2019 PowerPoint 2019 Excel 2016 Word 2016 Outlook 2016 PowerPoint 2016 Excel для iPad Excel для iPhone Еще. Меньше
Каскадная диаграмма показывает промежуточный итог по мере добавления или вычитания значений. Это полезно для понимания того, как на начальное значение (например, чистый доход) влияет ряд положительных и отрицательных значений.
Столбцы имеют цветовую кодировку, поэтому вы можете быстро отличить положительные числа от отрицательных. Столбцы начального и конечного значений часто начинаются на горизонтальной оси, тогда как промежуточные значения представляют собой плавающие столбцы. Из-за такого «внешнего вида» каскадные диаграммы также называют мостовыми диаграммами.
Создайте каскадную диаграмму
Выберите свои данные.
Нажмите «Вставка» > «Вставить водопад» или «Акции» > «Водопад».
Вы также можете использовать вкладку "Все диаграммы" в разделе "Рекомендуемые диаграммы", чтобы создать каскадную диаграмму.
Совет. Используйте вкладки "Дизайн" и "Формат", чтобы настроить внешний вид диаграммы. Если вы не видите эти вкладки, щелкните в любом месте каскадной диаграммы, чтобы добавить на ленту инструменты для работы с диаграммами.
Начинать промежуточные итоги или итоги с горизонтальной оси
Если ваши данные включают значения, которые считаются промежуточными или итоговыми, например чистый доход, вы можете настроить эти значения так, чтобы они начинались на горизонтальной оси с нуля и не "плавали".
Дважды щелкните точку данных, чтобы открыть область задач «Формат точки данных», и установите флажок «Задать как общее».
Примечание. Если вы щелкнете столбец один раз, вы выберете ряд данных, а не точку данных.
Чтобы столбец снова стал "плавающим", снимите флажок "Задать как итог".
Совет. Вы также можете установить итоги, щелкнув правой кнопкой мыши точку данных и выбрав "Установить как итог" в контекстном меню.
Показать или скрыть соединительные линии
Соединительные линии соединяют конец каждого столбца с началом следующего столбца, помогая показать поток данных на диаграмме.
Чтобы скрыть соединительные линии, щелкните правой кнопкой мыши ряд данных, чтобы открыть панель задач "Формат ряда данных", и снимите флажок "Показать соединительные линии".
Чтобы снова отобразить линии, установите флажок Показать соединительные линии.
Совет. В условных обозначениях диаграммы сгруппированы различные типы точек данных на диаграмме: Увеличение, Уменьшение и Итого. Если щелкнуть запись легенды, будут выделены все столбцы, составляющие эту группу на диаграмме.
Вот как создать каскадную диаграмму в Excel для Mac:
Выберите свои данные.
На вкладке "Вставка" на ленте нажмите (значок "Каскад") и выберите "Каскад".
Примечание. Используйте вкладки "Дизайн диаграммы" и "Формат" для настройки внешнего вида диаграммы. Если вы не видите эти вкладки, щелкните в любом месте каскадной диаграммы, чтобы отобразить их на ленте.
В июле 2015 года Microsoft объявила, что в готовящемся к выходу Office 2016 в линейку диаграмм будет добавлено 6 новых диаграмм. Больше всего в финансовом сообществе ждали встроенную каскадную диаграмму Excel.
Хотите шаблоны каскадных диаграмм Excel?
Если вы хотите сразу перейти в Excel и начать создавать более качественные каскадные диаграммы, нажмите кнопку ниже, чтобы получить наши шаблоны Excel.
Каскадные диаграммы 101
Каскадная диаграмма (также известная как каскадная диаграмма или промежуточная диаграмма) – это особый тип диаграммы, который показывает, как положительные или отрицательные значения ряда данных влияют на итоговое значение. Другими словами, это идеальный способ визуализировать начальное значение, положительные и отрицательные изменения, внесенные в это значение, и результирующее конечное значение. В каскадной диаграмме первый столбец является начальным значением, а последний столбец — конечным значением. Плавающие столбцы между ними — это положительные или отрицательные значения.
Примечание. Другие забавные названия каскадных диаграмм включают диаграмму Марио и диаграмму летающих кирпичей, потому что отдельные элементы диаграммы напоминают старую аркадную игру.
Некоторым людям нравится соединять линии между вкладами, чтобы диаграмма выглядела как мост (давая нам название мостовой диаграммы), в то время как другие оставляют столбцы плавающими.
Использование каскадных диаграмм
Каскадные диаграммы популярны в корпоративной и финансовой среде, потому что они очень полезны для визуализации положительных и отрицательных изменений измеряемой величины или ключевого показателя эффективности, например чистой ежемесячной прибыли или денежного потока.
Другие примеры количественного анализа, в которых используются каскадные диаграммы, включают:
- Визуализация отчетов о прибылях и убытках
- Сравнение доходов от продуктов
- Выделение изменений бюджета проекта
- Анализ запасов или продаж за определенный период времени.
- Отображение ценности продукта за определенный период времени.
- Создание исполнительных панелей управления
Короче говоря, используйте каскадную диаграмму всякий раз, когда вы хотите показать, как начальное значение увеличивается или уменьшается в результате серии положительных или отрицательных изменений.
Совет. Наиболее типичная каскадная диаграмма — это диаграмма с начальным и конечным значениями, но вы также можете создавать промежуточные итоги в качестве визуальных вех в ряду. Они отображаются как полные столбцы. Например, вы можете использовать чистый доход и валовой доход в качестве двух контрольных точек между начальным и конечным значениями валового дохода и чистого дохода.
Как создать каскадную диаграмму в Excel
До выхода Office 2016 создание каскадных диаграмм в Excel было крайне сложным процессом.
Обратите внимание, что я использовал слово "создание", а не "вставка". Правильно: вы не вставили каскадную диаграмму, вы ее создали.
. с помощью руководств
Чтобы создать каскадную диаграмму в Excel 2013 и более ранних версиях, вам нужно было определить дополнительные ряды данных (со сложными формулами) в таблице данных, а затем сделать их невидимыми на диаграмме.
И мы не говорим об 1 невидимой серии. Если в какой-то момент каскадная диаграмма опустилась ниже нуля, вам потребовалось как минимум семь дополнительных серий!
Вот лишь некоторые из множества руководств по созданию каскадной диаграммы в Excel до 2016 года:
. с помощью шаблонов
Чтобы избежать необходимости каждый раз повторять этот долгий процесс, люди часто прибегали к использованию шаблона каскадной диаграммы:
Конечно, использование шаблонов не идеально. Если ваши данные имеют другое количество категорий, вам придется изменить шаблон, что опять же потребует дополнительной работы.
В идеале каскадную диаграмму следует создавать так же, как и любую другую диаграмму Excel: (1) щелкните внутри таблицы данных, (2) щелкните на ленте диаграмму, которую хотите вставить.
. в Excel 2016
Microsoft решила прислушаться к отзывам пользователей и представила в Excel 2016 6 наиболее востребованных диаграмм, в том числе встроенную каскадную диаграмму Excel.
Больше никаких шаблонов, дополнительных рядов, формул или возни с диаграммами. 2 клика, и ваша потрясающая каскадная диаграмма вставлена.
Хотя добавление каскадных диаграмм в Excel 2016 — это большой шаг вперед, текущая функциональность по-прежнему оставляет желать лучшего.
10 шагов к идеальной водопадной диаграмме Excel
Вот несколько способов, которые помогут вам создавать более качественные каскадные диаграммы Excel, а также кое-что, чего еще не хватает.
1. Не забудьте установить итоги
Допустим, мы хотим визуализировать эту таблицу данных с помощью каскадной диаграммы: EBITDA нашей вымышленной компании за 2015 и 2016 годы и индивидуальный вклад 7 единиц малого бизнеса в изменение с 2015 по 2016 год.
Это не должно быть слишком сложно. Щелкните внутри таблицы данных, перейдите на вкладку "Вставка" и нажмите "Вставить каскадную диаграмму", а затем щелкните диаграмму. Вуаля:
Хорошо, технически это это каскадная диаграмма, но это не совсем то, на что мы рассчитывали. В легенде мы видим, что Excel 2016 имеет 3 типа столбцов в каскадной диаграмме:
Это правильно, но на диаграмме нет столбцов «Итого», а только «Увеличение» и «Уменьшение». Первый и последний столбцы должны быть Total (начиная с горизонтальной оси), и чтобы установить их как таковые, мы должны дважды щелкнуть каждый из них, чтобы открыть панель задач Format Data Point, и установить флажок Set as total.< /p>
Вы также можете щелкнуть точку данных правой кнопкой мыши и выбрать "Установить как итог" в списке параметров меню.
Наконец у нас есть каскадная диаграмма:
2. Избавьтесь от беспорядка в визуализации
Рекомендация по визуализации данных – удалить из визуализации ВСЕ элементы, которые не являются абсолютно необходимыми (если вам интересно, вы можете узнать об этом подробнее на нашем веб-семинаре: Углубленная визуализация данных).
Подобно другим диаграммам Excel, водопадная диаграмма Excel по умолчанию также имеет слишком много беспорядка. Легенда, вертикальная ось и метки, горизонтальные линии сетки — ничто из этого не способствует лучшему пониманию данных читателем. Во всяком случае, они отвлекают внимание.
Итак, давайте удалим все ненужные элементы и запишем ключевое сообщение в заголовок. Жаль, что название диаграммы нельзя вставить автоматически из ячейки.
Совет. Чтобы удалить отвлекающие элементы диаграммы, щелкните каждый из них правой кнопкой мыши и выберите "Удалить".
Отлично, стало намного лучше. Но это потребовало дополнительной работы, которая не потребовалась бы, если бы значения по умолчанию для Excel были лучше. В Zebra BI для Excel мы покажем вам, как сделать это лучше.
3. Разорвите ось, чтобы выделить вклад
Это ограничение особенно заметно в каскадных диаграммах, поскольку каскадные диаграммы содержат два разных типа данных:
- Итоги: обычно первый и последний столбец в серии.
- Вклады: плавающие кирпичи, образующие «мост» между двумя суммами.
Распространенная проблема заключается в том, что взносы часто очень малы по сравнению с итоговыми суммами. Это также видно в нашем примере (см. изображение выше).
Во-первых, по порядку: эта диаграмма правильно визуализирует ситуацию, поскольку взносы действительно ОЧЕНЬ малы по сравнению с общими суммами. Наш результат за 2016 год практически не отличается от результата за 2015 год.
Эта визуализация также полностью соответствует стандартам IBCS.
Однако пользователей (и их начальников) иногда больше интересуют вклады, чем итоги и взаимосвязь между ними.
В этом случае единственным приемлемым вариантом будет разбить вертикальную ось и начать общее значение с некоторого значения, большего 0. Допустим, 35 000. Это подчеркивает индивидуальный вклад, но рискует привести неосведомленных читателей к ложным выводам о данных.
Вы снова можете прибегнуть к помощи руководств и шаблонов:
Другой, несколько более простой вариант — сделать следующее:
- Нажмите на диаграмму, чтобы выбрать ее.
- Повторно добавьте вертикальную ось. Выберите «Дизайн» >> «Добавить элемент диаграммы» >> «Оси» >> «Основная вертикаль».
- "Разбить" вертикальную ось: щелкните правой кнопкой мыши вертикальную ось и выберите "Формат оси", затем в разделе "Параметры оси" напишите "35000" в разделе "Границы" >> "Минимум".
- Удалить вертикальную ось: щелкните правой кнопкой мыши вертикальную ось и выберите "Удалить".
В итоге у нас получилась вот такая диаграмма:
Теперь вклад гораздо более заметен, но нет очевидных указаний на то, что вертикальная ось не начинается с нуля, что очень плохо, потому что пользователь не делает правильный вывод из визуализации.
4. Добавить относительные вклады в процентах
При анализе взносов вас часто больше интересуют относительные вклады (в процентах от общего числа), чем абсолютные вклады.
К сожалению, если вы хотите сделать это в водопадной диаграмме Excel по умолчанию, вам не повезло — вы застряли с отображением только абсолютных вкладов.
Дочитайте эту статью до конца, чтобы узнать, как легко это сделать в Zebra BI.
5. Выделите разницу между итоговыми значениями
Еще одна вещь, которую вы не можете сделать в каскадной диаграмме Excel, — это отобразить общую разницу между 2015 и 2016 годами в нашем примере.
Конечно, на диаграмме видно, что столбец 2016 года выше столбца 2015 года (особенно теперь, когда мы обрезали вертикальную ось). Но насколько? Если вы не умеете делать сложные вычитания в уме, вы не знаете точного числа. Также нет возможности отобразить относительную разницу в процентах.
Поскольку эта разница между итоговыми значениями довольно важна, это определенно важная функция, отсутствующая в каскадных диаграммах Excel.
Нажмите здесь, чтобы узнать, как это делает Zebra BI.
6. Используйте вертикальные каскадные диаграммы
Из статьи «Как правильно выбрать бизнес-диаграмму» мы знаем, что горизонтальные диаграммы (т. е. диаграммы с горизонтальной осью категорий) используются для отображения данных, связанных со временем. Для всего остального лучше использовать вертикальные диаграммы.
Каскадные диаграммы не являются исключением. Как ни странно, в Excel 2016 нет возможности вставить вертикальную каскадную диаграмму. Хотя эта функция была запрошена, нет указаний, будет ли она реализована и когда.
Поэтому, если вы хотите визуализировать отчет о прибылях и убытках с помощью вертикальной водопадной диаграммы Excel, вам снова придется прибегнуть к использованию шаблонов и руководств, подобных этому…
Мы подготовили демонстрацию в Zebra BI, чтобы вы могли увидеть, как создать отчет о прибылях и убытках с вертикальными каскадными диаграммами.
7. Добавить (некоторые) промежуточные итоги
Поскольку мы говорим о визуализации отчетов о прибылях и убытках, в типичном отчете о прибылях и убытках есть некоторые категории, которые на самом деле являются суммой нескольких других категорий.
Например: вы можете рассчитать сумму всех операционных расходов (OpEx). Это лучше визуализирует взаимосвязь между «Доходом» и «Прибылью до вычета процентов и налогов» (EBIT). EBIT = доход - операционные расходы.
В таблице это легко сделать — просто напишите формулу, и все готово.
Когда вы создаете каскадную диаграмму в Excel? Не так много. Очевидно, это настолько сложно сделать вручную, что в Интернете нет ни одного учебника или шаблона.
Однако вы можете ввести промежуточные итоги и обозначить их как таковые на каскадной диаграмме. Однако вам нужно рассчитать их самостоятельно, чтобы убедиться, что они верны.
Вы можете увидеть, как Zebra BI автоматически создает промежуточные итоги, на этом удобном анимированном gif-файле в конце этой статьи.
8. Настройте свою диаграмму с помощью цветов
Цветовая схема по умолчанию в Excel могла бы быть лучше. Перейдите на вкладку "Дизайн диаграммы" и откройте галерею "Изменить цвета".
Здесь вы можете выбрать цветовую палитру. Вы также можете выбрать другую тему на вкладке «Разметка страницы». Чтобы настроить использование цветов, нажмите кнопку «Цвета» и выберите «Настроить цвета» в нижней части списка.
Вы можете настроить его так, чтобы положительные значения отображались зеленым цветом, а отрицательные — красным, что является распространенным подходом в финансовой отчетности.
9. Включение и отключение соединительных линий
Соединительные линии соединяют столбцы, показывая изменение значений на диаграмме. Их можно включить или отключить, щелкнув правой кнопкой мыши ряд данных, чтобы открыть панель "Формат ряда данных", и установив или сняв флажок "Показать соединительные линии".
10. Масштабируйте диаграммы
Наконец мы подошли к одной важной функции, отсутствовавшей в Excel с самого начала: масштабированию нескольких диаграмм.
Хотя эта проблема не ограничивается каскадными диаграммами, она слишком важна, чтобы не упомянуть ее здесь.
Убедиться, что все связанные диаграммы в отчете или сводной панели имеют одинаковый масштаб, – одна из самых важных концепций визуализации данных.
Если вы не синхронизируете весы, даже не вставляйте диаграммы
Слишком часто вы видите рядом две диаграммы Excel с совершенно разными масштабами. Хотя каждый из них сам по себе является адекватной визуализацией данных, вы должны убедиться, что они масштабируются, когда вы размещаете их рядом! В противном случае даже не вставляйте диаграммы, а просто оставьте данные в таблице.
Итак, как синхронизировать масштабы диаграмм Excel? Хотя процедура не особенно сложна, она занимает много времени. Это аналогичная процедура, которую мы использовали, чтобы сломать ось.
Допустим, у нас есть две водопадные диаграммы Excel по умолчанию, и нам нужно их масштабировать:
Первый шаг — повторно добавить вертикальную ось на обе диаграммы.
- Нажмите на первую диаграмму, чтобы выбрать ее.
- Повторно добавьте вертикальную ось. Выберите «Дизайн» >> «Добавить элемент диаграммы» >> «Оси» >> «Основная вертикаль».
- Повторите это для второй диаграммы.
Вот что у нас есть на данный момент:
Теперь нам нужно настроить масштаб правого графика так, чтобы он совпадал с масштабом левого. Щелкните правой кнопкой мыши вертикальную ось и выберите «Формат оси». Затем в разделе «Параметры оси» напишите «600» в разделе «Границы» >> «Минимум».
Удалите вертикальную ось с обеих диаграмм (щелкните правой кнопкой мыши по вертикальной оси и нажмите "Удалить"), и мы получим правильную визуализацию:
Хорошо, это было не так уж плохо. А что, если у вас есть ежемесячный отчет с 6 каскадными диаграммами? Будете ли вы делать эту процедуру для 6 графиков каждый месяц, когда данные меняются? Думаю, нет.
Конечно, это можно автоматизировать, но для этого нужно использовать VBA. Если вы не хотите использовать VBA, возможно, вам поможет эта статья Джона Пельтье от 2012 года.
Должен быть лучший способ!
Если у вас нет времени на настройку каскадных диаграмм Excel по умолчанию и вы хотите добавить эти расширенные функции в каскадные диаграммы несколькими щелчками мыши, взгляните на анимацию ниже:
Я уверен, вы согласитесь, что по сравнению с другими методами с последующими руководствами и использованием шаблонов, связанных в этой статье, это выглядит так просто, что даже страшно! Кстати, это можно сделать и в Excel 2013 и Excel 2010.
Добавление относительных вкладов и выделение различий
Теперь давайте попробуем сделать то, что вы не можете сделать с каскадными диаграммами Excel по умолчанию — относительные вклады в % и выделение различий:
Вы можете легко переключаться между отклонениями, отображаемыми в относительных и абсолютных значениях. Вы также можете контролировать, является ли увеличение положительным или отрицательным событием. Мы знаем, что в некоторых случаях, например, когда речь идет о расходах, увеличение является негативным явлением.
Еще одна полезная функция, которую мы добавили, – выделение различий между начальными и конечными значениями. Вы можете переключаться между относительной и абсолютной дисперсией или отображать их оба для еще большей плотности информации.
В этом примере видно, что разница между EBITDA в 2015 и 2016 годах составила 610 000, или 1,5 %.
Создание отчета о прибылях и убытках с помощью вертикальных каскадных диаграмм
Как насчет вставки двух масштабированных вертикальных каскадных диаграмм в отчет о прибылях и убытках? 2 клика и все готово. 🙂 Обратите внимание на визуализацию промежуточных итогов по OpEx!
Преимущество этого промежуточного итога в том, что это расчет, показывающий общую сумму всех затрат, что делает диаграмму более удобной для чтения.
Малые кратные с каскадными диаграммами
Лучшее мы оставили напоследок: вот 8 каскадных диаграмм за 2 клика:
Вам нужно всего лишь выбрать одну ячейку в ваших данных и выбрать тип горизонтальной каскадной диаграммы с помощью кнопки "Вклад". Затем Zebra BI создает несколько диаграмм и дает вам возможность управлять макетом и всем остальным.
Получить шаблоны каскадных диаграмм Excel
Если вы хотите создать любую из этих каскадных диаграмм Excel, введите свои данные ниже, чтобы получить немедленный доступ ко всем примерам каскадных диаграмм, использованным в этом руководстве.
Каскадная диаграмма – это инструмент визуализации данных, который показывает, как положительные или отрицательные значения в ряде данных изменяются последовательно. Распространенным сценарием является отображение кумулятивного эффекта доходов и расходов на чистую прибыль. Значения могут быть распределены либо по времени, либо по категориям. В этом руководстве мы покажем вам, как создать каскадную диаграмму в Excel.
Основы каскадной диаграммы
Каскадная диаграмма в основном состоит из 5 разделов:
- Область сюжета: здесь происходит графическое представление. Водопадная диаграмма визуализирует значения в виде столбцов. Каждый столбец начинается там, где заканчивается предыдущий столбец.
- Заголовок диаграммы. Название диаграммы.
- Вертикальная ось: ось, представляющая измеренные значения, также известная как ось Y.
- Горизонтальная ось: ось, содержащая категории данных, также известная как ось X.
- Легенда. Легенда – это индикатор, который помогает отличать ряды данных друг от друга.
Вставьте каскадную диаграмму в Excel
Начните с выбора данных. Включите метку данных в свой выбор, чтобы она автоматически распознавалась Excel.
Активируйте вкладку «Вставка» на ленте и щелкните значок каскадной диаграммы, чтобы просмотреть типы диаграмм в категории. Нажмите на каскадную диаграмму, чтобы создать диаграмму.
При нажатии на значок вставляется версия диаграммы по умолчанию. Теперь давайте рассмотрим некоторые дополнительные параметры настройки.
Настройка каскадной диаграммы в Excel
Способы настройки каскадной диаграммы
Большинство элементов диаграммы можно редактировать. Давайте рассмотрим некоторые из этих вариантов.
Двойной щелчок
Двойной щелчок по любому элементу открывает боковую панель Excel, которая предоставляет параметры для выбранного элемента. Имейте в виду, что после открытия боковой панели вам не нужно повторно дважды щелкать мышью — выбор следующего элемента в области диаграммы переключит меню параметров на этот элемент.
Боковая панель содержит параметры для отдельных элементов, а также некоторые общие параметры, такие как раскраска и эффекты.
Контекстное меню правой кнопки мыши
При щелчке элемента правой кнопкой мыши отображается контекстное меню с дополнительными параметрами. Вы можете изменять основные свойства стиля, например изменять цвета, удалять элементы или активировать боковую панель для доступа к дополнительным параметрам. Чтобы отобразить боковую панель, выберите параметры, начинающиеся со строки Format. Например; Отформатируйте область графика… на следующем изображении.
Сочетания клавиш для диаграмм (кнопки плюс и стили)
В Excel 2013 и более поздних версиях на диаграммах отображаются кнопки быстрого доступа. Вы можете добавлять/удалять элементы, применять предопределенные стили и наборы цветов, а также фильтровать значения несколькими щелчками мыши.
Еще одна удобная функция заключается в том, что вы можете видеть эффекты вашего выбора на лету, не применяя их на самом деле. Например; на следующем изображении мышь находится на элементе Axis Titles, и мы видим метки на нашей диаграмме.
Лента (инструменты для работы с диаграммами)
Каждый раз, когда вы активируете специальный объект, Excel добавляет новую вкладку или вкладки на ленту. Вы можете увидеть эти вкладки для конкретных диаграмм в разделе «Дизайн» и «Формат». В то время как вкладка «Дизайн» содержит параметры для добавления элементов, применения стилей, изменения данных и изменения самой диаграммы. На вкладке «Формат» представлены более общие параметры, общие с другими объектами.
Кратко; вкладки диаграмм на ленте — это единственное место, где можно найти все параметры, упомянутые ранее.
Советы по настройке каскадной диаграммы
Предустановленные макеты и стили
Вы можете найти параметры стиля на вкладке "Дизайн" или на значке кисть ярлыков диаграмм. Вот несколько примеров:
Применение быстрого макета:
Изменение цветов:
Обновить стиль диаграммы:
Переключить строку/столбец
Excel по умолчанию предполагает, что вертикальные метки — это категории, а горизонтальные метки — ряды данных. Если ваши данные разработаны в обратном порядке, вы обычно можете легко изменить метки одним щелчком мыши. Однако это не относится к диаграммам водопада. Кнопка "Переключить строку/столбец" на вкладке "Дизайн диаграммы" отключена.
Переместить диаграмму на другой лист
Диаграммы создаются на том же рабочем листе, что и выбранные данные. Если вам нужно переместить диаграмму на новый лист или другой лист, используйте диалоговое окно «Переместить диаграмму». Чтобы открыть диалоговое окно «Переместить диаграмму», щелкните его значок на вкладке «Дизайн» или в меню щелчка правой кнопкой мыши для самой диаграммы. Имейте в виду, что вам нужно щелкнуть правой кнопкой мыши в пустом месте в области диаграммы, чтобы увидеть эту опцию.
Читайте также: