【関数】特定条件に一致する数値の「中央値」・「最頻値」を計算できる「MEDIAN」・「MODE」と「IF」の組み合わせテクニック

Aさん

商品別や日別などの条件で中央値や最頻値を集計したいですが、MEDIANやMODEだと限界あります。。
こうした場合の集計に役立つ関数はありますか？

森田

その場合、MEDIANまたはMODEを「IF」と組み合わせ、配列数式にすることで対応できますよ！
では、具体的に解説していきますね。

はじめに

この記事は配列数式の概要とMEDIAN・MODE・IFの使い方を把握していることが前提です。

Excelには○○別の「中央値」と「最頻値」を集計するための関数がない

実務では、データの特徴や傾向を把握するために、商品別や日別などの切り口で集計する機会は非常に多いもの。

こうした条件別の中央値/最頻値を集計する際、商品名や日付などの条件に一致するレコードの数値データのみで中央値/最頻値を計算すれば良いです。

頑張れば、次のようにMEDIAN（またはMODE）でも集計は可能です。

ただし、指定するセル数が多ければ多いほど、手作業は増え、誤ったセルを選択するリスクも増えてしまうため、この方法は非効率です。

通常、他の集計方法（合計や個数等）であれば、条件に一致するレコードのみを対象として集計できるSUMIFSやCOUNTIFSといった関数が用意されています。

しかし、中央値と最頻値については、こうした関数がありません。

よって、条件別の中央値/最頻値を集計するために、MEDIANまたはMODEを「IF」と組み合わせ、配列数式にする必要があります。

【参考】Excel2016以前のバージョンは○○別の「最大値」と「最小値」を集計する関数もない

本記事の内容は中央値と最頻値に絞っていますが、Excel2016以前のバージョンの「最大値」と「最小値」も同様です。

Excel2016以前で条件別の最大値/最小値を集計したい場合は、本記事のテクニックを応用してください（MEDIANの部分をMAXやMINへ置き換え）。

なお、Excel2019以降やMicrosoft365のバージョンであれば、条件別の最大値/最小値を集計できる「MAXIFS」・「MINIFS」が登場しています。

よって、MAXIFS・MINIFSが使える環境であれば、こちらの関数を使った方が便利です。

配列数式でMEDIAN・MODEとIFを組み合わせれば、◯◯別の「中央値」・「最頻値」の集計が可能！

MEDIAN（MODE）+IFの配列数式にしたイメージは以下の通りです。

これで、条件別の中央値/最頻値の集計が可能となります。

今回は受注月別の中央値/最頻値を集計しました。

ちなみに、この数式の意味は次の通りです（例：MEDIAN）。

{=MEDIAN(IF(条件範囲=条件,計算対象範囲)}

IFの引数「値が偽の場合の値」は省略。

【参考】MEDIAN+IF・MODE+IFの数式を使い回すコツ

今回の配列数式は、1つの数式内で複数の計算/処理をまとめて行っています。

よって、複数セルへ数式を使い回す際は、通常の関数と同じように参照形式（絶対参照/相対参照）を設定しましょう。

ちなみに、今回は次の参照形式にしています。

元データがテーブルの場合、条件範囲・計算対象範囲は構造化参照になるため、参照形式の指定は不要。

【参考】2種類以上の条件で集計したい場合は

MEDIAN（MODE）+IFの配列数式を2種類以上の複数条件で集計したい場合は、次のような数式となります。

今回は、以下の2種類のAND条件に一致する中央値/最頻値を集計しました。
9月の商品名「いちご」は元データで1レコードのみのため、最頻値はエラー値「#N/A」を表示。

ちなみに、複数条件の数式の意味は次の通りです（例：MEDIAN）。

{=MEDIAN(IF((条件範囲1=条件1)*(条件範囲2=条件2),計算対象範囲)}

3種類以上の条件にしたい場合は、「条件範囲n=条件n」のセットをカッコ()で囲み、条件の間をアスタリスク（*）でつないでいけばOKです。
アスタリスク（*）はAND条件（~かつ）を意味しますが、OR条件にする場合は「+」へ置き換え。

サンプルファイルで練習しよう！

可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。

サンプルファイル_ワークシート関数_MEDIAN・MODE+IF（配列数式）

サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります（上記リンクから登録フォームへ遷移します）。

ブックを開いたら、次の手順を実施してください。
この手順は中央値のみ。

本記事の解説と同じ結果になればOKです！

後は最頻値の部分も同じ要領で行いましょう（MEDIANの部分をMODEに置き換える）。

さいごに

いかがでしたでしょうか？

MEDIAN・MODE+IF（配列数式）の組み合わせテクニックは、条件別の中央値/最頻値の集計に役立ちます。

頻度は高くはないですが、条件別に中央値/最頻値を集計する機会があれば、この機会に試してみてください。

なお、MEDIAN・MODE+IF（配列数式）以外にもExcelでのデータ集計の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。

Excel関数をフルに使って データの整形・集計・可視化の自動化を極める本

created by Rinker

ソシム

¥2,640 (2024/04/28 00:50:14時点 Amazon調べ-詳細)

• Kindle
• Amazon
• 楽天市場
• Yahooショッピング

ピボットテーブルも関数もぜんぶ使う！Excelでできるデータの集計・分析を極めるための本

created by Rinker

¥2,399 (2024/04/28 18:13:11時点 Amazon調べ-詳細)

• Kindle
• Amazon
• 楽天市場
• Yahooショッピング

ご参考になれば幸いですm(_ _)m

森田

私は、組織で使用するブックでは配列数式をなるべく使わないようにしていますが、条件別の中央値/最頻値を集計する場合だけは仕方なく使っています。
Excelのバージョンアップで標準的な関数として追加されることを期待したいですね。