() translation by (you can also view the original English article)
Коли вам потрібно знайти і витягти стовпець даних з однієї таблиці і помістити її в іншу, потрібно скористатися функцією VLOOKUP. Ця функція працює в будь-якій версії Excel під Windows і на Mac, а також в Google Sheets. Вона дозволить вам знайти дані в одній таблиці використовуючи деякий ідентифікатор в ній, загальний з іншою таблицею. Обидві таблиці можуть перебувати на різних аркушах або навіть в в різних книгах. Є також функція HLOOKUP, яка робить те ж саме, але з даними, розташованими горизонтально, по рядках.
Функції MATCH та INDEX корисні, коли вас цікавить місце розташування конкретних даних, таких як стовпець або рядок, яка містить ім'я людини.
Кращі варіанти
Перед тим, як ми зануримося в функції Excel, ви в курсі, що Envato Market містить масу скриптів і плагінів Excel, які дозволять вам виконувати просунуті функції?
Наприклад, ви можете:
- експортувати дані WordPress в Excel
- синтаксично аналізувати і отримувати дані з Excel за допомогою PHP класу
- конвертувати таблицю Excel в відповідну HTML таблицю
- конвертувати файли Excel в об'єкт .NET DataTable
- та багато іншого
Скрінкаст
Якщо ви хочете слідувати інструкціям цієї інструкції, використовуючи свій файл Excel, ви можете це зробити. Або, якщо хочете, скачайте zip файл, що додається до цієї інструкції, яка містить електронну таблицю з прикладом, вона називається vlookup example.xlsx.
Використання VLOOKUP
Коли VLOOKUP знаходить ідентифікатор, який ви задали в вихідних даних, вона може знайти будь-яку клітинку в цьому рядку і повернути вам цю інформацію. Майте на увазі, що у вихідних даних ідентифікатор повинен перебувати в першому стовпчику таблиці.
Синтаксис
Синтаксис функції VLOOKUP:
= VLOOKUP (шукане значення, діапазон таблиці, номер стовпця, [true / false])
Ось що означають ці аргументи:
- Шукане значення. Осередок, в якій знаходиться унікальний ідентифікатор.
- Діапазон таблиці. Діапазон осередків, які містять ідентифікатор в першому стовпці, а в наступних стовпцях розташовуються інші дані.
- Номер стовпця. Номер стовпця, в якому знаходяться дані, які вам потрібні. Не плутайте його з буквою стовпця. На малюнку вище, штати знаходяться в стовпці 4.
- True / False. Цей аргумент необов'язковий. True означає, що прийнятно приблизне збіг, а False означає, що допустимо тільки точний збіг.
Ми хочемо знайти кількість продажів з таблиці на малюнку нижче, тому використовуємо такі аргументи:
Визначення імені діапазону для створення абсолютної посилання
У Vlookup example.xlsx подивіться на лист Sales Amounts. Ми введемо формулу в клітинку B5, потім використовуємо можливість автозаповнення, щоб скопіювати формулу в осередку нижче неї. Це означає, що діапазон комірок у формулі повинен бути абсолютним посиланням. Хороший спосіб зробити це - це задати ім'я для діапазону комірок.
Завдання імені діапазону в Excel
- Перед тим, як вводити формулу, перейдіть на лист source data.
- Виділіть комірки від A4 (заголовок для стовпця Order #) до H203. Швидкий спосіб це зробити - це клікнути по A4, потім натиснути Ctrl-Shift-End (Command-Shift-End на Маке).
- Клікніть всередині поля Name Box (Ім'я) на колонку A (зараз в поле Name Box відображається A4).
- Надрукуйте data, потім натисніть Enter.
- Тепер ви можете використовувати у формулі ім'я data замість $ A $ 4: $ H $ 203.
Завдання імені діапазону в Google Sheets.
В Google Sheets ім'я задається трохи по іншому.
- Клацніть по заголовку першого стовпчика ваших вихідних даних, потім натисніть Ctrl-Shift-Стрілка вправо (Command-Shift-Стрілка вправо на Маках). Потім виберіть рядок заголовків стовпців.
- Натисніть Ctrl-Shift-Стрілка вниз (Command-Shift-Стрілка вниз на Маке). Потім виберіть актуальні дані.
- Клікніть в меню Data (Дані), потім виберіть Named and protected ranges (Іменовані та захищені діапазони).
- В поле Name and protected ranges box справа надрукуйте data, потім клацніть Done.
Введення формули
Щоб ввести формулу, перейдіть на лист Sales Amounts і клікніть по осередку B5.
Введіть формулу:
= VLOOKUP (A5, data, 8, FALSE)
Натисніть Enter.
Результат повинен бути рівний 40. Щоб заповнити значення нижче за стовпцем, знову клікніть на B5, якщо необхідно. Помістіть курсор миші на точку автозаповнення в правому нижньому кутку комірки, так, щоб курсор змінив свою форму на хрестик.
Клацніть двічі, щоб заповнити значення нижче за стовпцем.
Якщо хочете, ви можете запустити функцію VLOOKUP в наступних кількох шпальтах, щоб витягти інші поля, такі як прізвище або штат.
Використання MATCH
Функція MATCH не повертає вам значення з даних; ви задаєте значення, яке ви шукаєте і функція повертає позицію цього значення. Це як запитати, де знаходиться Мейн Стріт, 135, і отримати відповідь, що це четверте будівлю вниз по вулиці.
Синтаксис
Синтаксис функції MATCH:
= MATCH (шукане значення, діапазон таблиці, [тип збігу])
Аргументи:
- Шукане значення. Осередок, що містить унікальний ідентифікатор.
- Діапазон таблиці. Діапазон осередків, в якому ми шукаємо.
- Тип збігу. Необов'язковий. Визначає, наскільки точним має бути збіг, згідно:
| Наступне більшого значення | -1 | Значення повинні розташовуватися в порядку спадання. |
| Цільове значення | 0 | Значення можуть йти в будь-якому порядку. |
| Наступне менше значення | 1 | Тип за замовчуванням. Значення повинні розташовуватися в порядку зростання. |
Як і у випадку з функцією VLOOKUP, можливо, ви знайдете використання функції MATCH простішим, якщо призначите ім'я діапазону. Перейдіть на лист Source Data, виділіть діапазон від B4 (заголовок стовпчика c номером замовлення) до низу, клікніть в поле Name box (Ім'я), розташоване на колонку A, і назвіть його order_number. Зверніть увагу на те, що значення розташовані в порядку зростання.
Перейдіть на вкладку Match листа. У осередок B5 введіть функцію:
=MATCH(A5, order_number, 1)
Якщо ви не задавали ім'я діапазону, вам треба написати функцію так:
=MATCH(A5, 'Source Data'!A5:A203,0)
У будь-якому випадку ви побачите, що значення знаходиться в 14й позиції (що робить його 13м по порядку).
Використання INDEX
Функція INDEX протилежна функції MATCH і схожа на VLOOKUP. Ви повідомляєте функції, який рядок і стовпець даних вам потрібні, і вона повідомляє вам значення, яке знаходиться в осередку.
Cинтаксис
Синтаксис функції INDEX наступний:
= INDEX (діапазон даних, номер рядка, [номер стовпця])
Аргументи:
- Діапазон даних. Як і в інших двох функціях, це таблиця з даними.
- Номер рядка. Номер рядка з даними, яка необов'язково є рядком листа. Якщо діапазон починається на 10-му рядку листа, тоді це рядок №1.
- Номер стовпця. Номер стовпця діапазону даних. Якщо діапазон починається в стовпці E, тоді номер стовпця - 1.
Документація Excel говорить нам, що номер стовпця це необов'язковий аргумент, але номер рядка теж є свого роду необов'язковим. Якщо діапазон таблиці складається тільки з одного рядка чи одного шпальти, вам не потрібно використовувати інший аргумент.
Перейдіть на лист Index і клікніть на осередку C6. Спочатку ми хочемо знайти, що міститься в рядку 9, стовпці 3 таблиці. У формулі ми використовуємо ім'я діапазону, яке ми створили раніше.
Введіть формулу:
= INDEX (data, A6, B6)
Вона повертає прізвище покупця: Strevell. Змінюйте значення в осередках A6 і B6, і осередок C6 буде показувати різні результати (зверніть увагу, що багато рядки містять однакові штати і назви продуктів).
Висновок
Здатність робочого листа заглядати в інший робочий лист і витягувати звідти дані - це відмінний інструмент. Таким чином, у вас може бути один лист, який містить всі дані, необхідні вам для різних цілей, і ви можете отримувати звідти те, що вам потрібно для конкретних випадків.
