NPSをExcelで計算する方法｜空欄・無効回答の確認まで

NPSはExcelで回答を3群に分け、（推奨者数−批判者数）÷有効回答数×100で計算できます。先に空欄・文字・0〜10の範囲外を区別すると、分母の取り違えを見つけやすくなります。

NPSとは、0〜10点の推奨意向への回答をもとに、推奨者の割合から批判者の割合を引いた指標です。

本記事では、回答者1人につき1件の点数が並ぶデータを集計します。カスタマーサクセスの調査で、集計結果を報告する担当者向けの手順です。

無料のNPS計算Excelテンプレート

NPS計算テンプレートをダウンロード（Excel）

入力欄はA2:A1001、分類はB列、集計結果はE4:E9にあります。最初に入っているのは架空の動作確認用データで、有効回答10件、要確認5件、NPS 0.0です。実データを入れる前にA列のサンプルを消してください。

1. A2:A1001の内容だけを削除します。行全体の削除やB列の数式の削除はしません。
2. アンケートの点数を、A2から値として貼り付けます。上限は1,000件です。氏名やメールアドレスは不要です。
3. B列の「要確認」を探し、元データと照合します。確認できない値を、勝手に0点や数値へ置き換えません。
4. E7の有効回答数、E8の要確認件数、E9のNPSを確認します。要確認が残る場合は、除外理由を記録し、集計対象を確定してから報告します。
5. 「集計対象・期間」の欄に対象顧客と調査期間を記入し、調査ごとに別名で保存します。

Excelの計算設定が手動の場合は、入力後に再計算してください。1,000件を超えるデータは、このテンプレートの集計範囲外です。自作の表へ移す場合は、分類式の行とCOUNTIFの集計範囲を同じ最終行まで延ばします。

回答の分類と計算式

分類と計算の基本は、開発元Bain & CompanyのMeasuring Your Net Promoter Scoreに基づきます。

後ろの2行は、このテンプレートでデータを確認するための扱いです。例えば文字で保存された「9」は、回答者の意図が無効とは限りません。元データを確認し、転記・取込時の形式の問題なら数値へ直します。

B2：数値・範囲・整数を確認して分類する

自分で作る場合、A1を「回答点数」、B1を「分類」とし、B2に次の式を入れてB1001までコピーします。

=IFERROR(IF(ISNUMBER(A2),IF(AND(A2>=0,A2<=10,MOD(A2,1)=0),IF(A2>=9,"推奨者",IF(A2>=7,"中立者","批判者")),"要確認"),IF(A2="","未回答","要確認")),"要確認")

式は、数値なら0〜10の整数かを確認し、該当する3群へ振り分けます。数値以外は空欄・空文字と、それ以外を区別し、セルにエラーが入っていれば「要確認」とします。

MicrosoftのIS関数の説明では、ISNUMBERは文字の数字を数値へ変換せず判定します。この性質を使い、取り込み形式の違いを見つけます。

E4〜E9：人数とNPSを計算する

有効回答が0件ならNPSは空欄です。0件とNPS 0は別の状態で、NPS 0は推奨者と批判者の人数が同じ場合などに出ます。小数第1位まで示す場合は、E9の表示形式を数値の0.0にします。途中の割合は丸めません。

計算例：中立者を分母に入れる

点数が「10、9、9、8、7、7、8、6、5、0」の10件なら、推奨者3人、中立者4人、批判者3人です。NPSは（3−3）÷10×100＝0となります。

一方、推奨者80人、中立者30人、批判者40人なら、有効回答は150人で、NPSは（80−40）÷150×100＝26.666…、小数第1位表示では26.7です。推奨者割合53.333…%と批判者割合26.666…%を先に整数へ丸めると、53−27＝26となり、計算がずれます。

よくある集計ミスと確認箇所

COUNTAの件数をそのまま分母にすると、「未回答」と書かれた文字セルなども混ざる可能性があります。分類後の推奨者・中立者・批判者の合計を分母にします。

0点を未回答にすると、批判者を取りこぼします。空欄は除外しても、0点は回答です。配布テンプレートでは0点を含む例で違いを確認できます。

フィルターで非表示にすれば集計から外れると思うのも注意点です。この記事のCOUNTIFは指定範囲を数えるため、画面で隠した行も対象です。部署別・期間別に出すなら、対象データだけを別のファイルへ抽出して集計し、件数を照合します。

重複回答や調査対象の違いは、この式では検出できません。同じ人の複数回答をどう扱うかを先に決め、アンケート側の回答IDなどで確認します。テンプレートには点数だけを渡してください。

集計後の報告に何を添える？

数値だけを送らず、対象、期間、有効回答数、除外した件数と理由を添えます。例えば架空の調査なら、次のように記載できます。

9月調査の有効回答は150人。推奨者80人、中立者30人、批判者40人で、NPSは26.7。未回答5件を除外した。対象は契約中の利用担当者で、1人1回答として集計した。

前回との比較では、対象顧客・質問・回収方法がそろっているかを確認します。得点だけで解約率の増減を説明せず、自由記述や問い合わせ、ヘルススコアと合わせて、確認する顧客や改善テーマを決めます。