【Excel】最頻値を求める！2番目に多い値や0を除いた最頻値の出し方も解説

エクセルでもっとも多く出現する値（最頻値）を調べる、MODE関数の使い方をご紹介します。同率1位が複数ある場合の対処法、「2番目、3番目に多く出てくる値を抽出する方法」まで解説しているので、ぜひ参考にしてみてください。

最頻値を求める

最頻値を1つだけ抽出する（MODE.SNGL関数）

指定した「数値」の範囲の中で、もっとも多く出現する値を1つだけ抽出します。

例：=MODE.SNGL(A2:A5)

💡 ポイント：

• 最頻値が2つ以上ある場合、最初に出現する最頻値を返します。
• すべての数値が1回しか出現しない（最頻値がない）場合は、エラーになります。

最頻値が「複数」ある場合すべて抽出する（MODE.MULT関数）

指定した「数値」の範囲の中で、もっとも多く出現する値をすべて抽出します。

例：=MODE.MULT(A2:A6)

---

📖 Excel 2019以前の場合：

スピル非対応（Excel 2019以前）の場合は、以下の手順で入力します。

1. 関数を入力する前に、あらかじめ結果の展開先のセル（例：C2:C3）を選択しておきます。
2. 数式を入力したら、Ctrl + Shift + Enter を押して確定します。
3. 余分にセルを選択していた場合、余ったセルには #N/A が表示されます。

「0」を除いた最頻値を求める

FILTER関数を使い「範囲」を「0以外」のデータに絞込み、その結果から最頻値を探します。

例：=MODE.SNGL(FILTER(A2:A6, A2:A6<>0))

---

📖 Excel 2019以前の場合：

以下の数式を入力し、Ctrl + Shift + Enter を押して確定します。

例：=MODE.SNGL(IF(A2:A6=0,"",A2:A6))

「2番目・3番目」に多い値を求める

降順に並び替えて見つける

【手順1】作業列に「出現回数」を出す

1. データの隣の列（B列）に「作業列」を作成します。
2. 以下のように入力し、オートフィルで数式をコピーします。
   • 例：=COUNTIF($A$2:$A$7,A2)

💡 ポイント：COUNTIF関数の「検索範囲」のみ「$」を付けて固定します。これにより、数式を下にコピーしたときに「条件」のみ1つずつ下に移動します。

【手順2】出現回数が多い順に並び替える

1. ランキングを表示させたいセルに、以下のように入力します。
   • 例：=SORT(UNIQUE(A2:B7), 2, -1)

📖 数式の解説：

1. UNIQUE関数： 元データと作業列の範囲から、重複を除いた一意のデータを取り出します。
2. SORT関数：一意のデータ範囲の「2列目」を基準に、「-1（多い順）」に並び替えます。

---

📖 Excel 2019以前の場合：

以下の手順で、フィルター機能を使って降順に並び替えます。

1. 元データと作業列の範囲を選択します。
2. 「データ」タブにある「フィルター」 ボタンをクリックします。
3. 作業列のフィルタアイコン（▼）を開き、「降順」をクリックして並び替えます。

「2番目・3番目」だけピンポイントで抽出する（Excel 2021以降）

【手順】

1. 「降順に並び替えて見つける」の手順で、「作業列」に出現回数を出します。
2. 結果を抽出したいセルに、以下のように入力します。
   • 例：=INDEX(SORT(UNIQUE(A2:B7),2,-1),D2,1)

---

📖 数式の解説：

1. UNIQUE関数： 元データと作業列の範囲から、重複を除いた一意のデータを取り出します。
2. SORT関数：一意のデータ範囲の「2列目」を基準に、「-1（多い順）」に並び替えます。
3. INDEX関数：降順に並び替えられた範囲から、「n行目」のデータを抽出します。

関連記事

• INDEX関数はどういうときに使う？分かりやすく解説！
• SORT関数の使い方！複数条件・空白除外・並べ替えできない時の対処法
• UNIQUE関数とは？使えない場合の代用方法も