Excelダッシュボードの作成とデータの見える化

顧客管理・商談管理テンプレートのダッシュボードを見てみよう

これまでのレッスンで使ってきたExcelの「顧客管理・商談管理テンプレート」のダッシュボードシートを見てみましょう。予算や売上、商談、タスクに関する表やグラフが掲載されています。表は、各シートのデータについて、定義した集計期間で自動集計し、その集計データを基にグラフが変化します。そのため、このシートに都度入力すべきものは基本的にはありません。

「顧客管理・商談管理テンプレート.xlsx」をダウンロード

集計表を理解しよう

ダッシュボードにある集計表をそれぞれ理解しましょう。前提として、集計表はダッシュボードシートの上部に入力された集計期間で算出されています。自社の会計期間などに基づいて任意の日付を入力しておきましょう。

プロダクト別集計表

プロダクト別集計表は、商談管理シートの情報をプロダクト別に集計したものです。例えば、サービスAについては、商談が何件あって（G列）、商談が進んでいるものが何件で（H列）、売上見込み（J列）と売上（O列）がそれぞれいくらか把握できます。

それぞれの列の意味と、使われている関数は以下の通りです。関数については、算出期間や、空欄なら表示しない、などと複数の条件式を組み合わせているため、一見複雑です。ただし、テンプレートに入力してある式からどのデータを参照しているのか具体的に理解できますし、式を変更したい場合も、これまでのように関数を自身で考えるのではなく、生成AIに質問して条件式を作成してもらってもよいでしょう。

