Построить красивый график с двумя уровнями дат сложно без сводной таблицы, поэтому идем следующим путем:
Сначала создают сводную таблицу с данными о оде, месяце и количестве. Затем добавляют в исходную таблицу столбец «Цвет» и задают условие: если год больше или равен текущему году минус три года - область диаграммы получает определённый цвет. Для этого используют несколько формул.
Алгоритм решения
- Добавляю в исходную таблицу столбец «Цвет» и задают условие: если год больше или равен текущему году от которого отнимаю три года, тогда взять данные по Количеству.
- Создаю сводную таблицу с данными о Годе Месяце по строкам и Количестве и Цвету в столбцах.
- Создаю диаграмму из сводной таблицы.
- Через Конструктор диаграммы задаю тип отображения Количества - График, а Цвета - График с областями.
Формула для закрашивания диаграммы цветом:
Чтобы подсветить значения за последние три года, которые выше среднего, нужно:
- Добавить дополнительное условие - не только учитывать диапазон лет, но и сравнивать количество со средним значением.
- Использовать логическую функцию «И» (в DAX &&), чтобы объединить два условия: год в нужном диапазоне и количество больше среднего.
- Повторно настроить тип диаграммы, чтобы корректно отобразить закрашенные области.
Мера для определения среднего значения по году (без учета месяцев):
AVERAGE([Количество]);
ALL('Таблица2'[Месяц])
)
Мера DAX для закрашивания значений выше среднего по году:
MAX([Год])>=YEAR(TODAY())-3 && [Кол-во]>=[Среднее];
[Кол-во];
0
)
Заключение
Решить задачу с помощью простой таблицы и добавления полей не всегда получается — при агрегации в Excel данные могут считаться неправильно. Альтернативный способ - использовать надстройку Power Pivot или создать отдельную таблицу с вычисляемыми полями, хотя последний вариант менее удобен.
Надеюсь, этот материал поможет вам сделать визуализацию информации разнообразнее и нагляднее.