【Excel】ファネルグラフで段階的な減少を表現する方法

ビジネスの現場で、顧客獲得プロセスや販売プロセスなどの段階的な減少を視覚化したい場面はありませんか。

Excelで標準機能として用意されているグラフでは、このような段階的な減少を効果的に表現するのが難しいと感じるかもしれません。

この記事では、Excelでファネルグラフを作成し、段階的な減少をわかりやすく表現する手順を解説します。グラフ作成の基本から応用まで、具体的な操作方法をステップバイステップで学べます。

ぜひ最後まで読んで、データ分析に役立つファネルグラフを作成できるようになりましょう。

ファネルグラフの基本構造と作成の前提

ファネルグラフは、プロセスが進むにつれて段階的に減少していくデータを視覚化するのに適したグラフです。例えば、Webサイトへのアクセス数から問い合わせ数、商談数、成約数へと減少していく様子を表すのに使われます。

Excelでファネルグラフを作成するには、直接的な「ファネルグラフ」という種類はありません。そのため、積み上げ棒グラフを加工してファネル形状を表現します。この方法では、各段階のデータが中央揃えになるように見せる工夫が必要です。

ファネルグラフを作成する手順

積み上げ棒グラフで表現するためのデータ準備

ファネルグラフを作成するために、まずデータを適切な形式に加工します。各段階の減少数を直接入力するのではなく、全体に対する割合や、中心からのずれを計算した値を用意します。

1. 元データの準備
   A列に段階名(例:アクセス、問い合わせ、商談)、B列に各段階の数値(例:1000、500、200)を入力します。
2. 中心値の計算
   C列に、各段階の数値の半分(例:B2/2)を入力します。これがグラフの中心線となります。
3. 差引値の計算
   D列に、各段階の数値の半分から、一つ下の段階の数値の半分を引いた値(例:B2/2-B3/2)を入力します。これは、階段状の棒グラフの各部分の幅になります。
4. (オプション)一番下の段階の処理
   一番下の段階(例:成約)は、減少幅がないため、D列には0を入力します。

積み上げ棒グラフの作成とファネル形状への調整

準備したデータを使って、Excelの積み上げ棒グラフを作成し、ファネルグラフのように見せるための調整を行います。

1. グラフの元データの選択
   A列(段階名)、C列(中心値)、D列(差引値)の範囲を選択します。
2. グラフの挿入
   「挿入」タブをクリックし、「グラフ」グループから「棒グラフ」→「積み上げ棒グラフ」を選択します。
3. 系列の書式設定
   作成されたグラフ上で、D列(差引値)の棒グラフ(通常は一番手前にある棒)を右クリックし、「データ系列の書式設定」を選択します。
4. 系列の塗りつぶしなし
   「塗りつぶし」オプションで「塗りつぶしなし」を選択します。これにより、中心値の棒だけが見えるようになります。
5. 系列の重なり幅の調整
   D列(差立値)のデータ系列を選択した状態で、「系列のオプション」にある「系列のオーバーラップ」を「100%」に設定します。これにより、棒が重なり、中央揃えのように見えます。
6. (必要に応じて)グラフ要素の削除
   凡例や不要な軸ラベルなどは、グラフをクリックして選択し、Deleteキーで削除します。

データラベルの追加と書式設定

グラフの各段階の数値を表示し、より分かりやすいファネルグラフに仕上げます。

1. データラベルの追加
   C列(中心値)の棒グラフを右クリックし、「データラベルの追加」を選択します。
2. ラベルの書式設定
   追加されたデータラベルを右クリックし、「データラベルの書式設定」を選択します。
3. ラベルの位置調整
   「ラベルオプション」で「ラベルの位置」を「中央」などに設定し、見やすく調整します。
4. (オプション)段階名の追加
   必要であれば、段階名もデータラベルとして追加できます。「データラベルの書式設定」の「ラベルオプション」で「セルの値」にチェックを入れ、A列の段階名範囲を選択します。
5. (オプション)棒グラフの色調整
   C列(中心値)の棒グラフの色を、ファネルのイメージに合うように変更します。「塗りつぶし」オプションで好みの色を選択します。

ファネルグラフ作成時の注意点と失敗例

データ準備のミスによる形状の崩れ

ファネルグラフの肝となるのは、データ準備の段階です。特に、中心値と差引値の計算を間違えると、グラフの形状が意図したファネルにならなくなります。

計算式の間違い

中心値の計算がB列の数値そのものになっていたり、差引値の計算が一つ下の段階の数値になっていたりすると、棒グラフが左に寄ったり、幅が不均一になったりします。

対処法: 各段階の数値の半分を基準に、一つ下の段階の半分との差を正確に計算しているか、数式を確認してください。

棒グラフの重なり設定の誤り

積み上げ棒グラフを作成した後に、「系列のオーバーラップ」を100%に設定し忘れると、棒が隣り合って表示され、ファネル形状になりません。

系列のオーバーラップ未設定

グラフの見た目が、単なる積み上げ棒グラフのままになってしまいます。

対処法: 差引値のデータ系列を選択し、「データ系列の書式設定」で「系列のオーバーラップ」を100%に設定してください。

データラベルの表示位置の問題

データラベルが棒グラフの外側や、離れた位置に表示されてしまうと、どの段階の数値か分かりにくくなります。

ラベルが棒からずれる

数値と棒の対応関係が不明瞭になります。

対処法: データラベルを右クリックし、「データラベルの書式設定」から「ラベルの位置」を「中央」などに調整してください。

XLOOKUP関数やPower Queryとの連携

Excelの新しい関数や機能と組み合わせることで、ファネルグラフ作成の効率をさらに高めることができます。

XLOOKUP関数によるデータ集計の自動化

もし、集計元のデータが複数シートに分かれている場合、XLOOKUP関数を使って必要なデータを一つの表に集約できます。これにより、ファネルグラフ作成のためのデータ準備が効率化されます。

XLOOKUP関数とは

XLOOKUP関数は、指定した範囲から検索値と一致する値を検索し、対応する値を返します。VLOOKUP関数やHLOOKUP関数よりも柔軟な検索が可能です。Excel 2019以降、Microsoft 365で利用できます。

活用例: 顧客管理シートから、各段階の人数をXLOOKUP関数で自動的に集計し、ファネルグラフの元データとして利用します。

Power Queryによるデータ整形

Webサイトのデータやデータベースなど、外部から取得したデータを整形する際にPower Queryが役立ちます。複雑なデータ整形作業を自動化し、ファネルグラフ作成に必要なデータ形式に整えることができます。

Power Queryとは

Power Queryは、ExcelやPower BIなどで利用できるデータ取得・整形ツールです。繰り返し行うデータ整形作業を「クエリ」として保存し、ボタン一つで実行できます。Excel 2016以降で標準搭載、Excel 2010・2013ではアドインとして利用可能です。

活用例: WebサイトのアクセスログデータをPower Queryで取り込み、加工して「アクセス数」「問い合わせ数」などの段階的な数値を抽出し、ファネルグラフの元データを作成します。

まとめ

この記事では、Excelで積み上げ棒グラフを加工してファネルグラフを作成し、段階的な減少を視覚化する方法を解説しました。

データ準備からグラフの調整、ラベルの追加まで、具体的な手順を追うことで、ビジネスプロセスにおける減少傾向を分かりやすく表現できるようになります。

今後は、XLOOKUP関数やPower Queryと連携させ、データ集計・整形プロセスを自動化することで、さらに効率的にファネルグラフを作成できるようになるでしょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】