商品別の指定期間別の集計式は？

質問

次の様な表が有ります。
　A　　　　　　　B　　　　　C　　　　D　　　E　　　　F　　　　G　　　　H
日付　　　　　　年月　　商品名　　単価　入庫数　出庫数　　在庫数　在庫額
2007/05/01　H19/05　商品A　　200\　　xxx個　　xx個　　 xxx個 　　xx\
2007/04/30　H19/04　商品A　　200\ 　 xxx個 0個　　　xx個　　xxx\
2007/04/25　H19/04　商品A　　200\　　　0個　　xx個　　　xx個　　 xx\
2007/04/25　H19/04　商品B 2500\　　　0個　　xx個　　　xx個　　xxx\
以下、同類data

B列は表示方式をユーザ定義でgee/mmで、式=$A2によるcopyです。
入出庫日は一応日付順で、最新分は2行目に入力です。
商品名はA以外に多数あり、ランダム入力です。
在庫額は=$D2*$G2です。

(Sheet2に)商品別の、指定期間(年月)毎の各数量の合計を行いたいのです。

ａ.月毎の入庫集計は、例えばH19/1からH19/4までの合計は次式で
=SUMIF($B$2:$B300,">=H19/01",$E$2:$E300)
　　　-SUMIF($B$2:$B300,">=H19/05",$E$2:$E300)

ｂ．同、商品毎の集計は次式で
=SUMIF($C$2:$C300,"商品X",$E$2:$E300)

教えて頂きたいのはこの2式の内容を、１つの式に出来ませんでしょうか？。

回答No.2

imogasi · ベストアンサー

一番良いのは、データーピボットテーブル・・で表を作ることだと思う。
エクセルといえば関数式という人が多いが、能率的ではない。
ーーー
関数で考えるとして
これが意外にこのタイプの質問に書いてないが、
（１）たて１列に商品名の重複の無いリストを作る必要がある。
データーフィルターフィルタオプションの設定ー重複するレコードは無視する,が使えるでしょう
（２）横方向に期間を手入力する。
式を簡単にするため、上下２行を使い、上セルを期間のFROM日付、下セルをToの日付にすると良いと思う。
（３）交差するセルへの件数・金額の算出式だが、条件付き集計では定番の
SUMPRODUCT関数を使う。
例データ　Sheet2
A列　　　B列　　　C列　　　D列
日付 年月 商品名 金額
2007/5/1 H19/05 商品A 200
2007/5/2 H19/06 商品A 201
2007/5/3 H19/07 商品A 202
2007/5/4 H19/08 商品B 1000
2007/4/30 H19/04 商品A 200
2007/4/25 H19/04 商品A 250
2007/4/25 H19/04 商品B 2500
ーー
Sheet1に
A列　　　B列　　　　C列
ー 2007/4/1 2007/5/1
ー 2007/4/30 2007/5/31
商品A 450 603
商品B 2500 1000
B3の式は
=SUMPRODUCT((Sheet2!$A$2:$A$11>=B$1)*(Sheet2!$A$2:$A$11<=B$2)*((Sheet2!$C$2:$C$11=$A3)*(Sheet2!$D$2:$D$11)))
この式を下方向にそして右方向に式を複写する。
＄をつける場所・つけない場所に注意が必要だ

お礼

検証しました。御指導の関数SUMPRODUCT式で正答が出ました。ありがとうございました。
配列式は未だどうして（「*」で繋ぐ）そうなるのか理解できませんが、やってみると容易に正答が出ました。勉強します。

補足

早速の御回答ありがとうございます。
>一番良いのは、データーピボットテーブル・・で表を作ることだと思う
表の作り直しが必要になるのでしょうか？。今からは難しいかと‥‥。「ピボットテーブル」、「配列式」などは聞いた事が有りますが‥‥、使ったことが無く、私にとっては苦手方法ですね。スミマセン(/_;)。

簡潔な、他の方法はありませんでしょうか？。

回答No.1

zap35

＞この2式の内容を、１つの式に
とはどういう意味でしょうか？　日付が「H19/1からH19/4まで」で且つ「商品X」の値を集計したいという意味でよいですか？

複数条件の集計はSUMPRODUCT関数が利用できます
B列のデータの書式がどうなっているかわかりませんので質問文のまま条件式を使うと
=SUMPRODUCT(($B$2:$B300>="H19/01")*($B$2:$B300<"H19/05")*($C$2:$C300="商品X")*($E$2:$E300))
になります。

B列の日付が文字列か、シリアル値かで若干見直す必要がありますが、ご参考まで。

お礼

検証しました。御指導の関数式で正答が出ました。ありがとうございました。
配列式は未だどうして（「*」で繋ぐ）そうなるのか理解できませんが、やってみると容易に正答が出ました。勉強します。

補足

早速の御回答ありがとうございます。
>日付が「H19/1からH19/4まで」で且つ「商品X」の値を集計したいという意味でよいですか？

はい、その通りです。期間中の、商品の値(個数)の合計(集計)を求めたいのです。

A列も、B列もユーザ定義で、A=yyyy/mm/dd，　B=gee/mmです。
御回答頂いた式は配列式ですよね？、配列式はどうも理解し難くて‥‥。日付(期間)を「*」で繋ぐのが‥‥。他の方法（関数式）はありませんでしょうか？。