最大値、最小値を抽出する関数/条件付き/MAX、MIN関数等

image18090303_s 基礎コース

最大値、最小値を抽出する関数(MAX,MIN関数)

EXCELにて、指定したデータ範囲の中から最大値・最小値を求めるには次の関数を利用します。

=MAX(数値1, [数値2]・・・)

[数値1] = 最大値を求める1つ目の数値、またはセル範囲を指定します。
[数値2] = 最大値を求める2つ目の数値、またはセル範囲を指定します。

関数の説明 指定された[数値]の中で最大値を求める関数
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=MIN(数値1, [数値2]・・・)

[数値1] = 最小値を求める1つ目の数値、またはセル範囲を指定します。
[数値2] = 最小値を求める2つ目の数値、またはセル範囲を指定します。

関数の説明 指定された[数値]の中で最小値を求める関数
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

使い方例)MAX・MIN関数

下の表はある学校の学期末テストの結果表です。セルG9・G10にそれぞれ合計点の最高点・最低点を求めてみました。

33-0

① 合計点の中から最高点を求める。

[数値1] : 最高点を求めるセル範囲[G4:G7]を指定します。
=MAX(G4:G7)

② 合計点の中から最低点を求める。

[数値1] : 最低点を求めるセル範囲[G4:G7]を指定します。
=MIN(G4:G7)

 

 

条件付きで、指定の範囲から最大値、最小値を抽出する関数

最大値・最小値を求める際に、「全国の『みかん』や『リンゴ』の出荷量一覧の中から、『リンゴ』の出荷量が最大の地域・・・・」といった、条件付きで最大値を求める際には、次のデータベース関数と呼ばれる関数を利用します。

=DMAX(Database, フィールド, Criteria)

[Database] = 各列の見出しを含めたデータ範囲を指定します。
[フィールド] = 計算対象とする列を指定します。列の指定は、列の見出しを「”」で囲み文字列で指定するか、または、左端から数えた列数を数字で指定します。
[Criteria] = 条件が設定されているセル範囲を指定します。

関数の説明 指定した[フィールド]内にて、条件を満たす最大値を求める
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=DMIN(Database, フィールド, Criteria)

[Database] = 各列の見出しを含めたデータ範囲を指定します。
[フィールド] = 計算対象とする列を指定します。列の指定は、列の見出しを「”」で囲み文字列で指定するか、または、左端から数えた列数を数字で指定します。
[Criteria] = 条件が設定されているセル範囲を指定します。

関数の説明 指定した[フィールド]内にて、条件を満たす最小値を求める
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

データベースとは・・・

エクセルでは、規則正しく整理された、たくさんのデータの集合体をデータベースと呼びます。
またそのデータベースにおいて、「行」のことをレコード、「列」のことをフィールドと呼び、関数などにおいてよく使用されます。

 

使い方例)DMAX・DMIN関数

下の表は、前回の表の右側に「追試」の列を追加したものです。合計点で300点未満となる場合は、この列に「○」印を入力するようにしました。
結果として1人を除いて追試となりましたが、その中での最高点・最低点を求めて学力差を見てみることにしました。

if

① 追試者の合計点の中から最高点を求める。

(1)[Database] : 『合計点』『追試』の見出しを含むデータの範囲[G3:H7]を指定します。
(2)[フィールド] : [Database]にて指定した範囲内で、最高点を探す列(フィールド)は1行目なので、「1」を指定します。
(3)[Criteria] : 条件を入力しているセル範囲[H9:H10]を指定します。 →※見出し(H9)を含むことによってその見出し(H9)の列で(H10)の場合・・・という意味になります。
=DMAX(G3:H7,1,H9:H10)

② 追試者の合計点の中から最低点を求める。

(1)[Database] : 『合計点』『追試』の見出しを含むデータの範囲[G3:G7]を指定します。
(2)[フィールド] : [Database]にて指定した範囲内で、最高点を探す列(フィールド)は1行目なので、「1」を指定します。
(3)[Criteria] : 条件を入力しているセル範囲[H9:H10]を指定します。 →※見出し(H9)を含むことによってその見出し(H9)の列で(H10)の場合・・・という意味になります。
=DMIN(G3:H7,1,H9:H10)

 

最大値、最小値を検索し、色をつける

