У меня есть таблица Excel для учета доходов и расходов моего небольшого побочного бизнеса. Я пытаюсь создать кластеризованную столбчатую диаграмму, которая показывает мои доходы и расходы, сгруппированные по месяцам/годам.
Таблица доходов выглядит следующим образом:
А таблица расходов выглядит так:
Мне бы хотелось что-то вроде этого:
Где синим цветом обозначены мои расходы, а красным — мои доходы.
Проблема в том, что я не знаю, как создать связь между статьями доходов и статьями расходов, поскольку у Date есть дубликаты.
В настоящее время я создаю сводную таблицу для доходов и расходов, строки которой представляют собой месяц/год, а единственный столбец — это сумма итогов за этот месяц, а затем вручную копирую необработанные данные в отдельную таблицу. Это работает, но должен быть способ получше!
есть ли способ создать такую связь между двумя таблицами необработанных данных?
Я имею опыт работы с SQL, поэтому Excel иногда меня немного расстраивает.
решение1
Это прекрасный пример того, когда следует использовать Power Pivot.
Добавьте в модель данных таблицы доходов и расходов.
Создайте таблицу DATE и добавьте ее в модель данных. Отметьте ее как таблицу DATE.
Создайте связи между датой и доходом, а также датой и расходом:
Теперь вы можете создать сводную диаграмму на основе модели данных и включить в нее элементы из любой таблицы.
Я добавил в модель несколько явных показателей:
Total Income:= SUM ( Income[Total] )
Total Expense:= SUM ( Expense[Total] )
Доступен рабочий пример файлаЗДЕСЬ
решение2
Для такого рода вещей в Excel часто проще и чище поместить все ваши записи в одну таблицу. Вы можете добавить столбец, чтобы указать, является ли это строкой Дохода или строкой Расхода.
Однако есть решение вашей проблемы.
Сначала переименуйте столбцы "Итого" в "Доходы" и "Расходы". Вам нужны уникальные поля.
При создании сводной диаграммы выберите «Добавить эти данные в модель данных». Затем в меню «Поля сводной диаграммы» у вас будет опция «все». Там вы сможете выбрать данные из каждой таблицы в документе.
В Excel 2016 вам автоматически будут доступны поля месяца и года. Для более ранних версий вам, возможно, придется добавить эти столбцы (но вы можете вычислить их с помощью функций month() и year() и скрыть столбцы).
Видетьэта ссылкадля получения подробной информации.