Найти минимальное значение на основе 2 критериев (Excel 2013)

Поиск максимального значения в столбце на основе двух наборов критериев

Таким образом, логика будет следующей: найти минимальное значение в столбце M, где значение в столбце A соответствует столбцу N, а значение в столбце Y меньше 318.

Я пытался использовать формулу массива, подобную этой, но, похоже, она не работает/тяжело работает в памяти: =MIN(IF(AND(N:N=A2,Y:Y‹=318),M: М))

есть более простой способ? или, возможно, UDF, который мог бы работать?

Спасибо за помощь!


excel vba
person user3682157    schedule 30.01.2015    source источник

Ответы (3)


arrow_upward
1
arrow_downward

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

Вот три возможных рабочих версии:

1.) Используйте * для имитации AND

=MIN(IF((N:N=A2)*(Y:Y<=318),M:M))

подтверждается нажатием CTRL+SHIFT+ENTER

2.) Используйте несколько вложенных IF

=MIN(IF(N:N=A2,IF(Y:Y<=318,M:M)))

подтверждается нажатием CTRL+SHIFT+ENTER

3.) Используйте функцию AGGREGATE

=AGGREGATE(15,6,M:M/(N:N=A2)/(Y:Y<=318),1)

Преимущества этого подхода в том, что вам не нужна «запись массива», и он может игнорировать любые ошибки в данных.

В любом случае лучше уменьшить размеры диапазонов, если вы можете, потому что это может быть медленным с целыми столбцами.

person barry houdini    schedule 30.01.2015
comment
Привет, Барри. Впервые вижу агрегатную функцию. Можете ли вы объяснить, как это работает? - person user3682157; 31.01.2015
comment
АГРЕГАТ немного похож на функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ - первое число диктует функцию, например. SUM, AVERAGE, MIN и т. д. Хотя есть параметр MIN, вы не можете использовать его с условиями (поскольку он не принимает массив), поэтому 15 представляет функцию SMALL, 6 указывает Excel игнорировать ошибки, а затем часть M:M/(N:N=A2)/(Y:Y<=318) создает массив, который будет либо возвращать ошибки, либо числа (только при соблюдении ваших условий) - person barry houdini; 31.01.2015

arrow_upward
0
arrow_downward

Попробуйте это и отрегулируйте диапазоны в соответствии с вашими потребностями. Старайтесь не использовать ссылки на целые столбцы:

=SMALL(INDEX(($N$2:$N$101=A2)*($Y$2:$Y$101<=318)*$M$2:$M$101,),1+ROWS($M$2:$M$101)-COUNTIFS($N$2:$N$101,A2,$Y$2:$Y$101,"<=318"))

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

person tigeravatar    schedule 30.01.2015

arrow_upward
0
arrow_downward

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

Другими словами, у вас есть данные в столбцах от A до Y?

Итак, давайте создадим формулу в столбце AA: 1) определите, когда значение в столбце A совпадает с столбцом N, а значение в столбце Y ‹ 318

=and(A1=N1,Y1<318)

2) скопируйте AA1 во все строки ваших данных.

3) теперь у нас есть условие для отработки.. так как есть СУММЕСЛИ и СЧЁТЕСЛИ, но нет МИНИСЛИ.. нам придётся построить это самим. сначала ЕСЛИ: в столбце AB1:

=if(AA1,M1,"")

скопируйте это во все ваши данные.

наконец, сделайте мин:

=MIN(AB:AB)

Должен дать вам ответ.

Вероятно, вы могли бы соединить первые два вместе, но опять же, создавая сложную формулу, подобную этой, сначала стройте ее просто ;)

person Ditto    schedule 30.01.2015