複数シート間の集計

質問

毎日シートを1個づつ増やし、シート名がその日の日付
（1日であれば1）としています。
月の末には1つのBook内には1～31のシートができることになります。

同じBook内に集計という名のシートを作り
1～31の集計をしたいんです。
●1～31の各シートの内容は、

　　　A　　　　　　B　　　　
　　科目　　　　　金額
1　会議費　　　　1000　
2　福利厚生費　　2500　
3　会議費　　　　1500
4　交通費　　　　350

●集計シート

　　　A　　　　　　　B　　　　
　　科目　　　　　合計金額
1　会議費　　　　
2　福利厚生費　　　
3　交通費

こんな風にできないでしょうか？
お力かしてください(;´▽｀A``

回答No.4

shiotan99 · ベストアンサー

こんにちは～

ちょっと確認ですが、日別シートのデータは科目別に集計したものではなくて、ランダムに入力しているんですよね？（1行目にも3行目にも「会議費」があるということは）
つまりA列には同じ科目が複数出てくる、ということでよろしいですか？
No.1さんはそのへんをカン違いされているように思うのですが・・・。
カン違いしているのがコチラでしたらゴメンナサイ。

日別シートが科目別に集計したものではないのなら、集計シートでいきなり月間集計をするのではなくて、まず日別シートにその日1日分の科目別集計を出しておいたほうがずっとカンタンです。

たとえば、日別シートの D列とE列に日別の集計をするとすれば、

　　　D　　　　　　　E　　　　
1　 科目　　　　　合計金額
2　会議費　　　　
3　福利厚生費　　　
4　交通費

として、E2に

=SUMIF(A:A,D2,B:B)

と入れ、E4までフィルコピー（科目が3つだけとすれば）
これで、その日の科目別集計が出ます。
このシートに 1 という名前をつけてください。

あとは、No.1さんがおっしゃっているように「串刺し集計」を使えば、集計シートに月トータルの科目別集計を出すことができます。
ただ、No.1さんのようにわざわざ「開始」「終了」というシートは作らなくてもできると思います。

とりあえず最初に、月初の1日のシートと月末のシートだけを作っておきます。
先ほどの、1 という名前のシートをコピーして、たとえば「末日」という名前にします。

> 月の末には1つのBook内には1～31のシートができることになります。

月によって、28～31のシートになりますから、月末の日だけはシート名を、数字ではなく「末日」とします。
シート見出しはこんな↓感じになります。

＼集計／＼1／＼末日／

1 というシートから、「末日」というシートまでのすべての E2セルの値を合計すれば、月トータルの「会議費」が集計されます。日別の「会議費」の集計を、それぞれのシートの E2に出しているからです。
E3を合計すれば「福利厚生費」、E4を合計すれば「交通費」の集計になります。

集計シートの1行目が見出しで、科目名が2行目から始まっているとすれば、
集計シートの B2に、

=SUM('1:末日'!E2)

と入れて、B4までフィルコピー（科目が3つだけとすれば）

以上です。
2日以降は、「末日」のシートをコピーして、1 と「末日」シートの間に日付順に入れていけばいいと思います。

参考URL：

http://kokoro.kir.jp/excel/3d.html

お礼

こんにちわ・^ー^)
No5のアドバイスも頂きありがとうございました♪
現状どうしても指定されたフォームの為、１日１シートになってしまうのです。
データが重くなることまで教えていただいたのもあり、
No4のやり方で作成してみました。

はじめての投稿でこんなに親切にアドバイス頂き
とても嬉しいです。
今後、この件についてより良いフォーム作り頑張りますので
また、良きアドバイスをお願いいたします。

補足

早速のアドバイスありがとうございます(^o^ )
言葉が足りずすみませんでした。
shiotan99さんにご理解いただいたように、ランダムに科目が入っているのです。

もうひとつ、欲を出してお聞きしたいのですが
条件をもうひとつ増やしたとしたらどうしたらいいか？わかりませんでしょうか？
まずはA行の科目！もうひとつはC行に提出者。
科目と提出者が一致したものの合計を出せたら、
とても助かるんですが・・・・(_ _)

回答No.5

shiotan99

No.4です。

> まずはA行の科目！もうひとつはC行に提出者。
> 科目と提出者が一致したものの合計を出せたら、

　　　A　　　　　　B　　　　　C　　　　
1　 科目　　　　　金額　　　提出者
2　会議費　　　　1000 　　　小野
3　福利厚生費　2500 　　　中村
4　会議費　　　　1500 　　　中村
5　交通費　　　　350　　　　柳沢

こんな↑感じですか？
こういう場合の集計は、SUMPRODUCT関数を使います。

日別シートの集計を

