Функция НАИМЕНЬШИЙ() в EXCEL
Массив — ссылка на диапазон ячеек, содержащие данные, для которых определяется k-ое наименьшее значение. Также возможен ввод в качестве аргумента массива констант , например, = НАИМЕНЬШИЙ(<10:20:30:40:50>;1) .Формула вернет значение 10 (1-ое нименьшее).
k — позиция (начиная с наименьшей) в массиве или диапазоне ячеек. Если 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
Формула, позволяющая найти наименьшее число является базовой возможностью, выполняющей поиск ячейки с максимальным значением без ручного перебора и сравнения каждой ячейки.
Видеоинструкция
Формула найти наименьшее в Excel
Выполнить данную задачу можно при помощи формулы:
- Диапазон – это диапазон данных в котором будет производиться поиск и сравнение;
- Аргумент – указывает на позицию в рейтинге. Например, 1 показывает самое маленькое число, а 3 покажет цифру, занимающую третье место.
Способ №2
Следует указать нужную ячейку и кликнуть на знак функция (аналогично можно выполнить поиск наибольшего).
В появившемся окне выбираем в категории «Полный алфавитный перечень» и выбираем «НАИМЕНЬШИЙ».
Далее, как и в первом случае, выбираем массив и аргумент.
Готово, нажав на «Ок» данные появятся в ячейке.
Найти сумму двух наименьших чисел в Эксель
Решить задачу можно при помощи простой формулы:
В этом случае аргумент указан через <> и разделитель ; (точка с запятой).
Поиск минимального или максимального значения по условию
В Microsoft Excel давно есть в стандартном наборе функции СЧЁТЕСЛИ (COUNTIF) , СУММЕСЛИ (SUMIF) и СРЗНАЧЕСЛИ (AVERAGEIF) и их аналоги, позволяющие искать количество, сумму и среднее в таблице по одному или нескольким условиям. Но что если нужно найти не сумму или среднее, а минимум или максимум по условию(ям)?
Предположим, нам нужно найти минимальную цену для каждого товара в базе данных по поставщикам:
Таким образом, условием будет наименование товара (бумага, карандаши, ручки), а диапазоном для выборки — столбец с ценами.
Для будущего удобства, конвертируем исходный диапазон с ценами в «умную таблицу». Для этого выделите его и выберите на вкладке Главная — Форматировать как таблицу (Home — Format as Table) или нажмите Ctrl+T. Наша «поумневшая» таблица автоматически получит имя Таблица1, а к столбцам можно будет, соответственно, обращаться по их именам, используя выражения типа Таблица1[Товар] или Таблица1[Цена]. При желании, стандартное имя Таблица1 можно подкорректировать на вкладке Конструктор (Design) , которая появляется, если щелкнуть в любую ячейку нашей «умной» таблицы. Подробнее о таких таблицах и их скрытых возможностях можно почитать здесь.
Способ 1. Функции МИНЕСЛИ и МАКСЕСЛИ в Excel 2016
Начиная с версии Excel 2016 в наборе функции Microsoft Excel наконец появились функции, которые легко решают нашу задачу — это функции МИНЕСЛИ (MINIFS) и МАКСЕСЛИ (MAXIFS) . Синтаксис этих функции очень похож на СУММЕСЛИМН (SUMIFS) :
=МИНЕСЛИ( Диапазон_чисел ; Диапазон_проверки1 ; Условие1 ; Диапазон_проверки2 ; Условие2 . )
- Диапазон_чисел — диапазон с числами, из которых выбирается минимальное или максимальное
- Диапазон_проверки — диапазон, который проверяется на выполнение условия
- Условие — критерий отбора
Просто, красиво, изящно. Одна проблема — функции МИНЕСЛИ и МАКСЕСЛИ появились только начиная с 2016 версии Excel. Если у вас (или тех, кто будет потом работать с вашим файлом) более старые версии, то придется шаманить другими способами.
Способ 2. Формула массива
В английской версии это будет, соответственно =MIN(IF(Table1[Товар]=F4;Table1[Цена]))
Не забудьте после ввода этой формулы в первую зеленую ячейку G4 нажать не Enter , а Ctrl + Shift + Enter , чтобы ввести ее как формулу массива. Затем формулу можно скопировать на остальные товары в ячейки G5:G6.
Давайте разберем логику работы этой формулы поподробнее. Функция ЕСЛИ проверяет каждую ячейку массива из столбца Товар на предмет равенства текущему товару (Бумага). Если это так, то выдается соответствующее ему значение из столбца Цена. В противном случае – логическое значение ЛОЖЬ (FALSE) .
Таким образом внешняя функция МИН (MIN) выбирает минимальное не из всех значений цен, а только из тех, где товар был Бумага, т.к. ЛОЖЬ функцией МИН игнорируется. При желании, можно выделить мышью всю функцию ЕСЛИ(…) в строке формул
. и нажать на клавиатуре F9, чтобы наглядно увидеть тот самый результирующий массив, из которого потом функция МИН и выбирает минимальное значение:
Способ 3. Функция баз данных ДМИН
Этот вариант использует малоизвестную (и многими, к сожалению, недооцененную) функцию ДМИН (DMIN) из категории Работа с базой данных (Database) и требует небольшого изменения результирующей таблицы:
Как видите, зеленые ячейки с результатами транспонированы из столбца в строку и над ними добавлена мини-таблица (F4:H5) с условиями. Логика работы этой функции следующая:
- База_данных — вся наша таблица вместе с заголовками.
- Поле — название столбца из шапки таблицы, из которого выбирается минимальное значение.
- Критерий — таблица с условиями отбора, состоящая (минимально) из двух ячеек: названия столбца, по которому идет проверка (Товар) и критерия (Бумага, Карандаши, Ручки).
Это обычная формула (не формула массива), т.е. можно вводить и использовать ее привычным образом. Кроме того, в той же категории можно найти функции БДСУММ (DSUM) , ДМАКС (DMAX) , БСЧЁТ (DCOUNT) , которые используются совершенно аналогично, но умеют находить не только минимум, но и сумму, максимум и количество значений по условию.
Способ 4. Сводная таблица
Если в исходной таблице очень много строк, но данные меняются не часто, то удобнее будет использовать сводную таблицу, т.к. формула массива и функция ДМИН могут сильно тормозить Excel.
Установите активную ячейку в любое место нашей умной таблицы и выберите на вкладке Вставка — Сводная таблица (Insert — Pivot Table) . В появившемся окне нажмите ОК:
В конструкторе сводной таблицы перетащите поле Товар в область строк, а Цену в область значений. Чтобы заставить сводную вычислять не сумму (или количество), а минимум щелкните правой кнопкой мыши по любому числу и выберите в контекстном меню команду Итоги по — Минимум:
Как найти минимальное и максимальное значение в excel?
Если при работе с Excel возникла задача найти минимальное и (или) максимальное значение в определённом числовом диапазоне, то её решить можно несколькими способами.
1) Самый простой вариант — вам не требуется использовать полученные значения в формулах и дальнейших вычислениях.
В этом случае достаточно:
- щёлкнуть правой кнопкой мыши на любой ячейке диапазона.
- в контекстном меню выбрать: "Сортировка от минимального к максимальному" или "Сортировка от максимального к минимальному".
В результате этих действий числовой диапазон будет упорядочен и можно легко увидеть минимум / максимум.
2) Если же максимальное / минимальное значение необходимо внести в определённую ячейку таблицы Эксель, то необходимо воспользоваться специальными функциями.
В Excel есть 2 функции:
Рассмотрим их работу на примере.
В таблице представлены доходы компании за 5 лет. Необходимо найти самый низкий и самый высокий доход за этот период.
- ставим курсор мыши в ячейку, куда нужно внести результат.
- далее можно либо написать формулу вручную, либо воспользоваться мастером функций.
- в обоих случаях необходимо выделить нужный вам диапазон из чисел. В рассматриваемом примере это будет диапазон C3:C8.
- формула будет иметь следующий вид: МИН(C3:C8) ИЛИ МАКС(C3:C8).
Вот что получится в итоге:
3) Рассмотрим ещё одну ситуацию — требуется найти минимум / максимум только среди тех ячеек диапазона, которые удовлетворяют определённому условию.
В этом случае нужно использовать совместно функции ЕСЛИ и МИН / МАКС.
Важная деталь — такую формулу нужно вводить не через Enter, а с помощью комбинации клавиш Ctrl + Shift + Enter.
Если перед вами стоит задача найти в таблице с данными максимальное или минимальное значение, то это можно легко сделать используя обычные функции этой программы.
В качестве примера, возьмем вот эту таблицу с данными:
Сначала будем искать минимальное значение, для этого в ячейки с адресом А1 наберем следующую формулу =МИН(B2:B10), где:
МИН() — название функции, которая возвращает минимальное значение,
B2:B10 — это диапазон, в котором мы ищем минимальное значение.
Теперь найдем максимальное значение, в ячейки с адресом А2 вводим формулу =МАКС(B2:B10), где
МАКС() — название функции, которая возвращает максимальное значение,
B2:B10 — массив данных, в котором ведем поиск.
Как мы видим это очень простая операция, а название функций фактически совпадает с тем, что мы ищем, так что вам будет легко все это запомнить.
Для нахождения минимального и максимального значение в каком-либо диапазоне ячеек с цифрами в excel есть две функции МИН и МАКС или MIN и MAX. Можно, просто, в ячейке, в которой мы хотим увидеть определённое значение, набрать "=МАКС(A1:A10)", где A1:A10 — это диапазон ячеек, он может быть любым, какой укажете.
Так же можно вызвать диалог Мастера функций, выбрать нужную функцию и указать диапазон ячеек, в которых будет производиться поиск минимального или максимального значения.
Вы можете использовать функции MIN и MAX, чтобы найти минимальное и максимальное значения в диапазоне ячеек. Вот как вы можете использовать эти функции:
Чтобы найти минимальное значение в диапазоне ячеек, вы можете использовать функцию МИН. Например, чтобы найти минимальное значение в диапазоне ячеек от A1 до A10, введите в пустую ячейку следующую формулу: =MIN(A1:A10)
Чтобы найти максимальное значение в диапазоне ячеек, вы можете использовать функцию МАКС. Например, чтобы найти максимальное значение в диапазоне ячеек от B1 до B10, введите в пустую ячейку следующую формулу: =MAX(B1:B10)
Вы также можете использовать эти функции, чтобы найти минимальное и максимальное значения в диапазоне ячеек, которые соответствуют определенным критериям, используя функцию «ЕСЛИ».
Чтобы найти максимальное значение в диапазоне ячеек, соответствующих определенным критериям, вы можете использовать функции МАКС и ЕСЛИ вместе. Например, чтобы найти максимальное значение в диапазоне ячеек от A1 до A10, где значение больше 5, можно использовать следующую формулу: =МАКС(ЕСЛИ(A1:A10>5; A1:A10))
Точно так же, чтобы найти минимальное значение в диапазоне ячеек, соответствующих определенным критериям, вы можете использовать функции МИН и ЕСЛИ вместе. Например, чтобы найти минимальное значение в диапазоне ячеек от B1 до B10, где значение меньше 10, можно использовать следующую формулу: =МИН(ЕСЛИ(B1:B10<10; B1:B10))