別のワークブックで値を検索／参照する方法は？

VLOOKUP を使用した検索参照の設定は、特に元のファイルのパス、ファイル名、またはワークシートを頻繁に変更する場合、混乱を招くことがあります。このような状況では、VBA を使用して検索プロセスを自動化することで、より効率的な解決策となり、元のワークブックが閉じられている場合でも値を検索でき、範囲選択とデータ返却を自動化できます。

VBA を使用してワークブック間の検索を行う手順は次のとおりです：

1。 同時押しでAlt＋F11キーを押すと、Microsoft Visual Basic for Applicationsエディターウィンドウが開きます。

2。VBA エディターで、挿入＞標準モジュールをクリックし、以下のコードをモジュールウィンドウにコピー＆ペーストしてください：

VBA：別の閉じられたワークブックからデータと返す値を VLOOKUP で取得

Option Explicit

' Convert column number to column letter
Private Function GetColumn(ByVal Num As Integer) As String
    If Num <= 26 Then
        GetColumn = Chr(Num + 64)
    Else
        GetColumn = Chr((Num - 1) \ 26 + 64) & _
                    Chr((Num - 1) Mod 26 + 65)
    End If
End Function

Sub FindValue()

    Dim xAddress As String
    Dim xString As String
    Dim xFileName As Variant
    Dim xUserRange As Range
    Dim xRg As Range
    Dim xFCell As Range
    Dim xSourceSh As Worksheet
    Dim xSourceWb As Workbook
    
    On Error Resume Next
    
    ' Get current selection address
    xAddress = Application.ActiveWindow.RangeSelection.Address
    
    ' Ask user to select lookup range
    Set xUserRange = Application.InputBox( _
        Prompt:="Lookup values :", _
        Title:="Kutools for Excel", _
        Default:=xAddress, _
        Type:=8)
    
    If Err.Number <> 0 Then Exit Sub
    On Error GoTo 0
    
    ' Limit selection to used range
    Set xUserRange = Application.Intersect(xUserRange, _
                                           Application.ActiveSheet.UsedRange)
    
    ' Ask user to select source workbook
    xFileName = Application.GetOpenFilename( _
                "Excel Files (*.xlsx), *.xlsx", _
                1, _
                "Select a Workbook")
                
    If xFileName = False Then Exit Sub
    
    Application.ScreenUpdating = False
    
    ' Open source workbook
    Set xSourceWb = Workbooks.Open(xFileName)
    Set xSourceSh = xSourceWb.Worksheets.Item(1)
    
    ' Build external reference string
    xString = "='" & xSourceWb.Path & Application.PathSeparator & _
              "[" & xSourceWb.Name & "]" & _
              xSourceSh.Name & "'!$"
    
    ' Loop through user range
    For Each xRg In xUserRange
    
        ' Find matching value in source sheet
        Set xFCell = xSourceSh.Cells.Find( _
                        What:=xRg.Value, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        MatchCase:=False)
        
        ' If found, write formula 2 columns to the right
        If Not xFCell Is Nothing Then
            xRg.Offset(0, 2).Formula = _
                xString & _
                GetColumn(xFCell.Column + 1) & _
                "$" & xFCell.Row
        End If
        
    Next xRg
    
    ' Close source workbook without saving
    xSourceWb.Close False
    
    Application.ScreenUpdating = True

End Sub

重要な詳細：

• このコードは、検索範囲から右に2 列オフセットされた列に一致した値を返します。たとえば、列 B を選択した場合、結果は列 D に表示されます。
• 結果を別の列に表示させたい場合は、2をxRg.Offset(0,2).Formula 内の数値に他の値を指定してください（例：1は次の列、3は右から3 列目）。
• プロンプトが表示されたら、正しいワークブックとワークシートを選択してください。このコードは常に選択したファイルの最初のワークシートを使用します。元のシートが最初でない場合は、コードを調整してください。
• 初めて実行するマクロを実行する前に、必ずファイルを保存してください。マクロの実行後は元に戻すことができません。

3。マクロを実行するには、F5キーを押すか、実行ボタンをクリックしてください。「Kutools for Excel」というタイトルのダイアログボックスが表示され、検索したい値を含むセル範囲の選択を求められます。

