Excelで最小値と最大値を色で強調表示する方法。 Excelの関数最小値、最大値、平均値 Excelで最小値を求める方法

Windowsの場合 12.07.2021

Windowsの場合

avg(範囲1; 範囲2;...)

指定された範囲内の数値の平均が現在のセルに返されます。

例5.セル範囲 A1:A5 内例1平均値を求めます。

結果はセル A7 で取得されます。段階的なアクション 関数ウィザードセル A7 に次の数式を入力する必要があります。 =平均(A1:A5)。

最大値の決定

max(範囲1; 範囲2;...)- 統計関数のグループ。

指定された範囲の最大数が現在のセルに返されます。

例6。セル範囲 A1:A5 内例1最大値を決定します。

結果はセル A8 で得られます。

段階的なアクション 関数ウィザードセル A8 に次の数式を入力する必要があります。

=最大(A1:A5)。

セル A8 には数値 2000 が含まれます。

最小値の決定

min(範囲1; 範囲2;...)- 統計関数のグループ。

指定された範囲の最小値が現在のセルに返されます。

例7。セル B1:B5 の範囲内例2最小値を決定します。

結果はセル B8 で得られます。

段階的なアクション 関数ウィザードセル B8 に次の数式を入力する必要があります。

=分(B1:B5)。

セル B8 には数字 800 が入ります。

数値の順位の決定

ランク(セルアドレス;範囲)- 統計関数のグループ。

指定された範囲内のセルアドレスで指定された番号の位置(ランク)に対応する値が現在のセルに返されます。

例8.列 A の対応するセルの情報をセル D1、D2、D3、D4、D5 にコピーします。範囲 D1:D5 の各セルについて、数値の順位を決定します。

結果はセル E1:E5 で取得されます。ランク関数は最初にセル E1 に入力され、次に E5 までのすべてのセルにコピーされます。

段階的なアクション 関数ウィザードセル E1 に次の数式を入力します。

=ランク(D1; $ D$1:$ D$5) - $記号セット 絶対アドレス、コピー時にセルの範囲が変わらないようにします。

E5 までのすべてのセルの数式をコピーした後、範囲内の各値のランクを取得します。 D1:D5 の範囲内の最大値を持つ数値のランクは 1 になり、最小値は -5 になります。

予測機能

トレンド(既知の x 値、既知の y 値、新しい y 値) -統計関数のグループ

既知の値に基づいて計算された新しい value_X が現在のセルに返されます。線形近似が実行されます。

成長(既知の x 値; 既知の y 値; 新しい y 値) -統計関数のグループ。

既知の値に基づいて計算された新しい value_X が現在のセルに返されます。指数近似が実行されます。関数の動作は関数と同様です。傾向、指数トレンドに従って計算のみが実行されます。

行列を操作するための関数

mobr(配列)- 数学関数のグループ。

選択した範囲の配列に格納されている行列の逆行列を返します。

配列は、同じ数の行と列を持つ数値配列です。配列は、A1:C3 などのセル範囲として、または範囲または配列の名前として指定できます。配列内のセルのいずれかが空であるかテキストが含まれている場合、または配列の行と列の数が異なる場合、MOBR 関数はエラー値 #VALUE! を返します。

mopr(配列) -数学関数のグループ。

行列の行列式を返します (行列は配列に格納されます)。

行列の行列式は、配列要素の値から計算される数値です。配列は、同じ数の行と列を持つ数値配列です。配列内のいずれかのセルが空であるか、テキストが含まれている場合、MOPRED 関数はエラー値 #VALUE! を返します。 MOPRED は、配列の行数と列数が異なる場合にも #VALUE! エラー値を返します。

1) 最近使用した 10 件、2) 完全なアルファベット順リスト、3) 財務、4) 日付と時刻、5) 数学、6) 統計、7) 論理、8) テキスト、9) データベースの操作、10) 値のチェック

最大値/最小値を見つけるのは簡単な作業ですが、範囲内のすべての値の中からではなく、特定の条件を満たす値の中からのみ MAX/MIN を見つける必要がある場合は、やや複雑になります。

テキストと数値の 2 つの列を持つテーブルがあるとします。

