エクセル 各シートの合計の小計を別シートに出したい

質問

エクセル2007を使っています。

去年一年に使われた各部品の総計を出したいのですが教えて下さい。

まずデータをシステムからエクセルにエクスポートする。
各シートには一種類ずつの製品、それに使われる部品の合計。

シートAには製品Aに使われる部品X一年の出荷数＝使用された各部品の合計

同じくシートBには製品Bに使われる部品X一年の出荷数＝使用された各部品の合計

というように製品Jまで10シートあるとします。←今ここ

A-Jまでの製品には重複部品があるのでここで各製品の各部品の合計を別シートにまとめたいのですが簡単にできる方法はありますか？

ちなみに各シートに部品の合計の計算式が入っているので、各シートのデータをコピペしてまとめてもソートし直してデータの小計で出すともうまったく違った数値が出てきてしまう、もしくは0になります。部品は全部で1000種類以上あるのでひとつずつ手作業で入れるのは不可能です（いや可能ですが拒否です）。

会社の在庫管理システムが去年からちゃんと起動していないことが分かりこの作業をしています。
これで出た数値を元のシステムにマイナス数としてインポートして現在の部品の在庫数を出すために作業しています。これ自体は簡単なので問題はないのですが．．．

ちなみにマクロとかは使用したことがありません。

よろしくお願いします。

回答No.3

tom04

こんにちは！
各Sheet（10Sheet）とも1行目がタイトル行で、データは2行目以降にあり
A列が「部品名」、B列が「数量」になっているとします。
集計用のSheetは一番最後（Sheet見出しの11番目）にあるという前提で・・・

お望みでないVBAになってしまいますが・・・一例です。

Alt+F11キー　→　画面左下の「This Workbook」をダブルクリック　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub 集計() 'この行から
Dim i, k As Long
Dim ws As Worksheet
Set ws = Worksheets(11)
Application.ScreenUpdating = False
i = ws.Cells(Rows.Count, 1).End(xlUp).Row
If i > 1 Then
Range(ws.Cells(2, 1), ws.Cells(i, 2)).ClearContents
End If
For k = 1 To 10
i = Worksheets(k).Cells(Rows.Count, 1).End(xlUp).Row
Range(Worksheets(k).Cells(2, 1), Worksheets(k).Cells(i, 2)).Copy _
Destination:=ws.Cells(Rows.Count, 1).End(xlUp).Offset(1)
Next k
For i = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(Range(ws.Cells(2, 1), ws.Cells(i, 1)), ws.Cells(i, 1)) = 1 Then
ws.Cells(i, 3) = WorksheetFunction.SumIf(ws.Columns(1), ws.Cells(i, 1), ws.Columns(2))
End If
Next i
For i = ws.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If ws.Cells(i, 3) = "" Then
ws.Rows(i).Delete
Else
ws.Cells(i, 2) = ws.Cells(i, 3)
ws.Cells(i, 3).ClearContents
End If
Next i
Application.ScreenUpdating = True
End Sub 'この行まで

※　参考にならなかったらごめんなさいね。m(_ _)m

お礼

tom04様、

お返事が遅くなり大変失礼いたしました。

いつかは超えなければいけない壁、マクロ。
そしてこれを期に試してみたのですが、
【Alt+F8キー　→　マクロ　→　マクロ実行】が出てきません。
なので実行されず、これが正解だったのか判らずじまいです。
私は在米なのですがExcelは英語でも日本語でも機能は同じ。
なのにAlt+F8キーを押しても何も出てきません。なぜでしょう？

しかしこの先を進むことが出来ずまったく予想だにしていなかった展開になり、
私の作成した中途半端なリポートはお蔵入りをしました。
詳細は回答1のaokii様で書かせて頂きましたのでお目を通していただければ幸いです。

時間ができたら是非このマクロが起動するかどうか試してみたいと思います。
しかしAlt＋F8がうんともすんとも言わないので何処かが間違っているのでしょうか？
私の予想ではこれが正解なのでは？と思っております。

貴重なお時間を無駄にしてしまい申し訳ありませんでした。
ありがとうございました。

回答No.2

myi333

＜ちなみに各シートに部品の合計の計算式が入っているので、各シートのデータをコピペしてまとめてもソートし直してデータの小計で出すともうまったく違った数値が出てきてしまう、もしくは0になります。部品は全部で1000種類以上あるのでひとつずつ手作業で入れるのは不可能です＞

コピペするときに、「値貼り付け」していますか。

「各製品の各部品の合計」をコピーしてKシートに「値貼り付け」する
（A列　部品名、B列　使用された各部品の合計　）空白行は削除しておきます。

部品名は重複があるので、部品名の列だけをLシートにコピぺしてから、「重複を削除」します　Excel2010ではデータリボンの「重複の削除」を使います。
A列に重複なしの部品名ができたら、B1にsumif関数を入力して下までオートフィルします。
　SUMIF(SheetK!$A$2:$B$1000,A1,SheetK!$B$2:$B$1000)

お礼

myi333様、

お返事が遅くなり大変失礼いたしました。

貼り付けもしてみましたがちゃんと作動してくれません。
MRPからのエクスポートでフォーマットがおかしいためだと思います。
しかし最後まで試す事が出来ませんでした。
詳細は回答1のaokii様で書かせて頂いたような事となりお詫びしなければいけません。

貴重なお時間を無駄にしてしまい申し訳ありませんでした。
次回同じようなことがあれば試してみたいと思います。

ありがとうございました。

回答No.1

aokii

複雑な計算式を使って自動的に合計を出すこともできますが、単純に各シート毎にピボットテーブルを使って、各製品毎の部品を集計し、そのピボットテーブルを、集計する方法はいかがでしょう。

あるいは、各シートのデータをコピーして値のみを貼り付ける方法なら、ソートし直してデータの小計で出すこともできます。

お礼

aokii様、

お返事が遅くなり大変失礼いたしました。

ピボットテーブルをちゃんと使った事が無かったので試してみようと思ったのですが、
最終的に皆様から頂いた案は一つも最後まで試す事が出来ず、
そしてまったく思っても見なかった展開になったことをお詫びしなければいけません。

この質問をした翌日に、昨年だけではなく一昨年の在庫もちゃんと管理されていない事が判明。
年末、製造ラインを一週間停め社員一同で棚卸をするのですが、
この棚卸の時にかなりな手抜きがあったことが判明しました。
また製造BOM (部品表)というのが各製品にあるのですがそれも不完全だったことが判明。
製品が出荷されるとそれに使用された部品が在庫から抜かれる訳ですが、
不完全だったためシステム上の在庫と棚卸時の在庫にいつも大きな開きがありました。
不振に思ったため、この上半期の終わりを目途に私自身と選抜した社員とで一斉棚卸、
製造BOMの再確認と再構築、そしてシステムの見直しをしておりました。
やっとひと息つける段階に到達してふと、
「シマッタ、おしえてgooにお礼をしていない」と今に至ります。

貴重なお時間を無駄にしてしまい申し訳ありませんでした。
次回同じようなことがあれば試してみたいと思います。

ありがとうございました。