[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『SUMPRODUCT関数の使用法について』（わかあゆ）
本質問ボードのエリザベスさんの質問、『この場合は何関数？』 [20100717203922] の回答の中で、独覚さんが回答された =SUMPRODUCT((Sheet1!A$2:A$4=A2)*(Sheet1!B$1:D$1=B2)*(Sheet1!B$2:D$4)) が、どうしても理解できずにいます。 同質問のスレで次のようなことを書かせていただきましたが、改めて質問することに しました。 ・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・ 「回答に挑戦しながら技量アップに取り組んでいます。なので、時々、的外れな回答をして 質問者や他の回答者の皆様に迷惑をお掛けしていることが多いのではないかと心配してい ます。お詫びいたします。 トーリス・ガーリさんのINDEX関数の列番号取得にMATCH関数を使用された点（MATCH関数は 縦検索で横検索は・・・と思い込んでいましたので・・・恥ずかしいのですが）を勉強し よかったと感謝の気持ちでういたのですが。。。 独覚さんの回答に感心することしかりです。 質問者の「複数の関数を使用することはまだまだです。」、「sumproductかな？と試しまし たが」にしっかりと向き合った回答・・・感動です。 そこで、独覚さん、飛び込みで失礼だとは思うのですが、 =SUMPRODUCT((Sheet1!A$2:A$4=A2)*(Sheet1!B$1:D$1=B2)*(Sheet1!B$2:D$4)) の解説をお願いできませんか？ SUMPRODUCT関数は「指定した範囲の同じ行の値の積を求め、その合計を求める関数」ですよね？ 使用法はSUMPRODUCT(範囲１，範囲２，・・・，範囲30）で、=SUMPRODUCT（C3:C9,D3:D9）の ような書式の使い方が一般的ですよね。 それが「(Sheet1!A$2:A$4=A2)*(Sheet1!B$1:D$1=B2)*(Sheet1!B$2:D$4)」ですので？？？」 ・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・ どなたか、ご教授、くださいませんか？
>SUMPRODUCT関数は「指定した範囲の同じ行の値の積を求め、その合計を求める関数」ですよね？
◆その通りですが、この式はその応用です
１）セルに、=SUMPRODUCT((Sheet1!A$2:A$4=A2)*(Sheet1!B$1:D$1=B2)*(Sheet1!B$2:D$4))と入力して、
２）そのセルを指定して、数式バーの、=SUMPRODUCT((Sheet1!A$2:A$4=A2)*(Sheet1!B$1:D$1=B2)*(Sheet1!B$2:D$4))の部分の
３）「Sheet1!A$2:A$4=A2」を指定して、「F9」をクリックしてください
４）{FALSE;TRUE;TRUE;FALSE}になります、A2と同じなら「TRUE」になります
５）次に、「Sheet1!B$1:D$1=B2」を指定して、「F9」をクリックすると
６）{FALSE;TRUE;FALSE;TRUE}になったとします５）
７）FALSEは「0」、TRUEは「1」と同じですですので、
８）すなわち、{0;1;1;0}*{0;1;0;1}={0;1;0;0}となり、これが、条件1,2を満足するセルになります
９）これに、Sheet1!B$2:D$4 の数値を掛けると、条件を満足する合計ということになります
(Maron)
Maronさん、ご教示ありがとうございました。
{}って配列？
以前にも配列を勉強してみましたが、なかなか奥が深くて。。
SUMPRODUCTが配列の考え方を受け入れてくれるなんて！
勉強になりました
(わかあゆ)
＞勉強になりました 本当に勉強になったですか？ それならいいのですが。。。
Maronさん こんばんは 半平太です。
なんか、ご説明が全体的に不整合に感じます。 行の数、列の数、行の方向、列の方向、得られる配列の状態、などですけど。
(半平太) 2010/07/18 20:08
＞使用法はSUMPRODUCT(範囲１，範囲２，・・・，範囲30）で、=SUMPRODUCT（C3:C9,D3:D9） ＞のような書式の使い方が一般的ですよね。 １．そうなんですが、今回は第二引数がありません。
なので、SUMPRODUCT自体は乗算をしておらず、足し算だけやっています。 従って、SUMとおんなじとも云えます。 ただし、SUMなら、配列として入力する必要があります→[Ctrl]+[Shift]+[Enter]で数式を入力。
例 (1) H2セル =SUMPRODUCT((A$2:A$4=F2)*(B$1:D$1=G2)*B$2:D$4) (2) I2セル = SUM((A$2:A$4=F2)*(B$1:D$1=G2)*B$2:D$4)
行 ____A____ _B_ _C_ _D_ _E_ ____F____ ___G___ __H__ _I_ 1 商品名 S M L 商品名 サイズ 金額 2 オレンジ 100 120 130 オレンジ M 120 120 3 ケーキ 200 250 300 ケーキ L 300 300 4 トマト 50 70 80 トマト S 50 50
※ 「乗算」はと云うと、第一引数が自分自身で行っています。
２．第一引数の「(A$2:A$4=F2)*(B$1:D$1=G2)*B$2:D$4」の形は、 「3行1列」*「1行3列」*「3行3列」となっており、不ぞろいな行列が計算対象になっています。
※ついでですが、これらがSUMPRODUCTの複数の引数として分かれていたら、不ぞろいなのでエラーになります。
上の式がどんな計算過程を辿るかと云うと
{TRUE;FALSE;FALSE}*{FALSE,TRUE,FALSE}*{100,120,130;200,250,300;50,70,80}
↓
{0,1,0;0,0,0;0,0,0}*{100,120,130;200,250,300;50,70,80} ※「3行3列」*「3行3列」の形になる。
↓
{0,120,0;0,0,0;0,0,0}
つまり、条件が縦横合致した位置に1が立ち、それに対応する位置の金額が掛けられる事になります。
(半平太) 2010/07/18 20:33
半平太さん、ありがとうございました。 う～ん、難しいです。配列は。。。 Maronさんの説明で分かったつもりでいたのですけれど？？？ 再度、じっくり読み込み、理解に努めます。 (わかあゆ)
こんにちは。 配列数式がイメージしにくいときは、実際にセルに書き出すと分かりやすいですよ。
○【Sheet2】シート ※「_」セルは未入力または"" [R/C] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [1] 商品名 サイズ 金額 _ _ FALSE TRUE FALSE _ _ _ _ _ _ _ _ [2] オレンジ M 120 _ TRUE 0 1 0 _ 100 120 130 _ 0 120 0 [3] ケーキ L 300 _ FALSE 0 0 0 _ 200 250 300 _ 0 0 0 [4] トマト S 50 _ FALSE 0 0 0 _ 50 70 80 _ 0 0 0
E2:E4 {=(Sheet1!A$2:A$4=A2)} (3行1列) ←E2:E4を選択後、数式を入力して、
Ctrl+Shit+Enterで確定（配列数式）
F1:H1 {=(Sheet1!B$1:D$1=B2)} (1行3列) ←F1:H1を選択後、以下略
F2:H4 {=(Sheet1!A$2:A$4=A2)*(Sheet1!B$1:D$1=B2)} (3行3列) ←F2:H4を選択後、以下略
J2:L4 {=(Sheet1!B$2:D$4)} (3行3列) ←J2:L4を選択後、以下略
N2:P4 {=(Sheet1!A$2:A$4=A2)*(Sheet1!B$1:D$1=B2)*(Sheet1!B$2:D$4)} (3行3列) ←N2:P4を選択後、以下略
（コタ）
コタさん、見えないものを見えるようにする、理解には力を発揮しますね。 もやもやとしていたものが、少しずつ晴れてきました。 ありがとうございました。 (わかあゆ)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.