ある条件に一致するシートの検索と抽出

質問

初めて質問します。

あるアンケートを実施し、１人１つのシートを複数枚、一つのブックに
まとめたとします。

「シート１」
　　A　　　B　　　　C　　　D・・・

１　ID　　　氏名

２　００３　Cさん

３　Q１　　(1)

４　Q２　　(2)

「シート２」
　　A　　　B　　　　C　　　D・・・

１　ID　　　氏名

２　００１　Aさん

３　Q１　　(2)

４　Q２　　(3)

この時、シートの並びはIDや名前の順にはなっておらず、順不同です。
ですので、どのシートが誰なのかがシート名ではわからない状態です。

このような状態で、例えば
下記「集計シート」のA２と一致するIDがあるシートから上記B３の結果を抽出し、
下記C２に返す、というのを関数でつくることはできるでしょうか。

　　A　　　B　　　　C　　　D・・・

１　ID　　　氏名　　Q１　　Q２・・・

２　００１　Aさん　　　　　　　　・・・

３　００２　Bさん　　　　　　　　・・・

誰がどのシートなのかわからないため、IDを頼りに該当者を発見し、その人の
回答を集計シートにまとめる、ということになります。

どなたかご教示いただけますと幸いです。

回答No.1

tsubuyuki · ベストアンサー

お望みの
> 誰がどのシートなのかわからないため、IDを頼りに該当者を発見し、
> その人の回答を集計シートにまとめる
と言うやり方でももちろん可能です。
が、シートがいくつあるか私には解りませんが、
一々全てのシートを見に行くのは時間効率が物凄く悪いと思いませんか？

なので、代案です。
考え方の方針として、
・全てのシートの名前を、各々のＡ２セルの値に変える（マクロ）
・ＩＮＤＩＲＥＣＴ関数を使って、各シートから該当セルを参照する（ワークシート関数）
と言うモノです。

まず、シート名を変更するところから。
以下のマクロを走らせます。

Sub sample()
Dim WS As Worksheet
　　　　For Each WS In Worksheets
　　　　　　　　If WS.Name <> "集計" Then
　　　　　　　　　　　　WS.Name = WS.Range("A2")
　　　　　　　　End If
　　　　Next
End Sub

実に単純で、全てのシートに対し、名前が「集計」じゃなかったら
シート名を各々のＡ２セルの内容に書き換えなさい、と言うものです。
シート１００枚程度なら一瞬で終わります。

ただし、シート名は重複できませんので、既に在る名前には変更できません。
例えば２枚目のシートが「ＩＤ＝００１」で、５枚目のシートも「ＩＤ＝００１」だったら
５枚目のシートでエラーを返し、マクロが止まります。
コレを回避するには１行追加して

Sub sample()
Dim WS As Worksheet
On Error Resume Next
（以下同文に付き、省略）

としてやると、エラーが起きたシートの名前を「変えずに」次に進めることが出来ます。
エラーがあり、名前が変わらなかったシートは目視確認の上で手動で変更してやりましょう。
何らかの問題があるようですので（主に重複だとは思います）。

以上、シート名変更のマクロでした。

続いて、参照するための関数です。
前述の通り、ＩＮＤＩＲＥＣＴ関数を使います。
関数についての詳細は別途お調べいただきたいのですが、
ザックリと言うと「指定される文字列への参照を返す」関数です。解りづらいですね。

例えばあるセル（Ａ１セル）に、「B1」と言う文字列が入力されているとします。
Ｂ１セルには「テスト」と言う文字列が入力されている状態で、
Ｃ１セルに「=INDIRECT(A1)」と入力するとアラ不思議、Ｃ１セルには「テスト」と返ってきます。
Ｃ１セルに「=INDIRECT(A1&"ですよ")」と入力すると
アラアラ不思議、Ｃ１セルには「テストですよ」と返ってきます。
そんな関数ですので、色々試してみてください。

さて、本題に戻ります。
「集計」シートの例えばＣ２セル。
ココは「ＩＤ＝００１」のシートのＢ３セルを参照したいはずです。
ここで注目すべきは「集計」シートのＡ２セルに「ＩＤ」が入っているところです。
つまり、ＩＮＤＩＲＥＣＴ関数を使って、
　　Ｃ２セル：=INDIRECT(A2&"!B3")
としてやると良いですね。
Ａ２セルの内容は「００１」ですから、この式は実は「=００１!B3」と言うことです。
Ｄ２セルなら、
　　Ｄ２セル：=INDIRECT(A2&"!B4")
ですね。
名前（例えばＢ２セル）も同様に
　　Ｂ２セル：=INDIRECT(A2&"!B2")
でいけそうな感じですね。
これらを下方向に必要な範囲にコピー（フィル）してやればＯＫです。

