《(本科）第4章 Excel公式函数.pptx》由会员分享，可在线阅读，更多相关《(本科）第4章 Excel公式函数.pptx（49页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、课程主讲人：第4章 Excel公式函数22022年5月13日第4章 Excel公式函数本章教学要求：本章教学要求：v 掌握公式的输入和编辑、单元格的引用以及使用名称v 掌握数组公式、公式审核以及公式中常见错误类型v 掌握函数的表示、格式要求v 熟练掌握常用的函数使用32022年5月13日主要内容：4.1 公式4.2 函数第4章 Excel公式函数42022年5月13日4.1 公式 公式是由数字、运算符号以及Excel的内置函数等组成的数学表达式。 公式总是以等号（=）开头，后面跟一个包含数字、运算符、单元格引用和函数的表达式。 在单元格中输入公式后，单元格内存储的是公式，显示存储的是公式，显示
2、的是公式计算结果。的是公式计算结果。52022年5月13日公式的输入公式的输入v 在单元格中输入公式在单元格中输入公式v 在编辑栏中输入公式在编辑栏中输入公式运算符及优先级运算符及优先级顺序顺序符号符号说明说明1括号（）如有多组括号，起计算顺序是由最内层逐级向外计算2引用运算符包括冒号、空格和逗号，同一类中按出现的先后次序3算术运算符：负号-并不是减号，起作用是取得与原值正负号相反的值4算术运算符：百分号%相当于进行了除以100的运算，可连续使用，如输入“=5%”等于0.00055算术运算符：乘幂例如102等于100，23等于86算术运算符：乘*和除/不同于数学运算中的写法7算术运算符：加+和
3、减-同数学运算中的符号和含义8文本运算符：连接符&连接文本时，需要在文本上先加上半角状态的双引号9比较运算符包括=、=、6种，后3种不同于数学运算中的写法62022年5月13日 单元格和单元格区域引用v引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用数据的位置。引用不同工作簿中的单元格称为外部引用。v在默认状态下，Excel使用A1引用类型。这种类型用字母标志列（AXFD，共16384列），用数字标志行（11048576）。v引用单元格，可顺序输入列字母和行数字。例如，E18引用了列E和行18交叉处的单元格。v引用单元格区域，可输入左上角的单元格引用、冒号（：）和区域右下角单
4、元格的引用。例如，B2:D5引用了B列2行到D列5行的单元格区域。72022年5月13日单元格的引用方式：1. 通过地址引用通过地址引用v 相对引用v 绝对引用v 混合引用。 2. 通过名称引用通过名称引用82022年5月13日单元格的命名方法一：（1）选定需要命名的单元格、单元格区域或非相邻的选定区域；（2）单击编辑栏左边的名称框；（3）为单元格或单元格区域键入名称；（4）按回车键。方法二：（1）切换至【公式】选项卡（2）单击【定义的名称】组中的【定义名称】命令。 方法三：使用【名称管理器】方法四：根据所选内容命名；92022年5月13日u名称首字符必须是字母或者下划线。后面的字符可以是字母
5、、数字等；u名称不能与单元格标识相同。如B$1等；u不允许出现空格，但是下划线和句点可以用来表示分隔符；u一个名称最多允许包含255个字符；u名称中可以包含大小写字母，但是Excel对其不加以区分；u所有的名称均使用绝对引用102022年5月13日引用同一工作表中的单元格数据引用同一工作表中的单元格数据引用引用同一工作簿中某工作表中的数据同一工作簿中某工作表中的数据引用引用不同工作簿中工作表中的不同工作簿中工作表中的数据数据112022年5月13日 数组公式指可以同时进行多个计算并返回一种或多种结果的公式。 在数组公式中使用的两组或多组数据称为数组参数，数组参数可以是一个数据区域，也可以是数组
6、常量。 使用数组公式能减少工作量，提高工作效率。注意：注意：v数组运算公式的两端大括号（）并不是人工输入的，而是在输入普通的公式后，同时按下Ctrl+Shift+EnterCtrl+Shift+Enter三键后自动产生的。公式两端加上一对大括号，代表了数组运算v不能更改数组的一部分122022年5月13日公式审核公式审核是对已经输入好的公式提供一种检查、审核功能，如果某个公式输入异常，可以利用它查看具体是哪里出错了。1.公式求值2.显示公式3.追踪引用单元格4.追踪从属单元格132022年5月13日公式中常见的错误类型公式中常见的错误类型错误值错误值原因原因解决方法解决方法#DIV/O!公式中
7、的分母出现了零值修改公式使除数不为零#N/A数值对函数或公式不可用修改函数参数或公式，如果某些单元格暂无数值，可在这些单元格输入“#N/A”，公式在引用这些单元格时，将不进行数值计算而是返回“#N/A”#NUM!在需要数字参数的函数中使用了不能接受的参数或公式产生的数字太大(太小)，不能表示检查数字是否超出范围，确认函数中使用的参数类型是否正确#VALUE!输入了一个不能运算的参数或单元格里的内容包含不能运算的对象确认公式或函数所需的参数或运算符是否正确，并确认公式引用的单元格包含有效数值#NULL!使用了不正确的区域运算符或不正确的单元格引用如果要引用两个不相交的区域，使用“和”运算符或者“
8、并”运算符#REF!误删了公式中的引用区域，使公式或函数缺少引用的参数修改公式中的无效单元格引用#NAME？删除了公式中使用的名称，或使用了不存在的名称或名称有拼写错误检查公式中使用的名字，如不存在应添加名字，如为拼写错误请更正#输入到单元格的数值太长或公式产生的结果太长，单元格宽度容纳不下适当增加列宽 142022年5月13日4.2 使用函数 在Excel中，函数是预定义的内置公式，使用特定数值的参数，按语法的特定顺序进行计算。 函数语法：以函数名称开头，后面是括号，在括号中可有语法规定个数的参数，各参数间用逗号隔开。如果函数以公式的形式出现，在函数名称前必须加等号“=”。152022年5月
9、13日参数是函数进行计算所必须的初始值。用户把参数传递给函数，函数按特定的指令对参数进行计算，把计算结果返回给用户。函数的参数可以是数字数字、文本文本、逻辑值逻辑值或者是单元格单元格的引用的引用，也可以是常量公式常量公式和其他函数其他函数。 162022年5月13日1. Excel中使用的函数可分为：中使用的函数可分为：- 常用函数- 财务函数- 日期与时间函数- 数学和三角函数- 统计函数- 查找与引用函数- 数据库函数- 文本函数- 逻辑函数- 信息函数- 用户定义- 工程函数- 多维数据集、兼容性和Web- 等等172022年5月13日2 2函数的语法格式函数的语法格式函数的一般语法格式
10、为：=函数名(参数1，参数2，参数n)在使用函数时，要注意以下问题：（1）函数要以等号(=)开头，通常放入某个单元格中。（2）函数名与其后的括号“(”之间不能有空格。（3）当有多个参数时，参数之间用逗号（,）分开。（4）参数部分总长度不能超过1024个字符。（5）参数可以是数值、文本、逻辑值、单元格或单元格区域引用，也可以是各种表达式或函数。（6）函数中的逗号（,）、引号（”）等都是半角字符，而非全角的中文字符。182022年5月13日1. 1. 单一函数的调用方法：单一函数的调用方法：v直接输入函数v利用选项卡命令或按钮调用函数(【公式】/【函数库】【插入函数】命令)2 2. . 嵌套函数的
11、调用嵌套函数的调用 嵌套函数指一个函数的运行结果作为另一个函数中的参数使用的情况 注意：注意：公式中最多可以包含6464层层嵌套函数，并且当某个函数被嵌套在另一个函数中作为一个参数使用时，它必须返回与当前参数的数值类型相同的数值。192022年5月13日1 1求和函数（求和函数（Sum/ Sum/ Sumif/ Sumifs/ SumproductSumif/ Sumifs/ Sumproduct）（1）SUM函数-功能：功能：SUM函数用于计算单个或多个参数之和。-公式：公式：SUM(number1,number2,.)其中，“number1,number2, .”为1到30个需要求和的参数
12、。v在使用SUM函数时，可以计算直接键入到参数表中的数字、逻辑值及数字的文本表达式。如果参数为错误值或为不能转换成数字的文本，将会导致错误。v如果参数是单元格的引用，那么引用的空白单元格、逻辑值、文本和错误值将被忽略。 202022年5月13日（2）SUMIF函数-功能：功能：当IF后面的数值为“TRUE”时，SUMIF函数对符合指定条件的单元格求和。-公式：公式：SUMIF(range, criteria, sum_range)“range”为用于条件判断的单元格区域。“criteria”为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、“32”
13、、“32”、“apples”。注意：注意：条件可以是单元格引用；当条件为不等式或字符串时，条件可以是单元格引用；当条件为不等式或字符串时， 只能用引号括起来只能用引号括起来“sum_range”为需要求和的实际单元格。只有当 range 中的相应单元格满足条件时，才对sum_range中的单元格求和。如果省略sum_range，则直接对 range 中的单元格求和。212022年5月13日（3）SUMIFS函数功能功能：对范围中满足多个条件的单元格求和。公式公式：SUMIFS(sum_range,criteria_range1, criteria1,criteria_range2,criter
14、ia2,.) l Sum_range 必需。对一个或多个单元格求和，包括数字或包含数字的名称、名称、区域或单元格引用。l Criteria_range1 必需。在其中计算关联条件的第一个区域。l Criteria1 必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对 Criteria_range1 参数中的哪些单元格求和。例如，条件可以表示为 32、32、B4、苹果或 32。l Criteria_range2, criteria2, 可选。附加的区域及其关联条件。最多允许127个区域/条件对。222022年5月13日说明：u 仅当 Sum_range 参数中的每一单元格满足所有相
15、应的指定条件时，才对这些单元格进行求和。 例如，假设一个公式中包含两个 Criteria_range 参数。 如果 Criteria_range1 的第一个单元格满足 Criteria1，而 Criteria_range2 的第一个单元格满足 Critera2，则 Sum_range 的第一个单元格计入总和中。对于指定区域中的其余单元格，依此类推。u 包含 TRUE 的 Sum_range 参数中的单元格计算为 1；包含 FALSE 的 Sum_range 中的单元格计算为 0（零）。u 与 SUMIF 函数中的区域和条件参数不同，SUMIFS 函数中每个 Criteria_range 参数包
16、含的行数和列数必须与 Sum_range 参数相同。u 您可以在条件中使用通配符， 即问号 (?) 和星号 (*) 。 问号匹配任意单个字符；星号匹配任意一串字符。 如果要查找实际的问号或星号，请在字符前键入波形符 ()。232022年5月13日（4）多组数据相乘求和的SUMPRODUCT函数功能：功能：在给定的几组数组中，将数组间对应的元素相乘，返回乘积之和。公式：公式：=SUMPRODUCT(array1,array2,array3,)式中，array1,array2,array3,为1-30个数组。注意：注意：数组参数必须具有相同的维数，否则，函数SUMPRODUCT将返回错误值“#VA
17、LUE!”。对于非数值型的数组元素将作为0处理。242022年5月13日（1）COUNT函数-功能：功能：计算参数组中，参数为类型数据的个数。-公式：公式：COUNT(value1,value2,.)value1, value2, . 是包含或引用各种类型数据的参数（130个），但只有数字类型的数据才被计数。v函数COUNT在计数时，将把数字、逻辑值（0、1）、日期或以文字代表的计算进去；但是错误值或其它无法转化成数字的文字则被忽略。v如果参数是一个数组或引用，那么只统计数组或引用中的；数组中或引用的空单元格、逻辑值、文字或错误值都将忽略。如果要统计逻辑值、文字或错误值，请使用函数COUNTA
18、。 2计数函数（计数函数（Count/Counta/Countblank/Countif） 252022年5月13日（2）COUNTA函数-功能：功能：返回参数组中中非空白单元格非空白单元格的数目。利用函数 COUNTA 可以计算数组或单元格区域中数据项的个数。-公式：公式：COUNTA(value1,value2, .)value1, value2, . 为所要计数的值，参数个数为 130 个。在这种情况下，参数值可以是任何类型，它们可以包括空字符（“”），但不包括空白单元格不包括空白单元格（比（比较：空值）较：空值）。如果参数是数组或单元格引用，则数组或引用中的空白单元格将被忽略。2620
19、22年5月13日（3）COUNTBLANK函数功能：功能：计算指定单元格区域中空白单元格的个数。公式：公式：=COUNTBLANK(range)式中，range为需要计算其中空白单元格数目的区域。v 含有返回值为（空文本）的公式单元格也计算在内，但包含零值的单元格不计算在内。272022年5月13日（4）COUNTIF函数功能：功能：计算给定区域内满足特定条件的单元格的数目。语法：语法：COUNTIF(range, criteria)range 为需要计算其中满足条件的单元格数目的单元格区域；criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。282022年5
20、月13日（1）AVERAGE函数-功能：功能：对所有参数计算算术平均值。-公式：公式：AVERAGE(number1,number2,)number1,number2,为1到30个需要计算平均值的参数。参数应该是数字或包含数字的单元格引用、数组或名字。如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值零值则计算在内则计算在内。注意：注意：空单元格与零值的区别空单元格与零值的区别 3. 平均值函数（平均值函数（Average/Averageif/Averageifs/Daverage） 292022年5月13日（2）AVERAGEIF函数-功能：功能：返回某
21、个区域内满足给定条件的所有单元格的平均值（算术平均值）。-公式：公式：AVERAGEIF(range, criteria, average_range)AVERAGEIF 函数语法具有下列参数：Range 必需。要计算平均值的一个或多个单元格，其中包含数字或包含数字的名称、数组或引用。Criteria 必需。形式为数字、表达式、单元格引用或文本的条件，用来定义将计算平均值的单元格。例如，条件可以表示为 32、32、32、苹果 或 B4。Average_range 可选。计算平均值的实际单元格组。如果省略，则使用 range。302022年5月13日（3）AVERAGEIFS功能功能：返回满足多
22、个条件的所有单元格的平均值（算术平均值）。格式：格式：=AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, .)AVERAGEIFS 函数语法具有下列参数：Average_range 必需。要计算平均值的一个或多个单元格，其中包含数字或包含数字的名称、数组或引用。Criteria_range1、criteria_range2 等 Criteria_range1 是必需的，后续 criteria_range 是可选的。在其中计算关联条件的 1 至 127 个区域。Criteria1、c
23、riteria2 等 Criteria1 是必需的，后续 criteria 是可选的。 形式为数字、表达式、单元格引用或文本的 1 至 127 个条件，用来定义将计算平均值的单元格。 例如，条件可以表示为 32、32、32、苹果 或 B4。312022年5月13日（4）DAVERAGE函数功能：功能：返回数据库或数据清单中满足给定条件的数据列中数值的平均值。公式：公式：=DAVERAGE(database, field, criteria)式中，database构成数据清单或数据库的单元格区域。field指定函数所使用的数据列；criteria为一组包含给定条件的单元格区域。可以为参数 cri
24、teria 指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。322022年5月13日（1）ABS函数功能：功能：返回数字的绝对值。公式：公式：=ABS(number)式中，number为需要计算其绝对值的实数。（2）SQRT函数功能：功能：计算一个正数的正平方根。公式：公式：=SQRT(number)式中，number为需要计算其平方根的正数。如果参数number为负值，函数SQRT返回错误值“#NLIM!”。（3）ROUND函数功能：功能：返回某个数字按指定位数舍入后的数字。公式：公式：=ROUND(numbel,num_digits)式中，number为需要进行舍入
25、的数字 4数学函数（数学函数（Abs/Sqrt/Round）332022年5月13日（1）MIN函数功能：功能：返回给定参数表中的最小值。公式：公式：=MIN(numberl,number2,numberN)式中，numberl，number2，numberN为需要从中找出最小值的1到30个参数。（2）MAX函数功能：功能：返回给定参数表中的最大值。公式：公式：=MAX(numberl,number2,numberN)式中，numberl，number2，numberN为需要从中找出最大值的1到30个参数;函数中的参数可以是数字、空白单元格、逻辑值或表示数值的文字串。5最大值与最小值函数（最大
26、值与最小值函数（Max/Min）342022年5月13日功能：功能：执行真假值判断，根据逻辑计算的真假值，返回不同的结果。可以使用IF函数对数值和公式进行条件检测。公式：=IF(logical_test,value_if_true,value_if_false)式中，logical_test为计算结果为TRUE或FALSE的任何数值或表达式；value_if_true是logical_test为TRUE时函数的返回值。如果logical_test为TRUE并且省略value_if_true，则返回TRUE。value_if_true 可以为某一个公式；value_if_false是logica
27、l_test为FALSE时函数的返回值。如果logical_test为 FALSE并且省略value_if_false，则返回FALSE。value_if_false可以为某一个公式。6条件函数（条件函数（If）352022年5月13日（1）AND函数功能：功能：表示逻辑与，当所有条件都满足时（即所有参数的逻辑值都为真时），AND函数返回TRUE，否则，只要有一个条件不满足即返回FALSE。公式：公式：=AND(条件l,条件2,条件N)（2）OR函数功能：功能：OR函数表示逻辑或，只要有一个条件满足时，OR函数返回TRUE，只有当所有条件不满足时才返回FALSE。公式：公式：=OR(条件l,条
28、件2,条件N)（3）NOT函数功能：功能：NOT函数的功能是对参数的逻辑值求反。公式：公式：=NOT(条件)提示：提示：这三个函数一般与IF函数结合使用。7逻辑函数（逻辑函数（AND/OR/NOT）362022年5月13日（1）LOOKUP函数功能：功能：返回向量（单行区域或单列区域）或数组中的数值。向量形式的LOOKUP函数的公式为：=LOOKUP(lookup_value,lookup_vector,result_vector)数组形式的LOOKUP函数的公式为：=LOOKUP(lookup_value,array)lookup_value 函数LOOKUP在第一个向量中所要查找的数值，它
29、可以为数字、文本、逻辑值或包含数值的名称或引用；lookup_vector只包含一行或一列的区域，lookup_vector的数值可以为文本、数字或逻辑值；result_vector只包含一行或一列的区域，其大小必须与lookup_vector相同。array包含文本、数字或逻辑值的单元格区域或数组 8查找函数（查找函数（Lookup/Vlookup/Hlookup/Match）372022年5月13日（2）VLOOKUP函数功能：功能：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用函数 VLOOKUP 代替函数 HLOOK
30、UP。公式：公式：=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)式中，lookup_value为需要在数据表第一列中查找的数值，lookup_value可以为数值、引用或文字串；table_array为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用；col_index_num 为 table_array中待返回的匹配值的列序号；range_lookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。 382022年5月13日（3）HLOOKUP函数 功能：功能：在表格或数值数组的首行查
31、找指定的数值，并由此返回表格或数组当前列中指定行处的数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数HLOOKUP。当比较值位于要进行数据查找的左边一列时，请使用函数 VLOOKUP。 公式：公式：=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)式中，row_index_num为table_array中待返回的匹配值的行序号。 392022年5月13日微软发布微软发布XLOOKUP函数函数:https402022年5月13日412022年5月13日422022年5月13日（4）MATCH函数功能
32、：功能：返回在指定方式下与指定数值匹配的数组中元素的相应位置。公式：公式：=MATCH(1ookup_value,1ookup_array,match_type )式中，lookup_value是需要在数据表中查找的数值，可以是数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用；lookup_array可能包含所要查找的数值的连续单元格区域，可以是数组或数组引用；match_type数字-1、0或1，它指明Excel如何在lookup_array中查找lookup_value。432022年5月13日（1）INDEX函数功能：功能：返回表格或区域中的数值或对数值的引用。INDEX函数
33、有以下两种形式：返回数组中指定单元格或单元格数组的数值公式：=INDEX(array,row_num,column_num)返回引用中的指定单元格公式：=INDEX(reference,row_num,column_num,area_num)array是单元格区域或数组常数；row_num是数组中某行的行序号，函数从该行返回数值。如果省略row_num，则必须有column num；column_num是数组中某列的列序号，函数从该列返回数值。如果省略column_num，则必须有row_num。 reference是对一个或多个单元格区域的引用，area_num选择引用中的一个区域，并返回该
34、区域中row_num和column_num的交叉区域。9引用函数（引用函数（Index/Address/Indirect）442022年5月13日（2）ADDRESS函数功能：功能：按照给定的行号和列标，建立文本类型的单元格地址。公式：公式：=ADDRESS(row_num,column_num,abs_num,al,sheet_text)式中，row_num为在单元格引用中使用的行号；column_num为在单元格引用中使用的列标；abs_num指明返回的引用类型，当为l或省略时为绝对引用；当为2时为绝对行号，相对列标；当为3时为相对行号，绝对列标；当为4时为相对引用；a1用以指明A1或Rl
35、Cl引用样式的逻辑值TRUE或FALSE。如果A1为TRUE或省略，函数ADDRESS返回Al样式的引用；如果Al为FALSE，函数ADDRESS返回RlCl样式的引用；sheet_tex为文本，指明作为外部引用的工作表的名称，如果省略sheet_text，则不使用任何工作表名。452022年5月13日（3）INDIRECT函数 功能：功能：返回由文字串指定的引用。此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身时，可使用此函数。 公式：公式：=INDIRECT(ref_text,a1)式中，ref_text为对单元格的引用，此单元格可以包含Al样式的引用
36、、RlCl样式的引用、定义为引用的名称或对文字串单元格的引用，如果ref_text不是合法的单元格的引用，函数INDIRECT返回错误值#REF!；a1参数同ADDRESS函数。需要注意的是，如果ref_text是对另一个工作簿的引用（外部引用），则被引用的那个工作簿必须已经打开。如果源工作簿没有打开，函数INDIRECT返回错误值#REF!。462022年5月13日（1）TRANSPOSE函数功能：功能：求矩阵的转置矩阵。公式：公式：=TRANSPOSE(array)式中，array为需要进行转置的数组或工作表中的单元格区域。（2）MINVERSE函数（Matrix Inverse的简写）功
37、能：功能：返回矩阵的逆矩阵。公式：公式：=MINVERSE(array)式中，array是具有相等行列数的数值数组。array可以是单元格区域、常数数组如1,2,3;4,5,6;7,8,9；或区域和常数数组的名称。注意：v返回值为数组的公式，必须以数组公式的形式输入，按Ctrl+Shift+Enter结束。v对于一些不能求逆的矩阵，函数 MINVERSE 将返回错误值 #NUM！。不能求逆的矩阵的行列式值为零。10矩阵函数（矩阵函数（Transpose/Minverse/Mmult） 472022年5月13日（3）MMULT函数（Matrix Multiple的简写）功能：功能：返回两数组的矩
38、阵乘积。结果矩阵的行数与array1的行数相同，矩阵的列数与array2的列数相同。公式：公式：=MMULT(array1,array2)式中，array1,array2是要进行矩阵乘法运算的两个数组。 482022年5月13日辅助生产费用分配辅助生产费用分配 1辅助生产费用分配概述 -2500500080014003000400180006006000300100010000ZYXZYXZYX-2500300060005000800140040018000600300100010000ZYXNMZYXNZYXM-1492022年5月13日2辅助生产费用分配模型的建立“Minverse”函数 “Mmult”函数 3.外部受益单位应分配额ZYX40020004005002000600160050003000180072004000管理部门车间一般乙产品甲产品