Excel MAXとMINを1つのセルに！8つの方法

はじめに：MAXとMIN、別々に計算…それで満足ですか？
準備：挑戦者となる配列を用意する
8つのアプローチで、「最大値＆最小値」を同時ゲット！
まとめ：回り道こそ、最大の近道だ！

はじめに：MAXとMIN、別々に計算…それで満足ですか？

「この範囲の最大値と最小値を知りたい？そんなの、MAX関数とMIN関数で一発でしょ？」

そう思ったあなた、大正解！

通常は、それぞれの関数を別々のセルに入力するのが、最も簡単で分かりやすい方法です。

でも、ちょっと待ってください！

もし、その2つの答え（最大値と最小値）を、一つの数式で2つのセルに同時に表示させたいとしたら…？

「え、そんなことできるの？どうやって1つの数式から2つの答えを…？」

その疑問にお答えするのが、今回の冒険です！

今回は、「最大値と最小値を１つの数式で同時に表示！」という、シンプルながらも配列操作の奥深さを体感できるパズルに、なんと8種類もの異なるアプローチで挑みます。

目的は、

配列定数や特定の関数を使って、複数の結果を同時に返すという、Excelのスピル機能の面白さと、配列の作り方の多様性を学ぶこと！

この冒険が終わる頃には、あなたの「配列脳」が、さらにレベルアップしているかもしれませんよ！

準備：挑戦者となる配列を用意する

本記事では、無料のWeb版Excelを使用して検証および画像の作成を行っています。Windowsはもちろん、MacやLinuxの方もブラウザさえあれば挑戦できます！

何事も、まずは準備から。まっさらなシートのA1セルを起点に、以下の4行4列の数値配列を用意してください。

以下の数式をA1セルに貼り付けると表ができますよ！

={12,28,29,49;24,38,48,16;18,49,45,12;45,41,28,32}

この表には、最大値の「49」と最小値の「12」が、あえて複数含まれています。

これも今回のパズルのちょっとしたスパイスです。

私たちのゴールは、この範囲（A1:D4）の中から、最大値「49」と最小値「12」を、一つの数式で、例えばB6セルとB7セルに同時に表示させることです。

さあ、皆さんならどんな数式を組み立てますか？

「１つの数式で同時に表示させるにはどうしたらいいだろう？」と考えながら読み進めてみてくださいね！

8つのアプローチで、「最大値＆最小値」を同時ゲット！

アプローチ1：古典的な配列の合体！(CHOOSE / IF / VSTACK)

考えかた

まず思いつくのは、「最大値を計算する部分」と「最小値を計算する部分」を別々に作り、
最後にそれらを合体させて一つの配列にする、という方法ですね。

数式と解説

=CHOOSE({1;2},MAX(A1:D4),MIN(A1:D4))

・MAX(A1:D4): まず、普通に最大値「49」を計算します。

・MIN(A1:D4): 同じく、最小値「12」を計算します。

・CHOOSE({1;2}, ..., ...): ここが合体の魔法！CHOOSE関数は、通常CHOOSE(インデックス番号, 値1, 値2, ...)のように使いますが、第1引数に{1;2}という「縦方向の配列定数」を指定すると、「1番目には値1（MAXの結果）を、2番目には値2（MINの結果）を配置した、縦一列の配列を作りなさい」という命令になります。

これにより、{49;12}という配列がスピルして表示されるのです！

【配列定数の基本】
{ }で囲まれた数値や文字列の集まりを配列定数と呼びます。
・カンマ区切り {1,2,3} は横方向（1行）の配列
・セミコロン区切り {1;2;3} は縦方向（1列）の配列
これはExcel配列操作の基本中の基本なので、しっかり覚えておきましょう！

別解

=IF({1;0},MAX(A1:D4),MIN(A1:D4))

IF関数の第1引数に縦配列{1;0}（または{TRUE;FALSE}）を入れることでも、同様に結果を分岐させて配列を作れます。

=VSTACK(MAX(A1:D4),MIN(A1:D4))

最新のExcel（Microsoft 365）なら、VSTACK関数で「縦に積む」と命令するのが最もシンプルで分かりやすいですね！

アプローチ2：配列定数の計算マジック

考えかた

次は、配列定数をもっと直接的に計算に組み込む、関数好きにはたまらないテクニックです。

{1;0}と{0;1}という2つのスイッチを使って、1行目ではMAXだけを、2行目ではMINだけを有効にします。

数式と解説

={1;0}*MAX(A1:D4)+{0;1}*MIN(A1:D4)

この数式、一体何が起こっているのでしょうか？

1. {1;0}*MAX(A1:D4): 最大値「49」に、縦配列{1;0}を掛けます。Excelの配列計算（ブロードキャスト）により、結果は{1*49; 0*49}、つまり{49; 0}という配列になります。

2. {0;1}*MIN(A1:D4): 最小値「12」に、縦配列{0;1}を掛けます。結果は{0*12; 1*12}、つまり{0; 12}という配列になります。

3. {49;0} + {0;12}: 最後に、この2つの配列を足し合わせます。

すると、{49+0; 0+12}となり、目的の{49;12}が完成するのです！配列定数は本当に面白いですよね！

アプローチ3：SUBTOTAL – 関数1つでシンプルに

考えかた

複数の計算を1つの関数で行いたい…そんな時に頼りになるのが、集計の万能選手SUBTOTAL関数です。

