Excelで地域別・四半期売上を可視化する動的レポート(ダッシュボード)の作成方法

本記事では、地域ごとの四半期売上高を表示するレポートをExcelで作成する方法を、手順を追って詳しく解説します。完成品は、データが更新されると自動的に最新の状態へ反映される、動的かつインタラクティブなExcelダッシュボードとして活用できます。

この記事を読み終える頃には、以下のようなレポートが自分の手で作成できるようになります。

解説に使用したワークブックはダウンロード可能です。記事を読み進めながら、実際に同じ手順を試してみてください。

地域別四半期売上レポートを作成する手順

今回のデモでは、次のようなデータセットを使用します。

このデータには日付ごとの売上記録が含まれています。Excelの「テーブル」機能と「ピボットテーブル」機能を活用することで、これを四半期単位に整理し直します。

ステップ1:データセットをテーブルに変換する

データがまだテーブル形式でない場合は、まず範囲をテーブルに変換しましょう。Excelのテーブル機能は、参照・フィルタリング・並べ替え・更新など、多くの作業を効率化してくれる非常に便利な機能です。

• テーブルに変換したい範囲内のセルを選択し、キーボードでCtrl+Tを押します。または、挿入タブのテーブルグループにあるテーブルをクリックしても構いません。

• するとテーブルの作成ダイアログボックスが表示されます。範囲は自動的に選択され、「テーブルにヘッダーが含まれています」チェックボックスにも自動でチェックが入ります。OKをクリックすればテーブルが作成されます。

これでデータセットがテーブル形式に変換されました。

ステップ2:テーブルに名前を付ける

ここでテーブルに名前を付けておきましょう。後の作業が格段に楽になります。

テーブル名はテーブルデザインタブから変更できます。また、左上の名前ボックスを使って変更する方法もあります。今回はテーブル名を「Data」としました。

ステップ3:ピボットテーブルを作成する

レポート作成には、Excelで最も利用頻度の高いツールである「ピボットテーブル」を使用します。以下の手順で作成してください。

• まず、テーブル内の任意のセルを選択します。
• 次に挿入タブを開き、テーブルグループのピボットテーブルコマンドをクリックします。

• ピボットテーブルの作成ダイアログボックスが表示されます。コマンドをクリックする前にテーブル内のセルを選択していたため、テーブル/範囲フィールドにテーブル名(Data)が自動的に入力されています。
• ピボットテーブルは新しいワークシートに作成したいので、配置場所の選択ではデフォルトの新しいワークシートのままにしておきます。
• OKをクリックします。

新しいワークシートが作成され、ピボットテーブルのフィールド作業ウィンドウが自動的に表示されます。

ステップ4:カテゴリ別売上レポート用のピボットテーブルを準備する

まずカテゴリ別の売上レポートを作成し、その後に円グラフを追加していきます。レポートを作るために、ピボットテーブルのフィールドを次のように配置します。

下の画像をご覧ください。Sales(売上)フィールドを値エリアに2回配置しています。そのため、列エリアには追加の値フィールドが表示されています。また、行エリアにはCategory(カテゴリ)フィールドを配置しました。

画像の左側には、このフィールド設定によるピボットテーブルの出力結果が表示されています。

• 次に、売上の数値を総計に対する割合(%)で表示するよう書式を変更します。該当する列のセルを右クリックしてください。
• コンテキストメニューから値の表示方法を選択します。
• 続いて総計の%コマンドをクリックします。

これで列の値が総計に対する割合として表示されるようになりました。

ステップ5:カテゴリ別レポートの円グラフを作成する

データを視覚的にレポートするため、円グラフを追加しましょう。以下の手順で作成できます。

• まず、ピボットテーブル内のセルを選択します。
• 挿入タブを開き、グラフグループの円グラフアイコンをクリックします。
• ドロップダウンリストから円グラフを選択します。

ワークシート上に円グラフが表示されます。

いくつか調整を加えると、グラフは次のような見た目になります。

円グラフにカテゴリ名とデータラベルを表示する

データラベルは以下の手順で追加できます。

