Excel公式与函数详解幻灯片

1、第二章公式与函数，经济统计与分析软件的应用基础，1，2020/7/12，学习要点，掌握公式的用法，区分绝对地址与相对地址，掌握数组公式的用法，了解函数的分类，掌握逻辑函数、搜索与引用函数和统计函数的用法。2，2020/7/12，目录，2.1公式2.1.1理解Excel中的公式2.1.2运算符及其优先级2.1.3引用单元格2.1.4使用数组公式，3，2020/7/12，2，4，目录，2.2函数2.1.1函数介绍2.2.2使用函数2.2.3日期和时间函数2.2.4逻辑函数2.2.5查找和引用函数2.2.6数学和三角函数公式可以包括运算符、常数值、单元格引用、单元格区域引用、系统内部函数等。2.1.

2、1了解Excel运算符的四种类型：算术运算符比较运算符字符运算符引用运算符，2.1.2运算符及其优先级，6，2020/7/12，1)算术运算符用于完成基本的数学运算，如加法、减法和乘法，结果仍然是数字。1。运算符，7，2020/7/12，2)比较运算符用于比较两个值，结果将是一个逻辑值，即“真”或“假”。8，2020/7/12，3)字符运算符：3，4，1，2”)返回数组常量的第一行和第二列的值2。2.2.5查找和引用功能，63，2020/7/12，2。选择函数(P69)目的：使用index_num返回值参数列表中的值。语法：CHOOSE(index_num，value1，value2)，参数：

3、index_num是指定选定值的参数。Index_num必须是1到254之间的数字，或者是包含1到254的公式或单元格引用。函数CHOOSE返回值1；如果index_num为1；如果是2，函数CHOOSE返回值2，依此类推。如果index_num是一个十进制数，它将在使用前被截断和舍入。例如：“=CHOOSE(2，三星，苹果)”返回“苹果”。64，2020/7/12，3。VLOOKUP函数(P71)目的：在一个表或数值数组的第一列中找到指定的值，并返回该表或数组当前行中指定列的值。语法：VLOOKUP (lookup_value，table _ array，col _ index _ num，

4、range _ lookup)参数：lookup_value是要在第一列中搜索的值，可以是数值、引用或文本字符串。Table_array是您需要在其中查找数据的数据表。65，2020/7/12，2，Range_lookup是一个逻辑值，指定函数VLOOKUP是精确匹配还是近似匹配：TRUE还是省略，并查找近似匹配值。真，与假完全匹配。第2章公式和函数示例Vlookup函数=VLOOKUP (A6，A2:H9，5，真)，66，4。匹配功能(P71)目的：您可以在单元格区域中搜索指定的项目，然后返回该项目在单元格区域中的对应位置。如果需要获取单元格区域中某项的位置，而不是该项本身的位置，应该使用M

5、ATCH函数。语法：匹配(lookup_value，lookup _ array，match _ type)参数：Lookup _ value是要在数据表中找到的数值，它可以是一个值(数字、文本或逻辑值)或对某个值的单元格引用；Lookup_array是要搜索的单元格范围；67，2020/7/12，4。MATCH函数参数：match_type是可选的，可以是数字-1、0或1。此参数的默认值为1。1或省略，则查找小于或等于lookup_value的最大值，lookup_array必须按升序排序。0，查找等于lookup_value的第一个值，lookup_array可以按任何顺序排列。(完全匹配

6、)-1，查找大于或等于lookup_value的最小值，lookup_array必须按降序排序。示例：“=MATCH(b，a，b，c，0)返回2。68，2020/7/12，5。偏移功能(P73)目的：使用指定的参考作为参考框架，并获得具有给定偏移的新参考。返回的引用可以是单元格或单元格区域，并且可以指定返回的行数或列数。语法：OFFSET (Reference，row，cols，height，width)中文：offset (reference area，行数，列数，height，width)，69，2020/7/12，参数含义：Reference:Reference area as OFFS

7、ET Reference system .引用必须是对一个单元格或一系列相连单元格的引用。行数：相对于偏移参考系统左上角单元格上移(下移)的行数。Cols:相对于偏移参考系统左上角单元格的左(右)偏移列数。Height: height，即要返回的引用区域的行数。高度必须是正数。Width: width，即要返回的引用区域中的列数。宽度必须是正数。70，2020/7/12，在单元格C7中，输入公式：=SUM(偏移量(C2，1，2，3，1)，结果为18。71，2020/7/12，1。求和和汇总函数1)求和函数(P74)目的：求和计算。语法：SUM(number1，number2)，参数：numbe

8、r1，number2是要求和的参数，参数个数为1255。示例：“=SUM(1，2，3)”表示将三个数字1、2和3相加，结果为6。=SUM(A2:A4，15)是将A2、A3和A4中的数字相加，然后将结果加到15。2.2.6数学和三角函数，72，2020/7/12，2)SUMMPRODUCT函数(P75)目的：在给定的数组组中，将数组之间的对应元素相乘，并返回乘积之和。语法：sumproduct (array1，array2，array3)参数：array1，array2，array3是要相乘和求和的数组参数，参数个数为1255。示例：“=sumproduct (a1:b4，c1:d4)”表示将两

9、个数组的所有元素相应相乘，然后将乘积相加。也就是说，a1 C1 B1 D1 a2 C2 b2d 3a 3c 3b 3d 4a 4d 4。73，2020/7/12，2。舍入和舍入函数1) INT函数(P76)目的：将数字调低为整数，即返回不大于初始值的整数。语法：INT(number)参数：number是需要向下舍入的实数。示例：“=INT(7.8)”导致7；=INT(-5.3)结果为-6。74，2020/7/12，2) ROUND函数(P76)目的：返回一个按指定位数舍入的数字。语法：ROUND(number，num_digits)参数：number是要舍入的数字；Num_digits按此数字

