【VBA】[VLOOKUP] で別ブック(別表)からデータを転記

元データから転記する為に、よく使用される

関数 [VLOOKUP]

今回はVBA上で [VLOOKUP] 関数使って、
別表(別ブック)からデータを転記させるVBAコードを、
例題を用いて記載しようと思います。
 

広告

どんなメリットがあるか?

このコードのメリットは以下の事なのだろうと思います。

◆参照先の元データをわざわざ開かなくて良い
参照先の元データは大概、容量が大きいのではないでしょうか?
データ編集のたびに元データを開いて非常に時間が掛かり、悩ましい。
こんなお悩みをお抱えではないでしょうか?

◆数式を記入せずに、自動でデータを任意のセルに持って来れる
長い数式を毎回入力して手間だ。
提出資料だから数式を無くす為に、ひと手間かけている。
こんなお悩みをお抱えではないでしょうか?

今回ご紹介するVBAはこれらの悩みを解消してくれるでしょう。
参照元の重いファイルを開かずにデータが持って来れるので非常に楽です。
別Bookを開かなくて良いという事は、
別Bookを探す手間も無いという事なので、これだけでも時短必須です。

また引抜いてくるデータは「値」のみなので、
処理後、「値貼付け」等して、数式を解除する必要はありません。
そのまま誰かに提出出来る資料になります。

[VLOOKUP] を多用する方、
また日々の業務で「転記が大変だ」、「転記ミスが多い・怖い」
といった方の何かの転機になればと思っております。
 

例題とソースコード

VBA_別表からINDEX+MATCH_8
例えば『Book-A』の[表1] に『Book-B』の [Sheet1] からデータを持ってくる場合

上のExcel画面の
Excel-Workbook[Book-A] のセル【B2~B11】【C2~C11】に
Excel-Workbook[Book-B] から関数 [VLOOKUP] で
必要なデータを転記する・抜き出してくる
 

ソースコードと簡単な解説

Option Explicit

Sub Test()

'画面凍結
Application.ScreenUpdating = False

 Dim A, B, C As String
 Dim myBook1, myBook2 As Workbook
 Dim mySht1 As Worksheet
 Dim myRng1 As Range
 Dim result As Variant
 Dim i, EndLine1 As Long
 
 A = "表1"
 B = "Book-B"
 C = "表2"
 
 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:D")  '参照元データ
  
 mySht1.Activate
   
 With mySht1

  '[出身地] のデータを転記する★③-1
  For i = 2 To EndLine1
   result = Application.VLookup(Cells(i, 1), myRng1, 2, 0)
   If Not IsError(result) Then
    Cells(i, 2) = result
   End If
  Next i
 
 '[年齢] のデータを転記する★③-2
  For i = 2 To EndLine1
   result = Application.VLookup(Cells(i, 1), myRng1, 3, 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

上がソースコード例になります。
 

簡単な解説

大まかな流れとしては

★①.何行目まで関数 [VLOOKUP] を用いるか…(Endline1の部分)
★②.元データが眠っているbookを開く
★③.エラーが出なければ必要箇所に転記する
★④.元データのbookを閉じる

実際の処理フローを画面で見ると
下のような感じになります。これでイメージをつかめれば幸いです。
 

VBA_別表からINDEX+MATCH_5
何行目まで処理をするかを策定
VBA_別表からINDEX+MATCH_6
指定のBookを開く
VBA_別表からINDEX+MATCH_7
エラーが出なければ、転記スタート

ポイントとしてはコードの27行目「※※※」の部分です。
ここにはデータを参照したいBook(関数 [VLOOKUP] の参照先Book)の
在り処を記載します。
「※※※」には『住所』を入力するような感じでとらえて下さい。

その『住所』の調べ方は
該当のファイルを [右クリック] → [プロパティ] を選択し
「↓の画像の箇所①+ブック名②+.ファイルの拡張子( [.xlsx] 等)」
入れ込む。つまり、
↓の【場所:】以降の箇所を「コピペ+ファイル拡張子」
VBAコードを入力するところに貼り付け

VBA_別表からINDEX+MATCH_1
VBA_別表からINDEX+MATCH_2

これでOKです。上のソースコードの※※※を
「C:\*****\Desktop\新しいフォルダ\Book1.xlsx」この形にしましょう。
住所は[“”]で囲うことを忘れず。

続いて関数 [VLOOKUP] を用いて元データから必要項目を抜き出します。
その為に、あらかじめ[VLOOKUP]関数の”検索範囲”部分を
Range型変数に格納します。(★②あたりのSet myRng…)
こうしておくとコードがきれいに整理され、再編集の際も楽になります。

あとは【For Next】のループを使い、今回必要なデータ(出身地)を全て
[VLOOKUP] 関数を当てて抜き出します。(★③あたり)
最後に元データが記載されている
『Book-B』を閉じて終了です。 (★④あたり)

VBA_別表からINDEX+MATCH_4
コードを実行すると、(これくらいの量ならば)一瞬で転記されます。

こんな調子で意外と簡単にVBAは組めます。
上のソースコードは例題なので
参考程度に。御自身の状況に合わせてご活用下さい。
 

関数[VLOOKUP] で引抜きたいデータが2つ以上ある場合

【For Next】のループ箇所を増やすだけです。
例題的に言えば、『出身地』の他に『年齢』も同一の別Bookから抜き出す場合等です。
上のコードにはB列だけではなくC列にも転記する項目欄を設けています。
コード的には「★③-2の箇所」がそこに当たります。ご参考までに。

※ただし[VLOOKUP]の場合は、
 参照する元データの検索値(キーワード)に当たる部分を
 検索範囲の一番左側に持ってこなくてはならないのでご注意を。
 [VLOOKUP]について詳しく知りたい場合は、
 以下にまとめましたので宜しければご覧ください。

 

共有サーバーのファイルからも転記可能

ちなみにちなみに、この要領でやれば、共有サーバーに格納されているファイルを開き、必要なデータを楽々転記させる事も可能です。

『「※※※」の部分にどのように記載すれば良いか』は、
(共有サーバー上でも)
同じく該当のファイルを[右クリック] → [プロパティ]を選択
【場所:】以降の箇所を「コピペ+ファイル拡張子」して
VBAコードを入力するところに貼り付け
ればOKです。

皆さんの業務効率化が図れれば幸いです。

広告