Excelデータ活用法シリーズの最終回です。
これまででデータを整理し、分析や可視化を行ったので、今回は、重要な情報のみ整理して自分で確認して、その情報を他の人にも共有するステップに進めて説明します。
データの全体的な流れを把握して共有するための最も一般的な手段としてダッシュボードがあります。特に企業では同じ指標を全社的に共有して管理しなければならないので、共通で見るダッシュボードの構成は非常に重要です。
そこで今回のブログでは「ダッシュボード型レポートの作成」をテーマに取り上げます!
以前の2つのブログと同じデータセットを使います。
主に使用する機能は「ピボットテーブル」と「グラフ」です。
ステップ1. ダッシュボードの構成を計画する
まず、ダッシュボードをどのように構成するのかを整理しておくと、途中で迷うことが少なくなります。
ダッシュボードで知りたいコア指標やKPIを先に設定し、どの時点で指標を表示するのか考えてみましょう。
今回私は利益の変数をKPIとして設定し、様々な要因ごとの利益の変化を調べてみようと思います。
データを調べて詳細な項目を以下のように構成しました。また、それぞれどのように視覚化するのかも同時に考えてみました。
- 利益と売上高の推移 (折れ線グラフ) - 最初の行に配置します。
- 変数別の利益率 (円グラフ) - 2行目に配置します。
(: 顧客セグメント別、製品カテゴリ別、配送方式別、地域別) - 割引率ごとの利益 (散布図) - 3行目に配置します。
- 利益 TOP10 の製品名 (表) - 3行目に配置します。
可能であれば、紙や空白のスライドにどのように構成するか、区画を分けてみるのもいいですね:) 私はこのようにして計画を立てた後にダッシュボードを作成しました。
ステップ2. 項目別のピボットテーブルを作成する
ピボットテーブルを作成する手順を動画で簡単に紹介しました。
ソースデータを選択した後、上部の [挿入] メニューの一番左側にある [ピボットテーブル] を選択します。[新規ワークシート] の追加を選択した理由は、それぞれのシートごとに「ソースデータのシート」「ピボットテーブルのシート」「ダッシュボードのシート」で整理したいためです。
「1. 利益と売上高の推移」を表示するための表(テーブル)を構成してみます。
順番は以下のような流れです。
(1) 行と値に必要な変数をフィールド名のウィンドウからドラッグします。
(2) 私は「Order Date」(注文日)を行ボックスに選択しましたが、日付変数の場合、年、四半期、注文日が自動的に生成されます。その中で四半期による区分は必要ないので削除した状態になっています。
(3) 値ボックスには「Profit」(利益)と「Sales」(売上)の平均値を指定しました。デフォルトでは合計値が選択されますが、下の右側の画像のように値リストでマウスの右クリックでフィールドの値を設定することができます。
*今回、平均を選択した理由は、月ごとに日数が異なるため合計では月別の推移を正しく把握することが難しいためです。
次に「2. 変数別利益率」の中から「顧客セグメント別利益率」の表(テーブル)を作成してみます。
(1) 先ほど作成した表全体を選択します。
-表(テーブル)の任意の場所にマウスを置いて [Ctrl] + [A] を押すか、表の先頭のセルであるA3をクリックして表全体を選択します。
(2) コピーして([Ctrl] + [C])、横のスペースに貼り付けます([Ctrl] + [V])。
(3) 行ボックスに「Segment」(顧客セグメント)、値ボックスに「Profit」(利益)を選択します。
*全体の利益からそれぞれの顧客セグメントが占める割合を把握するために、今回は合計値で設定しました。
このように各領域のボックスに必要な値を選択して最終的に以下のように合計6つのピボットテーブルが完成しました。
「4. 利益 TOP10 製品名」は表形式で表示したいので、ダッシュボードの中で直接ピボットテーブルの形で表示してみようと思います。
ステップ3. グラフを作る
2つのグラフを作成する手順をみて全体的な流れを理解しましょう。この内容をもとにすれば他のタイプのグラフを作成することも難なくできるようになります。
それに続いて、ピボットテーブルから上位の項目だけを抽出してみる方法を見てみましょう。
- 利益と売上の推移(折れ線グラフ)
ピボットテーブルの任意のセルをクリックした状態で上部の [挿入] メニューの中央にあるグラフを選択します。私は最初のグラフは折れ線のグラフにしようと思っていたので、折れ線グラフを選択してみます。
その結果、以下のようなグラフが作成されます。
グラフのデザインを少し修正してみます。 グラフの名前を入れて、凡例の位置も変更しようと思います。それぞれの構成要素を修正したい場合、その要素をダブルクリックすると「書式設定」ウィンドウが表示されます。
ただ、現在はグラフのタイトルが無い状態であるため、タイトルは別の方法で設定します。
グラフのタイトルを設定した後、凡例をダブルクリックして [凡例の書式設定] ウィンドウをアクティブにしました。
位置を「下」に設定します:)
データラベルも追加してみましょう。
以下のように系列をクリックしてデータラベルを追加した後、同様にダブルクリックして書式設定ウィンドウを開きます。
位置や表示形式など様々な書式を指定することができます。
最終的に以下のようなグラフが完成しました。
完成したグラフを切り取り([Ctrl] + [X])、ダッシュボードシートに貼り付けます。
- 変数別利益率(円グラフ) - 顧客セグメント、製品カテゴリ、配送方法別
顧客セグメント(Segment)、製品カテゴリ(Category)、配送方式(Ship Mode)、地域(State)別の合計4つの円グラフを作成する予定でしたが、地域別の平均利益については負の数値の地域が存在するため、地域別の利益については棒グラフで作成します。その他の項目は全て同様の形式であるため、代表して顧客セグメント別の利益率グラフを作成する手順をみてみましょう。
折れ線グラフのケースと同様に、該当のピボットテーブルをクリックした後、上部の [挿入] メニューからグラフタイプを選択します。
以下のような円グラフが作成されました。
デザインを修正して、より分かりやすくしてみましょう。
[デザイン] メニューの中の右側にある [クイックレイアウト] の「レイアウト1」を選択します。
以下のように、よりすっきりとしたデザインに修正されました。(グラフタイトルも修正します)
円グラフの色が濃いためか、ラベルの文字がよく見えないので、文字色を白に変更してみましょう。ラベルをクリックし、[ホーム] メニューから文字色を指定することができます。
折れ線グラフと同様に完成したグラフを切り取り([Ctrl] + [X])、ダッシュボードシートに貼り付けます。
- 利益 TOP10 商品名
今回はグラフではなくピボットテーブルをそのまま利用しますが、利益の上位10件のみを表示するようにします。
ソースデータを再び全選択して([Ctrl] + [A])、ピボットテーブルを作成し、ピボットテーブルを配置する場所を [dashboard] シートの適当なセルに指定します。
ピボットテーブルの行を「Product Name」(製品名)に、値を「Profit」(利益)の合計に指定すると以下のように構成されます。
テーブルの「Product Name」の右側に下向きの三角形(▼)が表示されていますか? TOP10のみ表示されるようにフィルタリングをするためにはこの機能が必要です。
三角形をクリックするとポップアップウィンドウが表示され、[値フィルター] - [トップテン] を選択します。並び替え基準を「合計 / 利益」に指定した後、値の基準を「上位10項目」として、表示したい行の件数を指定します。私はTOP10を表示するので10と指定しました。
このようにして設定が完了すると、目的のテーブルが完成します!
(*並べ替えを選択してないので、TOP10に属するリストが順番には並んでいませんね... 自分で作成する場合は並べ替えを指定することで、よりきれいなダッシュボードを作ることができます。)
ステップ4. ダッシュボードを構成する
グラフを作成してダッシュボードシートに貼り付けた結果は以下のようになります。
まだExcelの印象が強くてダッシュボードの感じが弱いですよね? いくつか設定をしてみましょう。
まず、私は背景色を指定したかったので、[ホーム] メニューで色を設定しました。下図の赤枠で表示された部分をクリックして全体を選択した後、色を変更してください。
ピボットテーブルも同時に色が変更されたので、その部分だけドラッグして白色に戻します。
次に、Excelで表示されている目盛線、見出し(ヘッダー)を削除します。
デザイン的な要素についてはさらに気にすることもできますが、最も基本的な内容に限定して取り上げてみました。
今回のブログでは省略しましたが、スライサー機能を利用してコンテンツ全体に対して一度に同じフィルターを適用することもできますので、参考にしてください :-)
ステップ5. ダッシュボードのデータをより詳しく分析する
ダッシュボードを見て気になった疑問(例. n月に売上が異常に低下した理由は何だろう?)について分析して答えを見つけたいと思いますか? 再度新たに多方面からデータを視覚化して分析するとなると時間も長くかかりそうですね... :(
このような場合、セルフサービスの分析ツール HEARTCOUNT を活用することができます。
まず、以下は HEARTCOUNT ABI で作成した、上記のExcelで作成したものと同様のダッシュボードです。
先頭のグラフをみると、利益と売上は似たような推移が見られる時期もありますが、全く逆の時期(2020年1月)もあります。
その違いに最も大きな影響を与えた変数が何であるのか調べてみましょう。
該当のグラフの右上にある HEARTCOUNT のロゴをクリックして分析機能に移動します。
利益と売上の折れ線の中から、利益の折れ線がそのまま視覚化されて表示されます。
その中でも2020年1月の売上データが気になるので、その月のデータのみをドラッグして視覚化してみます。
次に、X軸に売上(Sales)を指定した後、売上はプラス(+)にもかかわらず利益がマイナス(-)のポイントを中心に見てみましょう。
以下の画像のドラッグした部分が該当します。
それぞれの変数に応じてポイントの色を区別して比較してみましょう。
(1) 製品カテゴリ(Category)による区分
右側に色の凡例が表示されています。
水色で表示されている「Furniture」が、先ほど見た範囲(売上はプラス(+)にもかかわらず利益がマイナス(-)の部分)に属するポイントが多いことが分かります。次いで「Technology」「Office Supplies」の順にその範囲に含まれるポイントの数が多いようです。
(2) 地域(State)による区分
地域別に見ると、New York (緑色) や California (紫色)など地域ごとのポイントを確認することができます。
(3) 割引率(Discount)による区分
色が明るい黄色ほど低い割引率、濃い紺色ほど高い割引率を示します。前記の2つの変数よりも明確な違いがみられるようです。割引率が高くなるほど利益と売上が強い負の相関関係を見せていますね。
このように、視覚的な分析と併せて凡例をそれぞれ選択し、凡例ごとの相関係数を確認しながら答えを見つけることができます。
最後に、これまでは手動分析から答えを探す手順を説明してきましたが、自動分析により一度に答えを探す方法を紹介し、本ブログを終えたいと思います。
まず、無効化されている自動分析メニューを有効にするため、右上にある [このキャンペーンをサーバーにアップロード] ボタンを押してキャンペーンを保存します。
その後、有効化された左側のメニューから [KPIs 管理] メニューを選択して、「Profit」(利益)をKPIとして指定します。
KPIの設定完了後、要因分析機能に移動します。
要因分析は、KPIの変化を最もよく説明する変数を説明力の強い順に提示する機能です。
この分析では、データセットに含まれるすべての変数との相関関係が自動的に分析されるため、主観が介入したり、特定の変数が欠落する心配はありません。
要因分析画面を確認すると、割引率(Discount)と売上(Sales)の2つの変数が要因の相違を最もよく表す変数として提示されました。
以下のような視覚化画面も併せて表示され、具体的な数値を確認することもできます。
今回のシリーズの最後のブログが終了しました。
Excelを活用してダッシュボードを作る手順と、ダッシュボードをみて生じた質問に対して HEARTCOUNT を利用して答えを探す手順までを説明してきましたが、色々な内容を盛り込んだため、記事が長くなってしまいました... :)
本ブログでは HEARTCOUNT ABI で作成したダッシュボードから直接、分析機能に移行するシナリオを紹介しました。
ただ、お持ちのExcelファイルをアップロードする方法でも、すでに使用されているダッシュボードからデータを抽出してアップロードする方法のいずれでも、このように答えをすばやく簡単に見つけることができます。
HEARTCOUNT を無料で開始したい方は、公式ホームページの [無料でスタート] をクリックしてください。
長い文章を読んでいただきありがとうございました!
ダッシュボードと分析機能を組み合わせたソリューション、HEARTCOUNT ABI を知る→
「Excel」に関連する他のコンテンツ
sidney yang
sidney yang