表の中から最大値・最小値を求め、そのセルに色を付ける場合には、「条件付き書式」を活用して設定します。

 

使い方例)条件付き書式にて最大値・最小値にセル色を付ける

前回の表にて、合計点欄の最高点・最低点に、条件付き書式を利用してセルの色が自動的に付くように設定してみましょう。

(1) 次の手順で条件付き書式を開始します。
column-270-5c9d9eb6-39c50

①セルの色を変える対象のセル範囲を選択します。
②リボンから、「条件付き書式(※文字の方)」をクリックして、表示されたメニューから「新しいルール」を選択します。

 

(2) 新しく表示される「新しい書式ルール」ダイアログボックスにて次のように指定します。
image18090303_s

①[ルールの種類を選択・・・]の一覧から、「数式を使用して、書式設定するセルを決定」を選択します。
②[次の数式を・・・]の内容に、『G4がセル範囲G4:G7の中で最大値(の場合)』とする数式を入力します。

=$G4=MAX($G$4:$G$7)

③[書式]ボタンから、セルの塗りつぶしを設定します。
④[OK]ボタンをクリックして設定を完了します。

 

(3) 書式設定の結果

最高点のセルG5が塗りつぶされました。

 

(4) 最低点への条件付き書式設定方法

「最低点」の場合も上記と同様に条件付き書式を設定すれば、自動的にセルが塗りつぶされるようになります。
その場合、数式をMAX関数ではなくMIN関数として指定してください。

 

最大値、最小値から2番目、3番目の数値を求める

データの中から、最大値・最小値ではなく、「○○番目」の数値が知りたい場合は、次の関数を利用します。

=LARGE(配列, 順位)

[配列] = 対象となるデータ範囲、または、数値を直接「,(カンマ)」で区切って指定します。
[順位] = 大きい方から数えた順位を数値で指定します。

関数の説明 [配列]内にて、指定した[順位]のデータを求める
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=SMALL(配列, 順位)

[配列] = 対象となるデータ範囲、または、数値を直接「,(カンマ)」で区切って指定します。
[順位] = 小さい方から数えた順位を数値で指定します。

関数の説明 [配列]内にて、指定した[順位]のデータを求める
EXCELバージョン Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

使い方例)LARGE・SMALL関数

下の表は、ある学校での学期末テストの結果です。合計点の中から、最高点や最低点を見つけセルの色を変えましたが、次点となる、「2番目に」髙い点数と、「2番目に」低い点数も参考のために抽出することにしました。

① 2番目に高い点数を求める。

(1)[配列] : 比べる対象のセル範囲[G4:G11]を指定します。
(2)[順位] : 2番目を抽出する為、「2」を指定します。
=LARGE(G4:G11,2)

② 2番目に低い点数を求める。

(1)[配列] : 比べる対象のセル範囲[G4:G11]を指定します。
(2)[順位] : 2番目を抽出する為、「2」を指定します。
=SMALL(G4:G11,2)

0以外の最小値、最小値から0を除く方法

最小値を求める際に、「0」を除いた中での最小値が知りたい場合は、次のようにCOUNTIF関数を組み合わせて入力しましょう。
→COUNTIF関数の詳細についてはこら

 

使い方例)「0」を除いた最小値を求める

下の表では、「土方歳一」さんが理科の試験で再試験扱いとなった為、仮に数値の「0」を入力して4教科の合計点を出しています。
この時、「0」点を除いた理科の最低点を求める方法を見てみましょう。

① 「0」を除いた理科の最低点を求める。

(1)COUNTIF関数にて、理科の点数のセル範囲[E4:E11]に「0」値がいくつ有るかを確認します。

COUNTIF(E4:E11,0)

この結果は「0」値の数の合計になりますが、言い換えれば、「0」値の順位が分かるという事になります。
つまり、『もし2個あれば、最小値の内の、1位・2位は「0」である』という意味になります。
これを利用して、「0」が占める順位の次の順位(+1)は、

COUNTIF(E4:E11,0)+1

という式が出来上がります。

(2)SMALL関数にて、(1)で導き出した順位の数値を求めます。

=SMALL(E4:E11, COUNTIF(E4:E11,0)+1 )

以上で、「0」値を除いた最小値を求める事が出来ます。

Rate article