エクセルでマッチング

質問

２つのエクセルファイルの内容をマッチングさせたいんですけど、
例えば、aaa.xlsとbbb.xlsというのがあって、
aaa.xlsのA列とbbb.xlsのA列にファイル名、
bbb.xlsのB列にパッケージ名が格納されていて、

aaa.xlsのA列の１行目から順に、bbb.xlsのA列と
同じファイル名が存在するかマッチングさせていき、
マッチした場合にマッチした行のパッケージ名を
aaa.xlsのB列に表示させたいんですけど、
関数なりマクロなりを使ってなんとかならないでしょうか？

回答No.9

taisuke555 · ベストアンサー

マクロは動いているという事でよろしいでしょうか？
前回の修正版です。（補足のように変更したつもりです）
確認してみてください。

Sub DataMatch()
　　Set ws1 = Workbooks("ステップアップ一覧.xls").Worksheets("Sheet1")
　　flg = 0
　　'OPENチェック
　　For Each wb In Workbooks
　　　　If (wb.Name = "SQLJ一覧.xls") Then
　　　　　　flg = 1
　　　　　　Exit For
　　　　End If
　　Next wb
　　If (flg = 0) Then 'OPENされていない場合はOPENする
　　　　Workbooks.Open Filename:="C:\管理台帳\SQLJ一覧.xls"
　　　　ws1.Activate 'ステップアップ一覧のシートをアクティブにする
　　End If

　　Set ws2 = Workbooks("SQLJ一覧.xls").Worksheets("Sheet1")

　　'マッチング処理
　　On Error Resume Next
　　For i = 3 To ws1.Cells(Rows.Count, 2).End(xlUp).Row 'ステップアップ一覧の3行目から終りの行まで
　　　　Err.Clear
　　　　no = ws2.Columns("B:B").Find(What:=ws1.Cells(i, 2)).Row 'SQLJ一覧のマッチング行
　　　　'上記式は、SQLJ一覧にマッチングデータがないとエラーになるので
　　　　'On error Resume Next によりエラーを回避
　　　　If (Err.Number = 0) Then 'SQLJ一覧にマッチングするデータがあった場合
　　　　　　ws1.Cells(i, 3) = ws2.Cells(no, 3) 'マッチング行の3列目をステップアップ一覧へコピー
　　　　End If
　　Next i
　　MsgBox "処理終了"
End Sub

まだ処理がおかしいようなら補足してください。
＞ステップアップ一覧のB列３行とSQLJ一覧のB列３行から比較し
私のプログラムは、ステップアップ一覧のB列３行目とSQLJ一覧のB列全てとを比較し、値を持ってきています。
SQLJ一覧のB1、B2セルの値が、B3、B4・・・と同じ値で無ければ問題ありませんが、
同じ値がある場合には、そちらも補足してください。

補足

マクロを動かしてみると、「インデックスが有効範囲にありません」というメッセージが出ました。
SQLJ一覧のB１は空白、B2は列名（SQLファイル名と書いてある）です。

回答No.11

taisuke555

SQLJ一覧の相対パスには、できないと思います。（私の知識の中では）

ステップアップ一覧と同じフォルダという事なら

