Як виконати VLOOKUP кілька критеріїв і стовпців у Google Таблицях

Автор

Google Таблиці, нарівні з MS Excel, є потужним інструментом для оперування даними, що виходить далеко за рамки звичайного їх зберігання. Завдяки різноманітності функцій, управління інформацією стає простим та швидким. Особливо варто відзначити функцію VLOOKUP, яка дозволяє проводити вертикальний пошук значень. Подібно до MS Excel, VLOOKUP в Google Таблицях дає змогу знаходити потрібні дані у вертикальному напрямку та витягувати їх з інших таблиць або аркушів. Однак, синтаксис VLOOKUP в Google Таблицях передбачає пошук лише в одному стовпці за раз. Якщо ви шукаєте способи використання VLOOKUP з кількома критеріями та стовпцями, ви потрапили за адресою. У цій статті ми розглянемо, як застосовувати VLOOKUP з багатьма критеріями та стовпцями в Google Таблицях, а також обговоримо ситуації, коли це може бути необхідно. Розпочнемо з вивчення стандартного синтаксису VLOOKUP.

Застосування VLOOKUP з кількома критеріями та стовпцями в Google Таблицях

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

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

Синтаксис звичайної функції VLOOKUP в Google Таблицях виглядає так:

=VLOOKUP( search_key, range, index, [is_sorted])

Кожен з цих аргументів означає:

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

  • range: це діапазон клітинок, у межах якого VLOOKUP проводить пошук. Важливо, щоб стовпець зі значенням search_key був першим у цьому діапазоні. Також у цьому діапазоні повинен знаходитися стовпець з цільовим значенням.

  • index: це номер стовпця в межах вказаного діапазону, який містить потрібне цільове значення. Перший стовпець має індекс 1, другий – 2 і так далі.

  • is_sorted: це необов’язковий аргумент, який вказує, чи відсортований перший стовпець діапазону. Приймає значення TRUE або FALSE.

Тепер, коли ви ознайомилися з синтаксисом VLOOKUP, давайте розглянемо, коли може знадобитися VLOOKUP з кількома критеріями.

Коли необхідний VLOOKUP з кількома критеріями в Google Таблицях?

Існує ряд ситуацій, коли використання VLOOKUP з кількома критеріями в Google Таблицях може бути корисним. Ось деякі з них:

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

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

Як використовувати VLOOKUP з кількома критеріями в Google Таблицях?

Існує два основних методи застосування VLOOKUP з кількома критеріями. Перший метод полягає у використанні допоміжного стовпця для отримання необхідних даних. Розглянемо цей метод детальніше.

Примітка: Для наочності, ми будемо використовувати дані про бали з математики та природничих наук за три семестри, а також список імен студентів. За допомогою описаних нижче методів, ми окремо знайдемо бали з математики для кожного з трьох семестрів.

Метод 1: Використання допоміжного стовпця

Використання допоміжного стовпця є одним із методів застосування VLOOKUP з кількома критеріями в одному стовпці. Цей метод передбачає додавання додаткового стовпця, який ми будемо використовувати для з’єднання значень з інших стовпців. У нашому випадку ми вставимо допоміжний стовпець безпосередньо перед стовпцем з іменами. Це зробить допоміжний стовпець першим у діапазоні. Ми використаємо його для об’єднання значень зі стовпців “Ім’я” та “Семестр”. Ось покрокові інструкції:

Використання допоміжного стовпця:

1. Відкрийте Google Таблиці з необхідними даними.

2. Вставте допоміжний стовпець ліворуч від стовпця “Ім’я”, клацнувши правою кнопкою миші на заголовку цього стовпця та вибравши “Вставити 1 стовпець зліва”.

3. У першу клітинку допоміжного стовпця введіть формулу =C2&","&D2, щоб об’єднати значення з клітинок.

4. Натисніть Enter, щоб побачити результат, наприклад, “John,1”.

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

6. Виберіть клітинку, куди ви хочете ввести значення, та почніть введення формули VLOOKUP.

7. Введіть search_key, що складається з клітинок H7 та I6, з’єднаних амперсандом та роздільником. Зафіксуйте посилання на комірку та стовпець за допомогою клавіші F4.

8. Додайте кому (,), щоб перейти до наступного аргументу range. Виберіть стовпці з даними, де потрібно шукати значення.

9. Зафіксуйте діапазон, натиснувши клавішу F4, та додайте кому (,), щоб перейти до наступного аргументу index.

10. У аргументі index введіть номер стовпця, де знаходиться потрібне значення. У нашому випадку це 4-й стовпець, тому вводимо 4.

11. Додайте кому (,), щоб перейти до аргументу is_sorted. Введіть 0 для точного співпадіння.

12. Закрийте дужку і натисніть Ctrl+Enter, щоб отримати потрібне значення.

13. Перетягніть маркер заповнення, щоб скопіювати формулу на інші клітинки таблиці.

Це метод використання допоміжного стовпця для застосування VLOOKUP з кількома критеріями в Google Таблицях. Далі ми розглянемо, як це можна зробити за допомогою ARRAY FORMULA.

Метод 2: Використання ФОРМУЛИ МАСИВА (ARRAY FORMULA)

Іншим методом є використання VLOOKUP з кількома критеріями за допомогою ARRAY FORMULA. Обидва методи працюють схоже, але різниця в тому, що цей метод не потребує допоміжного стовпця. Значення допоміжного стовпця та діапазон будуть створені безпосередньо у формулі. Ось інструкції:

1. Введіть формулу =ARRAYFORMULA у потрібну клітинку.

2. Введіть формулу VLOOKUP.

3. Виберіть і зафіксуйте контрольні клітинки G7 та H6 за допомогою клавіші F4. Розділіть їх за допомогою амперсанда та роздільника.

4. Додайте кому (,) та почніть створення діапазону, відкривши фігурну дужку {.

5. Виберіть перший стовпець, тобто стовпець “Ім’я”, та зафіксуйте його за допомогою клавіші F4.

6. Додайте роздільник (&","&) та виберіть стовпець “Семестр”, який ми поєднаємо зі стовпцем “Ім’я”. Зафіксуйте виділені клітинки за допомогою клавіші F4.

7. Додайте кому (,) та виберіть третій стовпець “Математика”, який містить цільові значення, та завершіть визначення діапазону.

8. Закрийте фігурну дужку та додайте кому (,) для переходу до наступного аргументу.

9. Введіть значення індексу стовпця, де розташоване цільове значення. У цьому випадку, ми введемо 2, щоб отримати дані зі стовпця “Математика”.

10. Додайте кому (,) та введіть 0 для точного співпадіння, а потім закрийте дужку, щоб завершити формулу VLOOKUP.

11. Знову закрийте дужку, щоб завершити формулу ARRAY FORMULA.

12. Натисніть Enter, щоб отримати результат.

13. Перетягніть маркер заповнення, щоб скопіювати формулу на інші клітинки таблиці.

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

***

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