社内SEの徒然なる日記

Excelでグループ別の順位を取得する方法 No.2 SUMPRODUCT関数

■ SUMPRODUCT関数

一応、前回(Excelでグループ別の順位を取得する方法 No.1 IFとCOUNTのパズル)の方法でも順位は表示できるのですが、今一つスマートじゃないですね(私の体と一緒。最近、腹を見るのが辛い)。

そこで使えるのがSUMPRODUCT関数です。

...まぁ、あまりにも万能すぎて、却って使いにくいのが欠点ですがね。

■ 普通の使い方

この関数、ExcelヘルプでSUMPRODUCT関数を調べると「範囲または配列の対応する要素の積を合計した結果を返します。」って書いています。

ここから、本来の使い方は配列の横の要素の掛け算の合計(例えば、数量×単価)を求めるモノだってことが分ります。

つまり、こういう事ですね。
Excelのグループ別順位(SUMPRODUCT) (0)

見積書のような、合計金額と明細の組合せみたいな表を作る時に便利そうですね。

ちょっと便利なSUM関数って感じですが、こいつは「論理積を作ってから合計する」って動きをします。この論理積を作るって部分を上手く利用することで、グループ別の順位を表示することが出来ます。

「論理積って何?」って思った方は「世の中には妙な言葉があるんだなー」位に思っていてOKです。知らなくても(色々な意味で)特に困りません。

■ 使い方

最初に、結論を出しちゃいます。

前回と同じ表を作って、SUMPRODUCT関数を仕込みます。
Excelのグループ別順位(SUMPRODUCT) (1)

表示している式はセルD3の内容です。後は、この式を他のセルにコピーしてやれば順位が出てきます。前回と違って、表の並べ替えをしなくても順位が表示されてますね。

少し分解します。
Excelのグループ別順位(SUMPRODUCT) (2)

式の左側(①)は、グループ列の全体を指定して、= で自分の行を指定します。これで「グループ列全体の中から自分(セルB3)と同じ値を対象にする」という指定になります。

式の右側(②)は、順位の基準とする数値を指定します。基本は①と同じですが、演算子は“>=”を使います。これで「金額列全体の中から自分(セルC3)以上の値を対象にする」という指定になります。

上の表で①②の条件に一致するものを確認します。
Excelのグループ別順位(SUMPRODUCT) (3)

最初に出た「論理積」って言葉ですが、要は「複数の条件の全てに一致する」って意味と思って良いと思います。

①と②の両方の条件を満たすのは、下記の赤点線で囲った範囲になります。
Excelのグループ別順位(SUMPRODUCT) (4)

3行目と4行目の2つですね。この2つという数が計算結果(順位)として表示されます。

ただ使うだけなら、ここまでの理解で十分かと思います。

思いますが、今一つ納得がいかなかったので少し内部動作を分析して見ました。そのあたりを、次回は書いていきます。

前回:Excelでグループ別の順位を取得する方法 No.1 IFとCOUNTのパズル
次回:Excelでグループ別の順位を取得する方法 No.3 SUMPRODUCT関数(分析)

投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end ---
スポンサードリンク

PageTop

コメント


管理者にだけ表示を許可する