Excelに使われている関数	説明
IF関数 COUNTIFS関数
	G32セルを解説します。 IF関数: 条件に基づいて結果を返す条件分岐関数です。 条件式: B32=""（B32が空でないかどうか） 真の場合: ""（空白を返す） 偽の場合: COUNTIFS(商談管理!B:B, "*" & B32 & "*", 商談管理!P:P, ">=" & $C$2, 商談管理!P:P, " COUNTIFS関数: 複数の条件を持つセルの数をカウントします。 条件1: 商談管理!B:B（B列） 条件式: "*" & B32 & "*"（B32の値が部分一致するかどうか） 条件2: 商談管理!P:P（P列） 条件式: ">=" & $C$2（P列の値がC2以上かどうか） 条件3: 商談管理!P:P（P列） 条件式: " この式は、B32が空でない場合に、商談管理シートのB列がB32の値と部分一致し、かつP列の値がC2からE2の期間内にあるセルの数をカウントします。 B32が空の場合は空白が返されます。
IF関数 SUMIFS関数
	J32セルを解説します。 IF関数: 条件に基づいて結果を返す条件分岐関数です。 条件式: B32=""（B32が空でないかどうか） 真の場合: ""（空白を返す） 偽の場合: SUMIFS(商談管理!R:R, 商談管理!B:B, "*" & B32 & "*", 商談管理!P:P, ">=" & $C$2, 商談管理!P:P, " SUMIFS関数: 複数の条件を持つセルの合計を計算します。 合計対象範囲: 商談管理!R:R（R列） 条件1: 商談管理!B:B（B列） 条件式: "*" & B32 & "*"（B32の値が部分一致するかどうか） 条件2: 商談管理!P:P（P列） 条件式: ">=" & $C$2（P列の値がC2以上かどうか） 条件3: 商談管理!P:P（P列） 条件式: " この式は、B32が空でない場合に、商談管理シートのB列がB32の値と部分一致し、かつP列の値がC2からE2の期間内にあるセルのR列の値を合計します。 B32が空の場合は空白が返されます。
ISERROR関数	ISERROR(I32/G32): この部分は、I32をG32で割った結果がエラー（例: ゼロで割り算が発生した場合）かどうかを判定します。ISERROR関数は引数で与えられた式がエラーを含む場合にTRUEを返し、エラーがない場合にFALSEを返します。
	L32セルを解説します。 ISERROR(I32/G32): この部分は、I32をG32で割った結果がエラー（例: ゼロで割り算が発生した場合）かどうかを判定します。ISERROR関数は引数で与えられた式がエラーを含む場合にTRUEを返し、エラーがない場合にFALSEを返します。 IF(ISERROR(I32/G32),"",(I32/G32)): この部分は、前述のISERROR関数の結果に基づいて条件分岐を行います。もしI32をG32で割った結果がエラーであれば（TRUEであれば）、空白を返します。エラーでなければ（FALSEであれば）、I32をG32で割った結果を返します。 つまり、この式は「I32をG32で割った結果がエラーでない場合、その結果を返し、エラーの場合は空白を返す」という処理を行っています。

担当者別集計表

担当者別集計表は、商談管理シートの情報を担当者別に集計したものです。例えば、田中太郎さんについては、商談が何件あって（E列）、売上見込み（J列）と売上（O列）がそれぞれいくらか把握できます。各担当者の予算については、このダッシュボードシートでのみ管理するため、事前に各担当者の予算（D列）を入力しておきましょう。

それぞれの列の意味と、関数については以下の通りです。

Excelに使われている関数	説明
IF関数 ISERROR関数 AVERAGEIFS関数
	I43セルを解説します。 IF関数: 条件に基づいて結果を返す条件分岐関数です。 条件式: ISERROR(AVERAGEIFS(商談管理!K:K, 商談管理!C:C, B43, 商談管理!P:P, ">=" & $C$2, 商談管理!P:P, " 真の場合: ""（空白を返す） 偽の場合: AVERAGEIFS(商談管理!K:K, 商談管理!C:C, B43, 商談管理!P:P, ">=" & $C$2, 商談管理!P:P, " AVERAGEIFS関数: 複数の条件を持つセルの平均を計算します。 平均対象範囲: 商談管理!K:K（K列） 条件1: 商談管理!C:C（C列） 条件式: B43（C列がB43と一致するかどうか） 条件2: 商談管理!P:P（P列） 条件式: ">=" & $C$2（P列の値がC2以上かどうか） 条件3: 商談管理!P:P（P列） 条件式: " この式は、AVERAGEIFS関数を使用して、商談管理シートのK列の値に対して、C列がB43と一致し、かつP列の値がC2からE2の期間内にある条件を満たすセルの平均を計算します。エラーが発生した場合は空白が返されます。

サマリー表

ダッシュボードシート上部にある集計表は、担当者別集計表のデータの一部をそれぞれ参照するなどしています。

それぞれの意味については以下の通りです。

タスクリスト

タスクリストは、タスク管理シートの「完or未完」（B列）で「未完」となっているタスクを日付順で10件転載しています。ここでは、Excelの「動的配列数式（スピル）」であるSORT関数を使用しています。動的配列関数とは、1つのセルに式を入力するだけで、そこに隣接するセルにも結果が表示されるものです。別のシートから条件に合致するものを転載する、といった場合に活躍する関数です。

Excelに使われている関数	説明
SORT関数 FILTER関数
	FILTER関数: 条件に基づいてセル範囲をフィルタリングする関数です。 フィルタリング対象範囲: タスク管理!B5:H15（B5からH15までの範囲） 条件: タスク管理!B5:B15 = "未完"（B列が"未完"であるセルのみを抽出） SORT関数: セル範囲の値を並べ替える関数です。 ソート対象範囲: FILTER(タスク管理!B5:H15, タスク管理!B5:B15 = "未完")（FILTER関数で抽出された結果をソート） ソートする列: 2（2列目、つまりB列を基準にソート） ソートの順序: 1（昇順） この式は、タスク管理シートのB列が"未完"である行を抽出し、それをB列を基準に昇順でソートしています。結果として、未完了のタスクがB列を基準に昇順で表示されます。

グラフを理解しよう

表を理解したら、次に3つのグラフをそれぞれ理解しましょう。

サマリー

サマリーの棒グラフは、予算、売上見込み、売上を棒グラフにしたシンプルなグラフです。グラフ部分をダブルクリックすると、グラフデザインというリボン（機能のボタンやメニュー）が表示されます。このリボンから、グラフの形式や色、設定など現状のグラフがどのようにできているかが分かります。

例えば、リボンの中の[グラフデータの選択]をクリックすると、グラフのデータ範囲がサマリーの表であることや、横軸のラベルにもサマリー表の項目が使われていることが分かります。

凡例の位置を変えたい場合はリボンの中の「グラフ要素を追加」、目盛の書式設定を変えたい場合は、グラフの中の目盛線をダブルクリックして表示される書式ウインドウから変更する、といったことが可能です。

プロダクト別実績

プロダクト別実績の表についても、[グラフデータの選択]からどこを参照しているか確認してみましょう。こちらは、プロダクト別集計表の一部のデータを参照してグラフ化していることが分かります。グラフのデータの範囲は、表全体でなくても、表の一部を複数組み合わせることも可能です。

担当者別予実

担当者別予実のグラフについて、[グラフデータの選択]からどこを参照しているか確認してみましょう。こちらは、担当者別集計表の一部を参照していることが分かります。

グラフを作ってみよう

データからグラフを作ってみましょう。例えば、プロダクト別集計表について、プロダクト名と、それぞれの売上見込み、売上をグラフにしたい場合です。グラフにしたい範囲を選択した上で、[挿入]>[おすすめグラフ]を押下すると、Excelがグラフの形式を推奨してくれます。

[おすすめグラフ]に希望のグラフが表示されなかったら、ほかのグラフの選択肢を選びましょう。

ピボットを使って表やグラフを作ってみよう

「顧客管理・商談管理テンプレート」にあるデータを表やグラフにするにはExcelの「ピボット」という機能がとても便利です。例えば、下記の表とグラフは「見込み客・取引先マスタシート」の都道府県の列の情報を基に、都道府県別の件数と比率をまとめたもので、「ピボット」を使って簡単に作成できます。「ピボット」はデータベースを見える化するのに大変役立つ機能ですので使えるようになりましょう。

ピボットをつかって、上記の表とグラフを作成する方法は次の通りです。慣れない間は、ピボットグラフのフィールドの設定に苦戦するかもしれません。何度も使ってこつをつかみましょう。