Подивіться відео - Як використовувати функцію VLOOKUP з кількома критеріями
Функція Excel VLOOKUP у своїй базовій формі може шукати одне значення пошуку та повертати відповідне значення з зазначеного рядка.
Але часто виникає необхідність використовувати Excel VLOOKUP з кількома критеріями.
Як використовувати VLOOKUP з кількома критеріями
Припустимо, у вас є дані з іменем студентів, типом іспиту та математичним балом (як показано нижче):
Використання функції VLOOKUP для отримання балів з математики для кожного студента для відповідних рівнів іспитів може стати проблемою.
Можна стверджувати, що кращим варіантом було б перебудова набору даних або використання зведеної таблиці. Якщо це працює для вас, нічого подібного. Але у багатьох випадках ви застрягаєте на даних, які у вас є, і зведена таблиця може не бути варіантом.
У таких випадках цей підручник для вас.
Тепер є два способи отримати значення пошуку за допомогою VLOOKUP з кількома критеріями.
- Використання колонки помічника.
- Використання функції ВИБІР.
VLOOKUP з кількома критеріями - використання стовпця -помічника
Я прихильник допоміжних стовпців у Excel.
Я знаходжу дві істотні переваги використання допоміжних стовпців перед формулами масиву:
- Це полегшує розуміння того, що відбувається на робочому аркуші.
- Це робить його швидшим у порівнянні з функціями масиву (помітно у великих наборах даних).
Тепер не зрозумійте мене неправильно. Я не проти формул масиву. Я люблю дивовижні речі, які можна зробити за допомогою формул масиву. Просто я зберігаю їх для особливих випадків, коли всі інші варіанти не допомагають.
Повертаючись до актуального питання, допоміжний стовпець потрібен для створення унікального кваліфікатора. Цей унікальний кваліфікатор потім можна використовувати для пошуку правильного значення. Наприклад, у даних є три Matt, але існує лише одна комбінація Matt та Unit Test або Matt та Mid-Term.
Ось кроки:
- Вставте допоміжний стовпець між стовпцями B і C.
- Використовуйте таку формулу у стовпці -помічнику: = A2 & ”|” & B2
- Це створить унікальні кваліфікатори для кожного екземпляра, як показано нижче.
- Це створить унікальні кваліфікатори для кожного екземпляра, як показано нижче.
- Використовуйте таку формулу в G3 = VLOOKUP ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)
- Копія для всіх клітинок.
Як це працює?
Ми створюємо унікальні кваліфікатори для кожного екземпляра імені та іспиту. У використаній тут функції VLOOKUP значення пошуку було змінено на $ F3 & ”|” & G $ 2 так, що обидва критерії пошуку об’єднані та використовуються як єдине значення пошуку. Наприклад, значенням пошуку для функції VLOOKUP у G2 є Matt | Unit Test. Тепер це значення пошуку використовується для отримання рахунку від C2: D19.
Пояснення:
Ймовірно, вам прийде в голову кілька питань, тому я подумав, що спробую відповісти тут:
- Чому я використав | символу, приєднуючись до двох критеріїв? - В деяких виключно рідкісних (але можливих) умовах у вас можуть бути два критерії, які відрізняються, але в результаті вони дають однаковий результат у поєднанні. Ось дуже простий приклад (вибачте мене за брак творчості тут):
Зауважте, що хоча А2 і А3 різні, а В2 і В3 різні, комбінації в кінцевому підсумку однакові. Але якщо ви використовуєте роздільник, то навіть комбінація буде іншою (D2 і D3).
- Чому я вставив допоміжний стовпець між стовпцями В і С, а не в крайньому лівому куті? - Немає ніякої шкоди, якщо вставити стовпчик -помічник ліворуч. Насправді, якщо ви не хочете стримувати вихідні дані, це має бути вашим шляхом. Я зробив це, оскільки це змушує мене використовувати меншу кількість клітинок у функції VLOOKUP. Замість того, щоб мати 4 стовпці в масиві таблиць, я міг би керувати лише 2 стовпцями. Але це тільки я.
Зараз немає єдиного розміру, який би підходив усім. Деякі люди можуть вважати за краще не використовувати жодну допоміжну колонку під час використання VLOOKUP з кількома критеріями.
Отже, ось вам метод допоміжних стовпців.
Завантажте файл прикладу
VLOOKUP з кількома критеріями - використання функції ВИБІР
Використання формул масиву замість допоміжних стовпців економить нерухомість на робочому аркуші, і продуктивність може бути однаково хорошою, якщо використовувати її у меншій кількості разів у книзі.
Враховуючи той самий набір даних, що використовувався вище, ось формула, яка дасть вам результат:
= VLOOKUP ($ E3 & ”|” & F $ 2, ВИБІРИ ({1,2}, $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19), 2, 0)
Оскільки це формула масиву, використовуйте її за допомогою Control + Shift + Enter, а не просто Enter.
Як це працює?
У формулі також використовується поняття допоміжного стовпця. Відмінність полягає в тому, що замість того, щоб поміщати стовпець -помічник у аркуш, розгляньте його як дані віртуальних помічників, які є частиною формули.
Дозвольте мені показати вам, що я маю на увазі під даними віртуальних помічників.
На наведеному вище малюнку, коли я вибираю частину формули ВИБІР і натискаю F9, вона показує результат, який дасть формула ВИБІР.
Результат {{"Метт | одиничний тест", 91; "Боб | одиничний тест", 52;…}
Це масив, де кома представляє наступну клітинку в тому ж рядку, а крапка з комою означає, що наступні дані знаходяться в наступному стовпці. Отже, ця формула створює 2 стовпці даних - один стовпець має унікальний ідентифікатор, а другий - оцінку.
Тепер, коли ви використовуєте функцію VLOOKUP, вона просто шукає значення у першому стовпці (цих даних віртуального 2 стовпця) і повертає відповідну оцінку.
Завантажте файл прикладу
Ви також можете використовувати інші формули для пошуку за кількома критеріями (наприклад, INDEX/MATCH або SUMPRODUCT).
Чи є ще якийсь спосіб це зробити? Якщо так, поділіться зі мною у розділі коментарів.
Вам також можуть сподобатися наступні підручники ПОГЛЯДУ:
- VLOOKUP Vs. ІНДЕКС/МАТЧ
- Отримайте декілька значень пошуку без повторення в одній клітинці.
- Як зробити VLOOKUP чутливим до регістру
- Використовуйте IFERROR з VLOOKUP, щоб позбутися #помилок.
