EXCEL在会计日常工作中的应用.ppt

《EXCEL在会计日常工作中的应用.ppt》由会员分享，可在线阅读，更多相关《EXCEL在会计日常工作中的应用.ppt（132页珍藏版）》请在三一办公上搜索。

1、,EXCEL在会计日常工作中的应用,授课老师:钟伟,第一章 EXCEL文件管理,新建文件工作表的隐藏文件的安全与保护快速打印指定表格,第二章 单元格编辑,录入相同的内容下拉列表录入录入内容的限制限制数字格式或大小文本长度单多条件限定限制重复输入录入区域的限制,第二章 单元格编辑,选取使用定位选取选择性粘贴粘贴数值粘贴运算隐藏查找,第三章 条件格式,3.1条件格式的设立、添加设立条件格式添加条件3.2定义条件单元格数值条件公式条件3.3条件格式实例应用3.4小结,第三章 条件格式,3.1条件格式的设立、添加设立条件格式添加条件3.2定义条件单元格数值条件公式条件3.3条件格式实例应用3.4小结,

2、第三章 条件格式,3.1条件格式的设立、添加设立条件格式操作步骤：选中区域格式 条件格式 输入条件选择格式添加条件在条件设置对话框中，单击添加按钮注：条件格式最多可以设置三个,第三章 条件格式,3.2定义条件单元格数值：用于简单的数值对比公式：用于设置较为复杂的单元格内容3.2.1单元格数值条件3.2.2公式条件,第三章 条件格式,3.2定义条件单元格数值：用于简单的数值对比公式：用于设置较为复杂的单元格内容3.2.1单元格数值条件3.2.2公式条件,第三章 条件格式,3.3条件格式实例应用3.3.1库龄分析的颜色提示3.2.2应收账款催款提醒监视重复录入格式化账簿代码录入的错误显示动态显示销

3、售额排行隐藏公式中的错误值,第三章 条件格式,3.3条件格式实例应用3.3.5代码录入的错误显示条件:1.代码位数不等于五位 2.代码位数不等于八位公式:=AND(LEN($B2)5,LEN($B2)8,$B20),第三章 条件格式,3.3条件格式实例应用3.3.6动态显示销售额排行条件:突出显示前N名商品的销售额公式:=$D2=LARGE($D$2:$D$10,5)最大值函数,MAX求出一个最大值,LARGE可以求第N个最大值.,第三章 条件格式,3.3条件格式实例应用3.3.7隐藏公式中错误值条件:把所有错误值隐藏公式:=ISERROR(D2)判断值是否为任意错误值（#N/A，VALUE！

4、）,第三章 条件格式,3.4小结本章对条件的创建、条件的设置作了详细介绍，同时也列举了大量应用实例。读者从实例中不难看出，如果想用好条件格式，掌握公式及函数的使用是非常重要的。习题：1、如何设置公式条件2、如何突出显示重复录入内容？3、如何突出显示一列数据中最大前三个数字？4、如何添加和删除条件格式？,第四章 数据表和图表,4.1排序4.1.1数据表排序4.1.2隔行插入空行4.2分列4.2.1拆分整列为多列4.2.2长文本型数字的导入4.2.3转化字符为日期格式4.3自动筛选4.3.1自动筛选的实现4.3.2一次删除所有重复记录,第四章 数据表和图表,4.3自动筛选4.3.1自动筛选的实现4

5、.3.2一次删除所有重复记录添加一辅助列,输入公式:=IF(COUNTIF($D2:D2,D2)1,1,2),这儿一定要注意理解绝对引用和相对引用的用法!第一个板手:=IF(COUNTIF($D$2:D2,D2)1,1,2)第二个板手:=IF(COUNTIF($D$2:D5,D5)1,1,2),第四章 数据表和图表,4.4高级筛选高级筛选功能灵活性强,和自动筛选相比有如下特点:可以把筛选结果复制到其他位置;需要设置条件区域,而且可以使用更多条件;可筛选不重复记录;4.4.1筛选符合条件的记录1.输入条件区域规则:(1)标题行和源区域一样(2)同行不同列的条件是并列关系;(3)同列不同行的条件是

