ポートフォリオ分析はエクセルでできる！関数を用いた手順を徹底解説

顧客満足度を調査するポートフォリオ分析は、専用のソフトを使わずにエクセルでおこなえるマーケティングリサーチのひとつです。エクセルは多くの企業でも導入されているツールであるため、手軽に分析を始められます。

しかし「エクセルでどうやって分析すればいいのだろう」「関数の使い方がいまいちわからない」と困っている方も、多いのではないでしょうか。

今回は、エクセルを使ったポートフォリオ分析のやり方を解説します。関数を用いたやり方を知り、自社のマーケティング力を向上させたい方はぜひ参考にしてください。

顧客満足度はポートフォリオ分析で可視化できる

「ポートフォリオ分析」は「CS（Customer Satisfaction）ポートフォリオ分析」とも呼ばれる、商品やサービスの顧客満足度を評価する手法です。

縦軸に顧客満足度、横軸に総合満足度への影響の重要度を設定し、グラフを4分割して項目をマッピングしていきます。顧客満足度を可視化した分析結果のグラフは、マーケティングの戦略的な意思決定に役立ちます。

以下に、ポートフォリオ分析の主なメリットをまとめました。

メリットは改善項目の優先度設定

ポートフォリオ分析の代表的なメリットは、自社が改善すべき項目の優先度を設定できることです。

項目ごとに顧客の評価を可視化できるため、どの項目を改善すると顧客満足度の総合評価を上げられるかがわかります。

また、項目ごとの顧客満足度が総合評価に与える影響を把握でき、改善の優先度が高い項目が明確にわかるため、効率的に開発を進められることもポートフォリオ分析のメリットです。

エクセルでポートフォリオ分析をおこなう手順

エクセルを使ったポートフォリオ分析の手順は、以下の通りです。

1. アンケート結果を表にまとめる
2. 集計した数値の相関係数を算出する
3. 満足度・重要度を用いて散布図を作成する

分析の手順を確認し、エクセルを使って効果的なポートフォリオ分析をおこなえるようにしましょう。

アンケート結果を表にまとめる

まず、アンケート結果を数値化してエクセルで表にまとめます。

その際、アンケート結果のどこまでを「満足度」として集計するかを決める必要があります。

例えば、
アンケートを「満足・やや満足・どちらともいえない・やや不満・不満」の5段階評価にした場合、「満足」と「やや満足」までを満足度として集計する、などです。

項目別の満足度は、各項目の回答数を全体数で割ると、項目別の満足度が算出されます。

ほかに、回答の段階別に得点を設定して合計の点数を算出する方法もあります。

集計した数値の相関係数を算出する

集計した数値をもとに、各項目満足度と総合満足度の相関係数を算出します。

相関係数とは、2つの変数の関連性の強さを表す係数です。-1から1までの値で表され、1に近いほど正の相関が、-1に近いほど負の相関が強く、0に近いほど相関は弱くなります。

相関係数を求める際は、ｘとｙの共分散を、ｘとｙの標準偏差の積で割ります。

• 共分散：2つのデータ間の関係を表す。「ｘの偏差×ｙの偏差」の平均で求められる
• 標準偏差：標準的な平均値との差。偏差を二乗し、平方根を計算して求められる
• 偏差：特定の数値と平均値との差。「特定の数値-データの平均値」で求められる

 
このとき、エクセルのデータ分析アドインを使用すると便利ですが、スプレッドシートなどの場合はCORREL関数を用いた方法があります。

CORREL関数は2つのセルの相関係数を求める関数で、次の式を入力して算出可能です。

総合満足度と各項目別満足度の範囲をそれぞれ設定し、重要度となる相関係数を求めましょう。

満足度・重要度を用いて散布図を作成する

満足度と重要度をそれぞれ算出できたら、それらの数値を用いて散布図を作成します。

縦軸を満足度、横軸を重要度（相関係数）に設定し、平均値で縦線と横横線を配置したグラフを用意します。

4分割されたグラフの各エリアには、それぞれ次の特徴があります。

• 第1象限（重要維持項目）：項目別満足度が高く、総合満足度への影響も大きい
• 第2象限（維持項目）：項目別満足度は高いが、総合満足度への影響は小さい
• 第3象限（改善項目）：項目別満足度が低く、総合満足度への影響も小さい
• 第4象限（重点改善項目）：項目別満足度は低いが、総合満足度への影響は大きい

4つの象限について、改善が必要な項目や改善の優先順位を検討し、各象限の施策を立案・改善していきましょう。各象限の概要について、詳しくは以下の記事で解説しています。

Excelの「データ分析」を使った方法

Excelのデータ分析アドインを使うことで、関数を用いるよりも便利にポートフォリオ分析をおこなえます。

「アドイン」とは、エクセルの拡張機能です。基本機能として搭載されているものと、ユーザ自身でインストールをする必要なのあるものが存在します。今回の「データ分析アドイン」は、エクセルの基本機能として利用可能です。

データ分析アドインでは、相関係数を算出するほか、ヒストグラムや移動平均・回帰分析などを含む19種類の分析を簡単に使用できます。

ただし、データ分析アドイン機能はブラウザ版Excel（Office365）やスプレッドシートにはないため、注意しましょう。

「データ分析」を表示させる

まず、Excelでアドインを設定して「データ分析」を表示させましょう。デフォルトの状態ではデータ分析の項目が表示されない場合がありますが、以下の手順で表示できます。

「データ分析」で相関係数を算出する

アドインを設定できたら、以下の手順で相関係数を算出しましょう。

相関係数を算出できたら、項目別の相関係数と満足度を用いて散布図を作成します。

エクセル上で相関係数と満足度を選択して「挿入＞グラフ＞散布図」の順にクリックすると、散布図が表示されます。散布図の縦軸と横軸のラベルは「データラベル＞その他のオプション」で調整可能です。

散布図の完成後に相関係数と満足度の平均線を追加すると、第一象限から第四象限の4エリアで項目を把握できるようになります。

まとめ：エクセルを使ったポートフォリオ分析を始めよう

• ポートフォリオ分析では顧客満足度を可視化できる
• スプレッドシートなどの場合は「CORREL関数」で分析可能
• エクセルの「データ分析」アドインを使うと、なお便利である

ポートフォリオ分析では、縦軸に顧客満足度、横軸に総合満足度への影響の重要度を設定し、グラフを4分割して項目をマッピングし、顧客満足度を可視化できます。

エクセルではデータ分析機能を使うと便利ですが、ブラウザ版のExcel（Office365）やスプレッドシートは非対応であるため、「CORREL関数」を使って相関係数を算出し、満足度を含めた値で散布図を作成するのがおすすめです。

エクセルを使ったポートフォリオ分析で顧客視点での満足度を把握し、顧客満足度の向上につながるマーケティング施策を検討しましょう。

タグ : データ分析 フレームワーク マーケティング戦略