Как найти второе максимальное значение в Excel

Обновлено: 01.07.2024

Как быстро найти или вернуть второе по величине или наименьшее значение в Excel?

Найти наибольшее или наименьшее значение в диапазоне может быть просто для большинства пользователей Excel, но как насчет поиска или возврата второго или n-го по величине или наименьшего значения из диапазона? Здесь этот учебник расскажет вам формулу для быстрого возврата второго по величине или наименьшего значения в Excel.

Найти или вернуть второе по величине или наименьшее значение с помощью формул

Здесь я представляю формулы, которые помогут вам найти второе по величине или наименьшее значение в диапазоне.

Выберите пустую ячейку, например F1, введите эту формулу =НАИБОЛЬШИЙ(A1:D8,2) и нажмите клавишу Enter, чтобы получить второе по величине значение диапазона. Смотрите скриншот:

Если вы хотите найти второе наименьшее значение, вы можете использовать эту формулу =МАЛЕНЬКИЙ(A1:D8,2), см. снимок экрана:

Совет. В приведенных выше формулах A1: D8 — это диапазон ячеек, в котором вы хотите найти значение, 2 указывает на второе по величине или наименьшее значение, которое вы хотите найти, и вы можете изменить их по своему усмотрению.

Найдите и выберите самое высокое или самое маленькое значение с помощью Kutools for Excel

Найти и вернуть наибольшее или наименьшее значение из указанного диапазона несложно, но в некоторых случаях вам нужно не только узнать наибольшее или наименьшее значение, но и определить его положение. В этом случае вы можете применить Kutools for ExcelВыбрать ячейки с помощью утилиты Max & Min Value, чтобы быстро справиться с этим без каких-либо формул.

После установки Kutools for Excel сделайте следующее: (Бесплатно загрузите Kutools for Excel сейчас!)

<р>1. Выберите диапазон ячеек, который вы хотите найти, и найдите максимальное или минимальное значение, а затем нажмите Kutools > Выбрать > Выбрать ячейки с максимальным и минимальным значением. Смотрите скриншот:

<р>2. В диалоговом окне выталкивания выполните следующие действия:

1) Укажите тип ячеек, из которых вы хотите найти максимальное/минимальное значение, вы можете искать в ячейках формул, ячейках значений или как в ячейках формулы, так и в ячейках значений;

2) Укажите, чтобы выбрать максимальное значение или максимальное значение;

3) Укажите, чтобы выбрать максимальное или минимальное значение из всего выбора или каждой строки/каждого столбца в выборе;

4) Укажите, чтобы выбрать все ячейки соответствия или только первую.

<р>3. Нажмите "ОК", и будет выбрано наибольшее или наименьшее значение.

Excel предлагает несколько интересных функций для работы с числами. Один из них - найти n-е по величине значение с критериями. Скажем, у вас есть какие-то цифры. С помощью Excel вы можете найти среди них 2-е или 3-е по величине число, удовлетворяющее критерию. Это можно сделать с помощью функций ЕСЛИ и НАИБОЛЬШИЙ. В этом уроке мы узнаем, как найти n-е наибольшее значение с критериями.

Рисунок 1. Пример поиска n-го по величине значения с помощью критериев

Общая формула

=НАИБОЛЬШИЙ(ЕСЛИ(диапазон=критерий,значения),n)

Основная цель этой формулы – найти n-е по величине значение. Это значение в базе данных, которое соответствует заданным критериям.

Процесс

В этой формуле используются функции НАИБОЛЬШИЙ и ЕСЛИ. Во-первых, вам нужно указать диапазон и целое число вместо n-го значения. Это извлечет n-е значение из диапазона. Для того чтобы удовлетворить критериям, необходимо применить формулу ЕСЛИ.В противном случае функция НАИБОЛЬШИЙ будет применяться ко всем данным и не будет соответствовать критериям. Следовательно, использование функции ЕСЛИ обеспечивает логическую проверку соответствия критериям.

Настройка данных

Следующий пример содержит базу данных с информацией о студентах. Столбцы A, B и C содержат имена учащихся, пол и возраст.

Рисунок 2. Пример данных

Чтобы найти второго по возрасту ученика, нам нужно:

Перейдите к ячейке G4.
Назначьте формуле =НАИБОЛЬШИЙ(ЕСЛИ(B2:B8="F",C2:C8),E4) значение G4.
Нажмите CTRL + SHIFT + ВВОД, чтобы применить формулу к G4.

Рисунок 3. Применение формулы к данным

Здесь критерием является то, что учащийся должен быть женского пола. В Cell G4 возраст второй старшей ученицы по имени Флора Дейзи, которой 13 лет.

Excel сделал работу с числами очень простой. Например, мы можем найти n-е по величине значение, удовлетворяющее условию. Функции ЕСЛИ и НАИБОЛЬШИЙ помогают нам достичь того, чему мы научились в этом уроке.

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

Это сообщение не ответило на ваш вопрос? Получите решение, связавшись с экспертом.

Я хочу найти самую большую дату в n-й строке. Но мой n выражается в форме ПОИСКПОЗ(). Я пробовал НАИБОЛЬШИЙ(ПОИСКПОЗ(A4,A:A, 0):ПОИСКПОЗ(A4,A:A, 0), 1). Работает только при большом (N:N,1)

