不使用易失性函数来引用编号列

Question

我尝试用它来COUNTIF计算包含给定字符串的列中的单元格数。要检查的列由包含数字的单元格指定。

这是我目前所拥有的简化版本。

单元格 G1 中的数字 3 表示要搜索的列是“名称”列右侧的第 3 列，标题为“R3”（D 行）。单元格 G2 中的公式正确计算了 D 列中包含“Y”的单元格数。

=COUNTIF(OFFSET(A:A,0,G1),"Y")

这个公式的问题在于OFFSET函数易挥发的这意味着每次我打开工作簿时，公式都会重新计算。如果我尝试关闭工作簿而不做任何更改，系统会弹出对话框询问我是否要保存（不存在的）更改。

是否有其他公式可以计算列中“Y”的数量，而无需使用易失性函数？如果有帮助的话，我感兴趣的列的实际列标题确实是 R1、R2、R3 等。如果有帮助的话，我可以将数据转换为表格。

Answer 1

您可以使用数组公式：

=SUM((COLUMN(B1:D1)-1=G1)*(B1:D9="Y"))

使用 CTRL+SHIFT+ENTER 输入此公式。其中：

B1:D1 代表“R”列标题。B1
:D9 代表“R”列数据。G1
代表要查找的舍入数。

此公式本质上是在“Y”处创建一个由 1 组成的数组，但如果该列与您的查找相匹配，则将其乘以 1，否则乘以 0。乘以 0 的项实际上被抑制，您只剩下那些与“Y”标志和正确的列号相匹配的项。

使用此方法的好处是它非常容易扩展，并且假设 A 列中的名称永远不会是“Y”，甚至可以在表结构中实现自动扩展，公式如下：

=SUM((COLUMN(Table1[#Headers])-1=G1)*(Table1="Y"))

Answer 2

我找到了另一种计算数字的方法。它使用了INDEX函数（非易失性）的一个特性，这是我错过的。如果您输入 0 作为行参数，输入某个值（我们称之为col）作为列参数，INDEX将返回整个col列作为数组。然后可以通过以下方式搜索列数组COUNTIF

=COUNTIF(INDEX($B:$D,0,G1),"Y")

当然，您也可以在列中输入 0 而不是行，以便以INDEX数组形式返回整行。

Answer 3

这种非挥发性配方将帮助您摆脱这一问题。

=COUNTIF(CHOOSE($G$2,$B$2:$B$10,$C$2:$C$10,$D$2:$D$10),"Y")

笔记：

• 为了避免Named/Dynamic Named Range在公式中使用单元格引用，您可以。
• 根据需要调整公式中的单元格引用。