エクセル複数ファイルを一つのシートに…

質問

エクセルのsheet1に縦に800個ほどのデータが並んでおります。それらのデータファイルが40個ほど一つのフォルダに保存されています。それらのデータを一つのエクセルのシートにまとめたいのですがどうしたらよいでしょうか？例えばA列にはブック1のデータB列にはブック2のデータというようにしたいのですが…

回答No.4

pascal3141 · ベストアンサー

別の質問で、行ごとに合成するVBAを紹介しましたので、それを１列ごとに合成するようになおしました。

'条件：(1)任意のフォルダの中に、合成したいすべてのエクセルファイルを入れる。各ファイルの名前は異なっていること。
'　　　(2)各エクセルファイルの中の「練習」シートの１列目に同じ形式でデータが入れてあるとする。
'　　　(3)集計は「Main.xls」の「練習」シートに順にくっつけていく。
'　　　(4)コード中の「シート名」は自由に変更してください。
'　　　(5)Main.xlsについては、シートが２つ「search」「練習」が必要です。「search」シートはフォルダの
'　　　　場所と、その中のファイルの名前を取得して一覧にしています。その後、そのファイル名を使って
'　　　　「練習」シートに各ファイルの「練習」シートのデータを合成しました。
'　　　(6)このコードは、「ツール」「マクロ」「VisualBasicEditor」でエディター画面を開き、
'　　　　「挿入」「Module1」の所にはりつけ,エクセル画面で「ツール」「マクロ」「マクロ」
'　　　　「FileSearchの実行」で動かす。

Sub FileSearch(): 'ファイル検索

Dim sfolda As String
Dim SName As String
Dim i, j, k, n As Integer
Dim ww As String
Dim L, S As Integer
Dim ws As Object

Dim DName As String
Dim PP, FF As String
Dim MaxG, DKoumoku, DLine As Integer
Dim MaxFileSu As Integer

'各値初期設定
MaxFileSu = 100: '最大ファイル数
DName = "練習": 'シート名
MaxG = 1000: '最大検索行数
DLine = 1: 'データ入力行数カウント

Application.ScreenUpdating = False

'現在のフォルダのパスを設定
sfolda = ThisWorkbook.Path

'ファイル名を入れるシートをセットおよび初期化
Set ws = Workbooks("Main.xls").Worksheets("search")
ws.Range("B1").ClearContents
ws.Range("A4:B200").ClearContents
ws.Cells(1, 2).Value = sfolda

'各ファイル名を検索しsearchシートに登録
SName = "*.xls"
n = 1

With Application.FileSearch
.LookIn = sfolda
.Filename = SName
rs1 = .Execute
If rs1 = 0 Then Exit Sub
For Each nm In .FoundFiles
ww = nm
S = 1
While S > 0
S = InStr(1, ww, "\", 1)
L = Len(ww)
ww = Right(ww, L - S)
Wend
If ww <> "Main.xls" Then
ws.Cells(n + 3, 1).Value = n: '１列目に番号セット
ws.Cells(n + 3, 2).Value = ww: '２列目にファイル名セット
n = n + 1
End If
Next nm
End With

'======================================================================
'合成処理

For n = 1 To MaxFileSu

'ファイル名をセット
PP = ws.Cells(1, 2).Value
If ws.Cells(n + 3, 2).Value = "" Then Exit For
FF = ws.Cells(n + 3, 2).Value
PP = PP & "\" & FF

'ファイルオープン
Workbooks.Open (PP)

'各シートからデータをMainに追加貼り付け
For i = 1 To MaxG
If Workbooks(FF).Worksheets(DName).Cells(i, 1).Value = "" Then
Exit For
Else
Workbooks("Main.xls").Worksheets(DName).Cells(i, DLine).Value = Workbooks(FF).Worksheets(DName).Cells(i, 1).Value
End If
Next i
DLine = DLine + 1
'ファイルクローズ
Workbooks(FF).Close
Next n

End Sub

お礼

コードまで書いていただきありがとうございます。
これで完璧に出来ます！！！

回答No.3

Wendy02

INDIRECT 関数ですが、昨日も、ここのカテゴリで書きましたが、INDIRECT 関数はブックを開かないと取れませんが、INDEX 関数は、ブックを開かずに、値を取得できます。

=INDEX('ブックのパス\[ブック名.xls]シート名'!範囲,行,列)

それを、値が取得できた時点で、コピー・値貼り付けしてしまえば、ブックのリンクの負担がなくなります。

それから、専門的になりますが、マクロの場合のお話をさせていただきます。

マクロの場合は、本当に、開かないでインポートすると、私のイメージでは、逆に遅くなりますね。しかし、値をインポートする場合は、オートメーション・オブジェクトといって、メモリ上の中に、Excelを開いて(見えるようにも、見えないままでもできます)で、値を取得する方法があります。

マクロの場合は、それよりも、ブック名には一定の基準があるのかとか、Sheet1 の800個のデータの位置とか、きちんとしたルールがあるか、どうかのほうが問題でしょうね。

お礼

回答ありがとうございます。確かにindexを使うとできますね。しかし、ブックリンクを解除するのに値を貼り付けをしなければならないのが私の中で問題でしたｗ。ブック名に一定の基準があり800個のデータの位置も一定の基準んになっていますのでマクロでやろうと思います。丁寧な回答本当にありがとうございました。

回答No.2

s_yoshi_6

「Book1～Book40」の「Sheet1」の「A1～A800」にデータが入力されているとして、
それを「Book0」の「Sheet1」の「A1～AN800」に表示させる場合、
「Book0」の「Sheet1」の「A1」に
=INDIRECT("[Book"&COLUMN()&".xls]Sheet1!A"&ROW())
と入力して、行・列方向にコピーして下さい。一覧で表示されるようになると思います。

その際、
COLUMN()はそのセルの列番号、ROW()はそのセルの行番号ですから、例えば
「Book0」がＢ列（２列目）から、元データがＡ列（１列目）から始まる場合は「COLUMN()」を「COLUMN()-1」
とするなどして、参照セルが正しくなるように調整して下さい（ROW()の場合も同じ）

また元データの列番号がＡ以外の場合
Sheet1!A"&ROW()
の「Ａ」を変更することをお忘れなく。

なお、元データに空白セルがある場合、上記の式では「０」が表示されますので、それを避けたければ、
・ツール→オプションの「表示」タブで「ゼロ値」のチェックを外すか、
・式を
=IF(INDIRECT("[Book"&COLUMN()&".xls]Sheet1!A"&ROW())="","",INDIRECT("[Book"&COLUMN()&".xls]Sheet1!A"&ROW()))
とするなどして下さい。

補足

本当にすいません。この方法だとやはりファイルを開いていないとダメですよね。値だけをBook0に持ってきたいのですがこれをするにはやはりマクロか何かを使わなければならないのでしょうか

回答No.1

noname#11074

Book1のSheet1のA1セルを参照する方法

=[Book1]Sheet1!$A$1

でセル参照すればよいのではないでしょうか。

今回は同一フォルダ内のようですが、
フォルダが異なる場合、
'パス名[ブック名]シート名'!セル名

とすればよいと思います。

お礼

回答ありがとうございます。u1skwさんのおっしゃる通りなのですが，何しろファイルが多量にあるもので出来ればBook1～Book40は開かないで出来る方法をと考えております。説明が足りなくてすいません