• まず、円グラフを選択します。
• グラフデザインタブのグラフのレイアウトグループにあるクイックレイアウトをクリックします。
• ドロップダウンからレイアウト1を選択します。

別の方法:

より柔軟な手法として、GETPIVOTDATA関数を使ってデータラベルを追加する方法もあります。この関数を使えば、ピボットテーブルから必要なデータを取り出せます。

下の画像は、元データから作成したピボットテーブルです。State(州)フィールドを行エリアに、Category(カテゴリ)フィールドを列エリアに、Sales(売上)フィールドを値エリアに配置しています。

ここで、ExcelのGETPIVOTDATA関数を見てみましょう。

GETPIVOTDATAの構文: GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], …)

ピボットテーブルにはdata_field(データフィールド)が1つだけありますが、それ以外のフィールドはいくつでも指定できます。

上記のピボットテーブルの場合:

• data_fieldはSales(売上)フィールドです。
• その他の2つのフィールドはState(州)とCategory(カテゴリ)です。

下の画像では、セルH9に次のGETPIVOTDATA数式を入力しています。

=GETPIVOTDATA("Sales", A3, "State", H7, "Category", H8)

この数式により、セルH9には950という値が返されます。

この数式はどのように機能するのか?

• data_field引数は言うまでもなくSales(売上)です。
• A3はピボットテーブル内のセル参照です。ピボットテーブル内のどのセルでも指定できます。
• field1, item1 = "State", H7。「Stateフィールドのうち、セルH7の値(Idaho)である項目」と読み替えられます。
• field2, item2 = "Category", H8。「Categoryフィールドのうち、セルH8の値(Office Supplies)である項目」という意味です。
• IdahoとOffice Suppliesが交差するセルの値、つまり950が返されるわけです。

ラベルを表示するには:

GETPIVOTDATA関数を使用して、カテゴリ名と売上値(全体に占める割合)をセルに表示させます(下の画像参照)。

理解を深めるため、セルD4に入力された数式を解説します。

=A4&" "&TEXT(GETPIVOTDATA("Sales", A3, "Category", A4), "0%")

• A4&" "の部分はシンプルです。セル参照の後にスペースを出力しています。
• 次にExcelのTEXT関数を使用しています。value(値)引数にはGETPIVOTDATA関数を渡し、format_text(表示形式)引数には"0%"という書式を指定しています。
• GETPIVOTDATAの部分は先ほど説明した通りなので、ここでは省略します。

次に、これらのデータをグラフ上に表示させます。

挿入タブ → 図グループ → 図形からテキストボックスを挿入します。

テキストボックスをグラフ上に配置したら、数式バーに等号(=)を入力し、セルD4を選択します。

Enterキーを押すと、テキストボックスにセルD4の値が表示されます。

同様の操作で他のテキストボックスも作成し、対応するセルを参照させます。

ポイント: 1つテキストボックスを作成すれば、そこから新しいテキストボックスを複製できます。手順は以下の通りです。

• 作成済みテキストボックスの枠線にマウスカーソルを合わせ、キーボードのCtrlキーを押します。プラス記号が表示されます。
• そのままマウスをドラッグすると、新しいテキストボックス(オブジェクト)が作成されます。好きな位置にドロップしてください。

これで、カテゴリ別売上を動的に表示する円グラフの作成は完了です。

最後に、このピボットテーブルの名前を「PT_CategorySales」に変更しておきます。

ステップ6:四半期売上用のピボットテーブルを準備する

年ごとの異なる四半期における売上の推移を確認したいケースもあるでしょう。

ここでは、次のようなレポートを作成します。

この画像は、各四半期の総売上に基づく米国上位15州を示しています。さらに、四半期ごとのトレンドを視覚化するためにスパークラインも追加しています。

四半期売上用のピボットテーブルは、以下の手順で作成します。

• まず、データテーブル内のセルを選択します。
• 挿入タブのテーブルグループからピボットテーブルを選択します。

• ピボットテーブルの配置場所を選択してOKをクリックします。今回のデモでは新しいワークシートを選択しました。

