MS Excel — как смоделировать данные, чтобы создать диаграмму с разбивкой по продуктам и месяцам (год к году)?

Question

У меня есть один столбец даты в моих необработанных данных. Даты находятся в диапазоне от 2018 до 2018 года. У меня есть несколько продуктов, и каждая строка представляет транзакцию продажи. На каждый продукт приходится несколько транзакций.

Как мне сделать так, чтобы в моей сводной таблице было два столбца: один для дат 2018 года, а другой для дат 2019 года? Возможно ли это?

Причина, по которой я хочу это сделать, заключается в том, что я могу создать сводную столбчатую диаграмму со значениями дат 2018 и 2019 годов рядом друг с другом. Например, объемы продаж за янв. 2018 г., янв. 2019 г., февр. 2018 г., февр. 2019 г. и т. д. Как лучше всего это сделать?

P.S. У меня есть тысячи строк данных.

Кроме того, я хотел бы отобразить объемы продаж для нескольких продуктов для дат, как указано выше, например, янв. 2018 против янв. 2019, февр. 2018 против февр. 2019 и т. д. Как мне добавить это в сводную столбчатую диаграмму? Ваши советы о том, как лучше всего ремоделировать имеющиеся у меня данные, были бы весьма признательны.

Мои исходные данные

Как бы я хотел, чтобы выглядела моя диаграмма. За исключением того, что я хотел бы разбивку по каждому продукту, чтобы я мог видеть годовую тенденцию:

Мне удалось найти в Интернете пример того, как должна выглядеть моя диаграмма, и поэтому я предполагаю, что моя сводная диаграмма должна выглядеть примерно так:

Answer 1

У меня нет доступа к Excel, поэтому я набросал кое-что в LibreOffice Calc, что охватывает основы.

Столбцы Date и Qty взяты из вашего примера. Я добавил несколько вспомогательных столбцов как простой способ организовать данные так, как вам нужно.

Столбец месяца извлекает номер месяца из даты. C2:

=MONTH(A2)

Столбец года извлекает год из даты. D2:

=YEAR(A2)

Если вы хотите, чтобы месяцы были представлены в правильной последовательности, это делает номер месяца. Названия месяцев будут представлены в алфавитном порядке, поэтому сводная таблица основана на номере месяца. Однако вы все равно хотите, чтобы названия месяцев были маркированы на диаграмме.

Есть несколько способов получить названия месяцев. Столбец E содержит один подход (не нужен, если вы используете другой подход). E2:

=TEXT(A2,"mmm")

Это просто форматирует дату для отображения только аббревиатуры месяца. Когда вы строите диаграмму на основе результатов сводной таблицы, вы хотите связать название месяца с номером месяца в сводной таблице, который отображается в столбце G. Чтобы использовать для этого столбец E, G3 будет содержать:

=VLOOKUP(H3,$C$2:$E$7,3)

Это всего лишь один из способов поиска значения. VLOOKUP ищет номер месяца из сводной таблицы в столбце Month данных и извлекает название месяца из столбца E для первого совпадения.

Другой подход не требует столбца E. Он просто преобразует номер месяца:

=CHOOSE(H3,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

CHOOSE использует номер месяца как индекс для выбора названия месяца из списка. Номер месяца "N" выбирает N-ное название в списке.

Для сводной таблицы выберите столбцы Qty, Month и Year в качестве исходных данных. В диалоговом окне сводной таблицы перетащите Year в окно Columns, Month в окно Rows и Qty в окно Values ​​(убедитесь, что метод агрегации установлен на Sum). Это даст вам данные, организованные для построения диаграммы.

Прошло некоторое время с тех пор, как я делал диаграммы в Excel. Насколько я помню, вы можете выбрать названия месяцев для меток осей, когда определяете диапазоны данных. LO Calc этого не делает. Для своего образца диаграммы я убрал метки осей, добавил название оси и поместил названия месяцев в заголовок с интервалом, чтобы все выровнять.