Функция Excel LARGE для получения n-го максимального значения
Эта статья расскажет вам, как использовать функцию НАИБОЛЬШИЙ в Excel со многими примерами формул.
При анализе набора чисел часто имеет смысл найти самые большие из них. Получить максимальное значение очень просто с помощью функции MAX. Когда дело доходит до нацеливания на конкретное наибольшее значение, скажем, на 2-е или 3-е по величине число в наборе данных, функция НАИБОЛЬШИЙ пригодится.
Функция Excel НАИБОЛЬШИЙ
Функция НАИБОЛЬШИЙ в Excel используется для возврата n-го по величине значения из набора числовых данных. Например, он может рассчитать наивысший балл, 2-й по величине заказ, результат 3-го места и так далее.
Синтаксис состоит из двух аргументов, оба из которых обязательны:
БОЛЬШОЙ(массив, к)
Где:
- Массив – диапазон или массив, где искать наибольшее значение.
- K – позиция от самого высокого до возврата, т. е. k-е наибольшее значение в наборе данных.
LARGE относится к категории статистических функций. Он доступен во всех версиях Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 и более ранних версиях.
3 вещи, которые нужно знать о функции LARGE
Прежде чем мы перейдем к более практическим вещам и начнем создавать свои собственные формулы, обратите внимание на 3 простых факта, которые объясняют суть:
- Функция НАИБОЛЬШИЙ обрабатывает только числовые значения. Пустые ячейки, текст и логические значения игнорируются.
- Если множество содержит какие-либо ошибки, возвращается ошибка.
- В случае множество содержит n значений, НАИБОЛЬШИЙ(массив,1) вернет максимальное значение, а НАИБОЛЬШИЙ(массив,n) вернет минимальное значение.
Основная БОЛЬШАЯ формула
В базовой форме формулу БОЛЬШОЙ в Excel построить очень просто. В качестве первого аргумента вы указываете диапазон числовых значений. Во втором аргументе вы определяете позицию от самой большой до возврата.
В приведенной ниже примерной таблице предположим, что вы хотите узнать 2-й по величине балл. Это можно сделать с помощью следующей формулы:
=БОЛЬШОЙ(B2:B10, 2)
Как использовать формулу НАИБОЛЬШИЙ в Excel – примеры
А теперь давайте рассмотрим более конкретные варианты использования и посмотрим, чем может быть полезна функция НАИБОЛЬШИЙ.
Как получить верхние значения N в Excel
Чтобы получить самые большие значения 3, 5, 10 и т. д. с помощью одной формулы, выполните следующие действия:
- Введите интересующие позиции в отдельные ячейки. Эти числа будут использоваться как к ценности.
- Создайте БОЛЬШУЮ формулу, применяя ссылку абсолютного диапазона для множество (в нашем случае $B$2:$B$10) и относительную ссылку на ячейку для к (Д3).
- Введите формулу в самую верхнюю ячейку, а затем перетащите ее вниз в нижние ячейки. Сделанный!
В качестве примера, мы собираемся найти 3 лучших результата в таблице ниже. Для этого набираем в D3, D4 и D5 цифры 1, 2 и 3 соответственно и в E3 вводим следующую формулу:
=БОЛЬШОЙ($B$2:$B$10, D3)
Перетащите его через E4, и вы получите такой результат:
Вместо того, чтобы вводить позиции на листе, вы можете использовать функцию СТРОКИ с расширением ссылки на диапазон для создания к значения автоматически, как объяснено в формуле Excel, чтобы найти первые N значений в списке.
=НАИБОЛЬШИЙ($B$2:$B$10, СТРОКИ(B$2:B2))
Кончик. Вы также можете использовать функцию НАИБОЛЬШИЙ для извлечения первых N записей с расширенным фильтром.
Как суммировать или усреднять самые большие значения N
Чтобы суммировать n первых значений в наборе данных, вы можете использовать НАИБОЛЬШИЙ вместе с СУММПРОИЗВ или СУММ следующим образом:
СУММПРОИЗВ(БОЛЬШОЙ(множество{1, …, н }))
Или же
СУММА(БОЛЬШОЙ(множество{1, …, н}))
Чтобы усреднить самый высокий н значений, объедините функции СРЗНАЧ и НАИБОЛЬШИЙ:
СРЕДНИЙ(БОЛЬШОЙ(множество{1, …, н}))
Чтобы увидеть, как это работает на практике, давайте найдем среднее значение трех лучших результатов в нашей примерной таблице. Для этого мы используем эту формулу:
=СРЕДНИЙ(БОЛЬШОЙ(B2:B10, {1,2,3}))
Чтобы сложить 3 самых высоких балла, формула выглядит следующим образом:
=СУММ(НАИБОЛЬШИЙ(B2:B10, {1,2,3}))
Примечание. Если вы используете диапазон, а не константу массива для к, вам нужно нажать Ctrl + Shift + Enter, чтобы сделать его формулой массива. В Excel 365, поддерживающем динамические массивы, все формулы можно заполнить как обычно, нажав клавишу Enter.
Как работают эти формулы:
Обычно функция НАИБОЛЬШИЙ возвращает одно значение на основе к количество. В этих формулах мы предоставляем константу массива, например {1,2,3}, для к аргумент, заставляющий его возвращать массив значений. Этот массив передается внешней функции для суммирования или усреднения.
Получить данные, связанные с n-м наибольшим значением
Чтобы получить информацию, относящуюся к наибольшим значениям, просто вложите функцию НАИБОЛЬШИЙ в каноническую формулу ПОИСКПОЗ ИНДЕКС:
ИНДЕКС(return_arrayСОВПАДЕНИЕ(БОЛЬШОЙ(lookup_array, н), lookup_array0))
Где:
- Return_array — это диапазон, из которого извлекаются совпадения.
- Lookup_array — это диапазон чисел, начиная с самого высокого ранга.
- N — позиция наибольшего значения для поиска.
Например, чтобы получить имя студента, который лучше всех сдал экзамены, введите эту формулу в F3:
=ИНДЕКС($A$2:$A$10, ПОИСКПОЗ(НАИБОЛЬШИЙ($B$2:$B$10, $D3), $B$2:$B$10, 0))
Где A2:A10 — возвращаемый массив (имена), B2:B10 — массив поиска (оценки), а D3 — позиция от наибольшей.
Чтобы узнать, у кого 2-й и 3-й наивысшие баллы, скопируйте форму на F4 и F5:
Заметки:
- Это решение хорошо работает для уникальных значений. Если ваш набор данных содержит дублирующиеся числа, может возникнуть “совпадение” в ранжировании, что приведет к неправильным результатам. Чтобы этого не произошло, используйте более сложную формулу для обработки связей.
- В Excel 365 вы можете использовать новые функции динамического массива для разработки гораздо более простого решения, которое автоматически решает проблему связей. Подробную информацию см. в разделе Как отфильтровать верхние значения N в Excel.
БОЛЬШАЯ формула для сортировки чисел по убыванию
Чтобы быстро отсортировать список чисел в Excel 365, вы можете использовать новую функцию СОРТИРОВКИ. В Excel 2019, 2016 и более ранних версиях, которые не поддерживают динамические массивы, нам приходится полагаться на старую добрую функцию НАИБОЛЬШИЙ для сортировки по убыванию и МАЛЕНЬКИЙ для сортировки по возрастанию.
В этом примере мы будем сортировать числа в формате A2:A10 от большего к меньшему. Чтобы это сделать, нам снова понадобится функция ROWS с расширяющейся ссылкой на диапазон для увеличения к аргумент на 1 с каждой строкой:
=БОЛЬШОЙ($A$2:$A$10, СТРОКИ(A$2:A2))
Приведенная выше формула переходит в самую верхнюю ячейку (C2). Затем вы перетаскиваете его через столько ячеек, сколько чисел содержится в исходном списке (в нашем случае C2:C10):
БОЛЬШАЯ формула для даты и времени
Как вы знаете, даты и время в Excel являются числовыми значениями: даты хранятся в виде целых чисел, а время — в виде десятичных дробей. Что это значит для нас? Функция НАИБОЛЬШИЙ вычисляет значения даты и времени точно так же, как и числа. Другими словами, формула, которую вы использовали для чисел, будет работать и для дат и времени!
Предполагая, что у вас есть список дат в B2:B10, следующая формула вернет самые последние даты N, в зависимости от того, сколько ячеек вы скопировали:
=НАИБОЛЬШИЙ($B$2:$B$10, СТРОКИ(B$2:B2))
Та же самая формула может также найти самые длинные 3 раза:
Получить будущую дату, ближайшую к сегодняшней или указанной дате
В этом примере показано более конкретное использование функции Excel НАИБОЛЬШИЙ с датами.
Из списка дат в B2:B10 предположим, что вы хотите вернуть дату в будущем, ближайшую к сегодняшнему дню. Чтобы выполнить задачу, мы будем использовать функции СЧЁТЕСЛИ и СЕГОДНЯ вместе, чтобы вычислить значение для к аргумент БОЛЬШОЙ:
=НАИБОЛЬШИЙ($B$2:$B$10, СЧЁТЕСЛИ($B$2:$B$10, “>”&СЕГОДНЯ()))
Чтобы найти следующую, но одну дату, формула:
=НАИБОЛЬШИЙ($B$2:$B$10, СЧЁТЕСЛИ($B$2:$B$10, “>”&СЕГОДНЯ())-1)
Чтобы найти дату, которая наступает сразу после заданной даты, введите целевую дату в какую-либо ячейку (E3 в этом примере) и соедините ссылку на эту ячейку в критериях СЧЁТЕСЛИ:
=НАИБОЛЬШИЙ($B$2:$B$10, СЧЁТЕСЛИ($B$2:$B$10, “>”&E1))
В ситуации, когда дата, соответствующая вашим критериям, не найдена, вы можете использовать функцию ЕСЛИОШИБКА в качестве «обертки», чтобы поймать ошибку и заменить ее любым текстом, который вы считаете нужным:
=ЕСЛИОШИБКА(БОЛЬШОЙ($B$2:$B$10, СЧЁТЕСЛИ($B$2:$B$10, “>”&E1)), “Не найдено”)
Как работает эта формула:
По сути, вы используете функцию СЧЁТЕСЛИ, чтобы подсчитать, сколько дат в списке больше, чем сегодняшняя или указанная дата. Этот счет является к значение, необходимое функции НАИБОЛЬШИЙ.
На момент написания сегодняшняя дата была 7 октября 2020 года. Функция СЧЁТЕСЛИ с «>» и СЕГОДНЯ() для критериев определила, что в B2:B10 есть 4 даты больше, чем 7 октября 2020 года. Это означает, что 4-я по величине дата в списке — это ближайшая дата в будущем, которую мы ищем. Итак, подставляем СЧЁТЕСЛИ во 2-й аргумент НАИБОЛЬШИЙ и получаем желаемый результат:
=БОЛЬШОЙ($B$2:$B$10, 4)
Предпоследняя дата является третьей по величине датой в нашем случае. Чтобы получить его, мы вычитаем 1 из результата COUNTIF.
Функция Excel БОЛЬШОЙ не работает
БОЛЬШАЯ формула может привести к ошибке #ЧИСЛО! ошибка по следующим причинам:
- Поставляемый множество пуст или не содержит ни одного числового значения.
- к значение является отрицательным числом.
- к значение больше, чем количество значений в множество.
Вот как можно использовать функцию НАИБОЛЬШИЙ в Excel, чтобы найти самые высокие значения в наборе данных. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Примеры формул Excel БОЛЬШИЕ (файл .xlsx)
