エクセルのデータ抽出

質問

いつもお世話になってます。

Win98　Excel97です。

過去ログうをいつもありがたく拝見させて頂いてるのですが頭が足りず理解できませんでした。#REF!になってしまい
途方にくれてます。

シート１に４万件、シート２に８００件のデータがあり、
シート３に重複したものだけを呼び出したいので過去ログを参考にVLOOKUPを使用して呼び出そうとしてるんですが
#N/Aや#REFになってしまい困ってます。

キーはA列に入れた名前で、列の数はAKまでです。
なにかよいやり方があればお教え下さい。

よろしくお願い致します。
なお、説明が拙いので補足があればすぐお返事させていただきます。

回答No.2

noname#148473 · ベストアンサー

まずシートの名前を確認してください。作例はSheet1～Sheet3を使用するようになっていますので、必要に応じて変更してください。

質問に明記されていないのですが、シート２のキー（名前）もA列にあるものとします。

メニューバーから「ツール」→「マクロ」→「VisualBasicEditor」を選びます。VisualBasicEditorが別ウィンドウで起動します。VisualBasicEditorのメニューバーの「挿入」→「標準モジュール」を選びます。
画面の右半分に真っ白な広いウィンドウが開きますので、ここに下記の点線内の内容を貼り付けます。

'-----------------------------------------
Sub データ比較()

Dim sa(65536) As Byte
Dim ii, fg, de1, de2 As Integer
Dim c1, c2, c3 As Integer
Dim c As Variant

Set WS1 = Worksheets("Sheet1")
Set WS2 = Worksheets("Sheet2")
Set WS3 = Worksheets("Sheet3")

de1 = WS1.Range("A1").End(xlDown).Row
de2 = WS2.Range("A1").End(xlDown).Row

WS3.Range("A1") = "重複"
WS3.Range("B1") = "シート１にのみ存在"
WS3.Range("C1") = "シート２にのみ存在"
WS3.Range("A2:C65536").ClearContents

For Each c In WS1.Range("A1:A" & de1)
fg = 0
For ii = 0 To de2 - 1
If StrComp(c, WS2.Range("A1").Offset(ii, 0).Value) = 0 Then
WS3.Range("A2").Offset(c1, 0).Value = c
sa(ii) = 1
fg = 1
c1 = c1 + 1
ii = de2
End If
Next
If fg = 0 Then
WS3.Range("A2").Offset(c2, 1).Value = c
c2 = c2 + 1
End If
Next

For ii = 0 To de2 - 1
If sa(ii) = 0 Then
WS3.Range("A2").Offset(c3, 2).Value = _
WS2.Range("A1").Offset(ii, 0).Value
c3 = c3 + 1
End If
Next

End Sub
'-----------------------------------------

貼り付けたら、Excelのほうに戻って、
メニューバーから「ツール」→「マクロ」→「マクロ」を選びます。「マクロ」と書かれたウィンドウが画面上に開きます。「データ比較」という行が反転していることを確認して（反転していないときは、クリックして反転させる）「実行」ボタンを押します。Sheet3に実行結果が書き出されます。

以上です。お望みのような結果が得られましたでしょうか？

補足

ご回答ありがとうございます。
ありがたく使わせていただいたのですが
「オーバーフローしました」と出て

de2 = WS2.Range("A1").End(xlDown).Rownの
部分が黄色くなり左側に→が出てます。

お忙しいところ恐れいりますが対処法を
教えて頂けますでしょうか？

回答No.5

noname#148473

＃２です。

＃４さんの仰るとおり、ちとミスってました(^＿^;)

Dim ii, fg, de1, de2 As Integer

の行を

Dim ii, fg, de1, de2

あるいは

Dim ii, fg, de1, de2 As Long

に変更すれば、エラーは出なくなるはずです。
失礼しました。
snoopy64さん、ありがとうございます。

お礼

お礼は遅くなって申し訳ありません。

希望通りの抽出が出来ました。
ありがとうございます。

今後も勉強を重ねていきたいと思います。
ありがとうございました。

回答No.4

snoopy64

de2　が扱える範囲を超えています。
とりあえず
Dim ii, fg, de1, de2 As Integer
を
Dim ii, fg, de1, de2
に変更してみてください。

しゃしゃり出ちゃいました(^^ゞ

お礼

お礼が遅くなって申し訳ありません。

おかげさまで希望通りの抽出が出来ました
ありがとうございました

回答No.3

snoopy64

数字の項目が検索キーになっていませんか？
見かけ上は同じ数字に見えても、文字型だったりするとマッチしません。

検索先が文字で検索キーが数字の場合、
=vlookup(Text(A2,"@"),Sheet1!$A:$K,3,0)

検索先が数字で検索キーが文字の場合、
=vlookup(Value(A2),Sheet1!$A:$K,3,0)

これでどうでしょうか。

お礼

お礼が遅くなって申し訳ありません。

今回は別の方で解決できましたがとても参考になりました。

ありがとうございます。

回答No.1

mshr1962

#REF!は参照範囲が削除されたときなどにでるエラー
#N/Aは参照範囲にキーと同じ物がない場合にでるエラー
になります。
=VLOOKUP(A1,Sheet1!A2:G40000,2,false)
の様になっていませんか？
参照範囲は絶対参照にしてください。
Sheet1!$A$2:$G$40000の様になります。名前付けした方がいいと思いますよ。

#N/Aは重複していない場合ですがここで空白にしたい場合は
=IF(ISNA(VLOOKUP(省略)),"",VLOOKUP(省略))
の様にしてください。

お礼

お礼が遅くなって申し訳ありません。

今回は別の方で解決できましたがとても参考になりました。

ありがとうございます。