Как найти второе минимальное значение excel
Перейти к содержимому

Как найти второе минимальное значение excel

  • автор:

Поиск наименьшего или наибольшего числа в диапазоне

Используйте autosum и Max или Min, чтобы вычислить наибольшее или наименьшее значение в диапазоне. Например, вы можете использовать его, чтобы узнать, кто имеет наименьший уровень ошибок в производственном запуске на заводе или наибольшую зарплату в отделе.

Если ячейки находятся в непрерывной строке или столбце

Значок кнопки

  1. Выберите ячейку ниже или справа от чисел, для которых нужно найти наименьшее число.
  2. На вкладке Главная щелкните стрелку рядом с элементом

Если ячейки не находятся в непрерывной строке или столбце

Для выполнения этой задачи используйте функции MIN, MAX, LARGE или LARGE, а не

Значок кнопки

Пример

Скопируйте следующий столбец Данные на пустой лист в столбец A.

МИНЕСЛИ (функция МИНЕСЛИ)

Функция МИНЕСЛИ возвращает минимальное значение из заданных определенными условиями или критериями ячеек.

Примечание: Эта функция доступна на Windows или Mac, если у вас Office 2019 или если у вас есть подписка на Microsoft 365. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена последняя версия Office.

Синтаксис

Фактический диапазон ячеек, для которого определяется минимальное значение.

Набор ячеек, оцениваемых с помощью условия.

условия1
(обязательно)

Условие в виде числа, выражения или текста, определяющее ячейки, которые имеют минимальное значение. Такой же набор условий используется для функций МАКСЕСЛИ, СУММЕСЛИМН и СРЗНАЧЕСЛИМН.

диапазон_условия2,
условие2, …(необязательный аргумент)

Дополнительные диапазоны и условия для них. Можно ввести до 126 пар диапазонов и условий.

Примечания

  • Размер и форма аргументов мин_диапазон и диапазон_условияN должны быть одинаковыми. В противном случае эти функции вернут ошибку #ЗНАЧ!.

Примеры

Скопируйте данные примеров из приведенных ниже таблиц и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Как найти второе минимальное значение excel

Найти наибольшее или наименьшее значение в диапазоне может быть просто для большинства пользователей Excel, но как насчет поиска или возврата второго или n-го наибольшего или наименьшего значения из диапазона? В этом руководстве вы узнаете, как быстро вернуть второе по величине или наименьшее значение в Excel.

Найдите или верните второе наибольшее или наименьшее значение с помощью формул

Здесь я представляю формулы, которые помогут вам найти второе по величине или наименьшее значение в диапазоне.

Выберите пустую ячейку, например F1, введите эту формулу = НАИБОЛЬШИЙ (A1: D8,2) , и нажмите Enter ключ, чтобы получить второе по величине значение диапазона. Смотрите скриншот:

документ возвращает второе наивысшее значение 1стрелка документадокумент возвращает второе наивысшее значение 2

документ возвращает второе наивысшее значение 3

Если вы хотите найти второе наименьшее значение, вы можете использовать эту формулу = МАЛЕНЬКИЙ (A1: D8,2) , см. снимок экрана:

Наконечник: В приведенных выше формулах A1: D8 — это диапазон ячеек, из которого вы хотите найти значение, 2 — второе по величине или наименьшее значение, которое вы хотите найти, и вы можете изменить их по своему усмотрению.

Найдите и выберите наибольшее или наименьшее значение с помощью Kutools for Excel

Найти и вернуть наивысшее или наименьшее значение из указанного диапазона легко, но в некоторых случаях вы не только хотите знать наибольшее или наименьшее значение, но также хотите определить его положение. В этом случае вы можете подать заявку Kutools for ExcelАвтора Выберите ячейки с максимальным и минимальным значением утилита, чтобы быстро справиться с этим без формул.

После установки Kutools for Excel, сделайте следующее: (Бесплатная загрузка Kutools for Excel прямо сейчас!)

документ возвращает второе наивысшее значение 4

1. Выберите диапазон ячеек, который вы хотите найти, найдите максимальное или минимальное значение и нажмите Кутулс > Выберите > Выберите ячейки с максимальным и минимальным значением. Смотрите скриншот:

2. В появившемся диалоговом окне выполните следующие действия:

1) Укажите тип ячеек, из которого вы хотите найти максимальное / минимальное значение, вы можете искать в ячейках формулы, ячейках значений или и в ячейках формулы, и в ячейках значений;

2) Укажите, чтобы выбрать максимальное значение или максимальное значение;

3) Укажите, чтобы выбрать максимальное или минимальное значение из всего выбора или каждой строки / каждого столбца в выборе;

документ возвращает второе наивысшее значение 5

4) Укажите, чтобы выбрать все совпадающие ячейки или только первую.

документ возвращает второе наивысшее значение 6

3. Нажмите Ok, и было выбрано наибольшее или наименьшее значение.

Поиск минимального или максимального значения по условию

min-if1.png

