Посмотрите видео - найдите второе, третье или N-е совпадающее значение
Когда дело доходит до поиска данных в Excel, я часто использую две удивительные функции - ВПР и ИНДЕКС (в основном в сочетании с функцией ПОИСКПОЗ).
Однако эти формулы предназначены для поиска только первого экземпляра значения поиска.
Но что, если вы хотите найти второе, третье, четвертое или N-е значение.
Что ж, это можно сделать, если немного поработать.
В этом руководстве я покажу вам различные способы (с примерами) поиска второго или N-го значения в Excel.
Найдите второе, третье или N-е значение в Excel
В этом руководстве я расскажу о двух способах поиска второго или N-го значения в Excel:
- Использование вспомогательного столбца.
- Использование формул массива.
Давайте начнем и сразу же приступим к делу.
Использование вспомогательного столбца
Предположим, вы являетесь координатором обучения в организации и имеете набор данных, как показано ниже. Вы хотите перечислить все обучение перед именем сотрудника.
В приведенном выше наборе данных сотрудники прошли обучение различным инструментам Microsoft Office (Excel, PowerPoint и Word).
Теперь вы можете использовать функцию ВПР или комбинацию ИНДЕКС / ПОИСКПОЗ, чтобы найти обучение, пройденное сотрудником. Однако он вернет только первый соответствующий экземпляр.
Например, в случае с Джоном он прошел все три тренировки, но когда я нахожу его имя с помощью ВПР или ИНДЕКС / ПОИСКПОЗ, он всегда возвращает «Excel», что является первым обучением для его имени в списке. .
Для этого мы можем использовать вспомогательный столбец и создавать в нем уникальные значения поиска.
Вот шаги:
- Вставьте столбец перед столбцом, в котором перечислены тренировки.
- В ячейке B2 введите следующую формулу:
= A2 & СЧЁТЕСЛИ ($ A $ 2: $ A2; A2)
- В ячейке F2 введите следующую формулу и скопируйте и вставьте для всех остальных ячеек:
= IFNA (ВПР ($ E2 & COLUMNS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
Приведенная выше формула вернет обучение для каждого сотрудника в том порядке, в котором он указан в списке. Если для сотрудника не указано обучение, возвращается пустое поле.
Как работает эта формула?
Формула СЧЁТЕСЛИ во вспомогательном столбце делает имя каждого сотрудника уникальным, добавляя к нему число. Например, первый экземпляр John становится John1, второй экземпляр становится John2 и так далее.
Формула ВПР теперь использует эти уникальные имена сотрудников для поиска подходящего обучения.
Обратите внимание, что $ E2 & COLUMNS ($ F $ 1: F1) - это значение поиска в формуле. Это добавит номер к имени сотрудника на основе номера столбца. Например, когда эта формула используется в ячейке F2, значение поиска становится «John1». В ячейке G2 он становится «John2» и так далее.
Использование формулы массива
Если вы не хотите изменять исходный набор данных, добавляя вспомогательные столбцы, вы также можете использовать формулу массива для поиска второго, третьего или n-го значения.
Предположим, у вас есть такой же набор данных, как показано ниже:
Вот формула, которая вернет правильное значение поиска:
= ЕСЛИОШИБКА (ИНДЕКС ($ 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.
Ниже представлен массив, который вы получите в этом примере:
{ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ}
Обратите внимание, что он имеет значение ИСТИНА в 1-й, 7-й и 111-й позиции, так как именно там в наборе данных появляется имя Джон.
ЕСЛИ ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””)
Вышеупомянутая формула IF использует массив ИСТИНА и ЛОЖЬ и заменяет ИСТИНА позицией ее появления в списке (заданной как СТРОКА ($ A $ 2: $ A $ 14) -1) и ЛОЖЬ на «» (пробелы). Ниже приведен результирующий массив, который вы получите с помощью этой формулы ЕСЛИ:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Обратите внимание, что 1, 7 и 11 - это позиция вхождения Джона в списке.
МАЛЕНЬКИЙ (ЕСЛИ ($ A $ 2: $ A $ 14 = $ D2, СТРОКА ($ 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, ROW ($ A $ 2: $ A $ 14) -1, ””), СТОЛБЦЫ ($ E $ 1: E1) ))
Функция ИНДЕКС теперь возвращает значение из списка в столбце B на основе позиции, возвращаемой функцией МАЛЕНЬКИЙ. Следовательно, в ячейке E2 он возвращает «Excel», который является первым элементом в B2: B14. В ячейке F2 он возвращает PowerPoint, который является седьмым элементом в списке.
Поскольку бывают случаи, когда для некоторых сотрудников проводится только одно или два обучения, функция ИНДЕКС возвращает ошибку. Функция ЕСЛИОШИБКА используется для возврата пробела вместо ошибки.
Обратите внимание, что в этих примерах я использовал ссылки на диапазоны. Однако на практических примерах полезно преобразовать данные в таблицу Excel. Преобразовывая в таблицу Excel, вы можете использовать структурированные ссылки, что упрощает создание формул. Кроме того, таблица Excel может автоматически учитывать любые новые элементы обучения, которые добавляются в список (так что вам не придется каждый раз корректировать формулы).
Что вы делаете, когда вам нужно найти второе, третье или N-е значение? Я уверен, что есть другие способы сделать это. Если вы используете что-то более простое, чем перечисленное здесь, поделитесь со всеми нами в разделе комментариев.
Щелкните здесь, чтобы загрузить файл примера.