　　If (flg = 0) Then 'OPENされていない場合はOPENする
　　　　FDir = thisworkbook.path 'ステップアップ一覧のパス取得
　　　　if ( right(FDir,1)<>"\") then '最終の[\]処理
　　　　　　FDir = FDir & "\"
　　　　End If
　　　　Workbooks.Open Filename:=FDir & "SQLJ一覧.xls"
　　　　ws1.Activate 'ステップアップ一覧のシートをアクティブにする
　　End If
のように変更すれば、移動しても（あくまで、ステップアップ一覧と同じフォルダ）
使用できるかと思います。（テストしていませんので記述ミスがあるかもしれません）
別サーバにSQLJ一覧のみ移動する時には、絶対パスを修正するようにしないと
できないと思います。（私の知識内では、無理と言う事で、断言できる訳ではありません）
移動するのも、エクスプローラなどのソフトを使うとEXCELで認識する事もできないですし・・・

お礼

同フォルダ内だと、上記のやり方でうまくいきました。ファイルを別サーバに分けて動かすことはないので、これで十分です。本当にいろんなことを教えていただきありがとうございました。

回答No.10

taisuke555

＞インデックスが有効範囲にありません
私の方でテストした時にはエラーがでませんので推測で回答します。

プログラムの中の
'マッチング処理
以下の部分ではOn Error Resume Nextでエラーが出てもメッセージが出ませんので、
　考えられるのは、シート名が違うのが一番怪しいですね！
前回同様の２行の変更はしていただいたでしょうか？
　　Set ws1 = Workbooks("ステップアップ一覧.xls").Worksheets("Sheet1")
　　Set ws2 = Workbooks("SQLJ一覧.xls").Worksheets("Sheet1")
大文字、小文字、全角、半角を正確に入れてください。

次にマクロを書く時のために、両ブックのシート名を補足してください。
もし、きちんと書いてある場合には、
エラーの出ている行（黄色になっている行）を教えてください。

よく分からなければ、お手数ですが
（１）[Alt]+[F11]でVisualBasicEditerに移り
（２）Sub DataMacth...End Subのプログラムの中にカーソルを移し（行はどこでも構いません）
（３）[F8]を押すと１行づつ実行していきます。（１行目が黄色くなると思います）
（４）[F8]を何度か押して、エラーがでたらその場所を教えてください。

手間を取らせますが、すみません。

お礼

すみません。シート名が間違ってました。シート名を訂正するとちゃんと動きました。あとは、メニューからマクロを実行させるには、ANo.#7のような感じでやればいいんですよね？
このたびは、マクロを作っていただきありがとうございました。説明もわかりやすくて、本当に助かりました。また機会がありましたらよろしくお願いいたします。

補足

今回のマクロは、SQLJ一覧のパスを絶対パスで指定しているのですが、もしこのファイルを別のサーバなどに移動させると、動かないですよね？相対パス指定はできるのでしょうか？

回答No.8

taisuke555

このプログラムで試してみてください。

１、[Alt]+[F11]を押す。VisialBasicEditerに変更されます。
２、左上にプロジェクトと書いてある中に、
　　　VBAProject(ステップアップ一覧.XLS）
　　　　　└Microsoft Excel Objects
　　　　　　　　├Sheet1
　　　　　　　　　・・・
　　　　　　　　└ThisWorkbook
　　とあると思うので、ThisWorkBookをダブルクリック
３．以下のコードを貼り付ける
　　（シート名は[Sheet1]にしてありますので、違う場合は
　　Set ws2 = Workbooks("ステップアップ一覧.xls").Worksheets("Sheet1")
　　Set ws1 = Workbooks("SQLJ一覧.xls").Worksheets("Sheet1")
　　この２行のシート名を変更してください。
***************************この下から*****************************
Sub DataMatch()
　　Set ws2 = Workbooks("ステップアップ一覧.xls").Worksheets("Sheet1")
　　flg = 0
　　'OPENチェック
　　For Each wb In Workbooks
　　　　If (wb.Name = "SQLJ一覧.xls") Then
　　　　　　flg = 1
　　　　　　Exit For
　　　　End If
　　Next wb
　　If (flg = 0) Then 'OPENされていない場合はOPENする
　　　　Workbooks.Open Filename:="C:\管理台帳\SQLJ一覧.xls"
　　End If

　　Set ws1 = Workbooks("SQLJ一覧.xls").Worksheets("Sheet1")
　　ws1.Activate 'SQLJ一覧のシートをアクティブにする

　　'マッチング処理
　　On Error Resume Next
　　For i = 1 To ws1.Cells(Rows.Count, 1).End(xlUp).Row 'SQLJ一覧の1行目から終りの行まで
　　　　Err.Clear
　　　　no = ws2.Columns("A:A").Find(What:=ws1.Cells(i, 1)).Row 'ステップアップ一覧のマッチング行
　　　　'上記式は、ステップアップ一覧にマッチングデータがないとエラーになるので
　　　　'On error Resume Next によりエラーを回避
　　　　If (Err.Number = 0) Then 'ステップアップ一覧にマッチングするデータがあった場合
　　　　　　ws1.Cells(i, 2) = ws2.Cells(no, 2) 'マッチング行の2列目をSQLJ一覧へコピー
　　　　End If
　　Next i
　　MsgBox "処理終了"
End Sub
***************************この上まで***************************
４．[Alt]+[F11]を押す。Excelに戻ります。
５．とりあえず「ツール」→「マクロ」→「マクロ」から「ThisWorkbook.DataMatch」を選択して実行してみてください。

処理（数十秒）を終了したら、メッセージボックスを表示するようにしています。
メッセージボックスは表示されますか？

メッセージがでなければ、マクロが動いていません。
メッセージがでて、何も処理がされなければ補足してください。

補足

taisuke555さん、マクロを作っていただきありがとうございます。また、kazuhiko5681さん、いろいろ丁寧に教えていただきありがとうございます。
ただ、申し訳ないのですが、処理の説明が間違っているところがありました。マッチングは、ステップアップ一覧のB列とSQLJ一覧のB列を比較し、マッチすると、ステップアップ一覧のC列にSQLJ一覧のC列を対照行に表示するという風になります。比較方法は、ステップアップ一覧のB列３行とSQLJ一覧のB列３行から比較し、一致しなければSQLJ一覧の行をずらして比較していくといった形になり、１つのセルのマッチングが終わると、ステップアップ一覧の行を下に移動し、同じように最終行まで比較を続けるといった形になります。
お手数ですが、よろしくお願いいたします。

回答No.7

kazuhiko5681

こんばんわ。

Sub Macro1()

Dim myWbn1 As Workbook
Dim myBook As String
Dim myWsn1 As Worksheet
Dim myWsn2 As Worksheet
Dim i As Integer
Dim j As Integer

For Each myWbn1 In Workbooks
If myWbn1.Name = "一覧表.xls" Then
myBook = myWbn1.Name: Exit For
End If
Next

If myBook = "" Then
Workbooks.Open Filename:="C:\管理台帳\一覧表.xls"
End If

Set myWsn1 = Workbooks("一覧表.xls").Worksheets(1)
Set myWsn2 = Workbooks("ステップアップ一覧.xls").Worksheets(1)
For i = 2 To myWsn2.Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To myWsn1.Cells(Rows.Count, 1).End(xlUp).Row
If myWsn1.Cells(j, 1).Value = myWsn2.Cells(i, 1).Value Then
myWsn1.Cells(j, 2).Value = myWsn2.Cells(i, 2).Value
End If
Next j
Next i

End Sub

上のコードで実行してみて下さい。もしこれでもエラーが出るようであれば、当方のほうでは正常に動作しておりますので、貴方様の方のパソコン側でチェックしない限り動くようにはならないかと思います。その時は下記のコードで実行してみて下さい。ただし、下記のコードを実行する時には、必ず２つのファイルを開いた後、マクロを実行して下さい。

Sub Macro1()

Dim myWbn1 As Workbook
Dim myBook As String
Dim myWsn1 As Worksheet
Dim myWsn2 As Worksheet
Dim i As Integer
Dim j As Integer

Set myWsn1 = Workbooks("一覧表.xls").Worksheets(1)
Set myWsn2 = Workbooks("ステップアップ一覧.xls").Worksheets(1)
For i = 2 To myWsn2.Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To myWsn1.Cells(Rows.Count, 1).End(xlUp).Row
If myWsn1.Cells(j, 1).Value = myWsn2.Cells(i, 1).Value Then
myWsn1.Cells(j, 2).Value = myWsn2.Cells(i, 2).Value
End If
Next j
Next i

End Sub

マクロ実行ボタンをメニューバーへ貼り付ける方法

１.エクセル画面で、メニューバーにマウスポインターをあわせて右クリック後、出てきたプルダインメニューのユーザー設定をクリックする。
２.表示されたダイアロゴボックスのコマンドタブを選択し、分類と書かれている下の四角の中から新しいメニューを選択する。
３.コマンドの下にある新しいメニューの上にマウスポインタを合わせ、その上で左ボタンを押してメニューバーのヘルプの横までドラッグしメニューバーの上でボタンを離す。
４.選択したボタンの編集を押し、名前と書いてある横の四角の中に任意の名前を入力してマクロの登録ボタンを押す。
５.マクロの登録ダイアログボックスのマクロ名の下の四角い部分にMacro1と入力し、ＯＫボタンを押してボックスを閉じ、閉じるボタンを押してユーザー設定ダイアログボックスを閉じる。

これで今作ったボタンにマクロが登録され、このボタンを押すとマクロが走ります。
もし、これでマクロが走らない時は、貴方様のパソコン側でチェックしなければ動かない原因がつかめません。ですから、シート状にコマンドボタンを配置して実行できるようにするのが一般的な使用方法だといえます。

補足

遅れてすみません。
実行時にエラーは出ないのですが、なにも変化がありませんでした。

回答No.6

kazuhiko5681

エクセルのバージョンとウインドウズのバージョンを教えて下さい。
それと、２つのブックが入っている場所のフルパス名も教えて下さい。

お手数をおかけいたします。よろしくお願いいたします。

補足

エクセルはExcel２０００でOSはWindows２０００Professionalです。

パスは、C:\管理台帳の中に入ってます。

よろしくお願いします。

回答No.5

kazuhiko5681

＞'SQLJ一覧.xls'が見つかりません。というエラーが発生しました。
このエラーは、ブック名が違っている時に起きるエラーです。次の確認をお願いします。

・SQLJ一覧.xlsを一覧表.xlsとブック名を変更する。
・コードの中のSQLJ一覧.xlsと書かれている部分をすべて一覧表.xls二変更する。

これでマクロを実行してみて下さい。その結果を再度お知らせ下さい。

＞できれば、シート上ではなく、メニュー上か、ファイルを開いた時にポップアップみたいな感じで出したいんですけど、可能でしょうか？
両方ともできます。どちらを説明すればよろしいのか教えて下さい。

お手数をおかけいたします。よろしくお願いいたします。

補足

ファイル名を一覧表.xlsに変更しましたが、結果はさきほどと同じく、「見つかりません」といったエラーが発生しました。

起動ですが、メニュー上のボタンで起動できるようよろしくお願いいたします。

回答No.4

kazuhiko5681

こんばんわ。サンプルマクロを組んでみました。つぎのように操作してみて下さい。
・ステップアップ一覧.xlsを開き、ＡＬＴ＋Ｆ１１キーを押してＶＢＥ画面を表示させ、画面左上のVBAProjectと書かれている下のSheet1をダブルクリックして表示された画面の右側の白い部分に下記のコードをコピー＆ペーストする。

Private Sub CommandButton1_Click()

Dim myWbn1 As Workbook
Dim myBook As String
Dim myWsn1 As Worksheet
Dim myWsn2 As Worksheet
Dim i As Integer
Dim j As Integer

For Each myWbn1 In Workbooks
If myWbn1.Name = "SQLJ一覧.xls" Then
myBook = myWbn1.Name: Exit For
End If
Next

If myBook = "" Then
Workbooks.Open Filename:="SQLJ一覧.xls"
End If

Set myWsn1 = Workbooks("SQLJ一覧.xls").Worksheets(1)
Set myWsn2 = Workbooks("ステップアップ一覧.xls").Worksheets(1)
For i = 2 To myWsn2.Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To myWsn1.Cells(Rows.Count, 1).End(xlUp).Row
If myWsn1.Cells(j, 1).Value = myWsn2.Cells(i, 1).Value Then
myWsn1.Cells(j, 2).Value = myWsn2.Cells(i, 2).Value
End If
Next j
Next i

End Sub

ＡＬＴ＋Ｆ１１キーを押してエクセルの画面にもどる。

・メニューバー上の表示→ツールバー→Visual Basicを順にクリックする。
・表示されたツールバーの中のコントロールツールボックス(かなづちとスパナのマーク)をクリックし、出てきたプルダウンメニューからコマンドボタン(一番右の上から２番目)をクリックし、シートの貼り付けたいところにマウスポインターをあわせてクリックする。
・デザインモード(一番左の一番上)をクリックしてへこんだ状態から普通の状態へ戻す。

コマンドボタンをクリックするとマクロが走り、貴方様のＩお考えになっている動作が実現できると思います。

ご不明な点・不具合等がありましたら、ご遠慮なくお知らせ下さい。私のわかる範囲でご一緒に考えていきたいと思います。

補足

おはようございます。
早速試してみましたが、実行すると、
「実行時エラー：1004
　'SQLJ一覧.xls'が見つかりません。というエラーが発生しました。ディレクトリはステップアップ一覧と同フォルダ内にあります。
あと、実行時のボタンなんですが、できれば、シート上ではなく、メニュー上か、ファイルを開いた時にポップアップみたいな感じで出したいんですけど、可能でしょうか？

よろしくお願いします。

回答No.3

kazuhiko5681

こんばんわ。さっそく補足いただき有難うございます。ＶＢＡの知識が皆無ということですので、コピー＆ペーストするだけできちんと動作するようにしたいと思いますので、次のことを教えて下さい。

・aaa.xlsの正式なブック名とbbb.xlsの正式なブック名
正式なブック名とは、貴方様が現在つけられているブック名のことです。

お手数をおかけいたします。よろしくお願いいたします。

補足

ファイル名はSQLJ一覧.xlsとステップアップ一覧.xlsです。
ステップアップ一覧がマクロの実行元で、ステップアップ一覧のファイル名の列と、SQLJ一覧.xlsのファイル名の列とをマッチングさせて、ファイル名が一致すると、SQLJ一覧.xlsのパッケージ名列にステップアップ一覧の一致した行のパッケージ名を表示するといった形にしようと思っています。
また、マクロの実行元には、実行ボタンを作りたいと思うのですが、もしよければそれも教えていただければありがたいです。

回答No.2

kazuhiko5681

初めまして。サンプルマクロを組んでみました。参考にしてみて下さい。
もし操作方法等で解らないことがありましたらお知らせ下さい。詳しく説明させていただきます。

Sub test()

Dim myWbn1 As Workbook
Dim myBook As String
Dim myWsn1 As Worksheet
Dim myWsn2 As Worksheet
Dim i As Integer
Dim j As Integer

For Each myWbn1 In Workbooks
If myWbn1.Name = "bbb.xls" Then
myBook = myWbn1.Name: Exit For
End If
Next

If myBook = "" Then
Workbooks.Open Filename:="bbb.xls"
End If

Set myWsn1 = Workbooks("aaa.xls").Worksheets(1)
Set myWsn2 = Workbooks("bbb.xls").Worksheets(1)
For i = 2 To myWsn2.Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To myWsn1.Cells(Rows.Count, 1).End(xlUp).Row
If myWsn1.Cells(j, 1).Value = myWsn2.Cells(i, 1).Value Then
myWsn1.Cells(j, 2).Value = myWsn2.Cells(i, 2).Value
End If
Next j
Next i

End Sub

補足

プログラムまで作って頂きありがとうございます。
ただ、私はマクロの知識は皆無なので、もしよければ
簡単な説明などしていただけたらありがたいです。
あと、使用方法なども教えてください。

回答No.1

ledm

VLOOKUPという関数があります。
ご希望どおり、２つのシートのデータをマッチングさせます。

例えば、aaa.xlsのＡ列のセルとbbb.xlsのＡ列をマッチングし、bbb.xlsのＢ列の値をaaa.xlsのＢ列に持ってきたいとします。
その場合は、まずＡ２のセルに、
＝ＶＬＯＯＫＵＰ（Ａ１，[bbb.xls]Sheet1＄Ａ＄１：＄Ｂ＄１０００，２，０）
という式になります。
式の項目の意味は、
Ａ１ ・・・ マッチングのキー
[bbb.xls]Sheet1＄Ａ＄１：＄Ｂ＄１０００ ・・・ 参照先の範囲
２ ・・・ マッチングした際に、上の参照先範囲の何列目のデータを持ってくるか
０ ・・・ 近似値ではなく、完全一致のみ
です。

詳しくはヘルプや、関数の本に載っていますよ。
但し、件数が多いと再計算に時間がかかりますが・・・。