量化公式Excel满足特定条件的单元格进行求和或汇总

Excel满足特定条件的单元格进行求和或汇总
如果要计算单元格区域中某个文本串或数字出现的次数，则可使用 COUNTIF 工作表
函数。如果要根据单元格区域中的某一文本串或数字求和，则可使用 SUMIF 工作表函数。
关于SUMIF函数在数学与三角函数中以做了较为详细的介绍。这里重点介绍COUNTIF的
应用。
COUNTIF可以用来计算给定区域内满足特定条件的单元格的数目。比如在成绩表中计< br>算每位学生取得优秀成绩的课程数。在工资表中求出所有基本工资在2000元以上的员工数。
语法形式为COUNTIF(range,criteria)。其中Range为需要计算其中满足条件的单元 格数
目的单元格区域。Criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、表达
式或文本。例如，条件可以表示为 32、、、。
1、成绩表
这里仍以上述成绩表 的例子说明一些应用方法。我们需要计算的是：每位学生取得优秀
成绩的课程数。规则为成绩大于90分 记做优秀。如图8所示
根据这一规则，我们在优秀门数中写公式（以单元格B13为例）：
=COUNTIF(B4:B10,
语法解释为，计算B4到B10这个范围，即jarry的各科成 绩中有多少个数值大于90的
单元格。
在优秀门数栏中可以看到jarry的优秀门数为两门。其他人也可以依次看到。
2、 销售业绩表
销售业绩表可能是综合运用IF、SUMIF、COUNTIF非常典型的示例。比如，可 能希望
计算销售人员的订单数，然后汇总每个销售人员的销售额，并且根据总发货量决定每次销售
应获得的奖金。
原始数据表如图9所示（原始数据是以流水单形式列出的，即按订单号排列）

图9 原始数据表
按销售人员汇总表如图10所示 如图10所示的表完全是利用函数计算的方法自动汇总的数据。首先建立一个按照销售
人员汇总的表 单样式，如图所示。然后分别计算订单数、订单总额、销售奖金。
（1） 订单数 --用COUNTIF计算销售人员的订单数。
以销售人员ANNIE的订单数公式为例。公式：
=COUNTIF($$C$$2:$$C$$13,A17)
语法解释为计算单元格A17(即销售 人员ANNIE)在销售人员清单$$C$$2:$$C$$13的范围
内（即图9所示的原始数据表）出现的次 数。
这个出现的次数即可认为是该销售人员ANNIE的订单数。
（2） 订单总额-- 用SUMIF汇总每个销售人员的销售额。
以销售人员ANNIE的订单总额公式为例。公式：
=SUMIF($$C$$2:$$C$$13,A17,$$B$$2:$$B$$13)
此公式在销售人员清单$$C$$2:$$C$$13中检查单元格A17 中的文本（即销售人员
ANNIE），然后计算订单金额列（$$B$$2:$$B$$13）中相应量的和。
这个相应量的和就是销售人员ANNIE的订单总额。
（3） 销售奖金-- 用IF根据订单总额决定每次销售应获得的奖金。
假定公司的销售奖金规则为当订单总额超过5万元时 ，奖励幅度为百分之十五，否则为
百分之十。根据这一规则仍以销售人员ANNIE为例说明。公式为：
=IF(C17<50000,10%,15%)*C17
如果订单总额小于 50000则奖金为 10%；如果订单总额大于等于 50000，则奖金为
15%。

图10 销售人员汇总表


图8
SUMIF
用途：根据指定条件对若干单元格、区域或引用求和。
语法：SUMIF(range，criteria，sum_range)

参 数：Range为用于条件判断的单元格区域，Criteria是由数字、逻辑表达式等组成的
判定条 件，Sum_range为需要求和的单元格、区域或引用。
实例：某单位统计工资报表中职称为“中 级”的员工工资总额。假设工资总额存放在工作
表的F列，员工职称存放在工作表B列。则公式为“=S UMIF(B1:B1000，中级，F1:F1000)”，
其中“B1:B1000”为提供逻辑判 断依据的单元格区域，中级为判断条件，就是仅仅统计
B1:B1000区域中职称为“中级”的单元格 ，F1:F1000为实际求和的单元格区域。
例如：sumif(A1:A20,
意思就是 ：在A1到A20这个区域中，凡是的就把它的数量求和。如果是不同一个
工作表，只要在区域的前面加 上工作表的名称就行了，如
sumif(sheet1!A1:A20,
但要注意的一点就是在 判断条件时，条件一定要与字段名相同，就算差一个点或一个空格，
公式都无法判断，所以条件与字段名 一定要一致
COUNTIF函数（计数求和）
COUNT函数，顾名思义是用来计数的，统 计所选择区域的数值型单元格个数。COUNTIF
是COUNT函数的引伸与拓展，在计数时加上先前 条件，只有符合计数的条件才进行统计计
算。比如，从员工信息表中，计算出有多少人的年龄大于35岁 。下面我们来看一个典型的
分类计数汇总的例子。这里有一张销售流水记录表，每名销售人员累计做了多 少“销售订单
个数”呢？
COUNTIF正常工作需要两个参数——条件区域（本例为左侧表 中“销售人员”一列）和
计数条件（本例为右侧表中的人员姓名）。要计算第一位销售人员的“订单数” ，考试,大提示
很简单，输入函数公式 “=COUNTIF($$C$$2:$$C$$16,E2)”即可。
二：SUMIF函数（条件求和）
SUM函数的作用是对数据求和，而SUMIF对它进行了 引伸和拓展，比如计算“金额”
在1元以上的数据总和、按照人员或产品分类计算数据总和等等。它有3 个参数，分别是条
件区域、判断条件、实际的求和区域（如果它与“条件区域”是一个区域，就可省略） 。
在上例中，计算每位“销售人员”的订单总金额，就要使用SUMIF函数来协助了。如果
要计算每个人的销售订单总金额，把左侧表的“销售人员”一列当作“条件区域”，把右侧表的
每个名单 当作求和“条件”，把左侧表的每笔“订单金额”当作“实际求和区域”，在G2单元格
中输入数据计算 公式 “=SUMIF($$C$$2:$$C$$16,E2,$$B$$2:$$B$$16)”，第1名销售人员的“订单总
额”就瞬间产生了。
小提示：
在本例的COUNTIF函数和SUMIF函数中， 由于“销售人员”区域与“订单总额”区域都
是固定的，所以在函数中引用这两列地址时，要使用“绝对 地址”，也就是在地址前添加“$$”
符号。三：IF函数
逻辑高手IF函数是一个条件函数， 它可以通过设置的条件进行逻辑判断。如果在刚才
的数据汇总表中再添加一列“销售奖金”数据，“销售 奖金”发放的方法是：如果某个人的订单
总额大于￥1500,000，那么“奖金”数用总额×5%， 否则“奖金”就用总额×3%。所以，第1位
销售人员“销售奖金”的计算公式应为“=IF(G2>1 500000,G2*0.05,G2*0.03)”。
结合刚才的知识，推测一下这个公式中3个参 数的涵义吧。“G2>1500000”是IF函数的
判断条件，“G2*0.05”是条件成立的操作 ，“G2*0.03”是条件不成立的操作。
点睛：
*数据的分类汇总是Excel最常见 的应用。分类汇总的方法很多，包括使用函数和公式。
设置的条件可看作是分类的依据，用“COUNT IF函数”进行分类计数汇总，用“SUMIF函数”
进行分类求和汇总，非常方便。
*IF 函数的作用是根据判断条件的真假，自动进行分支操作。比如根据身份证号码的奇
偶来判断性别，根据绩 效数据来填写成绩等等。在实际应用时，一个非常实用的应用是：把
IF函数的“真”或“假”参数嵌套 成另一个IF函数，实现多种分支操作。如2个IF函数的嵌套
可写成“=IF（条件，真，IF（条件 ，真，假））”，这样一来就可以实现3个分支判断了（在
Excel中，最多是7层函数嵌套）。 < br>*重复数据的筛选问题常常让我们感到非常棘手。COUNTIF函数除了可以实现分类计
数汇总 外，它和IF函数配合，还可以实现对重复数据的标识与筛选，从而将1列中的重复
数据删除。使用了公 式“=IF(COUNTIF($$C$$1:C1,C1)>1,”1”,”0”)”为重复数据的后面添加了标< br>识“1”，为不重复数的后面添加了标识“0”。在公式中，COUNTIF是条件计数，可利用它统计出从这列的第1个数据起，某个数出现了几次，再用IF函数判断，若出现2次以上就添
加“1” ，若出现1次则就添加“0”。最后用Excel中的自动筛选功能，将所有标识为“1”的数据
行删除 ，就能把重复数据统统清理掉了。