• 函数365之SUMPRODUCT函数:不仅仅是SUM+PRODUCT,我还会多条件求和、查找、计数

    Excel函数2017-4-240评论254
    函数名称:SUMPRODUCT 函数作用:SUMPRODUCT函数可用于将一组或多组数组间对应的元素相乘,并返回乘积之和。 函数语法:SUMPRODUCT(参数1, 参数2,参数3,…,参数255) 注意事项: 1、每一个参数都是其相应元素需要进行相乘并求和的数组参数,其中第1参数必选,其他参数可选,最多可支持255个参数; 2、数组参数必须具有相同的维数,否则,函数SUMPRODUCT将返回错误值#VALUE!; 3、函数SUMPRODUCT将非数值型的数组元素作为 0 处理; 4、任意参数中都不允许出现错误值,只要有任意一组参数出现任意一个或多个错误值,则SUMPRODUCT结果为错误值; 5、虽然像数组公式需要用CTRL+SHIFT+回车键三键结束,但是SUMPRODUCT执行的都是数组公式的运算,所以如果要用到这个函数需要尽可能的选择合理的区域范围,否则公式运算将会非常缓慢。 函数应用: 实例1、根据下表A、B两列的数量和金额计算出总金额,结果如D2列所示。(一般应用) 函数公式:D2单元格输入 =SUMPRODUCT(A2:A11,B2:B11) 解释说明: 1、要计算总金额,就要将数量分别乘以相应金额然后相加A2*B2+A3*B3+A4*B4+…+A11*B11; 2、这里我们可以用SUMPRODUCT函数更快更方便的对数量和金额进行相乘后求和,其中A2:A11为第1参数,B2:B11为第2参数。 实例2、根据下表的销量信息,计算A产品中A-2型号的总销量,如G2所示。(多条件求和) 函数公式:G2单元格输入 =SUMPRODUCT((A2:A11=E2)*(B2:B11=F2),C2:C11) 解释说明: 1、首先判断A列产品名称是否等于A产品A2:A11=E2,如果相等返回TRUE,不相等返回FALSE; 2、其次判断B列型号是否等于A-2型号B2:B11=F2,如果相等返回TRUE,不相等返回FALSE; 3、A2:A11=E2的结果与B2:B11=F2的结果分别相乘,其中TRUE*TRUE=1,表示两个条件同时成立,任何数与FALSE相乘都等于0,表示条件不成立; 4、用SUMPRODUCT函数将(A2:A11=E2)*(B2:B11=F2)的结果(1或0)分别与C2:C11相乘然后相加,就可以得到我们要的结果。 实例3、下表是某公司部分业务人员1-6月的销量情况,根据姓名及月份查找相应的销量,如K2单元格所示。(多条件查找) 函数公式:K2单元格输入 =SUMPRODUCT((A2:A11=I2)*(B1:G1=J2),B2:G11) 解释说明: 1、首先判断A列姓名是否等于条件的姓名A2:A11=I2,如果相等返回TRUE,不相等返回FALSE; 2、其次判断B1:G1月份是否等于指定的月份B1:G1=J2,如果相等返回TRUE,不相等返回FALSE; 3、A2:A11=I2的结果与B1:G1=J2的结果分别相乘,其中TRUE*TRUE=1,表示两个条件同时成立,任何数与FALSE相乘都等于0,表示条件不成立; 4、用SUMPRODUCT函数将(A2:A11=I2)*(B1:G1=J2)的结果(1或0)分别与B2:G11相乘然后相加,就可以得到我们要的结果。 实例4、下表是某集团旗下多个分公司部分员工信息,其中有公司名称和区域名称,要求根据公司及区域名称分别计算人数,如G列所示。(多条件计数) 函数公式:G2单元格输入 =SUMPRODUCT(($A$2:$A$15=E2)*($B$2:$B$15=F2)),公式下拉即可 解释说明: 1、首先判断A列公司是否等于条件的姓名A2:A15=E2,如果相等返回TRUE,不相等返回FALSE; 2、其次判断B列区域是否等于条件的区域B2:B15=F2,如果相等返回TRUE,不相等返回FALSE; 3、A2:A15=E2的结果与B2:B15=F2的结果分别相乘,其中TRUE*TRUE=1,表示两个条件同时成立,任何数与FALSE相乘都等于0,表示条件不成立; 4、用SUMPRODUCT函数将(A2:A15=E2)*(B2:B15=F2)的结果(1或0)全部相加,就可以得到我们要的结果,因为公司要下拉,所以区域要加绝对引用。
  • 函数365之SEARCHB函数:文本位置查找之查找字节位置

    Excel函数2017-4-230评论380
    函数名称:SEARCHB 函数作用:SEARCHB函数用于根据查找指定的文本字符在某个字符串中的字节位置 函数语法:SEARCHB(要查找的字符,在哪个字符里面查找,从第几个位置开始查找) 注意事项: 1、SEARCHB函数不区分字母大小写并且允许使用通配符。FINDB函数刚好相反! 2、如果要查找的文本为空文本 (""),则SEARCHB会返回查找字符的第1个位置; 3、如果第2参数中没有包含第1参数,则SEARCHB的结果会返回错误值 #VALUE!; 4、如果开始查找的位置小于等于0或者大于第2参数文本的字节个数,则SEARCHB返回错误值 #VALUE!。 5、SEARCHB函数的第3参数一般是省略的,表示要查找的位置从第2参数的文本的第1个位置开始查找。 6、SEARCHB函数与SEARCH函数的语法及用法相同,唯一不同的是SEARCH函数用于查找字符的在文本的第几个字符位置,而SEARCHB函数用于查找字符在文本的第几个字节位置。 函数应用: 实例1、根据下表指定字符在相应字符串中的字节位置,效果如C列所示。 函数公式:C2单元格输入 =SEARCHB(B2,A2),公式下拉即可 解释说明:查找某字符在字符串中的字节位置可以用SEARCHB函数,在Excel中,一个中文汉字或中文标点符号占2个字节,1个数字、字母或英文标点符号占1个字节。 实例2、根据下面表格A列的人员信息提取相应的联系方式,如B列所示。 函数公式:B2单元格输入 =MIDB(A2,SEARCHB("?",A2),99),公式下拉即可 解释说明: 1、在Excel中,一个中文汉字或中文标点符号占2个字节,1个数字、字母或英文标点符号占1个字节; 2、A列数据只有文字和数字两种类型,而数字属于单字节字符,可以用通配符“?”表示,SEARCHB函数支持通配符,所以我们可以用SEARCHB函数查找第一个数字出现的位置SEARCHB("?",A2); 3、然后用MIDB函数根据第1个数字出现的字节位置截取字符,截取的字节长度可以按照需求自行设置。
  • 函数365之SEARCH函数:文本位置查找之查找字符位置

    Excel函数2017-4-230评论311
    函数名称:SEARCH 函数作用:SEARCH函数用于根据查找指定的文本字符在某个字符串中的位置 函数语法:SEARCH(要查找的字符,在哪个字符里面查找,从第几个位置开始查找) 注意事项: 1、SEARCH函数不区分字母大小写并且允许使用通配符。FIND函数刚好相反! 2、如果要查找的文本为空文本 (""),则SEARCH会返回查找字符的第1个位置; 3、如果第2参数中没有包含第1参数,则SEARCH的结果会返回错误值 #VALUE!; 4、如果开始查找的位置小于等于0或者大于第2参数文本的字符个数,则SEARCH和SEARCHB返回错误值 #VALUE!。 5、SEARCH函数的第3参数一般是省略的,表示要查找的位置从第2参数的文本的第1个位置开始查找。 函数应用: 实例1、查找下面身份证号码中第1个“4”出现的位置,效果如B列所示。 函数公式:B2单元格输入 =SEARCH(4,A2),公式下拉即可 解释说明:查找某字符在字符串中的位置可以用FIND函数和SEARCH函数,这里我们用SEARCH函数,其中第1参数为要查找的字符,这里我们要查找数字4,如果需要查找的内容是文本,需要加双引号。 实例2、根据下面表格A列的人员信息提取相应的联系方式,如B列所示。 函数公式:B2单元格输入 =MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&1234567890)),99),公式下拉即可 解释说明: 1、查找文本字符的位置,所以用SEARCH函数分别查找0、1、2、3、4、5、6、7、8、9这10个数字分别在文本字符串中出现的位置; 2、为了防止查找的数字在文本字符串中不存在而出现错误值 #VALUE!,所以需要在文本字符串后面用连接符号“&”连接1234567890这10个数字,这样SEARCH函数就可以依次取得每个数字出现的位置,然后用MIN函数取最小值就可以得到第1个数字出现的位置。 3、然后用MID函数根据第1个数字出现的位置截取字符,由于不知道联系方式的长度,所以我们可以把数字设大一点,用99。 实例3、下表A列为某公司部分业务员的工作汇报记录,该公司要求规范的工作汇报格式应该以“今天”开头,内容要包含“客户”两个字,否则就是不规范的汇报,请判断A列工作汇报是否规范,效果如B列所示。 函数公式:B2单元格输入 =IF(ISERR(SEARCH("今天*客户",A2)),"不规范","规范"),公式下拉即可 解释说明: 1、规范的格式是以“今天”开头,包含“客户”两个字,我们可以用通配符表示“今天*客户”; 2、用SEARCH函数在A列工作汇报中查找“今天*客户”,如果可以返回位置则表示字符串符合我们要的格式,如果不符合我们要的格式SEARCH函数的结果将为错误值; 3、所以用ISERR函数判断SEARCH函数的结果是否为错误值,如果是则工作汇报不规范,否则为规范,用IF判断即可。
  • 函数365之ISODD函数:是不是奇数问我便知

    Excel函数2017-4-210评论327
    函数名称:ISODD 函数作用:ISODD函数用于判断数值是否为奇数,如果参数的值为奇数,则返回TRUE,否则返回FALSE。 函数语法:ISODD(参数) 注意事项: 1、参数可以是要判断的数值或者可以返回数值的单元格和公式; 2、如果参数是非数值型的数据,函数ISODD的结果将是#VALUE!错误值。 函数应用: 实例1、判断下表A列数据是否为奇数,如果是奇数则在B列显示奇数,否则为空,如B列所示。 函数公式:B2单元格输入 =IF(ISODD(A2),"奇数",""),公式下拉即可 解释说明: 1、判断数值是否奇数可以用ISODD函数,如果是则函数结果返回TRUE,否则返回FALSE; 2、在ISODD的结果外面嵌套一个IF函数,判断当ISODD结果为TRUE时IF结果为“奇数”,否则为空。 实例2、根据下面表格中的身份证号码判断这个人的性别是男性或者是女性,如C列所示。 函数公式:C2单元格输入 =IF(ISODD(--MID(B2,17,1)),"男","女"),公式下拉即可 解释说明: 1、首先我们要普及一个常识,现在国家标准所有的身份证号码都是18位的,其中第17位数字表示性别,如果该数字是奇数则身份证号码为男性,如果该数字是偶数则身份证号码为女性。 2、然后我们要把身份证号码的第17位数字提取出来,所以用公式MID(B2,17,1),因为MID函数截取的字符都属于文本,所以要在MID结果前面加双负号“--”,将文本型的数字转换成数值型的数字; 3、然后我们用ISODD函数判断身份证第17位数字--MID(B2,17,1)是否奇数,如果是返回TRUE,表示该号码为男性,不是则返回FALSE,表示该号码为女性,所以嵌套IF函数即可得到答案。
  • 函数365之RAND函数:瞬息万变永不重复的随机数

    Excel函数2017-4-200评论483
    函数名称:RAND 函数作用:RAND函数可用于返回大于等于0及小于1的均匀分布随机实数。 函数语法:RAND() 注意事项: 1、RAND函数没有参数; 2、每次计算工作表时都将返回一个新的随机实数; 3、每次计算的RAND的结果都不相同。 函数应用: 实例1、在A列A2:A11单元格中随机生成0-1的随机实数,如下图所示。 函数公式:A2单元格输入 =RAND(),公式下拉即可。 解释说明:生成0-1随机实数用RAND函数。 实例2、在A列A2:A11单元格中随机生成5-55的随机实数,如下图所示。 函数公式:A2单元格输入 =RAND()*50+5,公式下拉即可。 解释说明:生成a-b的随机实数可以直接套用公式RAND()*(b-a)+a即可。 实例3、在A列A2:A11单元格中随机生成1-10的随机不重复整数,如下图所示。 操作方法: 1、首先在B2单元格输入公式=RAND(),公式下拉至B11单元格; 2、然后在A2单元格输入公式=RANK(B2,$B$2:$B$11),公式下拉即可。
  • 函数365之PRODUCT函数:飞雪连天射白鹿之连乘诀

    Excel函数2017-4-190评论342
    函数名称:PRODUCT 函数作用:PRODUCT函数可用于计算所有参数的乘积。 函数语法:PRODUCT(参数1,参数2,参数3,...,参数255) 注意事项: 1、第1参数为必选参数,第2-255参数为可选参数,参数为数值或表示数值的公式及单元格引用; 2、如果参数为空白单元格、逻辑值和文本,计算时将被忽略。 函数应用: 实例1、将A列和B列所有的数值相乘计算结果,如D2单元格所示。 函数公式:D2单元格输入 =PRODUCT(A2:B11) 解释说明:计算多个数值或者多区域数值连乘用PRODUCT函数,这里计算区域为A2:B11 实例2、根据下表A列表达式计算结果,如B列所示。 函数公式:B2单元格输入 =PRODUCT(IFERROR(--MID(SUBSTITUTE(A2,"*",REPT(" ",99)),100*ROW($1:$10)-99,100),1)),数组公式CTRL+SHIFT+回车键三键结束,公式下拉即可 解释说明: 1、首先用SUBSTITUTE函数替换掉所有的“*”,然后用MID函数将表达式的每一个数值提取出来; 2、在MID的结果前面加双负号,可以将MID的结果文本型数值转数值型数值,将文本转换成错误值; 3、用IFERROR函数将错误值变成1,因为任何数乘以1等于本身,不影响计算; 4、最后用PRODUCT函数将所有数值相乘。
  • 函数365之MONTH函数:根据日期获取月份值

    Excel函数2017-4-180评论352
    函数名称:MONTH 函数作用:MONTH函数可用于返回日期或表示日期的序列号的月份值。 函数语法:MONTH(日期或表示日期的序列号) 注意事项: 1、MONTH函数有且只有一个参数,表示日期或者表示日期的序列号、公式、单元格引用等; 2、如果参数为非日期形式的文本,则MONTH函数的结果为#VALUE!错误值。 函数应用: 实例1、根据A列的日期,获取该日期的月份值,如B列所示。 函数公式:B2单元格输入 =MONTH(A2),公式下拉即可 解释说明:计算日期的月份值用MONTH函数,直接套用函数语法即可。 实例2、根据下表A列日期判断该日期属于第几季度,如B列所示。 函数公式:B2单元格输入 =TEXT(LEN(2^MONTH(A2)),"第0季度"),公式下拉即可 解释说明: 1、首先我们知道判断一个日期属于第几季度是根据月份来判断的,一年12个月,每三个月一个季度,这是常识; 2、我们可以用MONTH函数获取日期的月份值,然后将该月份值作为2的次方数2^MONTH(A2),然后将得到的结果再用LEN函数判断数字的位数; 3、观察规律我们会发现1-12月,2^1,2^2,2^3…2^12的数字位数分别是1,1,1,2,2,2,3,3,3,4,4,4,分别表示当前月份的季度数; 4、最后我们用TEXT函数将这个数字表示成B列所示,格式为“第0季度”,其中“0”表示占位符。 实例3、下表是某公司部分员工入职记录,现在要求计算2月份入职人数,如D2单元格所示。 函数公式:D2单元格输入 =SUM((MONTH(B2:B11)=2)*1),数组公式CTRL+SHIFT+回车键三键结束 解释说明: 1、要求计算月份用MONTH函数,分别判断提取每个日期的月份值MONTH(B2:B11),然后再判断这些月份值是否等于2,满足条件返回TRUE,不满足条件返回FALSE; 2、将前面得到的TRUE和FALSE分别乘以1,满足条件的TRUE*1=1,不满足条件的FALSE*1=0,最后用SUM将全部数值相加即可得到2月份的入职人数。 3、再强调一次,在进行加减乘除等运算时,TRUE等同于1,FALSE等同于0,任何数乘以1等于本身,任何数乘以0等于0。
  • 函数365之INDIRECT函数:最了解单元格内容的函数

    Excel函数2017-4-170评论486
    函数名称:INDIRECT 函数作用:INDIRECT函数可用于返回由文本字符串指定的引用。 函数语法:INDIRECT(单元格引用或表示单元格引用的文本,引用的类型) 注意事项: 1、第1参数为必选参数。表示对单元格的引用,或者定义为引用的名称或对作为文本字符串的单元格的引用。如果该参数不是合法的单元格的引用,函数INDIRECT返回错误值 #REF! 。 2、第2参数为可选参数。一个逻辑值,用于指定包含在第1参数中的引用的类型,如果参数为TRUE或省略,第1参数被解释为 A1-样式的引用,如果参数为FALSE,则将第1参数解释为R1C1样式的引用。 函数应用: 实例1、根据A列单元格地址返回相应的单元格内容,效果如B列所示。 函数公式:B2单元格输入 =INDIRECT(A2,1),公式下拉即可 解释说明: 1、返回单元格地址引用内容用INDIRECT函数,因为A列单元格地址样式为“A1”样式,所以INDIRECT的第2参数为TRUE,这里用“1”表示,公式也可以写成=INDIRECT(A2),省略第2参数。 2、“Sheet5!A1”这里指的是特定工作表的特定单元格地址,INDIRECT函数在处理多表引用和多工作簿引用中有非常强大的作用。 实例2、根据A列单元格地址返回相应的单元格内容,效果如B列所示。 函数公式:B2单元格输入 =INDIRECT(A2,0),公式下拉即可 解释说明: 1、返回单元格地址引用内容用INDIRECT函数,因为A列单元格地址样式为“R1C1”样式,所以INDIRECT的第2参数为FALSE,这里用“0”表示,公式也可以写成=INDIRECT(A2,FALSE)。 2、“R1C1”样式里面的R是ROW的缩写表示行号,C是COLUMN的缩写表示列号,所以“R4C4”就表示第4行第4列,熟悉单元格地址的朋友应该一看就知道这是D4单元格。 实例3、下图所示工作簿里面有4个工作表,每个工作表都是第1列姓名,第2列销量,要求在汇总表里面计算表1表2表3的销量总和,如B列所示。 函数公式:B2单元格输入 =SUM(SUMIF(INDIRECT("表"&{1,2,3}&"!A:A"),A2,INDIRECT("表"&{1,2,3}&"!B:B"))),公式下拉即可 解释说明: 本题是多工作表条件求和,所以需要用SUMIF求和及用INDIRECT函数构建多表引用,"表"&{1,2,3}&"!A:A"表示表1、表2、表3三个工作表的A列单元格区域,这是工作表单元格地址的命名规则“表名!单元格地址” 实例4、根据下表ABCD四列数据制作二级联动数据有效性,效果如图F列和G列所示。 操作步骤: 1、选择A1:D1单元格区域,然后在名称框输入“省份”,然后按回车键确认; 2、选择A1:D8单元格区域,然后选择“公式”选项卡,点击“根据所选内容创建”,然后把默认的“最左列”勾选去掉,保留“首行”,点击“确定”; 3、选中F2单元格,然后选择“数据”选项卡,点“数据有效性—设置—(允许)序列”,来源输入“=省份”,然后点击“确定”; 4、选中G2单元格,然后选择“数据”选项卡,点“数据有效性—设置—(允许)序列”,来源输入“=INDIRECT($F$2)”,然后点击“确定”; 5、做完以上4步已经全部完成。第一步定义省份名称,第二步批量定义各个省份的市区名称,第3、第4步分别设置省份和市区单元格的数据有效性。注意设置完省份数据有效性后要先随便选择一个省份,否则设置市区有效性后保存会报错,不过报错也没关系,点确定即可。 实例5、将下表ABCDE五列的姓名合并成一列,效果如G列所示。 函数公式:G2单元格输入 =INDIRECT(TEXT(SMALL(IF($A$2:$E$11<>"",ROW($2:$11)*100+COLUMN(A:E)),ROW(A1)),"r0c00"),0),数组公式CTRL+SHIFT+回车键三键结束,公式下拉即可 解释说明: 1、首先用IF判断姓名区域是否为空,如果不为空则将姓名所在单元格的行号乘以100再加上其所在列号,否则为空; 2、将所有IF的结果从小到大依次提取出来,这里用了SMALL函数,第2参数为ROW(A1); 3、然后用TEXT函数将表示单元格行号和列号的值用单元格样式“r1c1”样式表示,说到这里可能有人看不懂,没关系我们举个栗子,例如A2单元格不等于空,所以根据判断是将A2单元格的行号乘以100再加上其所在列号,结果为2*100+1=201,然后用TEXT函数将201格式化成“r0c00”格式,就变成了“r2c01”,这是单元格样式; 4、知道了单元格地址后用INDIRECT函数获取该地址内容,例如上面举的栗子,INDIRECT(r2c01,0)可以获取到A2单元格的内容。
  • 函数365之AVERAGEIF函数:有条件要求平均,没有条件创造条件也要求平均

    Excel函数2017-4-160评论539
    函数名称:AVERAGEIF 函数作用:AVERAGEIF函数可用于返回某个区域内满足给定条件的所有单元格的算术平均值。 函数语法:AVERAGEIF(条件区域, 计算条件, 计算平均值区域) 注意事项: 1、第1参数为必选参数。表示要计算平均值的条件区域;如果为空值或文本值,则AVERAGEIF会返回 #DIV0! 错误值。 2、第2参数为必选参数。表示要计算平均值的条件,包括数字、表达式、单元格引用或文本形式;如果条件中的单元格为空单元格,AVERAGEIF就会将其视为0值。 3、第3参数为可选参数。要计算平均值的实际单元格区域,如果忽略,计算平均值的区域为第1参数;计算时忽略TRUE 或 FALSE以及空单元格, 4、如果区域中没有满足条件的单元格,则AVERAGEIF会返回 #DIV/0! 错误值。 5、AVERAGEIF函数支持通配符“*”和“?”的使用。 函数应用: 实例1、下表是某公司营销部三个区域的销量,要求计算每个区域的平均销量,如E列所示。 函数公式:E2单元格输入 =AVERAGEIF($A$2:$A$11,D2,$B$2:$B$11),公式下拉即可 解释说明:本题是根据区域计算平均销量,也就是根据指定计算平均值,用AVERAGEIF函数,A2:A11作为条件区域,D2作为计算的条件,B2:B11作为要计算平均值的区域,由于公式需要下拉所以注意绝对引用。 实例2、根据下表的采购数量,计算采购量大于500的平均值,如D2单元格所示。 函数公式:D2单元格输入 =AVERAGEIF(B2:B11,">500") 解释说明:计算采购量大于500的平均值,也就是根据条件计算平均,用AVERAGEIF函数,其中B2:B11既作为条件区域,也作为计算平均值的区域,所以省略了第3参数,计算的条件表示为“>500”。 实例3、下表是某公司部分业务人员的销量情况,要求计算广西区域的业务人员的平均销量,如E2单元格所示。 函数公式:E2单元格输入 =AVERAGEIF(B2:B11,"广西*",C2:C11) 解释说明:要求计算广西区域的平均销量,也就是根据条件计算平均值用函数AVERAGEIF,计算的条件用通配符“广西*”表示以广西开头的区域,B2:B11为条件区域,C2:C11为计算平均值的区域。 实例4、下表是某公司部分员工信息,其中有性别和年龄,要求分别计算男性员工与女性员工的平均年龄,如I列所示。 函数公式:I2单元格输入 =AVERAGEIF($B$2:$F$11,H2,C$2),公式下拉即可 解释说明: 1、本题主要考计算平均值的区域不必与条件区域的大小和形状相同,求平均值的实际单元格是通过使用区域中左上方的单元格作为起始单元格,然后加入与条件区域的大小和形状相对应的单元格确定的。 2、所以本题的条件区域是B2:F11,计算平均值的区域只选择C2,这样计算时系统会自动将计算区域与条件区域相匹配。 3、由于公式需要下拉注意要绝对引用。
  • 函数365之AVERAGE函数:不患寡而患不均,有AVERAGE何愁不均

    Excel函数2017-4-150评论203
    函数名称:AVERAGE 函数作用:AVERAGE函数可用于返回参数的算术平均值。 函数语法:AVERAGE(参数1, 参数2, 参数3, ..., 参数255) 注意事项: 1、AVERAGE函数有255个参数,其中第1个参数为必选参数,其余参数为可选参数; 2、参数可以是数字或者是包含数字的名称、单元格区域或单元格引用; 3、逻辑值和直接键入到参数列表中代表数字的文本被计算在内; 4、如果区域或单元格引用参数包含文本、逻辑值或空单元格,则这些值将被忽略;但包含零值的单元格将被计算在内; 5、如果参数为错误值或为不能转换为数字的文本,将会导致错误; 函数应用: 实例1、下表是某班部分学生的成绩,要求计算平均分,如F2单元格所示。 函数公式:F2单元格输入 =AVERAGE(B2:B11,D2:D11) 解释说明:数学上计算平均分=总分/人数,所以=SUM(B2:B11,D2:D11)/20,在Excel里面有一个计算算术平均值的函数AVERAGE,直接根据语法使用即可。 实例2、下表是某班部分学生的成绩,要求计算前三名的平均分,如F2单元格所示。 函数公式:F2单元格输入 =AVERAGE(LARGE(B2:D11,{1,2,3})) 解释说明:计算第1,第2,第3名分别可以用函数LARGE(B2:D11,1),LARGE(B2:D11,2),LARGE(B2:D11,3)表示,其中合在一起第2参数可以用常量数组{1,2,3}表示,所以LARGE(B2:D11,{1,2,3})可以得到前三名的分数,然后套用AVERAGE函数计算平均值。 实例3、下表是某表演节目10个评委给出的打分,现在要求去掉一个最高分和一个最低分计算平均得分,如D2单元格所示。 函数公式:D2单元格输入 =AVERAGE(LARGE(B2:B11,ROW(2:9))),数组公式CTRL+SHIFT+回车键三键结束 解释说明:要求去掉一个最高分和一个最低分,也可以理解为只计算第2大到第9大的数值,也就是LARGE(B2:B11,ROW(2:9)),然后再套用AVERAGE函数。