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

[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関数を使ってはいけないと書いていますが、冒頭の記事主さんを批判する意図は全くありません。
    むしろ、いい気付きの機会をいただけたと感謝しています。

    個々のところ低調でしたが、少し調子が戻ってきた気がします。
    では、おやすみなさい。




    あなたへのおすすめ