この記事では、Excelの特定のテーブルから、基準を使用してn番目に小さいものと基準を使用してn番目に大きいものを検索する方法を学習します。

シナリオ:

簡単に言うと、データ番号の数値を操作しているときに、条件が与えられることがあります。つまり、与えられた5番目に高い値を検索する必要がある場合です。以下に説明するように、Excel関数を使用してこの問題の解決策を簡単に実行できます。

基準でN番目に大きい

問題を解決する方法は?

この記事では、 `link:/ logical-formulas-excel-large-function [LARGEfunction]`を使用する必要があります。次に、これらの関数から式を作成します。ここにテーブルが与えられ、与えられた基準で範囲内でn番目に大きい値を見つける必要があります。

一般式:

{ = LARGE ( IF ( c_range = "value" , range ) , n )}

c_range:基準範囲値:基準一致値範囲:結果配列n:n番目に大きい注:中括弧を手動で配置しないでください。これは配列数式です。#NUMを返すEnterだけでなく、Ctrl + Shift + Enterを使用する必要があります。エラー。

例:

これらはすべて理解しにくいかもしれません。それでは、以下に示す例で実行して、この数式をテストしてみましょう。

ここに、注文日、地域、注文価格を含む注文の詳細があります。

image

東部地域から5番目に高い注文価格を見つける必要があります。ここでは、範囲は、名前付き範囲Excelツールを使用して指定されています。

リージョン(C3:C16)

価格(D3:D16)

まず、LARGE関数を使用して5番目に大きい値を見つけ、次にそれらの5つの値に対して合計演算を実行する必要があります。次に、次の式を使用して合計を取得します

式を使用してください:

{ = LARGE ( IF ( region = G5 , price ) , F5 )}

説明:

  • `link:/ Tips-if-condition-in-excel [IF]`関数は、G5セルの指定されたEast値を持つ地域のすべての値に一致する基準をチェックし、価格範囲から対応するTRUE値を返します。

LARGE(\ {58.41; 303.63; FALSE; 153.34; 82.84; FALSE; 520.01; FALSE; 177; FALSE; FALSE; 57.97; 97.72; FALSE})、F5)

  • link:/ logical-formulas-excel-large-function [LARGE]

関数は、上記のようにすべての価格帯を持つ配列を取得し、以下のスナップショットに示すように、配列から5番目に大きい値を返します。

image

上記のスナップショットのように、数式ボックスに数式を表示できます。 Ctrl + Shift + Enterを使用して結果を取得します。

image

ご覧のとおり、配列数式は、東部地域を持つ5番目に高い価格$ 97.72を返します。

セル参照や名前付き範囲を使用する代わりに、配列、引用符内の条件、n値を関数に直接フィードすることもできます。

式を使用してください:

{ = LARGE ( IF ( C3:C16 = "West" , D3:D16 ) , 3 )}

Ctrl + Shift + Enterを使用

image

表で基準を持つn番目に大きい値を見つけると、数式が正常に機能することがわかります。

基準でN番目に低い

問題を解決する方法は?

この記事では、 `link:/ statistics-formulas-excel-small-function [SMALLfunction]`を使用する必要があります。次に、これらの関数から式を作成します。ここにテーブルが与えられ、与えられた基準で範囲内でn番目に小さい値を見つける必要があります。

一般式:

{ = SMALL ( IF ( c_range = "value" , range ) , n )}

c_range:基準範囲値:基準一致値範囲:結果配列n:n番目に大きい注:中括弧を手動で配置しないでください。これは配列数式です。#NUMを返すEnterだけでなく、Ctrl + Shift + Enterを使用する必要があります。エラー。

例:

これらはすべて理解しにくいかもしれません。それでは、以下に示す例で実行して、この数式をテストしてみましょう。

ここに、注文日、地域、注文価格を含む注文の詳細があります。

image

東部地域からn番目に小さい注文価格を見つける必要があります。ここでは、範囲は、名前付き範囲Excelツールを使用して指定されています。

リージョン(C3:C16)

価格(D3:D16)

まず、SMALL関数を使用して5番目に小さい値または最も小さい値を見つけてから、値に対してルックアップ操作を実行する必要があります。

次に、次の式を使用して合計を取得します

式を使用してください:

{ = SMALL ( IF ( region = G5 , price ) , F5 )}

