EXCELの複数のシートを一括処理するには？

質問

・複数のシートを移動して１つにまとめたブックがあります。
・そのシートの名前はデフォルトではありません。
・シートのデータを抽出して、違ったレイアウトのシートにまとめたい。

例えば・・・

姓名　○○○○　性別：○
身長　○○○
体重　○○

決まったレイアウトのデータを・・・

姓、名、性別、身長、体重
姓、名、性別、身長、体重
　　　　　　　　・
　　　　　　　　・
　　　　　　　　・

このように全てのシートのデータを一覧表にまとめたいのです。

どなたかご教授願えないでしょうか？

回答No.3

imogasi

こういうエクセルのシートの表の組み換えは、ＶＢＡというプログラムで無いと自由に旨く出来ない。パターン的にやさしい場合で、この質問コーナーの回答者の少数の熟練者のレベルで無いと出来ない。
普通のエクセルの関数の初心者・中級者には、思いつかない複雑な式になります。
エクセルをビジネス的に使おうと思うと、ＶＢＡの習熟は必須です。そういうことを認識してください。
特に別シートにデータが分散すると複雑になる。
ーー
＞そのシートの名前はデフォルトではありません
何が言いたいのか。シート名など違っているのは当たり前ではないか？
ーー
それに例を挙げるのも、模擬的で良いから、具体的にかけ。（「姓名」などとせず、「田中」などにして）
また、１例でなく、最低２－３例は質問に書け。
＞姓名　○○○○　性別：○のような書き方でなく
第１列には見出しの「姓名」や「身長」や「体重」の文字があるのか。データの意味を示すために質問に書いたのか。
データ例は、出来るだけシートの見えるままに書け（下記のわたしの例を参考に）。
どうせ初心者で式なりを丸写しする可能性が高く、その際シートのデータ列がはっきりしないと、回答を丸写しできないよ。
もう一度データ例を具体的に挙げて、別質問を勧める。
そしてデータの特徴を、文章で説明して添えること（この場合だと３行で１まとまりで、１名分データである、など。）
こういうデータは加工が必要になって始末が悪いことは、当初表の設計段階で、初心者だから判らないのかな。
ーー
ＶＢＡで１シートにまとめるのは、コードの丸写し実行になるだろうが１案だ。
＝＝＝
１シートのみの例を挙げてみる。多数シートの場合は数行追加・修正になるが略。
例データ　Ｓｈｅｅｔ１　各行に見出しの漢字がある例とする
Ａ列　　　　　Ｂ列　　　　Ｃ列　　　　Ｄ列
姓名 田中 年令 18
身長 156
体重 56
姓名 鈴木 年令 23
身長 161
体重 60
姓名 木村 年令 21
身長 159
体重 56
ーーーーー
コード　標準モジュール
Sub test02()
Dim sh1, sh2
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
'--
k = 2
d = sh1.Range("A65536").End(xlUp).Row
For i = 1 To d Step 3
sh2.Cells(k, "A") = sh1.Cells(i, "B")
sh2.Cells(k, "B") = sh1.Cells(i, "D")
sh2.Cells(k, "C") = sh1.Cells(i, "B").Offset(1, 0)
sh2.Cells(k, "D") = sh1.Cells(i, "B").Offset(2, 0)
k = k + 1
Next i
End Sub
ーー
Ｓｈｅｅｔ２
Ａ２：Ｄ４　題１行目の項目見出しは略している。
田中 18 156 56
鈴木 23 161 60
木村 21 159 56
質問の例の書き方なども参考にして。

回答No.2

KURUMITO

操作がやや複雑になりますので作業列を作って対応するのがよいでしょう。
それぞれのシートにはお示しの項目についてAおよびB列の３行目から下方にデータが入力されているとします。
初めに集計の対象とするシートについてCｔｒｌキーを押しながらシート見出しをクリックします。それによって作業グループを形成します。その上で画面で表示されているシートのD3セルに次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>COUNTA($A$3:$A$1000)/3,"",IF(COLUMN(A1)=1,LEFT(INDEX($A:$A,ROW(A1)*3),FIND("　",INDEX($A:$A,ROW(A1)*3))-1),IF(COLUMN(A1)=2,MID(INDEX($A:$A,ROW(A1)*3),FIND("　",INDEX($A:$A,ROW(A1)*3))+1,5),IF(COLUMN(A1)=3,INDEX($B:$B,ROW(A1)*3),IF(COLUMN(A1)=4,INDEX($A:$A,ROW(A1)*3+1),IF(COLUMN(A1)=5,INDEX($A:$A,ROW(A1)*3+2),""))))))

なお、この式では姓名の入力については姓と名の間が半角ではなく必ず全角のスペースが入っている形となっているとして式を組んでいます。

このことによって集計の対象とするシートのD,E,F,G,H列には姓、名、性別、身長、体重が表示されます。
次に集計のためのシートの操作に移ります。なお、この操作で前記の作業グループは解除されることになります。
A１セルから横の列には、集計の対象とするシート名のそれぞれを列を変えて入力します。
A２セルには次の式を入力して右横方向にオートフィルドラッグします。

=IF(A1="","",COUNTA(INDIRECT(A1&"!A3:A1000")))

A３セルには次の式を入力して右横方向にオートフィルドラッグします。

=IF(A2="","",SUM($A$2:A2)/3)

次にお望みの表を作るのですが例えばA５からE5せるには姓、名、性別、身長、体重の文字列を入力します。
A６セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(OR(ROW(A1)>MAX($3:$3),COLUMN(A1)>5),"",IF(ROW(A1)<=MIN($3:$3),INDEX(INDIRECT($A$1&"!D3:H1000"),ROW(A1),COLUMN(A1)),IF(COUNTIF($3:$3,ROW(A1)),INDEX(INDIRECT(INDEX($1:$1,MATCH(ROW(A1),$3:$3,0))&"!D3:H1000"),ROW(A1)-INDEX($3:$3,MATCH(ROW(A1),$3:$3,0)-1),COLUMN(A1)),INDEX(INDIRECT(INDEX($1:$1,MATCH(ROW(A1),$3:$3,1)+1)&"!D3:H1000"),ROW(A1)-INDEX($3:$3,MATCH(ROW(A1),$3:$3,1)),COLUMN(A1)))))

これで各シートに新たなデータが入力された場合や入力のデータが修正された場合でも即座に対応できることになります。
一度こちらが指示したとおりに試験してみてください。

回答No.1

keithin

準備：
ALT+F11を押す
現れた画面で挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピー貼り付ける

sub macro1()
　dim i
　worksheets.add before:=worksheets(1)
　for i = 2 to worksheets.count
　worksheets(1).cells(i, "A") = worksheets(i).name
　next i
end sub

ファイルメニューから終了してエクセルに戻る
ALT+F8を押し，今登録したマクロを実行する

手順：
１枚目のシートのＢ１，Ｃ１，Ｄ１…セルに，各シートで
「姓」がＡ２セルに書いてあるなら　A2
「名」がＢ２セルに書いてあるなら　B2
「性別」がＣ３セルに書いてあるなら　C3
「身長」がＡ４セルに書いてあるなら　A4
　：
といった具合に，集めたいセル番地を記入する
Ｂ２セルに
=INDIRECT($A2&"!"&B$1)
と式を記入し，右に下にコピー貼り付ける。