Excel公式：计算包含特定值的行

220 多个excel快捷键速查表，适用Windows和Mac。工作效率提高10倍。免费下载>>>

Excel 公式：计算包含特定值的行

通用公式

=SUM(—(MMULT(—(criteria),TRANSPOSE(COLUMN(data)))>0))

概括

要对包含特定值的行进行计数，您可以使用基于MMULT、TRANSPOSE、COLUMN和SUM函数的数组公式。在所示示例中，G5 中的公式为：

{=SUM(—(MMULT(—(data=90),TRANSPOSE(COLUMN(data)))>0))}

其中data是命名范围B4:B12。

注意：这是一个数组公式，必须使用 control shift enter 输入，Excel 365除外。

解释

在此示例中，目标是对包含特定值的行进行计数。挑战在于该值可能出现在几列中的任何一列中，并且可能出现在同一行的多个列中。从内到外，这个公式中使用的逻辑标准是：

—(data=90)

其中 data 是命名范围 B4:D12。这会为 data 中的每个值生成一个 TRUE / FALSE 结果，双重否定将 TRUE FALSE 值强制为 1 和 0 以产生如下数组：

{1,0,0;0,0,0;0,1,1;1,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,1}

与原始数据一样，此数组为 9 行 x 3 列 (9 x 3)，并作为array1进入 MMULT 函数。 Array2是使用以下代码段派生的：

TRANSPOSE(COLUMN(data))

这是这个公式的棘手和有趣的部分。COLUMN 函数只是为了方便而使用，作为生成正确大小的数值数组的一种方式。要使用 MMULT 执行矩阵乘法，array1 (3) 中的列数必须等于array2中的行数。

COLUMN 返回 3 列数组 {2,3,4}，TRANSPOSE 将此数组更改为 3 行数组 {2;3;4}。MMULT 然后运行并返回一个 9 x 1 数组结果：

=SUM(—({2;0;7;2;0;0;0;0;4}>0))

我们检查大于 0 的非零条目，并再次将 TRUE FALSE 强制为 1 和 0，并使用双负数来获得 SUM 内的最终数组：

=SUM({1;0;1;1;0;0;0;0;1})

在这个最终数组中，1 表示逻辑测试 (data=90) 返回 TRUE 的行。SUM 返回的总数是包含数字 90 的所有行的计数。

如果您需要检查特定的文本值，换句话说，从字面上检查单元格是否包含某些文本值，您可以更改此页面上的公式中的逻辑以使用 ISNUMBER 和 SEARCH 函数。例如，要计算包含“apple”的单元格/行，您可以使用：

=ISNUMBER(SEARCH(“apple”,data))

220 多个excel快捷键速查表，适用Windows和Mac。工作效率提高10倍。免费下载>>>

发表评论 取消回复