企业官网建设流程全解析

你是不是也遇到过这样的场景：面对一份密密麻麻的Excel数据表，老板让你“把A列大于100且B列是‘已完成’的数据筛出来”，或者“找出所有不在这个名单里的人”？你熟练地打开了筛选器，却发现“与”条件好说，“或”条件怎么搞？更别提“反向筛选”了——想找出所有“非A且非B”的数据，难道要手动一个个勾掉吗？

很多人第一时间会想到SUMIFS、COUNTIFS或者高级筛选。没错，它们能解决大部分问题。但今天我要讲的，是一个被严重低估的“邪修”思路：用最基础的COUNTIF函数，配合数组公式，实现灵活的多条件“或”筛选和反向筛选。

这听起来有点反直觉。COUNTIF不是用来数数的吗？怎么还能筛选？这正是“邪修”的精髓——跳出函数的常规用法，利用其返回数值（0或非0）的特性，构建出强大的逻辑判断引擎。相比SUMIFS的“且”逻辑，COUNTIF构建的“或”逻辑和“非”逻辑，在应对不规则、动态变化的条件组合时，往往更加简洁和直观。

本文将带你彻底搞懂这个技巧。读完你将掌握：

1. 核心原理：COUNTIF如何化身逻辑判断工具。
2. 实战三步法：从单条件到多条件“或”筛选，再到复杂的反向筛选。
3. 完整公式剖析：结合FILTER、SUMPRODUCT等函数，写出既强大又易读的公式。
4. 避坑指南：处理文本、数字、空值时的关键细节。
5. 性能与替代方案：何时该用，何时不该用。

我们从一个最真实的办公痛点开始。

1. 为什么需要 COUNTIF 来做“邪修”筛选？

在深入公式之前，我们先明确两个最常见的筛选困境，这也是COUNTIF解法大显身手的地方。

困境一：多条件“或”筛选的繁琐假设你有一张销售记录表，需要找出“产品是‘手机’或‘平板’”的所有记录。使用常规筛选器，你需要在“产品”列下拉菜单中，手动勾选“手机”和“平板”。如果条件有5个、10个呢？勾到手酸。如果条件列表是动态变化的（比如来自另一个单元格区域），常规筛选几乎无法自动完成。

困境二：反向筛选的“绕路”老板说：“列出所有‘部门’不是‘销售部’且‘状态’不是‘已离职’的员工。”你的第一反应可能是：先筛选出“销售部”的人，再筛选出“已离职”的人，然后手动把这两批人从总表里剔除？或者用高级筛选写条件区域，但需要理解“<>”运算符和条件区域布局规则，对很多人来说门槛不低。

COUNTIF的邪道解法，恰恰能优雅地解决这两个问题。它的核心优势在于：

• 条件动态化：条件可以是一个单元格区域，增删条件只需修改这个区域，公式自动生效。
• 逻辑直观化：“或”关系就是检查目标值是否出现在条件列表中；“非”关系就是检查结果是否为0。
• 兼容性广：从古老的 Excel 2007 到最新的 Microsoft 365 都能使用（数组公式部分版本需按 Ctrl+Shift+Enter）。

接下来，我们从COUNTIF的基础讲起，重新认识这个函数。

2. COUNTIF 函数的核心：不止于计数，更是逻辑探测器

COUNTIF函数语法非常简单：

=COUNTIF(range, criteria)

• range：要计数的单元格区域。
• criteria：计数的条件，可以是数字、表达式、单元格引用或文本字符串（如">100","苹果",A2）。

传统认知：它在range里数一数有多少个单元格满足criteria，返回一个数字。

邪修视角：它返回的数字本身，就是一个布尔值（TRUE/FALSE）的数值化形式。在Excel中，TRUE相当于1，FALSE相当于0。所以：

• 如果COUNTIF(A2, "苹果")的结果是1，意味着A2单元格等于“苹果”（逻辑为真）。
• 如果结果是0，意味着A2单元格不等于“苹果”（逻辑为假）。

关键跃迁：当criteria参数是一个区域时，COUNTIF会进行一系列匹配检查。

=COUNTIF(A2, $D$2:$D$5)

这个公式的意思是：检查A2单元格的值，是否出现在区域$D$2:$D$5中。如果出现，返回1（或匹配到的次数）；如果不出现，返回0。