数式を理解しやすくするために、列ごとに 2 つを作成しましょう。文章 (あ 6: あ 30 ）そして数字 (B6:B30 ）。 (サンプルファイルを参照してください)。

いくつかのタスクを考えてみましょう。

A.値に対応する数値の中から最大値を見つけてみましょう テキスト 1(セルに基準を入力します E6 ).
それらの。列のすべての値の中から最大値を探します数値ただし、その列の同じ行にある人の間でのみです。あテキスト値は テキスト 1。書いてみましょう（式を入力するときはを忘れずに押してください） CTRL+SHIFT+ENTER):
=LARGEST(IF(A6:A30=E6,B6:B30,"");1)

または一緒に 名前付き範囲:

=LARGE(IF(テキスト=E6,数値,""),1)

数式 Text=E6 の一部は、 (TRUE:FALSE:FALSE:FALSE:TRUE:FALSE:FALSE:FALSE:TRUE:FALSE:FALSE:FALSE: TRUE:FALSE:FALSE:FALSE:TRUE:FALSE:FALSE:FALSE) を返します。 :TRUE:FALSE:FALSE:FALSE:FALSE) (結果を表示するには、式のこの部分を選択してキーを押します)。 TRUE は、次の行に一致します。 テキスト値値が含まれています テキスト 1.

式の一部 IF(テキスト=E6;数値;"")、 (10:"":"":"":-66:"":"":"": -37:"":"":"":-5:"": "":"" が返されます) : 4:"":"":"":8:"":"":"":"")、数値列の値が TRUE に置き換えられ、その値が FALSE に置き換えられます。 "" の代わりに、任意のテキスト文字 (文字) を使用することも、完全に省略することもできます (この場合、配列は次のようになります (10: FALSE: FALSE: FALSE: -66: FALSE: FALSE: FALSE: -37: FALSE:偽: 偽:-5: 嘘: 嘘: 偽: 4: 嘘: 偽: 嘘: 8: 偽: 偽: 偽: 偽))。

MAX() 関数の代わりに、2 番目のパラメーター =1 を指定した LARGE() 関数が使用されます。基準を満たさない行が 1 行もない場合、式 = MAX(("":"":"":"":"":"":"": "":"":"":"": "":"":"":"":"" :"":"":"":"":"":"":"":"":"")) 0! が返されるため、誤解を招く可能性があります。この場合、LARGE() 関数はエラー #NUM! を返します。

B.一定の値の範囲、たとえば5から50に属する数値のみの最大値を求めてみましょう。セルには境界線を入力できます。私 14 そして J14 。解決策は次のとおりです。
=LARGE(IF((数値>=I14)*(数値<=J14);Числа);1)

で。助けを借りて見つけます 配列数式値に一致する値のうちの最小値 テキスト3:
=MIN(IF((テキスト=E7);数値;"");1)

それらの。列にある場合あ値 = テキスト3、その後、列の値が考慮されます B 、値の場合<> テキスト3の場合、最大値 +1 が考慮されます。つまり、確かに最小限ではありません。次に、MIN() 関数は結果の配列から最小値を返しますが、いずれの値も含まれないことは明らかです。<> テキスト3、結果は歪みません (問題 A を参照)。

別の解決策は、DMIN() 式を使用することです。 配列数式.
=DMIN(A5:B30,B5,I8:I9)

G.大きい値の中から最小値を見つけてみましょう。
=DMIN(A5:B30,B5,I10:I11)
範囲内のどこにあるのか I10:I11 =B6>AVERAGE(数値) という基準が含まれています

D.絶対値の最大値を求めてみましょう。上の写真を見ると、-99 であることがわかります。これを行うには、次を使用します 配列数式:

IF(MAX(ABS(数値))=MAX(数値),MAX(数値),-MAX(ABS(数値)))

E.最小の正の数を見つけてみましょう。

=SMALL(数値;COUNTIF(数値;"<=0")+1) - いつもの公式！

=SMALL(IF(数値>0,数値),1) - 配列数式.

アドバイス：

上記の問題はすべて、DMIN() 関数を使用せずに解決できます。これを行うには、基準を満たす値のみが表示される追加の列を作成する必要があります。次に、MAX() 関数または MIN() 関数を使用して、選択した値の中から最大値または最小値をそれぞれ決定します (サンプルファイル「配列数式のないシート」を参照)。

複数の条件

上記のアプローチは、いくつかのテキスト条件の最大値または最小値を見つける必要がある場合に拡張できます。

この場合、より複雑なコードを記述する必要があります。 配列数式:

=SMALL(IF(($A$6:$A$16=E6)*($B$6:$B$16=F6),$C$6:$C$16;"");1)

サンプルファイルでは、わかりやすくするために . さらに、選択には基準が使用されます (黄色のセルを参照)。

同様に、値が特定の範囲内にある行の最小値を見つけるための数式を設定できます。

大きなテーブルを操作する場合、列全体に重複した金額が散在していることが確実に見つかります。同時に、独自の重複がある最初の最小数値を持つテーブルからデータを選択する必要がある場合があります。条件に基づいた自動データサンプリングが必要です。 Excel では、この目的に配列数式を使用できます。

Excelで条件で選択する方法

