Содержание
Скорее всего вам известны простые функции МИН (MIN) и МАКС (MAX) , позволяющие быстро найти минимальное или максимальное значение в таблице. Но что если нам требуется найти, например, не самое большое, а 2-е или 5-е значение в ТОПе? Здесь помогут функции НАИБОЛЬШИЙ (LARGE) и НАИМЕНЬШИЙ (SMALL) .
Синтаксис этих функций похож:
=НАИБОЛЬШИЙ( Диапазон ; Позиция )
=НАИМЕНЬШИЙ( Диапазон ; Позиция )
- Диапазон – диапазон ячеек с числами, которые мы проверяем.
- Позиция – целое число, представляющее собой позицию (ранг, номер в рейтинге) извлекаемого элемента.
Например, если у нас есть таблица с прибылями по товарам, то с помощью функции НАИБОЛЬШИЙ можно легко определить максимальное (первое в рейтинге) значение из диапазона прибыли (B2:B22), которое фактически будет тождественно формуле МАКС(B2:B22):
Аналогичным образом, функция
… выдаст следующее за ним максимальное (2-е в рейтинге) и т.д.
Массив констант и сумма ТОПов
Что интересно, аргумент Позиция может быть не просто числом, а набором чисел — массивом констант в фигурных скобках. Так, например, для получения суммы первых трех максимальных значений в диапазоне можно использовать формулу с прописанным внутри массивом констант для первых трех позиций (сочетание Ctrl+Shift+Enter в конце можно не нажимать, хотя по факту это и формула массива):
Сортировка формулой
Функцию НАИМЕНЬШИЙ (SMALL) очень удобно использовать для сортировки формулой набора числовых значений. Для этого достаточно сделать вспомогательную нумерацию (1, 2, 3. ) и ссылаться на нее во втором аргументе:
Если вместо НАИМЕНЬШИЙ использовать функцию НАИБОЛЬШИЙ, то сортировка, естественно, будет уже по убыванию.
Только числа
Еще одной полезной особенностью этих функций является то, что они игнорируют все, кроме чисел, т.е. "не видят" текст и логические значения (ИСТИНА, ЛОЖЬ). Это бывает полезно использовать, например, для извлечения всех сумм по заданному наименованию, когда товар встречается больше одного раза и ВПР (VLOOKUP) уже не поможет:
В этом примере функция ЕСЛИ (IF) в столбце С проверяет наименование на соответствие заданному (Огурцы) и выводит сумму или логическую ЛОЖЬ. А для извлечения потом всех полученных сумм из столбца С используется наша функция НАИМЕНЬШИЙ, которая игнорирует ЛОЖЬ — и мы получаем список стоимостей всех сделок по нужному товару.
Массив — ссылка на диапазон ячеек, содержащие данные, для которых определяется k-ое наибольшее значение. Также возможен ввод массива констант, например, = НАИБОЛЬШИЙ(<10:20:30:40:50>;1)
k — позиция (начиная с наибольшей) в массиве или диапазоне ячеек. Если k ? 0 или k больше, чем количество значений в массиве, то функция НАИБОЛЬШИЙ() возвращает значение ошибки #ЧИСЛО!
Если n — количество значений в массиве, то формула =НАИБОЛЬШИЙ(массив;1) вернет наибольшее (максимальное) значение, а =НАИБОЛЬШИЙ(массив;n) — наименьшее (минимальное). Т.е. формула =НАИБОЛЬШИЙ(массив;1) эквивалентна =МАКС(массив) , а =НАИБОЛЬШИЙ(массив;n) эквивалентна =МИН(массив)
Пустые ячейки, логические значения (ЛОЖЬ и ИСТИНА) и текст функцией игнорируются. Это видно из таблицы в файле примера .
Значение ошибки в ячейке приводит к ошибке в формуле. Прежде чем применять функцию НАИБОЛЬШИЙ () — обработайте ошибку, например с помощью функции ЕСЛИОШИБКА().
Если в массиве нет ни одного числового значения, то функция вернет значение ошибки #ЧИСЛО!, что выгодно ее отличает от функции МАКС() , возвращающую в этом случае 0!
Значение числа в текстовом формате игнорируется функцией НАИБОЛЬШИЙ () (см. столбец Е на рисунке выше). Перед нахождением наибольшего значения можно попытаться преобразовать все значения в числовой формат. Это можно сделать формулой массива = НАИБОЛЬШИЙ(ЕСЛИ(ЕЧИСЛО(E5:E9+0);E5:E9+0;"");1)
Необходимо помнить особенность функции НАИБОЛЬШИЙ() при работе со списками чисел, среди которых имеются повторы. Например, если имеется исходный массив <1;2;3;6;6;7>, то третьим наибольшим (по версии функции НАИБОЛЬШИЙ() ) будет считаться 6, а не 3. Все правильно и логично, но иногда об этом забывают. С человеческой точки зрения третьим наибольшим будет все-таки, наверное, 3 (т.е. повторы не учитываются).
Наибольший с учетом условия
В отличие от функции СУММ() и СЧЁТ() у НАИБОЛЬШИЙ () нет аналога СУММЕСЛИ() и СЧЁТЕСЛИ() , позволяющих выполнять вычисления с учетом условия. Но, с помощью формул массива можно получить формулу для нахождения наибольшего с учетом условия (см. здесь).
Сумма 3-х наибольших
С помощью нестандартной записи второго аргумента можно расширить возможности функции НАИБОЛЬШИЙ() . Например, найдем сумму 3-х наибольших значений из диапазона A5:A9 = СУММ(НАИБОЛЬШИЙ(A5:A9;<1;2;3>))
Второй аргумент введен как константа массива, что позволило найти 3 наибольших значения.
Аналогично можно найти, например, среднее 2-х наибольших: =СРЗНАЧ(НАИБОЛЬШИЙ(A5:A9;<1;2>))
Удивительно, но 2 последние формулы даже не обязательно вводить как формулы массива.
Другие применения функции
Функция НАИБОЛЬШИЙ() является достаточно часто используемой, т.к. она позволяет упорядочивать числовые массивы. Ее можно, например, использовать для сортировки списков и таблиц.
Очень часто нам необходимо найти в выборке чисел или дат наибольшие или наименьшие числа. Для этих целей есть специальные функции НАИБОЛЬШИЙ и НАИМЕНЬШИЙ. Их синтаксис следующий:
Где Массив – это диапазон данных/чисел из которых необходимо выбрать наибольшее или наименьшее число, а k – это позиция, начиная с которого необходимо считать. То есть, если поставить цифру 1, то будут находиться самые крайние значения (самое максимальное или минимальное), если 2 – то второе по величине и так далее.
Давайте, для наглядности, разберем какой-нибудь простенький пример.
Допустим у нас таблица со счетами и датами их оплаты. Даты не отсортированы по возрастанию. Все даты уникальные, то есть, нет одинаковых. Нам необходимо постоянно искать ближайшие две даты, для того, чтобы оплатить их. Можно находить даты вручную, можно отсортировывать постоянно, а можно прописать простую формулу с использованием функции НАИМЕНЬШИЙ
Находим ближайшую дату:
Находим следующую дату:
Надеемся, что вам понятно почему мы используем в этой формуле цифру 2.
Но после ввода формулы вы увидите, что результат будет указан не в виде даты, а в виде цифр, например, в нашем случае ближайшая дата будет 13.09.2013, но вместо не будет указано число 41530, это и есть наша дата, только в числовом формате. Чтобы отобразить в формате даты необходим выделить эти ячейки, нажать правую кнопку мыши и выбрать «Формат ячеек…», выбрать «Формат даты» и необходимый вид как показано на рисунке.
Все, наша формула готова. Теперь ближайшие даты будут автоматически обновляться.
Надеемся, что статья была вам полезна и вы отблагодарите нас, нажав «Мне нравиться» чуть ниже на этой странице. Спасибо