如何计算Excel中带有文本的过滤单元格？

如何计算Excel中带有文本的过滤单元格？

在 Excel 中，用文本计算单元格很简单。 但是当涉及到 计算带有文本的过滤单元格，事情变得棘手。 本教程将介绍 三种方式

从过滤列表中计算包含文本的单元格。

使用辅助列计算带有文本的过滤单元格

通过连接 SUMPRODUCT、SUBTOTAL、INDIRECT、ROW 和 ISTEXT 函数计算过滤后的单元格和文本

通过组合 SUMPRODUCT、SUBTOTAL、OFFSET、MIN、ROW 和 ISTEXT 函数计算过滤后的单元格和文本

使用 COUNTIF 函数，加上辅助列的帮助，我们可以轻松地计算过滤后的单元格的文本。 请执行以下操作。

1. 请将下面的公式复制到单元格 D2 中，然后按 Enter 键得到第一个结果。

=小计 (103, A2)

注意：带有 SUBTOTAL 公式的辅助列用于测试该行是否被过滤。 和 103 代表 COUNTA 函数在 函数编号

论据。

2.然后 向下拖动填充手柄

到您也想应用此公式的单元格。

3.请将下面的公式复制到单元格中 F2，然后按 输入 获得最终结果的关键。

=COUNTIFS(A2:A18,"*", D2:D18, 1)

我们可以看到有 4 过滤数据中带有文本的单元格。

另一种用文本计算过滤单元格的方法是使用 组合 的 SUMPRODUCT, 小计, 间接, 行 & ISTEXT 功能。 请执行以下操作。

请将下面的公式复制到单元格中 E2，然后按 输入 获得结果的关键。

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A18))), --(ISTEXT(A2:A18)))

公式解释： 行(A2:A18) 返回范围 A2:A18 的相应行号。 间接（“A”＆ROW（A2：A18）） 返回给定范围内的有效单元格引用。 小计（103，间接（“A”＆ROW（A2：A18））） 测试该行是否被过滤，并为可见单元格返回 1，为隐藏单元格和空单元格返回 0。 ISTEXT（A2：A18） 检查范围 A2:A18 中的每个单元格是否包含文本，并为包含文本的单元格返回 True，为其他单元格返回 False。 双一元运算符 (--) 将 TRUE 和 FALSE 值强制转换为 1 和 0。 SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A18))), --(ISTEXT(A2:A18))) 可以看作 SUMPRODUCT({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1}). 然后 SUMPRODUCT 将两个数组相乘并返回值的总和，即 4。

第三种使用过滤数据中的文本计算单元格的方法是 串联 此 SUMPRODUCT, 小计, OFFSET, 闵, 行 & ISTEXT 功能。 请执行以下操作。

请将下面的公式复制到单元格中 E2，然后按 输入 获得结果的关键。

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)), -- (ISTEXT(A2:A18)) )

公式解释： OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1) 返回范围 A2:A18 中的单个单元格引用。 SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)) 检查行是否被过滤，并为可见单元格返回 1，为隐藏单元格和空单元格返回 0。 ISTEXT（A2：A18） 检查范围 A2:A18 中的每个单元格是否包含文本，并为包含文本的单元格返回 True，为其他单元格返回 False。 双一元运算符 (--) 将 TRUE 和 FALSE 值强制转换为 1 和 0。 SUMPRODUCT（小计（103，偏移量（A2：A18，ROW（A2：A18）-2--MIN（ROW（A2：A18）-2），，1）），--（ISTEXT（A2：A18））） 可以看作 SUMPRODUCT({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1}). 然后 SUMPRODUCT 将两个数组相乘并返回值的总和，即 4。