Unlimited WordPress themes, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Computer Skills
  2. Office
Computers

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

by
Difficulty:IntermediateLength:LongLanguages:
This post is part of a series called Spreadsheets for Finance.
Spreadsheets for Finance: Calculating Internal Rate of Return
Getting Started With Numbers on OS X

Ukrainian (українська мова) translation by Andy Yur (you can also view the original English article)

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

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

Кращі варіанти

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

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

Excel scripts and plugins on Envato Market
Скрипти та плагіни Excel на Envato Market

Скрінкаст

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

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

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

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

Синтаксис

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

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

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

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

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

syntax of vlookup
Синтаксис функції VLOOKUP

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

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

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

  1. Перед тим, як вводити формулу, перейдіть на лист source data.
  2. Виділіть комірки від A4 (заголовок для стовпця Order #) до H203. Швидкий спосіб це зробити - це клікнути по A4, потім натиснути Ctrl-Shift-End (Command-Shift-End на Маке).
  3. Клікніть всередині поля Name Box (Ім'я) на колонку A (зараз в поле Name Box відображається A4).
  4. Надрукуйте data, потім натисніть Enter.
  5. Тепер ви можете використовувати у формулі ім'я data замість $ A $ 4: $ H $ 203.
Name box displaying a range name
Поле name box (ім'я) зазвичай відображає адресу поточної осередки. Клікніть в нього і надрукуйте ім'я, щоб визначити діапазон.

Завдання імені діапазону в 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.
Data menu in Google Sheets
Визначення імені діапазону в Google Sheets

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

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

Введіть формулу:

= VLOOKUP (A5, data, 8, FALSE)

Натисніть Enter.

entering the vlookup function
Введення функції VLOOKUP

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

mouse pointer on the autofill dot
Коли ви навідете курсор миші на точку в правому нижньому кутку комірки, він перетвориться в перехрестя Автозаполнения.

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

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

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

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

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

Синтаксис

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

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

Аргументи:

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

Наступне більшого значення

-1

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

Цільове значення

0

Значення можуть йти в будь-якому порядку.

Наступне менше значення

1

Тип за замовчуванням. Значення повинні розташовуватися в порядку зростання.

Як і у випадку з функцією VLOOKUP, можливо, ви знайдете використання функції MATCH простішим, якщо призначите ім'я діапазону. Перейдіть на лист Source Data, виділіть діапазон від B4 (заголовок стовпчика c номером замовлення) до низу, клікніть в поле Name box (Ім'я), розташоване на колонку A, і назвіть його order_number. Зверніть увагу на те, що значення розташовані в порядку зростання.

name box with one column defined
Іменований діапазон може бути просто одним стовпцем, одним рядком, або навіть однією клітиною

Перейдіть на вкладку Match листа. У осередок B5 введіть функцію:

=MATCH(A5, order_number, 1)

entering the match function
Введення функції MATCH

Якщо ви не задавали ім'я діапазону, вам треба написати функцію так:

=MATCH(A5, 'Source Data'!A5:A203,0)

У будь-якому випадку ви побачите, що значення знаходиться в 14й позиції (що робить його 13м по порядку).

completed match function
Результат функції MATCH

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

Функція INDEX протилежна функції MATCH і схожа на VLOOKUP. Ви повідомляєте функції, який рядок і стовпець даних вам потрібні, і вона повідомляє вам значення, яке знаходиться в осередку.

Cинтаксис

Синтаксис функції INDEX наступний:

= INDEX (діапазон даних, номер рядка, [номер стовпця])

Аргументи:

  • Діапазон даних. Як і в інших двох функціях, це таблиця з даними.
  • Номер рядка. Номер рядка з даними, яка необов'язково є рядком листа. Якщо діапазон починається на 10-му рядку листа, тоді це рядок №1.
  • Номер стовпця. Номер стовпця діапазону даних. Якщо діапазон починається в стовпці E, тоді номер стовпця - 1.

Документація Excel говорить нам, що номер стовпця це необов'язковий аргумент, але номер рядка теж є свого роду необов'язковим. Якщо діапазон таблиці складається тільки з одного рядка чи одного шпальти, вам не потрібно використовувати інший аргумент.

Перейдіть на лист Index і клікніть на осередку C6. Спочатку ми хочемо знайти, що міститься в рядку 9, стовпці 3 таблиці. У формулі ми використовуємо ім'я діапазону, яке ми створили раніше.

Введіть формулу:

= INDEX (data, A6, B6)

entering the index function
Введення функції index

Вона повертає прізвище покупця: Strevell. Змінюйте значення в осередках A6 і B6, і осередок C6 буде показувати різні результати (зверніть увагу, що багато рядки містять однакові штати і назви продуктів).

Висновок

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

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.