Пошук другого, третього чи N-го значення Excel

Коли потрібно знайти будь-які дані в таблиці Excel, можна використовувати кілька функцій. Наприклад, функція ВПР або ІНДЕКС разом з ПОШУКПОЗ.

Ці функції шукають у таблиці заданий фрагмент і зупиняються першому збігу. Але якщо вам потрібно знайти не тільки перший збіг, а все?

Тож почнемо!

У цій статті я продемонструю вам, як це можна зробити.

Пошук фрагмента по всій таблиці, другий, третій та N-ий збіг

За допомогою додавання нового стовпця

Допустимо, у вас є наступна табличка:

Пошук другого, третього чи N-го значення Excel

Наприклад, вам необхідно перерахувати всі «Training», які пройшла людина, в осередках після його імені.

Отже, ми можемо викликати функцію ВПР або ІНДЕКС (разом з ПОШУКПОЗ), але тут є невелика проблема – коли функція знайде перший збіг (наприклад, для Джона) вона зупиниться і поверне вам результат.

Наприклад, Джон пройшов усі тренінги, але якщо ми використовуватимемо вищезгадані функції, результатом буде лише «Excel». Функція знайшла перший збіг імені Джона, зупинилася і передала нам результат. Але як зробити так, щоб вона не зупинялася на першому збігу?

Можна додати стовпець та провести нехитрі маніпуляції з ним.

Покрокова інструкція:

  • Вставимо стовпець «Помічник» одразу після імені;

Пошук другого, третього чи N-го значення Excel

  • У перший вільний осередок пропишемо:

=A2&ПОЛІЧИЛИ($A$2:$A2;A2)

Пошук другого, третього чи N-го значення Excel

  • А тепер, в інший стовпчик пропишемо таку функцію:

=ЕСНД(ВПР($E2&ЧИСЛСТОЛБ($F$1:F1);$B$2:$C$14;2;0);””)

Пошук другого, третього чи N-го значення Excel

Ця функція, в наступних стовпчиках, вкаже, які тренінги пройшла людина, якщо якийсь тренінг вона не пройшла — буде просто пусте місце.

Що робить цю функцію?

Ми використовуємо невелику хитрість. Функція РАХУНКИ робить кожне нове ім'я людини унікальним. Як вона це робить? дуже просто, вона додає цифру до імені, наприклад, Джон1, Джон2 і так далі.

Виходить, що тепер функція ВПР не зупиниться за першого збігу, тому що їх не буде. Тепер усі імена і навіть однакові — унікальні (через цифри наприкінці імені).

$E2&ЧИСЛСТОЛБ ($F$1:F1) це сам фрагмент, яким відбувається пошук. Функція ЧИСЛСТОЛБ додає цифру, орієнтуючись на номер рядка, до кінця імені, а далі відбувається пошук по цьому фрагменту.

За допомогою масиву

Якщо вам, з якихось причин, не подобається або ви не можете використовувати спосіб з додаванням стовпчика, є ще один варіант.

Допустимо, у нас та ж табличка:

Пошук другого, третього чи N-го значення Excel

Така функція також поверне правильний результат:

=ЯСЛИПОМИЛКА(ІНДЕКС($B$2:$B$14;НАЙМЕНШИЙ(ЯКЛИ($A$2:$A$14=$D2;РЯДОК($A$2:$A$14)-1;””);ЧИСЛСТОЛБ($E$1 :E1)));””)

Щоб вставити її, вам потрібно виділити комірки, в які потрібно вставити дані (у нашому випадку від E2 до G9).

І ще важливий момент, коли пропишете саму функцію, натисніть CTRL+SHIFT+ENTER замість ENTER. Так треба зробити, тому що ми працюємо з масивом даних.

Що робить цю функцію?

Отже, давайте розберемося:

$A$2:$A$14=$D2

Цей фрагмент нашої функції порівнює значення з осередком D2.

Результат або ІСТИНА або брехня.

Приклад результату виконання:

{ІСТИНА;брехня;брехня;брехня;брехня;брехня;ІСТИНА;брехня;брехня;брехня;ІСТИНА;брехня;брехня}

Продовжимо та розглянемо наступний фрагмент:

ЯКЩО($A$2:$A$14=$D2;РЯДОК($A$2:$A$14)-1;””)

Цей фрагмент нашої функції отримує на вході масив даних (ІСТИНА або брехня) і замінює істину на номер рядка з таблички, а брехня на порожнє місце.

Ось приклад виконання цього фрагмента:

{1;””;””;””;””;””;7;””;””;””;11;””;””}

Далі:

НАЙМЕНШИЙ(ЯКЩО($A$2:$A$14=$D2;РЯДОК($A$2:$A$14)-1;”);ЧИСЛСТОЛБ($E$1:E1))

А тепер функція НАЙМЕНШИЙ запише всі найменші порядкові числа, перше, друге, третє і так далі. А функція ЧИСЛСТОЛБ надає їм номери, відповідно до номера рядка.

ІНДЕКС ($ B $ 2: $ B $ 14; НАЙМЕНШИЙ (ЯКІ ($ A $ 2: $ A $ 14 = $ D2; РЯДОК ($ A $ 2: $ A $ 14)-1;”)); ЧИСЛСТОЛБ ($ E $ 1: E1) ))

У результаті функція ІНДЕКС, за порядковими номерами, отриманими від функції ЧИСЛСТОЛБ, поверне їх значення. Тобто в першому збігу повертається Excel і так далі.

Але якщо виникне помилка, а вона обов'язково виникне, тому що, в нашому випадку, не всі люди пройшли по 3 тренінги, функція ЕСЛИПОМИЛКА замінить усі помилки на порожні місця.

Отже, у розділі статті ми використовували функцію масиву. Це зручно, тому що її можна копіювати без жодних проблем. Моя думка — це найкращий спосіб, можливо він і складніший, але його можна без проблем масштабувати.

ні варіанти знайти всі збіги, просто я вважаю що це найпрактичніші. Якщо у вас є свої «зручні» способи, будь ласка, поділіться ними у коментарях.

nBook
Додати коментар