EXCEL在日工作中的应用.pptVIP

第五章 公式与函数 5.8.2 用MOD隔行填充颜色 MOD函数实例（P138） =MOD(ROW(),2)=0 返回当前行的行数 第五章 公式与函数 5.9 其他统计函数 统计函数是工作中常用的函数,以前我们学过SUM,COUNT等函数。本节将介绍其他几个统计函数。 5.9.1 用COUNTA函数自动统计工资表人数 功能:返回参数组中非空值的数目。 语法:=COUNTA(参数1,参数2,参数3……参数N) N最大值为30；可以进行多工作表的三维引用 注意和COUNT函数的对比！！ COUNT函数只统计数值型数据 例:=COUNTA(A1:B67) 第五章 公式与函数 5.9.1 COUNTA函数 参数2 参数1 打开EXCEL 第五章 公式与函数 5.9.1用COUNTA函数自动统计工资表人数 COUNTA函数实例 =COUNTA(B2:B5) 不便于插入行 =COUNTA(INDIRECT(B2:BROW()-1)) 利用INDIRECT函数把字符串转换为引用，利用ROW函数把当前行数减去1，得出上一行的行数。 第五章 公式与函数 5.9.2用MAX函数设置变动序号 MAX和MIN函数是分别求最大值和最小值的函数，它们常在复杂的数组公式中出现。 功能：MAX求一组数中的最大值；MIN求一组数中的最小值 语法：=MAX(数值1,数值2,……) =MIN(数值1,数值2,……) MAX和MIN函数实例（P141） 第五章 公式与函数 MAX和MIN函数实例（P141） 设置序号，要求： ①序号随行的删除可自动调整为新的连续序号 ②在小计行、合计行和空行前不加序号 =IF(OR(B6=,B6=小计,B6=合计),,MAX($A$1:A5)+1) 注意绝对引用和相对引用的使用 第五章 公式与函数 5.9.3用LARGE和SMALL实现销售数量自动排名 功能：LARGE求一组数中的第N个最大值；SMALL求一组数中的第N个最小值 语法： =LARGE(一组数值或单元格区域，第N个最大值) =SMALL(一组数值或单元格区域，第N个最小值) LARGE和SMALL函数实例 =LARGE(B2:B17,1) =SMALL(B2:B17,1) 当有两个第二大值相等的时候，一个会作为第二大，另一个作为第三大 第五章 公式与函数 5.10 其他查找引用函数 查找引用函数在单元格查询，数据表之间的取数、核对方面有着极其广泛的用途，前面我们学过VLOOKUP函数，以下介绍其他查找引用函数 5.10.1 ROW和COLUMN生成公式变动函数 语法:=ROW(引用的单元格或单元格区域) =COLUMN (引用的单元格或单元格区域) 如果引用的单元格或单元格区域省略,返回的为当前行号; 如果引用的是一个单元格,返回的是引用单元格所在的行号或列号; 如果引用的是一个单元格区域,返回的是单元格区域左上角所在的行号或列号。 第五章 公式与函数 5.10 其他查找引用函数 如果公式在A10单元格 例:=ROW()=？ 例:=ROW(C25)=？ 例:=ROW(D2:E10)=？ 例:=COLUMN()=？ 例:= COLUMN(C25)=？ 例:= COLUMN(D2:E10)=？ 10 25 2 1 3 4 第五章 公式与函数 5.10 其他查找引用函数 ROW和COLUMN函数实例 C14=VLOOKUP($B14,$B$2:$F$9,2,0) C15=VLOOKUP($B15,$B$2:$F$9, COLUMN(B1),0) 这两个公式的区别在于相对列数的不同。第一个公式直接用数字2，后面的公式必须要手工来改相对列数，这样不便于公式的复制；第二个公式用的是COLUMN(B1),这样随着公式向右的拖动，列发生改变，函数返回的值也随之改变，就可以实现公式的轻松复制。 第五章 公式与函数 5.10 其他查找引用函数 ROW和COLUMN函数实例 I3=LARGE($F$2:$F$9,1) J3=LARGE($F$2:$F$9,ROW(A1)) 这两个公式的区别在于第N大值的不同。第一个公式直接用数字1，后面的公式必须要手工来改第N大值，这样不便于公式的复制；第二个公式用的是ROW(A1),这样随着公式向下的拖动，行发生改变，函数返回的值也随之改变，就可以实现公式的轻松复制。 第五章 公式与函数 5.10 其他查找引用函数 5.10.2 MATCH和INDEX实现双向查找 MATCH是查询函数,INDEX是引用函数，在实际查找并返回值过程中，MATCH和INDEX总是结对出现在公式中。 功能： MATCH返回在指定方式下与指定数值匹配的数组中元素的相应位置 I