エクセルでの複数ブックの特定範囲を一覧化する

質問

エクセルでイベントの参加者を数か所から集め、集計を行っています。
今までは紙ベースで受け取り、エクセルへ手打ちをしていましたが、もう嫌です。
エクセルベースで送付してもらい、集計し、一覧化を行いたいです。

　　　 　 【ブックα】　　　　　　｜　　　　　【ブックβ】
　　　A　　　　B　　　　C　　　 ｜　　　　A　　　　B　　　　C　
１ 　年齢　性別　　名前　　　｜　１ 　年齢　性別　　名前
２　　・　　　　・　　　　・　　　　｜　２　　・　　　　・　　　　・
３　　・　　　　・　　　　・　　　　｜　３　　・　　　　・　　　　・
４　　・　　　　・　　　　・　　　　｜　４　　・　　　　・　　　　・

様式としてはとしては以上のような一覧で送られてきます。（だいぶ簡略化してますが）
この複数ブックの申し込みを以下のような一覧に仕上げたいです。

　　　A　　　B　　　　C　　　　D
１　　α　年齢　　性別　　名前
２ 　 ・　 　・　　　　・　　　　　・
３　　・　 　・　　　　・　　　　　・
４　　・　　 ・　　　　・　　　　　・
５　　β　 ・　　　　・　　　　　・
６　　・　　・　　　　・　　　　　・
７　　・　　・　　　　・　　　　　・

ちなみに、ブックごとの参加者は不定です。なので範囲としては（A１：C100)を抽出するようになると思います。

マクロなるもので、複数ブックを一つのブックにまとめ、複数シートとすることはできましたが、その複数シートの該当範囲のみを転記し、一覧化する（A行にシート名を転記する）ことができません。
また、ただ単に当該範囲を転記するだけでは、空欄セルが多数転記されてしまいそうですが、記入されているセルのみを転記することが可能であればなおいいです。

わたしが「こうできたらいいのに」と思うことはできると思っています。
ただ、私にはその知識がありません。
なのでお力添えいただければ幸いです。
つたない説明で伝わりにくい点もあるかと思いますが、よろしくお願いいたします。

回答No.3

x0000x · ベストアンサー

マクロで処理するなら以下で出来ます。

１．「一つのブックにまとめた」ブックで新たにシートを追加、追加シートのシート名を"一覧"とする。
２．Alt+F11を押下し、VisualBasicを開く。
３．VisualBasicのプロジェクトエクスプローラーで"一覧"シートをダブルクリックする。
４．Editerペインに以下のマクロを貼り付ける。

Public Sub 一覧作成()
Dim lSt As Worksheet
Set lSt = ActiveWorkbook.Sheets("一覧")
Dim wST As Worksheet
Dim listRow As Long

'一覧の一行目は自分で設定してね。
listRow = 1
For Each wST In ActiveWorkbook.Sheets
'対象外のシート名("Sheet3")は処理しない（★適当に変更する）
'一覧シート(lst.Name)も対象外とする。
If wST.Name <> "Sheet3" And _
wST.Name <> lSt.Name Then

Dim R As Long
Dim C As Integer
For R = 2 To 10000 '１シート最大10000人まで（★適当に変更する）

'A列にデータが無い場合は集計元シートのデータなしと判定→次のシートへ
If Trim(wST.Cells(R, 1)) = Empty Then
Exit For
End If

'一覧対象のデータありの場合
listRow = listRow + 1 '一覧シートの格納行
lSt.Cells(listRow, 1) = wST.Name 'シート名

For C = 1 To 3 '１シート最大3列とした場合（★適当に変更する）
lSt.Cells(listRow, C + 1) = wST.Cells(R, C)
Next

Next
End If
Next

End Sub

５．上記の"★"を確認変更する。
　★の意味合いは以下
　(1)対象外シート名の指定、上記では"Sheet3"と"一覧"を例示。
　(2)一覧表の対象となるコピー元の各シート最大行数は10000としている。これを超える場合は変更する。
　(3)各シートからコピーする列は列１~３、3列を超える場合は変更する。

６．Excel2007のリボンで「表示」「マクロ」から「~.一覧作成」を実行する。
　各シートのデータが”一覧”シートに集まります。
　ただし、一覧の先頭行はどこかのシートの1行目をコピーするなどして、手動で設定してください。
　（各シートの2行目からコピーしています）

以上

お礼

ありがとうございます。

解説付きで自分で改変がしやすく、非常にありがたいです。
こういう回答を待っていたんです。

回答No.2

KURUMITO

ブックαやブックβの各データはまとめのブックのあるシートに入力されているとします。それがシート名になっているとします。すなわちブックαやブックβと命名されたシートが有るとします。幾つかのシートになっていても構いません。
そこでまとめのシートですがＡ1セルにはシート名の文字を入力し、右横方向の列にはシート名、例えばブックα、ブックβなどと入力します。
Ａ2セルには0を入力します。
Ｂ2セルには次の式を入力して右横方向にドラッグコピーします。

=IF(B1="","",A2+COUNTIF(INDIRECT(B1&"!C:C"),"*?"))

Ａ4セルにはシート名、Ｂ4セルには年齢、Ｃ4セルには性別、Ｄ4セルには氏名の文字をそれぞれ入力します。
Ａ5セルには次の式を入力してＤ5セルまで横にドラッグコピーしたのちに下方向にもドラッグコピーします。

=IF(ROW(A1)>MAX($2:$2),"",IF(COLUMN(A1)=1,INDEX($1:$1,MATCH(ROW(A1)-0.1,$2:$2,1)+1),INDEX(INDIRECT(INDEX($1:$1,MATCH(ROW(A1)-0.1,$2:$2,1)+1)&"!A:C"),ROW(A1)-INDEX($2:$2,MATCH(ROW(A1)-0.1,$2:$2,1))+1,COLUMN(A1)-1)))

シートの数が多くなっても対応することができます。

お礼

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

回答No.1

K Kazz（@JazzCorp）

＞一覧化する（A行にシート名を転記する）ことができません。
シート名は全部同じだと思う、タブン、、、
A行ッウのはドコのこと？？