常规函数学习交流

Excel常见函数学习交流制造部唐柳萍/2013年4月2日SUM高级应用IF判断求解SUMIF条件求和COUNT非空单元格统计COUNTBLANK空白单元格统计HLOOKUP应用LOOKUP应用VLOOKUP应用COUNTIF条件单元格统计目录COUNTIFS多条件单元格统计SUM高级应用—条件求和利用SUM与IF制成数组函数格式：﹛

SUM(IF(

条件设置

,结果数值区域

))﹜注意：1.“结果数值区域”可以是条件对应的N个数列；2.数组公式，要CTRL+SHIFT+ENTER结束，即制成﹛﹜。3.必要时锁住单元格，如$B$6：$B$29=$C$31,避免因添加、移动、删除、拖拉单元格造成所选区域偏移。数列行数SUM高级应用—数组函数求和用“*”连接多条件数组函数求和函数格式：﹛

SUM(（区域1=条件1）*（区域2=条件2）*（区域3=条件3）*（结果数值区域）)﹜注意：1.“结果数值区域”即同时满足条件1、2、3…的数值的总和；2.最多可以设置7组条件；条件区域横向纵向选择均可。3.数组公式，要CTRL+SHIFT+ENTER结束，即制成﹛﹜。4.必要时锁住单元格，如$C$6：$C$29=$F$32,避免因添加、移动、删除、拖拉单元格造成所选区域偏移。IF条件判断函数一般应用函数格式：IF(

条件设置

,结果数值区域

)；如：IF(C3>60，”及格”,”不及格”)描述为：当C3单元格数值满足>60时，显示”及格”——条件判断为YES

当C3单元格数值不满足>60时，显示”不及格”——条件判断为NO注意：1.条件判断的结果可以是文本形式；也可以摘选单元格区域；

2.当条件判断的结果为文本形式时，须用英文字符””将文本引用，否则函数程序无法识别。

3.以上示例适合应用于同时满足单一条件的情况。IF多条件判断函数一般应用函数格式：IF（AND(条件1，条件2),满足条件值域，不满足条件值域)判断单元格是否同时满足N（N<7）个条件，取值。注意：1.所取数值必须是单一文本或单元格信息；

2.设置条件最多不超过7个；

3.可以用几个IF公式叠加制成，如示例。SUMIF条件求和函数应用函数格式:SUMIF(条件筛选区域，单一指定条件，求和值域)示例函数含义：在条件筛选区域A里查找B22值，然后在求和值域B找出对于值并求和。注意：AB区域只能选择单一且对应的行/列；所有“陨石黑”格式必须一致，否则函数程序无法识别，常见错误为空格不符、错别字、文本格式等。3.必要时锁住单元格，如$B$4：$B$21,避免因添加、移动、删除、拖拉单元格造成所选区域偏移。SUMIFS条件求和函数应用函数格式:SUMIFS(条件筛选区域1，条件1,条件筛选区域2,条件2...,求和值域)示例函数含义：在条件筛选区域1中查找A23值，再在条件区域2中查找B23,然后在求和值域找出对应值并求和。注意：1、2、求和区域只能选择单一且对应的行/列；所有“陨石黑”格式必须一致，否则函数程序无法识别，常见错误为空格不符、错别字、文本格式等。3.必要时锁住单元格，如$B$4：$B$21,避免因添加、移动、删除、拖拉单元格造成所选区域偏移。LOOKUP函数应用LOOKUP是向量或数组中查找信息，并返回指定单元格数值。函数格式：LOOKUP（指定条件，条件区域行/列，值区域行/列）示例函数表示：在A区域查找B22单元格信息，返回B区域对应数值。注意：1.指定条件可以是单元格或文本信息，文本信息须用””将其引用起来。2.如果在A区域出现多个“杨丽红”，计算机默认返回其中一个“杨丽红”对应B区域的数值，易产生统计错误。HLOOKUP纵向查找函数应用HLOOKUP函数功能：查找数组首行，并返回指定行对应单元格的值。函数格式：HLOOKUP（指定条件，查找区域，取值所在行数，0）示例函数描述为：在A2：P6区域的第一行（即A2：P2区域）查找A8“郭建峰”信息，由上至下数返回第5个单元格数值。注意：1.若函数选中区域第一行找不到指定信息，则函数显示#N/A。2.该函数默认从第一行内查找指定条件信息。3.指定条件可以是单元格或文本。文本必须用””将其引用，否则函数公式无法识别。4.函数最后一个参数取TRUE或FALSE对函数结果影响不大，为了便于记忆，建议函数最后一个参数统计为0.12345VLOOKUP横向查找函数应用12345VLOOKUP函数功能：在数组第一列中查找，然后在行之间移动以返回单元格中的值。注意：1.模糊查找可以用”?”代表任意单一字符；“*”代表任意多个字符；各字符、单元格、文本之间用&连接。COUNT/COUNTBLANK函数应用COUNT函数功能：返回指定单元格区域包含数字的单元格的个数。函数格式：COUNT（指定区域）COUNTBLANK函数功能：返回指定单元格区域内空白单元格个数。函数格式：COUNTBLANK（指定区域）注意：1.COUNT忽略空格、文本、错误（#N/A）;2.COUNTBLANK中即使单元格中含有返回值为空文本("")的公式，该单元格也会计算在内，但包含零值的单元格不计算在内。COUNTIF函数应用COUNTIF函数功能：计算指定区域中满足条件单元格的个数函数格式：COUNTIF（指定区域，设置条件）COUNTIF函数的16种常规公式设置注：DATA为作者所选的指定区域1、返加包含值12的单元格数量=COUNTIF(DATA,12)2、返回包含负值的单元格数量=COUNTIF(DATA,"<0")3、返回不等于0的单元格数量=COUNTIF(DATA,"<>0")4、返回大于5的单元格数量=COUNTIF(DATA,">5")5、返回等于单元格A1中内容的单元格数量=COUNTIF(DATA,A1)6、返回大于单元格A1中内容的单元格数量=COUNTIF(DATA,“>”&A1)7、返回包含文本内容的单元格数量=COUNTIF(DATA,“*”)8、返回包含三个字符内容的单元格数量=COUNITF(DATA,“???”)9、返回包含单词"GOOD"(不分大小写)内容的单元格数量=COUNTIF(DATA,“GOOD”)10、返回在文本中任何位置包含单词"GOOD"字符内容的单元格数量=COUNTIF(DATA,“*GOOD*”)11、返回包含以单词"AB"(不分大小写)开头内容的单元格数量=COUNTIF(DATA,“AB*”)12、返回包含当前日期的单元格数量=COUNTIF(DATA,TODAY())13、返回大于平均值的单元格数量=COUNTIF(DATA,">"&AVERAGE(DATA))14、返回平均值上面超过三个标准误差的值的单元格数量=COUNTIF（DATA，“>"&AVERAGE(DATA)+STDEV(DATA)*3)15、返回包含值为或-3的单元格数量=COUNTIF(DATA,3)+COUNIF(DATA,-3)16、返回包含值;逻辑值为TRUE的单元格数量=COUNTIF(DATA,TRUE)浅薄的经验总结，希望对大家有所参考☺☻函数应用特别提醒事项函数的提示语

#N/A函数编制无原则错误，但是计算机无法在你所选中的区域找到条件值。学会锁住单元格单元格地址是由英文字母与阿拉伯数字组成，字母代表横向坐标，数字代表纵向坐标，如，$A1即锁住横向坐标，用光标上下拖拉移动时依次为$A1、$A2、$A3…A列不变；锁住数字或同时锁住字母、数字也是一样的道理