С помощью формулы массива мы можем найти второе по величине значение из каждой группы в Google Таблицах.

Если вы просто хотите найти второе по величине значение, например, вторую по величине зарплату в столбце, вы можете использовать функцию НАИБОЛЬШИЙ в Google Таблицах.

Это будет как показано ниже для столбца B.

Вы можете изменить ссылку на столбец B:B, если зарплаты находятся в другом столбце.

Теперь взгляните на приведенную ниже таблицу, в которой указаны баллы учащихся на спортивном мероприятии.

Я отметил второй по величине балл каждого учащегося в столбце C.

Я хочу извлечь соответствующие строки (строки 2, 8 и 11), используя формулу массива в Google Таблицах.

Это несложная задача, если вы знаете логику. Вот шаги, или вы можете кратко изложить логику.

<р>1. Отсортировать столбец A (Имя) в порядке возрастания, а столбец C (Оценка) — в порядке убывания.

<р>2. Используйте Countifs, чтобы найти текущее количество отсортированного столбца A (имена).

<р>3. Отфильтровать результат шага 1, если результат шага 2 равен 2.

Мы включим все вышеперечисленные три шага в одну формулу, чтобы найти второе по величине значение из каждой группы в Google Таблицах. Вот так!

Формула для поиска второго по величине значения из каждой группы

Для приведенных выше примеров данных мы можем использовать приведенную ниже формулу комбинации.

Это означает, что формула ФИЛЬТР фильтрует результат формулы шага 1, если результат формулы шага 2 равен 2.

Примечание:

Как видите, мои исходные данные в A1:C уже отсортированы по именам, а не по баллам. Моя формула для поиска второго по величине значения из каждой группы в Google Sheets может использоваться в отсортированных или несортированных исходных данных. Независимо от того, имена отсортированы или не отсортированы. Вот почему я включил в формулу сортировку.
Чтобы получить третье по величине значение из каждой группы, вы можете изменить критерий 2 в последней части формулы на 3. Это означает, что приведенная выше формула полезна для фильтрации n-го по величине значения из каждой группы в Google Таблицах.
Вместо оценок вы можете использовать ту же формулу в столбце даты или отметки времени.
Вместо второго по величине значения вы можете изменить формулу, чтобы она возвращала второе по величине значение, заменив =2 на .

Вторые по величине значения из каждой группы без дубликатов

Предположим, что в приведенных выше исходных данных оценки «Марка» (имя в столбце А) такие же, как показано ниже (оценки остальных остаются прежними).

Здесь вторым по величине значением будет 18 в соответствии с нашей формулой выше. Потому что самое высокое значение 18 повторяется дважды.

Я хочу пропустить дубликаты. Что делать?

Предположим, что исходные данные находятся в формате A1:C на вкладке "Данные". Если это так, используйте приведенную ниже формулу SORTN в A2 на новой вкладке с именем «Результат».

Затем используйте ту же формулу фильтра в E2 на вкладке "Результат", а не на вкладке "Данные". Вы получите правильный результат.

У этого метода есть одна проблема. Если все баллы в группе одинаковые, например, все баллы «Оценка» равны 18, то результат не будет иметь второго по величине балла «Оценка».

Как я уже упоминал, приведенную выше формулу можно использовать в столбце даты или отметки времени вместо числового столбца. Так что вы можете найти вторую по величине дату или временную метку из каждой группы.

Это все о том, как найти второе по величине значение в каждой группе из таблицы или списка в Google Таблицах.

На листе Excel мы можем получить наибольшее, второе по величине или n-е по величине значение, применив функцию "Большой". Но если в списке есть повторяющиеся значения, эта функция не будет пропускать дубликаты при извлечении n-го по величине значения. Как в этом случае получить n-е по величине значение без дубликатов в Excel?

Перейти к:

Общая формула:

Используйте приведенную ниже формулу, чтобы найти наибольшее значение N без дубликатов.

Пояснения по синтаксису:

МАКС. В Excel эта функция возвращает наибольшее числовое значение из диапазона входных значений. Узнайте больше о функции MAX.
ЕСЛИ. Функция ЕСЛИ в Excel поможет вернуть одно значение для результата ИСТИНА, а другое — для результата ЛОЖЬ.
Диапазон — представляет входные значения из заданного рабочего листа.
Символ запятой (,) — это разделитель, который помогает разделить список значений.
Скобка () — основное назначение этого символа — группировать элементы.

Пример:

Теперь мы посмотрим, как найти N-е наибольшее значение без дубликатов.
См. изображение ниже.
Здесь мы зададим входные значения в столбце B.
Во-первых, мы хотим получить наибольшее значение, используя функцию MAX в ячейке E3. Используя формулу =MAX(диапазон).
Затем введите заданную формулу в ячейку E4.
После этого нажмите клавиши CTRL + SHIFT + ENTER.
Наконец, он отображает результат в выбранной ячейке.

Вывод:

Надеюсь, вы поняли формулу поиска N-го по величине значения без дубликатов в Excel. Оставьте комментарий или ответ ниже — дайте мне знать, что вы думаете! Я часто отвечаю на запросы. Чтобы узнать о дальнейших обновлениях, свяжитесь с Geek Excel и Excel Formulas!

Читайте также: