1. Home » 
2. エクセル関数の技 » 
3. 条件付きで最大値、最小値を求める

条件付きで最大値、最小値を求める:Excel関数の技

• さまざまな関数を使って条件付きで最大値や最小値を計算する方法を書いています。
  このページでは、MAX,MIN,MAXIFS,MINIFS,FILTER,DMAX,DMIN,INDEX,配列数式,SUMPRODUCTといった関数を使っています。

更新:2023/3/27;作成:2009/4/20

単純な最大値と最小値を求める　　　topへ

• 単純に最大値や最小値を求めるには、MAX関数とMIN関数が使えます。

MAX、MIN関数を使う

• [例題]　下表の売り上げの最大値と最小値を求めます。
  

  	B	C	D
  2	月	商品名	売上
  3	1月	りんご	9,649
  4	1月	みかん	6,183
  5	1月	バナナ	4,896
  6	2月	りんご	2,249
  7	2月	みかん	6,843
  8	2月	バナナ	4,478
  9	3月	りんご	5,991
  10	3月	みかん	6,432
  11	3月	バナナ	7,228
  12	4月	りんご	3,046
  13	4月	みかん	6,297
  14	4月	バナナ	5,152
  15		最大値	9,649
  16		最小値	2,249

• [解答例]
  最大値は　=MAX(D3:D14)　とすると　9,649　となります。
  最小値は　=MIN(D3:D14)　とすると　2,249　となります。
  

  	B	C	D
  2	月	商品名	売上
  3	1月	りんご	9,649
  4	1月	みかん	6,183
  5	1月	バナナ	4,896
  6	2月	りんご	2,249
  7	2月	みかん	6,843
  8	2月	バナナ	4,478
  9	3月	りんご	5,991
  10	3月	みかん	6,432
  11	3月	バナナ	7,228
  12	4月	りんご	3,046
  13	4月	みかん	6,297
  14	4月	バナナ	5,152
  15		最大値	=MAX(D3:D14)
  16		最小値	=MIN(D3:D14)

条件付きで最大値、最小値を求める　　　topへ

• 【例題】下表のデータで、みかんの売上の最大値、最小値を求めます。
  

  	B	C	D
  2	月	商品名	売上
  3	1月	りんご	9,649
  4	1月	みかん	6,183
  5	1月	バナナ	4,896
  6	2月	りんご	2,249
  7	2月	みかん	6,843
  8	2月	バナナ	4,478
  9	3月	りんご	5,991
  10	3月	みかん	6,432
  11	3月	バナナ	7,228
  12	4月	りんご	3,046
  13	4月	みかん	6,297
  14	4月	バナナ	5,152
  15		最大値
  16		最小値

MAXIFS、MINIFS関数を使う　　　topへ
[Excel for microsoft365,Excel2019,Excel2021]

• いろいろな方法で条件付きの最大値、最小値を求めることができますが、MAXIFS関数、MINIFS関数が最もスマートな方法になると思います。
• Excel for Microsoft 365,Excel2019,Excel2021では、MAXIFS関数やMINIFS関数を使うことができるようになりました。
  G5セルには　=MAXIFS(D3:D14,C3:C14,F3)　と入力しています。=MAXIFS(D3:D14,C3:C14,"みかん")でもOKです。
  G6セルには　=MINIFS(D3:D14,C3:C14,F3)　と入力しています。 =MINIFS(D3:D14,C3:C14,"みかん")でもOKです。
  • 詳細な使い方は MAXIFS関数の使い方(条件付きで最大値を求める)　MINIFS関数の使い方(条件付きで最小値を求める)　をご覧ください。
  

各商品ごとの最大値、最小値を求める

