常用函数及其应用课件

1-2 Excel财务管理基本操作1-2-2 常用函数及其应用公式和函数基础公式和函数基础一、公式的修改方法：选中公式所在的单元格，单击编辑栏。Delete：删除。编辑状态下，删除光标后面的内容。Backspace：退后一格。编辑状态下，删除光标前面的内容。二、Excel函数是一类特殊的公式。函数名不区分大小写。公式和函数基础公式和函数基础三、使用公式记忆式键入手工输入函数。设置：【Office按钮】【Excel选项】【公式】勾选【使用公式】区域的【公式记忆式键入】复选框【确定】启用“公式记忆式键入”功能后：1.当用户输入公式时，系统会自动显示以输入的字符开头的所有函数。2.通过在扩展下拉菜单中移动上、下方向键，或鼠标选择不同的函数，其右侧将显示此函数的功能简介。3.双击鼠标，或者按键，可添加此函数。1-2-2 常用函数及其应用常用函数及其应用1-2-2-1 SUM函数、SUMIF函数和SUMPRODUCT函数（数学与三角函数）（1）无条件求和SUM函数：求30个以内参数的和。公式为：=SUM（参数1，参数2，参数N）当对某一行或某一列的连续数据进行求和对某一行或某一列的连续数据进行求和时，可以使用工具栏中的自动求和按钮例：求全年的销售量。例：求全年的销售量。数据文件：自动求和练习.xls4（2）条件求和SUMIF函数：根据指定条件对若干单元格求和。公式为：=SUMIF(range,criteria,sum_range)式中：range（范围）用于做条件判断的区域；Criteria（标准，条件）条件，可以为数字、文本或表达式；Sum_range需要求和的数值所在的区域.5解释：如果range里的数值满足criteria，则对Sum_range里的数值求和。例1-4 某商场2月份销售的家电流水记录如下图，请按商品类别对销售额汇总。数据文件：P22页例1-4.xls6F4：在绝对引用和相对引用之间切换。例 汇总指定商品的月销量汇总指定商品的月销量.xls下图展示了一份某集团生成的所有饮品月度销量情况表，1.请用sumif函数计算指定商品指定商品的月销售量。2.请用sumif函数统计销量销量1000箱箱的饮品总销量。7知识点：数据的筛选、数据有效性、sumif函数。公式：1.Sumif(B2:B10,F2,C2:C10)2.Sumif(C2:C10,”1000”,C2:C10)例 统计第二季度方便面的销售情况统计第二季度方便面的销售情况.xls下图展示了一份某超市卖场第二季度的方便面销量情况表。1.请用sumif函数统计“康师傅康师傅”方便面的总销量。2.请用sumif函数统计B组员工组员工的总销量。9SUMIF函数按照条件判断区域，进行相对位置的偏移，来对求和区域进行统计求和。Excel支持两个通配符：星号“*”和问号“?”（1）*：可代替任意数目的字符，可以是单个字符、多个字符或者没有字符。（2）?：可代替任意单个字符。公式：1.Sumif(C3:I11,”康师傅”,D3:J11)2.Sumif(B3:H11,”B*”,D3:J11)或 Sumif(B3:H11,”B?”,D3:J11)（3）多组数据相乘求和sumproduct函数Sum：summary的缩写，求和。Product：乘积。公式为：=sumproduct(array1,array2,array3,)式中，array1,array2,array3,1至30个数组。功能：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。注意：1.数组参数必须具有相同的维数；否则，函数sumproduct将返回错误值”#VALUE!”.2.非数值型的数组元素将作为0处理。11（3）多组数据相乘求和sumproduct函数例：求三个数组对应元素的乘积之和。数据文件：sumprocut函数示例.xls12（一）计数函数COUNT功能：计算给定区域内数值型数值型参数的数目.公式为：=COUNT(参数1,参数2,参数N)例：下图展示了一份某班学生的月考成绩表，请统计各个科目的参考人数。1-2-2-4 Count函数和Countif函数（统计函数）数据文件：count函数示例.xls13思考：COUNT(A1:E16)=?（二）条件计数函数COUNTIF功能：计算给定区域内满足特定条件的满足特定条件的单元格（可以是可以是非数值型非数值型）的数目.公式为：=COUNTIF(range,criteria)式中：range（范围）需要计数的单元格区域；criteria（标准，条件）条件，可以为数字、表达式或文本。14解释：如果range里的数据满足criteria，则对range里的数据计数。即：用做判断的区域和需要计数的区域是同一区域。回忆：条件求和函数SUMIF。公式为：=SUMIF(range,criteria,sum_range)例：下图展示了一份某公司员工工资表，请用countif函数统计各个部门人数。数据文件：countif函数示例.xls151-2-2-5 IF函数（函数（逻辑函数）IF函数，也称条件函数。用途：根据参数条件的真假，返回不同的结果。公式为：=IF(logical_test,value_if_true,value_if_false)式中，logical_test：条件表达式，其结果要么为真，要么为假；Value_if_true：当logical_test为真时，返回的值。Value_if_false：当logical_test为假时，返回的值。16分析：如果logical_test是真的，则单元格=Value_if_true；如果logical_test是假的，则单元格=Value_if_false。1-2-2-5 IF函数函数例：下表列出了某企业各个销售部门2011年的销售业绩，请按评价标准对各个部门的业绩进行评价。数据文件：例1-5-1-练习.xls步骤：1.在单元格C3中输入公式 =IF(B3=300000,“合格”,”不合格”)171-2-2-5 IF函数函数例：下表列出了某企业各个销售部门2011年的销售业绩，请按评价标准对各个部门的业绩进行评价。数据文件：例1-5-2-练习.xls18分析分析300000400000不合格合格优秀判断过程：判断过程：IF 销售额300000 则：返回“不合格”否则：IF 销售额400000 则：返回”合格”否则：返回”优秀”公式：公式：=IF(B3300000,“不合格不合格”,IF(B3400000,“合格合格”,“优秀优秀”)函数的嵌套19销售额销售额1-2-2-5 IF函数函数例：下表列出了某企业各个销售部门2011年的销售业绩，请按评价标准对各个部门的业绩进行评价。数据文件：例1-5-3-练习.xls20分析分析300000400000合格好很好判断过程：判断过程：IF 销售额100000 则：返回“差”否则：IF 销售额300000 则：返回”合格”否则：IF 销售额400000 则：返回”好”否则：返回”很好”公式：公式：=IF(B3100000,“差差”,IF(B3300000,“合格合格”,IF(B3400000,“好好”,“很好很好”)100000差21销售额销售额公式：公式：=IF(B3100000,“差差”,IF(B3300000,“合格合格”,IF(B3400000,“好好”,“很好很好”)22分析分析300000400000合格好很好判断过程：判断过程：IF 销售额300000 则：IF 销售额100000 则：返回”差”否则：返回”合格”否则：IF 销售额400000 则：返回”好”否则：返回”很好”公式：公式：=IF(B3300000,IF(B3100000,“差差”,“合格合格”)IF(B3400000,“好好”,“很好很好”)100000差23销售额销售额第二种方法：第二种方法：第一次判断第二次判断1-2-2-5 IF函数函数例：下表列出了某企业各个销售部门2011年的销售业绩，请按评价标准对各个部门的业绩进行评价。数据文件：例1-5-练习.xls24更正教材P24页图1-31错误300000400000一般好很好判断过程：判断过程：IF 销售额100000 则：返回“差”否则：IF 销售额200000 则：返回”一般”否则：IF 销售额300000 则：返回”较好”否则：IF 销售额400000 则：返回”好”否则：返回”很好”公式：公式：=IF(B3100000,“差差”,IF(B3200000,“一般一般”,IF(B3300000,“较好较好”,IF(B33,46)=FALSE，AND(53,46)=TRUE例1-6 某企业根据各销售部门的销售额及销售费用确定奖金提取比例及提取额。若销售额300 000元且销售费用占销售额的比例不超过1%，则奖金提取比例为15%，否则为10%。27数据文件：例1-6.xls分析：先在D3计算部门A的奖金提取比例IF B3300000 与 C3/B3300000,C3/B33,46)=TRUE，OR(56)=TRUE，NOT(46)=FALSE练 习如下图所示，表中B列为职工年龄，C列为职称。1.公司提拔干部，需要筛选出年龄在35周岁以下，职称为工程师的人选。2.职工中，拥有“工程师”或“高工”职称的一共有多少人？31数据文件：逻辑关系运算.xls32问题1分析：对第一位职工，判断他是否满足条件“年龄在35周岁以下，职称为工程师”。判断过程IF B335 且 C3=“工程师”则：在D3 返回”满足”否则：在D3 返回”公式公式：在D3单元格输入=IF(AND(B335,C3=“工程师”),“满足”,“”)空文本33问题2分析：对第一位职工，判断他是否满足条件“拥有“工程师”或“高工”职称”。判断过程IF C3=“工程师”或 C3=“高工”则：在E3 返回”满足”否则：在E3 返回”公式公式：在E3单元格输入=IF(OR(C3=“工程师”,C3=“高工”),“满足”,“”)空文本34问题2的另一种解法：“职称”有3个取值：工程师、高工、助工判断过程IF C3“助工”则：在E3 返回”满足”否则：在E3 返回”公式公式：在E3单元格输入=IF(NOT(C3=“助工”),“满足”,“”)或者：=IF(C3“助工”,“满足”,“”)空文本1-2-2-7 Match函数 常用的定位函数Match：匹配是Excel常用的查找定位函数之一，主要用于确定查找值在查找范围中的位置序号。公式：=MATCH(lookup_value,lookup_array,match_type)式中：Lookup_value：需要在 lookup_array 中查找的值。Lookup_array：要搜索的单元格区域。即：在Lookup_array中找Lookup_value这个值。Match_type：如何查找。可取三个值：-1,0,1.当match_type=-1时：Lookup_array必须按降序降序排列，MATCH函数从大往小查，查找到 Lookup_value的最小数值的最小数值。例：在降序排列的数据列中，查找例：在降序排列的数据列中，查找20的相对位置。的相对位置。数据文件：数据文件：match函数函数.xls35查什么在哪里查以什么方式查公式：=MATCH(lookup_value,lookup_array,match_type)式中：Lookup_value：需要在 lookup_array 中查找的值。Lookup_array：要搜索的单元格区域。Match_type：如何查找。可取三个值：-1,0,1.当match_type=1时：Lookup_array必须按升序升序排列，MATCH函数从小往大查，查找到 Lookup_value的最大数值的最大数值。例：在升序排列的数据列中，查找例：在升序排列的数据列中，查找20的相对位置。的相对位置。数据文件：数据文件：match函数函数.xls36当match_type=0时：Lookup_array可以按任何顺序任何顺序排列，MATCH函数查找到=Lookup_value的第一个数值的第一个数值。例：在原始数据列中，分别查找例：在原始数据列中，分别查找20和和23的相对位置。的相对位置。数据文件：数据文件：match函数函数.xls没找到：返回没找到：返回”#N/A”，not applicable.Applicable，合适的。，合适的。37P26页 课本上的示例MATCH(12,23,43,12,55 ,0)=3MATCH(40,23,43,12,55 ,1)=1注意：1.MATCH 函数会返回lookup_value在 lookup_array 中匹配值的位置而不是匹配值本身。例如，MATCH(b,a,b,c,0)会返回 2，即“b”在数组 a,b,c 中的相对位置。2.查找文本值时，MATCH 函数不区分大小写字母。3.如果 MATCH 函数查找匹配项不成功，它会返回错误值#N/A。38MATCH（）一边查找lookup_value，一边检查Lookup_array是否升序排列。1-2-2-8 INDEX函数函数公式：=INDEX(array,row_num,column_num)式中：INDEX索引；array单元格区域或数组常数；Row_num行序号；Column_num列序号.用途：根据Row_num（行号）和Column_num（列号），返回array（数组）中的数值。常与MATCH函数结合使用。例：根据指定的行号和列号，找到数组（黄色凸显）中对应的元素值。数据文件：INDEX初步.xls39提供索引MATCH函数：定位，返回数据的位置。INDEX函数：根据位置返回实际的数值。实际工作中，常常将两者组合使用。例：下图是某组员工的工作量明细表。要求，根据B2中的姓名和B3中的项目，从明细表中查询对应的工作量。数据文件：多条件组合查询员工工作量.xls40如何用公式实现这一过程？1.确定行号、列号行号：MATCH(B2,D2:D7,0)此时=3列号：MATCH(B3,E1:H1,0)此时=32.根据行号和列号在数组中查询数值。INDEX(E2:H7,MATCH(B2,D2:D7,0),MATCH(B3,E1:H1,0)41人工如何查？根据“查找姓名”确定在哪一行，根据“项目号”确定在哪一列。行列交叉处的数值就是员工工作量。例：下表是某公司的一份设备分配信息表。请根据设备ID查询设备使用人姓名。数据文件：根据设备ID查询设备使用人.xls42查询步骤：1.根据设备ID在F2：F9区域查询行号。MATCH(B2,F2:F9,0)2.根据行号在E2：E9区域查询设备使用人。公式：=INDEX(E2:E9,MATCH(B2,F2:F9,0)如果数组只包含一列，则相对应的参数如果数组只包含一列，则相对应的参数 column_num 为可选参数。为可选参数。教材上示例：INDEX(1,2;3,4,0,2)=2;4数据文件：用INDEX返回整行或整列.xls解释：1.1.如果将如果将 row_num 设置为设置为 0（行号不作限制），函数（行号不作限制），函数 INDEX 则返回整个列的数组数值。则返回整个列的数组数值。2.要返回一个数组，INDEX 函数必须以数组公式形式输入：43输入步骤：返回一个结果 选定一个单元格数组公式 返回多个结果 选定单元格区域输入数组公式。同时按同时按【Ctrl+Shift+Enter】组合键。组合键。这时，Excel自动在公式的两边加上大括号。行号不做限制思考：如何用INDEX函数在1,2;3,4数组中返回第2行？数据文件：用INDEX返回整行或整列.xls44回忆：公式：=INDEX(array,row_num,column_num)1.如果数组只包含一列，则相对应的参数 column_num 为可选参数。如果数组只包含一行，则相对应的参数 row_num 为可选参数。2.如果将 column_num 设置为 0（列号不作限制），函数 INDEX 则返回整个行的数组数值。如果将 row_num 设置为 0（行号不作限制），函数 INDEX 则返回整个列的数组数值。3.要返回一个数组，INDEX 函数必须以数组公式形式输入。1-2-2-9 ADDRESS函数（查找与引用函数）函数（查找与引用函数）ADDRESS函数的功能：按照给定的行号和列标，返回文本类型的单元格地址。45公式为：=ADDRESS(row_num,column_num,abs_num,a1,sheet_text)式中：u row_num：行号；uColumn_num：列标；uAbs_num：abs，absolute，绝对的。何种引用类型？abs_num返回的引用返回的引用类型型1或省略绝对引用2行绝对列相对引用3行相对列绝对引用4相对引用1-2-2-9 ADDRESS函数函数46公式为：=ADDRESS(row_num,column_num,abs_num,a1,sheet_text)式中：u a1：是否A1引用样式。逻辑值。a1返回的引用返回的引用样式式TRUE或省略A1样式FALSER1C1样式补充：A1引用样式和R1C1引用样式根据表示方式表示方式的不同，单元格引用可分为A1引用样式引用样式和R1C1引用样式引用样式。单元格引用A1引用样式（默认）R1C1引用样式（1）A1引用样式（默认）：使用字母A XFD表示列标，用数字1 1048576表示行号，单元格地址由列标与行号组合而成。（2）R1C1引用样式如何启用R1C1引用样式：【office按钮】【Excel选项】【公式】选项卡在【使用公式】区域中勾选【R1C1】引用样式。练习练习R1C1引用样式：R，ROW（行）。C，Column（列）。行号和列标都用数字表示。例：例：nR2C3表示：第表示：第2行第行第3列交叉处位置。列交叉处位置。也就是也就是A1引用样式中的引用样式中的C2.回到A1引用样式。49uSheet_text：外部工作表的名称。文本值。可省略。教材上的例子：教材上的例子：P27ADDRESS(2,3)=“$C$2”绝对引用，绝对引用，A1样式。样式。ADDRESS(2,3,2)=“C$2”行绝对列相对，行绝对列相对，A1样式。样式。1-2-2-9 ADDRESS函数函数公式为：=ADDRESS(row_num,column_num,abs_num,a1,sheet_text)式中：:R1C1引用样式的相对引用符。例：ADDRESS(2,3,2,FALSE)=”R2C3”解释：行绝对列相对，解释：行绝对列相对，R1C1引用样式。引用样式。例：ADDRESS(2,3,”sheet2”)=”sheet2!$C$2”解释：绝对引用，解释：绝对引用，A1引用样式引用样式50练习：如下图所示，在B2:B5依次键入以下4个公式：=ADDRESS(4,5)；=ADDRESS(4,5,3)；=ADDRESS(4,5,3,FALSE)；=ADDRESS(4,5,3,FALSE,”sheet3”)。观察返回的结果。数据文件：ADDRESS函数练习1-2-2-9 INDIRECT函数（查找与引用函数）函数（查找与引用函数）Indirect，间接的。51公式为：=INDIRECT(ref_text,a1)式中：uref_text：ref，reference，引用（vi.或vt.）。text，文本。可以是单元格引用或名称。ua1：逻辑值。Ref_texta1A1样式TRUE或省略R1C1样式FALSEINDIRECT函数的功能：第第1个参数将给定一个字符串个参数将给定一个字符串，这个字符串可以是直接给出的，也可以是由单元格引用返回的。函数根据该字符串表示的引用返回对应的数据。练习数据文件：indirect函数示例1.xls52要求：在E1E4单元格依次输入公式“=indirect(“A1”,TURE)”、“=indirect(B1,TURE)”、“=indirect(B2,TURE)”、“=indirect(B3,TURE)”，观察计算结果。indirect函数的运算逻辑：书上的例子：书上的例子：P28页页数据文件：INDIRECT函数示例2.xls如果单元格A1包含文本”B2”，且单元格B2包含数值”1.333”，则INDIRECT($A$1)=INDIRECT(“B2”)=1.33353练习：分别在E3、E5、E7单元格输入公式“=indirect(”$A$1”)”、“=indirect(B2)”、“=indirect(“B2”)”，观察计算结果。想一想，为什么是这个结果。例：利用indirect函数动态统计工作量.xls下图中有一份公司某项目员工研发功能点的工作量明细表，请利用indirect函数实现根据功能或员工进行工作量汇总。步骤一：根据所选内容批量创建名称1.选择A2:E8单元格区域。2.【公式】【根据所选内容创建】，弹出【以选定区域创建名称】对话框。3.勾选【首行】和【最左列】复选框，取消其他复选框勾选，单击【确定】。步骤一：根据所选内容批量创建名称4.查看名称。【公式】【名称管理器】。共11个名称。以首行命名的4个名称：功能一、功能二、功能三、功能四。以最左列命名的6个名称：罗明、刘静、张李科、陈靖、和平、赵世杰。以左上角单元格命名的1个名称：员工工作量，引用B3：E8的数据区域。步骤二：根据功能进行工作量汇总查询功能：数据有效性该功能工作量明细：=indirect(G3)数组公式该功能工作量汇总：=sum(indirect(G3)步骤三：根据员工进行工作量汇总（略）56公式为：=INDIRECT(ref_text,a1)INDIRECT函数的功能：在第1个参数的位置找到一个文本文本类型类型的引用（地址或名称），并返回该引用所代表的数据。总 结练习：下图是一组学生的成绩表。请应用INDIRECT函数，通过选择姓名，查询该生的总成绩和平均成绩。数据文件：INDIRECT函数示例3.xls57H4单元格方法一：通过indirect函数引用名称实现。58分析：1.求总成绩，用SUM(B?:F?)。求平均分，用AVERAGE(B?:F?)。2.如何确定行号？需要根据在在H4中选择的姓名中选择的姓名在A列列中查找定位。回忆：确定查找值在查找范围中的位置序号，用MATCH()。用MATCH(H4,A:A,0)确定行号。思考：为什么是在A列中查找定位，而不是在A2:A7中查找定位？此时，MATCH(H4,A:A,0)=2，MATCH(H4,A2:A7,0)=1行号未知H4单元格方法二：通过indirect函数引用文本型地址实现。59思考：公式写成=SUM(BMATCH(H4,A:A,0):FMATCH(H4,A:A,0)，行不行？Excel将返回”#NAME？”3.如何将列标和MATCH函数返回的行号连接在一起呢？回忆：文本合并运算符&“B”&MATCH(H4,A:A,0)&”:F”&MATCH(H4,A:A,0)当MATCH(H4,A:A,0)=2时，上式=“B2:F2”思考：SUM(“B2:F2”)合法么？H4单元格604.回忆：根据文本形式的地址返回对应的数据：用indirect()。indirect(“B2:F2”)=96 78 88 84 81最后的公式：=sum(indirect(“B”&MATCH(H4,A:A,0)&”:F”&MATCH(H4,A:A,0)H4单元格解释：INDIRECT函数示例3练习答案.xls1-2-2-11 ROUND函数（数学与三角函数）函数（数学与三角函数）Round：vt.弄圆，四舍五入。61公式为：=ROUND(number,num_digits)式中：unumber：需要四舍五入的数字。uNum_digits：digit，数字。指定的位数。ROUND函数的功能：根据num_digits指定的位数，对number进行四舍五入。621.如果num_digits0，则舍入到指定的小数位。例：ROUND(123.456,2)=123.462.如果num_digits0，则舍入到指定的整数位。例：ROUND(123.456,-2)=100Num_digits+-0123.4563.如果num_digits=0，则舍入到整数。例：ROUND(123.456,0)=1231-2-2-11 ROUND函数（数学与三角函数）函数（数学与三角函数）63练习：如图所示，在B1:B3单元格分别输入公式“=ROUND(1234.5678,2)”、“=ROUND(1234.5678,-2)”、“=ROUND(1234.5678,0)”，观察计算结果。数据文件：ROUND函数示例.xls解释：ROUND函数与 的区别。