2026 年 9 月 12 日、コーチングの会社様から頼まれていたアンケートの集計表を、Excel の数式で計算される形に作り直しました。できあがった表のいくつかの項目で、件数がちょうど 2 倍になっていました。全角と半角の両方の書き方を拾うつもりで、数える条件を 2 本足していたのが原因です。
私が表を開いて、割合が 100% を超えている項目があることに気づきました。数式は確かめてあったはずでした。確かめに使ったソフトが Excel ではなかったので、見落としたのです。この日のうちに、集計は 2 通りの作り方で作り、納品先と同じ Excel に計算させて照らし合わせる、という決まりにしました。
回答が増えても計算される集計表にするため、数式だけの形に作り直しました
前の日に、アンケートの集計とレポートを、回答を読み込んで集計するプログラムで作っていました。そのあとで、私は社内に「今後回答が増えても対応できるように、集計の方法をエクセルの数式で計算されるようにしてほしい。」と頼みました。プログラムで出した数字は、回答が 1 件増えるたびに作り直しになるからです。
作り直した表は、原本の回答のシートを同じファイルに入れ、集計のシートはすべて数式で計算する形です。数式のセルは 2,109 あります。回答の行を足せば計算されるよう、範囲は 5,000 行まで取りました。数え方は次のとおりです。
- 選択式の設問は、選択肢の先頭の番号で数える（「１．」で始まる回答の数など）
- 複数選んでよい設問は、番号が含まれているかで数える
- 7 段階で答える設問は、段階ごとの件数から平均を出す
件数が 2 倍になったのは、全角と半角の条件を 2 本足したからです
回答は「１．月 1 回程度」のように、番号で始まっています。入力のしかたによっては、全角の「１．」と半角の「1.」が混ざっているかもしれない。そう考えて、数式を次のように 2 本足していました。
=COUNTIF(範囲,"１．*")+COUNTIF(範囲,"1.*")
当社の環境の Excel では、全角の「１．」で始まる条件で、半角の「1.」で始まる回答も数えられました。全角と半角を同じ文字として比べていたのです。そのため、同じ回答が 1 本目と 2 本目の両方で数えられ、件数がちょうど 2 倍になりました。Microsoft の COUNTIF 関数の説明には、大文字と小文字を区別しないことが書かれています。全角と半角を同じに扱うことも、同じ種類の落とし穴です。
直し方は、全角の条件 1 本だけにすることでした。Excel では、半角の回答もこの 1 本で数えられます。全角と半角の違いは、用語集の全角と半角にもまとめています。
検算をすり抜けたのは、Excel ではないソフトで確かめていたからです
数式は、作ったあとに確かめていました。Excel と互換のある別の表計算ソフトで計算させ、プログラムで出した数字と照らし合わせて、1,259 のセルが全部一致していました。
その別のソフトは、全角と半角を違う文字として比べます。だから 2 本の条件はそれぞれ別の回答を数え、2 倍にはなりませんでした。お客様が開くのは Excel です。確かめたソフトと、使われるソフトが違っていました。
検算に使った互換のソフト
- 全角の「１．」と半角の「1.」を違う文字として比べる
- 2 本の条件は、それぞれ別の回答を数える
- 件数は正しく出る
- プログラムの数字と全部のセルが一致した
お客様が使う Excel（当社の環境）
- 全角の「１．」と半角の「1.」を同じ文字として比べる
- 2 本の条件が、同じ回答を両方で数える
- 件数がちょうど 2 倍になる
- このソフトでは確かめていなかった
2026 年 9 月 12 日に起きたことの記録です。
その日のうちに、集計は 2 通りで作って Excel で照らし合わせる決まりにしました
私は社内に 2 つの指示を出しました。「2度と起きないようにしてほしい」と、「今後集計を依頼されたら基本的に手計算のものとエクセル関数のもの両方を作り、一致を確認してほしい」です。「手計算のもの」は、人が電卓で計算するものではなく、プログラムで原本から集計し、結果をファイルに残したものを指します。
- プログラムで集計する原本の回答を読み込んで集計し、結果をファイルに残す
- 数式の表を作る原本を参照する数式だけで集計し、セルごとに入るはずの値の一覧も同時に書き出す
- Excel で照らし合わせるExcel に計算させて全部のセルの値を取り出し、入るはずの値と照らす
- 回答を足して、もう一度照らすテストの回答を足したコピーで、件数・平均・割合が正しく増えるかを確かめる
- 食い違い 0 件で納品する1 つでも食い違えば、原因を突き止めてから直す
直したあとの表は、Mac の Excel を動かして計算させ、1,259 のセルが入るはずの値と全部一致することを確かめました。さらに、テストの回答を 2 件足したコピーでも計算させました。回答の数・頻度・所要時間・平均・拠点ごとの比較など 16 の項目が、増えるはずの分だけ増えることを確かめています。
2 つの作り方の結果を 1 セルずつ照らすことを、突き合わせと呼んでいます。人が目で見て「合っている」と言うのではなく、食い違いの件数が 0 だと機械で出すところまでが、この手順の中身です。
Excel で数えるときに、ほかにも気をつけていること
2 倍の件のあと、Excel の数式を書くときに確かめることを一覧にしました。
| 落とし穴 | 起きること | 当社のやり方 |
|---|---|---|
| 全角と半角を区別しない（当社の環境で確かめた） | 両方の条件を足すと、同じセルを 2 回数える | 条件は 1 本にし、Excel で計算させて確かめる |
| 大文字と小文字を区別しない | 「ABC」と「abc」が同じものとして数えられる | 区別したいときは、COUNTIF 以外の数え方にする |
| 数字と、数字の形の文字を同じに扱う | 「1」と文字の「1」が同じものとして数えられる | 数える列の中身の形を、先にそろえる |
| 「*」と「?」は、何にでも当てはまる記号になる | 所属の名前などに含まれていると、別の名前まで数える | 文字として数えるときは、前に「~」を付ける |
| ROUND は 5 を切り上げる | プログラムの丸め方と、0.01 ずれることがある | 集計のプログラムの丸め方を Excel にそろえる |
| 合計してから割るか、平均を平均するか | 丸めの境目で、0.01 ずれることがある | プログラムと数式で、計算の順番をそろえる |
丸め方のずれは、この集計でも 1 か所で起きました。プログラムの言語の標準の丸め方は、ちょうど 5 のときに偶数の側へ丸めます。Excel の ROUND 関数は 5 を切り上げます。集計のプログラムを Excel と同じ丸め方にそろえ、レポートとグラフも作り直しました。表示が変わったのは 1 か所で、小数第 2 位が 1 違っていました。丸め方の違いは、用語集の端数処理に、COUNTIF 関数の使い方はCOUNTIF 関数にまとめています。
数式にしなかったところも、そうと分かるように残しました
回答が増えても自動で計算されない部分が、2 つあります。
- 自由記述の回答をテーマに分ける表: 中身を読んで分けるものなので、数式にできません。回答が増えたら、分け直します
- 拠点ごとの比較の拠点名の列: 新しい拠点が増えたら、拠点名を貼り付けます。右側の数式は入れてあります
あわせて、調査票ごとの選択肢を一覧にして、集計表の中に残しました。数式は選択肢の先頭の番号で数えているので、選択肢の順番が変われば、数式との対応も変わります。数式にした部分と、人が手を入れる部分の境目が分かっていれば、次に集計表を受け取った人も迷いません。
数字の計算式を先に決めておく考え方は、稼働率と納期遵守率の計算式の記事に書きました。呼び名をそろえて数え方を 1 つにする話は、工程の呼び名を分類にまとめた記事にあります。日々の数字を Excel で集めて計算している会社には、入力から集計までを自動にする経営ダッシュボードもあります。月次の集計のように、毎月くり返す作業を AI に任せる進め方はAI伴走支援のページにあります。
