[整理版]Excel满足特定条件的单元格进行求和或汇总

1、整理版Excel满足特定条件的单元格进行求和或汇总Excel满足特定条件的单元格进行求和或汇总如果要计算单元格区域中某个文本串或数字出现的次数，则可使用COUNTIF 工作表函数。如果要根据单元格区域中的某一文本串或数字求和，则可使用SUMIF 工作表函数。关于SUMIF函数在数学与三角函数中以做了较为详细的介绍。这里重 点介绍COUXTIF的应用。COUNTIF可以用来计算给定区域内满足特定条件的单元格的数目。比如在成绩 表中计算每位学生取得优秀成绩的课程数。在工资表中求出所有基本工资在2000 元以上的员工数。语法形式为COUXTIF(range, criteria)。其中Range为需要

2、计算其中满足条件 的单元格数目的单元格区域。Criteria确定哪些单元格将被计算在内的条件，其 形式可以为数字、表达式或文本。例如，条件可以表示为32、32、32、 apples o1、成绩表这里仍以上述成绩表的例子说明一些应用方法。我们需要计算的是:每位学生 取得优秀成绩的课程数。规则为成绩大于90分记做优秀。如图8所示根据这一规则，我们在优秀门数中写公式(以单元格B13为例):二COUNTIF(B4:B10,”90)语法解释为，计算B4到B10这个范围，即jarry的各科成绩中有多少个数值 大于90的单元格。在优秀门数栏中可以看到jarry的优秀门数为两门。其他人也可以依次看到。2、销售

3、业绩表销售业绩表可能是综合运用IF、SUMIF、COUXTIF非常典型的示例。比如，可 能希望计算销售人员的订单数，然后汇总每个销售人员的销售额，并且根据总发货 量决定每次销售应获得的奖金。原始数据表如图9所示（原始数据是以流水单形式列出的，即按订单号排列）AC1江单号订单金额销售人员220010401 000. 00JARRY*g20010402.500.00HELEN420010403«. >'j ' re «25, 000. 00MICHAEL5200104044, 200. 00JARRY6:20010405丸 500.00ANNIE72001

4、04062, 500. 00.AN-NIE82001040715, 000. 001ARRY92001040895,000. 00MICHAEL10200104091, 000. 00HELEN112001041050,000. 00HELEN1220010411OSmoo 200. 00ANNIE14销售总额212, 4：80-. 00图9原始数据表按销售人员汇总表如图10所示如图10所示的表完全是利用函数计算的方法自动汇总的数据。首先建立一个 按照销售人员汇总的表单样式，如图所示。然后分别评算订单数、订单总额、销售 奖金。（1）订单数一用COUXTIF计算销售人员

5、的订单数。以销售人员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$

6、13)中相应量的和。这个相应量的和就是销售人员ANNIE的订单总额。(3)销售奖金一用IF根据订单总额决定每次销售应获得的奖金。假定公司的销售奖金规则为当订单总额超过5万元时，奖励幅度为百分之十五，否则为百分之十。根据这一规则仍以销售人员ANNIE为例说明。公式为：=IF(C17<50000, 10%, 15%)*C17如果订单总额小于50000则奖金为10%;如果订单总额大于等于50000,则奖 金为15%。B17 -I =1 =COUNTIF($C$2:$C$13, A17)ABCD销售人员,工里救订单总额销售奖金ANNIE |厂10,200. 00h 020.00-JARRY426

7、, 780. 002, 678.00HELEN355, 500. 008, 325.00MICHAEL2120,000. 00181000.00订单总数12212, 480. 0030, 023. 00WUMIF (SC$2:$C3L3, AIT,二邛(Cl 7 <50000, 10%, 15%)*C17图10销售人员汇总表B13二j= "UUNnF(B4:B10, 90")ABI谈"|blEIfGHjarryMikeHcnyAnnieJackyAndySmile数学95567565100S878语文75608575959285英梧6554901009S90

8、80物理100557040907065化学86407850883979端78598070889080)历史90558675859585,序均分84548168928679) 综合评定合格.不合格合格合格优秀合格合格l!优秀门数12001320图8SUMIF用途:根据指定条件对若干单元格、区域或引用求和。ini'4： SUMIF (range, criteria, sum_range)参数:Range为用于条件判断的单元格区域，Criteria是由数字、逻辑表达式 等组成的判定条件，Sum_range为需要求和的单元格、区域或引用。实例:某单位统计工资报表中职称为“中级”的员工工资总额。

