第八讲 Excel数组公式

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