【Excel2003】任意の日付だけの合計数を求めたい。

質問

図が小さくて分かりにくいかもしれません。

日付、店名、商品名、数量を抽出しています。
店名はＡ店～Ｅ店。（実際は５０店くらいある）
商品名は「ああ」と「いい」の２種類だけ。

求めたいのは、3/1～3/3、3/4～3/6、3/7～・・・（3日刻みで月末まで）のそれぞれの店の数量合計です。
日付の区切りがなければSUMIFで簡単に出来てしまうのですが、日付で区切ると分からなくなってしまいました。
なにかフラグを立ててやってみようとしたのですが、どうフラグを立てらいいのか知恵が沸きません。

なにかいい方法はないものでしょうか？
よろしくお願いします。

回答No.1

mshr1962 · ベストアンサー

数式で集計するなら配列数式を使うか、SUMPRODUCT関数です。
G5=SUMPRODUCT(($A$2:$A10000>=20100301)*($A$2:$A10000<=20100303)*($B$2:$B$10000=$F5)*($D$2:$D$10000))

商品別にするなら
G5=SUMPRODUCT(($A$2:$A10000>=20100301)*($A$2:$A10000<=20100303)*($B$2:$B$10000=$F5)*($C$2:$C$10000="ああ")*($D$2:$D$10000))

数式を使わないなら、ピボットテーブルで集計可能です。
行の見出しを日付と店名、データを数量にして作成するんですが
日付を3日毎にグループ化させることでお望みの形になります。
商品別にするなら商品名を列見出しにすればいいでしょう。

お礼

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

うん。これは素晴らしいなぁ～！

回答No.4

KURUMITO

回答Ｎｏ3です。新しく表を作ってＮｏ3でコピーして貼り付けてみましたが問題なく作動します。式が長いのでミスしている可能性がありますね。コピーして使ってみてください。

お礼

ありがとうございました。
分析ツールのアドオンが必要だったのですね。

回答No.3

KURUMITO

お示しの表で気になる点があります。Ａ列の日付の表示ですが20100301のようになっていますがこれは日付として2010/3/1のように入力されたものをセルの表示形式でyyyymmddとしていることによるものでしょうか？
もしもそうでないとしたら日付としての数値(シリアル値）ではなく文字列扱いになりますね。ここではお示しのデータがシリアル値であるとして説明します。
最も分かりやすい方法は次のようにすることでしょう。
Ｅ列を作業列としてＥ２セルには次の式を入力して下方にオートフィルドラッグします。
=A2&B2
次に求めたい表ですが、例えば望みの月のデータを表示させるとしてその月をＧ１セルに今年の３月でしたら2010/3/1のように入力してセルの表示形式で　m月　として３月と表示させます。
次に３日間ごとに日付ですがＨ２セルに次の式を入力して右横方向にオートフィルドラッグします。

=IF(MONTH(DATE(YEAR($G$1),MONTH($G$1),DAY($G$1)-1+COLUMN(A1)*3-2))=MONTH($G$1),MONTH($G$1)&"/"&COLUMN(A1)*3-2&" ～","")

これでその月の末日までの日付が３日間隔で表示されます。
次にＧ３セルから下方には店名を入力します。
Ｈ３セルには次の式を入力してＲ３セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(OR(H$2="",$G3=""),"",(IF(DATE(YEAR($G$1),MONTH($G$1),DAY($G$1)+COLUMN(A1)*3-3)<=EOMONTH($G$1,0),SUMIF($E:$E,DATE(YEAR($G$1),MONTH($G$1),DAY($G$1)+COLUMN(A1)*3-3)&$G3,$D:$D),0)+IF(DATE(YEAR($G$1),MONTH($G$1),DAY($G$1)+COLUMN(A1)*3-2)<=EOMONTH($G$1,0),SUMIF($E:$E,DATE(YEAR($G$1),MONTH($G$1),DAY($G$1)+COLUMN(A1)*3-2)&$G3,$D:$D),0)+IF(DATE(YEAR($G$1),MONTH($G$1),DAY($G$1)+COLUMN(A1)*3-1)<=EOMONTH($G$1,0),SUMIF($E:$E,DATE(YEAR($G$1),MONTH($G$1),DAY($G$1)+COLUMN(A1)*3-1)&$G3,$D:$D),0)))

これで該当月におけるお店ごとの集計が３日間隔で集計されて表示されます。

Ａ列における表示がシリアル値でなく文字列になっている場合にはシリアル値に直すことが必要です。その節は追加ご質問ください。

補足

ありがとうございます。

でも、おかしいなぁ～
#NAME?と表示されます。
もうちょっと研究してみますね。

※Ａ列はシリアル値ですので大丈夫です。

回答No.2

tom04

こんばんは！
一例です。
店舗数が50店舗くらいあるということなので
店舗も一緒に表示するようにしてみました。
余計なお世話かもしれませんが、勝手に↓の画像のように
表示させるSheet2の表の配置を変えさせてもらいました。
日付は全てシリアル値が入っているものとします。
今回は商品名は無視してもよいみたいですね。

左側のSheet1に作業用の列を設けています。
作業列E2セルに
=IF(COUNTIF($B$2:B2,B2)=1,ROW(A1),"")
という数式を入れオートフィルで下へずぃ～～～！っとコピーします。

そして、Sheet2のA4セルに
=IF(COUNT(Sheet1!$E$2:$E$1000)<ROW(A1),"",INDEX(Sheet1!$B$2:$B$1000,SMALL(Sheet1!$E$2:$E$1000,ROW(A1))))

B4セルに
=IF($A4="","",SUMPRODUCT((Sheet1!$A$2:$A$1000>=B$1)*(Sheet1!$A$2:$A$1000<=B$3)*(Sheet1!$B$2:$B$1000=$A4)*(Sheet1!$D$2:$D$1000)))
としてB4セルを列方向にコピーします。
最後にA4～B4セルをコピーした最後の列までを範囲指定し、
この最後の列のフィルハンドルで下へコピーすると
画像のような感じになります。
尚、数式はSheet1の1000行目まで対応できるようにしています。

以上、参考になれば良いのですが
他に良い方法があれば読み流してくださいね。m(__)m