Диаграммы — прекрасное средство для визуализации и анализа данных. В программе 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]: