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

2021年12月24日

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

関数 [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] で引抜きたいデータが２つ以上ある場合

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

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

【図解】関数[VLOOKUP] 別表から任意のデータを転記する
【図解】関数[VLOOKUP] 別表から任意のデータを転記する
別表（単価表など）からメインの表（売上表など）にデータを入れ込みたいときみなさん、どのように記入していますか？　 このような処理をExcelの数式で解決できるのをご存じでしょうか？今回は別表から条件に当てはなるデータを転 […]

　

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

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

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

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

広告

VBA / マクロ, パソコン

Posted by haginote.com