私は小さな副業の収入と支出を追跡するための Excel スプレッドシートを持っています。収入と支出を月/年ごとにグループ化して表示するクラスター棒グラフを作成しようとしています。
収入表は次のようになります。
経費表は次のようになります。
私が望むのは次のようなものです:
青が支出、赤が収入です。
問題は、日付に重複があるため、収入項目と支出項目の関係をどのように作成すればよいかわからないことです。
私が現在行っているのは、行が月/年で、列がその月の合計のみである収入と支出のピボット テーブルを作成し、生のデータを手動で別のテーブルにコピーすることです。これはうまくいきますが、もっと良い方法があるはずです。
2 つの生データ テーブルを使用してこの関係を作成する方法はありますか?
私は SQL のバックグラウンドを持っているので、Excel では時々少しイライラすることがあります。
答え1
これは、Power Pivot を使用するのに最適な例です。
収入テーブルと支出テーブルの両方をデータ モデルに追加します。
DATE テーブルを作成し、それをデータ モデルに追加します。それを DATE テーブルとしてマークします。
日付と収入、日付と支出の関係を作成します。
これで、データ モデルに基づいてピボット グラフを作成し、任意のテーブルの要素を含めることができるようになりました。
モデルにいくつかの明示的な尺度を追加しました。
Total Income:= SUM ( Income[Total] )
Total Expense:= SUM ( Expense[Total] )
動作例ファイルが利用可能ここ
答え2
Excel でそのような処理をする場合、すべてのエントリを同じテーブルに入れる方が簡単でわかりやすいことがよくあります。列を追加して、収入行か支出行かを指定できます。
しかし、あなたが求めているものに対する解決策はあります。
まず、「合計」列の名前を「収入」と「支出」に変更します。一意のフィールドが必要です。
ピボット グラフを作成するときは、「このデータをデータ モデルに追加」を選択します。すると、ピボット グラフ フィールド メニューに「すべて」オプションが表示されます。ここで、ドキュメント内のすべてのテーブルからデータを選択できます。
Excel 2016 では、月と年のフィールドが自動的に使用可能になります。以前のバージョンでは、これらの列を追加する必要がある場合があります (ただし、month() 関数と year() 関数を使用して計算し、列を非表示にすることができます)。
見るそのリンク詳細については。