最初の最小の数値に対応する値を決定するには、条件に従ってテーブルからのサンプルが必要です。与えられた価格表から市場で最初に安い製品を見つけたいとします。

自動サンプリングは、次の構造を持つ式によって実装されます。

INDEX(サンプリングのデータ範囲, MIN(IF(範囲=MIN(範囲),ROW(範囲)-ROW(列ヘッダー);””)))

「data_range_for_sampling」の場所で、テーブル (テキストなど) からサンプリングするための値の範囲 A6:A18 を指定する必要があります。INDEX 関数はそこから結果の値を 1 つ選択します。 range 引数は、最初の最小の数値が選択される数値を含むセルの領域を指します。 2 番目の ROW 関数の「column_header」引数では、数値範囲を含む列ヘッダーを持つセルへの参照を指定する必要があります。

当然のことながら、この式は配列で実行する必要があります。したがって、入力を確認するには、Enter キーだけでなく、CTRL+SHIFT+Enter キーの組み合わせ全体を押す必要があります。すべてが正しく行われると、数式バーに中括弧が表示されます。

次の図に注目してください。この数式は配列のセル B3 に入力されています。

対応する値を最初に最小の数値でサンプリングします。

この式を使用すると、数値に対する最小値を選択することができました。次に、式の動作原理を分析し、すべての計算の順序全体を段階的に分析します。

条件付きサンプリングの仕組み

ここでは INDEX 関数が重要な役割を果たします。その名目上のタスクは、特定の数値に対応する値をソーステーブル (最初の引数 - A6:A18 で示される) から選択することです。 INDEX は、2 番目 (テーブル内の行番号) と 3 番目の引数 (テーブル内の列番号) で定義された基準を考慮して機能します。ソーステーブル A6:A18 には列が 1 つしかないため、INDEX 関数の 3 番目の引数を指定しません。

隣接する範囲 B6:B18 の最小値の反対側のテーブル行番号を計算し、それを 2 番目の引数の値として使用するには、いくつかの計算関数が使用されます。

IF 関数を使用すると、条件に基づいてリストから値を選択できます。最初の引数は、範囲 B6:B18 内の各セルのどこで最小数値をチェックするかを指定します (IFB6:B18=MINB6:B18)。このようにして、論理値 TRUE と FALSE の配列がプログラムメモリ内に作成されます。この場合、最小値 8 には列 B6:B18 にさらに 2 つの重複が含まれるため、3 つの配列要素に値 TRUE が含まれます。

次のステップでは、範囲のどの行に各最小値が含まれているかを判断します。最初の最小値が決定されるため、これが必要になります。このタスクは ROW 関数を使用して実装され、プログラムメモリ内の配列の要素にシートの行番号を入力します。ただし、最初に、これらすべての数値から、テーブルの最初の行の反対側にある数値、B5、つまり数値 5 が減算されます。これが行われるのは、INDEX 関数がテーブル内の数値ではなく、テーブル内の数値を処理するためです。 Excel ワークシート。同時に、ROW 関数はシートの行番号のみを返すことができます。ずれを避けるためには、シートとテーブルの行番号の順序を差分を引いて比較する必要があります。たとえば、テーブルがシートの 5 行目にある場合、テーブルの各行はシートの対応する行より 5 少なくなります。

すべての最小値が選択され、テーブル内のすべての行番号が比較された後、MIN 関数は最小の行番号を選択します。この同じ行には、列 B6:B18 に表示される最初の最小の数値が含まれます。この行番号に基づいて、INDEX 関数はテーブル A6:A18 から対応する値を選択します。その結果、数式はこの値を計算結果としてセル B3 に返します。

Excelで最大の数値を持つ値を選択する方法

式の原理を理解したら、式を簡単に変更して他の条件に合わせて調整することができます。たとえば、Excel で最初の最大値を選択するように数式を変更できます。

Excel で最初の最大値 (ただし 70 未満) を選択できるように数式の条件を変更する必要がある場合:

Excel でゼロ以外の最初の最小値を選択する方法:

簡単にわかるように、これらの式は、MIN 関数と MAX 関数およびその引数が異なるだけです。

今、あなたを制限するものは何もありません。配列内の数式の動作原理を理解すると、多くの条件に合わせて数式を簡単に変更し、多くの計算問題を迅速に解決できます。

Excelには最小値を求める関数があります。ただし、条件ごとに最小値を見つけるのは困難です。アドインの機能でこのタスクに対応できます =MINSIF

(標準の Excel SUMIF 関数と同様)。

Excel版では 2016 上記には組み込み関数があります ミニズリー、使用できます。 Excelのバージョンが古い場合は、アドインをインストールすることでこの機能が利用可能になります。 VBA-エクセル.

関数には次の引数があります =ミネスリ(範囲;基準;[検索範囲 ])

