Перегляд відео - Пошук другого, третього або N -го відповідного значення
Що стосується пошуку даних у Excel, я часто використовую дві дивовижні функції - VLOOKUP та INDEX (переважно разом із функцією MATCH).
Однак ці формули призначені для пошуку лише першого екземпляра значення пошуку.
Але що, якщо ви хочете шукати друге, третє, четверте або N-е значення.
Ну, це можливо з невеликою кількістю додаткової роботи.
У цьому підручнику я покажу вам різні способи (з прикладами) того, як шукати друге або N -е значення в Excel.
Пошук другого, третього або N -го значення в Excel
У цьому посібнику я розгляну два способи пошуку другого або N-го значення в Excel:
- Використання допоміжного стовпця.
- Використання формул масиву.
Почнемо і зануримось прямо зараз.
За допомогою стовпця помічника
Припустимо, ви є координатором навчання в організації та маєте набір даних, як показано нижче. Ви хочете перерахувати всі тренінги перед іменем співробітника.
У наведеному вище наборі даних працівники пройшли навчання з використання різних інструментів Microsoft Office (Excel, PowerPoint та Word).
Тепер ви можете скористатися функцією VLOOKUP або комбінацією INDEX/MATCH, щоб знайти навчання, яке пройшов співробітник. Однак він поверне лише перший відповідний екземпляр.
Наприклад, у випадку з Джоном він пройшов усі три навчання, але коли я шукаю його ім’я за допомогою VLOOKUP або INDEX/MATCH, він завжди повертатиме „Excel”, що є першим тренінгом для його імені у списку .
Для цього ми можемо використати допоміжний стовпець і створити в ньому унікальні значення пошуку.
Ось кроки:
- Вставте стовпець перед стовпцем, у якому зазначено навчання.
- У клітинку В2 введіть таку формулу:
= A2 & COUNTIF ($ A $ 2: $ A2, A2)
- У клітинці F2 введіть таку формулу та скопіюйте та вставте всі інші клітинки:
= IFNA (VLOOKUP ($ E2 & COLUMNS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
Наведена вище формула повертає навчання для кожного працівника в тому порядку, в якому він зазначений у списку. У разі, якщо для співробітника немає переліку навчання, він повертає пробіл.
Як працює ця формула?
Формула COUNTIF у стовпці -помічнику робить ім’я кожного працівника унікальним, додаючи до нього номер. Наприклад, перший приклад Івана стає Іоанном 1, другий - Іваном 2 тощо.
Формула VLOOKUP тепер використовує ці унікальні імена співробітників для пошуку відповідного навчання.
Зауважте, що $ E2 & COLUMNS ($ F $ 1: F1) - це значення пошуку у формулі. Це додасть до назви працівника номер на основі номера стовпця. Наприклад, коли ця формула використовується у комірці F2, значення пошуку стає “John1”. У клітині G2 вона стає “John2” тощо.
Використання формули масиву
Якщо ви не хочете змінювати вихідний набір даних, додаючи допоміжні стовпці, ви також можете скористатися формулою масиву для пошуку другого, третього або n -го значення.
Припустимо, у вас такий самий набір даних, як показано нижче:
Ось формула, яка поверне правильне значення пошуку:
= IFERROR (ІНДЕКС ($ B $ 2: $ B $ 14, МАЛЕНЬКИЙ (ЯКЩО ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1), ""), COLUMNS ($ E $ 1 : E1))), "")
Скопіюйте цю формулу та вставте її у комірку E2.
Зауважте, що це формула масиву, і вам потрібно використовувати Control + Shift + Enter (утримуйте клавіші Control і Shift і натисніть клавішу Enter), а не натискати лише клавішу Enter.
Натисніть тут, щоб завантажити файл прикладу.
Як працює ця формула?
Давайте розберемо цю формулу на частини і подивимося, як вона працює.
$ A $ 2: $ A $ 14 = $ D2
Наведена вище частина формули порівнює кожну клітинку в A2: A14 зі значенням у D2. У цьому наборі даних він перевіряє, чи містить клітинка ім’я “Джон” чи ні.
Він повертає масив TRUE з FALSE. Якщо клітинка має ім'я "Джон", це було б істиною, інакше це було б неправдою.
Нижче наведено масив, який ви отримаєте в цьому прикладі:
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}
Зауважте, що він має ІСТИНУ на 1 -й, 7 -й та 111 -й позиціях, оскільки там, де в наборі даних з’являється ім’я Джон.
ЯКЩО ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, "")
Наведена вище формула IF використовує масив TRUE та FALSE і замінює TRUE позицією його появи у списку (задано ROW ($ A $ 2: $ A $ 14) -1) та FALSE на “” (пробіли). Нижче наведено отриманий масив, який ви отримаєте з цією формулою IF:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Зауважте, що 1, 7 та 11 - це позиція, у якій Джон занесений до списку.
МАЛЕНЬКИЙ (ЯКЩО ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), СТОЛКИ ($ E $ 1: E1))
Функція SMALL тепер вибирає перше найменше, друге найменше, третє найменше число з цього масиву. Зауважте, що він використовує функцію COLUMNS для створення номера стовпця. У комірці E2 функція COLUMNS повертає 1, а функція SMALL повертає 1. У комірці F2 функція COLUMNS повертає 2, а функція SMALL повертає 7.
ІНДЕКС ($ B $ 2: $ B $ 14, МАЛИЙ (ЯКЩО ($ A $ 2: $ A $ 14 = $ D2, РЯД ($ A $ 2: $ A $ 14) -1, »»), СТОЛКИ ($ E $ 1: E1) ))
Тепер функція INDEX повертає значення зі списку у стовпці B на основі позиції, поверненої функцією SMALL. Отже, у комірці E2 він повертає "Excel", який є першим елементом у B2: B14. У комірці F2 він повертає PowerPoint, який є сьомим елементом у списку.
Оскільки бувають випадки, коли для деяких співробітників є лише одне -два навчання, функція INDEX повертає помилку. Функція IFERROR використовується для повернення пробілу замість помилки.
Зауважте, що в цьому прикладі я використовував посилання на діапазони. Однак у практичних прикладах корисно перетворити дані в таблицю Excel. Перетворюючи в таблицю Excel, ви можете використовувати структуровані посилання, що полегшує створення формул. Крім того, таблиця Excel може автоматично враховувати будь -які нові навчальні елементи, додані до списку (тому вам не доведеться щоразу коригувати формули).
Що ви робите, коли вам потрібно шукати друге, третє або N-е значення? Я впевнений, що є ще кілька способів це зробити. Якщо ви використовуєте щось простіше, ніж перелічене тут, поділіться з нами всіма у розділі коментарів.
Натисніть тут, щоб завантажити файл прикладу.