もっとやるのなら、
　　Ｃ２セル：=INDIRECT($A2&"!B"&COLUMN())
としてやると、横方向もコピー（フィル）だけで作ることが出来そうです。
ただし、質問文中のフォーマットで、一問一答であることが条件ではあります。
（ＣＯＬＵＭＮ関数は「列番号」を数字で返す関数です。詳細は別途お調べ下さい。）

これで集計表も問題なく出来ると思います。

以上、発想の転換です。
参考になりますかどうか。

補記）
質問文中ではＩＤが「００１」など、全角数字３桁で表現されていますので、
マクロも関数も「それを想定して」の提案です。
これが「実は"1"（半角数字）で、表示形式で３桁に見せている」のであれば、
マクロも関数ももう少し見直さないといけないのかもしれません。

その「条件」は質問文中から読み取れませんので悪しからずご了承下さい。

お礼

早々にご回答いただきましてありがとうございました。

マクロはシロウトなので、ご教示いただいた内容を１つ１つ
確認しながら作っていたつもりですが、

WS.Name = WS.Range("A2")

のところでエラーが出ており、立ち止まっているところです。

INDIRECTも存在は知っていたものの、使うのは初めてだったのですが、
これはとても便利なのですね。

いろいろと活用の幅が広がりそうでありがたいです。

回答No.4

tsubuyuki

別案も示しておきます。

方針としては
・シート名を「（ＩＤに関わらず）連番でつけなおす」
・ＩＮＤＩＲＥＣＴ関数で参照する
と言うものです。
コレだと、シート名が重複することも無く、空白になる可能性も無いので
エラーが発生する可能性は格段に減ります。

マクロです。

※「集計」シートが先頭に在る場合
Sub Sample()
　　　　For i = 1 To Sheets.Count
　　　　　　　　If Sheets(i).Name <> "集計" Then
　　　　　　　　　　　　Sheets(i).Name = "回答" & i - 1
　　　　　　　　End If
　　　　Next
End Sub

※「集計」シートが末尾に在る場合
Sub Sample()
　　　　For i = 1 To Sheets.Count
　　　　　　　　If Sheets(i).Name <> "集計" Then
　　　　　　　　　　　　Sheets(i).Name = "回答" & i
　　　　　　　　End If
　　　　Next
End Sub

どちらかを走らせると、シート名が「回答*」と言うシート名に変わります。
文中の「回答」を適宜な文字列に変えてもＯＫです。
　　Sheets(i).Name = "シート" & i
としてやると、「シート*」と言う名前が付きます。

これをもって、ＩＮＤＩＲＥＣＴ関数を使います。
「集計」シートの１行目に項目行を持つとして、
「集計」シートの２行目に「回答1」シートの内容を転記していきます。
転記される側の「行」を判断するため、ＲＯＷ関数も使います。

例えば、Ｂ１セルに「回答1」シートのＡ２セルを転記してくるなら、
　　Ｂ１セル：=INDIRECT("回答"&ROW()-1&"!A2")
としてやります。
つまり、ROW()でその行の番号、２行目ですから「２」が返りますので、
ＩＮＤＩＲＥＣＴ関数のカッコ内は「回答1!A2」と出来るわけです。
これを必要数（行列とも）作ってやります。
そうすると、シート順に「集計」シートに転記されてきます。

このあと、全体をコピー→形式を選択して貼り付け→値を貼り付けてやり、
ＩＤ順にソートしてやれば探しやすくなるでしょう。
※式のままだと並べ替えできません。どう頑張ってもシート順にソートされます。

複雑な式で一発で参照することは可能です。
が、後のメンテナンス、引継ぎ等で苦労してしまうことを考えると、
マクロも式も単純なほうが良いと思いますよ。

回答No.3

tsubuyuki

> WS.Name = WS.Range("A2")
> のところでエラーが出ており、立ち止まっているところです。

エラーメッセージの内容がわからないので特定できませんが、
考えやすいエラーの原因としては、
・シート名が重複している
・エクセルのシート名に使えない文字を指定した
どちらかだと思います。

　　WS.Name = WS.Range("A2")
これは、「そのシートのＡ２セルの中身をシート名にしなさい」と言う意味です。
Ａ２セルが空白であれば、エクセルのシート名に空白を指定しようとすることになり、
これは当然、エクセルの仕様でエラーが発生します。

ＩＤがＡ２セルに無いのであれば、実際にＩＤを入力してあるセル番地に書き換えが必要です。

回答No.2

Nouble

要件を確認させてください

今回２シートともA2にID,B2に指名が入っているわけですが、
「こうなっているとは限らない」と、いうことなのですよね？

