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

1. Подготовка таблицы с исходными данными:

2. Построение комбинированной диаграммы:
ряд Показатель – график с маркерами и по основной оси, а ряд Кв – гистограмма с группировкой по вспомогательной оси:

3. Настройка диаграммы:

а) Параметры для вспомогательной оси:
– максимальное значение задать равное 1.
– положение подписи – нет.
б) Боковой зазор в формате ряда данных для ряда Показатель задать минимальным – 0.
в) Отформатировать точки ряда Кв разными цветами в соответствии с кварталами.
г) Выделить ряд Показатель и установить Линии проекции (вкладка Конструктор, Добавить элемент диаграммы, Линии, Линии проекции)

В 2016 версии построение каскадных диаграмм происходит в 2 щелчка мыши. Что делать, если 2016/2019 Excel ещё предел мечтаний, а строить подобную диаграмму надо.

В целом, каскадная диаграмма – это способ графически продемонстрировать количественные потоки, как из числа А получается число В.

1. ПОДГОТОВКА ИСХОДНЫХ ДАННЫХ – с учетом того, что данные могут попадать в отрицательную область значений, то необходимо учесть все возможные ситуации. В столбец Значение вводятся исходные данные, затем по формулам определяются значения различных областей диаграммы:

2. ПОСТРОЕНИЕ ДИАГРАММЫ:
Выделить исходные данные – все столбцы, кроме столбца Значение, т.е. диапазон B3:B15;D3:I15. На вкладке Вставка [Insert] в группе Диаграммы [Charts] выбрать Вставить гистограмму [Insert Column Chart] и тип Гистограмма с накоплением [Stacked Column].

3. НАСТРОЙКА ВНЕШНЕГО ВИДА ДИАГРАММЫ:
– выделить ряд Невидимка и задать цвет заливки Нет заливки [No Fill]
– ряды Плюс>0 и Плюс<0 оформить одним цветом, например, зеленым
– ряды Минус>0 и Минус<0 оформить одним цветом, например, красным
– удалить легенду: выделить легенду и нажать Delete.

 

Построение диаграммы происходит с использованием вспомогательных строк/столбцов или даже таблицы. В данном примере построение происходит по строкам 10-14 – это вспомогательная таблица, которая формирует данные для диаграммы, исходя из выбранных флажков справа от нее. В данном случае цель – включать или выключать ряды, а возможно, когда устанавливается флажок, исходные данные особым образом рассчитываются.

Затем, чтобы ничего лишнего не было показано на листе, строки или столбцы можно скрыть. Однако, скрывая исходные данные с листа (например, строки 8-14), на диаграмме мгновенно исчезают ряды данных.

Что же делать? На самом деле, надо лишь вспомнить об одной команде в настройках исходных данных диаграммы.

Итак, необходимо выделить диаграмму и на вкладке Конструктор [Design] в группе Данные [Data] выбрать команду Выбрать данные [Select Data]. В окне нажать кнопку Скрытые и пустые строки [Hidden and Empty Cells] – поставить флажок Показывать данные в скрытых строках и столбцах [Show data in hidden rows and colums]:

И данные тут же возвращаются на диаграмму.

Всё можно сделать рано или поздно. Но лучше рано!