减去两个数组的结果在Excel中超过某个值 [英] Countif the Result of Subtracting Two Arrays Exceeds a Certain Value in Excel

问题描述

我是数组公式的新手，并且遇到以下情况：

I am new to array formulae and am having trouble with the following scenario:

我有以下矩阵：

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列只有一些值）。

My goal is to count the number of rows within which the difference between the sum of columns F:J and the sum of columns R:V is greater than a threshold. Critically, only rows with full data should be included: row 1 (where there are only values for columns F1:J1) and row 2 (where there are only some values for columns F2:J2) should be ignored.

如果阈值= 2.5，则解为1。也就是说，第3行是唯一具有完整数据的行，其中F3：J3（8）和之和之间的差R3：V3（3）之和大于2.5（例如5> 2.5）。

If the threshold = 2.5, then the solution is 1. That is, row 3 is the only row with complete data where the difference between the sum of F3:J3 (8) and the sum of R3:V3 (3) is greater than 2.5 (e.g., 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))))))

但是即使我编辑矩阵使得F4：J4和R4：v4之和之间的差也超过2.5，总是会产生1的值。可悲的是，我正在努力理解原因，并希望对此提供任何指导。

But it seems to always produce a value of 1, even if I edit the matrix such that the difference between the sum of F4:J4 and R4:v4 also exceeds 2.5. Sadly I am struggling to understand why and would appreciate any guidance on the matter.

推荐答案

作为一个单元格中的数组公式，而无需使用volatile函数：

As an array formula in one cell without volatile functions:

=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

这篇关于减去两个数组的结果在Excel中超过某个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！