【文档说明】EXCEL财务会计与日常管理知识分析应用课件.ppt，共(131)页，1.123 MB，由小橙橙上传

转载请保留链接：https://www.ichengzhen.cn/view-2595.html

以下为本文档部分文字说明：

第一章EXCEL文件管理❖新建文件❖工作表的隐藏❖文件的安全与保护❖快速打印指定表格第二章单元格编辑❖录入相同的内容❖下拉列表录入❖录入内容的限制限制数字格式或大小文本长度单\多条件限定限制重复输入录入区域的限制第二章单元格

编辑❖选取使用定位选取❖选择性粘贴粘贴数值粘贴运算❖隐藏❖查找第三章条件格式❖3.1条件格式的设立、添加设立条件格式添加条件❖3.2定义条件单元格数值条件公式条件❖3.3条件格式实例应用❖3.4小结第三章条件格式❖3.1条件格式的设立、添加设立条件

格式添加条件❖3.2定义条件单元格数值条件公式条件❖3.3条件格式实例应用❖3.4小结第三章条件格式❖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.5代码录入的错误显示条件:1.代码位数不等于五位2.代码位数不等于八位公式:=AND(LEN($B2)<>5,LEN($B2)<>8,$B2<>0)第三章条件格式❖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！……）第三章条件格式❖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.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)同列不同行的条件是或者关系第四章数据表和图表2.设置筛选项目.复制标题行到要显示筛选结果的第一行.❖复制和手工输入有什么区别?3.数据→筛选→高级筛选数据源区域设置条件的区域第四章数据表和图表❖4.4.2筛

选本列不重复记录❖4.4.3筛选两区域重复记录❖4.4.4筛选两表中不重复记录=COUNTIF($D$16:$D$24,D3)=0第四章数据表和图表❖4.5数据透视表数据透视表是一种对数据清单快速建立汇总的动态总结报告,它可以随时调换行列的位置而进行不同形式的汇总,

是Excel提供的一个极为有效的汇总工具。数据透视表在销售数据汇总、出入库汇总及明细账汇总等方面有着广泛应用一个普通的数据表你的工作表含有大量数据，但是你知道这些数字的含义吗？这些数据能够解答您的问题吗？不普通的数据透视表数据透视表提供了一种快速且强大的方式来分析数值数据、以不

同的方式查看相同的数据以及回答有关这些数据的问题。第四章数据表和图表❖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.1创建数据透视表4.5.2调整数

据透视表格式4.5.3在数据透视表中设置公式4.5.4其他编辑❖调整汇总方式❖显示或隐藏汇总行❖数据透视表的更新和自动更新第四章数据表和图表❖4.5.7多个数据透视表合并第四章数据表和图表❖4.5.7多个数据透视表合并第四章数据表和图表❖

4.6图表4.6.1创建图表插入→图表四步曲❖设置图表类型❖设置数据源❖设置图表选项❖设置图表位置第四章数据表和图表-----四步曲第四章数据表和图表❖4.6.2双坐标图表0501001502002503003504004505001

月2月3月4月5月6月7月8月01000000020000000300000004000000050000000600000007000000080000000库存量系列3库存金额库存量01002003004005006001月2月3月4月5月6

月7月库存量0501001502002503003504004505001月2月3月4月5月6月7月8月01000000020000000300000004000000050000000600000007000000080000000库存量库存金

额单坐标图表常规设置的双坐标图表双坐标图表第四章数据表和图表❖4.6.2双坐标图表1.添加系列2.修改系列格式第五章公式与函数❖公式与函数是Excel的精华所在,它为分析和处理数据提供了方便,特别是在处理大批量数据和进行复杂数据分析等方面更能发挥它的强大功能。本章

在介绍函数的用法时，着重介绍它们在财务工作中的应用。❖本章要点：IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函数的用法IF、SUM、COUNTIF、SUMPRODUCT和VLO

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

、COUNTIF、SUMPRODUCT和VLOOKUP等常用函数的用法IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函数的实例应用第五章公式与函数5.1IF函数IF函数是工作中最常用函数之一，它可以根据设置的条件进行运算或返回值。

语法：=IF（逻辑表达式，TRUE，FALSE）逻辑表达式不成立返回的值逻辑表达式成立返回的值返回值为TRUE或FALSE的逻辑表达式例:=IF(5>3,"对","不对")例:=IF(5<3,"不对

","对")第五章公式与函数5.1IF函数逻辑表达式不成立返回的值逻辑表达式成立返回的值返回值为TRUE或FALSE的逻辑表达式打开EXCEL第五章公式与函数5.1.1单条件和多条件判断1.单条件返回文本IF函数实例（P113）逻辑表达式不成立返回的值逻辑表达式成立返回的值逻辑表达条件

：比较实际数和计划数的大小D2=IF（C2>B2,"节约"，"超支"）第五章公式与函数5.1.1单条件和多条件判断2.单条件判断并运算IF函数实例（P113）逻辑表达式不成立进行运算的表达式逻辑表达式成立时进行运算的表达

式逻辑表达条件：销售额是否超过3万元C2=IF（B2>30000,B2*0.015,B2*0.01）第五章公式与函数5.1.1单条件和多条件判断3.单条件判断返回引用区域IF函数实例（P113）逻辑表达式不成立返回的区域逻辑表达式成立时返回的区域逻辑表达条件：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(B2<20000,B2*1%,IF(B2<25000,B2*2%,IF(B2<3

5000,B2*3%,B2*4%)))=IF(B2<20000,B2*1%,IF(20000=<B2<25000,B2*2%,IF(25000=<B2<35000,B2*3%,B2*4%)))常见的错误常见的错误第五章公式与函数

5.1.1单条件和多条件判断4.多条件判断IF函数实例（P113）=IF(B2<10000,B2*1%,0)+IF(AND(B2<20000,B2>=10000),B2*2%,0)+IF(AND(B2<30000,B2>=20000),B2*3%,0)+IF(AND(B2<40000,

B2>=30000),B2*4%,0)+IF(AND(B2<50000,B2>=40000),B2*5%,0)+IF(AND(B2<60000,B2>=50000),B2*6%,0)+IF(AND(B2<70000,B2>=60000),B2*7%

,0)+IF(AND(B2<80000,B2>=70000),B2*8%,0)+IF(B2>80000,B2*9%,0)第五章公式与函数课堂练习：成绩表小结：这节课讲了IF函数的使用，有以下四种情况：❖单条件返回文本❖单条件进行运

算❖单条件返回区域❖多条件判断IF函数在实际工作中应用很广，要注意不同函数中参数的含义。第五章公式与函数5.2SUM函数SUM函数是工作中最常用函数之一，几乎所有的表格中都有合并的运算。语法：=SUM（参数1，参数2，……参数30）参数最多为30个参数可以为引用，数值，

文本，表达式和数组例:=SUM(5,3,2,1)例:=(a1:b1)第五章公式与函数❖5.2.1连续、不连续及交叉区域求和例1：连续区域的求和=SUM(A1:C5)例1：不连续区域的求和=SUM(A1，B3，D22)例1：交叉区域的求和=SUM(1:3C:C)注意：这儿有空格第五章公式与函

数❖5.2.2多工作表自动汇总是SUM函数的三维应用例：=SUM('1日:空白'!C5)单引号的作用是去掉工作表名的空格工作表的名称必须加感叹号！第五章公式与函数5.1SUMIF函数SUMIF函数是根据指定条件对若干单元格求和。语法：=SUMIF（

条件范围，条件，求和范围）需要求和的实际范围，省略则对条件范围求和只能用单条件而不能用复合条件，可以使用通配符用于条件判断的单元格区域例:=SUMIF(B2:B9,“副教授”,D2:D9)例:=SUMIF

(D2:D9,“>2000")第五章公式与函数5.1SUMIF函数求和范围条件条件范围打开EXCEL第五章公式与函数5.3.1单条件求和问题1:根据B列销售金额求和,要求对销售金额大于2000的数值求和SU

MIF函数实例（P121）省略求和范围,对条件范围进行求和条件条件范围=SUMIF（B2:B9,“>2000"）第五章公式与函数5.3.1单条件求和问题2:根据商品名称求和,要求对商品名称为A1的销售

金额求和SUMIF函数实例（P121）求和范围条件条件范围=SUMIF（A2:A9,“A1“,B2:B9）第五章公式与函数5.3.1单条件求和问题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）第五章公式与函数5.3.1单条件求和问题2:根据商品

名称求和,要求对商品名称为A1的销售金额求和SUMIF函数实例（P113）求和范围条件条件范围=SUMIF（A2:A9,“A1“,B2:B9）第五章公式与函数5.3.1单条件求和问题6:根据商品名称求第四五个字符为”A2”,且字符总长度为6个字符的销售金额求和S

UMIF函数实例（P121）求和范围条件条件范围=SUMIF（A2:A9,“???A2?“,B2:B9）第五章公式与函数5.3.2多条件及区间求和问题1:符合入库数量大于4小于10的商品,对其入库数量求和SUMIF函数实例（P122）=SUMIF(

C2:C9,“>4“)-SUMIF(C2:C9,“>=10“)410第五章公式与函数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,”1”,B3:E11)注意两个区域的大小要一致第五章公式与

函数5.4COUNTIF函数COUNTIF函数是根据指计算给定区域内满足特定条件单元格数目。语法：=COUNTIF（条件范围，条件）可以为数字,表达式或文本用于条件判断的单元格区域例:=COUNTIF(B

2:B9,“副教授”)例:=COUNTIF(D2:D9,“>2000")第五章公式与函数5.4COUNTIF函数条件条件范围打开EXCEL第五章公式与函数5.4.1按条件计数问题1:统计实发工资大于2500的人数COUNT

IF函数实例（P124）条件条件范围=COUNTIF（E2:E7,“>2500"）第五章公式与函数5.4.1按条件计数问题2:统计财务部的人数COUNTIF函数实例（P124）条件条件范围=COUNTIF（A2:A7,”财务部”）第五章公式与

函数5.4.2COUNTIF计数常见的错误1.区域选取的影响COUNTIF函数实例（P124）=COUNTIF（B3:B8,C3:C8,”>6”）=COUNTIF（B3:C8,”>6”）第五章公式与函数5.4.2COUNTIF计数常见的错误2.数字格式的影

响COUNTIF函数实例（P124）解决办法:把文本数字转换成数值型第五章公式与函数5.4.2COUNTIF计数常见的错误3.长数字的影响COUNTIF函数实例（P124）解决办法:在长数字中添加*号第五章公式与函数5.5SUMPRODUCT函数SUMPRODUC

T函数是在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。语法：=SUMPRODUCT（数组1，数组2,数组3，……）数组参数必须具有相同的维数，否则函数SUMPRODUCT将返回错误值：“#VALUE！”例:=SUMPRODUCT(1,2,3

,4,5)=?=1*2*3*4*5=120第五章公式与函数5.5SUMPRODUCT函数数组2数组1打开EXCEL数组3第五章公式与函数5.5.1库存金额的简便运算不用设置金额列,直接计算出总入库金额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）第五章公式与函数❖多条件计数和求和例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)统计数据:入库数量第五章公

