Функции НАИБОЛЬШИЙ и НАИМЕНЬШИЙ Excel

Случаются ситуации, когда нам необходимо определить минимальное или максимальное значение диапазона данных. Такие ситуации не создают особых проблем, в этом случае мы пользуемся функциями МИН и МАКС, которые возвращают минимальное и максимальное значение. Они не требуют особых навыков владения Excel, требуется лишь задать один аргумент – диапазон данных – и результат не заставит ждать. Гораздо интереснее ситуации складывается, когда требуется определить второй, третий, … k-ый наименьший/наибольший  элемент в диапазоне. В этом случае нам помогут Excel функции НАИБОЛЬШИЙ или НАИМЕНЬШИЙ. В сегодняшней статье мы рассмотрим, как найти k-ый минимальный или максимальный элемент (в статье будет рассмотрена функция НАИМЕНЬШИЙ, все сказанное, будет также относиться и к функции НАИБОЛЬШИЙ).

Описание функции НАИМЕНЬШИЙ

Функция НАИМЕНЬШИЙ возвращает k-ый наименьший элемент в диапазоне данных или массиве, содержащих числовые значения. К примеру, вы можете использовать функцию НАИМЕНЬШИЙ, чтобы найти третье наименьшее значение диапазона. Синтаксис функции выглядит следующим образом:

Синтаксис функции наименьший Excel

Массив – массив или диапазон числовых данных, для которых определяется k-ое наименьшее значение.

K – позиция искомого наименьшего значения (начиная с наименьшей) в массиве или диапазоне.

Обратите внимание, что функция НАИМЕНЬШИЙ вернет ошибку #ЧИСЛО!, если массив данных будет пустым или k будет больше, чем количество элементов в массиве. Если в диапазоне данных будут находиться текстовые значения, Excel будет игнорировать эти данные.

Пример использования функции НАИМЕНЬШИЙ

Рассмотрим на примере, как функция возвращает 1-й, 2-й, 3-й, 4-й и 5-й наименьший элемент из диапазона данных A1:A5.

Пример функции наименьший Excel

Здесь мы по порядку указали наименьшие значения от самого маленького до самого большого. Другими словами, мы сделали сортировку от наименьшего к наибольшему. Подобную методику мы применяли при создании дашборда в статье Добавление возможности сортировки по KPI для дашборда.

Обратите внимание, что функция со значением параметра k=1 дает тот же результат, что и функция МИН. И наоборот, если мы укажем значение k равным количеству элементов в массиве, функция будет работать аналогично функции МАКС.

Сумма первых трех наибольших значений

Функции НАИБОЛЬШИЙ и НАИМЕНЬШИЙ часто используются в формулах массивов, так как для параметра k, можно задать несколько элементов поиска. То есть, если нам необходимо определить сумму первых трех наибольших значений диапазона данных, первое, что нам придет в голову, это записать формулу таким образом:

=НАИБОЛЬШИЙ(A1:A5;1)+НАИБОЛЬШИЙ(A1:A5;2)+НАИБОЛЬШИЙ(A1:A5;3)

Однако, можно переписать функцию НАИБОЛЬШИЙ, используя функцию массива:

=СУММ(НАИБОЛЬШИЙ(A1:A5;{1;2;3}))

Здесь мы указали Excel, что нам необходимо вернуть первые три наибольших значения {1;2;3}, из диапазона A1:A5 и просуммировать их.

Фигурные скобки {} обычно используются в формулах массивов, тем не менее, в данном случае вам не нужно использовать сочетания клавиш Shift+Enter, чтобы указать Excel, что это формула массива. А просто вводить их так же, как и любые символы в формулах.


5 комментариев

  1. Добрый день! Проблема с использованием данной функции. Сортирует от большего к меньшему массив данных, но не учитывает отрицательные значения (после нуля; сортирую для дашборда). Что делаю неверно? Заранее большое спасибо!

    • Ирина, добрый день. Для данной функции нет ограничений по знакам, массив с отрицательными и положительными значениями он отсортирует от наименьшего к наибольшему. Возможно, вы используете текстовый формат, без примера, сложно что-либо говорить

  2. Добрый день!
    Не улавливаю как формулу построить. Нужно в таблице по условию (М или Ж) вывести рейтинг с ФИО и результатом (документ прилагаю, нужно заполнить желтую область).
    Как то вывела сортировку по значению, но как к нему подставить ФИО? И догадываюсь, что сортировку значений можно сделать гораздо проще.
    Буду благодарна за помощь.

Добавить комментарий для Irina Отменить ответ

Ваш e-mail не будет опубликован. Обязательные поля помечены *