ゴールデンウイークに「達人に学ぶSQL徹底指南書」（ミック著）を読んでいる。非常に評価の高い書籍だけれども、私にはレベルが高くてわからない・・・。そもそもSQLの初心者レベルが読む本ではなかった( ;∀;)
とりあえず、最後まで読み通した（眺めたといった方がいいかも）
もうすこしSQLが書けるようになったらもう一回読んでみよう。。。
で、その本の最初の方に「自己結合の使い方」の中で「ランキングの出し方」というくだりがあった。同じテーブル同士を結合し、自分より大きいレコードの数を数える・・・という感じのもの。・・・なにか記憶に引っかかるものがある。
そうだ！エクセルの関数、SUMPRODUCTがこんな感じだったかも。
だれかが作ったエクセルでグループが入り混じった表内でグループごとの順位を出す欄にSUMPRODUCT関数が使われていた。初めて見た関数でネットで調べたところ、意味が理解できなくて「わからん。この関数は一生使わない」を心に決めたものだった。（たしか5年前くらい）
今ならわかるかもしれない。と、SUMPRODUCT関数をもう一度勉強。
SUMPRODUCT関数を理解する
関数の説明：範囲または配列の対応する要素の積を合計した結果を返します。
引数として指定したセル範囲を配列として受け取るらしい。
もう少しわかりやすく言い換えると、
引数として指定した配列の対応する要素間の積をまず計算し、さらにその和を返す
（引用：http://www.eurus.dti.ne.jp/yoneyama/Excel/kansu/sumproduct.htm）
なお、配列とは
文字列や数値などの値を入れておく入れ物が何個か連続して連なったもの。カラーボックスの長いの、みたいなイメージ？
以下の文では配列を｛｝で表し、配列内の要素は｛1,2,3,4,5,6｝とカッコ内にカンマで区切って表します。左から１番目の要素、2番目の要素という言い方をします(本当は配列は要素番号ゼロから始まりますが、この文中ではわかりやすいように要素番号を１から始めます)
この表でやってみる。
①総売り上げを一発で出す（SUMPRODUCT関数の基本のキ！）
②「カテゴリー=果物」の売り上げを一発で出す（ここでカンマと＊のからくりに気が付く）
③「市場=東京」かつ「カテゴリー=果物」の売り上げを一発で出す
④「市場=東京」かつ「カテゴリー=果物」の個数を一発で出す
⑤「市場」毎の売り上げの順位を出す （ここが目玉。これがやりたい）
⑥おまけ：各々の売り上げを単価で割って一発で販売個数を出す
◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇
①総売り上げを一発で出す
すべての行の「単価（D列）」×「数量（E列）」の合計を出す。
下図の青枠のように指定します。
= SUMPRODUCT ( D2:D9 , E2:E9 )
配列１単価｛100,200,200,130,150,100,160,200｝
配列２数量｛15,8,12,20,20,45,30,35｝
配列１の要素１×配列２の要素１（100×15=150)、
配列１の要素２×配列２の要素２（200×8=1600)、
・・・
配列１の要素８×配列２の要素８（200×35=7000)、
で、そのすべての結果を足す（150+1600＋・・・+7000）。
で、返る答えは27400。
と理解した。
②「カテゴリー=果物」の売り上げを一発で出す
このように指定するらしい。
=SUMPRODUCT ( ( B2:B9 = ”果物” ) ＊ ( F2:F9 ) )
ここで疑問が。なぜ急に「＊」が出てくる？？？
なんで「 , 」じゃなくなるの？
関数のヘルプを見ると、
その他の算術演算を実行するには
通常どおり SUMPRODUCT を使用しますが、コンマは、配列引数と目的の算術演算子 (*、/、+、-) で区切ります。 すべての操作が実行されると、結果は通常どおり合計されます。
とある。よくわからないが、カンマの代わりに算術演算子でもいいらしい。
ためしに①の式を書き換えてみた。
＝SUMPRODUCT ( ( D2:D9 ) ＊ ( E2:E9 ) )
カンマを＊に変えても同じ答えになった。
じゃあ②の式の＊をカンマにしたらどうなる？
=SUMPRODUCT ( ( B2:B9 = ”果物” ) , ( F2:F9 ) )
答えはゼロ。
②の式を分解してみる。
=SUMPRODUCT ( ( B2:B9 = ”果物” ) , ( F2:F9 ) )
⇒( B2:B9 = ”果物” ) → B２は果物か？B３は果物か？
→ {True,True,Ture,Fales,Ture,Fales,Fales,Fales}
⇒( F2:F9 ) → {1500,1600,2400,2600,3000,4500,4800,7000}
True × 1500 はTrueが文字列で無効なので0になってしまう。
1をかけることでTrue/Falseを数値化できることを利用し（Trueが1、Falseが０になる０）式を以下のように変えてみる。
=SUMPRODUCT ( ( B2:B9 = ”果物” ) ×1 , ( F2:F9 ) )
そうすると答えがちゃんと返る。
要は、カンマを ＊ にすることで ×１を省略できている、と解釈。
なんとなく納得。納得したので気持ち悪さが消え、これで私はSUMPRODUCT関数を使えるようになるでしょう。（腑に落ちないところがあり気持ち悪いと使えない人）
配列１を果物ならばTrueを数値化し、Trueならば１、Falseならば0とすると
配列１果物か｛1,1,1,0,1,0,0,0｝
配列２売上 {1500,1600,2400,2600,3000,4500,4800,7000}
配列１の要素１×配列２の要素１（1×1500=1500)、
配列１の要素２×配列２の要素２（1×1600=1600)、
・・・
配列１の要素８×配列２の要素８（0×7000=0)、
で、そのすべての結果を足す（1500+1600＋2400+0+3000+0+0+0）。
答え8500が返る。
③「市場=東京」かつ「カテゴリー=果物」の売り上げを一発で出す
以下の様に指定。
=SUMPRPDUCT
( ($A$2:$A$9=”東京” )＊( $B$2:$B$9=”果物” )＊( $F$2:$F$9 ) )
順を追って分解する
=SUMPRODUC( 配列１* 配列２ * 配列３)
配列１の要素１×配列２の要素１×配列3の要素１
→ 東京 ; 果物 ; 1500 → True(1) × True(1) × 1500 = 1500
配列１の要素2×配列２の要素2×配列3の要素
→ 大阪 ; 果物 ; 1600 → False(0) × True(1) × 1600 = 0
・・・
配列１の要素8×配列２の要素8×配列3の要素8
→ 大阪 ; 野菜 ; 7000 → False(0) × False(0) × 7000 = 0
そのすべての結果を足す → 1500+0＋2400+0+3000+0+0+0
答え6900が返る。
④「市場=東京」かつ「カテゴリー=果物」の個数を一発で出す
個数をカウントする場合は以下のように指定するとでます
=SUMPRODUCT
( ($A$2:$A$9=”東京”) ＊ ($B$2:$B$9=”果物”) )
配列１の要素１×配列２の要素１
→ 東京 ; 果物 → True(1) × True(1) = 1
配列１の要素2×配列２の要素2
→ 大阪 ; 果物 → False(0) × True(1) = 0
・・・
配列１の要素8×配列２の要素
→ 大阪 ; 野菜 → False(0) × False(0)= 0
そのすべての結果を足す → 1+0＋1+0+1+0+0+0
答え3が返る。
なお、セルの値を利用し、コピー対応したい場合は下図のようにするとよいです。
C・D列12行目に入力した式をC・D列13~15行目にコピーすることで市場・カテゴリーのそれぞれの組み合わせの結果を出力できます。
セルC12カウント
=SUMPRODUCT
( ($A$2:$A$9=A12) ＊ ($B$2:$B$9=B12) )
セルD12売上計
=SUMPRODUCT
( ($A$2:$A$9=A12) ＊ ($B$2:$B$9=B12) ＊ ($F$2:$F$9) )
⑤「市場」毎の売り上げの順位を出す
さて、本題。市場グループごとに売り上げ順位をG列に出していきます。
セルG2に以下のように入力します。
= SUMPRODUCT ( ( $A$2 : $A$9 = A2 ) ＊ ( $F$2 : $F$9 >= F2 ) )
順を追って分解していきます。
セルG2の式について分解
= SUMPRODUCT ( ( $A$2 : $A$9 = 東京 ) ＊ ( $F$2 : $F$9 >= 1000 ) )
一つ目の配列：
$A$2 : $A$9 = A3 → {東京,大阪,東京,大阪,東京,大阪,東京,大阪,} が A2=東京 である
→｛True,False,True,False,True,False,True,False} →｛1,0,1,0,1,0,1,0}
二つ目の配列：
$F$2 : $F$9 >= F2 → {1000,6000,6800,3250,5250,2000,2400,7000} が F2=1000 以上 である
→｛True,True,True,True,True,True,True,True} →｛1,1,1,1,1,1,1,1}
一つ目の配列の要素と二つ目の配列の要素をかける
配列1 ｛ 1 , 0 , 1 , 0 , 1 , 0 , 1 , 0 }
× × × × × × × ×
配列2 ｛ 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 }
↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓
結果 ｛ 1 , 0 , 1 , 0 , 1 , 0 , 1 , 0 }
結果の和を出す → ４
「東京」かつセルF2の値「1000」以上に該当するセルの個数になります。
その個数が4個。つまり東京市場の中で売り上げが4位という結果にマッチします。
セルG3について分解
= SUMPRODUCT ( ( $A$2 : $A$9 = A3 ) * ( $F$2 : $F$9 >= F3 ) )
= SUMPRODUCT ( ( $A$2 : $A$9 = 大阪 ) * ( $F$2 : $F$9 >= 6000 ) )
一つ目の配列：
$A$2 : $A$9 = A3 → {東京,大阪,東京,大阪,東京,大阪,東京,大阪} が A3=大阪 である
→｛False,True,False,True,False,True,False,True} →｛0,1,0,1,0,1,0,1}
二つ目の配列：
$F$2 : $F$9 >= F3 → {1000,6000,6800,3250,5250,2000,2400,7000} が F3=6000 以上である
→｛False,True,True,False,False,False,False,True} →｛0,1,1,0,0,0,0,1}
一つ目の配列の要素と二つ目の配列の要素をかける
配列1 ｛ 0 , 1 , 0 , 1 , 0 , 1 , 0 , 1 }
× × × × × × × ×
配列2 ｛ 0 , 1 , 1 , 0 , 0 , 0 , 0 , 1 }
↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓
結果 ｛ 0 , 1 , 0 , 0 , 0 , 0 , 0 , 1 }
結果の和を出す → ２
「大阪」かつ「6000」以上に該当するセルの個数は2個。つまり大阪市場の中で売り上げが2位。
セルG4~G9も同様に計算できる。
⑥おまけ：各々の売り上げを単価で割って一発で販売個数を出す
あまり使うことがあるようには思えないが、演算子が使えるというから割り算をやってみる。
=SUMPRODUCT(F2:F9 / D2:D9)
F2:F9 は売上、D2:D9 は単価。配列1売り上げ・配列2単価を各要素で割り算し(例：2行目は1000÷100=10）その結果の和を出す。つまり売上個数の合計を出す。
結果 204。はい、できました。
◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇◇
以上。自分が納得するまでSUMPRODACT関数を掘り下げてみました。
私は以下のように解釈しました。
SUMPRODUCT関数は引数に配列をとり、以下のように指定。
SUMPRODUCT（配列1,配列2,…）
配列はセル範囲で指定可。例：SUMPRODUCT(F2:F9 , D2:D9)
配列と配列の区切りのカンマは掛け算を表す「*」で置き換え可。
配列の解にTrue/Falseを返す場合（条件に一致するかどうかを返す場合）はカンマではなく「*」を使うことでTrue/Falseを数値化することができる（Trueが1、Falseが０となる）。
①の一発で合計を出す、と⑤のグループごとの順位 以外②～④はSUMIF、COUNTIFを使う方が分かりやすいのでSUMPRODUCT関数を使うことはないと思う。
条件付きで順位を出せるのは便利なので、機会があれば使っていきたい。
だいぶ長くなってしまったけど、もし他にもSUMPRODUCT関数でハテナと思ってる方がいたら、わかりやすく説明できてたら良いなあと思う。
きっかけとなったSQLの考え方とは方向性がだいぶ違うことも分かった。。。
2020.5.8.修正：勝手に注意表記となって下付けになってしまうなどの文字化け？が出現したので アスタリスクを全角にしたり×の表記にしたり、エクセル式中に空白や折り返しをはさみました。このまま式をコピーすると使えない場合がありますのでご注意ください。全角アスタリスクや×はエクセル関数にする場合は半角アスタリスク（*）とし、空白や折り返しは削除してください。
コメント