Як отримати дані з електронної таблиці, використовуючи VLOOKUP, MATCH і INDEX

Коли вам потрібно знайти та витягти стовпець даних з однієї таблиці та помістити її до іншої, використовуйте функцію VLOOKUP. Ця функція працює в будь-якій версії Excel під Windows та Mac, а також у Google Sheets. Вона дозволить вам знайти дані в одній таблиці, використовуючи певний ідентифікатор у ній, спільний з іншою таблицею. Обидві таблиці можуть знаходитися на різних аркушах або навіть у різних книгах. Є також функція HLOOKUP, яка робить те саме, але з даними, розташованими горизонтально, по рядках.

Функції MATCH та INDEX корисні, коли вас цікавить розташування конкретних даних, таких як стовпець або рядок, що містить ім'я людини.

Найкращі варіанти

Перед тим, як ми поринемо у функції Excel, ви знаєте, що Envato Market містить масу скриптів і плагінів Excel, які дозволять вам виконувати просунуті функції?

Наприклад, ви можете:

  • експортувати дані WordPress до Excel
  • синтаксично аналізувати та отримувати дані з Excel за допомогою PHP класу
  • конвертувати таблицю Excel у відповідну HTML таблицю
  • конвертувати файли Excel на об'єкт .NET DataTable
  • і багато іншого
електронної
Скрипти та плагіни Excel на Envato Market

Якщо ви хочете дотримуватися вказівок цього посібника, використовуючи свій файл Excel, ви можете це зробити. Або, якщо хочете, скачайте zip файл, що додається до цього посібника, який містить електронну таблицю з прикладом, вона називаєтьсяvlookup example.xlsx.

Використання VLOOKUP

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

дані
Унікальний ідентифікатор повинен бути серійним номером, не повинно бути двох однакових ідентифікаторів в одній таблиці.

Синтаксис функції VLOOKUP:

=VLOOKUP(потрібне значення, діапазон таблиці, номер стовпця, [true/false])

Ось що означають ці аргументи:

  • Шукане значення. Осередок, в якому знаходиться унікальний ідентифікатор.
  • Діапазон таблиці. Діапазон осередків, які містять ідентифікатор у першому стовпці, а наступних стовпцях розташовуються інші дані.
  • Номер стовпця. Номер стовпця, у якому знаходяться дані, які вам потрібні. Не плутайте його з літерою стовпця. На малюнку вище штати знаходяться в стовпці 4.
  • True/False. Цей аргумент необов'язковий.Trueозначає, що прийнятно приблизний збіг, аFalseозначає, що допустимо лише точне збіг.

Ми хочемо знайти кількість продажів з таблиці на малюнку нижче, тому використовуємо такі аргументи:

отримати
Синтаксис функції VLOOKUP

Визначення імені діапазону для створення абсолютного посилання

УVlookup example.xlsxподивіться на листSales Amounts. Ми введемо формулу в комірку B5, потім використовуємо можливість автозаповнення, щоб скопіювати формула в комірки нижче за неї. Це означає, що діапазон осередків у формулі має бути абсолютним посиланням. Хороший спосіб зробити це - це встановити ім'я для діапазону осередків.

Завдання імені діапазону в Excel

Завдання імені діапазону в Google Sheets.

У Google Sheets ім'я задається трохи інакше.

  1. Клацніть на заголовку першого стовпця ваших вихідних даних, потім натиснітьCtrl-Shift-Стрілка вправо(Command-Shift-Стрілка вправона Маках). Потім виберітьрядок заголовків стовпців.
  2. НатиснітьCtrl-Shift-Стрілка вниз(Command-Shift-Стрілка внизна Маку). Потім оберіть актуальні дані.
  3. НатиснітьData (Дані), а потім виберітьNamed and protected ranges (Іменовані та захищені діапазони).
  4. У поліName and protected ranges boxправоруч надрукуйтеdata, потім натиснітьDone.
таблиці
Визначення імені діапазону в Google Sheets

Введення формули

Щоб ввести формулу, перейдіть на аркушSales Amountsі клацніть по осередкуB5.

отримати
Введення функції VLOOKUP

Результат повинен дорівнювати 40. Щоб заповнити значення нижче по стовпцю, знову натисніть на B5, якщо необхідно. Помістіть курсор миші на точкуавтозаповненняу нижньому правому кутку комірки, так, щоб курсор змінив свою форму на перехрестя.

таблиці
Коли ви помістите курсор миші на точку в правому нижньому кутку комірки, він перетвориться на перехрестя Автозаповнення.

Клацніть двічі, щоб заповнити значення нижче по стовпцю.

дані
Двічі клацніть перехрестям Автозаповнення, щоб скопіювати формулу нижче по стовпцю

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

Використання MATCH

Функція MATCH не повертає вам значення даних; ви задаєте значення, яке ви шукаєте та функція повертає позицію цього значення. Це як запитати, де знаходиться Мейн стріт, 135, і отримати відповідь, що це четверта будівля вниз по вулиці.

Синтаксис функції MATCH:

=MATCH(потрібне значення, діапазон таблиці, [тип збігу])

  • Шукане значення. Осередок, що містить унікальний ідентифікатор.
  • Діапазон таблиці. Діапазон осередків, у якому ми шукаємо.
  • Тип збігу. Необов'язковий. Визначає, наскільки точним має бути збіг, відповідно до: