1、第8讲 EXCEL复杂公式,大学计算机二,2,1、数据序列名称定义 2、常用函数使用 3、函数嵌套使用 4、数组公式编辑 5、条件格式运用,主要内容,大学计算机二,3,1、给数据序列指定名称,用途：定义名称后可以根据名称来引用该单元格或区域，可以简化公式，增强公式可读性，还能方便引用。 方法：选择要定义的单元格区域，插入名称定义。或者直接在A1单元格上面的地址栏输入名称后回车。,操作演示见：教学示例1：给数据序列指定名称,大学计算机二,4,2、常用函数使用,求和函数：SUM、SUMIF、SUMPRODUCT 统计函数：AVERAGE、COUNT、 COUNTIF、 COUNTA、RANK、PE
2、RCENTRANK,大学计算机二,5,SUMIF：根据指定条件对若干单元格求和。 语法： SUMIF(range,criteria,sum_range) Range：为用于条件判断的单元格区域。 Criteria：确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、“32”、“32” 或 “apples”。 Sum_range： 是需要求和的实际单元格。 说明： 只有在区域中相应的单元格符合条件的情况下， sum_range 中的单元格才求和。如果忽略了 sum_range，则对区域中的单元格求和。,求和函数,操作演示见：教学示例2：条件求和函数,大学
3、计算机二,6,SUMPRODUCT ： 在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。 语法： SUMPRODUCT(array1,array2,array3, .) Array1, array2, array3, . 为 2 到 30 个数组，其相应元素需要进 行相乘并求和。 说明： 数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。,求和函数,大学计算机二,7,COUNT：返回包含数字的单元格的个数以及返回参数列表中的数字个数。 语法： COUNT(value1,valu
4、e2,.)：Value1, value2, . 为包含或引用各种类型数据的参数（1到 30个），但只有数字类型的数据才被计算。 说明：函数 COUNT 在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。,统计函数,大学计算机二,8,COUNTA：返回参数列表中非空值的单元格个数。 语法：COUNTA(value1,value2,.) Value1, value2, . 为所要计算的值，参数个数为 1 到 30 个。在这种情况下，参数值可以是任何类型，它们可以包括空字符 (“”)，但不包括空白单元格。 说明： 如果参数是数组或单元格引用，则数组或
5、引用中的空白单元格将被忽略。如果不需要统计逻辑值、文字或错误值，请使用函数 COUNT。,统计函数,大学计算机二,9,COUNTIF：计算区域中满足给定条件的单元格的个数。 语法：COUNTIF(range,criteria)：Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。 例如，条件可以表示为 32、“32“、“32“ 或 “apples“。,统计函数,操作演示见：教学示例3：统计函数的运用,大学计算机二,10,RANK：一个数值在一组数值中的排位的函数。数值的排位是与数据清单中其他数值的相对大小
6、。语法：RANK(number,ref,order) Number为需要找到排位的数字；Ref 为包含一组数字的数组或引用。Order为一数字用来指明排位的方式。 如果 order 为 0 或省略，则Excel 将 ref 当作按降序排列的数据清单进行排位。 说明：函数 RANK 对重复数的排位相同。例如，在一列整数里，如果整数 10 出现两次，其排位为 5，则 11 的排位为 7（没有排位为 6 的数值）。,统计函数,大学计算机二,11,PERCENTRANK：求特定数值在一个数据集中的百分比排位的函数。此函数可用于查看特定数据在数据集中所处的位置。例如，可以使用函数 PERCENTRANK
7、 计算某个特定的能力测试得分在所有的能力测试得分中的位置。 语法：PERCENTRANK(array,x,significance) Array为彼此间相对位置确定的数字数组或数字区域。X为数组中需要得到其排位的值。Significance为可选项，表示返回的百分数值的有效位数。如果省略，函数 PERCENTRANK 保留3位小数。,统计函数,操作演示见：教学示例4：排序函数的运用,大学计算机二,12,3、函数嵌套,函数嵌套：是指将一个函数作为另外一个函数的参数使用。它返回的数值类型必须与参数使用的数值类型相同。,操作演示见：教学示例5：职工考勤统计,大学计算机二,13,4、数组公式,简单公式
8、： （1）含义：利用其他单元格中的数据进行加、减、乘、除 等算术运算的公式，称为“简单公式”。 （2）编辑方法：单击要输入公式的单元格使之成为活动单元格单击“公式编辑区” 在公式编辑区中输入公式。注意一定要在公式前面写上“=”。,大学计算机二,14,数组：数组就是一个具有维度的集合 。比如：一维数组、二维数组、多维数组。数组的表示一般为“ ”所包括（一维和二维数组）。Excel中也不例外，如果你想直接表示一个数组，也必须用“ ”括起来。,4、数组公式,大学计算机二,15,4、数组公式,数组公式：在Excel中，凡是以半角符号“=”开始的单元格内容都被Excel认为是公式，其只能返回一个结果。而
9、数组公式可以占用一个单元格，也可以占用多个单元格。它对一组数或多组数进行多重计算，并返回一个或多个结果数组公式可以返回一个或者是多个结果，而返回的结果又可以是一维或二维的，换句话说，Excel中的数组公式返回的是一个一维或二维的数组集合。,（1）含义： 数组公式可以同时进行多重计算并返回一种或多种结果。数组公式对两组或多组被称为数组参数的数值进行运算。每个数组参数必须有相同数量的行和列。,大学计算机二,16,=IF(1,0,D2:D8,C2:C8)这个公式里的数组1,0的括号就是用户自己输入的。,（2）编辑方法：输入公式完毕后，按CTRL+SHIFT+ENTER 组合键确认。 注意： 不要自己
10、键入花括号，否则，Excel认为输入的是一个正文标签。,如：普通公式：,数组公式：,花括号混合使用：,大学计算机二,17,（3）特点 ：数组公式的特点就是所引用的参数是数组参数，包括区域数组和常量数组。执行多重计算,它返回的是一组数据结果。 详细的说：数组公式的参数是数组，即输入有多个值；输出结果可能是一个，也可能是多个这一个或多个值是公式对多重输入进行复合运算而得到的新数组中的元素。,4、数组公式,大学计算机二,18,问题1：在D2:D4求出商品的销售金额。,在D2单元格输入公式“=B2*C2”，下拉公式即可。,选中D2:D4输入公式“=B2:B4*C2:C4”，三键结束输入数组公式，即可得
11、到同样结果。,大学计算机二,19,问题2：在F1求出商品的销售总金额。,大学计算机二,20,什么时候会用到数组公式？,当运算中存在着一些只有通过复杂的中间运算过程才会等到结果的时候，就需要使用数组公式了。,操作演示见：教学示例6：数组公式入门,数组公式使用小结： （1）三键输入数组公式。 （2）数组公式同时进行多个计算，可返回一个或多个结果。 （3）多单元格数组公式需选区多个单元格进行输入，多单元格数组公式具有保护公式的作用。 （4）数组公式可以完成复杂的中间运算得到最终想要的运算结果。,大学计算机二,21,5、条件格式,条件格式可以在很大程度上改进电子表格的设计和可读性，允许指定多个条件来确定单元格的行为，根据单元格的内容自动地应用单元格的格式。可以设定多个条件，但Excel只会应用一个条件所对应的格式，即按顺序测试条件，如果该 单元格 满足某条件，则应用相应的格式规则，而忽略其他条件测试。,操作演示见：教学示例5：职工考勤统计,
