Приведите результат вычитания двух массивов, превышающих определенное значение в Excel

Question

Я новичок в массиве формул и у меня возникли проблемы со следующим сценарием:

У меня есть следующая матрица:

F   G   H   I   J  ...  R   S   T   U   V
1   0   0   1   1                       
    0   1       1       1   2   3   1   2
2   0   2   3   1       2   0   1   0   0
2   1   0   0   1       0   0   3   0   0

Моя цель - подсчитать количество строк, в пределах которых разница между суммой столбцов F: J и суммой столбцов R: V превышает пороговое значение. Крайне важно, чтобы были включены только строки с полными данными: строка 1 (где есть только значения для столбцов F1:J1) и строка 2 (где есть только некоторые значения для столбцов F2:J2) должны игнорироваться.

Если порог = 2,5, то решение равно 1. То есть строка 3 является единственной строкой с полными данными, где разница между суммой F3:J3 (8) и суммой R3:V3 (3) больше 2,5 (например, 5 > 2,5).

Я попытался довольно патетически составить следующую формулу, основанную на учениях @Tom Sharpe и @QHarr:

=COUNT(IF(SUBTOTAL(9,OFFSET(F1,ROW(F1:F4)-ROW(F1),0,1,COLUMNS(F1:J1)))-SUBTOTAL(9,OFFSET(R1,ROW(R1:R4)-ROW(R1),0,1,COLUMNS(R1:V1)))>2.5,IF(AND(SUBTOTAL(2,OFFSET(F1,ROW(F1:F4)-ROW(F1),0,1,COLUMNS(F1:J1)))=COLUMNS(F1:J1),SUBTOTAL(2,OFFSET(R1,ROW(R1:R4)-ROW(R1),0,1,COLUMNS(R1:V1)))=COLUMNS(R1:V1)),SUBTOTAL(9,OFFSET(F1,ROW(F1:F4)-ROW(F1),0,1,COLUMNS(F1:J1)))),IF(AND(SUBTOTAL(2,OFFSET(F1,ROW(F1:F4)-ROW(F1),0,1,COLUMNS(F1:J1)))=COLUMNS(F1:J1),SUBTOTAL(2,OFFSET(R1,ROW(R1:R4)-ROW(R1),0,1,COLUMNS(R1:V1)))=COLUMNS(R1:V1)),SUBTOTAL(9,OFFSET(R1,ROW(R1:V1)-ROW(R1),0,1,COLUMNS(R1:V1))))))

Но, похоже, всегда выдается значение 1, даже если я редактирую матрицу так, что разница между суммой F4:J4 и R4:v4 также превышает 2,5. К сожалению, я изо всех сил пытаюсь понять почему и был бы признателен за любые указания по этому вопросу.

Answer 1

Как формула массива в одной ячейке без энергозависимых функций:

=SUM((MMULT(--(LEN(F2:J5)*LEN(R2:V5)>0),--TRANSPOSE(COLUMN(F2:J2)>0))=5)*(MMULT(F2:J5-R2:V5,TRANSPOSE(--(COLUMN(F2:J2)>0)))>2.5))

должен сделать свое дело:D

Answer 2

Попробуй это:

=SUMPRODUCT((MMULT(F1:J4-R1:V4,--(ROW(INDIRECT("1:"&COLUMNS(F1:J4)))>0))>2.5)*(MMULT((LEN(F1:J4)>0)+(LEN(R1:V4)>0),--(ROW(INDIRECT("1:"&COLUMNS(F1:J4)))>0))=(COLUMNS(F1:J4)+COLUMNS(R1:V4))))

Answer 3

Может быть, скажем, X1 (при условии, что вы пометили свои столбцы):

=COUNTIF(Y:Y,TRUE)

В Y1 независимо от выбранного вами среза (например, 2.5) и в Y2:

=((COUNTBLANK(F2:J2)+COUNTBLANK(R2:V2)=0)*SUM(F2:J2)-SUM(R2:V2))>Y$1

скопировано вниз, чтобы удовлетворить.

Answer 4

Я думаю, что это сделает это, заменив ваши AND на умножения (*):

=SUMPRODUCT(--((SUBTOTAL(9,OFFSET(F1,ROW(F1:F4)-ROW(F1),0,1,COLUMNS(F1:J1)))-SUBTOTAL(9,OFFSET(R1,ROW(R1:R4)-ROW(R1),0,1,COLUMNS(R1:V1)))>2.5)*(SUBTOTAL(2,OFFSET(F1,ROW(F1:F4)-ROW(F1),0,1,COLUMNS(F1:J1)))=COLUMNS(F1:J1))*(SUBTOTAL(2,OFFSET(R1,ROW(R1:R4)-ROW(R1),0,1,COLUMNS(R1:V1)))=COLUMNS(R1:V1))>0))

Это может быть упрощено немного больше, но немного меньше времени.

Answer 5

Просто еще один вариант...

=IF(NOT(OR(IFERROR(MATCH(TRUE,ISBLANK(F1:J1),0),FALSE),IFERROR(MATCH(TRUE,ISBLANK(R1:V1),0),FALSE))), SUBTOTAL(9,F1:J1)-SUBTOTAL(9,R1:V1), "Missing Value(s)")

Мой подход немного отличался от того, что вы пытались адаптировать из @TomSharp, в том, что я проверяю данные в ячейках (не пустые), а затем выполняю вычисление, иначе возвращаю сообщение об ошибке. Это по-прежнему вызов функции массива, поэтому при вводе формул нажмите ctrl+shft+enter,

Условие часть открытия if() проверяет, что ячейки каждого диапазона не пустые: if a match( true= isblank(cell))означает, что ячейка пуста (плохая), если нет совпадений... т.е. нет пустых ячеек, Match вернет #NA "ошибка" (хорошая). Ложь это хорошо = ошибки найдены? № ((т.е. без пустых ячеек))

Тогда пороговое условие становится:

=COUNTIF(X1:X4,">"&Threshold)' Note: no Array formula here

Я дал порог (сотовый W6) именованный диапазон для чтения.