2012年2月26日

Excelでの集計シートの作り方

自分で題名のことをやるとき、いつもExcelオンラインマニュアルを見るので、ここだけ見ればいいように纏めてみました。 日頃からExcelに限らず表計算ソフトに親しんでいる人には「何を今更」な内容です。 ※画像は、呟きのままでは小さかったので変更(それでも小さいですが・・・)。
4

元のExcelブック

周りにこんなExcelファイルがあるので改善を試みてみる・・・内容はデフォルメしています・・・

画像が小さい(ごめんなさい)ので補足。
[林檎]シートのC2セル:

  • =SUMIF(一覧!$A:$A,$B2,一覧!B:B)
    [合計]シートのB2セル:
  • =林檎!C4
仕様
夢乃 @iamdreamers

別のシステムでcsvファイルに出力したデータ(果物の銘柄ごとの毎月の出荷数)を左側の[一覧]シートに貼ると、右側の[合計]シートに果物の種類ごとの出荷数を集計する、っていう・・・

2012-02-26 11:04:15
問題点
夢乃 @iamdreamers

で、問題(?)は間に[林檎][梨][葡萄]の三シートを挟んでいるってこと・・・ここで例えば、ジョナゴールドを追加しようとすると、[林檎]シートを探さないといけない・・・また、桃とか別の果物がラインナップに入ったらシートの追加が必要・・・

2012-02-26 11:05:40

改造したExcelブック

じゃ、どうするか・・・っていうとこんな感じ。

補足。
[小計]シートのC2セル

  • =SUMIF(一覧!$A:$A,$B2,一覧!B:B)
    [合計]シートのB2セル
  • =SUMIF(小計!$A:$A,$A2,小計!C:C)
変更箇所
夢乃 @iamdreamers

変えたところは、①中央の三シートを[小計]シート一枚に結合、②[小計]シートの各銘柄に対応した果物の種類を追加、③[合計]シートにもSUMIFワークシート関数を適用して[小計]シートから集計する、ってところ。

2012-02-26 11:08:01
夢乃 @iamdreamers

これなら、銘柄や種類が増えても、シートを探したり追加したり、っていう手間が省ける・・・

2012-02-26 11:08:16

追加改造したExcelブック

夢乃 @iamdreamers

欲を言えば、[一覧]シートの列に余分なデータ、例えば四半期毎の合計とかが入っていても大丈夫なように、集計列も自動で探すのがいい・・・

2012-02-26 11:08:31

こんな感じで。

補足。
[小計]シートのC2セル

  • =SUMIF(一覧!$A:$A,$B2,OFFSET(一覧!$A$1,0,MATCH(C$1,一覧!$1:$1,0)-1,65536,1))
変更箇所
夢乃 @iamdreamers

変更したところは[小計]シートの集計用のワークシート関数・・・前の奴ではSUMIFワークシート関数の第三引数を B:B にしていたけど、OFFSETワークシート関数MATCHワークシート関数を使って、1行目のデータから動的に使えるように変更している・・・

2012-02-26 11:10:12
こんなときに使える

こんな感じ・・・

残りを読む(14)

コメント

コメントがまだありません。感想を最初に伝えてみませんか?