Excel满⾜特定条件的单元格进⾏求和或汇总
Excel满⾜特定条件的单元格进⾏求和或汇总
如果要计算单元格区域中某个⽂本串或数字出现的次数，则可使⽤COUNTIF ⼯作表函数。如果要根据单元格区域中的某⼀⽂本串或数字求和，则可使⽤SUMIF ⼯作表函数。关于SUMIF函数在数学与三⾓函数中以做了较为详细的介绍。这⾥重点介绍COUNTIF的应⽤。
COUNTIF可以⽤来计算给定区域内满⾜特定条件的单元格的数⽬。⽐如在成绩表中计算每位学⽣取得优秀成绩的课程数。在⼯资表中求出所有基本⼯资在2000元以上的员⼯数。
语法形式为COUNTIF(range,criteria)。其中Range为需要计算其中满⾜条件的单元格数⽬的单元格区域。Criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或⽂本。例如，条件可以表⽰为32、"32"、">32"、"apples"。
1、成绩表
这⾥仍以上述成绩表的例⼦说明⼀些应⽤⽅法。我们需要计算的是：每位学⽣取得优秀成绩的课程数。规则为成绩⼤于90分记做优秀。如图8所⽰
根据这⼀规则，我们在优秀门数中写公式（以单元格B13为例）：
=COUNTIF(B4:B10,">90")
语法解释为，计算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
countif函数多条件求和
如果订单总额⼩于50000则奖⾦为10%；如果订单总额⼤于等于50000，则奖⾦为15%。
图10 销售⼈员汇总表
图8
SUMIF
⽤途：根据指定条件对若⼲单元格、区域或引⽤求和。
语法：SUMIF(range，criteria，sum_range)
参数：Range为⽤于条件判断的单元格区域，Criteria是由数字、逻辑表达式等组成的判定条件，Sum_range为需要求和的单元格、区域或引⽤。
实例：某单位统计⼯资报表中职称为“中级”的员⼯⼯资总额。假设⼯资总额存放在⼯作表的F列，员⼯职称存放在⼯作表B列。则公式为“=SUMIF(B1:B1000，"中级"，F1:F1000)”，其中“B1:B1000”为提供逻辑判断依据的单元格区域，"中级"为判断条件，就是仅仅统计B1:B1000区域中职称为“中级”的单元格，F1:F1000为实际求和的单元格区域。
例如：sumif(A1:A20,"a",B1:B20)
意思就是：在A1到A20这个区域中，凡是"a"的就把它的数量求和。如果是不同⼀个⼯作表，只要在区域的前⾯加上⼯作表的名称就⾏了，如sumif(sheet1!A1:A20,"a",sheet1!B1:B20)
但要注意的⼀点就是在判断条件时，条件⼀定要与字段名相同，就算差⼀个点或⼀个空格，公式都⽆法判断，所以条件与字段名⼀定要⼀致
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>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列中的重复数据删除。使⽤了公式“=IF(COUNTIF($C$1:C1,C1)>1,”1”,”0”)”为重复数据的后⾯添加了标识“1”，为不重复数的后⾯添加了标识“0”。在公式中，COUNTIF是条件计数，可利⽤它统计出从这列的第1个数据起，某个数出现了⼏次，再⽤IF函数判断，若出现2次以上就添加“1”，若出现1次则就添加“0”。最后⽤Excel中的⾃动筛选功能，将所有标识为“1”的数据⾏删除，就能把重复数据统统清理掉了。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。