Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше
Предположим, вы хотите узнать, у кого самый маленький показатель погрешности в производственной цехе или самая большая заработная плата в вашем отделе. Существует несколько способов вычисления наименьшего или наибольшего числа в диапазоне.
Если ячейки находятся в соединимой строке или столбце
-
Вы можете выбрать ячейку снизу или справа от чисел, для которых нужно найти наименьшее число.
-
На вкладке Главная в группе Редактирование щелкните стрелку рядом с кнопкой
, выберите min (вычисляет наименьшее) или Max (вычисляет наибольшее) и нажмите клавишу ВВОД.
, выберите min (вычисляет наименьшее) или Max (вычисляет наибольшее) и нажмите клавишу ВВОД.Если ячейки не находятся в подрядной строке или столбце
Для этого используйте функции МИН, МАКС, МАЛЫЙ или БОЛЬШОЙ.
Пример
Скопируйте следующие данные на пустой лист.
|
|
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
НАИБОЛЬШИЙ
MAX
MIN
НАИМЕНЬШИЙ
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
Функция
НАИМЕНЬШИЙ(
)
, английский вариант SMALL(),
возвращает k-ое наименьшее значение из массива данных. Например, если
диапазон
A1:А4
содержит значения 2;10;3;7, то
формула
=
НАИМЕНЬШИЙ
(A1:А4;2)
вернет значение 3 (второе наименьшее).
Синтаксис
НАИМЕНЬШИЙ
(
массив
;
k
)
Массив
— ссылка на диапазон ячеек, содержащие данные, для которых определяется k-ое наименьшее значение. Также возможен ввод в качестве аргумента
массива констант
, например, =
НАИМЕНЬШИЙ({10:20:30:40:50};1)
.Формула вернет значение 10 (1-ое нименьшее).
k
— позиция (начиная с наименьшей) в массиве или диапазоне ячеек. Если k < 0 или k больше, чем количество значений в
массиве
, то функция
НАИМЕНЬШИЙ
()
возвращает значение ошибки #ЧИСЛО!
Что бывает, если диапазон содержит не только числа?
Пустые ячейки, логические значения (ЛОЖЬ и ИСТИНА) и текст функцией игнорируются. Это видно из таблицы в
файле примера
.
Как видно из таблицы (столбец D), значение ошибки приводит к ошибке в формуле. Если такая ситуация возможна, то создайте с помощью формулы
=
ЕСЛИОШИБКА
(A1;»ф»)
новый диапазон, которая заменит ошибку на текст, который игнорируется функцией. Конечно, нужно также изменить ссылку на диапазон в соседнем столбце.
Что бывает, если диапазон не содержит числа?
Если в массиве нет ни одного числового значения, то функция вернет значение ошибки #ЧИСЛО!, что выгодно ее отличает от функции
МИН()
, возвращающую в этом случае 0!
Если такая ситуация возможна и необходимо вычислить минимальное значение, то вместо функции
МИН()
нужно использовать формулу
=
НАИМЕНЬШИЙ
(массив;1)
Примечание
: Формула
=
НАИМЕНЬШИЙ
(массив;n)
вернет наибольшее (максимальное) значение из массива размера n. Т.е. формула
=
НАИМЕНЬШИЙ
(массив;1)
эквивалентна
=МИН(массив)
, а
=
НАИМЕНЬШИЙ
(массив;n)
эквивалентна
=МАКС(массив)
Значение числа в текстовом формате
игнорируется функцией
НАИМЕНЬШИЙ
()
(см. столбец Е на рисунке выше). Перед нахождением наименьшего значения можно попытаться преобразовать все значения в числовой формат. Это можно сделать
формулой массива
=
НАИМЕНЬШИЙ
(ЕСЛИ(ЕЧИСЛО(E5:E9+0);E5:E9+0;»»);1)
Что бывает, если в диапазоне есть повторы
Если имеется исходный массив {1;3;
3
;6;6;7}, то третьим наименьшим (по версии функции
НАИМЕНЬШИЙ
()
) будет считаться 3, а не 6. Все правильно и логично, но иногда об этом забывают. С человеческой точки зрения третьим наименьшим будет все-таки, наверное, 6 (т.е. повторы мы не учитываем).
Наименьший с учетом условия
В отличие от функции
СУММ()
и
СЧЁТ()
у
НАИМЕНЬШИЙ
()
в MS EXCEL 2010 нет аналога
СУММЕСЛИ()
и
СЧЁТЕСЛИ()
, позволяющих выполнять вычисления с учетом условия. Но, с помощью
формул массива
можно получить формулу для нахождения наименьшего с учетом условия (как это сделать см.
здесь
).
Кстати, в
Excel для Office 365 есть функция МИНЕСЛИ().
Сумма 3-х наименьших
С помощью нестандартной записи второго аргумента можно расширить возможности функции
НАИМЕНЬШИЙ
()
. Например, найдем сумму 3-х наименьших значений из диапазона
A5:A9
=
СУММ(
НАИМЕНЬШИЙ
(A5:A9;{1;2;3}))
Второй аргумент введен как
константа массива
, что позволило найти 3 наименьших значения.
Аналогично можно найти, например, среднее 2-х наименьших:
=СРЗНАЧ(
НАИМЕНЬШИЙ
(A5:A9;{1;2}))
Удивительно, но 2 последние формулы даже не обязательно вводить как
формулы массива
.
Другие применения функции
Функция
НАИМЕНЬШИЙ
()
является достаточно часто используемой, т.к. она позволяет упорядочивать числовые массивы. Ее можно, например, использовать для
сортировки списков
и
таблиц
.
Найти наибольшее или наименьшее значение в диапазоне может быть просто для большинства пользователей Excel, но как насчет поиска или возврата второго или n-го наибольшего или наименьшего значения из диапазона? В этом руководстве вы узнаете, как быстро вернуть второе по величине или наименьшее значение в Excel.
Найдите или верните второе наибольшее или наименьшее значение с помощью формул
Найдите и выберите наибольшее или наименьшее значение с помощью Kutools for Excel
Здесь я представляю формулы, которые помогут вам найти второе по величине или наименьшее значение в диапазоне.
Выберите пустую ячейку, например F1, введите эту формулу = НАИБОЛЬШИЙ (A1: D8,2), и нажмите Enter ключ, чтобы получить второе по величине значение диапазона. Смотрите скриншот:
Если вы хотите найти второе наименьшее значение, вы можете использовать эту формулу = МАЛЕНЬКИЙ (A1: D8,2), см. снимок экрана:
Наконечник: В приведенных выше формулах A1: D8 — это диапазон ячеек, из которого вы хотите найти значение, 2 — второе по величине или наименьшее значение, которое вы хотите найти, и вы можете изменить их по своему усмотрению.
Найти и вернуть наивысшее или наименьшее значение из указанного диапазона легко, но в некоторых случаях вы не только хотите знать наибольшее или наименьшее значение, но также хотите определить его положение. В этом случае вы можете подать заявку Kutools for ExcelАвтора Выберите ячейки с максимальным и минимальным значением утилита, чтобы быстро справиться с этим без формул.
После установки Kutools for Excel, пожалуйста, сделайте следующее:(Бесплатная загрузка Kutools for Excel Сейчас!)
1. Выберите диапазон ячеек, который вы хотите найти, найдите максимальное или минимальное значение и нажмите Кутулс > Выберите > Выберите ячейки с максимальным и минимальным значением. Смотрите скриншот:
2. В появившемся диалоговом окне выполните следующие действия:
1) Укажите тип ячеек, из которого вы хотите найти максимальное / минимальное значение, вы можете искать в ячейках формулы, ячейках значений или и в ячейках формулы, и в ячейках значений;
2) Укажите, чтобы выбрать максимальное значение или максимальное значение;
3) Укажите, чтобы выбрать максимальное или минимальное значение из всего выбора или каждой строки / каждого столбца в выборе;
4) Укажите, чтобы выбрать все совпадающие ячейки или только первую.
3. Нажмите Ok, и было выбрано наибольшее или наименьшее значение.
Щелкните здесь, чтобы узнать больше о выборе ячеек с максимальным и минимальным значением.
Относительные статьи:
- Как найти максимальное или минимальное значение для каждого дня в диапазоне Excel?
- Как найти максимальное или минимальное значение на основе уникальных значений в Excel?
- Как проверить или найти значение в списке в Excel?
- Как найти наиболее часто встречающееся число или текст в списке в Excel?
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (3)
Оценок пока нет. Оцените первым!
В процессе работы с большими таблицами, часто приходится сталкиваться с повторяющимися значениями сумм на разных строках. А нам нужно получить только первое наименьшее значение с одной минимальной суммой в таблице, не учитывая другие возможные дубликаты сумм.
Как получить первое минимальное значение?
Для демонстрации решения задачи создадим простую таблицу:
В этом примере данные таблицы где нужно найти значение находятся в диапазоне A6:B18. Как видно на рисунке в столбце A содержаться значения, а в столбце B соответствующие им суммы. Кроме того, видно, что среди сумм встречается несколько минимальных значений разбросанных по разным строкам.
Чтобы получить значение из столбца A которому соответствует первая наименьшая сумма в столбце B выполним 2 простых шага действий:
- В ячейке B3 введите следующую формулу:
- Подтвердите ввод формулы комбинацией горячих клавиш CTRL+SHIFT+Enter, так как она должна выполнится в массиве. Если все сделано правильно в строке формул появятся фигурные скобки {}.
В результате мы получили значение, соответствующее первой наименьшей сумме.
Детальный разбор формулы для первого наименьшего значения
Функция ИНДЕКС является самой главной в этой формуле. Ее номинальная задача – это поиск значения в указанной таблице (диапазон A6:A18 указанный в первом аргументе функции) на основе координат, указанных в ее втором (номер строки таблицы) и третьем (номер столбца) аргументах. Так как таблица, которую указано для работы функции ИНДЕКС имеет только один столбец A, то третий аргумент в параметрах – пропущен (необязателен). В тоже время во втором аргументе сразу используется несколько функций, работающих с диапазоном B6:B18.
В первом аргументе функции ЕСЛИ тестирует: является ли значение в каждой ячейке диапазона B6:B18 наименьшим числом. Таким образом в памяти создается массив логических значений ИСТИНА и ЛОЖЬ. В данном примере массив содержит только три элемента с положительным результатом теста, так как столько же содержит одинаковых минимальных значений исходная таблица. Если результат положительный формула переходит к следующему вычислительному этапу, а если отрицательный функция возвращает в массив памяти пустое текстовое значение.
Следующий вычислительный этап формулы – это определение, какие номера строк содержат эти минимальные суммы. Данный этап нам необходим для того, чтобы определить первое минимальное значение в диапазоне B6:B18. Реализовывается такая задача с помощью функции СТРОКА, которая создает в памяти программы следующий массив, состоящий из номеров строк. От этих номеров следует вычесть номер первой строки с которой начинается диапазон исходной таблицы. Это очень важно, ведь функция ИНДЕКС работает не с номерами строк листа Excel, а с номерами строк таблицы, указанной в ее первом аргументе. Таким образом, чтобы получить истинный номер исходной таблицы мы отнимаем от каждого номера строки листа, то количество строк, которое находится выше положения таблицы на листе.
Таким образом в памяти программы сформируется массив, состоящий из номеров строк. Далее функция МИН возвращает наименьший номер строки таблицы, которая содержит минимальное значение. Этот номер использует в качестве второго аргумента функция ИНДЕКС, которая поэтому же номеру строки возвращает нам значение из диапазона A6:B18.
Как получить первое наибольшее значение?
Понимая принцип действия формулы для получения первого наименьшего значения, мы теперь имеем возможность модифицировать ее под свои потребности. Например, нам нужно получить первое наибольшее значение. Для этого в ячейку D3 введите модифицированную формулу:
Не забудьте для подтверждения формулы нажать комбинацию горячих клавиш CTRL+SHIFT+Enter, чтобы на была выполнена в массиве {}.
Мы просто в нужном месте поменяли одну из функций МИН на МАКС.
Как получить второе наименьшее значение?
Для решения данной задачи в ячейку F3 введете новую модифицированную формулу:
Для подтверждения ввода формулы снова жмем CTRL+SHIFT+Enter, чтобы выполнить ее в массиве и сразу же получаем результат:
За основу взята первая формула, но в ней на этот раз мы используем новую функцию НАИМЕНЬШИЙ вместо первой функции МИН. Хитрая функция НАИМЕНШИЙ – это усовершенствованная функция МИН. Она позволяет получать первое наименьшее, второе наименьшее, третье наименьшее значение и т.д. Порядковый номер наименьших значений указывается во втором аргументе функции. В данном случаи — это число 2. То есть мы получаем из памяти массива второй наименьший номер строки для функции ИНДЕКС.
Как получить первое наибольшее число?
Чтобы получить второе наибольшее число следует в формуле указать в место функции НАИМЕНЬШИЙ функцию НАИБОЛЬШИЙ:
Принцип действия функции НАИБОЛЬШИЙ работает обратно пропорционально функции НАИМЕНЬШИЙ. Поэтому здесь мы меняем второю функцию МИН на МАКС.
Внимание! Если количество дубликатов в диапазоне меньше, чем число во втором аргументе для функций: НАИМЕНЬШИЙ или НАИМБОЛЬШИЙ, то эти функции будут возвращать ошибку «#ЧИСЛО!», по понятным причинам. Всегда заранее определяйтесь какую задачу вы ставите перед формулой!
Перейти к содержимому
Использование функций «НАИБОЛЬШИЙ» или «НАИМЕНЬШИЙ».
Вопреки мнению большинства людей в первый раз услышавших название этих функций, функции «НАИБОЛЬШИЙ» и «НАИМЕНЬШИЙ» не находят наибольшее и наименьшее значение функций. Вернее находят не только эти значения, возможности этих функций гораздо шире.
«НАИБОЛЬШИЙ» или «НАИМЕНЬШИЙ» находят заданное по счету наибольшее или наименьшее число.
Например, имеется таблица с результатами турнира по метанию молота.
В таблице отображены фамилии метателей и их результаты.
Чтобы найти победителя достаточно найти наибольший результат, но чтобы найти номинантов на второе и третье место необходимо найти не самый больший результат, а второй и третий по величине показатель. В этом нам поможет функция «НАИБОЛЬШИЙ», которая позволяет искать результаты второй и третий по величине от наибольшего.
Применение функции «НАИБОЛЬШИЙ»:
- Поставить курсор в ячейку , которой будет присвоено значение;
- Выбрать функцию «НАИБОЛЬШИЙ» в мастере функций;
- В появившейся форме указать диапазон значений и, какое по счету от наибольшего значения число следует искать;
- Нажать «ENTER» или «ОК».
Аналогичным образом можно применять и функцию «НАИМЕНЬШИЙ», которая в отличии от функции «НАИБОЛЬШИЙ» находит наименьшие значения.
