📌 阶段三 · 函数核心 · 第9讲 COUNTIF函数 · 约 40 分钟

给系统装上「点数器」：数清每个商品的出入库笔数，给商品档案做一次查重体检

学习目标

  • 分清 COUNT 与 COUNTIF 的分工，掌握条件计数的写法
  • 用 COUNTIF 统计每个商品的入库笔数与出库笔数
  • 用 COUNTIF 配合 IF 给商品档案编号查重
  • 把 COUNTIF 塞进条件格式和数据有效性，自动标红并禁止重复录入
  • 识别超 15 位长数字被误判重复的坑，会用 &"*" 化解

知识点讲解

COUNT：先学会最朴素的数数

COUNT 就是单纯地数数：统计一片区域里有多少个数字。它只认数字——空单元格、文本、逻辑值一律忽略。

语法：=COUNT(value1,value2,...)，第 1 个参数必填，后面还可以再挂其他区域，最多 255 个。

在进销存里它很实用：想数入库单一共记了多少笔，对数量列数一数就行。反过来它还能当体检工具：如果流水有 200 行，COUNT 却只数出 198，说明有格子是空的，或数字被存成了文本。

=COUNT(F:F)

对 F 列（数量列）计数：只数数字，数出来的就是有效记录条数，可以用来确认单据有没有漏录数量。

💡 提示:

  • COUNT 只统计数字，「看起来像数字」的文本型数字也不算
  • 如果 COUNT 的结果比行数少，先怀疑有空单元格或文本混入

COUNTIF：带上条件再数

COUNTIF 是「带条件地数数」：计算区域内满足单个条件的单元格个数。

语法：=COUNTIF(range,criteria)，第 1 参数是条件区域（在哪数），第 2 参数是条件（数什么样的）。

讲义的例子是在科目列里数「邮寄费」出现了几笔，条件直接引用单元格，改一下单元格内容就能改条件，非常灵活。放到进销存里，数 SP001 在入库单里出现几次，就是它今年一共入库了几笔。

=COUNTIF(E:E,H8)

在 E 列（科目划分）里数出与 H8 相同的科目有几笔——条件引用单元格，改 H8 就改条件。

=COUNTIF(入库单!C:C,A2)

在入库单 C 列（商品编号）里数 A2 这个编号出现的次数，即该商品的入库笔数。

💡 提示:

  • 条件优先引用单元格，别把文字敲死在公式里，一处修改全表联动
  • 区域可以选整列，公式更省事

条件不只是等于：区间、大于小于都能数

第 2 参数除了写具体值，还能写比较式，比如数出成绩大于等于 60 的人数：=COUNTIF(B2:H2,">=60")。比较符号和数字要整体放在英文双引号里。

进销存场景：数一数单笔入库数量满 100 的有几笔、金额超过 1000 的大单有几笔，写法完全一样。这个能力在盘点和异常检查时特别好用。

=COUNTIF(B2:H2,">=60")

统计区域内大于等于 60 的数字个数——条件表达式整体加英文双引号。

=COUNTIF(入库单!G:G,">=100")

统计入库单 G 列（数量）中单笔满 100 的记录数，用来发现大批量进货。

💡 提示:

  • 比较符号必须写在英文双引号里，写成 >=100 不带引号会直接报错

查重复：COUNTIF 的看家本领

一个值在区域里出现 1 次是正常、出现 2 次以上就是重复——把这句话写成公式就完成了查重。

讲义的例子是拿学生名单去体检名单里对：=COUNTIF(G:G,A2)，数出来 1 表示已体检、0 表示没体检，再套一层 IF 直接显示文字：=IF(COUNTIF(G:G,A2)=0,"未体检","已体检")。

进销存直接照搬：商品档案编号必须唯一，用 =IF(COUNTIF($A$2:$A$31,A2)>1,"重复","唯一") 下拉一列，所有重复编号当场现形。

=COUNTIF(G:G,A2)

在 G 列里找 A2：结果 1 表示存在，0 表示不存在。

=IF(COUNTIF($A$2:$A$31,A2)>1,"重复","唯一")

数 A2 在档案编号区出现的次数，超过 1 次就显示「重复」；区域加 $ 锁定，下拉时范围不动。

💡 提示:

  • 查重公式的计数区域必须用绝对引用，否则下拉时区域会跟着跑

长数字的坑：超过 15 位会被误判重复

Excel 对超过 15 位的数值只保留前 15 位有效数字，后面全部按 0 处理。所以两个前 15 位相同的长卡号，明明各只有 1 条，COUNTIF 却数出 2，判断成「重复」。

解决办法：给查找值后面连接一个 ，写成 =COUNTIF(A2:A3,A2&"")，强制把数值当文本来比对，长号码就能正确区分了。往下拖公式时记得给计数区域加绝对引用：=COUNTIF($A$8:$A$20,A8&"*")。

进销存里遇到超长条形码、商品序列号，同样用这招。

=COUNTIF(A2:A3,A2&"*")

A2&"*" 把数值强制转成文本再比对，绕开 15 位有效数字的限制。

=COUNTIF($A$8:$A$20,A8&"*")

带绝对引用的查重版：下拉检查整段长号码时，范围锁定不变。

