1、第2章 公式与函数,经济统计与分析软件应用基础,学习要点,掌握公式的使用方法，能够区分绝对地址和相对地址重点掌握数组公式的用法了解函数的分类，重点掌握逻辑函数、查找和引用函数、统计函数的使用方法。,目 录,2.1 公式2.1.1 认识Excel中的公式2.1.2 运算符及其优先级2.1.3 引用单元格2.1.4 使用数组公式,2018/3/7,4,目 录,2.2 函数2.1.1 函数简介2.2.2 使用函数2.2.3 日期和时间函数2.2.4 逻辑函数2.2.5 查找和引用函数2.2.6 数学和三角函数2.2.7 统计函数2.2.8 文本函数2.2.9 数据库函数,2018/3/7,5,2.1
2、 公式,公式以等号“=”开始。公式中可以包含运算符、常量数值、单元格引用、单元格区域引用、系统内部函数等。,2.1.1 认识Excel中的公式,Excel运算符的4种类型：算术运算符比较运算符字符运算符引用运算符,2.1.2 运算符及其优先级,1)算术运算符用于完成基本的数学运算，如加法、减法和乘法，结果仍然为数值型。,1.运算符,2)比较运算符用于比较两个值，结果将是一个逻辑值，即不是TRUE(真)就是FALSE(假)。,3)字符运算符：&（连接）将两个文本型字符串连接成一个字符串。如“North” & “wind”，结果是”Northwind”。4)引用运算符,如果公式中同时用到多个运算符
3、，Excel将按下表中所示的顺序进行运算，对于相同优先级的运算符，Excel将从左到右进行计算。如果要修改计算的顺序，可把要先计算的部分用一对圆括号括起来。,2.运算符的优先级,1.单元格引用的一般式工作表名!单元格引用或工作簿名工作表名!单元格引用在引用同一工作簿单元格时，工作簿名可以省略，在引用同一工作表时，工作表名可以省略。 如果同时引用多个工作簿相同区域中的数据，则可以使用“:”标识工作表的范围。如Sheet1:Sheet4。,2.1.3 引用单元格,2.相对地址与绝对地址1）相对地址单元格的相对引用（例如A1）是基于单元格的相对位置。复制公式时，公式所在单元格的位置改变，相对引用也随
4、之改变。相对引用是直接用列号和行号的组合来表示单元格地址，如：A1、B3。,默认情况下，公式使用相对引用。,在D1中输入：= A1+B1，显示30。那么当将D1公式复制到D2，则D2中公式及显示值是什么？D2中的公式是：= A2+B2，显示的值是90,90,30,在D1中输入：= SUM( A1:B2 )，显示120。那么当将D1公式复制到E2，则E2中公式及显示值是什么？E2中的公式是：= SUM( B2:C3 )显示的值是280,280,120,在C1中输入：= A1+B1，显示30。那么当在A列后插入一个新列，原来的C1成为 D1，其中的公式将是什么？显示值是多少？,D1中的公式是：=
5、A1+C1，显示的值是30,30,30,在C1中输入：= SUM( A1:B2 )，显示120。那么当在A列后插入一个新列，原来的C1成为 D1，其中的公式将是什么？显示值是多少？,D1中的公式是：= SUM( A1:C2 )，显示的值是120,120,120,在C1中输入：= SUM( A1:B2 )，显示120。那么当在B列后插入一个新列，原来的C1成为 D1，其中的公式将是什么？显示值是多少？,D1中的公式是：= SUM( A1:B2 )，显示值仍是120,120,120,在C1中输入：= SUM( A1:B1 )，显示30。如果将C1的公式复制到C2，则C2中的公式将是什么？显示值是多
6、少？C2中公式是：= SUM( A2:B2 )，显示值是90如果将C1的公式复制到B3，则B3中将显示什么？B3中显示：#REF! （单元格引用无效）,30,90,#REF!,2018/3/7,2）绝对地址单元格的绝对地址（格式为$A$1，即在行列标志前加“$”符号）总是在指定位置引用单元格。复制公式时，公式所在单元格的位置改变，绝对地址保持不变，绝对地址不会随单元格的不同而变化。提示：使用F4功能键，公式中选中的地址会在相对地址、绝对地址和混合地址之间切换。,3）混合地址混合地址具有绝对列和相对行，或是绝对行和相对列。绝对列采用$A1、$B1等形式，绝对行采用A$1、B$1等形式。如果公式所
7、在单元格的位置改变，则相对引用改变，而绝对引用不变。如果多行或多列地复制公式，相对引用自动调整，而绝对引用不作调整。如九九乘法表。,在D1中输入：= $A$1+B2，那么当将D1的公式复制到E2，则E2中显示的是什么？E2中的公式是：= $A$1+C3，显示的值是100在D1中输入：= $A1+$B2，那么当将D1的公式复制到E2，则E2中显示的是什么？E2中的公式是：= $A2+$B3，显示的值是120在D2中输入：$A$1+$A$2，那么当将D2中的内容复制到E3，则E3中显示的值是什么？E3中显示是：$A$1+$A$2,100,$A$1+$A$2,60,30,120,【例】在B2单元格中
8、输入一个含混合地址的公式，并通过公式复制可以快速产生一个九九乘法表。在B2单元格中应该输入一个什么公式第2章公式和函数实例1九九乘法表,B2单元格中的公式为：= $A2*B$1,数组公式在Excel中，公式只能返回一个结果。数组公式是用于建立可以产生多个结果或对可以存放在行和列中的一组参数进行运算的单个公式。数组公式的特点就是可以执行多重计算，返回一个或者是多个结果。数组公式是以数组为参数，在进行计算时需按Ctrl+Shift+Enter组合键执行，数组公式的外面会自动加上大括号“ ” 。如:=SUM(F2:F78*G2:G78)数组公式中的参数必须为一连续的单元格区域，形成“矩形”区域,如
9、$A$1:$D$5。,2.1.4 使用数组公式,【例】在B7单元格中求销售合计金额。第2章公式和函数实例 2数组公式计算,在B7单元格中输入：= SUM( B2:B5 * C2:C5 ),注意： 结束输入时必须按Ctrl + Shift + Enter组合键。,2、数组公式举例（1）利用数组公式进行分类统计【例】对不同商品分别计算其销售金额。第2章公式和函数实例 3利用数组公式求和,在C12单元格中输入公式： = SUM( IF( A2:A10 = 商品1 , B2:B10 * C2:C10 , 0 ) ),公式分析：C12单元格中的公式： = SUM( IF( A2:A10 = 商品1 ,
10、B2:B10 * C2:C10 , 0 ) )此公式的功能是：将A2:A10区域中数据为“商品1”的单元格的相应B列与C列相乘后求和。条件判断使用IF函数；求和时使用SUM函数。A2:A10区域中数据的判断使用IF函数，符合条件的则相应的B列和C列相乘，否则按零参加求和。C13单元格按上述方法处理。C14是对C12和C13求和，即：= C12 + C13。,IF函数语法格式： IF( logical , value1 , value2 )功能： 根据logical的值，返回value1或value2的值。说明：如果logical的值为TRUE，则返回value1的值；如果value1为空（lo
11、gical后的逗号必须存在），则返回0（零）；如果要返回TRUE，则必须要将value1设置为：TRUE。如果logical的值为FALSE，则返回value2的值；如果value2为空（value1后的逗号存在），则返回0（零）；如果value2被忽略（即：value1后的逗号不存在），则返回FALSE。,（2）利用数组公式排名次【例】根据平均成绩排名次。第2章公式与函数实例4利用数组公式统计名次例题分析：在I3至I9单元格中输入了数组公式： = MATCH( H3:H9 , LARGE( H3:H9 , ROW( 1:7 ) ) , -1 )公式中使用了3个函数： 用ROW函数产生数值1至
12、7 使用LARGE函数进行数据的排序 用MATCH函数来查找指定数值的位置,ROW函数语法格式： ROW( reference )功能： 返回引用的行号。说明：参数reference为需要得到其行号的单元格或单元格区域。如果省略参数reference，则是对函数ROW所在行的引用。,【例】 工作表中的数据如下图所示：,如果在C1中输入： =ROW(A5 )则C1中显示的值为：如果在C3中输入： =ROW( )则C3中显示的值为：,5,3,5,3,LARGE函数语法格式： LARGE( array , k )功能： 返回数据集array中第k个最大值。说明：参数array为需要从中选择第k个最大
13、值的数组或单元格区域。参数k为返回值在区域中按从大到小排序的排序次序。,如果数组或区域为空（即无数据），则函数返回错误值#NUM!。如果k小于等于0或k大于区域中的数据个数，则函数返回错误值#NUM!（公式或函数中某些数字有问题）。若区域中数据个数为n，则LARGE( array , 1 )返回最大的值，LARGE( array , n )返回最小的值。,【例】 工作表中的数据如下图所示：,如果在B4中输入：=LARGE( A1:C3 , 1 )则B4中显示的值为：如果在C4中输入：=LARGE( A1:C3 , 12 )则C4中显示的值为：,98,#NUM!,98,#NUM!,SMALL函数
14、语法格式： SMALL( array , k )功能： 返回数据集array中第k个最小值。说明：参数array为需要从中选择第k个最小值的数组或单元格区域。参数k为返回值在区域中按从小到大排序的排序次序。,【例】 工作表中的数据如下图所示：,如果在B4中输入：=SMALL( A1:C3 , 1 )则B4中显示的值为：如果在C4中输入：=SMALL( A1:C3 , 12 )则C4中显示的值为：,19,#NUM!,19,#NUM!,MATCH函数语法格式： MATCH( value , array , type )功能： 返回在指定方式下与指定数值匹配的数组中元素的相应位置。说明：参数valu
15、e为需要在数据表中查找的数值。可以是数字、文本或逻辑值，或者是对它们所在单元格的引用。参数array为所要查找的数据可能所在的数组或连续的单元格区域（必须是一维数组或同一行或同一列的单元格区域）。,参数type为查找的方式，为数字1、0或-1。 如果为1，则查找小于或等于value的最大值，数组array必须按升序排列； 如果为0，则查找等于value的第一个数值，数组array可以按任意顺序排列； 如果为-1，则查找大于或等于value的最小值，数组array必须按降序排列； 参数type缺省时，系统默认为1。,函数返回的是查找值（value）在区域（array）中的位置，而不是查找值的本身
16、。函数在查找时不区分字母的大小写。如果查找的是文本数据，且type是0，则value可以包含通配符（ * 或 ? ）。如果查找不成功，则返回错误值#N/A（公式或函数中没有可用的数值）。,【例】 工作表中的数据如下图所示：,如果在C1中输入：=MATCH(98 , A1:A8 , 0 )则C1中显示的值为：如果在C3中输入：=MATCH( 70 , B1:B8 , 1 )则C3中显示的值为：,5,4,5,4,公式分析： 例题中，从I3单元格至I9单元格输入了以下公式：=MARCH( H3:H9 , LARGE( H3:H9 , ROW( 1:7 ) ) , -1 )计算时的步骤为：计算公式RO
17、W( 1:7 )，其功能是在I3:I9区域内产生1至7的数。,计算公式LARGE( H3:H9 , ROW( 1:7 ) )其功能是将H3:H9区域内的数据从大到小排列，并显示在I3:I9区域内。,计算公式 MARCH( H3:H9 , LARGE( H3:H9 , ROW( 1:7 ) ) , -1 )其功能是查找H3:H9区域内的数（即平均分）在区域I3:I9内的位置（即排在第几位，也是其名次）。,RANK函数语法格式： RANK( number , ref , order )功能： 返回number在ref中的排位。,本题也可以使用rank函数排序,说明：number是要找到排位的数字；
18、ref为数字列表或对数字列表的引用。ref中的非数值型数据将被忽略。order为一数字，指明排位的方式。若order为0或省略，对数字的排位是基于ref的按照降序排列的列表；若order不为0，则数字的排位是基于ref的按照升序排列的列表。,【例】 工作表中的数据如下图所示：,如果在C1中输入：=RANK(78 , A1:A8 , 1 )则C1中显示的值为：如果在C3中输入：=RANK( 78 , A1:A8 )则C3中显示的值为：,5,4,5,4,注意： 函数RANK对重复数的排位相同，但重复数的存在将影响后续数值的排位。【例】 工作表中的数据如下图所示：,如果在C1中输入：=RANK(89
19、 , A1:A6 )则C1中显示的值为：如果在C3中输入：=RANK( 78 , A1:A6 )则C3中显示的值为：,2,4,2,4,常见错误信息# # # #错误原因： 输入到单元格中的数据长度超过了列的宽度，单元格中无法容纳。# div / 0错误原因： 公式中的分母为零。# N / A错误原因： 函数或公式中没有可用的数值。,2.1.5 公式审核及出错检查,# NAME ?错误原因： 公式中使用了Excel不能识别的文本。# NULL !错误原因： 试图为两个并不相交的区域指定交叉点。# NUM !错误原因： 公式或函数中的某些数字有问题。,# REF !错误原因： 单元格引用无效。#
20、VALUE !错误原因： 公式中的参数或运算对象的数据类型错误。,2.2 函 数,函数的结构以等号（=）开始。后面紧跟着函数名称和左圆括号，然后以逗号分隔该函数的参数，最后是右括号。如右图所示。,2.2.1 函数简介,函数的结构图,1.使用工具栏插入函数,2.2.2 使用函数,2.使用“插入”函数对话框,3.在单元格中浏览选择函数,1. DATE函数(P61)用途：返回代表特定日期的序列号(3个数转化成日期)。语法：DATE(year,month,day)参数：year参数的值可以包含14位数字；month代表每年中月份的数字；day代表一月中第几天的数字。示例：=DATE(2008,7,8)
21、，返回39637，该序列号表示2008-7-8。,2.2.3 日期和时间函数,2. YEAR函数(P61)用途：返回某日期的年份，返回值为19009999。语法：YEAR(serial_number)参数：serial_number为要查找年份的日期或日期所在的单元格地址。示例：假设A1单元格中是2014-8-9，则YEAR(A1)返回值为2014。,3. MONTH函数(P61)用途：返回某日期中的月份，它是112之间的整数。语法：MONTH(serial_number)参数：serial_number表示一个日期值。示例：假设A1单元格中的日期是2015-12-25，则MONTH(A1)返
22、回值为12。4. DAY函数(P61)用途：返回某日期中的天数，它是131之间的整数。语法：DAY(serial_number)参数：serial_number表示一个日期值。示例：假设A1单元格中的日期是2015-12-25，则DAY(A1)返回值为25 。,5. TODAY函数(P62)用途：返回系统的当前日期。语法：TODAY()参数：无。示例：如果知道某人的出生日期为2000-10-5，可以使用公式=YEAR(TODAY()-YEAR(2000-10-5)计算出其年龄。,6. WEEKDAY函数(P63)用途：返回代表一周中第几天的数字，在默认的情况下它的值为1(星期天) 7(星期六)
23、之间的一个整数。语法：WEEKDAY(serial_num,return_type)参数：serial_num代表日期；return_type为一数字，确定星期计算从哪一天开始 ，数字为1或省略，则表示一周的第一天为星期天；数字为2，则表示一周的第一天为星期一。示例：A2单元格中是2014-2-14(星期五)，则“=WEEKDAY(A2)”返回6， “=WEEKDAY(A2,2)”返回5 。,1. AND函数(P66)用途：所有参数的计算结果为TRUE时，返回 TRUE；只要一个参数的计算结果为FALSE，即返回 FALSE。语法： AND(logical1,logical2, )参数：log
24、ical1是要检验的第一个条件，logical2，可选，是要检验的其他条件，各条件值可为 TRUE 或 FALSE。最多可包含255个条件。示例：如果A2单元格的值为50，则“=AND(1A2,A2100)”的结果为TRUE， “=AND(1A2,A230)”的结果为FALSE。,2.2.4 逻辑函数,2. OR函数(P67)用途：在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE；每一参数的逻辑值都为 FALSE，才返回 FALSE。语法： OR(logical1,logical2, )参数：参数同AND函数。示例：如果A2单元格的值为50，则“=OR(180)”的结果为FALS
25、E。,3. NOT函数(P67)用途：对参数值求反。当要确保一个值不等于某一特定值时，可以使用 NOT 函数。语法：NOT(logical)参数：logical是计算结果为TRUE或FALSE的任何值或表达式。示例：“=NOT(FALSE)”用于对FALSE求反，结果为TRUE。,4. IF函数(P67)用途：返回当前系统日期和时间。语法：IF(logical_test, value_if_true,value_if_false)参数： logical_test：条件表达式，当为TRUE，函数返回value_if_true的值；当为FALSE，函数返回value_if_false的值。示例：“
26、=IF(A2=90)，返回B2到B27单元格区域中语文成绩大于等于90分的人数。,5 . MAX函数(P84)用途：返回一组值中的最大值。语法： MAX(number1,number2,)参数：number1，number2，是要从中找出最大值数字参数(1255) 。示例：“=MAX(-8,45,12.5)”返回最大值45 。6 . MIN函数(P84)用途：返回一组值中的最小值。语法： MIN(number1,number2,)参数与MAX函数相同。示例：“=MIN(45,TRUE)”返回最大值1 。,7. RANK函数(P85)用途：返回一个数字在数字列表中相对于其他数值的大小排位。语法：
27、RANK(number,ref,order)参数：number是要找到排位的数字。ref数字列表数组或对数字列表的引用。order一数字，指明数字排位的方式，0或省略：降序；不为0：升序。示例：=RANK(8,A1:A5) 若A1到A5的值为：5,3,10,8,-90返回值2 。,在H2单元格中输入公式：=RANK(F2,$F$2:$F$27)可以统计出学生总分的排名情况。,1. LEN函数(P88)用途：返回文本串中的字符个数。语法：LEN(text)参数：text是要查找其长度的文本。示例：=LEN( aBd会计F )返回值为6。2 . LEFT函数(P88)用途： 基于所指定的字符数返回
28、文本串中的第一个或前几个字符。语法：LEFT(text,num_chars)参数：text是包含要提取字符的文本串。num_chars指定函数要提取的字符数，它必须=0。示例：=LEFT(会计电算化,2)返回值为“会计”。,2.2.8 文本函数,3. MID函数(P89)用途：返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。语法：MID( text, start_num, num_chars)参数：text是包含要提取字符的文本串；start_num是文本中要提取的第一个字符的位置；num_chars指定从文本中返回字符的个数。示例：=MID(计算机等级Access考试,4,8)
29、返回“等级Access”。4 . RIGHT函数(P89)用途： 基于所指定的字符数返回文本串中的最后一个或多个字符。语法：RIGHT(text,num_chars)参数同LEFT函数。示例：=RIGHT(会计电算化,3)返回值为“电算化”。,1. DSUM函数(P100)用途：对数据库中满足条件记录的字段进行数字求和。语法：DSUM(database,field,criteria)参数：database表示构成列表或数据库的单元格区域；field用于指定函数所使用的列(列标签用双引号括起来)；criteria为包含指定条件的单元格区域。,2.2.9 数据库函数,示例：数据如下图所示，在E2中输入公式： =DSUM(A4:F20,“订单金额”,D1:D2)，则返回运货公司为联邦货运的订单金额总额。,2. DAVERAGE函数(P101)用途：计算满足给定条件数据库项数值的平均值。语法：DAVERAGE(database,field,criteria)参数与DSUM相同。,示例：数据如右图所示，在D2中输入公式： =DAVERAGE(A4:D21, D1,B1:B2)则返回职称为教授的年龄的平均值。,
