質問

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

日付    金額   摘要
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月になった場合はどうなるのか、といったこともよくわかりません。
よろしくお願いします。

通報する

回答 (7件)

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

>・元のファイルを作っておき、月が替わるごとにファイルをコピーして使うようにしたい。
この様な使い方はしないほうが良いですよ。あくまでの一つのファイルで集計しましょう。手間がかかりません。
例えば
集計シート
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の年と月を変更すれば瞬時に結果が出ます。

この回答へのお礼

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

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

↓の画像で説明させていただくと
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

この回答へのお礼

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

シート1で日付が該当の月だけでなく他の月が混じっていても対応できるようにしました。また、摘要も記載できるようにしました。
このためシート1では作業列を作って対応することにします。作業列が目障りでしたら列を非表示すればよいでしょう。
シート1のD1セルには検索したい年を2009年のように年も含めて入力します。
D2セルには検索したい月を9月のように入力します。
E2セルには次の式を入力します。
=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,"")
F2セルには次の式を入力します。
=IF(E2="","",IF(E2>0,IF(ISERROR(MATCH("ー",F$1:F1)),C2,INDIRECT("F"&MATCH("ー",F$1:F1))&","&C2),""))
G2セルには次の式を入力します。
=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,"")
H2セルには次の式を入力します。
=IF(G2="","",IF(G2>0,IF(ISERROR(MATCH("ー",H$1:H1)),C2,INDIRECT("H"&MATCH("ー",H$1:H1))&","&C2),""))
E2セルからH2セルまでを選択して下方にオートフィルドラッグします。
シート2にはまとめの表ですがA2セルには1日-15日、A3セルには16日ー末日
と入力します。
B2セルには次の式を入力します。
=SUMIF(Sheet1!$E:$E,">0",Sheet1!$B:$B)
B3セルには次の式を入力します。
=SUMIF(Sheet1!$G:$G,">0",Sheet1!$B:$B)
C2セルには次の式を入力します。
=INDIRECT("Sheet1!F"&MATCH(MAX(Sheet1!E:E),Sheet1!E:E,0))
C3セルには次の式を入力します。
=INDIRECT("Sheet1!H"&MATCH(MAX(Sheet1!G:G),Sheet1!G:G,0))

この回答へのお礼

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

>日付で2009/9/1~2009/15までの集計方法
⇒一案です。
 (1)データシートの日付がシリアル値(通常の日付形式)ならば、月/1以上且つ、月/15以下等の複数条件で金額欄を加算する方法があります。
   (SUMPRODUCT関数でサイト検索すれば使用例が参照できます)
 (2)データシートがその月のデータが入力されるのだからデータシート先頭から日付を確定すれば数式を変更する事は不要です。
   (例)
   月前期は、=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)
 (3)摘要は、数式で抽出可能ですが非常に長くなるので別案をご検討下さい。

この回答へのお礼

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

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の数字を変えてください

この回答へのお礼

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

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

簡単な方法としては、

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)


補助列がいやな場合は、

この回答へのお礼

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

このQ&Aは役に立ちましたか?0 件

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

新しく質問する

このQ&Aを見た人はこんなQ&Aも見ています

注目の記事

フリーアナウンサー長谷川豊氏の新コーナー「このニュースどう思います?」がスタート!

元フジテレビのフリーアナウンサー長谷川豊氏から気になるニュースについておしトピの皆さんに質問します! 皆さんの意見をもとに長谷川豊氏がコラムを執筆します! アプリリリース記念として最大1万分のアマゾンギフト券プレゼントキャンペーンも実施中!

このQ&Aを見た人が検索しているワード


新しく質問する

このQ&Aを見た人がよく見るQ&A

このカテゴリの人気Q&Aランキング

毎日見よう!教えて!gooトゥディ

べんりQ&A特集