image

前回の記事では、上位または下位のN値を合計する方法を学びました。

この記事では、上位または下位のN値を基準で合計しようとします。

基準を使用したTOPN値の合計

問題を解決する方法は？

この記事では、 `link：/ summary-excel-sumproduct-function [SUMPRODUCT関数]`を使用する必要があります。次に、これらの関数から式を作成します。ここでは、範囲と基準が与えられています。範囲内の上位5つの値を取得し、指定された基準に基づいて値の合計を取得する必要があります。

一般式：

= SUMPRODUCT ( LARGE ( (list = criteria) * (range), { 1 , 2 , .... ,n } } )

リスト：基準リスト基準：一致する基準範囲：値の範囲値：上位3つの値を検索する場合のように、コンマで区切られた数値は、\ {1、2、3}を使用します。

例：

ここに、A1：D50のデータセット値があります。

image

まず、都市「ボストン」に一致するLARGE関数を使用して上位5つの値を見つけ、それらの5つの値に対して合計演算を実行する必要があります。次に、次の式を使用して合計を取得します_式を使用します：_

= SUMPRODUCT ( LARGE ( ( City = "Boston" ) * (quantity) , { 1 , 2 , 3 , 4 , 5 } ) )

説明：

  • 市の「ボストン」は、上記の市の範囲と一致します。これは、trueとfalseの配列を返します。

  • `link：/ logical-formulas-excel-large-function [The LARGE]`関数は、数量範囲から上位5つの数値を返し、配列をSUMPRODUCT関数に返します。

SUMPRODUCT \ {193、149、138、134、123} * SUMPRODUCT関数は、上位5つの値の配列を取得し、上位5つの数値の配列はそれらの数値の合計を返します。

image

ここでは、都市と数量の範囲が名前付き範囲として指定されています。 Enterキーを押して、上位5つの数値の合計を取得します。

image

上記のスナップショットでわかるように、その合計は737です。値の合計は193 + 149 + 138 + 134 + 123 = 737です。

Excelフィルターオプションを使用して、データセット内の上記の値を確認できます。

City＆quantityヘッダーにフィルターを適用し、表示されるCityヘッダーの矢印ボタンをクリックします。以下の手順に従ってください。

image

手順：

。 Cityヘッダーセルを選択します。ショートカットCtrl + Shift + Lを使用してフィルターを適用します。フィルタオプションとして表示される矢印をクリックします。

。 （すべて選択）オプションを選択します。

。ボストンの都市のみを選択します。

。ここで数量ヘッダーを選択します。

。リストを最大から最小に並べ替えると、数式を使用して計算した上位5つの値がすべて表示されます。

上記のgifでわかるように、指定された基準に一致する5つの値すべて。これは、数式がこれらの値のカウントを取得するために正常に機能することも意味します

大きなN個の数字

上記のプロセスは、上からいくつかの数値の合計を計算するために使用されます。しかし、長い範囲でn個の（大きい）値を計算するには。

次の式を使用します：

= SUMPRODUCT ( LARGE ( ( City = "Boston" ) * (quantity), ROW ( INDIRECT ( "1:10" ) )

ここでは、1から10の配列を取得して上位10個の値の合計を生成します\ {1; 2; 3; 4; 5; 6; 7 ; 8; 9; 10} ROW＆INDIRECTExcel関数を使用します。

image

ここに、1147になる上位10の数値の合計があります。

基準を使用した下位N値の合計

問題を解決する方法は？

この記事では、 `link：/ summary-excel-sumproduct-function [SUMPRODUCT関数]`を使用する必要があります。次に、これらの関数から式を作成します。ここでは範囲が与えられており、範囲内の5つの値を下にして、値の合計を取得する必要があります。

一般式：

{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , { 1 , 2 , 3 , 4 , 5 } ) ) }

範囲：値の範囲値：下の3つの値を検索する場合のように、コンマで区切られた数値は、\ {1、2、3}を使用します。

例：

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

ここでは、A1：D50からの値の範囲があります。

image

ここでは、都市と数量の範囲は、名前付き範囲のExcelツールを使用して指定されています。

まず、基準に一致するSMALL関数を使用して下位5つの値を見つけ、それらの5つの値に対して合計演算を実行する必要があります。次に、次の式を使用して合計を取得します。式を使用します。

{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , { 1 , 2 , 3 , 4 , 5 } ) ) }

中括弧を手動で使用しないでください。 Enterだけの代わりにCtrlShift + Enterを使用して中括弧を適用します。

説明：

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

IF関数を使用した関数は、City “Boston”に一致する下位5つの数値を返し、配列をSUM関数に返します。

SUM（\ {23、27、28、28、30}））

  • SUM関数は、下位5つの値の配列を取得します。下位5つの数値の配列は、CTRL + SHIFT + ENTERで使用された数値の合計を返します。

image

ここでは、都市と数量の範囲が名前付き範囲として指定されています。これは配列数式であるため、Ctrl + Shift + Enterを押して、下位5つの数値の合計を取得します。

image

上記のスナップショットでわかるように、合計は136です。

上記のプロセスは、下からいくつかの数値の合計を計算するために使用されます。しかし、長い範囲でn個の（大きい）値を計算するには。

次の式を使用します：

{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , ROW ( INDIRECT ("1:10") ) ) ) }

中括弧を手動で使用しないでください。 Enterを使用する代わりに、Ctrl + Shift + Enterを使用します。

ここでは、1から10の配列を取得して下位10個の値の合計を生成します\ {1; 2; 3; 4; 5; 6; 7 ; 8; 9; 10} ROW＆INDIRECTExcel関数を使用します。

image

ここに、155になる下位10個の数値の合計があります。

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

注：

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

。数式は、ルックアップテーブルに重複がない場合にのみ機能します。 SUMPRODUCT関数は、数値以外の値（text abcなど）とエラー値（#NUM！、＃NULL！など）をnull値と見なします。

。 SUMPRODUCT関数は、論理値TRUEを1、Falseを0と見なします。

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

Excelで基準を使用して上位5つの値または下位5つの値の合計を返す方法に関するこの記事が説明的であることを願っています。 SUMPRODUCT関数に関するその他の記事はこちらからご覧ください。以下のコメントボックスで質問を共有してください。お手伝いさせていただきます。

私たちのブログが気に入ったら、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でワイルドカードを使用してフレーズに一致するセルをカウントします

人気の記事

生産性を高めるための50Excelショートカット

link：/ excel-generals-how-to-edit-a-dropdown-list-in-microsoft-excel [ドロップダウンリストを編集する]

link：/ excel-range-name-absolute-reference-in-excel [Excelでの絶対参照]

link：/ Tips-conditional-formatting-with-if-statement [条件付き書式の場合]

ワイルドカードを使用する場合

link：/ lookup-formulas-vlookup-by-date-in-excel [Vlookup by date]

link：/ Tips-inches-to-feet [Excel2016でインチをフィートとインチに変換]

link：/ excel-text-editing-and-format-join-first-and-last-name-in-excel [Excelで姓名を結合]

link：/ counting-count-cells-which-match-other-a-or-b [AまたはBのいずれかに一致するセルを数える]