Получить n-е совпадение с помощью INDEX
Чтобы найти n-е совпадение значения из диапазона и получить соответствующие данные, вы можете использовать формулу, основанную на ИНДЕКС, РЯД, МАЛЫЙ и IF функции.
Как получить информацию, связанную с n-м совпадением значения с помощью INDEX?
Чтобы получить информация о 3-м матче Усэйна Болта в таблице, как показано выше, вы можете использовать функции СТРОКА и ЕСЛИ вместе, чтобы получить массив, состоящий из номеров строк всех значений Усейна Болта. Затем используйте функцию SAMLL, чтобы получить номер строки указанного вами n-го совпадающего значения. Наконец, передайте результат в функцию ИНДЕКС, чтобы получить соответствующую информацию в той же строке.
Общий синтаксис
=INDEX(return_range,SMALL(IF(lookup_array=lookup_value,ROW(lookup_array-ROW(INDEX(lookup_array,1,1))+1),n))
√ Примечание. Это формула массива, требующая ввода с помощью Ctrl + Shift + Enter.
- диапазон_возврата: Диапазон, из которого нужно вернуть информацию, соответствующую n-му совпадению. Здесь имеется в виду год или временной диапазон.
- искомый_массив: Диапазон, в котором выполняется поиск позиции строки n-го совпадения. Здесь имеется в виду диапазон победителей.
- искомое_значение: Значение, которое вы указали для поиска n-го совпадения. Здесь имеется в виду Усэйн Болт.
- n: Укажите n-е совпадение. Чтобы найти первое совпадение значения, установите n как 1; чтобы найти второе совпадение, установите n как 2.
Чтобы получить информация о 3-м матче Усэйна Болта, скопируйте или введите приведенные ниже формулы в ячейки G8 и G9 и нажмите Ctrl + Shift + Enter чтобы получить результат:
Год (ячейка G8)
= ИНДЕКС (B6: B12, МАЛЕНЬКИЙ (ЕСЛИ (C6: C12=G5,РЯД(C6: C12-ROW (ИНДЕКС (C6: C12, 1,1)) + 1),G6))
Время (ячейка G9)
= ИНДЕКС (D6: D12, МАЛЕНЬКИЙ (ЕСЛИ (C6: C12=G5,РЯД(C6: C12-ROW (ИНДЕКС (C6: C12, 1,1)) + 1),G6))
Пояснение формулы
Здесь мы используем формулу ниже в качестве примера:
=INDEX(B6:B12,SMALL(IF(C6:C12=G5,ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6))
- СТРОКА (C6: C12): Функция СТРОКА возвращает номера строк каждой ячейки в диапазоне C6: C12 в таком массиве: {6;7;8;9;10;11;12}.
- СТРОКА (ИНДЕКС (C6: C12,1,1)): Функции ИНДЕКС и СТРОКА возвращают номер первой строки диапазона поиска. C6: C12 на этом листе Excel, который 6.
- ЕСЛИ (C6: C12 = G5,СТРОКА (C6: C12)-СТРОКА (ИНДЕКС (C6: C12,1,1))+ 1) = ЕСЛИ (C6: C12 = G5,{6;7;8;9;10;11;12}-6+ 1): Функция ЕСЛИ проверяет, каждое ли значение в диапазоне C6: C12 совпадает со значением в ячейке G5, Усэйн болт. Если да, функция вернет результат «соответствующий номер строки-6 + 1»; В противном случае верните ЛОЖЬ. Таким образом, результаты будут в таком массиве: {ЛОЖЬ; 2; 3; 4; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}.
- НЕБОЛЬШОЙ(ЕСЛИ (C6: C12 = G5,СТРОКА (C6: C12)-СТРОКА (ИНДЕКС (C6: C12,1,1))+ 1), G6) = НЕБОЛЬШОЙ({ЛОЖЬ; 2; 3; 4; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}, G6): Фрагмент возвращает 3rd (значение в ячейке G6) наименьшее значение из массива, которое 4.
- ИНДЕКС (B6: B12,НЕБОЛЬШОЙ(ЕСЛИ (C6: C12 = G5,СТРОКА (C6: C12)-СТРОКА (ИНДЕКС (C6: C12,1,1))+ 1), G6)) = ИНДЕКС (B6: B12;4) Функция ИНДЕКС возвращает 4th значение в диапазоне года B6: B12, Которая является Пекин 2008.
Связанные функции
Функция ИНДЕКС Excel возвращает отображаемое значение на основе заданной позиции из диапазона или массива.
Функция ЕСЛИ - одна из самых простых и полезных функций в книге Excel. Он выполняет простой логический тест, который зависит от результата сравнения, и возвращает одно значение, если результат ИСТИНА, или другое значение, если результат ЛОЖЬ.
Функция Excel ROW возвращает номер строки ссылки.
Связанные формулы
Получить n-е совпадение с помощью ВПР
Предположим, у вас есть список значений и соответствующая им информация. Чтобы получить связанную информацию о n-м совпадении значения, вы можете использовать формулу, основанную на функции ВПР.
Получить информацию, связанную с наименьшими значениями n
Чтобы получить данные, соответствующие наименьшему, второму наименьшему или n-му наименьшему значению в списке, таблице или строке в Excel, вы можете использовать формулы ИНДЕКС и ПОИСКПОЗ вместе с функцией МАЛЕНЬКИЙ.
Получите информацию, соответствующую максимальному значению
Чтобы получить данные, соответствующие максимальному значению в списке, таблице или строке в Excel, вы можете использовать формулы ИНДЕКС и ПОИСКПОЗ вместе с функцией МАКС.
Получите информацию, соответствующую минимальному значению
Чтобы получить данные, соответствующие минимальному значению в таблице, вы можете использовать формулы ИНДЕКС и ПОИСКПОЗ вместе с функцией МИН.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
- Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
- Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
- Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
- 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
