excel 何枚もsheetを一気に統合したいです

質問

質問させていただきます。
何百種類のsheetを一気に統合したいのですが、1枚1枚sheetを選択し、統合しているのが現状です。これだと手間がかかるので何か良い方法がありましたら教えて頂きたいです。

(すべてのsheetに統合させる同じ項目が入っています。A1部品名、B1数量
統合部分はsheetのテキストが入っているセルすべてです。）
宜しくお願いいたします。

回答No.6

atom_28 · ベストアンサー

一部修正します。
内容：with - end with 構文の中での記述漏れ

.Range(.Cells(xxx--), .Cells(xxx--)).Value = myData(i)

Range,cellの直前に、"."を追加しました。
一応、なくても動きますが、何のために、 withを使ったのと？？？となります。

Sub Macro1()
Dim myData(201) As Variant
Dim myDatasheets As Integer
Dim myRowPosition As Integer
Dim myRow As Integer
Dim myColumn As Integer

For i = 1 To Worksheets.Count
myData(i) = Worksheets(i).Cells(1, 1).CurrentRegion
Next
myDatasheets = i - 1

Worksheets.Add after:=Worksheets(Worksheets.Count)

myRowPosition = 0
With Worksheets(myDatasheets + 1)
For i = 1 To myDatasheets
myRow = UBound(myData(i), 1)
myColumn = UBound(myData(i), 2)
.Range(.Cells(myRowPosition + 1, 1), .Cells(myRowPosition + myRow, myColumn)).Value = myData(i)
myRowPosition = myRowPosition + myRow
Next
End With
End Sub

尚、このマクロを日ごろの仕事に使うためには、更に改造が必要です。
逆に、その分、今のままでは、下記事項をユーザーに要求します。
○各シートにて必ず、Header（「部品名」、「個数」)が存在すること
本当は、このヘッダーの存在をチェックして、処理するようにした方がよいとは思います。

○マクロを使えるようにするためには、セキュリティを少なくとも「Middle」に設定する必要があります。2003と2010では、その設定の仕方が異なります。
必要なら、言っていただければ、お答えします。

お礼

atom28様

この度はご回答ありがとうございました。
お手数お掛けしました。
大変わかりやすくとても勉強になりました。
またマクロも作って頂きありがとうございます。

こちらのマクロを実行した所、うまくできました。
マクロの実行結果のsheetを統合して、自分の思った通りの結果がでました。
本当に感謝しております。今までの作業時間がかなり短縮されました。

この度はありがとうございました。

回答No.5

atom_28

まずは、下記を試してください。
前提は、シート数は、200枚です。
全シート上のデータの行数を集めた数は、約32000個(integer)を上限とします。
これ以上であれば、変数の属性を変える必要があります。

(1) すべてのシートの情報を単純に1枚のシートに持ってきます。
　　これは、下方に示すマクロを使います。
　　一番右に、シートが自動生成されます。
前提：各シート上のデータの列の数は、必ずしも、同じである必要はありません。
　　　　また、行の数は、同じであることは、全く期待しておりません。

(2)単純に集めたデータ(一番右のシート)に対して、PIVOTを使って、集計します。
PIVOTは、本がたくさん売りに出されていますから、勉強してください。
しかし、参考までに、行に「部品名」、データに「個数」です。データの属性は「合計」です。

尚、単純に集めたシートにて、ヘッダー（「部品名」、「個数」）が、シート数分集まっていますが、
実害はないので、無視します。

(3)最後にPIVOTの表の部分を転記するのですが、PIVOTのシートにて、全セル選択、コピーして、
別の新しいシートを作成、cells(1,1)を選択した上で、「形式を選択して貼り付け」としてください。

