はじめに：「2番目」って、どっちの「2番目」？
「この表の中で、2番目に大きい数字は何？」
そう聞かれたら、皆さんはどう答えますか？
通常なら =LARGE(範囲, 2) で一発終了です。
何のひねりもありません。
しかし、もしデータに重複があったらどうでしょう？
例えば、テストの点数が {100点, 100点, 90点…} だった場合。
さて、「2番目に大きい点数」は、100点（2人目の1位）でしょうか？
それとも90点（次の順位）でしょうか？
今回は、この「2番目」という曖昧な概念に、LARGE関数を一切使わずに 15種類ものアプローチで挑みます！
【今回のターゲット（A1:D3）】
={6,7,22,46;49,38,10,31;46,3,32,49}
このデータには、最大値「49」が2つ、その次の「46」も2つ含まれています。
この状況下で、以下の3つのパターンに分けて攻略していきます。
- 重複がない世界での2番目（理論上のロジック）
- 重複あり：LARGE関数と同じ挙動（答えは 49）
- 重複無視：ユニークな2番目（答えは 46）
さあ、あなたはどの数式がお好みですか？
本記事では、無料のWeb版Excelを使用して検証および画像の作成を行っています。Windowsはもちろん、MacやLinuxの方もブラウザさえあれば挑戦できます！
パターン1：重複がない前提のロジック
まずは準備運動です。
重複があると計算がややこしくなるロジックを紹介するため、一時的にデータを以下のように変更して考えます。
（最大値と2番目が重複しないデータです）
={6,7,22,46;49,38,10,31;0,3,32,0}
このデータなら、最大値は49、2番目は46です。
数式1：SUM & COUNTIF
=SUM(IF(COUNTIF(A1:D3,">="&A1:D3)=2,A1:D3))
解説：
まず、「自分自身以上の数値が、全体で何個あるか？」を数えます。
1番大きい数なら「1個（自分だけ）」、
2番目に大きい数なら「2個（自分と1位）」になりますよね。
続いて、COUNTIFで順位を判定し、ちょうど「2」になる数値だけをSUMで合計しています。
（※重複がない前提なので合計しても大丈夫！）
数式2：MAX & COUNTIF
=MAX(IF(COUNTIF(A1:D3,">"&A1:D3)=1,A1:D3))
解説：
こちらは「自分より大きい数値（>）が1個だけある」ものを探します。
つまり、自分より上が1人しかいない＝自分は2位、ということですね。
数式3：RANK関数
=SUM((A1:D3)*(RANK(A1:D3,A1:D3)=2))
解説：
その名の通り順位をつけるRANK関数を使います。
配列内で順位が「2」である値を抽出し、合計しています。
重複があると「同率1位」などが発生してランクがズレるため、ユニークデータ専用の技です。
数式4：MMULT（総当たり戦）
=LET(a,TOCOL(A1:D3),r,MMULT((a<TOROW(a))*1,SEQUENCE(ROWS(a),,1,0)),SUM((r=1)*a))
解説：
出ました、行列計算！ここは何をやっているか分解して見てみましょう。
1. TOCOL と TOROW: データを縦一列の配列（a）と、横一列の配列（TOROW(a)）に変換します。
2. a < TOROW(a): 縦と横を総当たりで比較します。
つまり、「自分より大きい相手」がいる場所だけTRUEになります。
3. SEQUENCE(...): MMULT計算用の「すべて1」の縦ベクトルを作ります。
4. MMULT(...): 行列積を計算することで、行ごとに「TRUE（自分より大きい数）」の個数を合計します。
最大値なら0個、2番目なら1個（最大値だけが大きい）になりますよね。
5. SUM((r=1)*a): 「自分より大きい数が1個だけある」数値（＝2番目）を抽出して合計します。
※MMULT関数についてはExcel MMULT関数で合計・順位・平均を出す7つの方法で紹介しています！
パターン2：重複あり（LARGEと同じ挙動）
ここからが本番です！元のデータに戻します。
={6,7,22,46;49,38,10,31;46,3,32,49}
49が2つあるので、1番目も49、2番目も49です。
答えの「49」を目指します。
数式5：AGGREGATE（ちょっとズルい？）
=AGGREGATE(14,4,A1:D3,2)
解説：
AGGREGATE関数の第1引数「14」は、実はLARGE関数そのものです。
エラーを無視できるなどのメリットはありますが、やっていることはLARGEと同じ。
ちょっと反則気味ですが、これも立派な代替案です。
数式6：SMALL & COUNT（逆転の発想）
=SMALL(A1:D3,COUNT(A1:D3)-1)
解説：
「上から2番目」ということは、「下から数えると (全体の個数 – 1) 番目」ですよね。
つまり、COUNTで全体の個数を数え、そこから1引いた順位をSMALL関数で下から探します。
数式7：INDEX & SORT
=INDEX(SORT(TOCOL(A1:D3),,-1),2)
解説：
現代的なアプローチです。
TOCOLでデータを1列に並べる。SORT(..., -1)で降順（大きい順）に並べ替える。INDEX(..., 2)で上から2番目を取り出す。
非常に直感的で分かりやすいですね！
数式8：文字列操作（力技！）
=MID(SUBSTITUTE(TEXTJOIN(",",1,SORT(TOCOL(A1:D3),,-1)),",",REPT(" ",100)),101,100)*1
解説：
これは変態的（褒め言葉）な数式です。
はじめに、データを大きい順に並べたあと、TEXTJOINでカンマ区切りの一つの文字列にします。
その後、カンマを「大量のスペース」に置換し、MID関数で無理やり「2番目のブロック」を切り出しています。
数値計算ではなく文字として処理する、豪快な技です。
数式9：TAKE & DROP
=TAKE(DROP(SORT(TOCOL(A1:D3),,-1),1),1)
解説：
今度は、新関数の TAKE と DROP を使った、カード配りのようなアプローチです。
【関数の解説】
・DROP(配列, 行数)：配列の先頭（または末尾）から、指定した行数を捨てます。
・TAKE(配列, 行数)：配列の先頭（または末尾）から、指定した行数を取ります。
まず、TOCOLでデータを1列に並べて、データを大きい順に並べ替えます（SORT）。
DROP(..., 1) で、一番上の「1位」を捨てます。（残りは2位以降のデータ）
TAKE(..., 1) で、残ったデータの一番上（つまり元の2位）を取り出します。
「1位を捨てて、次のトップを取る」
おしゃれな処理ですね！
パターン3：重複無視（ユニークな2番目）
最後は、実務で最も要望が多いパターンです。
「1位が2人いても、次の点数の人を2位と呼びたい！」
つまり、49の次にある「46」を返す数式です。
【どんな時に使う？】
・ゲームのハイスコアランキング（同点1位の下は2位）
・営業成績の表彰（トップ賞の次の賞）
・在庫管理（一番多いロットを除いた、次のロット数）
数式10：MAX & IF（条件付き最大値）
=MAX(IF(A1:D3<MAX(A1:D3),A1:D3))
解説：
ロジックを分解してみましょう。
1. MAX(A1:D3)：まず、全体のチャンピオン（49）を見つけます。
2. IF(A1:D3 < 49, A1:D3)：全体の中で「チャンピオンより小さい値」だけを残します。
49だった場所はFALSEになります。
3. MAX(...)：その「チャンピオン抜きグループ」の中で、一番大きい値を探します。
これで、2番目の実力者（46）が選ばれるわけです。
MAXIFS関数が使える環境（Excel 2019以降）なら、=MAXIFS(A1:D3,A1:D3,"<"&MAX(A1:D3))
と、書くこともできます。
数式11：TOCOLを自力で再現
=LET(a,INDEX(A1:D3,QUOTIENT(SEQUENCE(12,,4),4),MOD(SEQUENCE(12,,0),4)+1),MAX(FILTER(a,a<MAX(a))))
※数式が長くなるので、SEQUENCE(12,, … と（12を直接指定）していますが、本来はSEQUENCE(COUNT(A1:D3),,などと数えます。
解説：
※これは Excel 2021 向けの数式です。
（便利なTOCOL関数はExcel 2024やMicrosoft 365から、LETやSEQUENCEはExcel 2021から使用可能です）
SEQUENCE, QUOTIENT, MOD を組み合わせて、2次元の表を1次元配列として読み込み、そこから最大値未満の最大を探しています。
TOCOLがない環境での意地の数式です。
詳しくは、Excel 複数列のデータを縦一列にする8つの方法にて詳しく解説しています！
数式12：XLOOKUP（近似一致の応用）
=XLOOKUP(MAX(A1:D3)-(9^-9),SORT(TOCOL(A1:D3)),SORT(TOCOL(A1:D3)),,-1)
解説：
「最大値より『ほんのすこーーーし』だけ小さい値」を検索値にします。
MAX(A1:D3)-(9^-9) で48.999…のような値を作り、XLOOKUPの「次の小な値(-1)」オプションで検索します。
すると、49より小さい最大の数（46）がヒットします。
数式13：INDEX & XMATCH
=INDEX(SORT(TOCOL(A1:D3)),XMATCH(MAX(A1:D3)-(9^-9),SORT(TOCOL(A1:D3)),-1))
解説：
数式12とほぼ同じロジックを、INDEXとXMATCHのコンビで行ったものです。
【関数の解説：XMATCH】
XMATCH関数は、MATCH関数の進化版です。
特に強力なのが第3引数の「一致モード」です。
・0：完全一致（デフォルト）
・-1：完全一致、または次に小さい項目
・1：完全一致、または次に大きい項目
今回は -1 を指定することで、「最大値よりちょっと小さい値」を検索したときに、その値以下で最大のデータ（つまり2番目の値）を見つけ出しています。
数式14：UNIQUE関数
=INDEX(UNIQUE(SORT(TOCOL(A1:D3),, -1)), 2)
解説：
これが一番現代的でスマートかもしれません。
1. データを1列にしてソート。
2. UNIQUE関数で重複を削除する。
（49, 49, 46… → 49, 46…）
3. その2番目を取る。
【関数の解説：UNIQUE】
UNIQUE(配列, [列の比較], [回数指定])
引数を省略して UNIQUE(配列) とすると、配列内の重複を削除して、ユニーク（一意）なリストを作成してくれます。
「ユニークな2番目」という言葉通りの処理ですね。
数式15：REDUCE（プログラミング的思考）
=REDUCE(0,A1:D3,LAMBDA(a,b,IF(b=MAX(A1:D3),a,MAX(a,b))))
解説：
最後はLAMBDA関数を使ったループ処理です。
配列の数値を一つずつ取り出し、「もしその値が最大値と同じなら無視、そうでなければ『これまでの最大値』と比較して大きい方を残す」という勝ち抜き戦を行います。
最終的に「最大値以外の中で最強のやつ」が生き残ります。
まとめ：たかが「2番目」、されど「2番目」
ただ「2番目」と言っても、重複をどう扱うかでロジックは大きく変わります。
「LARGE関数があるからいいや」と決めつけずに、データの特性に合わせて最適な数式を組めるようになると、Excelの世界はもっと広がります。
ぜひ、手元のデータでいろいろな「2番目」を探してみてくださいね！
