Часто задаваемые вопросы

 

    ЧАСТО ЗАДАВАЕМЫЕ ВОПРОСЫ

https://faq-ru.ru             

 

Как в экселе выбрать минимальное число


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

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

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

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

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

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

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

Пример

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

A

Данные

10

7

9

отображал

0

4

Формула

Описание (результат)

= MIN (A2: A7)

Наименьшее число в диапазоне (0)

= MAX (A2: A7)

Наибольшее число в диапазоне (27)

= МАЛЫЙ (A2: A7; 2)

Второе наименьшее число в диапазоне (4)

= КРУПНЫЙ (A2: A7; 3)

Третье наибольшее число в диапазоне (9)

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

См. также

большой

максимум

минимум

малый

support.office.com

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

В 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). В появившемся окне нажмите ОК:


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


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


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


www.planetaexcel.ru

Как выделить наименьшее и наибольшее значение в Excel цветом

В процессе подготовки разного рода ранжирования или анализа показателей успешности, очень удобно если выделить цветом наилучший или наихудший результат. Как выделить максимальное значение в Excel цветом? Здесь существенно поможет условное форматирование. Преимуществом такого подхода является сохранение работоспособности автоматического выделения цветом лучших/худших показателей даже при добавлении или удалении данных из таблицы.

Как выделить цветом максимальное значение в Excel

Для примера возьмем таблицу расходов:

Чтобы молниеносно выделить наибольшие и наименьшие расходы делаем следующее:

  1. Выделите диапазон с расходами B2:B10 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
  2. Выберите: «Использовать формулу для определения форматируемых ячеек».
  3. В поле ввода введите формулу: =МАКС($B$2:$B$10)=B2
  4. Нажмите на кнопку «Формат», чтобы выделить максимальное значение в столбце Excel. Для этого в появившемся окне «Формат ячеек» на вкладке «Заливка» выберите желаемый цвет заливки для ячеек. Например, зеленый. И нажмите ОК на всех открытых окнах.

В результате мы выделили цветом ячейку с максимальным числовым значением.

Как выбрать минимальное значение в Excel

Чтобы выделить цветом минимальное значение в Excel, повторите весь порядок действий, описанных выше. Только вместо функции МАКС, должна быть функция МИН. Данная функция позволяет выбрать наименьшее значение в таблице Excel. А вместо зеленого цвета заливки выберите красный цвет.

К одному и тому же диапазону должно быть применено 2 правила условного форматирования. Чтобы проверить выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»

Финальный эффект применения двух правил условного форматирования для выделения цветом наибольшего и наименьшего значения столбца B (Расходы):

Принцип действий обоих правил одинаковый. Они отличаются только функциями =МАКС() и =МИН(). Более того аргументы функций идентичны. Первая функция =МАКС() ищет в несменяемом диапазоне с абсолютными ссылками $B$2:$B$10 максимальное значение. После функции стоит оператор сравнения с изменяемой текущей ячейкой которая имеет относительную ссылку начиная от B2. Правило условного форматирования читает данную формулу так: если число больше всех, то выделить цветом. Как только совпадает максимальное число (которое возвращает функция МАКС) со значением текущей ячейки, формула возвращает логическое значение ИСТИНА и сразу же применяется формат со соответствующим цветом заливки. Так же срабатывает правило для умного форматирования ячеек столбца и с функцией МИН.



Выделение цветом трех наименьших значений подряд

В разных ситуациях полезно применяется автоматическое выделение цветом трех ячеек с наименьшими значениями. Условное форматирование со соответственными формулами прекрасно справляется с данной задачей. Таблица с данными для примера:

Усложним немного задачу. Ячейка с наименьшим значением должна автоматически получить красный цвет заливки. Следующая ячейка с вторым наименьшим значением получит цвет заливки оранжевый. А третье наименьшее значение – желтый.

Чтобы добиться данного эффекта следует выполнить следующие действия:

  1. Выделите диапазон ячеек B2:B9 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек».
  2. В поле ввода введите формулу: =НАИМЕНЬШИЙ($B$2:$B$9;1)=B2 и нажмите на кнопку «Формат», чтобы задать красный цвет заливки для ячейки. И нажмите ОК на всех открытых окнах.
  3. Не снимая выделения с диапазона B2:B9 сразу же создаем второе правило условного форматирования. Снова выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек».
  4. В поле ввода введите формулу: =НАИМЕНЬШИЙ($B$2:$B$9;2)=B2 и нажмите на кнопку «Формат», чтобы задать оранжевый цвет заливки для ячейки. И нажмите ОК на всех окнах.
  5. Создайте третье правило. А в поле ввода введите формулу: =НАИМЕНЬШИЙ($B$2:$B$9;3)=B2 и нажмите на кнопку «Формат», чтобы задать желтый цвет заливки для ячейки. ОК на всех окнах.
  6. К одному и тому же диапазону должно быть применено 3 правила условного форматирования. Чтобы проверить выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами».

Три наименьшие значения автоматически выделились разными цветами.

Внимание! Если просматриваемый диапазон содержит несколько одинаковых минимальных значений все они будут выделены цветом.

