エクセル 複数のシートからデータの摘出

質問

こんにちは教えてください。
複数のExcelシートから、重複データの抽出を行いたいのですが
ご教授お願いいたします。
現在バーション２０１０

元データ
シート１
　　　　　 A　　　　 B　　　　 C　　　　 D
1　　　　　 氏名　　 役職　　 部署　　 年齢
2　　　　　 山　　　　 社員　　 開発　　 45
3　　　　　 下　　　　 社員　　 人事　　 42
4　　　　　 川　　　　 役員　　 人事　　 50
5　　　　　 目　　　　 社員　　 企画　　 33
6　　　　　 高　　　　 平　　　　 人事　　 50
7　　　　　 坂　　　　 平　　　　 企画　　 33

シート２
　　　　　 A　　　　 B　　　　 C　　　　 D
1　　　　 氏名　　 役職　　 部署　　 年齢
2　　　　　 田　　　　 社員　　 開発　　 45
3　　　　　 田　　　　 社員　　 人事　　 42
4　　　　　 高　　　　 役員　　 人事　　 50
5　　　　　 坂　　　　 社員　　 企画　　 33
6　　　　　 高　　　　 平　　　　 人事　　 50
7　　　　　 坂　　　　 平　　　　 企画　　 33
シート３も同じ様にあり今後２０シートまで行く予定

集計用
シート２１

　　　　 A　　　　　　B　　　　　C　　　　D
1　　　　　　　　　　検索条件
2　　　　　　　　　　人事
3　　　　　　　　　　開発
4
5
6　　　　氏名　　　役職　　　　部署　　 年齢
7
8
9
10

部署で検索条件を行い現在２種類ですが増えて行き３０種類を超える予定です
諸先輩方のVBAを見よう見まねで組んだのですが
エクセルの２００３で動かすと上手くいきません。
もともとを理解していないのでダメだと思うのですが
宜しくお願いします。
又最初から、２０シート　３０種類のマクロを組み
問題なく動く様にもしたいです。

Sub オートフィルター()
For i = 1 To 3
With Worksheets(i)
.AutoFilterMode = False
.Range("A1:E1").AutoFilter
For k = ２ To ３
.Range("A1:E1").AutoFilter Field:=5, Criteria1:=Range("B" & k).Value
.Range(.Range("A2"), .Range("A1").SpecialCells(xlLastCell)).SpecialCells(xlCellTypeVisible).Copy Range("A65536").End(xlUp).Offset(1)
.AutoFilterMode = False
Next k
End With
Next i
End Sub

回答No.5

kikitaiOK · ベストアンサー

Sub オートフィルター()
Dim R As Range
For i = 1 To 3
With Sheets("Sheet" & i)
.AutoFilterMode = False
.Range("A1:D1").AutoFilter
For k = 2 To 3
.Range("A1:D1").AutoFilter Field:=3, Criteria1:=Sheets("集計用").Range("B" & k).Value
Set R = Sheets("集計用").Range("A65536").End(xlUp).Offset(1)
.Range("A1").CurrentRegion.Copy R
R.Resize(1, 4).Delete Shift:=xlUp
.AutoFilterMode = False
Next k
End With
Next i
End Sub

これでどうでしょうか？
参考URL
http://officetanaka.net/excel/vba/tips/tips155c.htm

お礼

お返事遅れてすいません。
出来ました。
ありがとうございます。

回答No.4

KURUMITO

関数で処理することが途中のデータを見ることができるのでお勧めです。
次のようにシート２１に作業列を使って対応します。
シート２１での作業を示します。

Ａ１セルにはシート名、Ａ２セルにはデータ数、Ａ３セルにはデータ総数とそれぞれ文字を入力します。
Ｂ１セルにはＳｈｅｅｔ１と入力してＵ１セルまでドラッグしてＳｈｅｅｔ２０まで表示します。
Ｂ２セルには次の式を入力してＵ２セルまで横にドラッグコピーします。

=IF(ISERROR(INDIRECT(B$1&"!A1")),"",COUNTIF(INDIRECT(B$1&"!A2:A1000"),"*?"))

Ｂ３セルには次の式を入力してＵ３セルまで横にドラッグコピーします。

=IF(B$2="","",SUM($B2:B2))