💡 提示:

  • 15 位以内的普通编号不需要加 &"*"
  • 治本的办法还是把长编号列提前设成文本格式（第 2 讲）

COUNTIFS：一次数两个条件

Excel 2007 及以上版本还有个 COUNTIFS，对满足多个条件的单元格计数：=COUNTIFS(条件区域1,条件1,条件区域2,条件2,...)。只写一对条件时，效果和 COUNTIF 完全一样。

讲义例子：统计「一车间」的「邮寄费」笔数，两个条件各占一对参数。进销存照搬：统计 SP001 且由供应商 GYS003 供货的入库笔数，把两张条件牌一起打出去。

=COUNTIFS(D:D,D2,E:E,E2)

D 列满足车间条件、E 列同时满足科目条件的记录才被计数——条件成对出现。

=COUNTIFS(入库单!C:C,A2,入库单!F:F,B2)

统计商品编号等于 A2 且供应商编号等于 B2 的入库笔数。

💡 提示:

  • 所有条件区域的行数必须一致，条件区域与条件成对书写
  • 最多可以放 127 对条件

塞进条件格式和数据有效性里用

COUNTIF 单独用是统计，塞进别的功能里就成了「规则」。

条件格式：选中目标区域，点【开始-条件格式-新建规则】，规则类型选「使用公式确定要设置格式的单元格」，输入 =COUNTIF($G:$G,A1)=0——凡是在 G 列里找不到的单元格自动上色；查重复则是 =COUNTIF(E:E,E2&"*")>=2，出现 2 次及以上的长号码标红。

数据有效性（数据验证）：选中列，【数据-数据验证】，条件选「自定义」，输入 =COUNTIF(C:C,C1)<2——从录入源头禁止重复，输重复值直接弹窗拦截。

=COUNTIF($G:$G,A1)=0

条件格式用：A1 的值在 G 列不存在（计数为 0）时给单元格上色；公式以选区左上角单元格为基准书写。

=COUNTIF(E:E,E2&"*")>=2

条件格式用：长号码出现 2 次及以上就标红，&"*" 处理超 15 位问题。

=COUNTIF(C:C,C1)<2

数据有效性用：只允许出现 1 次，录入第 2 次时 Excel 拒绝并弹窗。

💡 提示:

  • 条件格式公式里的行号对应选区的第一行，别对着别的行写
  • 数据有效性里写 <2 或 =1 都可以，含义都是「只能出现一次」

实战演练

这一讲给进销存系统装「点数器」。任务有两件：一是给商品档案做查重体检——30 个商品编号绝不允许重号，否则后面所有 SUMIF、VLOOKUP 都会张冠李戴；二是在商品档案右侧加一个临时统计区，用 COUNTIF 数出每个商品至今入库、出库各多少笔，这些笔数以后会变成仪表盘上「最活跃商品」的依据。可以先打开右侧练习数据（源讲义工作簿）试试手感，再到自己一路搭建的进销存工作簿里正式建造。

  1. 统计区立骨架：数入库笔数 — 在商品档案 J1、K1、L1 依次输入表头「入库笔数」「出库笔数」「编号体检」。J2 输入 =COUNTIF(入库单!C:C,A2)，下拉到 J31——每个商品全年的入库流水笔数立刻出来。
  2. 再数出库笔数 — K2 输入 =COUNTIF(出库单!C:C,A2)，下拉到 K31。对照 J 列看看：哪些商品入得多出得少、哪些进出频繁，心里先有个数。
  3. 编号查重体检 — L2 输入 =IF(COUNTIF($A$2:$A$31,A2)>1,"重复","唯一")，下拉 30 行。全部显示「唯一」才合格；出现「重复」就回 A 列找出重复行改正。
  4. 条件格式：重复编号自动标红 — 选中商品档案 A2:A31，点【开始-条件格式-新建规则】，选「使用公式确定要设置格式的单元格」，输入 =COUNTIF($A$2:$A$31,A2)>1，设置红色填充。以后手工改编号时一旦改重，颜色立刻报警。
  5. 数据有效性：从源头禁止重复 — 保持选中 A2:A31，点【数据-数据验证】，允许条件选「自定义」，公式输入 =COUNTIF($A$2:$A$31,A2)<2。然后在 A 列试着录入一个已存在的编号，验证 Excel 是否弹出拦截提示，最后取消。
  6. 加练一手 COUNTIFS — M1 输入表头「12月入库笔数」，M2 输入 =COUNTIFS(入库单!C:C,A2,入库单!B:B,">=2024-12-1")，下拉——同时按商品和日期两个条件计数，感受多条件点数器。

在线练习

下面的表格可以直接编辑,照着演练步骤在本页练习;需要菜单操作的步骤请下载配套练习文件,在自己电脑的 Excel 中完成。

表格加载中…

验收清单

逐项自查,全部通过即完成本讲:

  • ☐ J、K 两列下拉后 30 行都有数字，且没有错误值
  • ☐ L 列全部显示「唯一」，如有「重复」已回档案修正
  • ☐ 选中 A2:A31 的条件格式生效：手工改重一个编号会立即标红
  • ☐ 数据有效性拦截测试通过：录入重复编号时 Excel 弹窗拒绝
  • ☐ 能说出条件表达式为什么要加英文双引号，以及长号码查重为什么要加 &"*"