Функция НАИБОЛЬШИЙ() в EXCEL
k — позиция (начиная с наибольшей) в массиве или диапазоне ячеек. Если k ? 0 или k больше, чем количество значений в массиве , то функция НАИБОЛЬШИЙ() возвращает значение ошибки #ЧИСЛО!
Если n — количество значений в массиве , то формула =НАИБОЛЬШИЙ(массив;1) вернет наибольшее (максимальное) значение, а =НАИБОЛЬШИЙ(массив;n) — наименьшее (минимальное). Т.е. формула =НАИБОЛЬШИЙ(массив;1) эквивалентна =МАКС(массив) , а =НАИБОЛЬШИЙ(массив;n) эквивалентна =МИН(массив)
Пустые ячейки, логические значения (ЛОЖЬ и ИСТИНА) и текст функцией игнорируются. Это видно из таблицы в файле примера .
Значение ошибки в ячейке приводит к ошибке в формуле. Прежде чем применять функцию НАИБОЛЬШИЙ () — обработайте ошибку, например с помощью функции ЕСЛИОШИБКА() .
Если в массиве нет ни одного числового значения, то функция вернет значение ошибки #ЧИСЛО!, что выгодно ее отличает от функции МАКС() , возвращающую в этом случае 0!
Значение числа в текстовом формате игнорируется функцией НАИБОЛЬШИЙ () (см. столбец Е на рисунке выше). Перед нахождением наибольшего значения можно попытаться преобразовать все значения в числовой формат. Это можно сделать формулой массива = НАИБОЛЬШИЙ(ЕСЛИ(ЕЧИСЛО(E5:E9+0);E5:E9+0;»»);1)
Необходимо помнить особенность функции НАИБОЛЬШИЙ() при работе со списками чисел, среди которых имеются повторы. Например, если имеется исходный массив 6 ;6;7>, то третьим наибольшим (по версии функции НАИБОЛЬШИЙ() ) будет считаться 6, а не 3. Все правильно и логично, но иногда об этом забывают. С человеческой точки зрения третьим наибольшим будет все-таки, наверное, 3 (т.е. повторы не учитываются).
Наибольший с учетом условия
В отличие от функции СУММ() и СЧЁТ() у НАИБОЛЬШИЙ () нет аналога СУММЕСЛИ() и СЧЁТЕСЛИ() , позволяющих выполнять вычисления с учетом условия. Но, с помощью формул массива можно получить формулу для нахождения наибольшего с учетом условия (см. здесь ).
Сумма 3-х наибольших
С помощью нестандартной записи второго аргумента можно расширить возможности функции НАИБОЛЬШИЙ() . Например, найдем сумму 3-х наибольших значений из диапазона A5:A9 = СУММ(НАИБОЛЬШИЙ(A5:A9;))
Второй аргумент введен как константа массива , что позволило найти 3 наибольших значения.
Аналогично можно найти, например, среднее 2-х наибольших: =СРЗНАЧ(НАИБОЛЬШИЙ(A5:A9;))
Удивительно, но 2 последние формулы даже не обязательно вводить как формулы массива .
Другие применения функции
Функция НАИБОЛЬШИЙ() является достаточно часто используемой, т.к. она позволяет упорядочивать числовые массивы. Ее можно, например, использовать для сортировки списков и таблиц .
Поиск наименьшего или наибольшего числа в диапазоне
Предположим, вы хотите узнать, у кого самый маленький показатель погрешности в производственной цехе или самая большая заработная плата в вашем отделе. Существует несколько способов вычисления наименьшего или наибольшего числа в диапазоне.
Если ячейки находятся в соединимой строке или столбце
- Вы можете выбрать ячейку снизу или справа от чисел, для которых нужно найти наименьшее число.
- На вкладке Главная в группе Редактирование щелкните стрелку рядом с кнопкой , выберите min (вычисляет наименьшее) или Max (вычисляет наибольшее) и нажмите клавишу ВВОД.
Если ячейки не находятся в подрядной строке или столбце
Для этого используйте функции МИН, МАКС, МАЛЫЙ или БОЛЬШОЙ.
Пример
Скопируйте следующие данные на пустой лист.
Анализ топовых значений функциями НАИБОЛЬШИЙ и НАИМЕНЬШИЙ
Например, если у нас есть таблица с прибылями по товарам, то с помощью функции НАИБОЛЬШИЙ можно легко определить максимальное (первое в рейтинге) значение из диапазона прибыли (B2:B22), которое фактически будет тождественно формуле МАКС(B2:B22):
Аналогичным образом, функция
… выдаст следующее за ним максимальное (2-е в рейтинге) и т.д.
Массив констант и сумма ТОПов
Что интересно, аргумент Позиция может быть не просто числом, а набором чисел — массивом констант в фигурных скобках. Так, например, для получения суммы первых трех максимальных значений в диапазоне можно использовать формулу с прописанным внутри массивом констант для первых трех позиций (сочетание Ctrl+Shift+Enter в конце можно не нажимать, хотя по факту это и формула массива):
Сортировка формулой
Функцию НАИМЕНЬШИЙ (SMALL) очень удобно использовать для сортировки формулой набора числовых значений. Для этого достаточно сделать вспомогательную нумерацию (1, 2, 3. ) и ссылаться на нее во втором аргументе:
Если вместо НАИМЕНЬШИЙ использовать функцию НАИБОЛЬШИЙ, то сортировка, естественно, будет уже по убыванию.
Только числа
Еще одной полезной особенностью этих функций является то, что они игнорируют все, кроме чисел, т.е. «не видят» текст и логические значения (ИСТИНА, ЛОЖЬ). Это бывает полезно использовать, например, для извлечения всех сумм по заданному наименованию, когда товар встречается больше одного раза и ВПР (VLOOKUP) уже не поможет:
В этом примере функция ЕСЛИ (IF) в столбце С проверяет наименование на соответствие заданному (Огурцы) и выводит сумму или логическую ЛОЖЬ. А для извлечения потом всех полученных сумм из столбца С используется наша функция НАИМЕНЬШИЙ, которая игнорирует ЛОЖЬ — и мы получаем список стоимостей всех сделок по нужному товару.
Ссылки по теме
- Как извлекать не первое, а все сразу значения (Мульти ВПР)
- Как сортировать списки (числовые, текстовые) с помощью формул массива
- Поиск минимального и максимального значения по условию
Excel: как найти максимальное значение по группе
Часто вам может понадобиться найти максимальное значение некоторого набора данных в Excel на основе категории или группы.
Например, предположим, что у нас есть следующий набор данных, и мы хотели бы найти максимальное значение «очков» для каждой команды:
В следующем пошаговом примере показано, как это сделать.
Шаг 1: введите данные
Сначала введите значения данных в Excel:
Шаг 2: Найдите уникальные группы
Затем нам нужно использовать функцию =UNIQUE() для создания списка уникальных имен команд.
В нашем примере мы введем следующую формулу в ячейку D2 :
=UNIQUE( A2:A16 ) Это создаст список уникальных команд:
Шаг 3: Найдите максимальное значение по группе
Далее мы будем использовать следующую формулу, чтобы найти максимальное количество очков, набранных каждой командой:
=MAX(IF( A:A = D2 , B:B )) Мы введем эту формулу в ячейку E2 , а затем скопируем и вставим ее в оставшиеся ячейки в столбце E:
Это говорит нам:
- Максимальное количество очков, набранных игроками на Mavs, равно 26 .
- Максимальное количество очков, набранных игроками на «Уорриорз», равно 19 .
- Максимальное количество очков, набранных игроками «Лейкерс», равно 33 .
- Максимальное количество очков, набранных игроками в заезде, равно 19 .
- Максимальное количество очков, набранных игроками «Селтикс», составляет 29 .
Примечание.Чтобы рассчитать минимальное количество очков, набранных каждой командой, просто замените MAX в формуле на MIN .
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel: