18 июн. 2014 г.

Как закрасить площадь между двумя линиями на графике в Excel?

Я уже рассматривал некоторые хитрости построения графиков в Excel - график с двумя осями Y и прочие довольно простые вещи. Можно поговорить о чуть более сложных вещах.
Возьмем стандартный график с линиями (line chart). Зачем нужно закрашивать площадь между этими линиями? Часто площадь закрашивают для того, чтобы акцентировать внимание читателя на различии/расстоянии между двумя линиями. Другой вариант, который встречается в моей деятельности - построение совмещенных графиков показателей, имеющих сильные сезонные колебания.
К примеру, вот такой вот график. На нем изображены недельные данные по объемам хранимого в подземных хранилищах  природного газа (ПХГ) в США. Показатель имеет сильную сезонность, поэтому в таком представлении сложно понять, что происходит на "конце" графика и соотнести последние точки с предыдущими периодами.

В совмещенном же виде график будет выглядеть вот таким вот образом. По оси Х отображен только один год - 52 недели для недельных данных. Данные по разным годам совмещены в линиях - в данном представлении показывается только текущий (2014), серой линией обозначен среднее значение за пятилетний период, светло-серым закрашенным участком выделен диапазон между минимальным и максимальным значением за соответствующий период.

Рассмотрим только построение закрашенной светло-серой площади, которая показывает диапазон. Сначала строим обычный линейный график для максимума и минимума - предполагается, что исходные данные (обычный временной ряд) уже преобразованы для такого представления и у нас есть ряд Max и Min.

Сначала нужно закрасить площадь между двумя линиями. "Трюк" состоит в том, чтобы добавить на графику гистограмму с накоплением, которая будет состоять из двух частей. Верхняя часть гистограммы будет соответствовать расстоянию между графиками и будет видимой. Нижняя часть будет занимать площадь от линии Min до 0 и будет невидимой на графике.
Для этого нужно в расчетной таблице добавить два дополнительных расчетных столбца к двум имеющимся (Max и Min).
Нижняя часть = Min
Разница = Max - Min.



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

Выглядит правда все еще не очень похоже. Теперь нужно изменить вид диаграммы на гистограмму с накоплением. Соответственно выбираем сначала ряд "Разница" -> "Изменить тип диаграммы для ряда" -> "С областями и накоплением" (если выбрать просто гистограмму с накоплением, то будут "зубцы"). Тоже самое делается для для ряда "Нижняя часть".
Обратите внимание, что два ряда "Min" и "Нижняя часть" имеют одинаковые значения и наложены друг на друга. Удостоверьтесь, что вы выбираете нужный ряд. После этих манипуляций должно стать понятнее, что происходит. Фактически на графике совмещены две диаграммы.


В дальнейшем остается только доработать все "напильником", чтобы получить желаемый результат.
- Установить "Нет заливки" для нижней части гистограммы с накоплением. Это приведет к тому, что нижняя часть гистограммы станет невидимой.
- Поменять цвет для верхней части области на нужный.
Можно убрать сами линии Max и Min, чтобы они не отвлекали внимание, добавить дополнительные ряды ("2014" и "Среднее за 2009-2013"), чтобы получился итоговый результат (второй график) - но это уже дело вкуса и итоговый целей представления данных.
Единственное, что плохо с моей точки зрения - площадь получается с "зубцами", я пока не нашел способа, как можно обеспечивать"сглаживание" для такого графика. Если знаете - расскажите!

Дополнительная тонкость - чтобы убрать из легенды ненужные элементы (Нижняя часть, Разница), можно просто выделять их и нажимать Del - только нужно убедиться, чтобы вы выделили не всю легенду, а отдельный ее элемент (тогда отдельный элементы будет в выделенном треугольнике.
Вот ссылка на файл с данными и итоговым графиком. 

1 комментарий:

Анонимный комментирует...

Работает, спасибо огромное!