第1引数に配列定数を渡すことで、複数の集計結果を同時に得られます。

数式と解説

=SUBTOTAL({4;5},A1:D4)

・{4;5}: SUBTOTAL関数の第1引数「集計方法」には、通常1つの番号を指定しますが、ここに{4;5}という配列定数を指定します。

SUBTOTALでは、「4」がMAX、「5」がMINを意味します。（他にも9=SUM, 1=AVERAGEなどがあります）

・SUBTOTAL(..., A1:D4): Excelはこれを賢く解釈し、「A1:D4に対して、MAXとMINをそれぞれ計算した結果を配列で返しなさい」と命令されたものとして、{49;12}を返してくれるのです。

おそらくこれが最も短い数式でしょう。

アプローチ4：AGGREGATE – SUBTOTALの強化版

考えかた

SUBTOTALと似た動きをする、さらに高機能なAGGREGATE関数でも同じことができます。

数式と解説

=AGGREGATE({4;5},4,A1:D4)

・{4;5}: AGGREGATE関数の第1引数「集計方法」も、SUBTOTALと同じ番号体系です。
「4」がMAX、「5」がMINです。

・...,4,...: 第2引数「オプション」では、エラー値や非表示行を無視するかなどを指定できます。今回は「4」（何も無視しない）を指定していますが、他のオプションでも動作します。

・AGGREGATE(..., A1:D4): SUBTOTALと同様に、2つの集計結果を配列{49;12}として返します。SUBTOTALより引数は多いですが、より複雑な状況に対応できるのが強みです。

アプローチ5：LARGE – N番目に大きい値

考えかた

「N番目に大きい値」を返すLARGE関数を使ってみましょう。

最大値は「1番目に大きい値」、最小値は「”全部で何個あるか”番目に大きい値」と言い換えられますよね？

数式と解説

=LARGE(A1:D4,(COUNT(A1:D4))^{0;1})

・COUNT(A1:D4): まず、範囲内の数値の個数「16」を計算します。

・(16)^{0;1}: 次に、「16」を{0;1}乗します。数学のルールで、どんな数も0乗すれば「1」、1乗すれば「元の数」になるので、結果は{16^0; 16^1}、つまり{1; 16}という配列になります。

・LARGE(A1:D4,{1;16}): 最後に、LARGE関数に「1番目と16番目に大きい値を配列で返しなさい」と命令し、{49;12}を得ます。

アプローチ6：LARGE + FREQUENCY – 古典的な序列生成

考えかた

アプローチ5と考え方は同じですが、「{1; 個数}」という配列を、最新のべき乗計算ではなく、統計関数FREQUENCYを使った古典的なテクニックで生成します。

数式と解説

=LARGE(A1:D4,FREQUENCY(ROW(INDIRECT("A1:A"&COUNT(A1:D4)+1)),1))

・COUNT(A1:D4)+1: 個数「16」に1を足して「17」を計算します。

・ROW(INDIRECT("A1:A17")): INDIRECT("A1:A17")でA1:A17という参照を作り、その行番号を取得して{1;2;...;17}という連番配列を生成します。

・FREQUENCY(..., 1): FREQUENCY関数はFREQUENCY(データ配列, 区間配列)のように使います。

FREQUENCY関数については以下の記事でも紹介しています！

今回は「データ配列」が{1;...;17}、「区間配列」が「1」です。

FREQUENCYは、データ配列の中から「区間配列の値以下」の個数と、「区間配列の値より大きい」個数を数えます。

つまり、「1以下の個数（=1個）」と「1より大きい個数（=16個）」を数え、{1; 16}という配列を返してくれるのです！

・LARGE(A1:D4,{1;16}): あとはアプローチ5と同じですね。

アプローチ7：TAKE + SORT + TOCOL – 最新関数の合わせ技

考えかた

最新の動的配列関数を駆使すれば、こんな簡単な方法もあります。

まず配列を縦一列にして、並び替えてから、最後と最初の値を取り出す、という非常に直感的なロジックです。

数式と解説

=TAKE(SORT(TOCOL(A1:D4)),{-1;1})

・TOCOL(A1:D4): まず、TOCOL関数で4×4の配列を、縦一列16個の数値リストに変換します。

・SORT(...): 次に、SORT関数でこのリストを（デフォルトの）昇順（小さい順）に並び替えます。

・TAKE(...,{-1;1}): 最後に、TAKE関数が、この昇順リストから「最後の1行（-1）」と「最初の1行」を取り出します。

最後に来るのは最大値、最初に来るのは最小値なので、{49;12}という目的の配列が得られます。

アプローチ8：SUM + COUNTIF – 条件カウントの逆襲

考えかた

これは、完全に「暇つぶし」の領域です（笑）。

COUNTIF関数を使って、「自分より大きい（小さい）数がいくつあるか」を数えることで最大値・最小値を特定し、それをSUM関数で抽出し、配列定数で結合します。

数式と解説

={1;0}*SUM((COUNTIF(A1:D4,">"&A1:D4)=0)*A1:D4)/
SUM((COUNTIF(A1:D4,">"&A1:D4)=0)*1)+
{0;1}*SUM((COUNTIF(A1:D4,"<"&A1:D4)=0)*A1:D4)/
SUM((COUNTIF(A1:D4,"<"&A1:D4)=0)*1)