Иногда вместо того, чтобы просто найти наибольшее или максимальное число для всех ваших данных; вам нужно найти наибольшее число в подмножестве — например, наибольшее положительное или отрицательное число.
Если объем данных небольшой, задачу можно легко выполнить, вручную выбрав правильный диапазон для функции MAX .
В других обстоятельствах, таких как большая выборка несортированных данных, правильный выбор диапазона может оказаться трудным, если не невозможным.
Комбинируя функцию IF с MAX в формуле массива , можно легко установить условия, такие как только положительные или отрицательные числа, чтобы по формуле проверялись только данные, соответствующие этим параметрам.
MAX IF Разбивка формулы массива
Формула, используемая в этом руководстве, чтобы найти наибольшее положительное число:
= МАКС. (ЕСЛИ (A1: B5> 0, A1: B5))
Аргумент value_if_false функции IF, который является необязательным, опущен, чтобы сократить формулу. Если данные в выбранном диапазоне не соответствуют установленному критерию — числа больше нуля — формула вернет ноль (0)
Работа каждой части формулы:
- Функция IF фильтрует данные так, что только те числа, которые соответствуют выбранному критерию, передаются в функцию MAX
- функция MAX находит наибольшее значение для отфильтрованных данных
- Формула массива, обозначенная фигурными скобками {}, окружающими формулу, позволяет логическому тестовому аргументу функции IF выполнять поиск во всем диапазоне данных на предмет совпадения, например, числа больше нуля, а не только в одной ячейке данных.
CSE Формулы
Формулы массива создаются одновременным нажатием клавиш Ctrl , Shift и Enter на клавиатуре после ввода формулы.
В результате вся формула, включая знак равенства, окружена фигурными скобками. Примером может быть:
{= МАКС. (ЕСЛИ (A1: B5> 0, A1: B5))}
Из-за клавиш, нажимаемых для создания формулы массива, их иногда называют формулами CSE .
Пример формулы MAX IF Array для Excel
Как видно на рисунке выше, в этом учебном примере используется формула массива MAX IF, чтобы найти самые большие положительные и отрицательные значения в диапазоне чисел.
Шаги ниже сначала создают формулу, чтобы найти наибольшее положительное число, затем следуют шаги, необходимые для поиска наибольшего отрицательного числа.
Ввод данных учебника
- Введите числа, показанные на рисунке выше, в ячейки от А1 до В5 рабочего листа.
- В ячейках A6 и A7 введите метки Max Positive и Max Negative.
Ввод MAX IF вложенной формулы
Поскольку мы создаем как вложенную формулу, так и формулу массива, нам необходимо ввести всю формулу в одну ячейку рабочего листа .
После того, как вы ввели формулу, НЕ нажимайте клавишу Enter на клавиатуре или щелкайте мышью по другой ячейке, поскольку нам нужно превратить формулу в формулу массива.
- Нажмите на ячейку B6 — место, где будут отображаться результаты первой формулы
- Введите следующее:
= МАКС. (ЕСЛИ (A1: B5> 0, A1: B5))
Создание формулы массива
- Нажмите и удерживайте клавиши Ctrl и Shift на клавиатуре
- Нажмите клавишу Enter на клавиатуре, чтобы создать формулу массива
- Ответ 45 должен появиться в ячейке B6, так как это самое большое положительное число в списке
Если щелкнуть ячейку B6, полную формулу массива можно увидеть на панели формул над рабочим листом.
{= МАКС. (ЕСЛИ (A1: B5> 0, A1: B5))}
Нахождение наибольшего отрицательного числа
Формула для нахождения наибольшего отрицательного числа отличается от первой формулы только оператором сравнения, используемым в аргументе логического теста функции IF.
Поскольку теперь цель состоит в том, чтобы найти наибольшее отрицательное число, вторая формула использует оператор меньше чем ( < ), а не больше, чем оператор ( > ), чтобы проверить только те данные, которые меньше нуля.
- Нажмите на ячейку B7
- Введите следующее:
= МАКС. (ЕСЛИ (A1: B5 <0, A1: B5))
- Выполните шаги выше, чтобы создать формулу массива
- Ответ -8 должен появиться в ячейке B7, так как это самое большое отрицательное число в списке
Получение # ЗНАЧЕНИЕ! для ответа
Если ячейки B6 и B7 отображают # ЗНАЧЕНИЕ! Значение ошибки, а не ответы, указанные выше, возможно, потому, что формула массива была создана неправильно.
Чтобы устранить эту проблему, щелкните формулу на панели формул и снова нажмите клавиши Ctrl , Shift и Enter на клавиатуре.