Аналогичным способом можно выделять цветом наибольшие значения. Просто изменить в формуле функцию НАИМЕНЬШИЙ на НАИБОЛЬШИЙ. Например: =НАИБОЛЬШИЙ($B$2:$B$9;3)=B2

Функция наименьший и наибольший в Excel

Функция =НАИМЕНЬШИЙ() ищет в заданном (в первом аргументе) диапазоне очередное наименьшее значение. Очередь указана во втором аргументе функции. То есть если во втором аргументе мы указали число 3, значит функция возвращает третье по очереди наименьшее значение заданного диапазона $B$2:$B$9. Если результат вычисления функции равен текущей ячейке значит к ней присваивается соответственный формат (цвет заливки). Важно отметить что в формуле для просмотра диапазона мы используем абсолютные адреса ссылки, а после оператора сравнения =B2 – относительные, так как значение функции сравнивается с каждой ячейкой выделенного диапазона. А функция НАИБОЛЬШИЙ работает обратно пропорционально.

Полезный совет! Если нет необходимости разбивать 3 наименьших значения на разные цвета, тогда необязательно создавать 3 правила условного форматирования для одного и того же диапазона. Достаточно просто немного изменить формулу добавив всего один символ оператора: =НАИМЕНЬШИЙ($B$2:$B$9;3)>=B2. То есть – больше или равно.

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

exceltable.com

Эксель максимальное значение из диапазона Excelka.ru

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

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

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

Как найти минимальное значение в эксель?

Как найти максимальное значение в эксель?

Минимальное и максимальное значение в Excel

Если при работе с Excel возникла задача найти минимальное и (или) максимальное значение в определённом числовом диапазоне, то её решить можно несколькими способами.

1) Самый простой вариант — вам не требуется использовать полученные значения в формулах и дальнейших вычислениях.

В этом случае достаточно:

  • щёлкнуть правой кнопкой мыши на любой ячейке диапазона.
  • в контекстном меню выбрать: «Сортировка от минимального к максимальному» или «Сортировка от максимального к минимальному».

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

2) Если же максимальное / минимальное значение необходимо внести в определённую ячейку таблицы Эксель, то необходимо воспользоваться специальными функциями.

В Excel есть 2 функции:

Рассмотрим их работу на примере.

В таблице представлены доходы компании за 5 лет. Необходимо найти самый низкий и самый высокий доход за этот период.

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

Вот что получится в итоге:

3) Рассмотрим ещё одну ситуацию — требуется найти минимум / максимум только среди тех ячеек диапазона, которые удовлетворяют определённому условию.

В этом случае нужно использовать совместно функции ЕСЛИ и МИН / МАКС.

Важная деталь — такую формулу нужно вводить не через Enter, а с помощью комбинации клавиш Ctrl + Shift + Enter.

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

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

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

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

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

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

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

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

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

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

В 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 не было аналога СУММЕСЛИ или СРЗНАЧЕСЛИ для расчета максимального и минимального значения, поэтому использовали формулу массивов.

Пусть имеются данные

Нужно подсчитать максимальное значение в указанной группе. Название группы (критерий) введем в отдельную ячейку (D2). Пусть для начала это будет группа Б. Рядом введем следующую формулу:

Это формула массивов, поэтому ввести ее нужно комбинацией Ctrl + Shift + Enter.

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

Как это работает? Очень просто. Первым делом нужно указать диапазон, который будет использоваться в качестве аргумента функции МАКС, то есть только те ячейки, которые соответствуют указанной группе. Так как мы заранее позаботились об удобстве использования функции, то название группы указали не внутри формулы, а в отдельной ячейке (гораздо легче менять группу). Тогда формула для нужного диапазона выглядит так.

Указанное выражение отбирает только те значения, для которых название группы совпадает с условием в ячейке D2. Вот, как это видит Excel

На следующем этапе укажем функцию МАКС, аргументом которой выступает полученный выше массив. Excel воспринимает примерно так.

Видно, что максимальное значение внутри массива равно 31. Его и мы и увидим в ячейке с формулой. Нужно только не забыть итоговую функцию ввести комбинацией клавиш Ctrl + Shift + Enter, иначе ничего не получится. В строке формул формула массива отображается внутри фигурных скобок. Добавляются сами, специально дорисовывать не нужно.

Если функцию МАКС заменить на МИН, то по указанному условию (названию группы) будет выдаваться минимальное значение.

Функции Excel 2016 МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)

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

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

Все очень просто. Как и у СУММЕСЛИМН вначале указываем диапазон, где находится искомое максимальное значение (колонка В), затем диапазон с критериями (колонка А) и далее сам критерий (в ячейке D2). Можно указать сразу несколько условий. Таким же способом легко рассчитать минимальное значение по условию. Найдем, к примеру, минимум внутри группы Б.

Ниже показан ролик, как рассчитать максимальное и минимальное значение по условию.

Как найти максимальное значение по условию

Описание работы

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

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

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