　　　E　　　　　　F　　　　G　　　 H
1　 　　　　　　　小野　　 中村　　柳沢　
2　会議費　　　　
3　福利厚生費　　　
4　交通費

として、F2に

=SUMPRODUCT(($A$2:$A$50=$E2)*($C$2:$C$50=F$1),$B$2:$B$50)

絶対参照（$のつけ方）に注意してください。
A列が =E2（会議費）かつ C列が =F1（小野）の場合の B列の合計が出ます。
この数式を右に提出者分コピー（3人だとすれば、H2までコピー）
そのまま（ F2からH2が選択された状態で ）下に4行目までコピー（科目が3つだけだとして）
これで提出者別・科目別の日別集計ができます。

SUMPRODUCT関数は、SUMIF関数のように範囲を A：Aのように列全体を指定することはできませんので、何行目から何行目までと指定してやる必要があります。
ただし、SUMPRODUCT関数はあまり範囲を広げすぎると重くなります。必要最小限の範囲を指定してください。（ 1日あたりの件数が MAX50件ぐらいだと思えば、2行目から50行目までを指定）

あとは、集計シートに上と全く同じ表をA列から作り、
集計シートの B2に

=SUM('1:末日'!F2)

と入れて、右に下にフィルコピーすれば一応できるはずです。

すでに書いたように SUMPRODUCT関数はあまり多用すると処理が重くなります。提出者が何人いるのか、科目数はいくつあるのか、1日あたりのデータ件数は何件くらいあるのか、またPCの性能にも影響されるはずです。
そのあたりが不明ですのでなんとも言えません。とりあえず上記の方法で試してみてください。

それと、ちょっと疑問なのが、1日ごとにシートを作る必要があるのでしょうか？ ということです。
A列を「日付」、B列を「科目」、C列を「金額」、D列を「提出者」として、日付に関係なく、1日から月末までを1つのシートでできないのでしょうか？

これでも SUMPRODUCT関数を使えば、月トータルの科目別・提出者別の集計はできます。（ 集計だけが目的だとすれば、の話ですが・・・ ）

回答No.3

imogasi

エクセルのツールに、メニューでデータ－統合・・があります。
これが応用できないか考えてみてください。
たとえば
http://www.eurus.dti.ne.jp/~yoneyama/Excel/syukei.html
で後半の「シート間のデータの統合」を読んでみて考えてください。

回答No.2

kaisendon

１です。。。

１の回答の最後の行を修正します。

誤：→「集計」シートの任意のセルに　=SUM(開始:終了!B1)
正：→「集計」シートの任意のセルに　=SUM(開始:終了!B2)

最近ケアレスミスばかりの私でした。。。

反省。。。。。

回答No.1

kaisendon

こんばんは

質問の内容は
「一日に一枚、シートが増えてゆく」
「増えていったシートの同じセル番地を増えたシートの分だけ
　串刺し計算をしたい」
ということでよろしいでしょうか？

そうであるなら
仮に「開始」「終了」という
何も入力していない、空のダミーシートを作ります。
シートタブの配置は「開始」シートを左に、「終了」シートを右にして
1 2 3 4 5 …と増えてゆくシートを「開始」シートと「終了」シートの
間に挟みます。

「集計」シートの任意のセルに、例えばB1に
=SUM(開始:終了!B1)
と入力しておきます。

数式を下方向にコピーしてゆけば
各シートの同じセル番地が串刺し計算されます。

※（「集計」シートを置く場所は「開始」シートと「終了」シートに
　挟まないで下さい）

このようにしておけば
「その日の日付」のシートが増えていっても、
「開始」シートと「終了」シートに挟まれているなら
「その日の日付」シートの同じセル番地が
「集計」シートに反映されるはずです。

あと、質問の中では、なぜか３行目の会議の科目が
集計シートには無いですね。。。

計算式を複雑にするよりも
上記のように設定した関数を
下方向にずら～～～～っとコピーした後に
集計シートのいらない行を削除した方が楽なのではないかと思います。

文章で書くと何のことやら分からないかも知れないのですが。。。
（分かったかな？）
つまり下記のようなことです。

＼原紙／＼集計／＼開始／＼1／＼2／＼3／…＼終了／
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　↑
　　　　　　　　　　　　　　毎日、原紙シートをコピーしてシートを追加してゆく

シート1～シート…までのB2の値を「集計」シートで合計したい。
→「集計」シートの任意のセルに　=SUM(開始:終了!B1)

お礼

こんにちわ♪
このたびはアドバイスありがとうございます。
シートを最初と最後を決めてから式を作るんですね。
とても勉強になりました。

今回は科目がランダムに入ってしまうため、
No4で作業させていただきました。

お忙しい中のご回答に感謝いたします。