Как искать или находить значения в другом файле?

Настройка ссылок для поиска с помощью функции ВПР (VLOOKUP) может быть затруднительной, особенно если вы часто изменяете путь, имя или лист исходного файла. В таких случаях автоматизация процесса поиска с помощью VBA становится более удобным решением: она позволяет выполнять поиск значений даже при закрытой исходной книге, а также автоматизирует выбор диапазона и возврат данных.

Выполните следующие шаги, чтобы использовать VBA для поиска данных в других книгах:

1. Нажмите одновременно клавиши Alt+F11, чтобы открыть окно редактора Microsoft Visual Basic for Applications.

2.В редакторе VBA выберите команду Insert>Module, затем скопируйте и вставьте приведённый ниже код в окно модуля:

VBA: Поиск данных и Возвращаемое значение из другой закрытой книги

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. Запустите макрос, нажав клавишу F5 или кнопку Run. Появится диалоговое окно «Kutools для Excel», в котором вам будет предложено выбрать диапазон ячеек со значениями для поиска.

4. После выбора диапазона нажмите кнопку OK. Вскоре появится ещё одно диалоговое окно с предложением выбрать исходную книгу (даже если она закрыта). Найдите нужный файл, выберите его и нажмите кнопку Open, чтобы подтвердить выбор.

После завершения работы макроса соответствующие значения из исходной книги будут возвращены в целевой столбец вашей Текущий лист. Если некоторые значения отсутствуют, убедитесь, что Диапазон значений поиска в Текущий лист точно совпадают со значениями в Исходные данные (регистр символов и начальные/Пробелы в конце пробелы имеют значение для точного совпадения).

Преимущества: Поддержка работы с закрытыми книгами, отсутствие жёстко заданного пути к файлу в формулах и гибкость при выборе исходных файлов «на лету».
Рекомендации: Макросы должны быть включены; VBA может не работать на защищённых листах или с файлами, не относящимися к Excel. Сохраняйте книгу в формате с поддержкой макросов (*.xlsm), если планируете часто использовать этот метод.

Если возникают ошибки, убедитесь, что в именах листов и файлов нет опечаток, проверьте корректность диапазона «Выберите диапазон» и доступность «Путь к файлу». Для отладки рекомендуется выполнять код пошагово в редакторе VBA.

Помимо классического подхода с использованием ВПР (VLOOKUP) и VBA, в Excel существуют альтернативные способы выполнения поиска данных в других книгах. Они могут быть предпочтительнее в тех случаях, когда структура ваших данных отличается, вы предпочитаете формулы макросам или вам требуется большая гибкость (например, поиск влево или использование нескольких критериев).

Использование функций ИНДЕКС и ПОИСКПОЗ для поиска в других книгах

Сочетание функций ИНДЕКС и ПОИСКПОЗ позволяет выполнять поиск значений в любом направлении — влево, вправо, вверх или вниз — in another workbook. This is especially useful when the column you want to retrieve data from is not to the right of the lookup column (a limitation of 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» можно заменить на любой другой — он будет отображаться, если совпадений не найдено.

Преимущества: Более гибкая, чем ВПР, и проще в использовании; помогает избежать множества распространённых ошибок, характерных для устаревших формул. Однако функция XLOOKUP доступна только в новых версиях Excel.

Для сложных задач, таких как поиск по нескольким критериям, работа с объединёнными листами или необходимость избежать проблем с производительностью в очень больших файлах, рекомендуется организовывать Исходные данные в виде структурированных таблиц или использовать инструмент Power Query в Excel для эффективного Связать данные между несколькими книгами.