Создать сводную диаграмму, сравнивающую данные о доходах и расходах

Создать сводную диаграмму, сравнивающую данные о доходах и расходах

У меня есть таблица Excel для учета доходов и расходов моего небольшого побочного бизнеса. Я пытаюсь создать кластеризованную столбчатую диаграмму, которая показывает мои доходы и расходы, сгруппированные по месяцам/годам.

Таблица доходов выглядит следующим образом:

Таблица доходов

А таблица расходов выглядит так:

Таблица расходов

Мне бы хотелось что-то вроде этого:

Диаграмма

Где синим цветом обозначены мои расходы, а красным — мои доходы.

Проблема в том, что я не знаю, как создать связь между статьями доходов и статьями расходов, поскольку у Date есть дубликаты.

В настоящее время я создаю сводную таблицу для доходов и расходов, строки которой представляют собой месяц/год, а единственный столбец — это сумма итогов за этот месяц, а затем вручную копирую необработанные данные в отдельную таблицу. Это работает, но должен быть способ получше!

есть ли способ создать такую ​​связь между двумя таблицами необработанных данных?

Я имею опыт работы с SQL, поэтому Excel иногда меня немного расстраивает.

решение1

Это прекрасный пример того, когда следует использовать Power Pivot.

Добавьте в модель данных таблицы доходов и расходов.

Создайте таблицу DATE и добавьте ее в модель данных. Отметьте ее как таблицу DATE.

Создайте связи между датой и доходом, а также датой и расходом:

Связь между таблицами в Power Pivot

Теперь вы можете создать сводную диаграмму на основе модели данных и включить в нее элементы из любой таблицы.

Сводная диаграмма на основе модели данных со значениями из всех трех таблиц

Я добавил в модель несколько явных показателей:

Total Income:= SUM ( Income[Total] )
Total Expense:= SUM ( Expense[Total] )

Доступен рабочий пример файлаЗДЕСЬ

решение2

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

Однако есть решение вашей проблемы.

Сначала переименуйте столбцы "Итого" в "Доходы" и "Расходы". Вам нужны уникальные поля.

При создании сводной диаграммы выберите «Добавить эти данные в модель данных». Затем в меню «Поля сводной диаграммы» у вас будет опция «все». Там вы сможете выбрать данные из каждой таблицы в документе.

введите описание изображения здесь

В Excel 2016 вам автоматически будут доступны поля месяца и года. Для более ранних версий вам, возможно, придется добавить эти столбцы (но вы можете вычислить их с помощью функций month() и year() и скрыть столбцы).

Видетьэта ссылкадля получения подробной информации.

Связанный контент