【VBA】[INDEX＋MATCH]で別ブック(別表)からデータを転記する
PC業務において、さまざまな場面で活躍する
関数 [INDEX＋MATCH]
今回はVBA上で[INDEX＋MATCH] 関数使って、
別表（別ブック）からデータを転記させるVBAコードを、
例題を用いて記載しようと思います。
どんなメリットがあるか？
このコードによって得られることは以下の事かと思います。
◆参照先の元データをわざわざ開かなくて良い
参照先の元データは大概、容量が大きいのではないでしょうか？
データ編集のたびに元データを開いて非常に時間が掛かり、悩ましい。
こんなお悩みをお抱えではないでしょうか？
◆数式を記入せずに、自動でデータを任意のセルに持って来れる
長い数式を毎回入力して手間だ。
提出資料だから数式を無くす為に、ひと手間かけている。
こんなお悩みをお抱えではないでしょうか？
今回ご紹介するVBAはこれらの悩みを解消してくれるでしょう。
参照元の重いファイルを開かずにデータが持って来れるので非常に楽です。
別Bookを開かなくて良いという事は、
別Bookを探す手間も無いという事なので、これだけでも時短必須です。
また引抜いてくるデータは「値」のみなので、
処理後、数式を解除する必要はありません。
[INDEX＋MATCH] or [VLOOKUP]を多用する方で、同じような悩みを持った方が、
解決の方向へ向かうお手伝い出来たなら幸いです。
例題とソースコード
上のExcel画面の
Excel-Workbook[Book-A] のセル【B2~B10】【C2~C10】に
Excel-Workbook[Book-B] から関数 [INDEX＋MATCH] で
必要なデータを転記する・抜き出してくる
ソースコードと簡単な解説
Option Explicit
Sub Test()
'画面凍結
Application.ScreenUpdating = False
Dim A, B, C As String
Dim myBook1, myBook2 As Workbook
Dim mySht1 As Worksheet
Dim myRng1, myRng2, myRng3 As Range
Dim result As Variant
Dim i, EndLine1 As Long
A = "表1"
B = "Book-B"
C = "Sheet1"
Set myBook1 = ThisWorkbook
Set mySht1 = myBook1.Worksheets(A)
If mySht1.Range("A2") <> "" Then
EndLine1 = mySht1.Range("A1").End(xlDown).Row '★①
'別ブックの別表を開く★②
Set myBook2 = Workbooks.Open("※※※") '←ファイルの『場所』を記入
Set myRng1 = myBook2.Sheets(C).Range("B:B") '参照氏名
Set myRng2 = myBook2.Sheets(C).Range("C:C") '出身地
Set myRng3 = myBook2.Sheets(C).Range("D:D") '年齢
mySht1.Activate
With mySht1
'[出身地] のデータを転記する★③-1
For i = 2 To EndLine1
result = Application.Index(myRng2, Application.Match(Cells(i, 1), myRng1, 0))
If Not IsError(result) Then
Cells(i, 2) = result
End If
Next i
'[年齢] のデータを転記する★③-2
For i = 2 To EndLine1
result = Application.Index(myRng3, Application.Match(Cells(i, 1), myRng1, 0))
If Not IsError(result) Then
Cells(i, 3) = result
End If
Next i
End With
myBook2.Close '別表を閉じる★④
End If
'画面凍結解除
Application.ScreenUpdating = True
End Sub
上がソースコード例になります。
簡単な解説
大まかな流れとしては
★①．何行目まで関数 [INDEX＋MATCH] を用いるか…(Endline1の部分)
★②．元データが眠っているbookを開く
★③．エラーが出なければ必要箇所に転記する
★④．元データのbookを閉じる
実際の処理フローを画面で見ると
下のような感じになります。これでイメージをつかめれば幸いです。
ポイントとしてはコードの27行目「※※※」の部分です。
ここにはデータを参照したいBook（関数 [INDEX＋MATCH] の参照先Book）の
在り処を記載します。
「※※※」には『住所』を入力するような感じでとらえて下さい。
その『住所』の調べ方は
該当のファイルを [右クリック] → [プロパティ] を選択し
「↓の画像の箇所①＋ブック名②＋.ファイルの拡張子( [.xlsx] 等)」を
入れ込む。つまり、
下の【場所：】以降の箇所を「コピペ＋ファイル拡張子」を
VBAコードを入力するところに貼り付け
これでOKです。上のソースコードの※※※を例えば
「C:\*****\Desktop\新しいフォルダ\Book1.xlsx」等、この形にしましょう。
住所は[“”]で囲うことを忘れず。
続いて関数 [INDEX＋MATCH] を用いて元データから必要項目を抜き出します。
その為に、あらかじめ引用箇所・必要箇所をRange型変数に格納します。
（★②あたりのSet myRng…）
こうしておくとコードがきれいに整理され、再編集の際も楽になります。
あとは【For Next】のループを使い、今回必要なデータ（出身地）を全て
[INDEX＋MATCH] 関数を当てて抜き出します。
（★③あたり）
最後に元データが記載されている『Book-B』を閉じて終了です。
こんな調子で意外と簡単にVBAは組めます。
上のソースコードを参考に御自身の状況に合わせてご活用下さい。
関数[INDEX＋MATCH] で引抜きたいデータが２つ以上ある場合
【For Next】のループ箇所を増やすだけです。
例題的に言えば、『出身地』の他に『年齢』も同一の別Bookから抜き出す場合等です。
上のコードにはB列だけではなくC列にも転記する項目欄を設けています。
コード的には「★③-2の箇所」がそこに当たります。ご参考までに。
共有サーバーのファイルからも転記可能
ちなみにちなみに、この要領でやれば、共有サーバーに格納されているファイルを開き、必要なデータを楽々転記させる事も可能です。
『「※※※」の部分にどのように記載すれば良いか』は、
（共有サーバー上でも）
同じく該当のファイルを[右クリック] → [プロパティ]を選択
【場所：】以降の箇所を「コピペ＋ファイル拡張子」して
VBAコードを入力するところに貼り付ければOKです。
皆さんの業務効率化が図れれば幸いです。