
[EXCEL] AVEREGEIF関数で男女別平均点を出してはいけない! SなしのIF関数を使ってはいけない ~乗っかり記事~
【まとめ】
・惰性で集計してはいけない。例年出しているから、ではダメ。
・それでも出さなきゃいけない集計は自動化しておく。
・条件付きの平均は、AVEREGEIF関数(単一条件のみ集計可)を使ってはいけない。AVEREGEIFS関数(単一・複数条件いずれも集計可)を使う。
・SUM/COUNT/AVEREGE のいずれも、「Sなし」のIF系関数は使わない。「S付き」のIFS系関数を使う。
【説明】
こんな記事を拝見しました。
【Excel】もう関数で悩まない!テストの男女別平均点を「Averageif関数」でもっとスマートに求める方法
教育関係の方のサイト。
試験の男女別平均点をaverageif関数で出す方法を紹介しつつ、「男女別で平均点」を出すことへの疑問を投げかけているものです。
「男女別平均点」は、これまで出していたから、という理由で毎年出しているようです(しかも、トンデモ数式で、それなりの労力=時間をかけて)。
その集計、意味ないからやめませんか、というもの。
同感です。
その集計、意味ある?
「お役所」でも、毎年毎年、色々な集計表を作りますが、それ、なににつかわれるの?という感じ。
施策(せさく)や予算に結びつくものもありますが、大抵は、議会や委員会や会議で「どうなっている?」と聞かれたときに答えられるように、というもの。
「〇〇の状況は△△と承知しています」と言えるように。
勿論それはそれで重要なのですが、そのために、かなりの労力が割かれています。
100準備して、聞かれるのは1~2みたいな。
(しかも、ここ3年ぐらいの数値だけ。それだと、過去からの変化の移り変わりはほぼ分からない。)
とはいえ、やっぱり準備はしておかないといけない。
「お役所」では「承知している」ということが重要なんですよね。
ますは「現状認識から」というか(そこで終わっている例も多々あるでしょうけど)。
やっぱり集計はしないといけない。
ならば集計はするとしても、その労力は最小限にしたいものです。
というわけで、話が戻ります。
「お役所」(私の経験)では、平均点はあまり出しませんが、学校なら試験結果で多用するデータでしょう。平均との比較は重要ですから。
男女別の比較が全く無意味だとは思いません。
有意な差があるなら、その理由と対策を求める必要もあるでしょう。
あるいは、男女差だけでなく、出身校別とか、地域別とか、部活別とか、通学時間別、自宅での学習時間別とか、朝ごはんの有無別とか(データが取れるか否かはともかく)。
あるいは、通塾の有無別とか、通学時間別とか、家庭環境別(ひとり親)とか。
後段はかなりセンシティブな内容なので、相当の留意が必要ですが、何かの要因によって学力に差が出るのだとしたら、その解決を図る必要は検討すべきかもしれません(そんなに簡単にはいかないでしょうけど)。
そういった場合、要因は一つだけ(例:男女別)とは限りません。
複数の要因が絡み合っているかもしれません。
それをどう把握するか?
Averageif関数を使っていけない
平均点についていえば、「Sなし」のAverageif関数では複数の要素(条件)を掛け合わせた集計は出来ません。
複数の条件で算出するには、Averageifs関数を使います。
「SつきのIF系関数」です。SUMIFSやCOUNTIFSと同類。
私は、そもそもAverage関数自体を使わない(=平均を出すことがない)のでノーマークでしたが、この「S」なしのAverageif関数も曲者(くせもの)です。使ってはいけません。
なぜか?
それは、「S付き」の「Averageifs関数は、条件が1つでも使えるから。
そして何より、「Sなし」のAverageif関数と「S付き」のAverageifs関数では記述方法が違うから、です。
=AVERAGEIF(条件範囲, 条件,平均を出す数値の範囲)
=AVERAGEIFS(平均を出す数値の範囲, 条件の範囲1, 条件1, [条件範囲2, 条件2], ...)
どうでしょうか? これ、SUMIFとSUMIFSの関係と同じです。
例えばこんな表があったとして・・・

