Функция НАИБОЛЬШИЙ в Excel – это не просто инструмент для поиска максимального значения. Это ключ к решению задач оптимизации, анализа данных и даже прогнозирования.
В этой статье вы узнаете:
- Как использовать НАИБОЛЬШИЙ для поиска нескольких максимумов
- Секреты применения функции с массивами и формулами
- 7 необычных примеров использования НАИБОЛЬШИЙ, которые удивят вас
- И многое другое!
Анализируя набор чисел, часто имеет смысл найти самые большие из них. Получить максимальное значение очень просто с помощью функции МАКС. Когда дело доходит до поиска конкретного наибольшего значения, скажем, нам нужно второе или третье по величине число в наборе данных, функция НАИБОЛЬШИЙ оказывается весьма полезной.
- Функция Excel НАИБОЛЬШИЙ
- 3 вещи, которые нужно знать о функции НАИБОЛЬШИЙ
- Как работает формула
- Как получить первые N значений в Excel
- Сумма или среднее первых N значений
- Имя N-го по величине значения
- Сортировка чисел по убыванию
- Самые последние дата и время
- Ранжирование по месяцам
- Найти ближайшую к указанной дату
- Почему не работает?
Функция Excel НАИБОЛЬШИЙ возвращает числовое значение в зависимости от его позиции в списке при сортировке по значению в порядке убывания. Другими словами, она может получить «n-е по величине» значение — 1-е по величине значение, 2-е, 3-е значение и т. д.
Функция Excel НАИБОЛЬШИЙ
Функция НАИБОЛЬШИЙ (LARGE по-английски) используется чтобы выбрать из набора числовых данных n-енаибольшее значение в Excel. Например, он может определить наивысший балл, второй по величине заказ, третье место по весу и т. д.
Формула предусматривает использование двух аргументов, оба из которых являются обязательными:
НАИБОЛЬШИЙ(массив; k)
Где:
- Массив — диапазон или массив, в котором нужно искать наибольшее значение.
- K — позиция от самого высокого до возвращаемого, т.е. k-е по величине значение в наборе данных.
НАИБОЛЬШИЙ относится к категории статистических функций. Она доступна во всех версиях Excel для Office 365, Excel 2021, Excel 2019, Excel 2016 и более ранних версиях.
3 вещи, которые нужно знать о функции НАИБОЛЬШИЙ
Прежде чем мы перейдем к более практическим вещам и начнем создавать собственные формулы, обратите внимание на 3 простых факта, которые важно учитывать:
- Функция НАИБОЛЬШИЙ обрабатывает только числовые значения. Пустые ячейки, текст и логические значения игнорируются.
- Если массив содержит какие-либо ошибки, возвращается ошибка.
- Если массив содержит n значений, выражение НАИБОЛЬШИЙ(массив; 1) вернет наибольшее, а НАИБОЛЬШИЙ(массив;n) – наименьшее значение.
Как работает формула НАИБОЛЬШИЙ
Формула для наибольшего значения в Excel очень проста. В качестве первого аргумента вы указываете диапазон числовых значений. Во втором аргументе вы определяете, какое по счету значение вы хотите получить, начиная с самого большого.
Предположим, что в приведенной ниже таблице вы хотите узнать второй по величине балл. Это можно сделать так:
=НАИБОЛЬШИЙ(B2:B10; 2)
А теперь давайте рассмотрим более конкретные случаи использования и посмотрим, чем еще может быть полезна функция НАИБОЛЬШИЙ.
Как получить первые N значений в Excel
Вычисление наибольшего значения в Excel требует выполнения следующих действий:
- Введите интересующие позиции в отдельные ячейки. Эти числа будут использоваться в качестве значений k .
- Создайте формулу НАИБОЛЬШИЙ, применяя абсолютную ссылку на диапазон для массива (в нашем случае $B$2:$B$10) и относительную адресацию для ячейки со значением k (D3).
- Введите формулу в самую верхнюю ячейку, а затем перетащите ее в ячейки ниже. Готово!
В качестве примера мы собираемся найти 3 лучших результата в таблице ниже. Для этого записываем числа 1, 2 и 3 в D3, D4 и D5 соответственно и вводим следующую формулу в E3:
=НАИБОЛЬШИЙ($B$2:$B$10; D3)
Если хочется обойтись без вспомогательного столбца с числами, можно сделать так:
=НАИБОЛЬШИЙ($B$2:$B$10; ЧСТРОК(B$2:B2))
или
=НАИБОЛЬШИЙ($B$2:$B$10; СТРОКА(A1))
То есть, при помощи функций ЧСТРОК или СТРОКА мы организуем своего рода счетчик, значение которого будет изменяться от 1 и выше с шагом 1 (то есть, 1,2,3 ….).
Скопируйте вниз по столбцу, и вы получите такой результат:
Сумма или среднее первых N значений
Чтобы суммировать первые N значений в наборе данных, вы можете использовать НАИБОЛЬШИЙ вместе с СУММПРОИЗВ или СУММ следующим образом:
СУММПРОИЗВ(НАИБОЛЬШИЙ( массив ; {1; …; n }))
Или
СУММ(НАИБОЛЬШИЙ( массив ; {1; …; n }))
Чтобы усреднить самые высокие значения n , объедините функции СРЗНАЧ и НАИБОЛЬШИЙ:
СРЗНАЧ(НАИБОЛЬШИЙ( массив ; {1; …; n }))
Чтобы увидеть, как это работает на практике, давайте найдем среднее значение трех лучших результатов в нашей таблице. Для этого мы используем эту формулу:
=СРЗНАЧ(НАИБОЛЬШИЙ(B2:B10;{1;2;3}))
Чтобы сложить 3 наивысших балла, подход следующий:
=СУММ(НАИБОЛЬШИЙ(B2:B10;{1;2;3}))
Примечание. Если вы используете диапазон, а не константу массива для k, вам нужно вводить формулу при помощи Ctrl + Shift + Enter, чтобы сделать ее формулой массива. В Excel 365 и Excel 2021, поддерживающих динамические массивы, все формулы можно вводить как обычно, нажав Enter.
Как работают эти формулы:
Обычно НАИБОЛЬШИЙ возвращает одно значение на основе числа k. Здесь же мы используем массив констант, в частности {1;2;3}, для аргумента k, заставляя формулу возвращать массив значений. В данном случае – первое, второе и третье по величине значения. Они затем передаются внешней функции для суммирования или усреднения.
Имя N-го по величине значения
Чтобы получить информацию, относящуюся к самым большим значениям, просто вставьте функцию НАИБОЛЬШИЙ в формулу ИНДЕКС ПОИСКПОЗ:
ИНДЕКС( возвращаемый_массив ; ПОИСКПОЗ(БОЛЬШОЙ( массив_значений ; n ); массив_значений ; 0))
Где:
- возвращаемый_массив — это диапазон, из которого можно извлечь информацию.
- массив_значений — это диапазон чисел.
- 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 выведет первое совпадение дважды, как показано скриншоте ниже.
Чтобы решить проблему вывода наибольших значений с дубликатами, нам нужна более сложная функция ПОИСКПОЗ:
=ИНДЕКС($A$2:$A$10; ПОИСКПОЗ(1; ($B$2:$B$10=НАИБОЛЬШИЙ($B$2:$B$10; D3)) * (СЧЁТЕСЛИ(F$2:F2; $A$2:$A$10)=0); 0))
Во всех версиях, кроме Excel 365 и Excel 2021, это работает только как формула массива. Поэтому, пожалуйста, не забудьте нажать Ctrl + Shift + Enter, чтобы правильно ввести ее.
Примечание. Это решение подразумевает, что возвращаемый столбец (Студент в нашем случае) содержит только уникальные значения.
Сортировка чисел по убыванию
Чтобы быстро отсортировать список чисел в Excel 365, вы можете использовать новую функцию СОРТ. В Excel 2019, 2016 и более ранних версиях, которые не поддерживают динамические массивы, нам приходится полагаться на старую добрую функцию НАИБОЛЬШИЙ для сортировки по убыванию и НАИМЕНЬШИЙ для сортировки по возрастанию.
В этом примере мы будем сортировать числа в формате A2:A10 от большего к меньшему. Чтобы это сделать, нам снова понадобится функция ЧСТРОК или СТРОКА с изменяющейся ссылкой, чтобы организовать счётчик и увеличивать аргумент k на 1 с каждой новой строкой:
=НАИБОЛЬШИЙ($A$2:$A$10; ЧСТРОК(A$2:A2))
или
=НАИБОЛЬШИЙ($A$2:$A$10; СТРОКА(A1))
Приведенную выше формулу относится записываем в С2. А затем копируем вниз по столбцу, сколько чисел в исходном списке (в нашем случае до 10-й строки):
Самые последние дата и время
Как вы знаете, даты и время в Excel представляют собой числовые значения: даты хранятся как целые числа, а время — как десятичные. Что это значит для нас? Формула НАИБОЛЬШИЙ вычисляет значения даты и времени точно так же, как и числа. Другими словами, формула, которую вы использовали для чисел, будет работать и для дат и времени!
Предполагая, что у вас есть список дат в B2:B10, это вернет самые последние N дат, в зависимости от того, во сколько ячеек вы его скопируете:
=НАИБОЛЬШИЙ($B$2:$B$10; ЧСТРОК(B$2:B2))
или
=НАИБОЛЬШИЙ($B$2:$B$10; СТРОКА(A1))
По той же формуле можно найти и 3 самых продолжительных времени:
Ранжирование по месяцам
Для ясности этот пример разделен на две части: (1) определение трех самых больших сумм за каждый месяц и (2) получение имени клиента для каждой из трех наибольших ежемесячных сумм.
Часть 1. Получите 3 максимальные суммы каждый месяц.
Чтобы получить 3 максимальные суммы за каждый месяц, запишем в F2:
=НАИБОЛЬШИЙ(ЕСЛИ(ТЕКСТ($B$2:$B$15;"mmmm")=F$1;$C$2:$C$15);$E2)
Примечание. Это формула массива , и ее необходимо вводить с помощью клавиш Ctrl+Shift+Enter, за исключением Excel 365.
Мы сначала используем функцию ТЕКСТ, чтобы получить названия месяцев для каждой даты.
Пользовательский формат даты «mmmm» вернет строку типа «Январь», «Февраль», «Март» для каждого имени в указанном диапазоне дат. Результатом является массив названий месяцев:
{"январь":"январь":"январь":"январь":"январь":"февраль":"февраль":"февраль":"февраль":"февраль":"март":"март":"март":"март"}
Функция ТЕКСТ передает этот массив в функцию ЕСЛИ, которая сравнивает названия месяца со значением в F1.
Только суммы за январь успешно проходят сравнение и возвращают значение из столбца С, все остальные возвращают ЛОЖЬ:
{433:400:555:566:342:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}
Наконец, функция НАИБОЛЬШИЙ использует этот массив значений для возврата «n-го» наибольшего значения. В ячейке F2 функция НАИБОЛЬШИЙ возвращает 566, первое наибольшее значение. Поскольку формула копируется вниз и по всей таблице, функция НАИБОЛЬШИЙ возвращает три самые большие суммы за каждый из трех месяцев.
Теперь, когда мы знаем три самых больших значения за каждый месяц, мы можем использовать эту информацию как «ключ» для получения имени клиента для каждого из них.
Часть 2: Получение имен клиентов
Примечание. Это пример использования ИНДЕКС и ПОИСКПОЗ с несколькими критериями. Если эта концепция для вас нова, вот базовый пример.
Чтобы получить имя, связанное с тремя наибольшими значениями в F2:F4, мы используем ИНДЕКС и ПОИСКПОЗ:
=ИНДЕКС($A$2:$A$15; ПОИСКПОЗ(1;($C$2:$C$15=F2) * (ТЕКСТ($B$2:$B$15;"mmmm")=F$7);0))
Примечание. Это формула массива , и ее необходимо вводить с помощью клавиш Ctrl+Shift+Enter, за исключением Excel 365.
Выражение, создающее искомый_массив, использует логическую логику для «отфильтровки» сумм, которые (1) не относятся к январю и (2) не соответствуют значению в F2 (566). Результатом является массив из единиц и нулей, например:
{0:0:0:1:0:0:0:0:0:0:0:0:0:0}
Если искомое_значение равно 1, ПОИСКПОЗ возвращает 4 в функцию ИНДЕКС:
=ИНДЕКС($A$2:$A$15; 4) // возвращает "Мария"
По мере копирования формулы по всей таблице она возвращает трех клиентов с наибольшими суммами за каждый из трех месяцев.
Найти ближайшую к указанной дату
В этом примере показано еще одно возможное использование функции Excel НАИБОЛЬШИЙ с датами.
Предположим, из списка дат в B2:B10 вы хотите вернуть дату, ближайшую к заданной. Чтобы выполнить эту задачу, мы будем использовать функцию СЧЕТЕСЛИ, чтобы вычислить значение аргумента k функции НАИБОЛЬШИЙ:
=ЕСЛИОШИБКА(НАИБОЛЬШИЙ($B$2:$B$10; СЧЁТЕСЛИ($B$2:$B$10; ">"&E1)); "Не найдено")
Чтобы найти следующую дату, используйте формулу:
=ЕСЛИОШИБКА(НАИБОЛЬШИЙ($B$2:$B$10; СЧЁТЕСЛИ($B$2:$B$10; ">"&E1)-1); "Не найдено")
В ситуации, когда дата, соответствующая вашим критериям, не найдена, мы использовать функцию ЕСЛИОШИБКА , чтобы в случае ошибки и заменить ее любым текстом, который вы считаете нужным.
Почему не работает?
Функция НАИБОЛЬШИЙ может выдать ошибку #ЗНАЧ! по следующим причинам:
- Предоставленный массив пуст или не содержит ни одного числового значения.
- Значение k является отрицательным числом.
- Значение k больше, чем количество значений в массиве.
Вот как можно использовать функцию НАИБОЛЬШИЙ в Excel, чтобы найти самые большие значения в наборе данных.
Благодарю вас за чтение.
