Извлечение информации, связанной с наименьшими n значениями
Чтобы извлечь данные, соответствующие наименьшему, второму наименьшему или n-му наименьшему значению в списке, таблице или строке Excel, используйте формулу, сочетающую функции ИНДЕКС и ПОИСКПОЗ вместе с функцией НАИМЕНЬШИЙ.
Как извлечь информацию, соответствующую наименьшим n значениям?
Чтобы узнать рекорд мужского спринта на 100 м на Олимпийских играх за 1996–2020 и связанную с ним информацию из приведённой выше таблицы, сначала используйте функцию НАИМЕНЬШИЙ для поиска наименьшего значения в диапазоне «Временной диапазон». Затем передайте результат функции ПОИСКПОЗ, чтобы определить строку, в которой находится это значение. После этого функция ИНДЕКС извлечёт данные из соответствующей строки.
Общий синтаксис
=INDEX(return_range,MATCH(SMALL(lookup_array,n),lookup_array,0))
√ Примечание: Это формула массива, которую необходимо вводить с помощью комбинации клавиш Ctrl+Shift+Enter.
- диапазон_возврата: Диапазон, из которого функция ИНДЕКС возвращает значение, соответствующее наименьшему n-му элементу. В данном случае — это диапазон победителей или годов.
- n: Указывает наименьшие n значений. Чтобы найти наименьшее значение, установите n равным 1; чтобы найти второе наименьшее значение, установите n равным 2.
- массив_поиска: Диапазон, в котором функции НАИМЕНЬШИЙ и ПОИСКПОЗ извлекают и находят n-е наименьшее значение. В данном случае это временной диапазон.
Чтобы узнать рекорд мужского спринта на 100 м на Олимпийских играх за 1996-2020, скопируйте или введите приведённые ниже формулы в ячейку G5 и нажмите Enter, чтобы получить результат:
НАИМЕНЬШИЙ()D6:D12,1)
Чтобы узнать сопутствующую информацию о рекорде мужского спринта на 100 м на Олимпийских играх за 1996-2020, скопируйте или введите приведённые ниже формулы в ячейки G6 и G7 и нажмите Enter, чтобы получить результаты:
=ИНДЕКС()C6:C12,ПОИСКПОЗ(НАИМЕНЬШИЙ())D6:D12,1),D6:D12,0))
=ИНДЕКС()B6:B12,ПОИСКПОЗ(НАИМЕНЬШИЙ())D6:D12,1),D6:D12,0))
Пояснение формулы
В качестве примера используем следующую формулу:
=INDEX(C6:C12,MATCH(SMALL(D6:D12,1),D6:D12,0))
- SMALL(D6:D12,1):Функция НАИМЕНЬШИЙ возвращает 1-е наименьшее значение из диапазона D6:D12 («Временной диапазон»), которое составляет 9,63.
- ПОИСКПОЗ()SMALL(D6:D12,1),D6:D12,0)=ПОИСКПОЗ()9,63,D6:D12,0): Тип совпадения 0 указывает функции ПОИСКПОЗ найти позицию точного значения 9,63 в диапазоне «Временной диапазон» D6:D12, чтобы определить строку с соответствующей информацией. Функция вернёт 3, так как значение 9,63 находится на 3-й позиции столбца времени.
- ИНДЕКС()C6:C12,ПОИСКПОЗ()SMALL(D6:D12,1),D6:D12,0)) = ИНДЕКС(C6:C12Функция ИНДЕКС возвращает 3-е значение из диапазона победителей C6:C12, то есть Усэйн Болт.
Связанные функции
Функция ИНДЕКС Excel возвращает Отображаемое значение на основе заданной позиции из диапазона или массива.
Функция ПОИСКПОЗ в Excel ищет заданное значение в указанном диапазоне ячеек и возвращает его относительную позицию.
Связанные формулы
Получение информации, соответствующей Минимальное значение
Чтобы извлечь данные, соответствующие минимальному значению в таблице, используйте совместно функции ИНДЕКС, ВЫДЕЛИТЬФОРМУЛЫ и МИН.
Получение информации, соответствующей Максимальное значение
Чтобы извлечь данные, соответствующие максимальному значению в списке, таблице или строке Excel, используйте совместно функции ИНДЕКС, ПОИСКПОЗ и МАКС.
Поиск следующего наибольшего совпадения с помощью ИНДЕКС и ПОИСКПОЗ
Чтобы найти следующее наибольшее совпадающее число (то есть наименьшее значение, которое больше или равно заданному) в диапазоне числовых значений Excel, используйте функции ИНДЕКС и ПОИСКПОЗ с типом совпадения –1.
Поиск ближайшего совпадающего значения по нескольким критериям
Иногда требуется найти ближайшее или приближённое совпадение по нескольким критериям. С комбинацией функций ИНДЕКС, ПОИСКПОЗ и ЕСЛИ вы легко и быстро справитесь с этой задачей в Excel.
Лучшие инструменты для повышения продуктивности в Office
Kutools для Excel — Помогает Вам выделяться из толпы
Kutools для Excel предлагает более 300 функций,гарантируя, что всё необходимое всегда под рукой…
Office Tab — Включает вкладочное чтение и редактирование в Microsoft Office (включая Excel)
- Переключайтесь между десятками открытых документов всего за секунду!
- Сокращает сотни кликов мышью каждый день — забудьте о «мышечной руке» раз и навсегда!
- Повышает продуктивность на 50 % при одновременном просмотре и редактировании нескольких документов.
- Добавляет эффективность вкладок в Office (включая Excel) — как в Chrome, Edge и Firefox.