Excelで最大の正の数・負の数を見つける方法｜MAX IF配列数式の使い方

データ全体の中から単純に最大値を求めるだけでなく、「正の数だけの中で一番大きい値」や「負の数だけの中で一番大きい値」のように、特定の条件を満たす数値の中から最大値を見つけたいケースは意外と多くあります。

データ量が少なければ、MAX関数の引数として該当するセル範囲を手動で選択するだけで済みます。しかし、大量のデータやソートされていないデータの場合、正しい範囲を選択するのは困難であり、場合によってはほぼ不可能です。

そこで役立つのが、IF関数とMAX関数を組み合わせた配列数式です。この方法を使えば「正の数のみ」「負の数のみ」といった条件を数式内で設定でき、条件に一致するデータだけを自動的に評価対象とすることができます。

MAX IF配列数式の仕組み

このチュートリアルで、最大の正の数を見つけるために使用する数式は次のとおりです。

=MAX( IF( A1:B5>0, A1:B5 ) )

数式の各部分が担う役割は以下のとおりです。

• IF関数： データをフィルタリングし、設定した条件を満たす数値だけをMAX関数に渡します。
• MAX関数： フィルタリングされたデータの中から最大値を見つけます。
• 配列数式： 数式を囲む波括弧 { } で示されます。これにより、IF関数の論理テストが単一のセルではなく、データ範囲全体を検索できるようになります。

CSE数式とは

配列数式は、数式を入力した後、キーボードの Ctrl キーと Shift キーを押しながら Enter キーを押すことで作成されます。

そうすることで、等号（=）を含む数式全体が波括弧で囲まれ、次のような形になります。

{ =MAX( IF( A1:B5>0, A1:B5 ) ) }

作成時に押すキーの頭文字（Ctrl・Shift・Enter）から、配列数式は「CSE数式」と呼ばれることもあります。

ExcelでのMAX IF配列数式の実例

以下の例では、MAX IF配列数式を使って、数値の範囲から最大の正の数と最大の負の数をそれぞれ求めます。まず最大の正の数を求める数式を作成し、続いて最大の負の数を求める手順を紹介します。

ステップ1：サンプルデータの入力

1. ワークシートのセルA1～B5に、任意の数値（正の数と負の数が混在したもの）を入力します。
2. セルA6に「最大の正の数」、セルA7に「最大の負の数」というラベルを入力します。

ステップ2：MAX IFネスト数式の入力

今回はネストされた数式（入れ子構造の数式）かつ配列数式を作成するため、数式全体を1つのセルに入力する必要があります。

重要： 数式を入力したら、Enterキーを押したり、マウスで別のセルをクリックしたりしないでください。次のステップで配列数式への変換を行います。

• 最初の計算結果を表示するセルB6をクリックします。
• 次の数式を入力します。

=MAX( IF( A1:B5>0, A1:B5 ) )

ステップ3：配列数式の作成

1. キーボードの Ctrl キーと Shift キーを押したままにします。
2. その状態で Enter キーを押し、配列数式を作成します。
3. セルB6に 45 と表示されれば成功です。これはリスト内で最も大きい正の数です。

セルB6をクリックすると、ワークシート上部の数式バーに完全な配列数式が表示されます。

{ =MAX( IF( A1:B5>0, A1:B5 ) ) }

最大の負の数を見つける方法

最大の負の数を求める数式は、最初の数式と比べて、IF関数の論理テストで使用する比較演算子が異なるだけです。

今度の目的は最大の負の数を見つけることなので、「より大きい」演算子（ > ）の代わりに「より小さい」演算子（ < ）を使用し、0より小さいデータだけをテストします。

• セル B7 をクリックします。
• 次の数式を入力します。

=MAX( IF( A1:B5<0, A1:B5 ) )

• 前述の手順と同じように Ctrl + Shift + Enter を押して配列数式を作成します。
• セルB7に -8 と表示されれば成功です。これはリスト内で最も大きい（0に近い）負の数です。

#VALUE!エラーが表示される場合の対処法

セルB6やB7に期待した答えではなく #VALUE! エラーが表示される場合は、配列数式が正しく作成されていないことが原因である可能性が高いです。

この問題を修正するには、数式バー内の数式をクリックして選択し、もう一度 Ctrl + Shift + Enter を押してください。