範囲- チェックするセルの範囲。

基準- 最小値チェックを指定する数値、式、またはテキスト形式の条件。
[ 検索範囲 ]- 最小値を決定するための実際の範囲。このパラメータが指定されていない場合は、パラメータで指定されたセルが使用されます。範囲.

例1

値と論理式を条件として指定できます。

最低文学グレードを決定する次の例を考えてみましょう。これを行うには、パラメータで基準値「Literature」が指定され、パラメータ範囲- アイテムのリスト。
論理式を条件として指定する場合 «<>ロシア", その後、ロシア語を除くすべての科目で最低成績が決定されます。

例 2

次の例では、パラメータ SEARCH_RANGEが指定されていないため、パラメータで指定されたセルの中から最小値が決定されます。範囲.

各種ランキングを作成したり、成功指標を分析したりする際に、最良の結果と最悪の結果を色で強調表示すると非常に便利です。 Excelで最大値を色で強調表示するにはどうすればよいですか? ここで条件付き書式が役に立ちます。このアプローチの利点は、テーブルにデータを追加またはテーブルから削除した場合でも、最良/最悪のインジケーターの自動強調表示が機能し続けることです。

Excelで最大値を強調表示する方法

たとえば、次の経費表を考えてみましょう。

最高経費と最低経費をすばやく特定するには、次の手順を実行します。

その結果、最大の数値を持つセルが強調表示されました。

Excelで最小値を選択する方法

Excel で最小値を強調表示するには、上記の手順全体を繰り返します。 MAX 関数の代わりに MIN 関数が必要です。この関数を使用すると、Excel テーブル内の最小値を選択できます。また、緑の塗りつぶし色の代わりに赤を選択します。

同じ範囲に 2 つの条件付き書式ルールを適用する必要があります。確認するには、「ホーム」-「スタイル」-「条件付き書式」-「ルールの管理」ツールを選択します。

2 つの条件付き書式設定ルールを適用して列 B (経費) の最大値と最小値を強調表示する最終的な効果は次のとおりです。

両方のルールの動作原理は同じです。両者の違いは =MAX() 関数と =MIN() 関数のみです。さらに、関数の引数は同一です。最初の関数 =MAX() は、絶対参照 $B$2:$B$10 を使用して不変範囲の最大値を検索します。関数の後には、B2 から始まる相対参照を持つ、変更中の現在のセルとの比較演算子があります。条件付き書式設定ルールでは、この数式を次のように読み取ります。数値が最大の場合は、その数値が色で強調表示されます。 (MAX 関数によって返される) 最大数値が現在のセルの値と一致すると、数式はブール値 TRUE を返し、対応する塗りつぶし色の書式がすぐに適用されます。列セルのスマートフォーマットのルールは、MIN 関数でも機能します。

連続した 3 つの最小値を強調表示します

さまざまな状況で、値が最も低い 3 つのセルが自動的に強調表示されると便利です。適切な数式を使用した条件付き書式設定は、これに非常に役立ちます。データテーブルの例:

タスクを少し複雑にしてみましょう。最小値のセルには、自動的に赤色の塗りつぶし色が設定されます。 2 番目に低い値を持つ次のセルは、オレンジ色の塗りつぶし色になります。そして、3 番目に低い値は黄色です。

この効果を実現するには、次の手順に従います。

最小の 3 つの値が自動的に異なる色で強調表示されます。

注意！表示されている範囲に同一の最小値が複数含まれている場合、それらすべてが色で強調表示されます。

同様の方法で、最大値を色で強調表示できます。数式内の関数 SMALL を MAXIMUM に変更するだけです。例: =LARGEST($B$2:$B$9,3)=B2

Excel の最小関数と最大関数

関数 =SMALL() は、(最初の引数で) 指定された範囲内で次に小さい値を検索します。キューは関数の第 2 引数で指定します。つまり、2 番目の引数に数値 3 を指定した場合、関数は指定された範囲 $B$2:$B$9 内で 3 番目に小さい値を返します。関数の計算結果が現在のセルと等しい場合、対応する形式 (塗りつぶしの色) がそのセルに割り当てられます。関数の値は選択した範囲の各セルと比較されるため、範囲を表示する式では絶対参照アドレスを使用し、比較演算子 =B2 の後には相対参照アドレスを使用することに注意することが重要です。そして、LARGE 関数は反比例して機能します。

役立つアドバイス! 最小の 3 つの値を異なる色に分割する必要がない場合、同じ範囲に対して 3 つの条件付き書式ルールを作成する必要はありません。 =SMALL($B$2:$B$9;3)>=B2 という演算子記号を 1 つ追加するだけで、数式を少し変更するだけで十分です。つまり、以上です。