-----------------------------------------
マクロの組み込み方
データシートのあるエクセルブックにて、
(a)ツール/マクロ/新しいマクロの記録 (excel2003の場合、2010でも、マクロの記録はあります)
　記録開始したら、即終了とします・・・・マクロの枠を作成しただけ。
(b)先に作成したマクロの枠(多分名称は「Macro1」、この部分に、
下のMacro1()の記述をそっくり、コピペする。
(c)マクロの実行
ツール/マクロ/マクロ(Excel2003の場合)　で、Macro1()を選んで、「実行」とします。

Sub Macro1()
Dim myData(201) As Variant
Dim myDatasheets As Integer
Dim myRowPosition As Integer
Dim myRow As Integer
Dim myColumn As Integer

For i = 1 To Worksheets.Count
myData(i) = Worksheets(i).Cells(1, 1).CurrentRegion
Next
myDatasheets = i - 1

Worksheets.Add after:=Worksheets(Worksheets.Count)

myRowPosition = 0
With Worksheets(myDatasheets + 1)
For i = 1 To myDatasheets
myRow = UBound(myData(i), 1)
myColumn = UBound(myData(i), 2)
Range(Cells(myRowPosition + 1, 1), Cells(myRowPosition + myRow, myColumn)).Value = myData(i)
myRowPosition = myRowPosition + myRow
Next
End With
End Sub

回答No.4

tom04

こんばんは！
↓の画像のようにすべてのSheetの配置が統一されていて、A列（部品名）の並びも一緒であれば
「串刺し計算」が可能です。
Sheet見出し（Excel画面の下にSheet名が表示されているところ）の一番左側Sheetに
Sheet見出しの2番目以降の数量を集計する場合の「串刺し計算」のやり方です。

Sheet1（一番左側のSheet）のB2セルを選択　→　画面右上にあるオートサム（Σ）のアイコンをクリック　→　2番目SheetのB2セルを選択
→　Sheet見出しの左側の右向き▶を選択しShiftキーを押しながら最後のSheet見出しをクリック　→　Enterで確定！

仮にSheetが100ある場合は数式バーに
=SUM(Sheet2:Sheet100!B2)
と表示されます。これをフィルハンドルで下へコピー！
これですべてのSheetの「部品名」の合計数量がB列に表示されます。

※この手間が面倒な場合はVBAになりますが一例です。

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り、マクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub 集計() 'この行から
Dim i As Long, k As Long, lastRow As Long
With Worksheets(1)
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Range(.Cells(2, "B"), .Cells(lastRow, "B")).ClearContents
For k = 2 To Worksheets.Count
For i = 2 To lastRow
With .Cells(i, "B")
.Value = .Value + Worksheets(k).Cells(i, "B")
End With
Next i
Next k
End With
End Sub 'この行まで

※　マクロの場合は各Sheetの変更があるたびにマクロを実行する必要があります。
尚、一旦マクロを実行すると元に戻せませんので別Bookでマクロを試してみてください。
万一Sheet1に数式が入っている場合は数式も消えて実データになってしまいます。m(_ _)m

お礼

回答ありがとうございます。
お手数をお掛けしまして大変感謝しております。

「串刺し計算」大変勉強になりました。
またマクロも作って頂きありがとうございました。

大変参考になりました。ありがとうございました。

回答No.3

atom_28

統合の意味がわかりません。
もう少し、具体的に、入力情報が、何で、出力情報が何を明確に教えていただけないでしょうか？
例えば、

シートその１
A1　部品名　B1　個数
A2　アメ　　B2　10
A3　チョコ　B3　20

シートその２
A1　部品名　B1　個数
A2　アメ　　B2　10
A3　チョコ　B3　20

統合するとは、
例えば、シート100番目に
A1　部品名　B1　個数
A2　アメ　　B2　20
A3　チョコ　B3　40

やりたいこと如何によっては、単純コピペ、PIVOTを使うとかでも
方法は、いくらでも考えられます。

よろしくお願いします。

補足

ありがとうございます。

補足させて頂きます。部品名はばらばらです。

シートその１
A1　部品名　B1　個数
A2　アメ　　B2　10
A3　チョコ　B3　20
A3　スナックB3　20

シートその２
A1　部品名　B1　個数
A2　アメ　　B2　30
A3　スナック　B3　20

統合するとは、
例えば、シート100番目に
A1　部品名　B1　個数
A2　ピザ　　B2　20
A3　スナックB3　40
A4　アメ　　B2　30

などなど、宜しくお願い致します。

回答No.2

kuma8ro

一つのファイル内でしたら、計算式を入力する際に、シートのタブを複数選択すれば、選択したシートの全ての同一セルを対象とした計算式を入力出来ます。→参考URL

しかし、何百も有っては、この手法は使えませんね。

と言うことで、No.1さんの言われる通り、マクロの勉強をしましょう。

参考URL：

http://hamachan.info/excel/kusizasi.html

お礼

ありがとうございます。

何百もあるとほんと手間ですね。勉強したいと思います。

この度はありがとうございました。

回答No.1

lv4u

一般的に、似たような作業だけど、少しずつ違うことを何度も繰り返して、正確に・素早く行うことは、人間は苦手です。
ですので、そういう処理を行うプログラムを作ることになります。
エクセルであれば、エクセルＶＢＡという言語がありますから、それを使ってプログラミングします。

もちろん、どんなロジックで作ればいいかを考えて、それを実行するコードを打ち込むことになるので、それなりの考える時間が必要です。
もし、ＶＢＡという言語を知らないなら、その言語から勉強する時間も必要です。

それでも、プログラムは、人間とは比べものにならない速度で処理してくれますから、頑張る価値はあります。

ちなみに、ある方が、エクセルの表を作るのに、頑張ったけど、その作業があまりに面倒で挫折したという表がありました。
その表を作るためのデータ処理プログラムを作るのに、私が２日くらいかかりましたけど、完成したプログラムは、１０秒もかからずに、処理結果を出してきました。

大手の書店に行けば、エクセルＶＢＡの書籍が沢山あると思います。その中で、参考になる本を購入して、チャレンジされてみればいいと思います。

お礼

ありがとうございます。

VBAは難しいですね。本を見て参考にしたいと思います。

この度はありがとうございました。