式与函数❖多条件计数和求和例5-17在入库明细汇总表中,根据要求计算举一反三:1.计算供应商A1或A2的冰箱入库数量。2.计算供应商A1的冰箱或彩电的品种数。SUMPRODUCT函数实例（P126）第五章公式与函数5.6VLOOKUP函数VL

OOKUP函数是在表格或数值组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值。它是最常用的函数之一功能：1.指定位置查找和引用数据2.表与表的核对3.利用模糊运算进行区间查询第五章公式与函数5.6VLOOKUP

函数语法:=VLOOKUP(查找目标,查找区域,相对列数,TRUE或FALSE)要找的内容在查找区域中的哪一列?在哪儿查找?注意:查找内容必须在查找区域的第一列!!要查找的内容例:=VLOOKUP(B

2,$D$2:$H$9,3,0)TRUE:模糊查找,FALSE:精确查找,可以用其1和0代替第五章公式与函数5.6VLOOKUP函数相对列数查找区域查找目标打开EXCEL精确查找或模糊查找第五章公式与函数5.6.1单个区域查找问题1:

要求在C列,从员工信息表中根据姓名查找其级别.VLOOKUP函数实例（P129）=VLOOKUP(B2,$G$9:$H$14,2,0)要找的内容在查找区域中的第2列在哪儿查找?员工信息表,注意绝对引用的使用!!要查找的内容:张三精确查

找,可以用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)要找的内容在查找区域中的第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函数查找时,常遇到下列几种查询错误:参数设置错误空格

及不可见字符引起的错误格式不一致引起的错误第五章公式与函数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)不可见字符引起的错误3.数字格式不一致引起的错误第五章公式与函数5.7INDIRECT函数INDIRECT是一个非常重要的函数,它可以把随意组合或者插入变量的字符串转换成可以使用的引用。功能：返回由文字串指定的引用，并对引用进行

计算，显示其内容。第五章公式与函数5.7INDIRECT函数语法:=INDIRECT(文本字符串,引用类型)TRUE：A1类型FALSE：R1C1类型省略为A1类型对单元格的引用或字符串，此单元格可以包含A1样式的引用

