У цьому посібнику з Excel ви дізнаєтесь, як працювати з кількома збігами (результатами) з функції VLOOKUP. Ми розглянемо дві різні техніки. Перший фактично використовує функцію VLOOKUP (разом з COUNTIF). Другий використовує INDEX / MATCH для імітації VLOOKUP.
VLOOKUP з кількома результатами
Для пошуку декількох збігів за допомогою функції VLOOKUP вам потрібно створити допоміжний стовпець у таблиці даних. Допоміжний стовпець використовує функцію COUNTIF для створення унікального ідентифікатора для кожного екземпляра. Допоміжний стовпець має бути крайнім лівим стовпцем у наборі даних. Якщо коригувати набір даних непрактично, слід переглянути інший метод у наступному розділі.
Давайте розглянемо цей метод на практиці:
Крок 1:
Зверніть увагу на формулу в комірці F6:= B5 & COUNTIF (B5: B $ 9, B5)
Зокрема, вказаний діапазон: B6: B $ 11. Зверніть увагу на знак $. Знак $ "блокує" посилання на клітинку: B $ 11. Тому, коли ви копіюєте формулу вниз, B $ 11 залишається заблокованим. B6, однак, не заблоковано, тому під час копіювання формули вниз B6 перетворюється на B7 тощо. Ця техніка створює унікальний номер для кожного знайденого екземпляра. Причина, по якій ми залишаємо B6 розблокованою, полягає в тому, що в разі обліку екземпляра його видаляють із загальної кількості, створюючи унікальний номер.
Також зверніть увагу на &. & об’єднує Ім'я продукту з номером його екземпляра, щоб створити одне поле, яке ми будемо використовувати під час пошуку.
Крок 2:
Ми перемістили новий допоміжний стовпець (project_adj) ліворуч від набору даних у B14: C19. Тепер ми можемо виконати VLOOKUP для кількох результатів. Замість того, щоб шукати шкарпетки, шукайте шкарпетки1 та шкарпетки2. Тепер у вас може бути кілька рядків результатів VLOOKUP, що представляють кілька знайдених збігів.
Недоліком цього методу є те, що ви повинні відредагувати вихідний набір даних (або скопіювати/вставити набір даних в іншому місці) для виконання кількох результатів VLOOKUP. Крім того, ви можете використовувати метод INDEX / MATCH:
INDEX / MATCH для пошуку кількох матчів
Більшість користувачів Excel усвідомлюють потужність функції VLOOKUP, але багато хто не знають про потужність функції INDEX та функції Match, що використовуються разом. Комбінацію INDEX / MATCH можна використовувати для імітації VLOOKUP з перевагою більшої гнучкості.
Примітка: Зображення безпосередньо нижче містить формули. Нижнє зображення містить результати формули.
Що відбувається у формулах вище?
MATCH - шукає позицію значення з діапазоном. У цьому прикладі MATCH шукає "Шкарпетки" у списку продуктів.
INDIRECT - Створює посилання з рядка тексту. Ми використовуємо це для коригування масиву пошуку списку продуктів. Як тільки збіг знайдено, діапазон коригується, щоб виключити це збіг із пошуку, дозволяючи знайти наступне збіг. У комірці G5 ми встановлюємо початковий діапазон на B5: B10 (встановивши початковий номер_початку в комірці F5 на 5). У G5 ми знайшли збіг у першому рядку діапазону, тому початковий номер F6 дорівнює 5+1 = 6.
INDEX - Повертає значення з масиву на основі позиції номера стовпця/рядка в цьому масиві.
!! Я думаю, включити формулу масиву, щоб у них була 1 формула для посилання …
