Використання VLOOKUP з кількома критеріями в Excel: покрокова інструкція
Мабуть, не знайдеться людини, яка б жодного разу не працювала в Microsoft Excel. Переваги цього програмного забезпечення можна перераховувати довго. Від малого бізнесу до великих корпорацій, MS Excel є незамінним інструментом. Серед безлічі функцій, VLOOKUP є однією з найбільш корисних, заощаджуючи час користувачам, що працюють з великими обсягами даних. Чи можливо використовувати VLOOKUP з двома або більше критеріями? Звісно, і ми розповімо вам як. У цій статті ми розглянемо два методи використання VLOOKUP з кількома критеріями, зокрема з використанням допоміжних стовпців.
Поглиблений погляд на VLOOKUP
Продовжуйте читати, щоб дізнатися про детальні кроки, які пояснюють, як застосувати VLOOKUP з кількома критеріями, з наочними прикладами для кращого розуміння.
Що таке VLOOKUP в Excel?
VLOOKUP розшифровується як Vertical Lookup (вертикальний пошук). Це вбудована функція MS Excel, яка дозволяє користувачам шукати певні значення, переглядаючи їх вертикально на аркуші. Функція використовує просту формулу:
=VLOOKUP(значення_пошуку; таблиця; номер_стовпця; [тип_пошуку])
Де:
- значення_пошуку: значення, яке потрібно знайти в даних.
- таблиця: діапазон комірок, в якому потрібно шукати значення.
- номер_стовпця: номер стовпця в таблиці, з якого потрібно отримати результат.
- тип_пошуку: визначає, який тип відповідності шукається. TRUE (або 1) для наближеної відповідності, FALSE (або 0) для точної відповідності.
Чи можна використовувати VLOOKUP з двома критеріями?
Так, ви можете використовувати VLOOKUP з двома або навіть більшою кількістю критеріїв. Існує два основних методи: використання допоміжних стовпців та використання функції CHOOSE. Далі ми розглянемо обидва ці методи детальніше.
Як використовувати VLOOKUP з кількома критеріями?
Давайте перейдемо безпосередньо до методів, які ви можете використовувати для застосування VLOOKUP з декількома критеріями.
Метод 1: Використання допоміжних стовпців
Цей метод використовується для пошуку значення, що відповідає декільком умовам. Розглянемо крок за кроком, як це зробити:
Примітка: У прикладі нижче ми використаємо оцінки студентів з математики та природничих наук за 1, 2 та 3 семестри. Загальні бали з математики будуть обчислені окремо для кожного семестру. Ми знайдемо оцінки з математики для кожного семестру, використовуючи інформацію з таблиці праворуч.
1. Відкрийте файл MS Excel, де містяться потрібні дані.
2. Вставте новий стовпчик між двома стовпцями, які ви хочете об’єднати.
3. Об’єднайте стовпці B і D за допомогою амперсанда (&) і роздільника (,) за формулою: =B2&”, “&D2.
4. Натисніть Enter, щоб побачити результат об’єднання.
5. Скопіюйте формулу вниз для об’єднання всіх інших рядків.
6. Введіть формулу VLOOKUP там, де ви хочете отримати результат. Ви також можете переглядати та редагувати формулу в рядку формул.
7. Значенням пошуку (lookup_value) буде об’єднання клітинок H7 і I6. Запишіть формулу як H7&”, “&I6.
8. Закріпіть рядки та стовпці, натискаючи F4, оскільки нам потрібно буде заповнити й інші клітинки. Заблокуйте стовпець H та рядок 6, щоб використовувати VLOOKUP з кількома критеріями.
9. Перейдіть до наступного аргументу, яким є таблиця (table_array), додавши кому (,).
10. Виберіть діапазон, що містить потрібні дані.
11. Закріпіть посилання на клітинки, натиснувши клавішу F4.
12. Додайте кому (,) і перейдіть до наступного аргументу: номер_стовпця (col_index_num).
13. Вкажіть номер стовпця, з якого потрібно отримати значення. В цьому випадку, це третій стовпець (“Математика”). Введіть 3 у рядку формул.
14. Введіть кому (,), щоб перейти до наступного аргументу: тип_пошуку (range_lookup).
15. Виберіть FALSE (або 0) для точного пошуку, щоб отримати правильні значення.
16. Закрийте дужку, завершивши формулу.
17. Натисніть Enter, щоб отримати перше значення.
18. Скопіюйте формулу на всю таблицю, щоб отримати всі необхідні дані.
Ці кроки мають розвіяти ваші сумніви щодо використання VLOOKUP з кількома критеріями.
Метод 2: Використання функції CHOOSE
Цей метод дозволяє використовувати VLOOKUP з кількома критеріями, використовуючи функцію CHOOSE. На відміну від попереднього методу, тут вам не потрібен допоміжний стовпець. Ви можете використовувати функцію CHOOSE, щоб досягти потрібного результату. Виконайте наступні кроки, щоб використати VLOOKUP з декількома критеріями.
1. Відкрийте файл MS Excel з потрібними даними.
2. Введіть формулу VLOOKUP у потрібну комірку.
3. Значенням пошуку (lookup_value) буде об’єднання клітинок G7 і H6. Запишіть формулу як G7&”, “&H6.
4. Закріпіть рядки та стовпці, натискаючи F4, оскільки нам потрібно буде заповнити й інші клітинки. Заблокуйте стовпець G та рядок 6.
5. Введіть кому (,), щоб перейти до наступного аргументу.
6. Тут, замість звичайного table_array, використовуйте функцію CHOOSE.
7. Введіть {1,2} в фігурних дужках, як index_num, щоб створити комбінацію.
8. Введіть кому (,), щоб перейти до наступного аргументу value1.
9. Виберіть значення1, тобто стовпець імен, і заблокуйте значення, натиснувши F4.
10. Щоб об’єднати value1 з наступним стовпцем, додайте амперсанд (&) з роздільником (,), а потім виберіть значення2, тобто стовпець семестрів.
11. Закріпіть значення, натиснувши клавішу F4, і додайте кому (,), щоб перейти до наступного аргументу.
12. Щоб додати значення2, виберіть стовпець “Математика”, який є потрібним результатом, і заблокуйте значення, натиснувши клавішу F4.
13. Закрийте дужку, щоб завершити функцію CHOOSE. Тепер ви маєте table_array без допоміжного стовпця.
14. Введіть кому (,) і перейдіть до аргументу номер_стовпця (col_index_num), введіть 2, оскільки “Математика” є другим стовпцем вихідної таблиці.
15. Додайте кому (,), щоб перейти до аргументу тип_пошуку (range_lookup) і виберіть FALSE, щоб отримати точне значення.
16. Закрийте дужку та натисніть Ctrl+Shift+Enter, щоб отримати результат.
17. Скопіюйте формулу на всю таблицю, щоб отримати всі результати.
Це метод VLOOKUP з кількома критеріями з використанням функції CHOOSE.
Поширені питання
Питання 1: Чому потрібно використовувати роздільник під час створення комбінацій?
Відповідь: Якщо ми створюємо комбінації без роздільників, є ймовірність отримання однакових комбінацій для різних значень. Наприклад:
| З роздільником | Без роздільника |
| абв | 123 |
| abc, 123 | abc123 |
| abc1 | 23 |
| abc1,23 | abc123 |
Тому завжди рекомендується використовувати роздільник, щоб уникнути таких плутанин.
Питання 2: Чи потрібно додавати допоміжний стовпець саме між стовпцями даних?
Відповідь: Ні, ви можете додати допоміжний стовпець в будь-якому місці (крайній праворуч або ліворуч), якщо не хочете вносити змін в оригінальні дані. Однак вставка між стовпцями дозволяє додати лише два стовпці до table_array, а не чотири або більше. Ви можете працювати так, як вам зручно.
***
Ми розглянули два методи, які можна застосувати під час використання VLOOKUP з кількома критеріями. Сподіваємось, що цей посібник був корисним, і ви дізналися, як використовувати VLOOKUP з кількома критеріями та з кількома допоміжними стовпцями. Звертайтесь до нас із запитаннями та пропозиціями в коментарях нижче. Також, поділіться з нами, про що ви хотіли б дізнатися в майбутніх статтях.