Автор: Келлі Остання зміна: 2020-05-09

Наприклад, ви створюєте таблицю купівлі фруктів в Excel, і тепер вам потрібно вилучити фрукти з іншої книги та повернути відповідні ціни, як показано на скріншотах нижче. Тут я допоможу вам вирішити це за допомогою функції VLOOKKUP в Excel.

1. Відкрийте обидві книги, з яких ви будете шукати значення та повертати значення.

2. Виберіть порожню комірку, якій ви повернете ціну, введіть формулу = VLOOKUP (B2, [Price.xlsx] Аркуш1! $ A $ 1: $ B $ 24,2, FALSE) в нього, а потім перетягніть його ручку заповнення, щоб застосувати цю формулу до діапазону, як вам потрібно.

Можливо, пам’ятка переплутана для налаштування шляху збереження, імені файлу та робочого аркуша у функції VLOOKUP. Цей метод запровадить VBA для його легкого вирішення.

1 Натисніть кнопку інший + F11 клавіші, щоб відкрити вікно Microsoft Visual Basic for Applications.

2. Натисніть Insert > Модулі, а потім вставте нижче коду VBA у вікно модуля, що відкривається.

VBA: дані Vlookup і повернені значення з іншої закритої книги

Private Function GetColumn(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
xAddress = Application.ActiveWindow.RangeSelection.Address
Set xUserRange = Application.InputBox("Lookup values :", "Kutools for Excel", xAddress, Type:=8)
If Err <> 0 Then Exit Sub
Set xUserRange = Application.Intersect(xUserRange, Application.ActiveSheet.UsedRange)
xFileName = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", 1, "Select a Workbook")
If xFileName = False Then Exit Sub
Application.ScreenUpdating = False
Set xSourceWb = Workbooks.Open(xFileName)
Set xSourceSh = xSourceWb.Worksheets.Item(1)
xString = "='" & xSourceWb.Path & Application.PathSeparator & _
"[" & xSourceWb.Name & "]" & xSourceSh.Name & "'!$"
For Each xRg In xUserRange
Set xFCell = xSourceSh.Cells.Find(xRg.Value, , xlValues, xlWhole, , , False)
If Not (xFCell Is Nothing) Then
xRg.Offset(0, 2).Formula = xString & GetColumn(xFCell.Column + 1) & "$" & xFCell.Row
End If
Next
xSourceWb.Close False
Application.ScreenUpdating = True
End Sub

примітки: Цей VBA поверне значення у стовпці, який знаходиться на 2 стовпці позаду вибраного стовпця. Наприклад, я вибираю стовпець B при застосуванні цього VBA, значення відображатимуться у стовпці D. Якщо вам потрібно змінити цільовий стовпець, дізнайтеся код xRg.Offset (0, 2) .Formula = xString & GetColumn (xFCell.Column + 1) & "$" & xFCell.Row , та замініть 2 на інший номер, як вам потрібно.

3 Натисніть кнопку F5 або клацніть на прогін для запуску цього VBA.

4. У діалоговому вікні, що відкриється, вкажіть діапазон даних, який ви шукаєте, і натисніть на OK кнопки.

5. Тепер, будь ласка, виберіть книгу, де ви будете шукати значення у діалоговому вікні Вибір робочої книги, та натисніть на відкритий кнопки.

Тепер усі вибрані значення шукаються у зазначеній закритій книзі, а відповідні значення повертаються у вказаному стовпці. Дивіться знімок екрана: