VLOOKUP між двома книгами: простий покроковий посібник

Якщо ви працюєте з кількома книгами в Excel, вам може знадобитися скористатися функцією VLOOKUP, щоб отримати дані з однієї книги в іншу.

Функція VLOOKUP шукає певне значення в таблиці в окремій книзі та повертає відповідне значення з того самого рядка. Синтаксис має містити повне посилання на другу робочу книгу та назву аркуша.

Отримати правильні посилання може бути складно, коли ви використовуєте цю функцію вперше. У цій статті використовуються зразки електронних таблиць, щоб показати вам, як саме вводити формули.

Ви також дізнаєтеся про деякі вдосконалення функції VLOOKUP і кілька альтернатив цій функції.

Давайте розпочнемо!

Зміст

• Що таке VLOOKUP?
• Як використовувати VLOOKUP між двома книгами
  • Зразок даних
  • Крок 1. Тримайте обидві книги відкритими
  • Крок 2: Почніть вводити формулу VLOOKUP
  • Крок 3: Як посилатися на другий робочий зошит
• 3 поширені помилки VLOOKUP і способи їх усунення
  • 1. Помилка #N/A
  • 2. #Посилання! Помилка номера стовпця
  • 3. Помилка лапок і квадратних дужок
• 4 поради щодо керування різними книгами
• Як автоматично оновлювати дані
• Розширене використання функції VLOOKUP
• 2 основні альтернативи VLOOKUP
  • 1. ІНДЕКСНА ВІДПОВІДНІСТЬ
  • 2. XLOOKUP
• Як використовувати VLOOKUP на кількох аркушах
  • Крок 1: Визначте компоненти
  • Крок 2: Налаштуйте дані
  • Крок 3: Використовуйте функцію VLOOKUP
  • (Необов’язково) Крок 4: Виправте помилки #N/A
• Заключні думки

Що таке VLOOKUP?

Припустимо, у вас є книга продажу товарів і окрема книга з цінами на кожен товар. Ви хочете отримати доступ до цін з другої книги та використовувати їх у розрахунках у першій книзі.

VLOOKUP є одним із рішень цього завдання. Термін розшифровується як «Вертикальний пошук» . «

Це вбудована функція, яка дозволяє шукати конкретне значення в стовпці та повертати відповідне значення в іншому стовпці на основі розташування.

Його можна використовувати з:

• єдина електронна таблиця

• кілька аркушів в одній робочій книзі

• дві робочі книги Excel

Якщо у вас немає додаткової складності, пов’язаної з кількома книгами, перегляньте цей підручник на .

Як використовувати VLOOKUP між двома книгами

Функція VLOOKUP особливо корисна, коли потрібно шукати дані на кількох робочих аркушах або книгах.

Три основні кроки для цього:

1. Відкрийте обидві книги.

2. Введіть формулу VLOOKUP у першу книгу Excel.

3. Надайте повне кваліфіковане посилання на другу книгу.

Зразок даних

Цей простий підручник працює на прикладах, заснованих на зразках даних. Щоб продовжити, створіть нову книгу під назвою «Продажі» та введіть ці дані в стовпці A і B:

• Яблука, 40

• Апельсини, 30

• Груші, 70

• Банани, 20

Додайте третій стовпець для інформації про ціну:

Створіть другу книгу Excel під назвою «Продукт» і перейменуйте Аркуш1 на «Ціни». Введіть ці дані в стовпці A та B:

• Яблука 1,50 шт

• Апельсини, 2.30

• Груші 3,60

• Банани, 2.10

Дані виглядають так:

Щоб обчислити дохід від продажів у першій електронній таблиці, нам потрібно знайти ціну в другій електронній таблиці. VLOOKUP на допомогу!

Крок 1. Тримайте обидві книги відкритими

Під час використання формули VLOOKUP між двома книгами мають бути відкриті обидві робочі книги. Якщо друга книга закрита, функція поверне помилку.

Ви можете відкрити їх окремо або скористатися командою «Відкрити» в Excel, щоб відкрити їх обидва одночасно. Є два способи відкрити книгу:

• Перейдіть на вкладку «Файл» і виберіть «Відкрити».

• Скористайтеся комбінацією клавіш «Ctrl + O» .

Відкривши обидві робочі книги, ви можете переключатися між ними, натискаючи відповідну вкладку внизу вікна Excel.

Крок 2: Почніть вводити формулу VLOOKUP

Ось базовий синтаксис функції VLOOKUP:

=VLOOKUP(пошукове_значення, масив_таблиці, номер_індексу_стовпця, [діапазон_пошуку])

• lookup_value : значення, яке потрібно знайти в іншій книзі.

• table_array : діапазон комірок в іншій книзі, що містить дані, які ви хочете отримати.

• col_index_num : номер стовпця в діапазоні, який містить дані, які потрібно отримати.

• range_lookup : цей останній параметр необов’язковий. Якщо встановити значення TRUE або пропустити його, VLOOKUP шукатиме приблизний збіг. Якщо встановити значення FALSE, VLOOKUP шукатиме лише точну відповідність.

Припустімо, ви хочете знайти ціну яблук у другій книзі наших вибіркових даних. Ось як формула розкладеться:

• Пошукове_значення знаходиться в клітинці A2 першої книги.

• Масив таблиці знаходиться в діапазоні клітинок A1:B5 другої книги.

• col_index_num — другий стовпець, у якому міститься ціна.

Найскладнішою частиною формули VLOOKUP є правильне посилання на другу книгу.

Крок 3: Як посилатися на другий робочий зошит

Щоб завершити формулу VLOOKUP для двох робочих книг, вам знадобиться використати повне посилання, яке містить ці відомості з другої книги:

• назва робочої книги

• ім'я аркуша

• діапазон клітинок

Це гарантує, що Excel точно знає, де знайти дані, які ви шукаєте. Повне посилання використовується для аргументу table_array функції VLOOKUP.

Це значення для вибіркових даних:

• назва книги: Product.xlsx

• назва листа: Ціни

• діапазон клітинок: $A1:$B5

Щоб дізнатися ціну на перший товар, введіть таку формулу в клітинку C2 першої книги:

=VLOOKUP(A2; [Product.xlsx]Ціни!$A$1:$B$5; 2; FALSE)

На цьому малюнку показано дію функції в рядку формул для першої клітинки:

Зауважте, що назва робочої книги береться в квадратні дужки, а після назви аркуша стоїть знак оклику.

Якщо ім’я файлу Excel містить пробіл або спеціальні символи, то всю книгу та аркуш потрібно взяти в одинарні лапки.

У формулі використовуються абсолютні значення для діапазону клітинок (префікс $). Це дозволяє скопіювати формулу до решти рядків, натиснувши маркер заповнення.

Ось результати VLOOKUP:

І це все! Тепер ви знаєте, як ефективно використовувати VLOOKUP. Щоб допомогти вам у разі виникнення будь-яких помилок, у наступному розділі ми зібрали три найпоширеніші помилки VLOOKUP.

3 поширені помилки VLOOKUP і способи їх усунення

Тепер, коли ви навчилися використовувати VLOOKUP з іншої книги, ви можете зіткнутися з деякими типовими помилками.

Ось три найпоширеніші помилки та способи їх усунення.

1. Помилка #N/A

Помилка #N/A виникає, коли пошукове значення не знайдено в масиві таблиці. Щоб виправити цю помилку, ви можете спробувати наступне:

• Перевірте правопис значення пошуку.

• Переконайтеся, що шукане значення знаходиться в першому стовпці масиву таблиці.

• Переконайтеся, що значення пошуку не відформатовано як текст на двох аркушах.

• Переконайтеся, що аргумент range_lookup має значення FALSE, щоб знайти точний збіг.

У наведеному вище прикладі не вдалося знайти відповідне значення для Cherries у другій книзі.

2. #Посилання! Помилка номера стовпця

# Ref! Помилка виникає, коли аргумент col_index_num перевищує кількість стовпців у масиві таблиці.

Щоб виправити цю помилку, переконайтеся, що аргумент col_index_num менше або дорівнює кількості стовпців у вихідній таблиці.

На малюнку нижче для col_index_num встановлено значення 3, але в діапазоні посилання є лише два стовпці.

3. Помилка лапок і квадратних дужок

Помилка лапок і квадратних дужок виникає, коли ви виконуєте VLOOKUP зі значенням, що містить лапки або квадратні дужки.

Щоб виправити цю помилку, ви можете спробувати наступне:

• Використовуйте подвійні лапки навколо пошукового значення.

• Використовуйте функцію CHAR(34) , щоб вставити подвійні лапки.

• Використовуйте функцію ЯКЩО ПОМИЛКА, щоб повернути порожню клітинку, якщо пошукове значення містить лапки або квадратні дужки.

Ознайомившись із цими помилками, їх легко виправити! Щоб ще більше допомогти вам стати професіоналом VLOOKUP, у наступному розділі ми надали чотири поради щодо керування різними книгами.

4 поради щодо керування різними книгами

Працюючи з кількома книгами, важливо відстежувати, яка книга містить які дані.

Ось наші чотири найкращі поради, щоб зберегти здоровий глузд:

1. Використовуйте угоди про найменування, щоб запам’ятати, які електронні таблиці Excel містять які дані.

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

3. Використовуйте функцію IFERROR для обробки будь-яких помилок #N/A, які можуть виникнути, якщо пошукові значення не знайдено у вихідних даних.

4. Враховуйте, щоб їх не можна було перезаписати.

Як автоматично оновлювати дані

Під час роботи з кількома файлами Excel може знадобитися багато часу.

Якщо ви хочете, щоб ваша формула VLOOKUP автоматично оновлювалася, коли ви додаєте або видаляєте дані з інших робочих книг, ви можете скористатися функцією INDIRECT.

Ось як:

• У формулі VLOOKUP замініть аргумент масиву таблиці функцією INDIRECT.

• У функції INDIRECT використовуйте посилання на клітинку, яка містить масив таблиці в іншій книзі.

Щоб досягти цього за допомогою наших зразків робочих книг, виконайте такі дії.

1. Використовуйте одну клітинку в стовпці D, щоб ввести посилання на аркуш «Ціни» робочої книги «Продукт» і діапазон клітинок A1:B5 так: [Product.xlsx]Ціни!A:B

2. У клітинці C2 книги «Продажі» використовуйте цю формулу: =VLOOKUP(A2, INDIRECT($D$1), 2, FALSE)

3. Скопіюйте формулу вниз (абсолютні посилання гарантують збереження клітинки з електронною таблицею).

Ці дії забезпечують автоматичне оновлення змін вихідного аркуша у підключених клітинках.

На цьому малюнку показано функцію INDIRECT у поєднанні з VLOOKUP:

Зверніть увагу, що в цьому прикладі використовується діапазон «A:B» для посилання на два стовпці. Це означає, що коли ви додаєте більше продуктів і цін, формула продовжуватиме працювати в поточній книзі.

Тепер, коли ви відчули смак потужності VLOOKUP, давайте розглянемо деякі розширені способи використання цієї функції в розділі!

Розширене використання функції VLOOKUP

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

Ви також можете комбінувати VLOOKUP з іншими функціями. Наприклад, ви можете вставити його у функцію IF для додаткового комплексного аналізу.

Комплексний аналіз включає такі методи, як тести хі-квадрат і Т-тести парних вибірок. Ось приклад використання тесту хі-квадрат у Excel:

Досвідчені користувачі використовують VLOOKUP для порівняння наборів даних і виявлення невідповідностей. Ви знайдете інші способи в цій статті на .

Але що, якщо ви не хочете використовувати VLOOKUP з будь-якої причини? Вам пощастило! Є кілька альтернатив, про що ми розповімо в наступному розділі.

2 основні альтернативи VLOOKUP

Коли справа доходить до пошуку значень на різних аркушах, VLOOKUP часто є функцією переходу. Однак є деякі альтернативи, які можуть бути корисними в певних ситуаціях.

Два найпоширеніші:

• ВІДПОВІДНІСТЬ ІНДЕКСУ

• XLOOKUP

1. ІНДЕКСНА ВІДПОВІДНІСТЬ

INDEX і MATCH — це потужна комбінація двох функцій, які можна використовувати для пошуку значень у таблиці. На відміну від VLOOKUP, який ��имагає, щоб значення пошуку було в крайньому лівому стовпці таблиці, INDEX MATCH може шукати значення в будь-якому стовпці.

Ось як це працює:

1. Функція INDEX повертає значення з указаного діапазону на основі номера рядка та стовпця.

2. Функція MATCH повертає позицію шуканого значення в указаному діапазоні.

Щоб використовувати INDEX MATCH, вам потрібно вказати діапазон, у якому ви хочете шукати, рядок, у якому ви хочете знайти пошукове значення, і стовпець, у якому ви хочете повернути результат.

Ось приклад формули:

=ІНДЕКС(Діапазон таблиці;ПІДПИС (Значення пошуку;Діапазон пошуку;0);Індекс стовпця)

Щоб використовувати це зі зразками електронних таблиць, ось така формула:

=INDEX([Product.xlsx]Ціни!$B$1:$B$5, MATCH(A2, [Product.xlsx]Ціни!$A$1:$A$5, 0))

Це розбивається як:

• Функція MATCH шукає значення в клітинці A2 (назва продукту) у діапазоні A1:A5 на аркуші «Ціни» робочої книги «Продукт».

• Функція INDEX повертає значення з діапазону B1:B5 на аркуші «Ціни» книги «Продукт» на основі номера рядка, наданого функцією MATCH.

На цьому малюнку показана формула в дії:

2. XLOOKUP

XLOOKUP — це новіша функція, представлена ​​в Excel 365 . Він схожий на VLOOKUP, але має деякі додаткові функції.

Однією з найбільших переваг XLOOKUP є те, що він може шукати значення ліворуч від стовпця пошуку.

Ось як це працює:

1. Функція XLOOKUP повертає значення з указаного діапазону на основі пошукового значення.

2. Ви можете вказати, чи бажаєте ви отримати точну чи приблизну відповідність.

3. Ви також можете вказати, що повертати, якщо збігів не знайдено.

Щоб використовувати XLOOKUP, вам потрібно вказати значення пошуку, діапазон, у якому ви хочете шукати, і стовпець, у який ви хочете повернути результат.

Ось приклад формули:

=XLOOKUP(Пошукове значення,Діапазон пошуку,Діапазон повернення,”Не знайдено”,0)

Щоб використовувати це зі зразками електронних таблиць, ось така формула:

=XLOOKUP(A2; [Product.xlsx]Ціни!$A$1:$A$5; [Product.xlsx]Ціни!$B$1:$B$5)

Це розбивається як:

• A2: Значення, яке потрібно знайти (назва продукту).

• [Product.xlsx]Ціни!$A$1:$A$5: діапазон, у якому потрібно шукати пошукове значення (назви продуктів на аркуші «Ціни» книги «Продукт»).

• [Product.xlsx]Ціни!$B$1:$B$5: діапазон, що містить значення, що повертаються (ціни на аркуші «Ціни» книги «Продукт»).

Це формула в дії:

Як використовувати VLOOKUP на кількох аркушах

Якщо вам потрібно виконати VLOOKUP між двома робочими книгами, вам також може знадобитися використовувати VLOOKUP для кількох аркушів.

На щастя, процес для цього дуже схожий на виконання VLOOKUP між двома книгами.

Є чотири кроки.

Крок 1: Визначте компоненти

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

Наприклад, якщо ви хочете знайти значення в діапазоні A2:B6 на аркуші Jan у книзі Sales_reports.xlsx, ваша формула виглядатиме так:

=VLOOKUP(A2;[Sales_reports.xlsx]Січень!$A$2:$B$6,2;НЕПРАВДА)

Крок 2: Налаштуйте дані

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

Ви можете розглянути можливість створення різних вкладок для конкретних довідкових даних.

Крок 3: Використовуйте функцію VLOOKUP

Після налаштування даних ви можете використовувати функцію VLOOKUP для пошуку та отримання даних з інших аркушів.

Просто переконайтеся, що назва аркуша включена у вашу формулу, як показано вище.

(Необов’язково) Крок 4: Виправте помилки #N/A

Якщо ви зіткнулися з помилками #N/A під час використання VLOOKUP на кількох аркушах, ще раз перевірте формулу, щоб переконатися, що ви правильно вказали посилання на всі необхідні аркуші та діапазони.

Ви також можете переконатися, що ви виконуєте точну відповідність, а не пошук діапазону.

Заключні думки

Ви навчилися використовувати VLOOKUP для пошуку та отримання даних у двох електронних таблицях. Ця універсальна функція дозволяє легко шукати значення в одній книзі на основі відповідних значень в іншій.

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

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

Використовуючи такі функції, як INDIRECT, INDEX-MATCH або XLOOKUP, ви можете ще більше підвищити гнучкість і ефективність пошуку даних.

Продовжуйте практикуватися та досліджувати широкі можливості Excel; це лише верхівка айсберга. Щасливого пошуку даних!