MS Excel - 製品と月（前年比）に分類されたグラフを作成するためにデータをモデル化するにはどうすればよいですか?

Question

生データには日付列が 1 つだけあります。日付の範囲は 2018 年から 2018 年までです。複数の製品があり、各行は販売取引を表しています。製品ごとに複数の取引があります。

ピボット テーブルに 2 つの列 (1 つは 2018 年用、もう 1 つは 2019 年の日付用) を作成するにはどうすればよいでしょうか。これは可能ですか。

これを実行する理由は、2018 年と 2019 年の日付値を並べてピボット バー チャートを作成できるようにするためです。例: 2018 年 1 月、2019 年 1 月、2018 年 2 月、2019 年 2 月などの販売数量。これを行う最適な方法は何でしょうか。

追伸: 数千行分のデータがあります。

さらに、上記の日付の複数の製品の販売数量を表示したいと思います。たとえば、2018 年 1 月と 2019 年 1 月、2018 年 2 月と 2019 年 2 月などです。これをピボット バー チャートに追加するにはどうすればよいでしょうか。取得したデータを最も適切に再モデル化する方法についてアドバイスをいただければ幸いです。

私の生データ

グラフはこんな感じにしたいと思っています。ただし、各製品の内訳も表示して、前年比の傾向を確認できるようにしたいと思います。

チャートの見た目がどうなっているかについての例をオンラインで見つけることができたので、ピボットも次のようになる必要があると想定しています。

Answer 1

Excel にすぐにアクセスできないので、LibreOffice Calc で基本をカバーするものを大まかに作成しました。

日付列と数量列は、あなたの例からのものです。必要に応じてデータを整理するための簡単な方法として、いくつかのヘルパー列を追加しました。

月の列は日付から月番号を抽出します。C2:

=MONTH(A2)

年列は日付から年を抽出します。D2:

=YEAR(A2)

月を適切な順序でグラフ化したい場合は、月番号を使用します。月名はアルファベット順にグラフ化されるため、ピボット テーブルは月番号に基づきます。ただし、グラフのラベルには月名を使用する必要があります。

月の名前を取得する方法はいくつかあります。列 E には 1 つの方法が含まれています (他の方法を使用する場合は必要ありません)。E2:

=TEXT(A2,"mmm")

これは、日付をフォーマットして、月の略称のみを表示するだけです。ピボット テーブルの結果からグラフを作成する場合、月の名前をピボット テーブルの月番号 (列 G に表示される) に関連付ける必要があります。このために列 E を使用するには、G3 に次の内容を含めます。

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

これは、値を検索する方法の 1 つにすぎません。VLOOKUP は、データの月列のピボット テーブルから月番号を検索し、最初の一致に対して列 E から月名を取得します。

別の方法では列 E は必要ありません。月番号を変換するだけです。

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

CHOOSE は、リストから月の名前を選択するためのインデックスとして月番号を使用します。月番号が「N」の場合、リストの N 番目の名前が選択されます。

ピボット テーブルでは、Qty、Month、Year 列をソース データとして選択します。ピボット テーブル ダイアログで、Year を列ウィンドウに、Month を行ウィンドウに、Qty を値ウィンドウにドラッグします (集計方法が Sum に設定されていることを確認します)。これで、グラフ用に整理されたデータが得られます。

Excel でグラフを作成してからしばらく経ちました。データ範囲を定義するときに、軸ラベルの月名を選択できると記憶しています。LO Calc ではそれができません。サンプル グラフでは、軸ラベルを省略し、軸タイトルを追加し、タイトルに月名を入れ、スペースを入れてすべてを揃えました。