またエクセルの行・列と混同しないよう
「甲乙丙」という渋い選択肢にしています
「〇〇」に何が入るかは内容次第です。出身校でもいいし、地域でもいい。通塾の有無だったら、あり・なし・不明 の三択でしょうか。
これ、男女別の集計を出すなら以下の通りになります。
手作業なら結構手間ですが、数式を入れれば一瞬です。

参照しているデータは同じです。

当然、結果は同じですが、カッコ内の記述が違います。
=AVERAGEIF($C:$C,H2,$E:$E)
=AVERAGEIFS($E:$E,$C:$C,H2)
「Sなし」のAVERAGEIFは、最初に条件の範囲(男か女か)を入れて、次に条件(男)を入れ、最後に集計する数値(点数)の範囲を指定します。
一方、「S付き」のAVERAGEIFS関数は、最初に集計する数値(点数)の範囲を入れ、次に条件の範囲(男か女か)を入れ、最後に条件(男)を入れます。条件が複数の場合は、更に「条件の範囲、条件」を追加していきます。
これ、何が問題かというと、「Sなし」のAVERAGEIF関数を使って単一条件を出した後に「条件を追加したい」となった場合、一から数式を入れ直さないといけないんです。
しかも、既述の順番が違うので、混乱します。
「Sあり」関数は後から出来たため、以前からの「Sなし」関数と同じだと使い勝手が悪いので記述を変えざる得なかった、という理由は分かりますが、こういう関数を見ると、ついつい文句を言いたくなります。
従って、条件が1つであっても、常に「S付き」のAVERAGEIFS関数を使うのが正解です。覚える関数は少ない方がいいですから。
無理して2つ覚えても混乱するだけです。
「Sありなら、最初に数値を指定する」と覚えれば済みます(SUMIFSも)。
そのうえで条件が増えたら、条件を追加するだけ。
上の例でしたら、こんな感じ(右下図)。

当然、参照データは増えてます。
=AVERAGEIFS($G:$G,$C:$C,J$8,$E:$E,$I11)

このように、条件が1つでも複数でも、簡単に出せます。
従って、合計(SUMIFS)でも件数(COUNTIFS)でも平均(AVERAGEIFS)でも、単一条件だろうが複数条件だろうが、「Sあり」の関数を使うべきです。
SUMIF関数とSUMIFS関数を並べて使い方を説明しているサイトを散見しますが、「意味ないこと教えるなよ。初心者を惑わすなよ」って思ってしまいます。
この辺り、以下の記事で書いていますが、これに「AVERAGEIF」もついかしないと・・・(MAXIF・MINIFは、さすがにいいかな)。
[EXCEL]使ってはいけない関数3選 ①VLOOKUP、②SUMIF、③COUNTIF 初心者向けエクセル情報を鵜呑みにするなかれ
以上、乗っかり記事でした。
話は戻りますが、上はあくまで集計用の例。
「男女別の平均を出しましょう」という意味ではありません。
注1:COUNTIFとCOUNTIFSは、既述順は同じ(範囲⇒条件)ですが、「S付き」でも単一条件の集計が出来るので「Sなし」を使う意味はありません。
注2 「S付き」関数はEXCEL2007より前では使えません(もう2007を使ている人はいないと思いますが・・・)
注3 複数の項目をクロス集計して有意な結果が出るか調べるには、上の方法より、ピボットテーブルを使って色々試す方がいいでしょう。
上の数式は、集計方法が固まっている場合の作表に使い、結果を「結果一覧」シートに流し込んだら、別シートの「集計」シートに即座に集計表が出来上がっている、というのが理想です。
注4 実際の試験では、上から下に名前、左から右へ科目ごとの点数が並ぶかと思いますので、数式はもっと複雑になることでしょう。
なお、本当は、テストの結果は別にして、「お役所は平均を出してはいけない」という記事を書こう書こうと思いつつ(本記事とは観点は全く別)、昼休みに見た冒頭記事に触発され、勢いでこの記事を書いてしまいました(構想は昼寝をしつつ練り、夕食後の1時間半で書き上げました)。
これについては、またいつか記事にします。
なお、Average関数を使ってはいけないと書いていますが、冒頭の記事主さんを批判する意図は全くありません。
むしろ、いい気付きの機会をいただけたと感謝しています。
個々のところ低調でしたが、少し調子が戻ってきた気がします。
では、おやすみなさい。