
アンケートの空欄を0のまま平均すると満足度が下がる。AIに空欄と0を分けて数えてもらうと、未回答を除いた本当の値が出ます
「満足度、思ったより低いね」。上司にそう言われて、アンケートの集計表をもう一度開きました。
5段階でつけてもらった満足度の平均が、どうも実感より低いんですよね。回答を見返しても、低い点をつけた人はそんなにいません。
原因は、空欄と0でした。まだ答えていない人のマスは空欄のまま。1人は、満足度の欄に「0」と入れていました。=AVERAGE は、この空欄と0を別々に扱います。これを知らないまま平均を出すと、数字が静かに下がるんです。
直し方はシンプルでした。空欄と0を平均に入れず、1〜5の有効な回答だけで数える。この式をAIに書いてもらったら、本当の平均が戻ってきました。
この記事で分かること。
✅ AVERAGEで平均を出すと、空欄は無視されるのに0だけ足されて平均が下がる理由
✅ AIに頼む、1〜5の有効回答だけで平均して回答率も出す式(コピーで使えます)
✅ 分けて数えたあと、取りこぼしが無いか確かめる方法
使うのは、説明用に作った架空の満足度アンケートです。実在の調査や個人の回答ではありません。

📊 空欄・0・文字が、1つの列に混ざっています
上の表を見てください。満足度は1〜5でつけてもらう約束です。でも、R03さんとR10さんのマスは空欄。R04さんは「0」。R06さんは「未回答」という文字を入れていました。
12人に配って、ちゃんと1〜5で答えてくれたのは8人。残りの4人は、それぞれちがうかたちで「まだ答えていない」状態なんですよね。
ここで =AVERAGE(B2:B13) と平均を出すと、どうなるか。

📉 空欄は無視するのに、0は足してしまいます
返ってきた平均は3.33。数えた件数は9件でした。
ここがつまずきどころなんです。AVERAGE は、空欄は自動で飛ばします。でも0は「0点」という立派な数字として、合計にも件数にも入れてしまう。有効な回答は8件なのに、0を足した9件で割っていました。
満足度1〜5の世界に、0は本来いません。その0が1件混じるだけで、平均は下へ引っぱられます。赤いエラーは出ません。もっともらしい数字が、そのまま報告に乗ってしまう。ここが、一番こわいところです。
🛠️ AIに「空欄と0を外して平均したい」と頼みます
1〜5の有効な回答だけで平均する。この作り方を、AIにそのまま相談しました。返ってきたのがこれです。

教えてくれたのは、3本の式でした。
平均: =AVERAGEIFS(B2:B13,B2:B13,">=1",B2:B13,"<=5")
有効回答数:=COUNTIFS(B2:B13,">=1",B2:B13,"<=5")
回答率: =COUNTIFS(B2:B13,">=1",B2:B13,"<=5")/COUNTA(A2:A13)
やっていることは、">=1" と "<=5" の2つの条件で、1以上5以下の数値だけを対象にすること。それで、空欄も0も「未回答」の文字も、ぜんぶ計算から外れます。
回答率は、有効な回答数を回答者の人数で割った値。COUNTA(A2:A13) がA列の回答者IDを数えて、分母にしてくれます。
この3本を入れた結果が、こうなりました。

✅ 本当の平均は3.75、回答率は66.7%でした
1〜5の有効な回答で数えた平均は3.75。さっきの3.33より、はっきり上がりました。0を外しただけで、こんなに変わるんですよね。
あわせて分かったのが、有効な回答は8件で、回答率は66.7%ということ。「平均3.75」の後ろに「12人中8人が答えた」が見えると、数字の重みも伝わります。
⚠️ 空欄と0をどう扱うかは、式では決められません
ここで一度、立ち止まります。AI自身が、大事な注意も教えてくれました。

Excelに分かるのは、マスが空欄か0かだけ。その中身が何を意味するかは、アンケートの作り方で決まります。
⚠️ 空欄は「未回答だから外す」のか、「答えなかった人も不満とみなして0点」にするのか
⚠️ 0は「本物の最低評価」なのか、ただの誤入力なのか
たとえば満足度が0〜10の調査なら、0は本物の最低点です。1〜5の調査なら、0は選択肢に無いので、誤入力か、未回答を0と書いたものなんです。
同じ0でも、意味はアンケート次第。だから「0を外す」と決めるのは、調査の中身を知っている人の仕事になります。式は、決めたことを実行してくれるだけ。ここは人が握っておくところですね。
🔍 分けたら、取りこぼしが無いか確かめます
最後に答え合わせです。確かめ方も聞いておきました。

コツは、有効・空欄・その他(0や文字)を、それぞれ別々に数えること。引き算で出すと、必ず総数に合ってしまって確認になりません。1つずつ数えて、足し上げます。

有効が8、空欄が2、0が1件、文字が1件。足すと12。回答者の総数も12。ぴたりと一致しました。これなら、どこかの回答を数え落としていないと言い切れます。
もしこの合計が総数とずれていたら、6や7のような範囲外の数字が紛れているサインですね。
📈 回答が増えても、集計は追いつきます
アンケートは、あとから回答が届きます。試しに、満足度5の人を1人足してみました。

式の範囲を少し広めに取ってあるので、手で直すところはありません。平均は3.75から3.89へ。有効回答は8から9、回答率も66.7%から69.2%へ、自動で動きました。
💡 そのまま使える、頼み方のテンプレ
自分のアンケートでやるときは、こう頼むと早いです。
満足度1〜5のアンケートがB2:B13にあります。
空欄・0・範囲外の数値・文字は平均に入れず、
1〜5の有効な回答だけで平均したいです。
有効回答数と回答率も出す式を、そのまま使える形で教えてください。
伝えるのは3つ。範囲(B2:B13)と、外したいもの(空欄・0・範囲外)と、ほしい結果(平均・有効回答数・回答率)。ここさえ渡せば、式はAIが組んでくれます。
平均が実感とずれていたら、まず空欄と0を疑う。1〜5の回答で数え直して、最後に有効・空欄・その他を足して総数と合わせる。
この順番を通すと、やっと信じられる1枚になるんですよね。
🛠️ 自分の表でも同じことをしたいけれど、作る時間が無い。そういう時は、表をそのまま預かって組んでいます。
作ったものは全部ここにまとめてあります。
📩 LINEはこちらです。よかったら、のぞいてみてください。

#Excel #仕事効率化 #AI活用 #ChatGPT #アンケート集計 #データ分析 #AVERAGE #関数 #業務効率化 #Excel関数