10、对数字参数进行舍入。示例：“=ROUND(3.26，1)”导致3.3；“=ROUND(68.7，0)”的结果是69。“=ROUND(-53.65，1)”的结果是-53.7。75，2020/7/12，3。随机函数1)随机函数(P78)目的：返回统一发布的大于或等于0且小于1的随机实数，每次计算都会生成一个新的随机实数。语法：RAND()参数：无。76，2020/7/12，4。除两个数得到余数或整数1)模函数(P78)目的：返回除两个数的余数，其符号与除数相同。语法：MOD(数字，除数)参数：数字是被除数，除数是除数。示例：“=MOD(5，2)”结果为1。=MOD(7，-3)结果为-2；=MOD(

11、-7，3)结果为2。77，2020/7/12，5。乘积和指数函数1) EXP函数(P79)目的：返回e的n次幂。语法：EXP(数字)参数：数字是应用于基数e的指数。示例：=EXP(1)返回e的1的2.718281828次幂。=EXP(2)将e返回2的幂。78，2020/7/12，6。LOG 1)LOG函数(P80)目的：根据指定的基数返回一个数的对数。语法：LOG(数字，基数)参数：数字是一个正实数，其对数将被计算；可选底数是对数的底数。如果省略基数，则假定其值为10。示例：“=LOG(10)”返回以10为底的对数，结果为1；=LOG(9，3)返回基于3的9的对数，结果是2。79，2020/7

12、/12，2) LN函数(P80)目的：返回数字的自然对数(以e为基数)。语法：LN(数字)参数：数字是一个正实数，其自然对数将被计算。示例：“=LN(35)”返回35的自然对数。80，2020/7/12，1。AVERAGE函数(P82)目的：计算所有参数的算术平均值。语法：AVERAGE(number1，number2)，参数：number1，number2，这是计算平均值(1255)的参数。示例：“=AVERAGE(A1:A5，5)”表示用数字5对单元格区域A1至A5中的数字进行平均。，2.2.7统计函数，81，2020/7/12，并在G2单元格中输入公式：=AVERAGE($B$2:$E$

13、2)，以计算每门课程中每个学生的平均分数。82，2020/7/12，2。COUNT函数(P83)目的：返回数字参数的个数。语法：count(value1，value2)，Parameters: value1，value2是包含或引用各种类型数据的参数(1255)，但仅计算数字类型数据的数量。例如：如果A1=8，A2=中国，A3=真，A4=，A5=-5，“=COUNT(A1:A5)”返回2。83，2020/7/12，3。counta函数(P83)的目的：返回参数组中非空值的个数。语法：COUNTA(值1，值2)，参数：值1，值2，这些参数包含或引用除空白单元格(1255)之外的任何类型的数据。例

14、如：如果A1=8，A2=中国，其他单元格为空，则“=COUNTA(A1:A5)”返回2。84，2020/7/12，4。COUNTIF函数(P83)目的：计算某个区域中满足给定条件的单元格数。语法：COUNIF(范围，标准)参数：范围是需要计算满足条件的单元格数的区域；数字、表达式或文本形式的条件。85，2020/7/12，示例：在单元格B30中输入公式：=COUNTIF(B2:B27，=90)，并将B2单元格中中文分数大于或等于90分的人数返回B27。86，2020/7/12，5。最大函数(P84)目的：返回一组值中的最大值。语法：MAX(数字1，数字2)，参数：数字1，数字2，这是从中找到最

15、大值的数字参数(1255)。示例：“=MAX(-8，45，12.5)”返回最大值45。6.最小函数(P84)目的：返回一组值中的最小值。语法：最小(数字1，数字2)，参数与最大函数相同。示例：“=最小值(45，真)”返回最大值1。87，2020/7/12，7。RANK函数(P85)目的：返回数字列表中一个数字相对于其他数值的大小排名。语法：RANK(number，ref，order)参数：number是查找RANK的数字。引用数字列表的数组或数字列表的引用。顺序是一个数字，表示数字的排列方式，0或省略：降序；不是0:升序。例如：=RANK(8，A1:A5)，如果A1到A5的值是：5，3，10，

16、8，-90，则返回值2。88，2020/7/12，在H2单元格中输入公式：=RANK(F2，$F$2:$F$27)，以计算学生总成绩的排名。89，2020/7/12，1。LEN函数(P88)目的：返回文本字符串中的字符数。语法：LEN(text)参数：text是要找到其长度的文本。示例：=LEN(aBd accounting f)返回值6。2.LEFT函数(P88)用于根据指定的字符数返回文本字符串中的前几个字符。语法：LEFT(text，num_chars)参数：text是包含要提取的字符的文本字符串。Num_chars指定函数要提取的字符数，必须等于0。例：=LEFT(会计电算化，2)返回值为“会计”。2.2.8文本功能，90，2020/7/12，3。MID函数(P89)目的：从用户指定的文本字符串中的指定位置返回特定数量的字符。语法：MID(文本，起始字符数，字符数)参数：文本是包含要提取的字符的文本字符串；Start_num是要在文本中提取的第一个字符的位置；Num_chars指定从文本返回的字符数。示例：=MID(计算机等级访问考试，4，8)返回“等级访问”。4.右函数(P89)用于根据指定的字符数返回文本字符串中的最后一个或多个字符。语法：RIGHT(文本，数字字符)参数与LEFT函数相