EXCEL公式函数应用培训

EXCEL介绍（公式函数篇）公式函数的作用概述公式和函数使得电子表格变得非常有用。尤其是在处理大量数据的时候。数据更新后，公式将自动更新结果。函数可以大大加强公式的功能和进行普通运算符不能完成的计算。通常使用的只有十几个函数。目录Part1引用函数Part2查找函数Part3统计函数Part4时间、日期、文本函数Part5公式相关：数据有效性、条件格式Part6数组公式Part1引用函数基础概念认识单元格引用常用引用函数OFFSET函数函数综合应用实例Partl引用函数－单元格的引用用某种记号表示一个单元格，称为对单元格的引用。在Excel中，有如下两种方法表示一个单元格：（1）A1引用样式最右下角的单元格——IV65536（2）R1C1引用样式设置R1C1方式Partl引用函数－单元格的引用区域的引用连续区域用矩形的对角单元格的引用，中间加上冒号“:”来表示例：A1:E4不连续区域依次使用各个单元格及单元格区域的引用，之间用逗号“,”连接起来例：A1,E4:J8,F10:L20Partl引用函数－单元格的引用含义：单元格作为一个整体以单元格地址的描述形式参与运算称为单元格引用。引用方式：相对引用绝对引用混合引用单元格引用公式与函数单元格引用单元格引用方式相对引用、绝对引用、混合引用的概念使用“$”锁定引用引用实例：相对引用：A1，B5绝对引用：$A$5，$C$2混合引用：$A3，D$1相对、绝对引用举例利润率恒定，所以用“$”锁定引用在D2单元格输入：=B2*C2*$G$1求产品的利润？混合引用举例－九九乘法表B2公式：=IF(B$1$A2,,B$1&*&$A2&=&B$1*$A2)Partl引用函数－OFFSET函数OFFSET函数将源引用根据指定的行、列偏移产生新的单元格引用。OFFSET函数语法OFFSET(基点,行数,列数,[高度],[宽度])函数特征基点必须是单元格，不能是数组；[高度][宽度]用来指定目标区域大小，若不写，则默认与基点区域相同[高度][宽度]允许负数。Partl引用函数－OFFSET函数Partl引用函数－OFFSET函数一个疑问：如何统计最后5天的销售量的和？=SUM(OFFSET($C$1,COUNTA(C:C)-1,0,-5))Part2查找函数困惑一：手头上有很多孔内无铜的编号，另外有一个编号AR值数据表，如何知道这些孔内无铜编号的AR值？困惑二：CCTC新春晚会中奖一等奖是1688、1353、2247，如何知道这几个号码对应哪个员工？Part2查找函数FindingyourMr.Right&Mrs.Right!Part2查找函数介绍：VLOOKUPVLOOKUP可用于：产品信息查询、员工信息查询Part2查找函数VLOOKUP函数VLOOKUP(查找值,数据表,列序号,查找模式)在C2输入：=VLOOKUP(A2,$F$2:$G$13,2,false)关于查找模式：1.false:精确查找，不必排序；2.true：近似查找，首列升序VLOOKUP举例2Part3统计函数COUNT家族－COUNT，COUNTA，COUNTIFSUM家族－SUM，SUMIF数据库函数－DSUM，DCOUNT...Part3统计函数－Count家族COUNT(单元格/区域1，单元格/区域2...)统计指定单元格或区域包含数字的单元格个数。COUNTA(单元格/区域1，单元格/区域2...)统计指定单元格或区域非空的单元格个数。COUNTIF(引用,条件)统计指定单元格或区域满足指定条件的单元格个数。COUNTIF介绍条件的形式可以为数字、表达式、文本或数组，只能用单条件不能是复合条件当条件为文本时可以用通配符*(表示任意多的字符)和?(表示单个字符)，如果要查找不是通配符的*和?字符，可用~*和~?表示COUNTIF举例统计上面数字区域的大于20的单元格个数?=COUNTIF($A$2:$D$7,“20”)上例中的条件也可以是引用别的单元格的，如：写成：=COUNTIF($A$2:$D$7,&G1)Countif举例2一厂QC统计加班实例：(动画演示）COUNTIF举例3（文本）求区域A2:D6中以A开头的单元格数=COUNTIF(A2:D6,A*)求区域A2:D6中以A开头以6结束的单元格数=COUNTIF(A2:D6,A*6)求区域A2:D6中第3位为A的单元格数=COUNTIF(A2:D6,??A*)谈谈SUMIFSUMIF(引用1,条件,引用2)结果：按引用1中满足条件的单元格位置求引用2中对应位置的单元格中数值的总和。sumif举例开路报废面积是多少？3.2.2sumif举例3.2.2sumif举例=SUMIF(C2:C7,开路,E2:E7)sumif举例综合应用实例：歌唱比赛的评分如何快速统计平均分（去掉一个最高分，一个最低分，再求平均）2-)Counta()(Min-)Max(-)SUM(所有评分所有评分所有评分所有评分=(SUM($B2:$G2)-Max($B2:$G2)-MIN($B2:$G2))/(COUNTA($B2:$G2)-2)数据库函数dsum→返回数据范围的列中满足指定条件的数字之和dsum(数据范围,列标题,条件)数据库函数dsum举例3.3dsum举例2dsum举例=DSUM(A1:E7,E1,C11:D12)数据范围列标题条件Part4时间日期、文本函数时间日期函数：DAY,WeekDay,NOW(),Today()...文本函数：Left，Right，Find，Len，...时间函数您所看到的日期与Excel的内部日期表示是不同的。对您来说，2005年8月22日是一个带有年、月、日的日期。而对Excel来说，2005年8月22日是序列数38586。对于Excel，凌晨12:00点则是0，而中午12:00点则是小数0.5。时间函数2Now(),返回当前的时间。Today(),返回当前的日期。A1-B1:计算两个日期之前的天数。Year(A1),moth(A1),day(A1),hour(A1),minute(A1),Second(A1):返回某事件表示的各细项。文本函数1Upper:把文本全部转换为大写。Lower:把文本全部转换为小写。Proper:把文本转换为“恰当”形式（每一个单词的第一个字母大写）。Left:从字符串的开始返回特定数量的字符。Left(“Add012”,3)=“ADD”Right:从字符串的尾部返回特定数量的字符。Right(“Add012”,3)=012Mid:从字符串的任意位置开始返回特定数量的字符。Mid(“Add012”,2,2)=“dd”文本函数2Trim：除了两个单词之间的单个空格，可以去掉文本参数中的所有空格。Clean：从字符串中去掉不能打印的字符。当引入某种类型数据时，常会出现这些“垃圾”字符。Exact：比较两个字符串是否完全相同。Len：返回字符串中的字符个数。Find:查找某个字符在文本中的位置。Concatenate：将两个单元格中的信息合并到一个单元格中。使用Left和Find函数分割名字Left(A2,find(“”,A2)-1)Right(A2,len(A2)-find(“”,A2))Part5公式相关：条件格式、数据有效性条件格式1条件格式是指当指定条件为真时，Excel自动应用于单元格的格式Part5公式相关：数据有效性、条件格式条件格式2：标出加班时数大于36小时的员工数据有效性Part5公式相关：数据有效性、条件格式Part6数组公式数组公式是用于建立可以产生多个结果或对可以存放在行和列中的一组参数进行运算的单个公式。数组公式概述由于一个单元格内只能储存一个数值,所以当结果是一组数据时,单元格只返回第一个值,如:如输入={23,24,25,22}结果返回为：23数组公式概述要返回指定结果，要多个单元格：=INDEX({23,24,25,22},1)返回23=INDEX({23,24,25,22},2)返回24=INDEX({23,24,25,22},3)返回25=INDEX({23,24,25,22},4)返回22或者取共性：=SUM({20,30,40,10})返回100=MAX({20,30,40,10})返回40数组公式的参数数组公式最大的特征就是所引用的参数是数组参数,包括区域数组和常量数组。区域数组,是一个矩形的单元格区域,如$A$1:$D$5常量数组,是一组给定的常量,如{1,2,3}或{1;2;3}或{1,2,3;1,2,3}数组公式中的参数必须为矩形,如{1,2,3;1,2}就无法引用了数组公式的输入同时按下CTRL+SHIFT+ENTER数组公式的外面会自动加上大括号“{}”予以区分数组公式的工作原理=SUM(IF(A2:A6=BB,(B2:B6)*(C2:C6),0))求产品为“BB”的总价？数组公式的工作原理=SUM(IF(A2:A6=BB,(B2:B6)*(C2:C6),0))该公式等效于：=SUM(IF(A2=BB,B2*C2,0)IF(A3=BB,B3*C3,0)IF(A4=BB,B4*C4,0)...)=SUM(0,2*150,0,0,2*300)=900用数组公式实现歌唱比赛的评分{=AVERAGE(IF((所有评分最高分)*(所有评分最低分),所有评分)}{=AVERAGE(IF((条件1)*(条件2),区域)}求满足条件1且满足条件2的区域数组里对应单元格的平均值。“*”表示且。“+”表示或。数组公式举例2－报废数据统计统计“常规”、“8层及以上”板件的开路报废面积？=SUM(IF((LEFT(G2:G17,2)=常规)*(E2:E17=8)*(D2:D17=开路),F2:F17))