「集計シートに、既にID入力はされていて、
此に過不足はない、一人１件のみである」
と、いう前提で動いて構わないわけですね？

>１人１シート
と、いうことで
仮定ですが
シート全体から探す必要、
６万行を超えてもっと下まで探す必要、
此は無いと思って良いですか？

回答記入の左側には「Q1」とか「氏名」とかの、ラベルが必ず例外なくあると思って良いですか？

５００行目まで、
つまりA1:Z500くらいの範囲で探して良いでしょうか？
なお、
駄目な場合はその旨をお申し付けください。

一応出来ましたけれどひな形です
使用に当たってはご自身で加筆して頂く必要があります。
残念ながら「それでも!!」という場合でなければお勧めできません。
C2:==CHOOSE(
SUMPRODUCT(NOT(ISERROR(FIND($A2,Sheet1!$A$1:$Z$500)))*1)+
SUMPRODUCT(NOT(ISERROR(FIND($A2,Sheet2!$A$1:$Z$500)))*2)+
SUMPRODUCT(NOT(ISERROR(FIND($A2,Sheet3!$A$1:$Z$500)))*3)+
SUMPRODUCT(NOT(ISERROR(FIND($A2,Sheet4!$A$1:$Z$500)))*4)+
SUMPRODUCT(NOT(ISERROR(FIND($A2,Sheet5!$A$1:$Z$500)))*5),
OFFSET(Sheet1!$A$1,SUMPRODUCT((Sheet1!$A$1:$Z$500=C$1)*ROW(Sheet1!$A$1:$Z$500))-1,SUMPRODUCT((Sheet1!$A$1:$Z$500=C$1)*COLUMN(Sheet1!$A$1:$Z$500)),1,1),
OFFSET(Sheet2!$A$1,SUMPRODUCT((Sheet2!$A$1:$Z$500=C$1)*ROW(Sheet2!$A$1:$Z$500))-1,SUMPRODUCT((Sheet2!$A$1:$Z$500=C$1)*COLUMN(Sheet2!$A$1:$Z$500)),1,1),
OFFSET(Sheet3!$A$1,SUMPRODUCT((Sheet3!$A$1:$Z$500=C$1)*ROW(Sheet3!$A$1:$Z$500))-1,SUMPRODUCT((Sheet3!$A$1:$Z$500=C$1)*COLUMN(Sheet3!$A$1:$Z$500)),1,1),
OFFSET(Sheet4!$A$1,SUMPRODUCT((Sheet4!$A$1:$Z$500=C$1)*ROW(Sheet4!$A$1:$Z$500))-1,SUMPRODUCT((Sheet4!$A$1:$Z$500=C$1)*COLUMN(Sheet4!$A$1:$Z$500)),1,1),
OFFSET(Sheet5!$A$1,SUMPRODUCT((Sheet5!$A$1:$Z$500=C$1)*ROW(Sheet5!$A$1:$Z$500))-1,SUMPRODUCT((Sheet5!$A$1:$Z$500=C$1)*COLUMN(Sheet5!$A$1:$Z$500)),1,1)
)

使用に当たっては
SUMPRODUCT(NOT(ISERROR(FIND($A2,Sheet4!$A$1:$Z$500)))*4)+
と
OFFSET(Sheet4!$A$1,SUMPRODUCT((Sheet4!$A$1:$Z$500=C$1)*ROW(Sheet4!$A$1:$Z$500))-1,SUMPRODUCT((Sheet4!$A$1:$Z$500=C$1)*COLUMN(Sheet4!$A$1:$Z$500)),1,1),
を随時シート分追加頂き
その上で全シート名をご使用になっているブックに沿う形に変更頂く必要があります。

数式の文字数制限も気になるので、不適切な回答だと思います
ご容赦ください。

なお、
http://sdrv.ms/13L5aQG
に、サンプルを置いておきます。

お礼

２回もご連絡いただいてありがとうございます。

要件も十分にお示しできず失礼しました。

１つのブックには多くても５０～６０のシートが入る程度です。

なお、アンケートのフォーマットは決まっているため、IDや氏名、質問はどのシートも同じセルに入っています。

IDもひとりひとりに割り振られているため、重複はありません。

なぜシートが順不同になってしまうのかと言いますと、孫請け、下請けを経てこちらに集まってくるので、あまり細かい指示をしてしまうと回収がうまくいかなくなるためです。
孫請けは１，０００程度、下請けは１００程度で、最終的にこちらには７０程度のブックになります。
シートさえまとめてくれれば集計はこちらでやるという前提です。

最初にご教示いただきましたマクロを試してみたところ、
WS.Name = WS.Range("A２")
のところでエラーが出てしまいました。

補足

大変失礼しました。

最初のご回答は別の方でした。