• Excel for Microsoft 365、Excel2021ではSpill機能が使えるようになりました。
  複数の商品名の最大値や最小値を一つの数式で求めることができます。
  商品名はUNIQUE関数で取り出すことができます。
  F3セルに　=UNIQUE(C3:C14)　と入力します。
  
  各商品の最大値は　=MAXIFS(D3:D14,C3:C14,F3#)　で求めることができます。
  F3# の # はスピルしているセル範囲全体を参照するための演算子で「スピル範囲演算子」と呼ばれます。
  
  各商品の最大値は　=MINIFS(D3:D14,C3:C14,F3#)　で求めることができます。
  

FILTER関数を利用する　　　topへ
[Excel for microsoft365,Excel2021]

• Microsoft 365ではFILTER関数を使うことができます。
  FILTER関数の使い方は　FILTER関数でデータを抽出する　をご覧ください。
• みかんの最大値は　=MAX(FILTER(D3:D14,C3:C14="みかん"))
  みかんの最小値は　=MIN(FILTER(D3:D14,C3:C14="みかん"))
  で求めることができます。
  
• 各商品について求める場合
  商品名はUNIQUE関数で取り出すことができます。
  F3セルに　=UNIQUE(C3:C14)　と入力します。
  
• FILTER関数で　商品名を条件に一致するデータを配列に取り出し、MAX関数で最大値を計算します。
  G3セルには　=MAX(FILTER($D$3:$D$14,$C$3:$C$14=$F3))　と入力しています。
  G3:G5セルを選択して、[Ctrl]+[D]でG3セルの数式をG4:G5セルへコピーします。
  
• 最小値は　=MIN(FILTER($D$3:$D$14,$C$3:$C$14=$F3))　で求めることができます。
  

配列数式を使う　　　topへ
[全Excelのバージョンで使用可]

• [例題]　上表のデータで、みかんの売上の最大値、最小値を求めます。
  • 最大値は　=MAX(IF(C3:C14="みかん",D3:D14))　と入力し、Shift+Ctrl+Enter　で数式を確定します。
    最小値は　=MIN(IF(C3:C14="みかん",D3:D14))　と入力し、Shift+Ctrl+Enter　で数式を確定します。
    すると、数式は{　と　}　でくくられ、配列数式として機能します。
  • このときの数式は以下のように配列間で処理されています。
    
  • 
• [例題2]　ある期間のバナナの売上の最大値、最小値を求めてみます。
  なお、データ表は以下のようにB列に日付のシリアル値が入力されています。
  • 条件は2009年2月のバナナの売上で最大値と最小値を求めます。
    =MAX(IF((B3:B14>=DATE(2009,2,1))*(B3:B14<DATE(2009,3,1))*(C3:C14="バナナ"),D3:D14))
    =MIN(IF((B3:B14>=DATE(2009,2,1))*(B3:B14<DATE(2009,3,1))*(C3:C14="バナナ"),D3:D14))
    と入力し、Shift+Ctrl+Enter　で数式を確定します。
  • 条件部分に論理式を使っています。AND条件にするため論理積(*を使った掛け算)とします。
    2009年2月のデータとは日付が2009/2/1以上で2009/3/1未満としています。
    (B3:B14>=DATE(2009,2,1))*(B3:B14<DATE(2009,3,1))
    さらに、C列がバナナで有ることも条件なので(AND条件) *(C3:C14="バナナ") を掛けています。
  • 

INDEX関数を利用する　　　topへ
[全Excelのバージョンで使用可]

• ここに書いた例では最大値を求めることはできますが、最小値は求めることができません。
  ここで書いた方法では、条件に合わないものは「0」となり、他の数値よりもっとも小さくなるためです。
• INDEX関数の配列形式を利用する方法もあります。
  =INDEX(配列, 行番号, [列番号])で行番号に「0」を指定すると配列全体を返すのを利用します。
• G3セルの数式は　=MAX(INDEX(($C$3:$C$14=F3)*($D$3:$D$14),0))　としています。
  詳細な使い方は　INDEX関数の使い方　をご覧ください。
  
• この数式の計算途中を見てみると、配列の中から最大値を求めているのが分かります。
  下図は、G4セルの数式を使っています。
  

SUMPRODUCT関数を利用する　　　topへ
[全Excelのバージョンで使用可]

• ここに書いた例では最大値を求めることはできますが、最小値は求めることができません。
  (条件に合わないものは「0」となり、他の数値よりもっとも小さくなるので)
• 条件付きの計算でよく利用されるSUMPRODUCT関数とMAX関数の組み合わせでも求めることができます。
• G3セルの数式は　=SUMPRODUCT(MAX(($C$3:$C$14=F3)*($D$3:$D$14)))　としています。
  詳細な使い方は　SUMPRODUCT関数の使い方　をご覧ください。
  

スポンサードリンク

　PageViewCounter
　
　Since2006/2/27