• 次に、Order Date(注文日)フィールドを列エリアへ、State(州)フィールドを行エリアへ、Sales(売上)フィールドを値エリアへそれぞれ追加します。

• 四半期ごとのレポートを表示するには、列ラベル内の任意のセルを右クリックし、コンテキストメニューからグループ化を選択します。

• グループ化ダイアログボックスの単位セクションで四半期を選択します。

• OKをクリックすると、ピボットテーブルは次のように変わります。

ステップ7:売上上位15州を表示する

前のステップの結果には、データセット内のすべての州の四半期レポートが含まれています。すべての州が必要であれば、このまま進めて問題ありません。しかし、より詳細な分析のために上位の州だけを抽出したい場合は、以下の手順が便利です。

• まず、State(州)列(または行ラベル)内の任意のセルを右クリックします。
• コンテキストメニューのフィルターにマウスを合わせ、トップテンを選択します。

• トップテン フィルター(State)ダイアログボックスの表示オプションで15を選択します。

• OKをクリックすると、ピボットテーブルには売上上位15州のみが表示されます。

ステップ8:スパークラインを追加する

スパークラインを追加する前に、行と列の両方の総計を削除しておきます。詳細な手順は以下の通りです。

• まず、ピボットテーブル内のセルを選択します。
• リボンのデザインタブを開きます。
• レイアウトグループから総計を選択します。
• ドロップダウンリストから行と列をオフにするを選択します。

これで総計の行・列が削除されました。

• スパークラインを追加するには、セルF5を選択し、リボンの挿入タブを開きます。
• スパークライングループから折れ線を選択します。

• スパークラインの作成ダイアログボックスで、データ範囲にB5:E19、場所の範囲にF5:F19を指定します。

• OKをクリックすると、ピボットテーブルは最終的に次のような見た目になります。

• さらに見栄えを良くするために、マーカーを追加しましょう。スパークラインを含むセルを選択するとリボンにスパークラインタブが現れるので、表示グループのマーカーにチェックを入れます。

これがスパークラインの最終的な出力結果です。

ステップ9:スライサーを追加して出力をフィルタリングする

ピボットテーブルにスライサーを追加するには、以下の簡単な手順に従ってください。

• まず、スライサーを作成したいピボットテーブルを選択します。
• 挿入タブのフィルターグループにあるスライサーコマンドをクリックします。

• スライサーの挿入ダイアログボックスに、ピボットテーブルで利用可能なすべてのフィールドが表示されます。スライサーを作成したいフィールドを選択してください。今回のデモでは、Customer Name(顧客名)、State(州)、Category(カテゴリ)の3つのフィールドを選択しました。

• OKをクリックすると、3つのスライサーがワークシートの上部に表示されます。

ステップ10:最終レポートを仕上げる

個別に作成した要素をすべて1枚のワークシートにまとめ、最終的なレポートを完成させましょう。

スライサーで項目を選択・解除すると、結果がリアルタイムで変化します。例えば、State(州)スライサーでArizonaを選択すると、その州に関するデータだけが表示されます。

複数の項目を同時に選択することも可能です。例えばAlabamaを追加選択すると、次のように表示されます。これで、地域別の四半期売上を表示するレポートの完成です。

まとめ

以上が、Excelで地域別の四半期売上を表示するレポートを作成するために必要なすべての手順でした。このガイドを参考にすれば、誰でも簡単に独自のダッシュボードを作成できるはずです。本記事が皆様のお役に立てば幸いです。ご質問やご提案があれば、ぜひコメント欄でお知らせください。

関連記事

• Excelで経費報告書を作成する方法(初心者向け手順)
• Excelで収支報告書を作成する(実例3選)
• Excel VBAでPDF形式のレポートを生成する方法(3つのテクニック)
• Excelで生産報告書を作成する(2つのパターン)
• Excelで日次活動報告書を作成する方法(実例5選)
• Excelで日次生産報告書を作成する(無料テンプレート付き)
• Excelで通信簿・成績表を作成する方法(無料テンプレート付き)