6、或者关系,第四章 数据表和图表,2.设置筛选项目.复制标题行到要显示筛选结果的第一行.复制和手工输入有什么区别?3.数据筛选高级筛选,数据源区域,设置条件的区域,第四章 数据表和图表,4.4.2筛选本列不重复记录4.4.3筛选两区域重复记录4.4.4筛选两表中不重复记录=COUNTIF($D$16:$D$24,D3)=0,第四章 数据表和图表,4.5数据透视表数据透视表是一种对数据清单快速建立汇总的动态总结报告,它可以随时调换行列的位置而进行不同形式的汇总,是Excel提供的一个极为有效的汇总工具。数据透视表在销售数据汇总、出入库汇总及明细账汇总等方面有着广泛应用,一个普通的数据表,你的工作表

7、含有大量数据，但是你知道这些数字的含义吗？这些数据能够解答您的问题吗？,不普通的数据透视表,数据透视表提供了一种快速且强大的方式来分析数值数据、以不同的方式查看相同的数据以及回答有关这些数据的问题。,第四章 数据表和图表,4.5数据透视表4.5.1创建数据透视表三步曲之一：确定报表类型,第四章 数据表和图表,4.5数据透视表4.5.1创建数据透视表三步曲之二：确定数据源,第四章 数据表和图表,4.5数据透视表4.5.1创建数据透视表三步曲之三：布局,第四章 数据表和图表,4.5数据透视表4.5.1创建数据透视表三步曲之三：确定显示位置,第四章 数据表和图表,4.5.5固定数据透视表格式4.5.

8、1创建数据透视表4.5.2调整数据透视表格式4.5.3在数据透视表中设置公式4.5.4其他编辑调整汇总方式显示或隐藏汇总行数据透视表的更新和自动更新,第四章 数据表和图表,4.5.7多个数据透视表合并,第四章 数据表和图表,4.5.7多个数据透视表合并,第四章 数据表和图表,4.6 图表4.6.1创建图表插入图表四步曲设置图表类型设置数据源设置图表选项设置图表位置,第四章 数据表和图表-四步曲,第四章 数据表和图表,4.6.2 双坐标图表,单坐标图表,常规设置的双坐标图表,双坐标图表,第四章 数据表和图表,4.6.2 双坐标图表1.添加系列2.修改系列格式,第五章 公式与函数,公式与函数是Ex

9、cel的精华所在,它为分析和处理数据提供了方便,特别是在处理大批量数据和进行复杂数据分析等方面更能发挥它的强大功能。本章在介绍函数的用法时，着重介绍它们在财务工作中的应用。本章要点：IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函数的用法IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函数的实例应用,第五章 公式与函数,公式与函数是Excel的精华所在,它为分析和处理数据提供了方便,特别是在处理大批量数据和进行复杂数据分析等方面更能发挥它的强大功能。本章在介绍函数的用法时，着重介绍它们在财务工作中的应用。本章要点：IF、SUM、COUNTIF、

10、SUMPRODUCT和VLOOKUP等常用函数的用法IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函数的实例应用,第五章 公式与函数,5.1 IF函数IF函数是工作中最常用函数之一，它可以根据设置的条件进行运算或返回值。语法：=IF（逻辑表达式，TRUE，FALSE）,逻辑表达式不成立返回的值,逻辑表达式成立返回的值,返回值为TRUE或FALSE的逻辑表达式,例:=IF(53,对,不对),例:=IF(53,不对,对),第五章 公式与函数,5.1 IF函数,逻辑表达式不成立返回的值,逻辑表达式成立返回的值,返回值为TRUE或FALSE的逻辑表达式,打开EXCEL,第五章

11、 公式与函数,5.1.1 单条件和多条件判断1.单条件返回文本IF函数实例（P113）,逻辑表达式不成立返回的值,逻辑表达式成立返回的值,逻辑表达条件：比较实际数和计划数的大小,D2=IF（C2B2,节约，超支）,第五章 公式与函数,5.1.1 单条件和多条件判断2.单条件判断并运算IF函数实例（P113）,逻辑表达式不成立进行运算的表达式,逻辑表达式成立时进行运算的表达式,逻辑表达条件：销售额是否超过3万元,C2=IF（B230000,B2*0.015,B2*0.01）,第五章 公式与函数,5.1.1 单条件和多条件判断3.单条件判断返回引用区域IF函数实例（P113）,逻辑表达式不成立返回

