メインコンテンツへスキップ
見出し画像

アンケートの空欄を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はこちらです。よかったら、のぞいてみてください。

    画像

    https://lin.ee/UqFmzfB

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

     
     
    \初心者OK!自動化・効率化できるExcel教室/ ◇AI×Excelの仕事時短術で自分の時間爆増! ◇自分だけの自動化ツールがつくれる! ◆元PC平凡▶半年で職場のExcel先生 ◆AIを使った自動化ツール作成数100超

    あなたへのおすすめ