Excelで15日締めと月末締めの集計を出したい

質問

下記のようなデータがあります。

日付　　　　金額　　　摘要
2009/9/1　　100　　○○
2009/9/5　　200　　△△
2009/9/16　-100　　××

これを別シートに下記のように集計したいと思っています。

日付　　　　金額　　摘要
1～15　　　300　　　○○,△△
16～末　　-100　　　××

注意点として、
・31日で終わる月も28日で終わる月も後半は末日まで集計。
・元のファイルを作っておき、月が替わるごとにファイルをコピーして使うようにしたい。
・実際に使う人は入力しかしない。毎月式を修正するようなことはしたくない。
・今のところデータはその月のデータのみを入れる予定。

日付で2009/9/1～2009/15までの集計方法というのがよくわからず、
また10月になった場合はどうなるのか、といったこともよくわかりません。
よろしくお願いします。

回答No.1

kybo · ベストアンサー

月が変わるたびに別ファイルにするのであれば、末日が何日か調べなくても出来るので、

簡単な方法としては、

D2に
=DAY(A2)

の様に入れ、

15日以下
=SUMIF(Sheet1!D2:D11,"<=15",Sheet1!B2:B11)

15日以上
=SUMIF(Sheet1!D2:D11,">15",Sheet1!B2:B11)

のようにして集計します。

15日以下
=SUMPRODUCT((DAY(Sheet1!A2:A11)<=15)*Sheet1!B2:B11)

15日以上
=SUMPRODUCT((DAY(Sheet1!A2:A11)>15)*Sheet1!B2:B11)

補助列がいやな場合は、

お礼

回答ありがとうございます。
どちらでもできました。補助列なしの方を使ってみます。

回答No.7

tom04

No.5です！
たびたびすみません。

前回、Sheet2の合計金額の数式を書いていませんでした。

Sheet2のB2セルは
=SUMPRODUCT((Sheet1!$A$2:$A$100<>"")*(Sheet1!$D$2:$D$100))

B3セルは
=SUMPRODUCT((Sheet1!$B$2:$B$100<>"")*(Sheet1!$D$2:$D$100))

としてみてください。
どうも何度も失礼しました。m(__)m

回答No.6

hallo-2007

>・元のファイルを作っておき、月が替わるごとにファイルをコピーして使うようにしたい。
この様な使い方はしないほうが良いですよ。あくまでの一つのファイルで集計しましょう。手間がかかりません。
例えば
集計シート
1　　2009　年　 9　　月
2　月初～15日
3　16日～月末
データシート
1　日付　　　　金額　　　摘要
2　2009/9/1　　100　　○○
3　2009/9/5　　200　　△△
4　2009/9/16　-100　　××
・・・と集計の結果を表示するシートとデータを入れるシートを準備します。
集計の結果のシート
A1セルに　2009　　C1セルに　9　と集計したい年と月を入れます。
初日から15日の金額の合計は
=SUMIF(データ!A:A,"<="&DATE(A1,C1,15),データ!B:B)-SUMIF(データ!A:A,"<"&DATE(A1,C1,1),データB:B)
で出ます。SUMIF関数で、15日以下の合計から1日未満の合計を引きます。
同様に
=SUMIF(データ!A:A,"<"&DATE(A1,C1+1,1),データ!B:B)-SUMIF(データ!A:A,"<"&DATE(A1,C1,16),データ!B:B)
とします。
DATE(A1,C1+1,1)は翌月の初日ですの翌月の初日以下で求めます。
A1、C1の年と月を変更すれば瞬時に結果が出ます。

お礼

回答ありがとうございます。
実は、そこからさらに年度集計にリンクを貼っています。
年度も含めて同じシートにすると、
データ入力用、月別集計用の２つがセットで12ヶ月分と
年度のシートが同じファイルになりますので、
慣れていない人にはややこしいかなと思い、今回別のファイルで作ることにしました。

回答No.5

tom04

こんばんは！
すでに回答は出ていますので
参考程度で目を通してみてください。

↓の画像で説明させていただくと
Sheet1のA列に列挿入を2度行い（元のA列がC列になります）
A・B列を作業列としています。
作業列が目障りであれば数式を入れた後に、非表示にしてください。

Sheet1のA2セルに
=IF(C2="","",IF(DAY(C2)<=15,ROW(A1),""))

B2セルに
=IF(AND(DAY(C2)>=16,C2<=EOMONTH($C$2,0)),ROW(A1),"")
という数式を入れて、A2・B2セルを範囲指定した後に
オートフィルで下へコピーします。
Sheet2の数式は100行まで対応できる数式にしていますので
100行目くらいまでコピーしてみてください。

そして、Sheet2のC2セルに
=IF(COUNT(Sheet1!$A$2:$A$100)>=COLUMN(A1),INDEX(Sheet1!$E$2:$E$100,SMALL(Sheet1!$A$2:$A$100,COLUMN(A1))),"")

C3セルに
=IF(COUNT(Sheet1!$B$2:$B$100)>=COLUMN(A1),INDEX(Sheet1!$E$2:$E$100,SMALL(Sheet1!$B$2:$B$100,COLUMN(A1))),"")

という数式を入れ、C2・C3セルを範囲指定してオートフィルで
列方向へコピーすると画像のような感じになります。