9、假设工资总额 存放在工作表的F列，员工职称存放在工作表B歹人则公式为“二SUMIF(B1:B1OOO, 中级，F1:F1OOO)W ,其中 “B1:B1OOO” 为提供逻辑判断依 据的单元格区域，中级为判断条件，就是仅仅统计BLBIOOO区域中职称为“中 级”的单元格，F1:F1OOO为实际求和的单元格区域。例如：sumif (Al:A20,Bl:B20)意思就是:在Al到A20这个区域中，凡是a的就把它的数量求和。如果是不同一个工作表，只要在区域的前面加上工作表的名称就行了，如sumif (sheetl! A1: A20, a，sheetl !B1 :B20)但要注意的一点就是在判断条件 时

10、，条件一定要与字段名相同，就算差一个点或一个空格，公式都无法判断，所以 条件与字段名一定要一致COUNTIF函数（计数求和）COUNT函数，顾名思义是用来计数的，统计所选择区域的数值型单元格个数。 COUNTIF是COUNT函数的引伸与拓展，在计数时加上先前条件，只有符合计数的条 件才进行统计计算。比如，从员工信息表中，计算出有多少人的年龄大于35岁。 下面我们来看一个典型的分类计数汇总的例子。这里有一张销售流水记录表，每名 销售人员累计做了多少“销售订单个数”呢，COUNTIF正常工作需要两个参数一一条件区域（本例为左侧表中“销售人员” 一 列）和计数条件（本例为右侧表中的人员姓名）。要计算

11、第一位销售人员的“订单 数”，考试，大提示很简单,输入函数公式“二COUNTIF（$C$2:$C$16,E2）”即可。二:SUMIF函数（条件求和）SUM函数的作用是对数据求和，而SUMIF对它进行了引伸和拓展，比如计算 “金额”在1元以上的数据总和、按照人员或产品分类计算数据总和等等。它有3 个参数，分别是条件区域、判断条件、实际的求和区域（如果它与“条件区域”是 一个区域，就可省略）。在上例中，计算每位“销售人员”的订单总金额，就要使用SUMIF函数来协助 了。如果要计算每个人的销售订单总金额，把左侧表的“销售人员” 一列当作“条 件区域”，把右侧表的每个名单当作求和“条件”，把左侧表的每

12、笔“订单金额”当作“实际求和区 域”，在G2单元格中输入数据计算公式“二SUMIF（$C$2:$C$16,E2,$B$2:$B$16）”，第1名销售人员的“订单总额”就瞬间 产生了。小提示:在本例的COUXTIF函数和SUMIF函数中，由于“销售人员”区域与“订单总 额”区域都是固定的，所以在函数中引用这两列地址时，要使用“绝对地址”，也 就是在地址前添加“$”符号。三:IF函数逻辑高手IF函数是一个条件函数，它可以通过设置的条件进行逻辑判断。如 果在刚才的数据汇总表中再添加一列“销售奖金”数据，“销售奖金”发放的方法 是：如果某个人的订单总额大于，1500, 000,那么“奖金”数用总额X5

13、%,否则“奖 金”就用总额X3%。所以，第1位销售人员“销售奖金”的计算公式应为rt=IF(G2> 1500000, G2*0. 05, G2*0. 03) ”。结合刚才的知识，推测一下这个公式中3个参数的涵义吧。"G2>1500000”是 IF函数的判断条件，“G2*0. 05”是条件成立的操作，"G2*0. 03”是条件不成立 的操作。点睛：*数据的分类汇总是Excel最常见的应用。分类汇总的方法很多，包括使用函 数和公式。设置的条件可看作是分类的依据，用“COUNTIF函数”进行分类计数汇 总,用“SUMIF函数”进行分类求和汇总，非常方便。*IF函数的作用是根据判断条件的真假，自动进行分支操作。比如根据身份证 号码的奇偶来判断性别，根据绩效数据来填写成绩等等。在实际应用时，一个非常 实用的应用是:把IF函数的“真”或“假”参数嵌套成另一个IF函数，实现多种 分支操作。如2个IF函数的嵌套可写成“二IF(条件，真，IF(条件，真，假)”， 这样一来就可以实现3个分支判断了(在Excel中，最多是7层函数嵌套)。*重复数据的筛选问题常常让我们感到非常棘手。COUNTIF函数除了可以实现分 类计数汇总外，它和IF函数配合，还可以实现对重复数据的标识与筛选，从而将 1列中的重复数据删除。