В Microsoft Excel давно есть в стандартном наборе функции СЧЁТЕСЛИ (COUNTIF) , СУММЕСЛИ (SUMIF) и СРЗНАЧЕСЛИ (AVERAGEIF) и их аналоги, позволяющие искать количество, сумму и среднее в таблице по одному или нескольким условиям. Но что если нужно найти не сумму или среднее, а минимум или максимум по условию(ям)?
Предположим, нам нужно найти минимальную цену для каждого товара в базе данных по поставщикам:
Таким образом, условием будет наименование товара (бумага, карандаши, ручки), а диапазоном для выборки — столбец с ценами. Для будущего удобства, конвертируем исходный диапазон с ценами в «умную таблицу». Для этого выделите его и выберите на вкладке Главная — Форматировать как таблицу (Home — Format as Table) или нажмите Ctrl+T. Наша «поумневшая» таблица автоматически получит имя Таблица1, а к столбцам можно будет, соответственно, обращаться по их именам, используя выражения типа Таблица1[Товар] или Таблица1[Цена]. При желании, стандартное имя Таблица1 можно подкорректировать на вкладке Конструктор (Design) , которая появляется, если щелкнуть в любую ячейку нашей «умной» таблицы. Подробнее о таких таблицах и их скрытых возможностях можно почитать здесь.

Способ 1. Функции МИНЕСЛИ и МАКСЕСЛИ в Excel 2016

  • Диапазон_чисел — диапазон с числами, из которых выбирается минимальное или максимальное
  • Диапазон_проверки — диапазон, который проверяется на выполнение условия
  • Условие — критерий отбора

Поиск минимального по условию функцией МИНЕСЛИ

Просто, красиво, изящно. Одна проблема — функции МИНЕСЛИ и МАКСЕСЛИ появились только начиная с 2016 версии Excel. Если у вас (или тех, кто будет потом работать с вашим файлом) более старые версии, то придется шаманить другими способами.

Способ 2. Формула массива

min-if2.png

В английской версии это будет, соответственно =MIN(IF(Table1[Товар]=F4;Table1[Цена]))

Не забудьте после ввода этой формулы в первую зеленую ячейку G4 нажать не Enter , а Ctrl + Shift + Enter , чтобы ввести ее как формулу массива. Затем формулу можно скопировать на остальные товары в ячейки G5:G6.

Давайте разберем логику работы этой формулы поподробнее. Функция ЕСЛИ проверяет каждую ячейку массива из столбца Товар на предмет равенства текущему товару (Бумага). Если это так, то выдается соответствующее ему значение из столбца Цена. В противном случае – логическое значение ЛОЖЬ (FALSE) .

Таким образом внешняя функция МИН (MIN) выбирает минимальное не из всех значений цен, а только из тех, где товар был Бумага, т.к. ЛОЖЬ функцией МИН игнорируется. При желании, можно выделить мышью всю функцию ЕСЛИ(…) в строке формул

min-if3.png

. и нажать на клавиатуре F9, чтобы наглядно увидеть тот самый результирующий массив, из которого потом функция МИН и выбирает минимальное значение:

min-if4.png

Способ 3. Функция баз данных ДМИН

Этот вариант использует малоизвестную (и многими, к сожалению, недооцененную) функцию ДМИН (DMIN) из категории Работа с базой данных (Database) и требует небольшого изменения результирующей таблицы:

min-if5.png

Как видите, зеленые ячейки с результатами транспонированы из столбца в строку и над ними добавлена мини-таблица (F4:H5) с условиями. Логика работы этой функции следующая:

min-if6.png

  • База_данных — вся наша таблица вместе с заголовками.
  • Поле — название столбца из шапки таблицы, из которого выбирается минимальное значение.
  • Критерий — таблица с условиями отбора, состоящая (минимально) из двух ячеек: названия столбца, по которому идет проверка (Товар) и критерия (Бумага, Карандаши, Ручки).

Это обычная формула (не формула массива), т.е. можно вводить и использовать ее привычным образом. Кроме того, в той же категории можно найти функции БДСУММ (DSUM) , ДМАКС (DMAX) , БСЧЁТ (DCOUNT) , которые используются совершенно аналогично, но умеют находить не только минимум, но и сумму, максимум и количество значений по условию.

Способ 4. Сводная таблица

Если в исходной таблице очень много строк, но данные меняются не часто, то удобнее будет использовать сводную таблицу, т.к. формула массива и функция ДМИН могут сильно тормозить Excel.

Установите активную ячейку в любое место нашей умной таблицы и выберите на вкладке Вставка — Сводная таблица (Insert — Pivot Table) . В появившемся окне нажмите ОК:

min-if7.png

В конструкторе сводной таблицы перетащите поле Товар в область строк, а Цену в область значений. Чтобы заставить сводную вычислять не сумму (или количество), а минимум щелкните правой кнопкой мыши по любому числу и выберите в контекстном меню команду Итоги по — Минимум:

min-if8.png

Вытаскивать данные из сводной в дальнейшие расчеты теперь можно с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA) , которую мы подробно разбирали ранее:

min-if9.png

Ссылки по теме

  • Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
  • Выборочное вычисление суммы, среднего и т.д. по одному или нескольким критериям
  • Что такое «умные таблицы» в Excel и как с ними работать

Добавить комментарий

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