arrays - 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

作为一个单元格中的数组公式，没有 volatile 函数:

=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