☰

在 Excel 中使用 COUNTIF 计算数组中“大于”值的数量

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 microsoft-excel-2007 google-docs countif
在 Excel 中使用 COUNTIF 计算数组中“大于”值的数量

我的情况是这样的。我是一名校长，正在为我的老师创建一个跟踪器，用于跟踪学生在评估中的进步情况。在当前的跟踪器中，它的设置如下：

            % of            67%
            Students Met

**Student Name    Week 1 Goal:         Week 1 Actual:        Goal Met?:**
  Example A       23                    32                    Y
  Example B       45                    44                    N
  Example C       53                    55                    Y

我使用公式对“目标达成情况？”列进行 Y 或 N 的输入，具体取决于实际值是否达到或超过目标值。然后，我使用另一个公式来确定学生达成率的百分比，方法是对“目标达成情况”列中的“Y”进行 COUNTIF，将其除以该列的 COUNTA，然后乘以 100。

我想知道的是……这对老师来说非常麻烦，我放的列和公式越多，老师输入错误的地方和弄乱公式的风险就越大……我有没有办法只对每周目标和每周实际列的数组进行 COUNTIF，然后只计算实际值 >= 目标值的行？比如有没有办法做 COUNTIF(E5:F17,F>=E)，并在条件中的 E 和 F 旁边放一些符号作为变量，这样它就可以逐行比较值，然后计算 F 值大于 E 值的行？

任何建议都很好，因为这样我就可以消除“目标达成”列（他们总是输入这个列，而我必须每周回去修改）。在庞大的跟踪器上，每 3 列都要保护，这很烦人！

非常感谢您的建议！

布伦丹

答案1

您应该始终使用密码保护您不想被更改的单元格。

要回答您的问题，您需要输入一个数组公式。假设您的列名称从 A3 开始，请键入以下内容，然后单击Ctrl+ Shift+ Enter。

=SUM(IF(C4:C6>=B4:B6,1,0))/COUNT(C4:C6)

如果操作正确，整个公式周围将出现花括号。

={SUM(IF(C4:C6>=B4:B6,1,0))/COUNT(C4:C6)}

答案2

我的解决方案

以下数组公式，加上适当调整的单元格引用，应该可以实现您想要的效果：

=SUM(1*((OFFSET(C2,0,0,COUNT(C:C),1)-OFFSET(B2,0,0,COUNT(C:C),1))>=0))/COUNT(C:C)

例子：

Excel 片段

设置C2引用第一个OFFSET到顶部单元格中的“实际”数据（F5在您的示例中），B2OFFSET引用“目标”数据倒数第二个单元格（E5在你的），并设置所有的致谢：致谢引用包含“实际”或“目标”数据之一的列（乙：乙或者女：女在你的）。 确定您选择替换的列中只有包含数值数据的单元格致谢：致谢引用是那些包含“实际”或“目标”值的单元格，否则公式将返回不准确的结果，或者完全失败。

要将公式作为数组公式输入，请正确输入公式，然后按 Ctrl-Shift-Enter，而不是直接按 Enter。（请参阅这里有关数组公式的更多信息，或者直接在线搜索“excel 数组公式”。） 任何时候您对公式进行了更改，总是必须使用 Ctrl-Shift-Enter 输入它才能使其正常运行。

怎么运行的

  • 每个都COUNT检索现有数据的行数。
  • 两次OFFSET调用检索了两个感兴趣的数组，即“实际”分数和“目标”分数。在每次调用中：
    • 第一个参数标记数组的左上角单元格；
    • 第二和第三个参数分别是行和列偏移量（这里都为零）；
    • 第四个参数表示数组中要包含的行数；
    • 第五个参数表示要包含的列数。
  • 由于这是一个数组公式，减去“实际”和“目标”数组将返回一个大小相同的数组，但包含它们之间的元素差异。
  • 比较>=0将差异数组转换为TRUE/FALSE值数组，指示是否达到目标。
  • 用于1*将布尔值TRUE和FALSE值转换为数字1和0，然后通过SUM函数进行计数。
    • 在 Excel 中，TRUE和FALSE隐式地携带一和零的数值。
    • TRUE但是，如果您尝试将包含/值的范围或数组传递FALSE给类似的函数SUM，它将忽略TRUE值并返回零。
    • 因此，将整个TRUE/FALSE数组乘以 1 是一种欺骗 Excel 以有用的方式解释数组的方法。
  • SUM除以就是COUNT达到设定目标的学生人数的期望比例。

编辑：根据 wbeard52 的回答，在 Excel 2010 或更高版本中应该可以用1*...结构替换 hack IF。在旧版本的 Excel 中，IF数组输入处理不当。

答案3

另外几个数组公式选项，因此您需要按Ctrl+ Shift+Enter来提交它们。

查找已达成的目标数量：

=SUM(--(C4:C6>B4:B6))

要查找实现目标的百分比：

=SUM(--(C4:C6>B4:B6))/count(C4:C6)

您可以单独使用它们。请记住按Ctrl+ Shift+将它们作为数组公式输入Enter。

答案4

另一种“数组公式”方法是这样的：

=AVERAGE(IF(E5:E17<>"",IF(F5:F17>=E5:E17,1,0)))

已确认CTRL++SHIFTENTER

这样你就得到了一个实际的百分比，比如 0.5 - 如果你想要 50，乘以 100

相关内容