Excelのピボットグラフを使って前年比比較チャートを作成する方法 - TechRepublic

この Excel ピボットグラフには、3 年間の請求書の前年比比較が表示されます。 — Excel ピボットグラフを作成すると、次のような前年比の結果を表示できます。

多くの組織では、財務実績を評価するために、収益やその他の資産を前年比で比較し、これを「前年比分析」と呼びます。収益やその他の資産を測定（比較）することで、組織が意図したとおりに成長しているかどうかを確認できます。この記事では、Microsoft Excelのピボットテーブル機能とピボットグラフ機能を使用して、前年比グラフを作成する方法を説明します。

参照: ソフトウェアインストールポリシー (TechRepublic Premium)

Windows 10 64ビットシステムでMicrosoft 365を使用していますが、以前のバージョンもご利用いただけます。すべての問題が解決するまで、Windows 11へのアップグレードは控えることをお勧めします。ご参考までに、デモ用の.xlsxファイルをダウンロードできます。この機能は、以前の.xls形式ではサポートされていません。Web版Excelは、既存の.xlsxファイルでピボットテーブルとピボットグラフの両方をサポートしています。Web版Excelでピボットテーブルを作成することもできますが、列をグループ化することはできません。また、ピボットグラフを挿入することもできません。

前年比チャートとは何ですか?

多くの組織は、目標達成に向けた成長率を確認するために、前年比チャートを活用しています。必ずしも収益を比較する必要はありません。例えば、コールセンターであれば、過去数年間のサービスコールの増減を比較するかもしれません。また、投資家を探している組織や会社売却を検討している組織は、好ましい前年比分析を提示する必要があります。これは、業績を評価する効果的な方法です。

図Aに示す前年比グラフでは、データセットの各年が1本の線で示されています。2022年第1四半期を2021年と2022年と比較したい場合は、図Aに示すように、各年を1本ずつ、合計3本の線で表示する必要があります。データポイントが何を表すかは、ニーズによって異なります。この例では、グラフは各月の請求書数を比較しています。

図A

これらの比較は、企業のデータ保存方法によって、比較的簡単になる場合もあれば、非常に複雑になる場合もあります。可能であれば、シンプルなデータベースタイプのリストから始めることをお勧めします。また、Excelを使用してデータを分析する場合も同様です。例えば、図Bのシートには顧客の請求書情報が保存されています。各レコードには、顧客、出荷日、請求月、請求合計額が示されています。（2022年のレコードは数件しか表示されていませんが、表には2020年、2021年、2022年のレコードが数十件含まれています。）

図B

ここまでで、シンプルなYOYピボットグラフの見た目と目的を理解し、ソースデータも確認できました。次は、ピボットグラフをサポートするピボットテーブルを作成しましょう（図A）。

Excelでピボットテーブルを作成する方法

データ構造のおかげで、ピボットテーブルは簡単に作成できます。しかし、実際に作業を始める前に、まず何が必要なのかを確認しましょう。この例では、請求書の件数を年と月ごとにカウントします。つまり、レコードを月と年ごとにグループ化する必要があります。複雑に思えるかもしれませんが、心配はいりません。それほど複雑ではありません。

次のようにピボットテーブルを作成していきましょう。

テーブルデータセット内の任意の場所をクリックします。
[挿入] タブをクリックし、[テーブル] グループで [ピボットテーブル] を選択します。
図Cに示すように、Excelはユーザーのニーズを的確に判断します。設定を確認してから「挿入」をクリックしてください。

図C

新しいシートには、空のピボットテーブルフレームとフィールドリストが表示されます。
図 Dを参考に、リストからフィールドを下部にある適切なリストコントロールにドラッグします (ピボットテーブルでは CustomerID は必要ありません)。

図D

Years フィールドはどこから来たのか疑問に思っていませんか？ソースデータに日付フィールドが含まれている場合、Excel では自動的に追加されます。

ピボットテーブルの表示が異なっている場合は、月を表すセル（左端のフィールド）のいずれかを右クリックし、「グループ化」を選択します。「基準」リストで、「月」と「年」が選択されていることを確認してください（図Eを参照）。「四半期」または「日」の選択を解除する必要があるかもしれません。

図E

ピボットテーブルデータで請求書を月別および年別にカウントしたら、ピボットグラフを作成します (図 A )。

Excelでピボットグラフを作成する方法

この時点で、請求書の件数を月ごと、年ごとに集計するピボットテーブルが完成しました。ここから、前年比のピボットグラフを作成するのは簡単です。

作成したピボットテーブル内の任意の場所をクリックします。
[挿入] タブをクリックし、[グラフ] グループから [ピボットグラフ] を選択します。
表示されるダイアログで、左側のリストから「線」を選択し、「OK」をクリックします。
図 F は、 2 つのピボットオブジェクトを並べて示しています。

図F

ピボットチャートはほとんどの作業を実行しますが、チームメンバーが見たいのはピボットチャートです。

簡単でしたね！ピボットテーブルがグループ化の作業を行うので、ピボットグラフはユーザーによる入力をほとんど必要としません。ピボットテーブルは、データやピボットテーブルだけでは伝えられないストーリーを伝えてくれます。2022年第1四半期は、いわゆる「桁外れ」の好調ぶりのようです。もちろん、請求書の数は実際の収入を反映していません。そのため、ピボットテーブルを変更する必要があります。

Excelで請求書の金額を合計する方法

YOYピボットグラフはチームメンバーの誰かにとっては役立つかもしれませんが、ほとんどの人は収益に関心があるでしょう。3年間の収益を比較するには、ピボットテーブルに「金額」フィールドを追加する必要があります。そのためには、ピボットテーブル内の任意の場所を右クリックし、表示されるサブメニューから「更新」を選択します。これにより、データソースの「金額」がフィールドリストに追加されます。

図 Gに示すように、フィールドリストから値コントロールリストに金額をドラッグし、請求額フィールドを削除します。

図G