第1个回答 2013-10-19
秋尽冬至，又到一年年终时。在工作中，数据的运算量和汇总的操作一下比平时多了不少。众所周知，在Excel中可以对数据进行各种不同的运算和汇总，今天我向大家介绍与条件相关的函数三兄弟，分别是“COUNTIF”、“SUMIF”和“IF”函数。他们有一个共同的特点——都姓字符“IF”。 大兄弟：COUNTIF函数（计数求和） COUNT函数，顾名思义是用来计数的，统计所选择区域的数值型单元格个数。COUNTIF是COUNT函数的引伸与拓展，在计数时加上先前条件，只有符合计数的条件才进行统计计算。比如，从员工信息表中，计算出有多少人的年龄大于35岁。 下面我们来看一个典型的分类计数汇总的例子。这里有一张销售流水记录表，每名销售人员累计做了多少“销售订单个数”呢？ 大兄弟COUNTIF正常工作需要两个参数——条件区域（本例为左侧表中“销售人员”一列）和计数条件（本例为右侧表中的人员姓名）。要计算第一位销售人员的“订单数”，很简单，输入函数公式 “=COUNTIF($C$2:$C$16,E2)”即可（见图1）。 二兄弟：SUMIF函数（条件求和） 此函数可根据指定条件对若干单元格、区域或引用求和。 sumif函数语法是：SUMIF(range，criteria，sum_range) sumif函数的参数如下： 第一个参数：Range为条件区域，用于条件判断的单元格区域。 第二个参数：Criteria是求和条件，由数字、逻辑表达式等组成的判定条件。 第三个参数：Sum_range 为实际求和区域，需要求和的单元格、区域或引用。 当省略第三个参数时，则条件区域就是实际求和区域。 criteria 参数中使用通配符（包括问号 (?) 和星号 (*)）。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符 (~)。 实例1：求报表中各栏目的总流量 实例：求报表中各栏目的总流量 结果如下图所示。 选中F2单元格，输入公式：=SUMIF(B2:B19,E2,C2:C19)，输入公式完成后，按键盘上ctrl+shift+enter组合键（必须按此ctrl+shift+enter组合键数组公式才能得到正确结果），即可统计出办公软件栏目的总流量。 以此类推，选中F3单元格，输入公式：=SUMIF(B2:B19,E3,C2:C19)，输入公式完成后，按键盘上ctrl+shift+enter组合键，可以求得网站运营栏目的总流量。 选中F4单元格，输入公式：=SUMIF(B2:B19,E4,C2:C19)，输入公式完成后，按键盘上ctrl+shift+enter组合键，可以求得电脑医院栏目的总流量。 选中F5单元格，输入公式：=SUMIF(B2:B19,E5,C2:C19)，输入公式完成后，按键盘上ctrl+shift+enter组合键，可以求得工具软件网络安全栏目的总流量。 选中F6单元格，输入公式：=SUMIF(B2:B19,E6,C2:C19)，输入公式完成后，按键盘上ctrl+shift+enter组合键，可以求得栏目的总流量 实例2，计算“金额”在1元以上的数据总和、按照人员或产品分类计算数据总和等等。它有3个参数，分别是条件区域、判断条件、实际的求和区域（如果它与“条件区域”是一个区域，就可省略）。 在上例中，计算每位“销售人员”的订单总金额，就要使用SUMIF函数来协助了。如果要计算每个人的销售订单总金额，把左侧表的“销售人员”一列当作“条件区域”，把右侧表的每个名单当作求和“条件”，把左侧表的每笔“订单金额”当作“实际求和区域”，在G2单元格中输入数据计算公式 “=SUMIF($C$2:$C$16,E2,$B$2:$B$16)”（见图2），第1名销售人员的“订单总额”就瞬间产生了。 小提示： 在本例的COUNTIF函数和SUMIF函数中，由于“销售人员”区域与“订单总额”区域都是固定的，所以在函数中引用这两列地址时，要使用“绝对地址”，也就是在地址前添加“$”符号。 三兄弟：IF函数 逻辑高手IF函数是一个条件函数，它可以通过设置的条件进行逻辑判断。 实例1、在刚才的数据汇总表中再添加一列“销售奖金”数据，“销售奖金”发放的方法是：如果某个人的订单总额大于￥1500,000，那么“奖金”数用总额×5%，否则“奖金”就用总额×3%。所以，第1位销售人员“销售奖金”的计算公式应为“=IF(G2>1500000,G2*0.05,G2*0.03)”，如图3所示。 结合刚才的知识，推测一下这个公式中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列中的重复数据删除。在如下图4所示的案例中，使用了公式“=IF(COUNTIF($C$1:C1,C1)>1,”1”,”0”)”为重复数据的后面添加了标识“1”，为不重复数的后面添加了标识“0”。在公式中，COUNTIF是条件计数，可利用它统计出从这列的第1个数据起，某个数出现了几次，再用IF函数判断，若出现2次以上就添加“1”，若出现1次则就添加“0”。最后用Excel中的自动筛选功能，将所有标识为“1”的数据行删除，就能把重复数据统统清理掉了。 与“IF”相关的函数三兄弟，不但为运算添加了“条件”，而且为我们打通了很多解决问题的道路，只要肯用心发掘，一定还能找到更多的问题解决之道。比如： 实例2、根据出勤和加班情况发放补贴，假设考勤分为三种情况，分别为“有加班”、“ div:eq(0) > div:eq(3) > table:eq(2) > tbody:eq(0) > tr:eq(0) > td:eq(0) > div:eq(0) > #content:eq(0) > br:eq(0)" offset="25">全勤”、“有缺勤”，其中上班日超过20天为“有加班”，20天的为“全勤”，剩下的为“有缺勤”。表格如图1所示，需要在“出勤类型”一列中显示出出勤状况。 将 div:eq(0) > div:eq(3) > table:eq(2) > tbody:eq(0) > tr:eq(0) > td:eq(0) > div:eq(0) > #content:eq(0) > br:eq(3)" offset="3">鼠标指针定位到E4单元格，输入公式“=IF(D4>20,"有加班",IF(D4=20,"全勤","有缺勤"))”(图2,不包括外侧引号)，按下回车键后，相应单元格中即显示出对应的“考勤类型”。然后再在每条记录的对应单元格中输入相应的公式即可。第一次的工作量可能会大些，但以后需要再次使用时，只要更改考勤数据，“考勤类型”就出来了，对于一些固定类型的表格，如教师判定学生的成绩级别，这种方法还是可以带来很大方便的。 小提示: 1.公司中所用的符均为英文半角，所有的字符、符号之间没有任何空格。 2.IF最多只支持七层嵌套，不过对付日常应用应该足够了。=if(a2<60,"不合格",if(a2<80,"合格",if(a2<90,"良好","优秀")))。 3.也可以结合其他函数使用，=if(a2>average($a$2:$a$9),"合格","不合格") 4.如果只有两种情况，只需要使用“插入”→“函数”命令，然后选择“IF”函数，最后输入相应的判别条件及判别结果就可以了。当然和文中一样直接输入IF函数也是可以的