Загрузите образец книги
В этом руководстве будет показано, как найти наибольшее значение ячейки, которое соответствует определенным условиям в Excel и Google Таблицах.
Макс.если функция массива
Функция MAX определяет наибольшее значение в серии чисел.
Мы можем использовать функцию MAX в сочетании с функцией IF, чтобы определить наибольшее значение, которое соответствует указанному условию.
Пользователям Google Таблиц и Excel 2022 или более поздних версий рекомендуется использовать более простую функцию MAXIFS. Это объясняется в следующем разделе.
В этом примере функции MAX и IF в формуле массива используются для определения наибольшего Размер заказа для каждого Название магазина
| 1 | {= МАКС (ЕСЛИ (B3: B8 = "A", D3: D8))} |
В Office 365 и версиях Excel после 2022 года вы можете просто ввести указанную выше формулу, как обычно (нажав клавишу ВВОД).
Однако для Excel 2022 и более ранних версий формулу необходимо вводить, нажимая CTRL + SHIFT + ENTER. После этого вы заметите фигурные скобки вокруг формулы.
Чтобы показать, как работает эта формула, давайте разберем ее на шаги.
Это наша окончательная формула (показана без автоматически добавленных скобок формулы массива):
| 1 | = МАКС (ЕСЛИ (B3: B8 = "A"; D3: D8)) |
Сначала значения диапазона ячеек добавляются в формулу в виде массивов:
| 1 | = МАКС (ЕСЛИ ({"A"; "B"; "A"; "B"; "A"; "B"} = "A", {500; 400; 300; 700; 600; 200})) |
Далее Название магазина = Условие «A» создает массив значений ИСТИНА / ЛОЖЬ:
| 1 | = МАКС (ЕСЛИ ({ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ}, {500; 400; 300; 700; 600; 200})) |
Затем функция ЕСЛИ изменяет все значения ИСТИНА на соответствующие Размер заказа:
| 1 | = МАКС ({500; ЛОЖЬ; 300; ЛОЖЬ; 600; ЛОЖЬ}) |
Функция MAX определяет наибольшее число в массиве, игнорируя любые значения FALSE, чтобы показать наибольшее число. Размер заказа для Название магазина = «А»:
| 1 | =600 |
Макс. Если - несколько критериев
Мы также можем определить наибольшее значение на основе нескольких критериев, используя логическую логику.
Этот пример покажет самый большой Размер заказа для каждого Название магазина, но для Сроки заказа до 30.04.2021 с использованием функций MAX, IF и DATE:
| 1 | {= МАКС (ЕСЛИ ((B3: B8 = "A") * (C3: C8 |
Обратите внимание, что здесь мы умножаем два набора критериев ИСТИНА / ЛОЖЬ вместе:
| 1 | (B3: B8 = "A") * (C3: C8 |
Если оба критерия ИСТИНА, то общее условие будет вычислено как ИСТИНА, но если один (или несколько) критериев - ЛОЖЬ, оно будет вычислено как ЛОЖЬ.
Используя эту методологию, можно добавить к этой формуле множество различных критериев.
Max If - несколько критериев со ссылками на ячейки
Обычно жестко записывать значения в формулы не рекомендуется. Вместо этого более гибко использовать отдельные ячейки для определения критериев.
Чтобы соответствовать Название магазина до значения, указанного в столбце F, мы можем обновить формулу до следующего вида:
| 1 | {= МАКС (ЕСЛИ ((B3: B8 = F3) * (C3: C8 |
Блокировка ссылок на ячейки
Чтобы наши формулы было легче читать, мы показали формулы без заблокированных ссылок на ячейки:
| 1 | {= МАКС (ЕСЛИ ((B3: B8 = F3) * (C3: C8 |
Но эти формулы не будут работать должным образом при копировании и вставке в другое место в файле. Вместо этого вы должны использовать ссылки на заблокированные ячейки следующим образом:
| 1 | {= МАКС (ЕСЛИ (($ B $ 3: $ B $ 8 = F3) * ($ C $ 3: $ C $ 8 |
Прочтите нашу статью о блокировке ссылок на ячейки, чтобы узнать больше.
Функция MAXIFS
Пользователи Google Sheets и Excel 2022 или более поздней версии могут использовать одну функцию MAXIFS для воспроизведения поведения функций MAX и IF, показанных в предыдущих примерах.
В следующем примере используются функции MAXIFS и DATE для отображения наибольшего Размер заказа для каждого Название магазина для Сроки заказа до 30.04.2021:
| 1 | = МАКСЕСЛИМН (D3: D8; B3: B8; «A»; C3: C8; «<» & ДАТА (2021,4,30)) |
Функция MAXIFS не требует от пользователя нажатия клавиш CTRL + SHIFT + ENTER при вводе формулы.
Максимальное значение If (максимальное значение с условием) в Google Таблицах
Приведенные выше примеры работают в Google Таблицах точно так же, как и в Excel, но, поскольку доступна функция MAXIFS, рекомендуется использовать эту единственную функцию вместо комбинирования функций MAX и IF.
Если необходимо использовать примеры, использующие функции MAX и IF, в Google Таблицах требуется, чтобы вы вводили их как формулы массива. Вместо отображения формулы с фигурными скобками массива Excel {} при нажатии CTRL + SHIFT + ENTER автоматически добавляется функция ARRAYFORMULA вокруг формулы:
| 1 | = ARRAYFORMULA (MAX (IF ((B3: B8 = "A") * (C3: C8 |
