过滤列中基于条件的计数（Microsoft Excel）

Marty有一个很大的工作表，列出了公司中的所有员工（过去和现在）。工作表跟踪有关每个员工的各种信息，例如姓名，地址，部门，性别，状态等。

马蒂经常过滤数据以满足他的需求。他希望有一种方法可以根据过滤后显示的行来确定男性员工的百分比和女性员工的百分比。 SUBTOTAL函数可以在已过滤的列上工作以提供各种计数，但不允许他根据已过滤的列的内容（“ M”或“ F”）确定计数。

一种方法是使用数据透视表确定百分比。

数据透视表相对易于使用，尤其是回答诸如此类的单个问题。但是，在允许您查看有关员工的详细信息方面，它们并不是十分出色，您可以看到有关男性/女性问题的汇总答案，但不能同时看到有关这些员工的详细信息。因此，我想集中精力在对马蒂问题的回答中使用简单公式。

创建公式来获得所需的百分比要比最初看起来要困难得多。例如，当所有员工记录都可见时，确定计数很容易。例如，假设性别在C列中，则可以简单地使用类似的方法来确定男性雇员的记录百分比：

问题是，如果按不同于C列的列过滤记录，例如，您可能按status列中的内容过滤记录。上面的公式仍然会根据性别列中的所有记录为您提供一个百分比，而不仅仅是由于过滤而当前可见的记录。

在这一点上，您可能认为SUBTOTAL函数可能有效，但Marty发现它无效。同样，如果性别在C列中，则可以在C列的底部添加以下内容：

但是，这无法满足所需的需求。主要问题是SUBTOTAL函数为您提供了可见记录的计数，但是在C列中这些记录是包含“ M”还是“ F”之间没有区别。次要问题是COUNTA会计数所有记录，而不是只是显示的那些。因此，该公式无法为您显示包含“ M”或“ F”的显示记录的百分比

但是，显示的是总记录数中显示的记录数的百分比。

如果需要，您可以尝试使用帮助器列。只需添加一列以将每个员工的“ M”或“ F”状态表示为1或0。这可以通过一个简单的公式来完成，例如：

再次，此公式假定社会性别列为C。假设此公式位于X列（您的帮助者列）中，则可以按以下方式使用两个SUBTOTAL函数：

SUBTOTAL的首次使用为您提供了包含“ M”的行数

SUBTOTAL的第二次使用将为您提供可见行的总数。公式的结果是可见记录的男性工人所占的百分比。您可以通过从1中减去男性结果来确定女性工作者的百分比。

如果您不能使用帮助程序列（或者您不想使用帮助程序列），则要克服前面讨论的方法的不足会比较棘手；它需要一个更复杂的公式。以下是一个有效的公式，但我将单个公式分为四行，以使其易于解释。

我将详细介绍该公式的工作原理，请耐心等待一段时间，这将需要一段时间。首先让我们看一下公式的这一部分，该部分跨越第一行的末尾和第二行的总数：

此处OFFSET函数的使用取决于3个参数。第一个对应于用于计算将要返回的参考的起点（在这种情况下，起点为C2：C9999）。第二个参数是要从第一个参数中定义的范围的开头偏移的行数。在这种情况下，要偏移的行是通过从要分析的实际行中减去该范围内的最低行号（它将始终返回值2）来定义的。因此，例如，如果要分析的行是第10行，则从中减去2（起始行）将使我们与第一个参数指定的范围的开头相比有8行的偏移。

第三个参数为空白，因此默认为0。这是在第一个参数指定的范围内要从第一列偏移的列数。最后，第四个参数是数字1，表示您希望OFFSET返回仅1个单元格高的范围。

底线是包括了公式的整个部分，因此它返回对要分析的列中单个单元格的引用。为了便于解释，我们将返回的值称为“ SingleCell”。将其插入原始公式中，我们得到：

然后，第一个SUBTOTAL函数返回单个单元格的COUNTA结果（由用于第一个参数的值3表示）。结果是SUBTOTAL返回0或1，具体取决于该单元格是否可见。 （如果将单元格从结果中滤除，则返回0。如果未将其滤除（可见），则返回1。）

公式的下一部分取决于ISNUMBER和SEARCH函数。此部分代码将返回0或1，具体取决于单元格是否包含字母“ M”。那么，最终结果归结为：

在此单行的情况下，SUMPRODUCT将返回0，这意味着该行在总计数中“不计数”。由于SUMPRODUCT是基于数组的函数，因此它将基于原始范围中每一行的乘法分别计算乘积。因此，它确定满足以下两个条件的所有行的计数：该行可见，并且该行包含字母“ M”。

最后，将其除以最终SUBTOTAL函数的结果，该结果是可见行数的COUNTA结果。最终结果是在C列中可见“ M”字符的可见行的百分比-Marty想要的确切结果。

为了获得可见行中女性的百分比，您所需要做的就是更改要搜索的内容：在公式中将“ M”更改为“ F”，您应该可以。

不过，有一点需要注意。 SEARCH函数不能区分大写和小写。因此，如果您在性别列（C列）中使用“男”代替“ M”，并使用“女”代替“ F”，则在公式中搜索“男”将匹配每一行，因为包含“女性”将包含字符“男性”。因此，最好坚持使用“ M”和“ F”，或者，如果必须使用“ Male”和“ Female”，则只需在公式中使用“ Female”并计算等于1减的男性记录的百分比女性百分比。

注意：

如果您想知道如何使用此页面（或_WordTips_网站上的任何其他页面）中描述的宏，我准备了一个包含有用信息的特殊页面。

链接：/ wordribbon-WordTipsMacros [点击此处在新的浏览器标签中打开该特殊页面]。

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧（13550）适用于Microsoft Excel 2007、2010、2013、2016、2019和Office 365中的Excel。