，定义为引用的名称或对文字串单元格的引用。例:=INDIRECT(“R4C4”,0)例:=INDIRECT(“A1”)第五章公式与函数5.6INDIRECT函数引用类型文本字符串打开EXCEL第五章公式与函数5.7.1行列转置以前我

们学过用选择性粘贴,现在我们来学习用公式进行行列的转置.INDIRECT函数实例（P134）C1=INDIRECT("A"&COLUMN(A1))A结合后面的数字组合成一个新的引用,注意相对引用的使用!!利用相对绝对的原理,把列数取出和前面的”A”组合成一个新的引用

,注意相对引用的使用!!第五章公式与函数5.7.2日报表的自动累计日报表是每天必做的工作,累计工作则是日报表中重要的一项。如果是比较复杂的日报表，手工输入累计值或每张逐一设置公式，是一件很麻烦的事。这时就要用到INDIRECT函数INDIRECT函数实例（P135）日=INDIR

ECT(DAY(C2)-1&"日!D13")+D12利用取日期中的天数，减去1再加上“日!D13”就得到了上前一天报表的本月累计的引用前一天报表的D13是前一天的本月累计，再加上今天的本日累计就得出今天的本月累计第五章公式

与函数5.7.3二级下拉列表设置二级下拉列表是指在选取一级下拉列表内容后，在后面二级下拉列表中可以显示相对应的子列表。光用我们以前学到的数据有效性已经不够了，这儿要用到INDIRECT函数INDIRECT函数实例（P136）=INDIRECT(A2)这里的A2的内容是”河

南省“，但用了INDIRECT函数后，返回是的“河南省“所对应的名称代表的区域举一反三:能不能做三级下拉列表?第五章公式与函数❖5.8其他数学函数本节介绍的ROUND和MOD函数用法简单,但用途却极为广泛.5.8.1用ROUND函数处理工资表的计算误差语法

:=ROUND(数字,指定的位数)如果指定的位数大于0,则舍入到指定的小数位;如果指定的位数等于0,则舍入到最接近的整数;如果指定的位数小于0,则在小数舍入;例:=ROUND(25.265,2)=25.27例:=ROUND(25.265,0)=25例:=R

OUND(25.265,-1)=30第五章公式与函数5.8.1ROUND函数指定的位数数字打开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.2MOD函数除数被除数打开EXCEL第五章公式与函数5.8.2用MOD隔行填充颜色MOD函数实例（P138）=MOD(R

OW(),2)=0返回当前行的行数第五章公式与函数❖5.9其他统计函数统计函数是工作中常用的函数,以前我们学过SUM,COUNT等函数。本节将介绍其他几个统计函数。5.9.1用COUNTA函数自动统计工资

表人数功能:返回参数组中非空值的数目。语法:=COUNTA(参数1,参数2,参数3……参数N)N最大值为30；可以进行多工作表的三维引用注意和COUNT函数的对比！！COUNT函数只统计数值型数据例:=COUN

TA(A1:B67)第五章公式与函数5.9.1COUNTA函数参数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和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:B17,1)=S

MALL(B2:B17,1)当有两个第二大值相等的时候，一个会作为第二大，另一个作为第三大第五章公式与函数❖5.10其他查找引用函数查找引用函数在单元格查询，数据表之间的取数、核对方面有着极其广泛的用途，前面我们学过VLOOKUP函数，以下介绍其他查找引用函数5.10.1ROW和COL

UMN生成公式变动函数语法:=ROW(引用的单元格或单元格区域)=COLUMN(引用的单元格或单元格区域)如果引用的单元格或单元格区域省略,返回的为当前行号;如果引用的是一个单元格,返回的是引用单元格所在的行号或列号;如果引用的是一个单元格区域,返回的是单元格

区域左上角所在的行号或列号。第五章公式与函数❖5.10其他查找引用函数❖如果公式在A10单元格例:=ROW()=？例:=ROW(C25)=？例:=ROW(D2:E10)=？例:=COLUMN()=？例:=COLUMN(C25)=？例:=CO

LUMN(D2:E10)=？10252134第五章公式与函数❖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，后面的公式必须要手工来改相对列数，这样不便于公式的复制；第二个公式用的是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),这样随着公式向下的拖动，行发生改变，函数返回的值也随之改变，就可以实现公式的轻松复制。第五章公式与函数❖5.10其他查找引用函数5.10.2MATCH和INDEX实现双向查

