【Excel2003】データ一覧から売上実績を抜き取りたい

質問

【Excel2003】データ一覧から売上実績を抜き取りたい

画像を添付いたします。
黄色い２つの一覧から青い表を作りたいのです。
黄色い表は、データベースから引っ張ってきたものを貼り付けたものです。
Ａ列、Ｉ列の日付は標準であり文字列ではありません。

例えば、2010/4/20に「あ店」で売り上げたＡ用紙とＢ用紙の合計数を抜き出したいのです。

かならずしも毎日、用紙の売上があるとは限りません。
特にＢ用紙は売上が少なく1つも売れない日があります。
添付画像は都合により４店しか書きませんでしたが、実際は５０店ほどあります。

SUMPRODUCTあたりで出来そうな気もしますがうまくいきません。
ご教授ください。

回答No.1

mshr1962 · ベストアンサー

S4=SUMPRODUCT((TEXT($A$2:$A$30,"0000!/00!/00")*1=S$2)*($B$2:$B$30=$Q4)*($F$2:$F$30))+SUMPRODUCT((TEXT($I$2:$I$30,"0000!/00!/00")*1=S$2)*($J$2:$J$30=$Q4)*($N$2:$N$30))

お礼

早速のご回答ありがとうございました。

実際のデータ用に修正して使わせていただきました。

機会ありましたらまたよろしくお願いいたします。

回答No.5

tom04

こんばんは！
すでに回答は出ていますので、参考程度で・・・

余計なお世話かもしれませんが、店番と店名も抜き出して表示できるようにしてみました。
↓の画像のようにSheet1に作業列を設けます。
作業列H2セルに
=IF(COUNTIF($B$2:B2,B2)=1,ROW(A1),"")
という数式を入れ、オートフィルで下へずぃ～～～！っとコピーします。

そして、Sheet2のA3セルに
=IF(COUNT(Sheet1!$H$2:$H$1000)<ROW(A1),"",INDEX(Sheet1!B$2:B$1000,SMALL(Sheet1!$H$2:$H$1000,ROW(A1))))
という数式を入れ、隣のB3セルまでコピーします。

そして、C3セルに
=IF($A3="","",SUMPRODUCT((Sheet1!$A$2:$A$1000=TEXT(C$1,"yyyymmdd")*1)*(Sheet1!$B$2:$B$1000=$A3)*(Sheet1!$F$2:$F$1000))+SUMPRODUCT((Sheet1!$I$2:$I$1000=TEXT(C$1,"yyyymmdd")*1)*(Sheet1!$N$2:$N$1000)))

（数式はSheet1の1000行目まで対応できるようにしています。）
という数式を入れ、列方向へコピーします。

最後に3行目全てを範囲指定し、最後の列のフィルハンドルで下へコピーすると
画像のような感じになります。

尚、店番・店名があらかじめ入力されているのであれば、大きなお世話になってしまいますので、
読み流してくださいね。

以上、参考になれば幸いです。m(__)m

お礼

おおっ！！店番と店名までも！！
これだと店舗の開閉店があったときまで対処できますね。いいですねーこれは。

ありがとうございます。

回答No.4

web2525

この手の処理ならピポットテーブルを利用する方が手軽にできます

お礼

ご回答ありがとうごいました。

ピボットは考えていなかったですが、こういう処理を何十品もデータ化して印刷するので・・・

回答No.3

keithin

なんだ「標準」てそういう意味ですか。小さくて見えなかったので標準のセルに日付のデータで入れてあるのかと思いましたよ。
集計表の日付セルは年月日で記入してあるんで間違いないでしょうね。

数の例の直し：
=SUMPRODUCT((sheet1!$a$1:$a$100=text(s$2,"yyyymmdd")*1)*(sheet1!$b$1:$b$100=$o3))+SUMPRODUCT((sheet1!$I$1:$I$100=text(s$2,"yyyymmdd")*1)*(sheet1!$J$1:$J$100=$o3))

「sheet1!$a$1:$a$100=text(s$2,"yyyymmdd")」は間違いなので勘違いしないこと。
他の式も同様です。
＃配列側をわざわざ変換して重たくする必要はありません。

お礼

ご回答ありがとうございました。

日付の部分はなんと言い方するのが正しいのか悩みました。
シリアル値というべきだったでしょうか。
とにかく文字列になっていると不都合が起こるということなので、文字列でないことを強調することしか考えてなかったです。

まだ素人の域を脱せませんが、毎日コツコツと知識を増やしております。

回答No.2

keithin

数量の合計を取りたいなら
=SUMPRODUCT((sheet1!$a$1:$a$100=s$2)*(sheet1!$b$1:$b$100=$o3),sheet1!$f$1:$f$100)+SUMPRODUCT((sheet1!$I$1:$I$100=s$2)*(sheet1!$J$1:$J$100=$o3),sheet1!$N$1:$N$100)
　所々がカンマになっているのを見落とさないこと

数なら
=SUMPRODUCT((sheet1!$a$1:$a$100=s$2)*(sheet1!$b$1:$b$100=$o3))+SUMPRODUCT((sheet1!$I$1:$I$100=s$2)*(sheet1!$J$1:$J$100=$o3))

実は例示の通りでないなら
=SUMPRODUCT((sheet1!$a$1:$a$100=s$2)*(sheet1!$b$1:$b$100=$o3)*(sheet1!$e$1:$e$100="Ａ用紙"))+SUMPRODUCT((sheet1!$I$1:$I$100=s$2)*(sheet1!$J$1:$J$100=$o3)*(sheet1!$M$1:$M$100="Ｂ用紙"))

●よくある間違い：
　無理に一個のSUMPRODUCTに詰め込んで式の作成を間違えた
　式のある部分では「A1:A100」にしたのに，別の部分では「E1:E50」のように数を間違えている
　SUMPRODUCTに列:列の式を書いてみたり，間違ったデータ範囲を計算させている

あなたが実際にやってみてミスった式を，手抜きせず恥ずかしがらずご質問に正直にコピーして掲示してみると，どこをどう勘違いしているのかどこを直すのが効率的か，よいアドバイスもすぐに寄せられます。正解を教わってコピーしたら出来たのは，出来るようになったとは言いませんから。