Excel 2-ое наибольшее значение

Отыскать наибольшее значение в Excel не составляет труда. Но по мере необходимости находить максимум по условию – проблематично. Для этих целей в надстройку добавлена новенькая функция =МАКСЕСЛИ (подобна обычной функции Excel СУММЕСЛИ).

В Excel версии 2016 и выше возникла интегрированная функция МАКСЕСЛИ, сможете воспользоваться ей. Если ваш Excel наиболее ранешней версии, то эту функцию можно употреблять установив надстройку VBA-Excel.

У функции последующие аргументы =МАКСЕСЛИ(ДИАПАЗОН;КРИТЕРИЙ;[ ДИАПАЗОН_ПОИСКА ])

ДИАПАЗОН – Спектр проверяемых ячеек.

Пример 1

В качестве аспекта можно указывать значения и логические выражения:

1. Разглядим последующий пример в котором определяется наибольшая оценка по литературе. Для этого в параметр КРИТЕРИЙ обозначено значение "Литература", а в параметр ДИАПАЗОН – перечень предметов.
2. Если в качестве аспекта указать логическое выражение "<>Российский", то обусловится наибольшая оценка по всем предметам кроме российского языка.

Пример 2

В последующем примере параметр ДИАПАЗОН_ПОИСКА не задан, потому наибольшее значение определяется посреди ячеек обозначенных в параметре ДИАПАЗОН .

Примечание: Мы стараемся как можно оперативнее обеспечивать вас животрепещущими справочными материалами на вашем языке. Эта страничка переведена автоматом, потому ее текст может содержать некорректности и грамматические ошибки. Для нас принципиально, чтоб эта статья была для вас полезна. Просим вас уделить пару секунд и сказать, посодействовала ли она для вас, при помощи клавиш понизу странички. Для удобства также приводим ссылку на оригинал (на британском языке).

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

Ячейки, которые находятся в смежных строчках либо столбцах

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

На вкладке Основная в группе Редактирование щелкните стрелку рядом с клавишей Автосумма а, изберите пункт мин (вычисление меньшей) либо Max (подсчитывает наибольшее значение), а потом нажмите кнопку ВВОД.

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

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

Пример

Скопируйте обозначенные ниже данные на пустой лист.

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

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

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

В этом примере данные таблицы где необходимо отыскать значение находятся в спектре A6:B18. Как видно на рисунке в столбце A содержаться значения, а в столбце B надлежащие им суммы. Не считая того, видно, что посреди сумм встречается несколько малых значений разбросанных по различным строчкам.

Чтоб получить значение из столбца A которому соответствует 1-ая меньшая сумма в столбце B выполним 2 обычных шага действий:

1. В ячейке B3 введите последующую формулу:
2. Подтвердите ввод формулы композицией жарких кнопок CTRL+SHIFT+Enter, потому что она обязана выполнится в массиве. Если все изготовлено верно в строке формул покажутся фигурные скобки <>.

В итоге мы получили значение, соответственное первой меньшей сумме.

Детализированный разбор формулы для первого меньшего значения

Функция ИНДЕКС является самой главной в данной формуле. Ее номинальная задачка – это поиск значения в обозначенной таблице (спектр A6:A18 обозначенный в первом аргументе функции) на базе координат, обозначенных в ее втором (номер строчки таблицы) и 3-ем (номер столбца) аргументах. Потому что таблица, которую обозначено для работы функции ИНДЕКС имеет лишь один столбец A, то 3-ий аргумент в параметрах – пропущен (необязателен). В тоже время во 2-м аргументе сходу употребляется несколько функций, работающих с спектром B6:B18.

В первом аргументе функции ЕСЛИ тестирует: является ли значение в каждой ячейке спектра B6:B18 минимальным числом. Таковым образом в памяти создается массив логических значений ИСТИНА и ЛОЖЬ. В данном примере массив содержит лишь три элемента с положительным результатом теста, потому что столько же содержит схожих малых значений начальная таблица. Если итог положительный формула перебегает к последующему вычислительному шагу, а если отрицательный функция возвращает в массив памяти пустое текстовое значение.

Последующий вычислительный шаг формулы – это определение, какие номера строк содержат эти малые суммы. Данный шаг нам нужен для того, чтоб найти 1-ое малое значение в спектре B6:B18. Реализовывается таковая задачка при помощи функции СТРОКА, которая делает в памяти программки последующий массив, состоящий из номеров строк. От этих номеров следует отнять номер первой строчки с которой начинается спектр начальной таблицы. Это весьма принципиально, ведь функция ИНДЕКС работает не с номерами строк листа Excel, а с номерами строк таблицы, обозначенной в ее первом аргументе. Таковым образом, чтоб получить настоящий номер начальной таблицы мы отнимаем от всякого номера строчки листа, то количество строк, которое находится выше положения таблицы на листе.

Таковым образом в памяти программки сформируется массив, состоящий из номеров строк. Дальше функция МИН возвращает меньший номер строчки таблицы, которая содержит малое значение. Этот номер употребляет в качестве второго аргумента функция ИНДЕКС, которая потому же номеру строчки возвращает нам значение из спектра A6:B18.

Как получить 1-ое наибольшее значение?

Понимая принцип деяния формулы для получения первого меньшего значения, мы сейчас имеем возможность видоизменять ее под свои потребности. К примеру, нам необходимо получить 1-ое наибольшее значение. Для этого в ячейку D3 введите измененную формулу:

Не забудьте для доказательства формулы надавить комбинацию жарких кнопок CTRL+SHIFT+Enter, чтоб на была выполнена в массиве <>.

Мы просто в подходящем месте поменяли одну из функций МИН на МАКС.

Как получить 2-ое меньшее значение?

Для решения данной задачки в ячейку F3 введете новейшую измененную формулу:

Для доказательства ввода формулы опять нажимаем CTRL+SHIFT+Enter, чтоб выполнить ее в массиве и сходу же получаем итог:

За базу взята 1-ая формула, но в ней сейчас мы используем новейшую функцию НАИМЕНЬШИЙ заместо первой функции МИН. Хитрецкая функция НАИМЕНШИЙ – это улучшенная функция МИН. Она дозволяет получать 1-ое меньшее, 2-ое меньшее, третье меньшее значение и т.д. Порядковый номер меньших значений указывается во 2-м аргументе функции. В данном случаи — это число 2. Другими словами мы получаем из памяти массива 2-ой меньший номер строчки для функции ИНДЕКС.

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

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

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

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