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