Как найти ячейки с максимальным значением в столбце Microsoft Excel

Posted on: 2020年10月22日 2020年11月12日
Tags: tips

В этой статье мы узнаем, как найти максимальное значение, если оно соответствует нескольким условиям в Excel.

Сценарий:

При работе с длинными диапазонами данных нам нужно найти максимальное значение среди диапазона, в котором выполняется более одного условия. Проще говоря, определение максимального значения с помощью функции ЕСЛИ Excel. Функция IF возвращает True или False, а функция Max ищет максимальное значение из соответствующего массива.

Синтаксис для поиска max по нескольким критериям

\{=MAX

(IF

(Criteria1=match1),IF(Criteria2=match2,

range_max))}

Примечание. Используйте Ctrl + Shift + Enter при работе с массивами или диапазонами в Excel. По умолчанию в формуле будут созданы фигурные скобки. НЕ пытайтесь ставить фигурные скобки вручную.

Пример:

Все это может сбивать с толку. Итак, давайте протестируем эту формулу, запустив ее на примере, показанном ниже. Здесь мы выполним формулу над значениями с критериями.

Используйте формулу:

=MAX(

IF(

B2:B11=»East»,

IF( C2:C11 >

50 , D2:D11 )))}

критерий 1 — цена должна быть из региона «Восток»

критерий 2 — цена, если количество больше 50.

Пояснение:

IF (C2: C11> 50, D2: D11) возвращает массив значений FALSE и значений цен, где количество больше 50.

\{ ЛОЖНЫЙ ; 303,63; 108,46; 153,34; ЛОЖНЫЙ ; 95,58; 520.01; 90,27; 177; ЛОЖНЫЙ } . IF (B2: B11 = «Восток», IF (C2: C11> 50, D2: D11)) возвращает массив оставшихся ценовых значений, если регион является Востоком.

Функция MAX находит максимальное значение из возвращенного массива, и это будет требуемое значение PRICE.

Здесь мы сопоставили диапазон (B2: B11) со значением «Восток» и количеством (C2: C11) больше 50, что возвращает максимум из диапазона цен (D2: D11). Нажмите Enter, чтобы получить максимум диапазона

Как видите, у нас есть значение MAX, если критерии совпадают из диапазона значений.

Альтернативная функция для версии Excel 365 и 2019.

Функция Excel MAXIFS — это новая функция, представленная в Excel 365 и 2019. Эта функция возвращает значение MAX из заданного диапазона.

В этой статье мы узнаем, как использовать функцию MAXIFS в Excel.

Синтаксис функции MAXIFS

=MAXIFS(max_range,criteria_range1,criteria1,…)

Max_range1: это числовой диапазон, содержащий максимальное значение.

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

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

У вас может быть несколько пар критериев_диапазона и критериев.

Давайте разберемся с функцией MAXIFS на примере.

Пример функции MAXIFS Итак, у меня есть таблица некоторых данных. Первый столбец содержит числовые значения. Второй столбец содержит регион, а третий столбец — отдел.

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

Формула для получения максимального значения из «Восточного» региона будет:

=MAXIFS(A2:A16,B2:B16,»East»)

Он вернет 82.

Здесь мы указали жестко заданные критерии, но мы также можем дать ссылку на критерии, чтобы сделать их динамическими.

=MAXIFS($A$2:$A$16,$B$2:$B$16,E2)

Здесь мы использовали link: / excel-range-name-what-is-an-absolute-reference-in-excel [absolute range] для блокировки максимального диапазона и диапазона критериев.

Вы можете поместить более 1 критерия в функцию MAXIFS. Например, если я хочу получить значение MAX от ИТ-отдела ЮЖНОГО региона, то формула будет иметь вид:

=MAXIFS(A2:A16,C2:C16,»IT»,B2:B16,»SOUTH»)

Это вернет 100 согласно приведенным выше данным. Если мы поместим «Accounts» вместо IT, MAXIFS вернет 82.

Итак, это статья о том, как использовать функцию MAXIFS в Excel 365 и 2019. Надеюсь, она была полезной. Если у вас есть какие-либо сомнения относительно этой статьи или любой другой темы Excel, спросите в разделе комментариев ниже.

Статья по теме

link: / statistics-formulas-calculate-max-if-condition-match [Рассчитать максимальное значение, если условие-совпадение] | Чтобы вычислить значение MAX с условием в Excel 2016 и более ранних версиях, используйте эту формулу. Используя вместе функции MAX и IF, мы можем получить условный вывод Max.

Функции Excel 2019/365:

link: / excel-365-functions-how-to-use-the-excel-unique-function [Как использовать УНИКАЛЬНУЮ функцию Excel] | Функция UNIQUE возвращает уникальные значения из заданного массива.

link: / excel-365-functions-the-sort-function-in-excel [Функция сортировки в Excel] | Функция SORT возвращает отсортированную форму предоставленного массива.

link: / excel-365-functions-how-to-use-excel-sortby-function [Как использовать функцию Excel SORTBY] | Функция SORTBY сортирует заданный диапазон по другому заданному диапазону. Диапазон сортировки не обязательно должен быть частью таблицы.

link: / excel-365-functions-how-to-use-the-excel-filter-function [Как использовать функцию Excel FILTER] | Функция ФИЛЬТР возвращает все совпадающие значения заданных критериев и переносит результат в соседние ячейки.

link: / excel-365-functions-the-sequence-function-in-excel [Функция SEQUENCE в Excel] | Функция ПОСЛЕДОВАТЕЛЬНОСТЬ возвращает серию последовательных чисел. Это формула динамического массива.