12、的区域,逻辑表达式成立时返回的区域,逻辑表达条件：A2是否等于销售一部,=SUM(IF(A2=销售一部,B5:B9,E5:E9),第五章 公式与函数,5.1.1 单条件和多条件判断4.多条件判断IF函数实例（P113）,逻辑表达式不成立返回的表达式,逻辑表达式成立时返回的值,逻辑表达条件：B2或C2任一为0是否成立,=IF(OR(B2=0,C2=0),(C2-B2)/C2),第五章 公式与函数,5.1.1 单条件和多条件判断4.多条件判断IF函数实例（P113）,=IF(B220000,B2*1%,IF(B225000,B2*2%,IF(B235000,B2*3%,B2*4%),=IF(B22

13、0000,B2*1%,IF(20000=B225000,B2*2%,IF(25000=B235000,B2*3%,B2*4%),常见的错误,常见的错误,第五章 公式与函数,5.1.1 单条件和多条件判断4.多条件判断IF函数实例（P113）,=IF(B2=10000),B2*2%,0)+IF(AND(B2=20000),B2*3%,0)+IF(AND(B2=30000),B2*4%,0)+IF(AND(B2=40000),B2*5%,0)+IF(AND(B2=50000),B2*6%,0)+IF(AND(B2=60000),B2*7%,0)+IF(AND(B2=70000),B2*8%,0)+

14、IF(B280000,B2*9%,0),第五章 公式与函数,课堂练习：成绩表小结：这节课讲了IF函数的使用，有以下四种情况：单条件返回文本单条件进行运算单条件返回区域多条件判断IF函数在实际工作中应用很广，要注意不同函数中参数的含义。,第五章 公式与函数,5.2 SUM函数SUM函数是工作中最常用函数之一，几乎所有的表格中都有合并的运算。语法：=SUM（参数1，参数2，参数30）,参数最多为30个,参数可以为引用，数值，文本，表达式和数组,例:=SUM(5,3,2,1),例:=(a1:b1),第五章 公式与函数,5.2.1 连续、不连续及交叉区域求和例1：连续区域的求和=SUM(A1:C5)例

15、1：不连续区域的求和=SUM(A1，B3，D22)例1：交叉区域的求和=SUM(1:3 C:C),注意：这儿有空格,第五章 公式与函数,5.2.2 多工作表自动汇总是SUM函数的三维应用例：=SUM(1日:空白!C5),单引号的作用是去掉工作表名的空格,工作表的名称必须加感叹号！,第五章 公式与函数,5.1 SUMIF函数SUMIF函数是根据指定条件对若干单元格求和。语法：=SUMIF（条件范围，条件，求和范围）,需要求和的实际范围，省略则对条件范围求和,只能用单条件而不能用复合条件，可以使用通配符,用于条件判断的单元格区域,例:=SUMIF(B2:B9,“副教授”,D2:D9),例:=SUM

16、IF(D2:D9,“2000),第五章 公式与函数,5.1 SUMIF函数,求和范围,条件,条件范围,打开EXCEL,第五章 公式与函数,5.3.1 单条件求和问题1:根据B列销售金额求和,要求对销售金额大于2000的数值求和SUMIF函数实例（P121）,省略求和范围,对条件范围进行求和,条件,条件范围,=SUMIF（B2:B9,“2000）,第五章 公式与函数,5.3.1 单条件求和问题2:根据商品名称求和,要求对商品名称为A1的销售金额求和SUMIF函数实例（P121）,求和范围,条件,条件范围,=SUMIF（A2:A9,“A1“,B2:B9）,第五章 公式与函数,5.3.1 单条件求和

17、问题3:根据B列销售金额求和,要求对销售金额大于D2的数值求和SUMIF函数实例（P121）,省略求和范围,对条件范围进行求和,条件,条件范围,=SUMIF（B2:B9,“&D2）,第五章 公式与函数,5.3.1 单条件求和问题4:对B列中大于平均数的销售金额求和SUMIF函数实例（P121）,省略求和范围,对条件范围进行求和,条件,条件范围,=SUMIF(B2:B9,“&AVERAGE(B2:B9),第五章 公式与函数,5.3.1 单条件求和问题5:求商品名称包含”A”的销售金额之和SUMIF函数实例（P121）,求和范围,条件,条件范围,=SUMIF（A2:A9,“A*“,B2:B9）,第

18、五章 公式与函数,5.3.1 单条件求和问题2:根据商品名称求和,要求对商品名称为A1的销售金额求和SUMIF函数实例（P113）,求和范围,条件,条件范围,=SUMIF（A2:A9,“A1“,B2:B9）,第五章 公式与函数,5.3.1 单条件求和问题6:根据商品名称求第四五个字符为”A2”,且字符总长度为6个字符的销售金额求和SUMIF函数实例（P121）,求和范围,条件,条件范围,=SUMIF（A2:A9,“?A2?“,B2:B9）,第五章 公式与函数,5.3.2 多条件及区间求和问题1:符合入库数量大于4小于10的商品,对其入库数量求和SUMIF函数实例（P122）,=SUMIF(C2

19、:C9,“4“)-SUMIF(C2:C9,“=10“),4,10,第五章 公式与函数,5.3.2 多条件及区间求和问题2:B列品名分别为”AA”,”BB”,”CC”的销售数量之和SUMIF函数实例（P122）,=SUM(SUMIF(B2:B9,”AA”,”BB”,”CC”,C2:C9),第五章 公式与函数,5.3.2 多条件及区间求和问题3:对品名分别为”AA”的手机入库数量进行求和SUMIF函数实例（P122）,=SUMIF(A2:A9,”AA手机”,D2:D9)注意要先添加一辅助列,第五章 公式与函数,5.3.3 不相邻区域的求和SUMIF函数实例（P123）,=SUMIF(A3:D11,

20、”1”,B3:E11)注意两个区域的大小要一致,第五章 公式与函数,5.4 COUNTIF函数COUNTIF函数是根据指计算给定区域内满足特定条件单元格数目。语法：=COUNTIF（条件范围，条件）,可以为数字,表达式或文本,用于条件判断的单元格区域,例:=COUNTIF(B2:B9,“副教授”),例:=COUNTIF(D2:D9,“2000),第五章 公式与函数,5.4 COUNTIF函数,条件,条件范围,打开EXCEL,第五章 公式与函数,5.4.1 按条件计数问题1:统计实发工资大于2500的人数COUNTIF函数实例（P124）,条件,条件范围,=COUNTIF（E2:E7,“2500

21、）,第五章 公式与函数,5.4.1 按条件计数问题2:统计财务部的人数COUNTIF函数实例（P124）,条件,条件范围,=COUNTIF（A2:A7,”财务部”）,第五章 公式与函数,5.4.2 COUNTIF计数常见的错误1.区域选取的影响COUNTIF函数实例（P124）,=COUNTIF（B3:B8,C3:C8,”6”）,=COUNTIF（B3:C8,”6”）,第五章 公式与函数,5.4.2 COUNTIF计数常见的错误2.数字格式的影响COUNTIF函数实例（P124）,解决办法:把文本数字转换成数值型,第五章 公式与函数,5.4.2 COUNTIF计数常见的错误3.长数字的影响CO

22、UNTIF函数实例（P124）,解决办法:在长数字中添加*号,第五章 公式与函数,5.5 SUMPRODUCT函数SUMPRODUCT函数是在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。语法：=SUMPRODUCT（数组1，数组2,数组3，）,数组参数必须具有相同的维数，否则函数SUMPRODUCT将返回错误值：“#VALUE！”,例:=SUMPRODUCT(1,2,3,4,5)=?,=1*2*3*4*5=120,第五章 公式与函数,5.5 SUMPRODUCT函数,数组2,数组1,打开EXCEL,数组3,第五章 公式与函数,5.5.1库存金额的简便运算不用设置金额列,直接计算出

23、总入库金额SUMPRODUCT函数实例（P126）,数组2,数组1,=SUMPRODUCT(B2:B9,C2:C9),数组参数必须具有相同的维数,第五章 公式与函数,5.5.2 多条件计数和求和1.多条件同时成立计数:SUMPRODUCT(条件1)*(条件2)*(条件3)*(条件n)求和:SUMPRODUCT(条件1)*(条件2)*(条件3)*(条件n)*(要统计的数据区域)2.任一条件成立计数:SUMPRODUCT(条件1)+(条件2)+(条件3)+(条件n)求和:SUMPRODUCT(条件1)+(条件2)*(条件3)+(条件n)*(要统计的数据区域)SUMPRODUCT函数实例（P127）

24、,第五章 公式与函数,多条件计数和求和例5-17 在入库明细汇总表中,根据要求计算问题1:计算供应商A1的冰箱入库类型的品种数.SUMPRODUCT函数实例（P126）,条件2:类别为冰箱,条件1:供应商的名字为A1,=SUMPRODUCT(B3:B11=“A1”)*(C3:C11=“冰箱”),第五章 公式与函数,多条件计数和求和例5-17 在入库明细汇总表中,根据要求计算问题2:计算供应商A3的洗衣机入库数量.SUMPRODUCT函数实例（P126）,条件3:类别为洗衣机,条件1:供应商的名字为A3,=SUMPRODUCT(B3:B11=“A3”),(C3:C11=“洗衣机”)*E3:E11

25、),统计数据:入库数量,第五章 公式与函数,多条件计数和求和例5-17 在入库明细汇总表中,根据要求计算举一反三:1.计算供应商A1或A2的冰箱入库数量。2.计算供应商A1的冰箱或彩电的品种数。SUMPRODUCT函数实例（P126）,第五章 公式与函数,5.6 VLOOKUP函数VLOOKUP函数是在表格或数值组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值。它是最常用的函数之一功能：1.指定位置查找和引用数据2.表与表的核对3.利用模糊运算进行区间查询,第五章 公式与函数,5.6 VLOOKUP函数语法:=VLOOKUP(查找目标,查找区域,相对列数,TRUE或FALSE

26、),要找的内容在查找区域中的哪一列?,在哪儿查找?注意:查找内容必须在查找区域的第一列!,要查找的内容,例:=VLOOKUP(B2,$D$2:$H$9,3,0),TRUE:模糊查找,FALSE:精确查找,可以用其1和0代替,第五章 公式与函数,5.6 VLOOKUP函数,相对列数,查找区域,查找目标,打开EXCEL,精确查找或模糊查找,第五章 公式与函数,5.6.1 单个区域查找问题1:要求在C列,从员工信息表中根据姓名查找其级别.VLOOKUP函数实例（P129）,=VLOOKUP(B2,$G$9:$H$14,2,0),要找的内容在查找区域中的第2列,在哪儿查找?员工信息表,注意绝对引用的使

27、用!,要查找的内容:张三,精确查找,可以用0代替,第五章 公式与函数,5.6.1 单个区域查找问题2:要求在D,E列,分别根据工资级别和姓名,从基本工资表和提成表查找相应的数值.VLOOKUP函数实例（P130）,=VLOOKUP(C2,$G$2:$H$7,2,0),要找的内容在查找区域中的第2列,在哪儿查找?基本工资表,注意绝对引用的使用!,要查找的内容:工资级别,精确查找,可以用0代替,第五章 公式与函数,5.6.2 多个区域查找利用以前所学的函数VLOOKUP函数实例（P130）,=VLOOKUP(A2,IF(C2=公司1,$F$3:$G$6,$F$10:$G$13),2,0),要找的内

28、容在查找区域中的第2列,在哪儿查找?现在有两个表,需要判断的时候就要想到IF函数,要查找的内容:姓名,精确查找,可以用0代替,第五章 公式与函数,5.6.3 模糊查找计算个人所得税以前学过IF函数条件判断后再求值,但嵌套太多,容易出错,这里运用VLOOKUP函数来解决这个问题VLOOKUP函数实例（P131）,=C2*,要找的内容在查找区域中的第3列,在哪儿查找?,要查找的内容:应税所得,模糊查找,可以省略,应税所得,VLOOKUP(C2,$G$2:$I$10,3)-VLOOKUP(C2,$G$2:$J$10,4),第五章 公式与函数,5.6.4 处理查找出现的错误在利用VLOOKUP函数查找

29、时,常遇到下列几种查询错误:参数设置错误空格及不可见字符引起的错误格式不一致引起的错误,第五章 公式与函数,1.参数设置错误VLOOKUP函数实例（P131）,错误原因:选取查询区域错误,错误原因:省略参数是模糊查找,公式1:=VLOOKUP(B10,A1:E5,3,0),公式2:=VLOOKUP(B11,B2:C5,3,0),错误原因:选取查询区域错误,公式3:=VLOOKUP(B12,B2:E5,3),第五章 公式与函数,2.空格及不可见字符引起的错误VLOOKUP函数实例（P132）,解决方法:替换不可见字符,解决方法:转换格式,(1)空格引起的错误,解决方法:替换空格,(2)不可见字符

30、引起的错误,3.数字格式不一致引起的错误,第五章 公式与函数,5.7 INDIRECT函数INDIRECT 是一个非常重要的函数,它可以把随意组合或者插入变量的字符串转换成可以使用的引用。功能：返回由文字串指定的引用，并对引用进行计算，显示其内容。,第五章 公式与函数,5.7 INDIRECT函数语法:=INDIRECT(文本字符串,引用类型),TRUE：A1类型FALSE：R1C1类型省略为A1类型,对单元格的引用或字符串，此单元格可以包含A1样式的引用，定义为引用的名称或对文字串单元格的引用。,例:=INDIRECT(“R4C4”,0),例:=INDIRECT(“A1”),第五章 公式与函

31、数,5.6 INDIRECT函数,引用类型,文本字符串,打开EXCEL,第五章 公式与函数,5.7.1 行列转置以前我们学过用选择性粘贴,现在我们来学习用公式进行行列的转置.INDIRECT函数实例（P134）,C1=INDIRECT(A&COLUMN(A1),A结合后面的数字组合成一个新的引用,注意相对引用的使用!,利用相对绝对的原理,把列数取出和前面的”A”组合成一个新的引用,注意相对引用的使用!,第五章 公式与函数,5.7.2 日报表的自动累计日报表是每天必做的工作,累计工作则是日报表中重要的一项。如果是比较复杂的日报表，手工输入累计值或每张逐一设置公式，是一件很麻烦的事。这时就要用到I

32、NDIRECT函数INDIRECT函数实例（P135）日,=INDIRECT(DAY(C2)-1&日!D13)+D12,利用取日期中的天数，减去1再加上“日!D13”就得到了上前一天报表的本月累计的引用,前一天报表的D13是前一天的本月累计，再加上今天的本日累计就得出今天的本月累计,第五章 公式与函数,5.7.3 二级下拉列表设置二级下拉列表是指在选取一级下拉列表内容后，在后面二级下拉列表中可以显示相对应的子列表。光用我们以前学到的数据有效性已经不够了，这儿要用到INDIRECT函数INDIRECT函数实例（P136）,=INDIRECT(A2),这里的A2的内容是”河南省“，但用了INDIR

33、ECT函数后，返回是的“河南省“所对应的名称代表的区域,举一反三:能不能做三级下拉列表?,第五章 公式与函数,5.8 其他数学函数本节介绍的ROUND和MOD函数用法简单,但用途却极为广泛.5.8.1 用ROUND函数处理工资表的计算误差语法:=ROUND(数字,指定的位数),如果指定的位数大于0,则舍入到指定的小数位;如果指定的位数等于0,则舍入到最接近的整数;如果指定的位数小于0,则在小数舍入;,例:=ROUND(25.265,2)=25.27,例:=ROUND(25.265,0)=25,例:=ROUND(25.265,-1)=30,第五章 公式与函数,5.8.1 ROUND函数,指定的位

34、数,数字,打开EXCEL,第五章 公式与函数,5.8.1用ROUND函数处理工资表的计算误差ROUND函数实例（P134）,=ROUND(G4,2)通过设置小数点位数,只是显示上保留两位小数,实质上单元内部的小数位数并没有改变,而用ROUND函数是实质上把多余的位数舍掉了,而不仅仅是在显示上。,第五章 公式与函数,5.8.2 用MOD函数隔行填充颜色功能：返回两数相除的余数，结果的正负号与被除数相同。语法:=MOD(被除数,除数),例:=MOD(4,2)=0例:=MOD(5,2)=1例:=MOD(-10,4)=-2例:=MOD(-10,-4)=-2,第五章 公式与函数,5.8.2 MOD函数,

35、除数,被除数,打开EXCEL,第五章 公式与函数,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 C

36、OUNTA函数,参数2,参数1,打开EXCEL,第五章 公式与函数,5.9.1用COUNTA函数自动统计工资表人数COUNTA函数实例（P134）,=COUNTA(B2:B5)不便于插入行=COUNTA(INDIRECT(B2:B&ROW()-1)利用INDIRECT函数把字符串转换为引用，利用ROW函数把当前行数减去1，得出上一行的行数。,第五章 公式与函数,5.9.2用MAX函数设置变动序号MAX和MIN函数是分别求最大值和最小值的函数，它们常在复杂的数组公式中出现。功能：MAX求一组数中的最大值；MIN求一组数中的最小值语法：=MAX(数值1,数值2,)=MIN(数值1,数值2,)MAX

37、和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函数实例（P142）,=LARGE(B2

38、:B17,1),=SMALL(B2:B17,1),当有两个第二大值相等的时候，一个会作为第二大，另一个作为第三大,第五章 公式与函数,5.10 其他查找引用函数查找引用函数在单元格查询，数据表之间的取数、核对方面有着极其广泛的用途，前面我们学过VLOOKUP函数，以下介绍其他查找引用函数5.10.1 ROW和COLUMN生成公式变动函数语法:=ROW(引用的单元格或单元格区域)=COLUMN(引用的单元格或单元格区域),如果引用的单元格或单元格区域省略,返回的为当前行号;如果引用的是一个单元格,返回的是引用单元格所在的行号或列号;如果引用的是一个单元格区域,返回的是单元格区域左上角所在的行号或

39、列号。,第五章 公式与函数,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函数实例（P141）,C14=VLOOKUP($B14,$B$2:$F$9,2,0),C15=VLOOKUP($B15,$B$2:$F$9,COLUMN(B1),0),这两个公式的区别在于相对列数的不同。第一个公式直接用数字2，后面的公式必须

40、要手工来改相对列数，这样不便于公式的复制；第二个公式用的是COLUMN(B1),这样随着公式向右的拖动，列发生改变，函数返回的值也随之改变，就可以实现公式的轻松复制。,第五章 公式与函数,5.10 其他查找引用函数ROW和COLUMN函数实例（P141）,I3=LARGE($F$2:$F$9,1),J3=LARGE($F$2:$F$9,ROW(A1),这两个公式的区别在于第N大值的不同。第一个公式直接用数字1，后面的公式必须要手工来改第N大值，这样不便于公式的复制；第二个公式用的是ROW(A1),这样随着公式向下的拖动，行发生改变，函数返回的值也随之改变，就可以实现公式的轻松复制。,第五章 公

41、式与函数,5.10 其他查找引用函数5.10.2 MATCH和INDEX实现双向查找MATCH是查询函数,INDEX是引用函数，在实际查找并返回值过程中，MATCH和INDEX总是结对出现在公式中。功能：MATCH返回在指定方式下与指定数值匹配的数组中元素的相应位置INDEX返回表格、区域中的数值或数值的引用。,第五章 公式与函数,5.10 其他查找引用函数5.10.2 MATCH和INDEX实现双向查找语法：=MATCH(查找的值,查找区域,查找类型),查找的类型为三种：-1，0，1如果为1,查找小于或等于查找值的最大数值；如果为0,查找等于查找值的第一个数值；如果为-1,查找大于或等于查找

42、值的最小数值；如果省略，则默认为1,在哪儿查找?,要查找的内容,第五章 公式与函数,5.10 其他查找引用函数5.10.2 MATCH和INDEX实现双向查找语法：=INDEX(区域,行数,列数),行数和列数：是指相对于该区域的行数和列数，而并非相对整个工作表的行数和列数。,为单元格 区域或数组常数,MATCH和INDEX函数实例（P144）,=INDEX($A$1:$H$6,MATCH(A11,$A$1:$A$6,0),MATCH(B11,$A$1:$H$1,0),第五章 公式与函数,行数,为单元格 区域或数组常数,=INDEX($A$1:$H$6,MATCH(A11,$A$1:$A$6,0

43、),MATCH(B11,$A$1:$H$1,0),列数,查找的类型为0,查找等于查找值的第一个数值，第一个结果是2，表示行数为2；第二个结果为4，表示列数为4,在哪儿查找?,要查找的内容：部门、3月费用计划,第五章 公式与函数,5.10 其他查找引用函数5.10.3 OFFSET实现单元格区域移动OFFSET函数总能返回一个变动的区域或变动的单元格,这就使公式中的单元格或单元格区域,可以随着给定条件值的变化而变化,从而达到灵活设置公式的目的。功能:以引用的左上单元格为基准,按指定的行偏移、列偏列、行数、列数返回一个新的引用。,第五章 公式与函数,5.10 其他查找引用函数5.10.3 OFFS

44、ET实现单元格区域移动(实例P145)语法：=OFFSET(引用,行偏移,列偏移,行数,列数),行数和列数：返回区域的行数和列数,如与引用的行数或列数相同,可省略,返回区域的左上单元格相对于引用的左上单元格的行(列)偏移数,向上(左)为负值向下(右)为正值,同一行(列)为0,A2的值,B2:C3的和,D2:F4的和,第五章 公式与函数,5.10 其他查找引用函数5.10.4 用ADDRESS和HYPERLINK实现入库单快速查找1.ADDRESS函数功能:按照给定的行号和列标,建立文本类型的单元格地址。,第五章 公式与函数,5.10 其他查找引用函数5.10.3用ADDRESS和HYPERLI

45、NK实现入库单快速查找语法：=ADDRESS(行数,列数,引用类型,引用工作簿及工作表名称),1.绝对引用(可省略)2.绝对行号,相对列标3.相对行号,绝对列标 4.相对引用,“$A$1”,“A$1”,“$A1”,“A1”,第五章 公式与函数,5.10 其他查找引用函数5.10.4 用ADDRESS和HYPERLINK实现入库单快速查找2.HYPERLINK函数功能:创建一个快捷方式(跳转),用以打开保存在网络服务器、Intranet或Internet上的文件。,第五章 公式与函数,5.10 其他查找引用函数5.10.3用ADDRESS和HYPERLINK实现入库单快速查找语法:=HYPERL

46、INK(链接路径或文件名，链接处显示的文字)实例 P146,第五章 公式与函数,5.11 文本函数5.11.1 字符串的查找和截取功能:LEFT:取左边的N个字符RIGHT:取右边的N个字符MID:根据指定位置取指定位数字符LEN:字符串的字符数FIND:在指定字符串中查找指定字符的位置SEARCH:查找特定字符或文本串的位置,第五章 公式与函数,5.11 文本函数语法:实例见:P148LEFT(字符串或引用单元格,N)RIGHT(字符串或引用单元格,N)MID(字符串或引用单元格,开始截取位数,长度)LEN(字符串或引用单元格)FIND(查找的字符,字符串或引用单元格,开始查找位数)SEAR

47、CH(查找的字符,字符串或引用单元格,开始查找位数)说明:FIND区分大小写并且不允许使用通配符,而SEARCH可以,AB,FG,BCD,7,6,5,第五章 公式与函数,5.11 文本函数5.11.2 SUBSTITUTE和REPLACE实现指定内容替换功能:SUBSTITUTE:在某下文本中替换指定的文本.REPLACE:在某一文本中替换指定位置处的任意文本,例:=SUBSTITUTE(A2,”国税局”,”国家税务局”),例:=REPLACE(A2,4,3,”国家税务局”),第五章 公式与函数,5.11 文本函数5.11.2 SUBSTITUTE和REPLACE实现指定内容替换语法：=SUB

48、STITUTE(字符串或引用单元格,要替换的文本,要替换旧文本的新文本,替换掉第N个旧的文本)语法：=REPLACE(字符串或引用单元格,要替换起始位置,要替换原文本的字符个数,新的文本),公式中的N如果省略,默认替换全部的旧文本字符,第五章 公式与函数,5.12 时间函数5.12.1 用TODAY和NOW计算和更新库龄本小节介绍的是能分别显示当前日期函数TODAY和当前时间函数NOW,它们的特点可以随着日期和时间的变化而变化,但这种更新是在工作簿打开或工作表重新计算的前提下.在下面几中常见情况下,工作表会重新计算.(1)双击工作表中的某个单元格(2)编辑任一个单元格内容(3)按F9键,第五章

49、 公式与函数,5.12 时间函数5.12.1 用TODAY和NOW计算和更新库龄P150功能:TODAY:显示当前日期NOW:显示当前时间语法:TODAY()NOW(),二者均没有参数,但括号不能省略,第五章 公式与函数,5.12 时间函数5.12.1 日期格式的拆分与组合在对日期进行处理时,常根据实际需要对日期进行拆分和组合功能:YEAR:返回具体日期的年份MONTH:返回具体日期的月份DAY:返回具体日期的天数DATE:根据已知年、月、日数值，返回具体日期WEEKDAY：转换日期为星期中的一天，一般用于判断是一周的第几天,第五章 公式与函数,5.12.1 日期格式的拆分与组合语法:实例见:

50、P150YEAR(日期)MONTH(日期)DAY(日期)DATE(年、月、日)WEEKDAY(日期，返回值的类型),1989,07,08,1989-07-08,第五章 公式与函数,5.12.1 日期格式的拆分与组合语法:实例见:P150WEEKDAY(日期，返回值的类型),第五章 公式与函数,5.12 时间函数5.12.2 用DATEDIF计算日期间隔DATEIF是EXCEL的一个隐藏函数，且在EXCEL2002以前版本中，帮助文件没有它的介绍，但事实上它是一个十分有用的函数。功能：可以计算两日期之间的天数、月数、年数语法：DATEDIF(开始日期,结束日期,单位代码),开始日期要比结束日期要