excel sumif函数作为Excel2003中一个条件求和函数,在实际工作中发挥着强大的作用,虽然在2007以后被SUMIFS所取代,但它依旧是一个EXCEL函数的经典。本系列将详细介绍excel sumif函数的从入门、初级、进阶到高级使用方法以及SUMIF在隔列求和和模糊求和实现按指定条件求平均值中的应用如下所示: 条件求和函数SUMIF 假如A1:A36单元格存放某班学生的考试成绩,若要计算及格学生的平均分,可以使用公式“=SUMIF(A1:A36,″>=60″,A1:A36)/COUNTIF(A1:A36,″>=60″)。公式中的“=SUMIF(A1:A36,″>=60″,A1:A36)”计算及格学生的总分,式中的“A1:A36”为提供逻辑判断依据的单元格引用,“>=60”为判断条件,不符合条件的数据不参与求和,A1:A36则是逻辑判断和求和的对象。公式中的COUNTIF(A1:A36,″>=60″)用来统计及格学生的人数。 实例:求报表中各栏目的总流量 选中F2单元格,输入公式:=SUMIF(B2:B19,E2,C2:C19),输入公式完成后,按键盘上ctrl+shift+enter组合键(必须按此ctrl+shift+enter组合键数组公式才能得到正确结果),即可统计出办公软件栏目的总流量。 实例:求销售报表产品名中有"炉"字的总金额 选中G6单元格,输入公式:=SUMIF(B3:B9,"*炉",D3:D9),输入公式完成后,按键盘上ctrl+shift+enter组合键(必须按此ctrl+shift+enter组合键数组公式才能得到正确结果),即可求出销售报表产品名中有"炉"字的总金额。 实例:统计计算机和微波炉的销售总额。 选中G6单元格,输入公式:=SUM(SUMIF(B3:B9,{"计算机","微波炉"},D3:D9)),公式输入完成后,按键盘上ctrl+shift+enter组合键(必须按此ctrl+shift+enter组合键数组公式才能得到正确结果),即可求出计算机和微波炉的销售总额。 实例:计算6月份总销售额 选中G6单元格,输入公式:=SUMPRODUCT(C3:C9,D3:D9),即可求出所有产品的总销售额。 SUMIF作为Excel2003中一个条件求和函数,在实际工作中发挥着强大的作用,虽然在2007以后被SUMIFS所取代,但它依旧是一个EXCEL函数的经典。特别是高级用法,依旧适用于后面的版本。 SUM是求和,IF是如果。如果什么。。就求和,其实就是按条件求和。它的结构如下: SUMIF(条件判断区域,条件,求和区域) 说明: 1 条件判断区域。即然是条件求和,一是要有条件,二是要有判断的区域,SUMIF的第二个参数就是求和的条件,第一个参数呢就是拿条件来这个区域进行对比的区域。第一个参数必须是单元格引用。 2、条件。按条件求和就得有条件,该条件可以是字符串("ABC",可以用大于等对比符号连接起来的条件表达式(">100"),也可以使用通配符来表示匹配求和("AB*C")。 3、求和区域。求和函数可以没有求和的区域算什么求和函数,该参数为单元格区域引用。如C2:C100。 4、如果求和区域和判断区域是一个区域该怎么办呢?如在C2:C100中计算大于100的数字之和。在这种情况下SUMIF函数由三个参数变换为两个参数。即: SUMIF(求和区域,条件) 例1;如下图所示。要求根据左表中的商家明细表,生成右侧的汇总表,汇总出商家的总进货量和总金额。 公式: =SUMIF($A$2:$A$8,$F3,C$2:C$8) 公式说明: $A$2:$A$8是判断区域,拿F3的商家名称在这个区域进行判断是否相同,如果上同,就把C2:C8区域的金额进行求和。 如果只按C列进判断求和呢?比如,计算C列金额大于200的金额之和。公式可以变换为: SUMIF(C$2:C$8,">200") 关于入门级的SUMIF函数用法就介绍到这里了。 SUMIF函数的使用方法(技巧篇)_隔列求和 我们在学习了入门篇后,在常用技巧篇中将学习SUMIF常用的一些技巧。 谈到隔列求和,可能就会想到用数组公式,其实只需要用SUMIF函数就可以轻松实现。 例:计算费用表中的计划和实际合计数。 公式;=SUMIF(B$2:G$2,B$2,B3:G3) 提示;SUMIF函数不只是左右示和,还可以上下求和。 在计算求和时,有时需要按模糊的条件进行求和。例; 在计算产品名称中包含A的所有C列进价之和。公式可以这样写 =SUMIF(A1;A100,"*A*",C1:C100) 公式说明:在公式里*是通配符,表示有0个或任意多个字符。“*A*”表示条件为A前面有任意多个字符,A后面有任意多个字符。 另外,“?”表示单个占位符。"?A"表示A前面必须有一个任意字符.而且以A结尾. SUMIF函数实现按指定条件求平均值 Excel 2003中的条件求和SUMIF函数非常实用,例如在年级段总成绩表中计算某科教师所教的所有班级成绩的平均分(如5到8班化学老师的平均分),就可以利用如下方法实现: SUMIF函数是按给定条件对指定单元格进行求和的函数。其语法格式是:SUMIF(range,criteria,sum_range),range是要根据条件进行计算的单元格区域,每个区域中的单元格都必须是数字和名称、数组和包含数字的引用,空值和文本值将被忽略。criteria 是指对range指定的区域实行什么条件,其形式可以为数字、表达式或文本。如条件可以表示为 32、"32"、">32" 或 "apples";sum_range是要进行相加的实际单元格,如果省略Sum_range,则当区域中的单元格符合条件时,它们既按条件计算,也执行相加。 注意:Sum_range 与Range的大小和形状可以不同,相加的实际单元格从sum_range 中左上角的单元格作为起始单元格,然后包括与range大小和形状相对应的单元格。公式中range是指“K2:K132”,也就是“班级”这列所有单元格;criteria是指“">4"”,意思是指班级数大于4的5、6、7、8班;而sum_range是指“化学”这列成绩,意思是对符合“班级”条件的化学分数求和。而整个SUMIF函数公式是计算所有班级为5、6、7和8班的同学的化学成绩的总和。 COUNTIF函数是统计指定区域中满足给定条件的单元格个数的函数。其语法格式是:COUNTIF(range,criteria),range是一个或多个要计数的单元格,其中包括数字或名称、数组或包含数字的引用,空值和文本值将被忽略。criteria 是指按什么条件进行统计,其形式可以为数字、表达式、单元格引用或文本。 公式中range是指“K2:K132”,也就“班级”这列所有单元格;criteria是指“">4"”,意思也指班级数大于4的5、6、7、8班。整个COUNTIF函数公式是统计班级这列中班级数为5、6、7和8班的同学的总人数。而整个公式就是等于所有班级为5、6、7和8班的同学的化学成绩总和除以班级数为5、6、7和8班的同学的总人数。 如果每科教师所教的班级一样,还可以在公式中加入$,以便进行拖动填充($为绝对应用,拖动公式时所引应用的单元格不发生变化),如:=SUMIF($K$2:$K$132,">4",G2:G132)/COUNTIF($K$2:$K$132,">4")。 假设我们用一个工作表记录授课信息,工作表的第一行为标题,第二和第三行放置一些统计数据,从第四行开始每行记录一次课(不是一节课)的相关信息(预计一年的记录不超过10000行,即用到行数10004就可以了),其中,B列以A、B、C、D标出课型(不同的课型课酬不一样),D列是授课人当时上了多少节课(请来的专家一般不可能只上一节课)。我们的目的之一是:统计各种课型即ABCD四种课型的总节数。 |
|