ДИАПАЗОН — Диапазон проверяемых ячеек.

  • КРИТЕРИЙ — Условие в формате числа, выражения или текста, определяющее проверку максимального значения.
  • [ ДИАПАЗОН_ПОИСКА ] — Фактический диапазон для определения максимального значения. Если данный параметр не задан, будет использоваться ячейки, задаваемые параметром ДИАПАЗОН.
  • В качестве критерия можно указывать значения и логические выражения:

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

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

    excelka.ru

    Минимальное значение без учёта нулей - Приёмы работы с формулами - Эффективная работа в Excel - Статьи об Excel

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

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

    1. Решение с дополнительным столбцом

    В В1 напишите формулу


    и скопируйте её на диапазон В2:В5. Эта промежуточная формула в дополнительном столбце будет "убирать" из исходных данных нули, заменяя их на пустую строку ""

    В С1 напишите формулу

    Она и вернёт искомый результат - число 3

    Если в диапазоне есть отрицательные числа, то вместо формулы


    надо использовать формулу

    2. Решение формулой массива
    Для решения этой задачи используйте такую формулу массива:

    =МИН(ЕСЛИ(A1:A5<>0;A1:A5))


    Как это работает: Вот эта часть формулы A1:A5<>0 формирует массив {ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА}, после чего применяя функцию ЕСЛИ() мы преобразуем массив в следующий: {5:7:ЛОЖЬ:ЛОЖЬ:3}. Так как функция МИН() игнорирует текстовые и логические значения, то в итоге получаем искомое - число 3

    Если в диапазоне есть отрицательные числа, то вместо формулы

    =МИН(ЕСЛИ(A1:A5<>0;A1:A5))


    надо использовать формулу

    =МИН(ЕСЛИ(A1:A5>0;A1:A5))


    3. Решение простой формулой
    Используйте такую формулу:

    =НАИМЕНЬШИЙ(A1:A5;СЧЁТЕСЛИ(A1:A5;0)+1)


    Как это работает: С помощью функции СЧЁТЕСЛИ мы подсчитываем количество нулей в диапазоне и прибавив к этому количеству единицу мы возвращаем первое наименьшее число более нуля с помощью функции НАИМЕНЬШИЙ

    Если в диапазоне есть отрицательные числа, то вместо формулы

    =НАИМЕНЬШИЙ(A1:A5;СЧЁТЕСЛИ(A1:A5;0)+1)


    надо использовать формулу

    =НАИМЕНЬШИЙ(A1:A5;СЧЁТЕСЛИ(A1:A5;"<=0")+1)


    Примечание:
    Решение формулой массива можно применять не только к данным находящимся на листе, но и для виртуальных массивов
    В файле -примере приведены все решения, включая варианты для диапазонов, содержащих отрицательные числа

    Область применения: Любая версия Excel

    Комментарий: Этот же прием можно применять для получения среднего значения без учёта нулей в любой версии Excel, хотя начиная с версии Excel 2007, появилась функция СРЗНАЧЕСЛИ()

    www.excelworld.ru

    Функции МАКС и МИН в Excel по условию

    Подсчет максимального и минимального значения выполняется известными функциями МАКС и МИН. Бывает, что вычисления нужно произвести по группам или в зависимости от условия, как в СУММЕСЛИ.

    Долгое время в Excel не было аналога СУММЕСЛИ или СРЗНАЧЕСЛИ для расчета максимального и минимального значения, поэтому использовали формулу массивов.  

    Пусть имеются данные

    Нужно подсчитать максимальное значение в указанной группе. Название группы (критерий) введем в отдельную ячейку (D2). Пусть для начала это будет группа Б. Рядом введем следующую формулу:

    =МАКС(ЕСЛИ(A2:A13=D2;B2:B13))

    Это формула массивов, поэтому ввести ее нужно комбинацией Ctrl + Shift + Enter.

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

    Как это работает? Очень просто. Первым делом нужно указать диапазон, который будет использоваться в качестве аргумента функции МАКС, то есть только те ячейки, которые соответствуют указанной группе. Так как мы заранее позаботились об удобстве использования функции, то название группы указали не внутри формулы, а в отдельной ячейке (гораздо легче менять группу). Тогда формула для нужного диапазона выглядит так.

    ЕСЛИ(A2:A13=D2;B2:B13)

    Указанное выражение отбирает только те значения, для которых название группы совпадает с условием в ячейке D2. Вот, как это видит Excel

    На следующем этапе укажем функцию МАКС, аргументом которой выступает полученный выше массив. Excel воспринимает примерно так.

    Видно, что максимальное значение внутри массива равно 31. Его и мы и увидим в ячейке с формулой. Нужно только не забыть итоговую функцию ввести комбинацией клавиш Ctrl + Shift + Enter, иначе ничего не получится. В строке формул формула массива отображается внутри фигурных скобок. Добавляются сами, специально дорисовывать не нужно.

    Если функцию МАКС заменить на МИН, то по указанному условию (названию группы) будет выдаваться минимальное значение.

    Функции Excel 2016 МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)

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

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

    Все очень просто. Как и у СУММЕСЛИМН вначале указываем диапазон, где находится искомое максимальное значение (колонка В), затем диапазон с критериями (колонка А) и далее сам критерий (в ячейке D2). Можно указать сразу несколько условий. Таким же способом легко рассчитать минимальное значение по условию. Найдем, к примеру, минимум внутри группы Б.

    Ниже показан ролик, как рассчитать максимальное и минимальное значение по условию.

    Поделиться в социальных сетях:

    statanaliz.info

    Как найти среднее, минимальное и максимальное значение в Excel

    В Microsoft Office Excel можно работать с цифрами и узнавать любое числовое значение. Этот табличный процессор справится практически со всеми расчётами. Он идеально подходит для бухгалтерского учёта. Для вычислений существуют специальные инструменты — формулы. Их можно применять к диапазону или к отдельным ячейкам. Чтобы узнать минимальную или максимальную цифру в группе клеток, необязательно искать их самостоятельно. Лучше воспользоваться предназначенными для этого опциями. Также полезно будет разобраться, как посчитать среднее значение в Excel.

    Это особенно актуально в таблицах с большим объёмом данных. Если в столбце, например, указаны цены на продукцию торгового центра. И вам надо узнать, какой товар самый дешёвый. Если искать его «вручную», уйдёт очень много времени. Но в Экселе это можно сделать буквально за несколько кликов. Утилита также высчитывает среднее арифметическое. Ведь это две простые операции: сложение и деление.

    В этой статье мы расскажем, как вычислить различные значения в Эксель

    Максимальное и минимальное

    Вот как найти максимальное значение в Excel:

    1. Поставьте курсор-ячейку в любое место.
    2. Перейдите в меню «Формулы».
    3. Нажмите «Вставить функцию».
    4. В списке выберите «МАКС». Или напишите это слово в поле «Поиск» и нажмите «Найти».
    5. В окне «Аргументы» введите адреса диапазона, максимальное значение которого вам нужно узнать. В Excel имена клеток состоят из буквы и цифры («B1», «F15», «W34»). А название диапазона — это первая и последняя ячейки, которые в него входят.
    6. Вместо адреса можно написать несколько чисел. Тогда система покажет самое большее из них.
    7. Нажмите «OK». В клетке, в которой стоял курсор, появится результат.

    Следующий шаг – укажите диапазон значений

    Теперь будет легче разобраться, как найти минимальное значение в Excel. Алгоритм действий полностью идентичен. Просто вместо «МАКС» выберите «МИН».

    Среднее

    Среднее арифметическое вычисляется так: сложить все цифры из множества и поделить на их количество. В Экселе можно посчитать суммы, узнать, сколько ячеек в строке и так далее. Но это слишком сложно и долго. Придётся использовать много разных функций. Держать в голове информацию. Или даже что-то записывать на листочек. Но можно упростить алгоритм.

    Вот как найти среднее значение в Excel:

    1. Поставьте ячейку курсор в любое свободное место таблицы.
    2. Перейдите на вкладку «Формулы».
    3. Нажмите на «Вставить функцию».
    4. Выберите «СРЗНАЧ».
    5. Если этого пункта нет в списке, откройте его с помощью опции «Найти».
    6. В области «Число1» введите адрес диапазона. Или напишите несколько цифр в разных полях «Число2», «Число3».
    7. Нажмите «OK». В ячейке появится нужное значение.

    Нажмите ОК для подсчета

    Так можно проводить расчёты не только с позициями в таблице, но и с произвольными множествами. Excel, по сути, играет роль продвинутого калькулятора.

    Другие способы

    Максимальное, минимальное и среднее можно узнать и другими способами.

    1. Найдите панель функций с обозначением «Fx». Она над основной рабочей областью таблицы.
    2. Поставьте курсор в любую ячейку.
    3. Введите в поле «Fx» аргумент. Он начинается со знака равенства. Потом идёт формула и адрес диапазона/клетки.
    4. Должно получиться что-то вроде «=МАКС(B8:B11)» (максимальное), «=МИН(F7:V11)» (минимальное), «=СРЗНАЧ(D14:W15)» (среднее).
    5. Кликните на «галочку» рядом с полем функций. Или просто нажмите Enter. В выделенной ячейке появится нужное значение.
    6. Формулу можно скопировать непосредственно в саму клетку. Эффект будет тот же.

    Впишите диапазон и нажмите Enter

    Найти и вычислить поможет Excel-инструмент «Автофункции».

    1. Поставьте курсор в ячейку.
    2. Перейдите в раздел «Формулы».
    3. Найдите кнопку, название которой начинается на «Авто». Это зависит от выбранной в Excel опции по умолчанию («Автосумма», «Авточисло», «Автосмещ», «Автоиндекс»).
    4. Нажмите на чёрную стрелочку под ней.
    5. Выберите «МИН» (минимальное значение), «МАКС» (максимальное) или «СРЗНАЧ» (среднее).
    6. В отмеченной клетке появится формула. Кликните на любую другую ячейку — она будет добавлена в функцию. «Растяните» рамку вокруг неё, чтобы охватить диапазон. Или щёлкайте по сетке с зажатой клавишей Ctrl, чтобы выделять по одному элементу.
    7. Когда закончите, нажмите Enter. Результат отобразится в клетке.

    В Excel вычислить среднее значение достаточно легко. Не нужно складывать, а потом делить сумму. Для этого существует отдельная функция. Также можно найти минимум и максимум в множестве. Это намного легче, чем считать вручную или выискивать цифры в огромной таблице. Поэтому Эксель популярен во многих сферах деятельности, где требуется точность: бизнес, аудит, кадровое делопроизводство, финансы, торговля, математика, физика, астрономия, экономика, наука.

    nastroyvse.ru

    Функция МИН в Excel - 6 примеров использования

    В этой статье вы узнаете, как использовать функцию МИН в Excel 2007-2019, найти наименьшее значение по условию и выделить самое маленькое число в вашем диапазоне.

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

    Итак, начнем :)

    Функция Excel МИН - синтаксис

    Функция МИН проверяет ваш диапазон данных и возвращает наименьшее значение в этом наборе.

    Синтаксис для Excel МИН следующий:

        МИН (число 1; [число 2]; ...)

    число1, [число2],… - это серия чисел, откуда вы хотите получить минимум. Число1 - обязательный аргумент, в то время как [число 2] и следующие являются необязательными. В одной формуле МИН допускается до 255 аргументов. Аргументы могут быть числами, ссылками на ячейки и диапазонами.

    Функция МИН является одной из самых простых в применении. Вот пару примеров, доказывающих это:

    1. Нахождение наименьшего значения

    Допустим, у вас есть фрукты на складе. Ваша задача - проверить, какие из них заканчиваются. Есть несколько способов это сделать:

    1: Ввести каждое значение из столбца «Количество на складе» в формулу:

    = МИН(366;476;398;982;354;534;408)

    2: Сделать ссылки на каждую ячейку столбца Количество:

    = МИН(В2; В3; В4; В5; В6; В7; В8)

    3: Или просто сослаться на весь диапазон ячеек:

    = МИН(В2:В8)

    4: в качестве альтернативы вы можете создать именованный диапазон и использовать его вместо этого, чтобы избежать любых прямых ссылок:

    2. Как найти самую раннюю дату

    Представьте, что вы ждете несколько доставок и хотели бы быть готовыми к самой ближайшей. Как узнать самую раннюю дату в Excel? Легко! Используйте функцию МИН, следуя логике из примера 1:

    Примените МИН и выберите даты, ссылаясь непосредственно на ячейки:

    = МИН(В2:В8)

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

    = МИН(дата_поставки)

    3. Поиск абсолютного минимума в Excel

    Предположим, что у вас есть диапазон чисел, и вам нужно определить не просто самое маленькое из них, но найти абсолютный минимум или минимум по абсолютному значению (по модулю). Одна только МИН функция не сможет справиться с этим, поскольку она просто будет искать минимальное число. Здесь вам нужна вспомогательная функция, которая может конвертировать все отрицательные числа в положительные.

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

    Но вернемся к нашей задаче. Готовое решение для этого конкретного случая называется функцией ABS, которая возвращает абсолютное значение заданных вами чисел. Таким образом, комбинация функций МИН и ABS сделает свое дело. Просто введите следующую формулу в любую пустую ячейку:

    {= МИН(ABS(А1:Е12))}

    Важно! Вы заметили фигурные скобки вокруг формулы? Это признак того, что это формула массива, и ее нужно вводить с помощью комбинации клавиш Ctrl + Shift + Enter, а не просто Enter.

    Как найти самое маленькое значение в Excel, исключая нули

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

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

    {= МИН(ЕСЛИ(В2: В15>0,В2:В15))}

    Вы, наверное, заметили фигурные скобки вокруг формулы массива. Просто помните, что вы не вводите их вручную. Они появляются в тот момент, когда вы нажимаете Ctrl + Shift + Enter на клавиатуре.

    Нахождение минимума на основе условия

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

    Нажмите Ctrl + Shift + Enter, чтобы эта функция массива работала, и наслаждайтесь результатом.

     Выглядит довольно легко, правда? И как вы определите минимум на основе 2 или более условий? Может быть, есть более простая формула для этой задачи, доступная в Excel? Читайте дальше, чтобы узнать ответ. ;)

     Как подсветить наименьшее число в Excel

    А что, если вам не нужно возвращать наименьшее значение, а вы просто хотите найти его в своей таблице? Самый простой способ найти ячейку - выделить ее. И самый простой способ сделать это - применить условное форматирование. Это даже проще, чем создать формулу:

    1 Создайте новое правило условного форматирования, щелкнув Условное форматирование -> Создать правило

    2 Когда откроется диалоговое окно «Создание правила форматирования», выберите тип правила «Форматировать только первые или последние значения».

    3 Поскольку задача состоит в том, чтобы найти одно-единственное наименьшее значение, выберите опцию «последним» в раскрывающемся списке и укажите 1 в качестве количества ячеек, которые нужно выделить.

    Но что делать, если в вашей таблице снова есть ноль? Как игнорировать нули при выделении минимального значения? Не беспокойтесь, для этого случая тоже есть маленькая хитрость:

    1. Создайте новое правило условного форматирования, выбрав опцию «Использовать формулу для определения форматируемых ячеек».

    2. Введите это выражение в поле «Значения формата» для проверки условия:

    =B2=МИН(ЕСЛИ($B$2:$B$15>0,$B$2:$B$15))

    Где B2 - первая ячейка диапазона, в которой будет найдено самое маленькое значение

    3. Выберите цвет ( Формат… -> Заливка) и нажмите ОК.

    4. Наслаждайтесь :)

    Почему не работает функция МИН?

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

    Исправление ошибки #ЗНАЧ! в формуле МИН

    Как правило, вы получаете #ЗНАЧ! сообщение об ошибке, когда хотя бы одно из значений, использованных в формуле, неверно. Что касается МИН, это может произойти, когда один из аргументов поврежден, например, что-то не так с ячейками, которые использует формула.

    Например, #ЗНАЧ! может появиться, если один из аргументов является ячейкой с ошибкой или в ссылке на ячейку есть опечатка.

    Что может вызвать ошибку #ЧИСЛО! ?

    Excel показывает ошибку #ЧИСЛО!, когда невозможно рассчитать вашу формулу. Обычно это происходит, когда числовое значение слишком велико или мало для отображения в Excel. В Excel допустимы номера от -2,2251E-308 до 2,2251E-308. Если один из ваших аргументов находится вне этого диапазона, функция вернет #ЧИСЛО!

    Я получаю сообщение #ДЕЛ/0!, что делать?

    Исправление #ДЕЛ/0! -  это легко. Не делите на ноль! :) Не шучу, это единственное решение этой проблемы. Проверьте диапазон ваших данных, исправьте ошибку #ДЕЛ/0!, и формула незамедлительно вернет правильный результат.

    Ищете минимум, но получаете #ИМЯ?

    Сообщение #ИМЯ? означает, что Excel не может распознать формулу или ее аргументы. Наиболее вероятная причина этого результата - опечатка. Вы можете либо неправильно написать формулу, либо указать неверные аргументы для получения такого результата.

    Другая возможная причина этой проблемы лежит в именованном диапазоне. Итак, если вы ссылаетесь на несуществующий диапазон или в нем есть опечатка, вы увидите #ИМЯ? в ячейке с результатом.

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

    Вот и все на сегодня. Пожалуйста, не стесняйтесь делиться своими мыслями и вопросами в разделе комментариев, я буду рад получить от вас обратную связь! :)

    Еще полезная дополнительная информация:

    Как сделать зависимый выпадающий список в Excel? - Одной из наиболее полезных функций проверки данных является возможность создания выпадающего списка, который позволяет выбирать значение из предварительно определенного перечня. Но как только вы начнете применять это в своих таблицах, то неизбежно столкнетесь с проблемой: нужно сделать один выпадающий список… Создаем выпадающий список в Excel при помощи формул - Задача: Создать выпадающий список в Excel таким образом, чтобы в него автоматически попадали все новые значения. Сделаем это при помощи формул, чтобы этот способ можно было использовать не только в Excel 2007 и старше, но и в Excel 2003. Как… 5 способов создания выпадающего списка в ячейке Excel - Одной из наиболее полезных функций при вводе данных является возможность использовать выпадающий список. Он позволяет выбирать значение из предварительно определенного перечня и разрешает вводить только те данные, которые соответствуют вашим требованиям. Мы предложим вам несколько простых способов, как создавать выпадающие…

    mister-office.ru

    МИН и ЕСЛИ Минимальное ненулевое значение

     

    В EXCEL ранее 2016 версии нет функции, объединяющей в себе МИН и ЕСЛИ.

     

    В тех случаях, когда необходимо найти минимум, но с определённым условием (например, ненулевой), необходимо воспользоваться функцией массива.

     

     

    Найдем в примере наименьшие ненулевые продажи в наших минимаркетах.

     

     

    Нулевые значения будем отбрасывать, т.к. это означает, что магазины закрывались на ремонт.

     

     

    Будем работать с окнами аргументов (fx), чтобы разобраться в порядке и в скобках.

     

     

    Для этого:

     

    1. В ячейку с будущим результатом вводим функцию =МИН(

    2. Далее после открытия аргументов с помощью кнопки fx, вводим в первый аргумент вручную с скобками - ЕСЛИ()

    3. Выделяем слово ЕСЛИ два раза левой кнопкой и также нажимаем на fx 2 раза с задержкой, чтобы открыть окно аргументов.
    4. В аргумент Лог_выражение выделяем весь диапазон наших продаж и прописываем условие >0
    5. В аргумент Значение_если_истина заново выделяем тот же наш диапазон и нажимаем ОК.
    6. Выражение выдаст ошибку #ЗНАЧ! т.к. такая запись подразумевает использование выражений в виде массива.

    Поэтому ставим курсор на наше выражение и нажимаем сочетание клавиш Ctrl+Shift+Enter.


     


    Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
    (для перевода по карте нажмите на VISA и далее "перевести")

    excelspb.ru

    Поиск максимального значения в excel Excelka.ru

    Как найти максимальное значение по условию

    Описание работы

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

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

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

    ДИАПАЗОН — Диапазон проверяемых ячеек.

  • КРИТЕРИЙ — Условие в формате числа, выражения или текста, определяющее проверку максимального значения.
  • [ ДИАПАЗОН_ПОИСКА ] — Фактический диапазон для определения максимального значения. Если данный параметр не задан, будет использоваться ячейки, задаваемые параметром ДИАПАЗОН.
  • В качестве критерия можно указывать значения и логические выражения:

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

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

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

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

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

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

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

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

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

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

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

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

    В 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 предназначена для нахождения максимального числового значения, хранящегося в столбце ячеек списка или поле базы данных с учетом установленных критериев поиска, и возвращает соответствующий результат.

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

    Рассматриваемые функции могут применяться для поиска данных в таблицах, оформленных как база данных Excel, а также списках и базах данных.

    Примеры использования функций ДМИН и ДМАКС в Excel по условию

    Пример 1. В таблице содержатся данные о сотрудниках фирмы (оформлена с учетом требований к БД). Необходимо определить:

    1. Минимальный и максимальный возраст мужчины.
    2. Минимальный и максимальны возраст женщины.
    3. Максимальный возраст члена коллектива с фамилией, начинающейся на «Б».

    Вид таблицы данных:

    На этом же листе создадим таблицу условий и таблицу для вывода найденных значений:

    В ячейку C2 поместим список с двумя возможными значениями условий выборки из базы данных («М» и «Ж»).

    Для нахождения минимального/максимального возраста мужчины используем функции:

    • A10:D18 – диапазон ячеек, в которых находится БД;
    • D1 – ячейка с названием поля данных, в котором хранятся сведения о возрасте;
    • A1:D2 – диапазон ячеек, в которых находится таблица условий.

    В таблице критериев установим условие значением пола «Ж» и аналогичным способом найдем значения минимального/максимального возраста женщины:

    Удалим содержимое из ячейки C2, в ячейке B2 введем строку =»=Б*». Для поиска максимального возраста с установленным критерием используем функцию:

    Данная запись является избыточной, поскольку после удаления значения из C2 будет произведен автоматический расчет с учетом нового критерия в ячейке D5. Однако, данная функция приведена для наглядности, чтобы продемонстрировать возможность указания второго аргумента в виде числового значения (в данном случае 4 – четвертый столбец, соответствующий полю «Возраст»). Полученный результат:

    В результате мы получили все данные после выборки с условием отбора возраста из общей БД в Excel.

    Сравнение максимального и минимального значения в Базе Данных Excel

    Пример 2. В таблице содержатся данные о сотрудниках, их должности (руководящее звено или штатный сотрудник), зарплатах и дате прима на работу. Найти разницу между минимальной зарплатой руководящего сотрудника и максимальной зарплатой штатного сотрудника, принятых на работу не позже 1.01.2015 года.

    Вид таблицы данных:

    Создадим две таблицы условий с разными условиями поиска. С помощью функции ДАТА запишем дату 1.01.2015 и установим формат «Общий» для ячейки с результатом, чтобы получить числовое представление даты – 42005. Получим следующее:

    Для нахождения разницы зарплат используем следующую формулу:

    Особенности использования функций ДМАКС и ДМИН в Excel

    Обе рассматриваемые функции имеют однотипный синтаксис (список аргументов совпадает):

    • база_данных – обязательный, может быть указан в виде ссылки на диапазон ячеек, которые соответствуют БД, списку или таблице, которая соответствует критериям, установленным для баз данных в Excel. В такой таблице строки являются записями, а столбцы – полями. В первой строке должны быть указаны наименования полей (столбцов).
    • поле – обязательный, принимает ссылку на ячейку, содержащую наименование столбца (поля) таблицы, списка или БД, где будет выполняться поиск наибольшего (ДМАКС) или наименьшего (ДМИН) значения соответственно. Может быть также указан в виде текстовых данных или числового значения – соответствующего номера столбца (отсчет начинается с левой части таблицы с числа 1).
    • условия – обязательный, принимает ссылку на диапазон ячеек, в которых указаны критерии поиска. В таком диапазоне должно содержаться хотя бы одно поле, соответствующее полю таблицы, списка или БД, в которых будет выполняться поиск максимального или минимального значения.
    1. Если любой из аргументов функции указан в виде данных недопустимого типа или ссылки на диапазон пустых ячеек, результатом выполнения любой из рассматриваемых функций будет код ошибки #ЗНАЧ!.
    2. Если в качестве аргумента поле был указан столбец (поле БД), не содержащий числовые данные, результатом выполнения функции будет значение 0 (нуль).
    3. Для удобства указания аргумента условия рекомендуют создавать отдельную таблицу, содержащую не менее двух записей (строк). При этом первая строка должна содержать наименование полей данных, полностью соответствующих наименованиям полей таблицы (списка или БД), где выполняется поиск. Вторая (и последующие) строка должна содержать критерии поиска (логические выражения, данные для сравнения). Такую таблицу условий следует размещать над основной таблицей (БД или списком), поскольку последняя может пополняться новыми записями со временем.
    4. При написании условий используются записи следующих видов:
    • =»=телевизор» – точное совпадение текстовой строки. Для неточных совпадений можно использовать замещающие знаки, например, «*» – любое число символов, «?» – один любой символ;
    • Для числовых данных используют знаки сравнения значений: «>», » «, «= «.

    Функции МАКС и МИН в Excel по условию

    Подсчет максимального и минимального значения выполняется известными функциями МАКС и МИН. Бывает, что вычисления нужно произвести по группам или в зависимости от условия, как в СУММЕСЛИ.

    Долгое время в Excel не было аналога СУММЕСЛИ или СРЗНАЧЕСЛИ для расчета максимального и минимального значения, поэтому использовали формулу массивов.

    Пусть имеются данные

    Нужно подсчитать максимальное значение в указанной группе. Название группы (критерий) введем в отдельную ячейку (D2). Пусть для начала это будет группа Б. Рядом введем следующую формулу:

    Это формула массивов, поэтому ввести ее нужно комбинацией Ctrl + Shift + Enter.

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

    Как это работает? Очень просто. Первым делом нужно указать диапазон, который будет использоваться в качестве аргумента функции МАКС, то есть только те ячейки, которые соответствуют указанной группе. Так как мы заранее позаботились об удобстве использования функции, то название группы указали не внутри формулы, а в отдельной ячейке (гораздо легче менять группу). Тогда формула для нужного диапазона выглядит так.

    Указанное выражение отбирает только те значения, для которых название группы совпадает с условием в ячейке D2. Вот, как это видит Excel

    На следующем этапе укажем функцию МАКС, аргументом которой выступает полученный выше массив. Excel воспринимает примерно так.

    Видно, что максимальное значение внутри массива равно 31. Его и мы и увидим в ячейке с формулой. Нужно только не забыть итоговую функцию ввести комбинацией клавиш Ctrl + Shift + Enter, иначе ничего не получится. В строке формул формула массива отображается внутри фигурных скобок. Добавляются сами, специально дорисовывать не нужно.

    Если функцию МАКС заменить на МИН, то по указанному условию (названию группы) будет выдаваться минимальное значение.

    Функции Excel 2016 МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)

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

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

    Все очень просто. Как и у СУММЕСЛИМН вначале указываем диапазон, где находится искомое максимальное значение (колонка В), затем диапазон с критериями (колонка А) и далее сам критерий (в ячейке D2). Можно указать сразу несколько условий. Таким же способом легко рассчитать минимальное значение по условию. Найдем, к примеру, минимум внутри группы Б.

    Ниже показан ролик, как рассчитать максимальное и минимальное значение по условию.

    excelka.ru

    Подcвечивать максимум (минимум) автоматически - EXCEL СПб

    В стандартных инструментах условного форматирования EXCEL отсутствует возможность АВТОМАТИЧЕСКИ форматировать только те ячейки, которые содержат максимальные или минимальные значения.

    Для того, чтобы это сделать, необходимо создать определенное выражение (формулу), результатом которого будет являться либо пара ИСТИНА/ЛОЖЬ, либо значения 1/0 и поместить его в модуль настройки условного форматирования.

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

    В результате нам необходимо «подсветить» ячейку с наибольшим среднедневным потреблением кофе в странах, измеренным в чашках на одного человека:

     

    Для этого:

     

    1. Будем использовать вариант с результатом ИСТИНА/ЛОЖЬ, и в ячейку B1 (может быть любая другая) внесем следующее выражение:

    =B3=МАКС($B$3:$B$35)

    B3 – первая проверяемая на максимум ячейка, она будет изменяться после прменения условного форматирования к диапазону.

    =B3=МАКС($B$3:$B$35)– фукнция максимума, выдающая (или как говорят – «возвращающая») максимум из диапазон, содержащего все значения к-ва чашек.

    Фиксация необходима для того, чтобы адреса диапазонов не «съехали» при применении форматирования на весь диапазон.

    Скопируем полностью выражение из строки формул в буфер обмена.

    2. Выделим весь диапазон со значениями (от B3 до B35), и на вкладке «Главная», нажмем кнопку «Условное форматирование»

    3. В открывшемся меню выберем «Создать правило».

    4. В типе правила выберем «Использовать формулу для определения форматируемых ячеек»

    5. В поле формул вставим скопированное выражение и, нажав кнопку «Формат» настроим желаемое форматирование максимального значения (в нашем случае – это будет заливка желтым).

    6. Сохраним изменения в каждом окне, нажав на «ОК».

    7. В результате максимальное значение будет подсвечено желтым цветом.

    Выражение в условном форматировании проверяет равенство выбранной ячейки максимуму из диапазона, и если вдруг она будет равна максимуму – выдаст результат «ИСТИНА», что в свою очередь даст сигнал включения условного форматирования, которое мы настроили.

     


    Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
    (для перевода по карте нажмите на VISA и далее "перевести")

    excelspb.ru


    Смотрите также

    faq-ru.ru

      Карта сайта, XML.