这就是我们实现“或”筛选的基石。区域$D$2:$D$5就是我们的“条件列表”。A2只要匹配其中任意一个，公式结果就大于0，即逻辑为真。

理解了这一点，我们就可以开始构建筛选体系了。

3. 环境准备：理解绝对引用与数组公式

在动手前，有两个基础概念必须牢固掌握，否则公式会错乱。

3.1 绝对引用 ($) 的重要性

在构建下拉填充的公式时，引用方式决定成败。

• $D$2:$D$5：绝对引用。无论公式复制到哪，条件区域始终锁定在D2:D5。
• A2：相对引用。当公式向下填充时，会自动变成A3,A4... 从而逐行检查。

在本文的所有公式中，条件列表区域务必使用绝对引用（如$E$2:$E$10），而待检查的单元格使用相对引用。

3.2 数组公式与动态数组

本文的公式分为两类：

1. 传统数组公式：适用于 Excel 2019 及更早版本。公式输入后，必须按Ctrl + Shift + Enter组合键结束，Excel会在公式两边自动加上大括号{}。这类公式通常与SUMPRODUCT、INDEX等函数配合，进行多条件判断和结果聚合。
2. 动态数组公式：适用于 Microsoft 365 和 Excel 2021。这是革命性的更新，一个公式就能返回多个结果，并自动“溢出”到下方的单元格。FILTER函数就是动态数组函数的代表。

本文将同时给出两种环境的解法，但会以更现代、更强大的动态数组公式（FILTER）作为主要讲解对象。

我们的示例数据如下：

目标1（或筛选）：筛选出“部门”为“销售部”或“技术部”的员工。目标2（反向筛选）：筛选出“部门”不是“销售部”且不是“技术部”的员工。

下面，我们进入实战。

4. 核心流程拆解：从单条件到多条件“或”筛选

让我们把复杂问题分解。首先实现“或”筛选。

4.1 第一步：构建逻辑判断列

我们在D2单元格输入以下公式，并向下填充：

=COUNTIF($B2, $F$2:$F$3) > 0

• $B2：相对引用，检查当前行的部门。
• $F$2:$F$3：绝对引用，这是我们的条件列表区域，假设我们在F2和F3分别输入了“销售部”和“技术部”。
• COUNTIF(...)：判断B2的值是否在{“销售部”， “技术部”}中。在则返回1，不在则返回0。
• > 0：将数值结果转化为TRUE/FALSE。1>0为TRUE，0>0为FALSE。

填充后，D列会显示一系列TRUE/FALSE，TRUE就代表该行满足“部门是销售部或技术部”的条件。

4.2 第二步：利用 FILTER 函数输出结果（动态数组公式）

这是最简洁的方法。在一个空白单元格（如H2）输入：

=FILTER(A2:C6, COUNTIF($B$2:$B$6, $F$2:$F$3)>0)

公式详解：

• A2:C6：这是我们的源数据区域。
• COUNTIF($B$2:$B$6, $F$2:$F$3)>0：这是筛选条件。
  • COUNTIF($B$2:$B$6, $F$2:$F$3)：这里发生了一个数组运算。$B$2:$B$6是一个5行1列的垂直数组，$F$2:$F$3是一个2行1列的垂直数组。Excel会进行“广播”计算，最终生成一个5行1列的中间数组。这个数组的每个元素，表示对应行的B列值在F2:F3中出现的次数。
  • 对于“张三”（销售部），在{销售部， 技术部}中出现1次，中间结果为1。
  • 对于“李四”（技术部），出现1次，结果为1。
  • 对于“王五”（市场部），出现0次，结果为0。
  • 以此类推。
• >0：将上述中间数组的每个元素与0比较，1>0为TRUE，0>0为FALSE。最终得到一个由TRUE/FALSE构成的逻辑数组：{TRUE; TRUE; FALSE; TRUE; TRUE}。
• FILTER函数根据这个逻辑数组，从A2:C6中筛选出对应为TRUE的行。

按下回车，H2:J5区域会自动“溢出”显示出筛选结果：张三、李四、赵六、孙七的数据。这一切只需要一个公式！

4.3 第三步：传统数组公式方案（兼容旧版）

如果你的Excel不支持动态数组，可以使用INDEX+SMALL+IF的经典组合，但这更复杂。更推荐使用SUMPRODUCT配合辅助列。 在辅助列D2输入并下拉：

=SUMPRODUCT(($B2=$F$2:$F$3)*1)

或者直接用：

=--(COUNTIF($B2, $F$2:$F$3)>0) // 双负号将TRUE/FALSE转为1/0

然后对D列进行筛选，筛选值为1的行即可。虽然多了一步，但逻辑清晰，兼容性好。

至此，“或”筛选已经完成。它的强大之处在于，你只需要在F2:F3区域里增删部门名，筛选结果就会实时、动态地更新，无需修改公式。

5. 反向筛选的完整实现：找出“不属于”任何条件的数据

反向筛选，即“非”筛选，是“或”筛选的逆操作。我们的目标是：找出那些在B列的值，完全没有出现在条件列表中的行。

基于之前的逻辑，这变得非常简单：COUNTIF(...)的结果如果等于0，就说明该行数据是“反向”的。

5.1 动态数组公式实现（FILTER）

在空白单元格输入：

=FILTER(A2:C6, COUNTIF($B$2:$B$6, $F$2:$F$3)=0)

与“或”筛选公式的唯一区别，就是把>0改成了=0。

• COUNTIF(...)=0：生成逻辑数组，只有那些在条件列表中一次都没出现的部门，才会是TRUE。
• 在我们的例子中，只有“王五”（市场部）不在{销售部， 技术部}中，所以逻辑数组为{FALSE; FALSE; TRUE; FALSE; FALSE}。
• FILTER函数据此只返回TRUE对应的那一行数据。

按下回车，结果区域将只显示王五的记录。

5.2 处理多列反向筛选（“且非”关系）

更复杂的需求来了：筛选出“部门不是销售部且销售额不大于1000”的记录。 这其实是两个反向条件的“与”关系。我们需要构建两个逻辑判断，然后相乘。

假设条件1：部门不等于“销售部”（条件列表在F2）。 条件2：销售额不大于1000（即小于等于1000，这是一个数值条件）。

公式如下：

=FILTER(A2:C6, (COUNTIF($B$2:$B$6, $F$2)=0) * ($C$2:$C$6<=1000))

公式详解：

• (COUNTIF($B$2:$B$6, $F$2)=0)：生成一个数组，部门不是“销售部”的为TRUE。
• ($C$2:$C$6<=1000)：生成另一个数组，销售额小于等于1000的为TRUE。
• 两个逻辑数组相乘（*）：在数组运算中，TRUE*TRUE=1，其他情况为0。只有两个条件同时为TRUE的行，结果才是1（被视作TRUE）。
• FILTER根据最终结果为1（TRUE）的行进行筛选。

这个公式会返回李四（技术部，800）和孙七（技术部，950）的数据。张三和赵六因为部门是销售部被排除，王五因为销售额1200>1000被排除。

6. 进阶技巧与常见问题排查

掌握了核心公式后，我们来看一些实战中必然会遇到的细节和坑。

6.1 条件列表包含空单元格或公式返回空值

如果条件区域$F$2:$F$10中有空单元格，COUNTIF在匹配时，会将空值也作为一个条件。这可能导致你意想不到的结果，比如匹配到数据源中的空单元格。解决方案：使用动态范围或清理数据源。可以使用OFFSET或TABLE，但更简单的方法是确保条件区域是紧凑无空的。或者，使用FILTER先清理条件列表：