找MATCH是查询函数,INDEX是引用函数，在实际查找并返回值过程中，MATCH和INDEX总是结对出现在公式中。功能：MATCH返回在指定方式下与指定数值匹配的数组中元素的相应位置INDEX返回表格、区域中的数值或数值的引用。第五章公式与函数❖5.10其他查找引用函数5.

10.2MATCH和INDEX实现双向查找语法：=MATCH(查找的值,查找区域,查找类型)查找的类型为三种：-1，0，1如果为1,查找小于或等于查找值的最大数值；如果为0,查找等于查找值的第一个数值；如果为-1,

查找大于或等于查找值的最小数值；如果省略，则默认为1在哪儿查找?要查找的内容第五章公式与函数❖5.10其他查找引用函数5.10.2MATCH和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),MATCH(B11,$A$1:$H$1,0))列数查找的类型为0,查找等于查找值的第一个数值，第一个结果是2，表示行数为2；第二个结果为4，表示列数为4在哪儿查找?要查找的内容：部门、3月费用计划第五章公式与函数❖5.10其他查找引用函数5

.10.3OFFSET实现单元格区域移动OFFSET函数总能返回一个变动的区域或变动的单元格,这就使公式中的单元格或单元格区域,可以随着给定条件值的变化而变化,从而达到灵活设置公式的目的。功能:以引用的左上单元格为基准,按指定的行偏移、列偏列、行数、列数返回一个新的引用。例:=

OFFSET(A1,1)？例:=SUM(OFFSET(B2,,,2,2))？例:=SUM(OFFSET(A1,1,3,3,3))？第五章公式与函数❖5.10其他查找引用函数5.10.3OFFSET实现单元格区域移动(实例P145)

语法：=OFFSET(引用,行偏移,列偏移,行数,列数)行数和列数：返回区域的行数和列数,如与引用的行数或列数相同,可省略返回区域的左上单元格相对于引用的左上单元格的行(列)偏移数,向上(左)为负值向下(右)为正值,同一行(

列)为0A2的值B2:C3的和D2:F4的和第五章公式与函数❖5.10其他查找引用函数5.10.4用ADDRESS和HYPERLINK实现入库单快速查找1.ADDRESS函数功能:按照给定的行号和列标,建立文本类型的单元格地址。例:=

ADDRESS(1,1,1)？例:=ADDRESS(1,1,2)？例:=ADDRESS(1,1,3)？例:=ADDRESS(1,1,4)？第五章公式与函数❖5.10其他查找引用函数5.10.3用ADDRESS和HYPERLINK实现入库

单快速查找语法：=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实现

入库单快速查找语法:=HYPERLINK(链接路径或文件名，链接处显示的文字)❖实例P146第五章公式与函数❖5.11文本函数5.11.1字符串的查找和截取功能:❖LEFT:取左边的N个字符❖RIGHT:取右边的N个字符❖MID:根据指定位置取指定位数字符❖LEN:字符串的字

符数❖FIND:在指定字符串中查找指定字符的位置❖SEARCH:查找特定字符或文本串的位置第五章公式与函数❖5.11文本函数语法:实例见:P148❖LEFT(字符串或引用单元格,N)❖RIGHT(字符串或引用单元格,N)❖MID(字符串或引用单元

格,开始截取位数,长度)❖LEN(字符串或引用单元格)❖FIND(查找的字符,字符串或引用单元格,开始查找位数)❖SEARCH(查找的字符,字符串或引用单元格,开始查找位数)说明:FIND区分大小写并且不允许使用通配符,而SEARCH可以例:=LEFT(“ABC

DEFG”,2)？例:=RIGHT(“ABCDEFG”,2)？例:=MID(“ABCDEFG”,2,3)？例:=LEN(“ABCDEFG”,)？例:=FIND(“F”,“ABCDEFG”,1)？例:=

SEARCH(“?f”,“ABCDEFG”,1)？ABFGBCD765第五章公式与函数❖5.11文本函数5.11.2SUBSTITUTE和REPLACE实现指定内容替换功能:❖SUBSTITUTE:在某

下文本中替换指定的文本.❖REPLACE:在某一文本中替换指定位置处的任意文本例:=SUBSTITUTE(A2,”国税局”,”国家税务局”)例:=REPLACE(A2,4,3,”国家税务局”)第五章公式与函数❖5.11文本函数5.11.2SUBST

ITUTE和REPLACE实现指定内容替换语法：=SUBSTITUTE(字符串或引用单元格,要替换的文本,要替换旧文本的新文本,替换掉第N个旧的文本)语法：=REPLACE(字符串或引用单元格,要替换起始位置,要替换原文本的字符个数,新的文本)公式

