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

Как получить первое или второе наименьшее значение в таблице Excel

В процессе работы с большими таблицами, часто приходится сталкиваться с повторяющимися значениями сумм на разных строках. А нам нужно получить только первое наименьшее значение с одной минимальной суммой в таблице, не учитывая другие возможные дубликаты сумм.

Как получить первое минимальное значение?

Для демонстрации решения задачи создадим простую таблицу:

Список транзакций.

В этом примере данные таблицы где нужно найти значение находятся в диапазоне 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. То есть мы получаем из памяти массива второй наименьший номер строки для функции ИНДЕКС.

Как получить первое наибольшее число?

Чтобы получить второе наибольшее число следует в формуле указать в место функции НАИМЕНЬШИЙ функцию НАИБОЛЬШИЙ:

Принцип действия функции НАИБОЛЬШИЙ работает обратно пропорционально функции НАИМЕНЬШИЙ. Поэтому здесь мы меняем второю функцию МИН на МАКС.

Внимание! Если количество дубликатов в диапазоне меньше, чем число во втором аргументе для функций: НАИМЕНЬШИЙ или НАИМБОЛЬШИЙ, то эти функции будут возвращать ошибку «#ЧИСЛО!», по понятным причинам. Всегда заранее определяйтесь какую задачу вы ставите перед формулой!

Максимальный и Минимальный по условию в EXCEL

Нахождение максимального/ минимального значения — простая задача, но она несколько усложняется, если МАКС/ МИН нужно найти не среди всех значений диапазона, а только среди тех, которые удовлетворяют определенному условию.

Пусть имеется таблица с двумя столбцами: текстовым и числовым.

Для удобства понимания формул создадим два именованных диапазона для каждого из столбцов: Текст ( A 6: A 30 ) и Числа ( B6:B30 ). (см. файл примера ).

Рассмотрим несколько задач:

А. Найдем максимальное значение среди тех чисел, которые соответствуют значению Текст1 (критерий введем в ячейку E6 ).Т.е. будем искать максимальное значение не среди всех значений столбца Числовые значения , а только среди тех, у которых в той же строке в столбце А текстовое значение равно Текст1 . Напишем формулу массива (не забудьте при вводе формулы нажать CTRL+SHIFT+ENTER ): =НАИБОЛЬШИЙ(ЕСЛИ(A6:A30=E6;B6:B30;»»);1)

или с Именованными диапазонами :

Часть формулы Текст=E6 , вернет массив <ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ: ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>(для просмотра результата выделите эту часть формулы в Строке формул и нажмите клавишу F9 ). ИСТИНА соответствует строкам, у которых в столбце Текстовые значения содержится значение Текст1 .

Часть формулы ЕСЛИ(Текст=E6;Числа;»») , вернет массив <10:"":"":"":-66:"":"":"": -37:"":"":"":-5:"": "":"":4:"":"":"":8:"":"":"":"">, где вместо ИСТИНА подставлено значение из числового столбца, а вместо ЛОЖЬ — значение Пустой текст . Вместо «» можно было бы использовать любой текстовый символ (букву) или вообще опустить (в этом случае массив будет выглядеть так <10:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:-66: ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:-37:ЛОЖЬ: ЛОЖЬ: ЛОЖЬ:-5:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:4: ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:8: ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>).

Функция НАИБОЛЬШИЙ() со вторым параметром =1 использована вместо функции МАКС() , т.к. в случае, если критерию не удовлетворяет ни одна строка, то формула = МАКС(<"":"":"":"":"":"":"": "":"":"":"": "":"":"":"":"":"":"": "":"":"":"":"":"":"">) вернет 0!, что может ввести в заблуждение. Функция НАИБОЛЬШИЙ() в этом случае вернет ошибку #ЧИСЛО!

Б. Найдем максимальное значение только среди чисел принадлежащих определенному интервалу значений, например от 5 до 50. Границы можно ввести в ячейки I 14 и J14 . Решением является формула массива := НАИБОЛЬШИЙ(ЕСЛИ((Числа>=I14)*(Числа<=J14);Числа);1)

В. Найдем с помощью формулы массива минимальное значение среди тех, которые соответствуют значению Текст3 := МИН(ЕСЛИ((Текст=E7);Числа;»»);1)

Т.е. если в столбце А значение = Текст3 , то учитывается значение в столбце B , если значение <> Текст3 , то учитывается максимальное значение+1, т.е. заведомо НЕ минимальное. Далее функция МИН() возвращает минимальное значение из полученного массива, причем понятно, что ни одно из значений, где <> Текст3, не исказит результат (см. задачу А).

Другое решение с помощью формулы ДМИН() , которая не является формулой массива . =ДМИН(A5:B30;B5;I8:I9)

где в диапазоне I8:I9 содержится табличка с критерием (см. файл примера ). Подробнее о функции ДМИН() см. статью Функция ДМИН() — нахождение минимального значения по условию в MS EXCEL .

Г. Найдем минимальное значение, среди тех, которые больше среднего : =ДМИН(A5:B30;B5;I10:I11) где в диапазоне I10:I11 содержится критерий =B6>СРЗНАЧ(Числа)

Д. Найдем максимальное значение по модулю. Из рисунка выше видно, что это -99. Для этого используйте формулу массива :

Е. Найдем минимальное положительное число:

= НАИМЕНЬШИЙ(Числа;СЧЁТЕСЛИ(Числа;»<=0″)+1) — обычная формула!

= НАИМЕНЬШИЙ(ЕСЛИ(Числа>0;Числа);1) — формула массива .

СОВЕТ:

Все вышеуказанные задачи можно решить без использования формул массива и функции ДМИН() . Для этого потребуется создать дополнительный столбец, в котором будут выведены только те значения, которые удовлетворяют критериям. Затем, среди отобранных значений с помощью функций МАКС() или МИН() , определить соответственно максимальное или минимальное значение (см. файл примера Лист без формул массива).

Множественные условия

Вышерассмотренный подход можно распространить на случаи когда необходимо найти максимальный или минимальный для нескольких текстовых условий.

В этом случае придется записать более сложную формулу массива :

В файле примера для наглядности настроено Условное форматирование для выделения строк, удовлетворяющим критериям . Кроме того, для выбора критериев использован Выпадающий список (см. желтые ячейки).

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