Ａ４セルからＤ４セルまでに氏名、役職、部署、年齢の項目名を入力します。
Ａ５セルには次の式を入力し、Ｄ５セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>MAX($B$3:$W$3),"",IF(ROW(A1)<=$B$3,INDEX(INDIRECT($B$1&"!A2:D1000"),ROW(A1),COLUMN(A1)),INDEX(INDIRECT(INDEX($B$1:$W$1,MATCH(ROW(A1)-0.1,$B$3:$W$3,1)+1)&"!A2:D1000"),ROW(A1)-INDEX($B$3:$W$3,MATCH(ROW(A1)-0.1,$B$3:$W$3,1)),COLUMN(A1))))

Ｅ５セルには次の式を入力して下方にドラッグコピーします。

=A5&B5&C5&D5

Ｆ５セルには次の式を入力して下方にドラッグコピーします。

=IF(E5="","",IF(AND(COUNTIF(E$5:E5,E5)=1,COUNTIF(E:E,E5)>1),ROUNDDOWN(MAX(F$4:F4),-2)+100,IF(COUNTIF(E:E,E5)>1,INDEX(F$4:F4,MATCH(E5,E$4:E4,0))+COUNTIF(E$4:E4,E5),"")))

最後にお求めの重複データをＨ列からＫ列に表示させます。
Ｈ４セルからＫ４セルに項目名を並べます。
Ｈ５セルには次の式を入力してＫ５セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>INT(MAX($F$5:$F$1000)/100),"",INDEX($A:$D,MATCH(ROW(A1)*100,$F:$F,0),COLUMN(A1)))

お礼

お返事遅れてすいません。
出来ましたありがとうございます。
また、宜しくお願いします。

回答No.3

kikitaiOK

Ａ、Ｂ、Ｃ・・・が列名
１、２、３・・・行名
各シート名は「Sheet1～Sheet20」
集計用シート名は「集計用」
とすると

For i = 1 To 20
シートを増やす場合、この20を増やしたシート名の数字に変更すればよい

With Worksheets(i)
　シート名が「Sheet1～ｘ」で統一されているのであれば
　With Sheets("Sheet" & i)に変えたほうがいいかも？

.AutoFilterMode = False
.Range("A1:E1").AutoFilter
　各シートの"A1:E1"でオートフィルタをするから
　たぶん"A1:D1"の間違いでは？

For k = 2 To 3
　次の集計シート検索条件のある「行番号」を指定してあると思いますので
　検索条件が増えればそのぶんこの３を増やしてあげれいいと思います。

.Range("A1:E1").AutoFilter Field:=5, Criteria1:=Range("B" & k).Value
　"A1:D1"の間違い？であってれば、部署があるのは左から３番目の列だからField:=3
　検索条件の
　Range("B" & k).valueの部分も
　Sheets("集計用").Range("B" & k).value
　にしたほうが後から見て分かりやすいと思います。

.Range(.Range("A2"), .Range("A1").SpecialCells(xlLastCell)).SpecialCells(xlCellTypeVisible).Copy Range("A65536").End(xlUp).Offset(1)
.AutoFilterMode = False
Next k
End With
Next i
End Sub

これでとりあえず検索条件のデータは集計用のシートに出てくるようになるはずです。

このあとどのように重複チェックされてるのかはわかりませんが・・・

お礼

ありがとうございます。
ご指摘の所間違っていました。
これでは質問する以前の話ですね・・・
とりあえず２０１０では摘出出来るので
作業を進めています。
もしよければもう一つ

シートに摘出データが存在しないと
項目だけ摘出されてしまいます。
いい方法はなでしょうか？

わがままですいません。

回答No.2

shintaro-2

とりあえず、1回マクロの記録で、所望の操作をマクロとして保存してください。

そうすると、autofilterのfieldやcriteriaの使い方が理解できるようになると思います。

お礼

アドバイスありがとうございます。
時間があればゆっくり勉強したいのですが・・・
もし解るのであればVBAの答えを
いただけたら幸いです。
又早いお返事ありがとうございます。

回答No.1

kagetora0723

マクロなど難しいことはわかりませんが、参考になりそうなURLを貼っておきます。

エクセルで複数ファイルからデータを参照する時
http://okwave.jp/qa/q7507749.html

excel　複数ブック・シートからのデータ抽出
http://okwave.jp/qa/q7989857.html

複数のエクセルファイルからデータ抽出とまとめる方法について。
http://okwave.jp/qa/q3681193.html

お礼

アドバイスありがとうございます。
なかなか自分にピッタリと合うのが・・
色々と迷っています。
応用力が乏しいものですいません。
ご回答いただきありがとうございます。