中的N如果省略,默认替换全部的旧文本字符第五章公式与函数❖5.12时间函数5.12.1用TODAY和NOW计算和更新库龄本小节介绍的是能分别显示当前日期函数TODAY和当前时间函数NOW,它们的特点可以随着日期和时间的变化而变化,但这种更新是在工作簿打开或工作表重新计算的前提下

.在下面几中常见情况下,工作表会重新计算.❖(1)双击工作表中的某个单元格❖(2)编辑任一个单元格内容❖(3)按F9键第五章公式与函数❖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日期格式的拆分与组合语法:实例见:P150❖YEAR(日期)❖MONTH(日期)

❖DAY(日期)❖DATE(年、月、日)❖WEEKDAY(日期，返回值的类型)例:=YEAR(1989-07-08)？例:=MONTH(1989-07-08)？例:=DAY(1989-07-08)？例:=DATE(1989，07，08）？例:=WEEKDAY(19890708,1)？198907

081989-07-08第五章公式与函数5.12.1日期格式的拆分与组合语法:实例见:P150❖WEEKDAY(日期，返回值的类型)第五章公式与函数❖5.12时间函数5.12.2用DATEDIF计算日期间隔DATEIF是EXCEL的一个隐藏函数，且在EXCEL2002以前版本中，帮助文件没有

它的介绍，但事实上它是一个十分有用的函数。功能：可以计算两日期之间的天数、月数、年数语法：DATEDIF(开始日期,结束日期,单位代码)开始日期要比结束日期要早,否则返回错误值。“Y”:返回整年数;“M”:返回整月数“D”:返回整天数“MD”:返回天数差,忽略日期的年和月“

YM”:返回月份差,忽略日期的年和天数“YD”:返回天数差,忽略日期的年第五章公式与函数❖5.12.3❖设置A1的值2010-12-22，B1的值为2011-12-31例:=DATEDIF(A1,B1,“Y”)=？例:=DATEDIF(

A1,B1,“M”)=？例:=DATEDIF(A1,B1,“D”)=？例:=DATEDIF(A1,B1,“MD”)=？例:=DATEDIF(A1,B1,“YM”)=？例:=DATEDIF(A1,B1,“YD”)=？112第五章公式与函数❖5.13IS类

信息函数IS类信息函数是一个具有相似特征函数的集合,它们根据判断返回TRUE或FALSE,常见的IS函数有以下几个:❖ISBLANK:判断是否为空格,是则返回TRUE,否则返回False❖ISERR:判断值是否任意错误值(除去#N/A)❖ISERROR:判断值是否任意错误值(#N

/A、#VALUE！、#REF！、#DIV/0！、#NUM！、#NAME？)❖ISLOGICAL：判断值是否为逻辑值❖ISNA：判断值是否为错误值#N/A（值不存在）❖ISNOTEXT：判断值是否为任意不是文本的项❖ISNUMBER：判断值是否为数字

❖ISREF：判断值是否为引用❖ISTEXT：判断值是否文本第五章公式与函数❖语法：IS函数名(数值或公式)❖实例见P152IS函数只能返回TRUE或FALSE，不能返回实际数值第五章公式与函数❖5.14综合实例本

节主要介绍函数在会计工作中的综合应用实例，并以同样的问题提供了多个解决思路和方法。5.14.1多条件模糊求和在第5.5节介绍SUMPRODUCT函数时，详细地对多条件计数以及求和做了说明，但是SU

MPRODUCT函数中不能直接使用通配符，所以无法独立完成多条件模糊计数以及求和。例：5-39见P153要求根据销售明细表统计出张三销售打印机的数量。第五章公式与函数❖5.14综合实例5.14.2工龄工资计算例：5-40见P1545.14.3工作日

统计工作日统计一般是除去周六或周日剩下的工作日天数。例：5-41见P1545.14.4金额填充在打印发票或其他票据金额时，为了能精确打印，常需要把金额拆分。例：5-42见P1565.14.5费用多栏账转化从财务软件中导出的费用明细账多栏账，通

常要占用三四页的宽度，这给打印和阅读带来了不便，如何把多栏账转化为更为直观的账表。例：5-43见P157小结❖本节介绍了在财务工作中常用到的函数，其应用实例也是根据在财务中常遇到的问题而设置的。函数功能非常强大，实际工作中不要仅局限于

函数的基本用法，而是要结合函数的特点去灵活运用。