説明:

  • `link:/ Tips-if-condition-in-excel [IF]`関数は、G5セルの指定されたEast値を持つ地域のすべての値に一致する基準をチェックし、価格範囲から対応するTRUE値を返します。

LARGE(\ {58.41; 303.63; FALSE; 153.34; 82.84; FALSE; 520.01; FALSE; 177; FALSE; FALSE; 57.97; 97.72; FALSE})、F5)

  • link:/ statistics-formulas-excel-small-function [SMALL]

関数は、上記のようにすべての価格帯を持つ配列を取得し、以下のスナップショットに示すように、配列から5番目に大きい値を返します。

image

上記のスナップショットのように、数式ボックスに数式を表示できます。 Ctrl + Shift + Enterを使用して結果を取得します。

image

ご覧のとおり、配列数式は、東部地域を持つ5番目に小さい価格$ 153.34を返します。

セル参照や名前付き範囲を使用する代わりに、配列、引用符内の条件、n値を関数に直接フィードすることもできます。

式を使用してください:

{ = SMALL ( IF ( C3:C16 = "west" , D3:D16 ) , 3 )}

Ctrl + Shift + Enterを使用

image

表に基準を持つn番目に大きい値が見つかった場合、数式が正常に機能することがわかります。

以下に、いくつかの注意事項を示します。

注:

。数式は数値でのみ機能します。

。中括弧を手動で配置しないでください。これは配列数式です。#NUMを返すEnterだけでなく、Ctrl + Shift + Enterを使用する必要があります。

エラー。 。の値は基準値の数より大きくすることはできません。そうでない場合は#NUMを返します。エラー。

。基準が一致しない場合、式はエラーを返します。

。引数配列は、関数以外は同じ長さである必要があります。

基準でn番目に大きいものとExcelの基準でn番目に小さいものを見つける方法に関するこの記事が説明的であることを願っています。 Excelでのルックアップ式に関するその他の記事については、こちらをご覧ください。私たちのブログが気に入ったら、Facebookで友達と共有してください。また、TwitterやFacebookでフォローすることもできます。私たちはあなたから話を聞くのが大好きです、私たちがどのように私たちの仕事を改善、補完、または革新し、あなたのためにそれをより良くすることができるかを私たちに知らせてください。 [email protected]までご連絡ください

関連記事

link:/ summary-excel-sumproduct-function [ExcelでSUMPRODUCT関数を使用する方法]:Excelの複数の配列の値を乗算した後のSUMを返します。

link:/ summary-sum-if-date-is-between [SUM if date is between]:指定された日付または期間の間の値の合計をExcelで返します。

link:/ summary-sum-if-date-is-greater-than-given-date [日付が指定された日付より大きい場合の合計]:*指定された日付または期間の後の値の合計をExcelで返します。

link:/ summary-2-ways-to-sum-by-month-in-excel [Excelで月ごとに合計する2つの方法]:*指定された特定の月内の値の合計をExcelで返します。

link:/ summary-how-to-sum-multiple-columns-with-condition [条件付きで複数の列を合計する方法]:Excelで条件を持つ複数の列にわたる値の合計を返します `link:/ Tips-excel-ワイルドカード[Excelでワイルドカードを使用する方法*] `:Excelでワイルドカードを使用してフレーズに一致するセルをカウントします

人気の記事

link:/ keyboard-formula-shortcuts-50-excel-shortcuts-to-increase-your-productivity [生産性を高めるための50Excelショートカット]:タスクをより速くします。これらの50のショートカットにより、Excelでの作業がさらに高速になります。

link:/ forms-and-functions-introduction-of-vlookup-function [ExcelでVLOOKUP関数を使用する方法]:これは、さまざまな範囲から値を検索するために使用される、Excelで最も使用され人気のある関数の1つです。とシート。 link:/ Tips-countif-in-microsoft-excel [ExcelでCOUNTIF関数を使用する方法]:この驚くべき関数を使用して条件付きの値をカウントします。特定の値をカウントするためにデータをフィルタリングする必要はありません。ダッシュボードを準備するには、Countif関数が不可欠です。

link:/ excel-formula-and-function-excel-sumif-function [ExcelでSUMIF関数を使用する方法]:これはもう1つのダッシュボードの必須関数です。これは、特定の条件で値を合計するのに役立ちます。