概要
「この条件のとき、一番よく出てくる値（最頻値）は何？」
「東京支店で一番多い単価は？」
「田中さんが一番よく取ってくる売上パターンはいくらくらい？」
こういう「よく出てくる値＝最頻値（さいひんち）」を、条件付きで出したいときに使えるのが
MODE（または MODE.SNGL）＋ FILTER の組み合わせです。
SUMIFS や AVERAGEIFS みたいに「条件付き最頻値」の専用関数はないので、
- FILTER で「条件に合う行だけ」を抜き出す
- 抜き出した数値に対して MODE で最頻値を求める
という二段構えで実現します。
MODE（最頻値）の基本
MODE の役割
MODE は「最頻値」、つまり「一番よく出てくる値」を返す関数です。
書式は次のようになります。
=MODE(数値1, [数値2], …)
または範囲をそのまま渡すこともできます。
=MODE(B2:B100)
Excel の新しいバージョンでは、基本的に
=MODE.SNGL(B2:B100)
と書くのが推奨です（動きは「一番頻度が高い値を1つ返す」で同じ、と考えてOKです）。
例えば、値が
10, 20, 20, 30, 30, 30, 40
なら、30 が最頻値なので MODE は 30 を返します。
FILTER の基本
条件に合う行だけを抜き出す
FILTER は「条件を満たす行だけを取り出す」関数です。
基本形は次の通りです。
=FILTER(抽出したい範囲, 条件式)
例えば、A2:A100 に支店名、B2:B100 に売上があるとして、
「支店が東京の売上だけ」を抜き出したければこうなります。
=FILTER(B2:B100, A2:A100="東京")
この FILTER の結果は、「東京支店の売上だけが並んだ仮想の一覧」になります。
この一覧に MODE をかけることで、「東京支店で一番よく出る売上」を求められます。
条件付き最頻値の基本形（MODE＋FILTER）
支店が東京の売上の最頻値
前提の表イメージ
A列：支店名
B列：売上
「東京支店の売上で、一番よく出てくる金額（最頻値）」を求める式はこうなります。
=MODE.SNGL(
FILTER(B2:B100, A2:A100="東京")
)
中で起きていることは次の通りです。
まず FILTER が「A列が東京の行に対応する B列の値だけ」を取り出す。
次に MODE.SNGL が、その抽出された値の中で一番頻度の高い数値（最頻値）を返す。
このように、「条件で絞り込む」部分を FILTER に、
「最頻値を求める」部分を MODE に担当させています。
複数条件付き最頻値（AND 条件）
担当者＝田中 かつ 支店＝東京 の売上最頻値
前提の表イメージ
A列：担当者
B列：支店
C列：売上
「担当者が田中」「支店が東京」の両方を満たす行だけを対象に、その売上の最頻値を求めたいとします。
式は次のようになります。
=MODE.SNGL(
FILTER(
C2:C100,
(A2:A100="田中") * (B2:B100="東京")
)
)
ここで重要なのは FILTER の第2引数、条件式の部分です。
A2:A100=”田中” は TRUE/FALSE の並び。
B2:B100=”東京” も TRUE/FALSE の並び。
この 2 つを掛け算すると「両方 TRUE のところだけ 1（TRUE）」になり、
結果として「担当者が田中 かつ 支店が東京」の行だけが通過します。
その行に対応する C 列（売上）を MODE.SNGL に渡すことで、条件付き最頻値が求まります。
数値条件を組み合わせた最頻値
売上が 100,000 以上の行だけの最頻値
B列：売上
「売上が 100,000 以上の行だけを対象に、その売上の最頻値」を求めたい場合はこうです。
=MODE.SNGL(
FILTER(B2:B100, B2:B100>=100000)
)
B2:B100>=100000 が TRUE になった行だけが FILTER で抽出され、
その結果に MODE.SNGL をかけて最頻値を求めています。
担当者＝田中 かつ 売上が 100,000 以上 の最頻値
A列：担当者
B列：売上
「担当者が田中で、かつ売上が 100,000 以上」という複数条件の例です。
=MODE.SNGL(
FILTER(
B2:B100,
(A2:A100="田中") * (B2:B100>=100000)
)
)
掛け算で AND 条件を表現している点は、先ほどの例と同じです。
セル参照で条件を変えられる形にする
条件をセルから指定したい場合
A列：担当者
B列：売上
E2：担当者名（条件を入力するセル）
「E2 に入力された担当者の売上の最頻値」を知りたいときは、次のように書けます。
=MODE.SNGL(
FILTER(B2:B100, A2:A100=E2)
)
E2 の値を「田中」「佐藤」などに変えるだけで、その担当者がよく出す売上パターンを瞬時に見られます。
数値のしきい値もセルから指定する
B列：売上
E2：しきい値（例：100000）
「売上が E2 以上の中で一番よく出てくる金額」を求める場合はこうです。
=MODE.SNGL(
FILTER(B2:B100, B2:B100>=E2)
)
しきい値を式に埋め込まず、セル参照にしておくことで、条件をあとから自由に変えられます。
MODE＋FILTER を使うときの注意点
該当データがないときはエラーになる
FILTER は「条件を満たすデータが1件もない」とエラー（#CALC!）になります。
それを MODE.SNGL に渡すと当然エラーになるので、実務では IFERROR と組み合わせるのがおすすめです。
例として、東京支店の売上最頻値を出しつつ、該当がないときは「該当なし」と表示するにはこうします。
=IFERROR(
MODE.SNGL(FILTER(B2:B100, A2:A100="東京")),
"該当なし"
)
複数の最頻値がある場合
最頻値が複数ある（たとえば 10 と 20 が同じ回数で最頻）場合、
MODE.SNGL は「最初に見つかった最頻値」を返します。
「すべての最頻値を列挙したい」となると、別のテクニックが必要になりますが、
「代表的な1つ」が分かればよい実務では MODE.SNGL で十分なことが多いです。
FILTER が使えない古いバージョン
MODE＋FILTER は「動的配列対応の Excel（Microsoft 365 など）」が前提です。
古いバージョンでは FILTER 自体がないため、このやり方はそのまま使えません。
その場合は、別の列に「条件に合うときだけその値を出す IF 式」を書き、
その列に対して MODE をかける、といった工夫が必要です。
例題
問題1
A2:A100 に支店名、B2:B100 に売上が入っています。
「支店が東京」の売上の最頻値（最もよく出てくる金額）を求める MODE.SNGL＋FILTER の式を書いてください。
=MODE.SNGL(FILTER(B2:B100, A2:A100="東京"))
問題2
A2:A100 に担当者名、B2:B100 に売上が入っています。
「担当者が田中」の売上の最頻値を求める式を書いてください。
=MODE.SNGL(FILTER(B2:B100, A2:A100="田中"))
問題3
B2:B100 に売上が入っています。
「売上が 100,000 以上」の行だけを対象に、その売上の最頻値を求める式を書いてください。
=MODE.SNGL(FILTER(B2:B100, B2:B100>=100000))
問題4
A2:A100 に担当者名、B2:B100 に売上が入っています。
「担当者が田中 かつ 売上が 100,000 以上」の売上の最頻値を求める式を書いてください。
=MODE.SNGL(
FILTER(
B2:B100,
(A2:A100="田中") * (B2:B100>=100000)
)
)
問題5
A2:A100 に担当者名、B2:B100 に売上が入っています。
E2 に担当者名が入力されているとき、
「E2 の担当者の売上最頻値」を求め、該当データがない場合は「該当なし」と表示する式を書いてください。
=IFERROR(
MODE.SNGL(FILTER(B2:B100, A2:A100=E2)),
"該当なし"
)
まとめ
条件付き最頻値には専用関数がないため、
条件に合うデータを FILTER で抜き出す
抜き出した値の最頻値を MODE.SNGL で求める
という形で実現します。
型としては、次の 2 パターンを覚えておくと実務でかなり使えます。
=MODE.SNGL(FILTER(数値範囲, 条件式))
=IFERROR(
MODE.SNGL(FILTER(数値範囲, 条件式)),
"該当なし"
)
「よく出てくる値」を条件付きで見に行くと、
単なる平均や合計とは違う“その人らしさ・その支店らしさ”が見えてきます。
ぜひ、自分のデータで一度試してみてください。