4。 範囲を選択したら、OKをクリックします。するとすぐに、元のワークブック（閉じていても可）を選択するための別のダイアログボックスが表示されます。適切なファイルを参照して選択し、開くをクリックして確定してください。

マクロが完了すると、元のワークブックからの対応する値が現在のワークシートの対象列に返されます。一部の値が見つからない場合は、検索値の範囲が現在のワークシートとソースデータで完全に一致しているか確認してください（大文字・小文字や先頭の後ろのスペースが完全一致に影響します）。

メリット：閉じたワークブックにも対応！数式内にハードコードされたファイルパスを回避でき、実行時にソースファイルを柔軟に選べます。
注意点：マクロを有効にする必要があります。VBA は保護されたシートやExcel 以外のファイル形式では動作しない場合があります。この方法を頻繁に使う場合は、マクロ対応のワークブック（*。xlsm）として保存してください。

エラーが発生した場合は、シート名やファイル名にタイポがないこと、選択した範囲が適切であること、およびファイルパスにアクセス可能であることをご確認ください。デバッグ時には、VBA エディターを使ってコードを1 行ずつステップ実行することをおすすめします。

従来の VLOOKUP 関数や VBA に加え、Excel にはワークブック間の検索を行うための別の方法もあります。データ構造が異なる場合や、マクロよりも数式を好む場合、さらには左方向への検索や複数条件での検索など、より柔軟な対応が必要な場面に最適です。

ワークブック間で INDEX 関数と MATCH 関数を使用する

INDEX と MATCH を組み合わせることで、別のワークブック内の任意の方向（左、右、上、下）から値を検索できます。これは、取得したい列が検索列の右側にない場合（VLOOKUP の制限）に特に役立ちます。

シナリオ：別の開いているワークブックから価格を取得したいが、果物の名前が必ずしも最初の列にあるとは限らないと仮定します。

1。 結果を表示したいセル（例：C2）を選択し、以下の数式を入力してください（必要に応じてワークブック名、シート名、範囲を置き換えてください）：

=INDEX([Price.xlsx]Sheet1!$B$1:$B$24, MATCH(B2, [Price.xlsx]Sheet1!$A$1:$A$24,0))

2。Enterキーを押して確定し、必要に応じてフィルハンドルをドラッグして数式を他の行にもコピーまたは入力してください。

パラメーターの説明：

• [Price.xlsx]Sheet1!$B$1:$B$24：価格が格納されているセル範囲です。
• B2：検索する果物の名前を入力してください。
• [Price.xlsx]Sheet1!$A$1:$A$24：検索値を探す範囲です。
• 末尾の0が完全一致を保証します。

強み：左右両方向の検索に対応し、より柔軟なレイアウトを実現します。
ヒント：元のファイルを移動または名前変更する際は、数式を更新せずにそのままにしないでください。

XLOOKUP を使用したワークブック間検索（Excel 365 以降）

Excel 365 またはExcel 2021 をご利用の場合、新しい XLOOKUP 関数はさらに柔軟です。完全一致を簡単に検索でき、左側の検索をサポートし、エラーなく欠損値を自動的に処理できます。

使用方法：
結果を表示したいセルに以下を入力します：

=XLOOKUP(B2, [Price.xlsx]Sheet1!$A$1:$A$24, [Price.xlsx]Sheet1!$B$1:$B$24, "Not found")

Enterキーを押して確定し、必要に応じて数式をコピーできます。「Not found」は、検索が失敗した際に表示したい任意のテキストに変更可能です。

メリット：VLOOKUP よりも柔軟で管理しやすく、従来の数式にありがちな多くのエラーを回避できます。ただし、XLOOKUP は新しいバージョンのExcel でのみご利用いただけます。

複数条件での一致、結合されたシート内での検索、非常に大規模なファイルでのパフォーマンス問題の回避など、複雑な要件がある場合は、ソースデータを構造化テーブルで整理するか、Excel の Power Query ツールを使用して複数のワークブック間を効率的にデータのリンクすることを検討してください。