Формула массива с несколькими условиями имеет проблему с игнорированием пустых ячеек

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

Вот мои данные

A    B        C
0.03
0.06
0.09
0.12
0.18 1.87E-06 0.236033
0.21 4.04E-05 0.249285
0.24 8.31E-05 0.268155
0.27 1.54E-04 0.268104
0.30 2.49E-04 0.274135
0.33 3.08E-04 0.301185
0.36 4.06E-04 0.311255
0.39 4.70E-04 0.329554
0.42 5.39E-04 0.332625
0.45 5.97E-04 0.339827
0.48 6.69E-04 0.347729
0.51 7.54E-04 0.359481
0.54 8.49E-04 0.371833
0.57 9.46E-04 0.379580
0.60 1.08E-03 0.386926

и вот моя формула массива. {=SUMPRODUCT(IF(AND($A3:$A22<0.5,$A3:$A22>0.2),$B3:$B22,""),IF(AND($A3:$A22<0.5,$A3:$A22>0.2),$C3:$C22,""))/SUM(IF(AND($A3:$A22<0.5,$A3:$A22>0.2),$B3:$B22,""))}

Я не понимаю, почему эта формула массива не работает. Если я заменю and в операторе if одним условием, тогда это будет работать нормально, возвращая значение 0,321592. {=SUMPRODUCT(IF($A3:$A22<0.5,$B3:$B22,""),IF($A3:$A22<0.5,$C3:$C22,""))/SUM(IF($A3:$A22<0.5,$B3:$B22,""))}

Я думаю, это может быть из-за того, что у меня есть несколько пустых ячеек, но их нет в столбце, к которому я применяю логическое утверждение, поэтому я запутался. Я хотел бы получить решение, чтобы я мог применить этот оператор if с and или каким-то обходным путем в формуле массива. Спасибо


arrays if-statement excel array-formulas blank-line
person WnGatRC456    schedule 10.01.2020    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Это потому, что AND не может возвращать массив, он возвращает только одиночные TRUE или FALSE. Попробуйте следующую формулу массива:

{=SUMPRODUCT(($A3:$A22<0.5)*($A3:$A22>0.2)*($B3:$B22),($A3:$A22<0.5)*($A3:$A22>0.2)*($C3:$C22))/SUM(($A3:$A22<0.5)*($A3:$A22>0.2)*($B3:$B22))}

Нужен ли результат?

person basic    schedule 10.01.2020
comment
Вы спрашиваете, нужен ли результат? Если да, то я хотел бы проверить. - person WnGatRC456; 10.01.2020
comment
Да я не проверял правильность результата, просто переделал функции для работы с массивами - person basic; 10.01.2020
comment
Для ваших тестовых данных это было 0,321638. - person basic; 10.01.2020

arrow_upward
0
arrow_downward

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

{==SUMPRODUCT(IF($A3:$A22<0.5,IF($A3:$A22>0.2,$B3:$B22,""),""),IF($A3:$A22<0.5,IF($A3:$A22>0.2,$C3:$C22,""),""))/SUM(IF($A3:$A22<0.5,IF($A3:$A22>0.2,$B3:$B22,""),""))}
person WnGatRC456    schedule 10.01.2020