=LET( criteriaList, FILTER($F$2:$F$100, $F$2:$F$100<>""), // 去除空值 FILTER(A2:C100, COUNTIF($B$2:$B$100, criteriaList)>0) )

（LET函数可定义中间变量，需 Microsoft 365 支持）

6.2 匹配文本时的大小写与通配符

COUNTIF默认不区分大小写。“Apple”和“apple”会被视为相同。如果需要区分，可以考虑使用EXACT函数结合数组公式，但这会复杂很多。对于通配符（*,?,~），如果条件本身包含这些字符，需要在criteria参数中将~放在它们前面进行转义，例如"~*"来匹配星号本身。

6.3 处理数字与文本混合列

当数据列中既有数字又有文本时，COUNTIF的行为是可靠的。但要注意，数字100和文本"100"在COUNTIF眼中是不同的。确保你的条件类型与数据列类型一致。如果不确定，可以使用TEXT函数或VALUE函数进行转换。

6.4 性能问题：大数据量下的优化

COUNTIF配合数组运算，在数据量极大（例如数十万行）时，计算可能会变慢，因为它是逐行进行数组比较。优化建议：

1. 缩小范围：尽量精确限定COUNTIF的range参数，不要引用整列（如B:B），而用实际范围（如$B$2:$B$10000）。
2. 使用辅助列：如果条件不常变化，可以将COUNTIF(...)>0或=0的计算结果放在一个辅助列中，然后直接基于这个逻辑列进行筛选或FILTER。这相当于把计算成本分摊到数据更新时，而不是每次筛选时。
3. 考虑 Power Query：对于极其复杂、频繁的筛选需求，使用 Power Query 进行数据清洗和转换是更专业、性能更好的选择。

6.5 常见错误与排查

7. 最佳实践与工程化建议

将“邪修”技巧用于实际工作流时，遵循以下建议能让你的表格更健壮、更易维护。

1. 命名区域，让公式更可读不要使用$F$2:$F$10这样的引用。选中条件区域，在左上角名称框输入“部门条件列表”，然后按回车。公式就可以写成：

   =FILTER(数据表, COUNTIF(数据表[部门], 部门条件列表)>0)

   清晰明了，即使表格结构变动，也只需更新名称定义，无需修改大量公式。

2. 将条件列表放在独立的工作表专门创建一个名为“Config”或“参数”的工作表，存放所有筛选条件列表。这样主数据表看起来更干净，条件管理也更集中。

3. 使用表格对象（Ctrl+T）将你的源数据转换为“表格”（快捷键Ctrl+T）。这样做的好处是：

   • 公式中可以使用结构化引用，如Table1[部门]，自动适应数据行的增减。
   • FILTER等动态数组公式引用表格列时，溢出范围也会自动调整。

4. 为反向筛选提供清晰的标签在输出结果旁边，用公式自动生成筛选条件的描述，避免他人（或未来的你）迷惑。

   ="筛选条件：部门不属于 " & TEXTJOIN(", ", TRUE, 部门条件列表)

5. 封装复杂逻辑如果同一个复杂的反向筛选逻辑需要在多个地方使用，考虑使用LAMBDA函数（Microsoft 365）将其定义为一个自定义函数。例如，定义一个叫FilterNotIn的函数，以后只需调用=FilterNotIn(数据区域, 判断列, 排除列表)即可。

8. 总结：何时该用，何时该换

用COUNTIF实现多条件“或”筛选和反向筛选，是一个巧妙、灵活且兼容性强的技巧。它特别适合以下场景：

• 条件列表动态变化：条件经常增删改，且来源可能是一个手工维护的区域。
• 条件数量较多：需要匹配的条件有十几个甚至几十个，手动勾选不现实。
• 需要嵌套在复杂公式中：作为中间逻辑判断的一部分，参与更复杂的计算。
• Excel版本较旧：在没有FILTER、XLOOKUP等新函数的环境下，它是实现动态“或”筛选的轻量级方案。

然而，它并非万能。在以下情况，可能有更好的选择：

• 极高性能要求：面对海量数据，优先考虑 Power Pivot 或 Power Query。
• 条件逻辑极其复杂：涉及多重嵌套的“与”、“或”、“非”组合，使用SUMPRODUCT或FILTER直接构建布尔表达式可能更直观。
• 需要返回匹配项的具体信息：例如，不仅要筛选，还要知道每条数据具体匹配了条件列表中的哪一项，这时XLOOKUP或INDEX/MATCH可能更合适。

技术的价值在于解决问题。COUNTIF的这次“邪修”之旅，核心不是记住几个公式，而是掌握一种思路：深入理解每个基础函数的核心输出（尤其是其数值/逻辑特性），并敢于将它们以非常规的方式组合，从而解决看似需要更高级工具才能处理的问题。这种“函数思维”的锻炼，远比死记硬背一百个函数语法更有价值。

下次当你在Excel中遇到棘手的多条件筛选时，不妨先想一想：COUNTIF能不能帮上忙？也许，一个看似简单的函数，就能撬动让你头疼许久的难题。