尚、EOMONTH関数を使っていますので
Excel2003以前のバージョンですと
メニュー　→　ツール　→　アドイン　→　「分析ツール」にチェックを入れておく必要があります。

尚、繰り返しSheet1を利用したいと言うことですので
月が変われば、Sheet1のC列～E列の「摘要」まで入力してあるセルを
範囲指定し、Deleteキーで削除すれば繰り返し使用できるはずです。

以上、参考になれば幸いですが、
的外れなら読み流してくださいね。

どうも長々と失礼しました。m(__)m

お礼

回答ありがとうございます。
簡単な方法でよかったようなので、
日付を１日１行に固定して作ることにしました。
たぶんこれからいろいろな要望が出てくると思いますので、参考にさせていただきます。

回答No.4

KURUMITO

シート1で日付が該当の月だけでなく他の月が混じっていても対応できるようにしました。また、摘要も記載できるようにしました。
このためシート1では作業列を作って対応することにします。作業列が目障りでしたら列を非表示すればよいでしょう。
シート1のＤ1セルには検索したい年を2009年のように年も含めて入力します。
Ｄ2セルには検索したい月を9月のように入力します。
Ｅ2セルには次の式を入力します。
=IF(AND($A2>=DATEVALUE($D$1&$D$2&"1日"),$A2<=DATE(LEFT($D$1,4),LEFT($D$2,LEN($D$2)-1),15)),MAX(E$1:E1)+1,"")
Ｆ2セルには次の式を入力します。
=IF(E2="","",IF(E2>0,IF(ISERROR(MATCH("ー",F$1:F1)),C2,INDIRECT("F"&MATCH("ー",F$1:F1))&","&C2),""))
Ｇ2セルには次の式を入力します。
=IF(AND($A2>=DATEVALUE($D$1&$D$2&"16日"),$A2<=DATE(LEFT($D$1,4),LEFT($D$2,LEN($D$2)-1)+1,0)),MAX(G$1:G1)+1,"")
Ｈ2セルには次の式を入力します。
=IF(G2="","",IF(G2>0,IF(ISERROR(MATCH("ー",H$1:H1)),C2,INDIRECT("H"&MATCH("ー",H$1:H1))&","&C2),""))
Ｅ2セルからＨ2セルまでを選択して下方にオートフィルドラッグします。
シート2にはまとめの表ですがＡ2セルには1日－15日、Ａ3セルには16日ー末日
と入力します。
Ｂ2セルには次の式を入力します。
=SUMIF(Sheet1!$E:$E,">0",Sheet1!$B:$B)
Ｂ3セルには次の式を入力します。
=SUMIF(Sheet1!$G:$G,">0",Sheet1!$B:$B)
Ｃ2セルには次の式を入力します。
=INDIRECT("Sheet1!F"&MATCH(MAX(Sheet1!E:E),Sheet1!E:E,0))
Ｃ3セルには次の式を入力します。
=INDIRECT("Sheet1!H"&MATCH(MAX(Sheet1!G:G),Sheet1!G:G,0))

お礼

回答ありがとうございます。
補助列が多くなりましたが、この方法だと確実に摘要も入りますね。
ちょっと考えてみます。

回答No.3

mu2011

>日付で2009/9/1～2009/15までの集計方法
⇒一案です。
　（１）データシートの日付がシリアル値（通常の日付形式）ならば、月／１以上且つ、月／１５以下等の複数条件で金額欄を加算する方法があります。
　　　（SUMPRODUCT関数でサイト検索すれば使用例が参照できます）
　（２）データシートがその月のデータが入力されるのだからデータシート先頭から日付を確定すれば数式を変更する事は不要です。
　　　（例）
　　　月前期は、=DATE(YEAR(データシート!A2),MONTH(データシート!A2),1)、=DATE(YEAR(データシート!A2),MONTH(データシート!A2),15)
　　　月後期は、=DATE(YEAR(データシート!A2),MONTH(データシート!A2),16)、=DATE(YEAR(データシート!A2),MONTH(データシート!A2)+1,0)
　（３）摘要は、数式で抽出可能ですが非常に長くなるので別案をご検討下さい。

お礼

回答ありがとうございます。
＃１の方の方法がとてもわかりやすかったので、そちらでやってみます。

回答No.2

aleister

E2に年
F2に月を入力しているとします。

1-15
=SUMPRODUCT((Sheet1!$A$2:$A$100>=DATE(Sheet2!$E$2,Sheet2!$F$2,1))*((Sheet1!$A$2:$A$100<=DATE(Sheet2!$E$2,Sheet2!$F$2,15))*(Sheet1!$B$2:$B$100)))
16-末
=SUMPRODUCT((Sheet1!$A$2:$A$100>=DATE(Sheet2!$E$2,Sheet2!$F$2,16))*((Sheet1!$A$2:$A$100<=DATE(Sheet2!$E$2,Sheet2!$F$2+1,1)-1)*(Sheet1!$B$2:$B$100)))

もう少し簡単にできるかもしれませんが。。

データが100行までとしていますので、それ以上増える場合は100の数字を変えてください

お礼

回答ありがとうございます。
少し長かったので＃１の方の方法でやってみます。