Перейти до основного матеріалу

Як шукати / знаходити значення в іншій книзі?

У цій статті йдеться про пошук значень та повернення даних з іншої книги, а також про пошук / пошук значень з іншої книги. Тут я докладно представлю три рішення.


Перегляд даних і повернення значень з іншої книги в Excel

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

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

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

примітки:
(1) У наведеній вище формулі B2 - це фрукт, який ви будете шукати з іншої книги, Price.xlsx вказує ім'я файлу книги, з якої ви будете шукати, Sheet1 означає назву листа, з якого ви будете шукати, і 1 долар США : $ B $ 24 - це діапазон, з якого ви будете шукати. Ви можете змінювати їх, як вам потрібно.
(2) Після закриття книги, з якої ви шукали, формула автоматично оновиться до = VLOOKUP (B2, 'W: \ test \ [Price.xlsx] Sheet1'! $ A $ 1: $ B $ 24,2, FALSE), W: \ test \ - це шлях збереження книги, з якого ви шукали.

Наразі всі ціни повернулися правильно, як показано на лівому скріншоті. І ці ціни оновлюватимуться автоматично, якщо зміниться оригінальна книга, яку ви шукали.

стрічка записки Формула занадто складна, щоб запам'ятати? Збережіть формулу як автоматичний запис тексту для повторного використання лише одним клацанням у майбутньому!
Детальніше ...     Безкоштовне випробування

Дані пошуку та повернені значення з іншої закритої книги з VBA

Можливо, пам’ятка переплутана для налаштування шляху збереження, імені файлу та робочого аркуша у функції 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. Тепер, будь ласка, виберіть книгу, де ви будете шукати значення у діалоговому вікні Вибір робочої книги, та натисніть на відкритий кнопки.

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

Найкращі інструменти продуктивності офісу

🤖 Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати   |  Видалити порожні рядки   |  Об’єднайте стовпці або клітинки без втрати даних   |   Раунд без Формули ...
Супер пошук: VLookup за кількома критеріями    Багатозначний VLookup  |   VLookup на кількох аркушах   |   Нечіткий пошук ....
Розширений розкривний список: Швидке створення випадаючого списку   |  Залежний спадний список   |  Виберіть розкривний список, що вибирається ....
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняйте діапазони та стовпці ...
Особливості: Фокус сітки   |  Перегляд дизайну   |   Велика панель формул    Диспетчер робочих книг і аркушів   |  Бібліотека ресурсів (автотекст)   |  Вибір дати   |  Об’єднайте робочі аркуші   |  Шифрування/розшифрування клітинок    Надсилайте листи за списком   |  Супер фільтр   |   Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи, ...)   |   50 + Графік типи (діаграма Ганта, ...)   |   40+ Практичний Формули (Розрахуйте вік на основі дня народження, ...)   |   19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху, ...)   |   12 Перетворення Tools (Числа до слів, Валютна конверсія, ...)   |   7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділені клітини, ...)   |   ... і більше

Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу.  Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...

Опис


Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi this works great thanks! Would it be at all possible to show me how i would change the code if i have the workbook open that i would like to lookup the data in?
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations