《EXCEL在会计日常工作中的应用课件》由会员分享，可在线阅读，更多相关《EXCEL在会计日常工作中的应用课件（132页珍藏版）》请在金锄头文库上搜索。
1、EXCEL在会计日常工作中的应用授课老师:李 星1EXCEL在会计日常工作中的应用第一章 EXCEL文件管理新建文件工作表的隐藏文件的安全与保护快速打印指定表格2EXCEL在会计日常工作中的应用第二章 单元格编辑录入相同的内容下拉列表录入录入内容的限制限制数字格式或大小文本长度单多条件限定限制重复输入录入区域的限制3EXCEL在会计日常工作中的应用第二章 单元格编辑选取使用定位选取选择性粘贴粘贴数值粘贴运算隐藏查找4EXCEL在会计日常工作中的应用第三章 条件格式3.1条件格式的设立、添加设立条件格式添加条件3.2定义条件单元格数值条件公式条件3.3条件格式实例应用3.4小结5EXCEL在会计
2、日常工作中的应用第三章 条件格式3.1条件格式的设立、添加设立条件格式添加条件3.2定义条件单元格数值条件公式条件3.3条件格式实例应用3.4小结6EXCEL在会计日常工作中的应用第三章 条件格式3.1条件格式的设立、添加条件格式的设立、添加设立条件格式设立条件格式操作步骤：选中区域操作步骤：选中区域格式格式条件格式条件格式输入条件输入条件选择格式选择格式添加条件添加条件在在条件设置条件设置对话框中，单击添加按钮对话框中，单击添加按钮注：条件格式最多可以设置三个注：条件格式最多可以设置三个7EXCEL在会计日常工作中的应用第三章 条件格式3.2定义条件定义条件单元格数值单元格数值：用于简单的数
3、值对比：用于简单的数值对比公式公式：用于设置较为复杂的单元格内容：用于设置较为复杂的单元格内容3.2.1单元格数值单元格数值条件条件3.2.2公式公式条件条件8EXCEL在会计日常工作中的应用第三章 条件格式3.2定义条件定义条件单元格数值单元格数值：用于简单的数值对比：用于简单的数值对比公式公式：用于设置较为复杂的单元格内容：用于设置较为复杂的单元格内容3.2.1单元格数值单元格数值条件条件3.2.2公式公式条件条件9EXCEL在会计日常工作中的应用第三章 条件格式3.3条件格式实例应用条件格式实例应用3.3.1库龄分析的颜色提示库龄分析的颜色提示3.2.2应收账款催款提醒应收账款催款提醒监
4、视重复录入监视重复录入格式化账簿格式化账簿代码录入的错误显示代码录入的错误显示动态显示销售额排行动态显示销售额排行隐藏公式中的错误值隐藏公式中的错误值10EXCEL在会计日常工作中的应用第三章 条件格式3.3条件格式实例应用条件格式实例应用3.3.5代码录入的错误显示代码录入的错误显示条件条件:1.代码位数不等于五位代码位数不等于五位 2.代码位数不等于八位代码位数不等于八位公式公式:=AND(LEN($B2)5,LEN($B2)8,$B20)11EXCEL在会计日常工作中的应用第三章 条件格式3.3条件格式实例应用条件格式实例应用3.3.6动态显示销售额排行动态显示销售额排行条件条件:突出显
5、示前突出显示前N名商品的销售额名商品的销售额公式公式:=$D2=LARGE($D$2:$D$10,5)最大值函数最大值函数,MAX求出一个最大值求出一个最大值,LARGE可以求可以求第第N个最大值个最大值.12EXCEL在会计日常工作中的应用第三章 条件格式3.3条件格式实例应用条件格式实例应用3.3.7隐藏公式中错误值隐藏公式中错误值条件条件:把所有错误值隐藏把所有错误值隐藏公式公式:=ISERROR(D2)判断值是否为任意错误值（判断值是否为任意错误值（#N/A，VALUE！）13EXCEL在会计日常工作中的应用第三章 条件格式3.4小结小结本章对条件的创建、条件的设置作了详细介绍，同时也
6、本章对条件的创建、条件的设置作了详细介绍，同时也列举了大量应用实例。读者从实例中不难看出，如果想列举了大量应用实例。读者从实例中不难看出，如果想用好条件格式，掌握公式及函数的使用是非常重要的。用好条件格式，掌握公式及函数的使用是非常重要的。习题：习题：1、如何设置公式条件、如何设置公式条件2、如何突出显示重复录入内容？、如何突出显示重复录入内容？3、如何突出显示一列数据中最大前三个数字？、如何突出显示一列数据中最大前三个数字？4、如何添加和删除条件格式？、如何添加和删除条件格式？14EXCEL在会计日常工作中的应用第四章 数据表和图表4.1排序排序4.1.1数据表排序数据表排序4.1.2隔行插
7、入空行隔行插入空行4.2分列分列4.2.1拆分整列为多列拆分整列为多列4.2.2长文本型数字的导入长文本型数字的导入4.2.3转化字符为日期格式转化字符为日期格式4.3自动筛选自动筛选4.3.1自动筛选的实现自动筛选的实现4.3.2一次删除所有重复记录一次删除所有重复记录15EXCEL在会计日常工作中的应用第四章第四章 数据表和图表数据表和图表4.3自动筛选自动筛选4.3.1自动筛选的实现自动筛选的实现4.3.2一次删除所有重复记录一次删除所有重复记录添加一辅助列添加一辅助列,输入公式输入公式:=IF(COUNTIF($D2:D2,D2)1,1,2)这儿一定要注意理解绝对引用和相对引用的用法这
8、儿一定要注意理解绝对引用和相对引用的用法! !第一个第一个 板手板手 :=IF(COUNTIF($D$2:D2,D2)1,1,2):=IF(COUNTIF($D$2:D2,D2)1,1,2)第二个第二个 板手板手 :=IF(COUNTIF($D$2:D5,D5)1,1,2):=IF(COUNTIF($D$2:D5,D5)1,1,2)16EXCEL在会计日常工作中的应用第四章 数据表和图表4.4高级筛选高级筛选高级筛选功能灵活性强高级筛选功能灵活性强,和自动筛选相比有如下特和自动筛选相比有如下特点点:可以把筛选结果复制到其他位置可以把筛选结果复制到其他位置;需要设置条件区域需要设置条件区域,而且
9、可以使用更多条件而且可以使用更多条件;可筛选不重复记录可筛选不重复记录;4.4.1筛选符合条件的记录筛选符合条件的记录1.输入条件区域输入条件区域规则规则:(1)标题行和源区域一样标题行和源区域一样 (2)同行不同列的条件是并列关系同行不同列的条件是并列关系;(3)同列不同行的条件是或者关系同列不同行的条件是或者关系17EXCEL在会计日常工作中的应用第四章 数据表和图表2.设置筛选项目设置筛选项目.复制标题行到要显示筛选结果的第一行复制标题行到要显示筛选结果的第一行.复制和手工输入有什么区别复制和手工输入有什么区别?3.数据数据筛选筛选高级筛选高级筛选数据源区域设置条件的区域18EXCEL在
10、会计日常工作中的应用第四章 数据表和图表4.4.2筛选本列不重复记录筛选本列不重复记录4.4.3筛选两区域重复记录筛选两区域重复记录4.4.4筛选两表中不重复记录筛选两表中不重复记录=COUNTIF($D$16:$D$24,D3)=019EXCEL在会计日常工作中的应用第四章 数据表和图表4.5数据透视表数据透视表数据透视表是一种对数据清单快速建立汇总的动数据透视表是一种对数据清单快速建立汇总的动态总结报告态总结报告,它可以随时调换行列的位置而进行不它可以随时调换行列的位置而进行不同形式的汇总同形式的汇总,是是Excel提供的一个极为有效的汇总提供的一个极为有效的汇总工具。数据透视表在销售数据
11、汇总、出入库汇总工具。数据透视表在销售数据汇总、出入库汇总及明细账汇总等方面有着广泛应用及明细账汇总等方面有着广泛应用20EXCEL在会计日常工作中的应用一个普通的数据表日期日期地区地区姓名姓名产品名称产品名称销量销量销售单价销售单价销售金额销售金额2004-1-12004-1-1中南区中南区赵志赵志B产品品100100240024002400002400002004-1-102004-1-10西北区西北区杨东林杨东林B B产品产品25252400240060000600002004-1-152004-1-15华南区华南区杨磊杨磊D D产品产品4545120012005400054000200
12、4-2-12004-2-1中南区中南区孙之孙之B B产品产品6565240024001560001560002004-2-52004-2-5中南区中南区赵志赵志A A产品产品10101200120012000120002004-2-92004-2-9西北区西北区杨东林杨东林D D产品产品13131200120015600156002004-2-132004-2-13华南区华南区杨磊杨磊E产品品24244100410098400984002004-2-172004-2-17中南区中南区孙之孙之B B产品产品31312400240074400744002004-2-212004-2-21中南区中南
13、区赵志赵志D产品品12121200120014400144002004-2-252004-2-25西北区西北区杨东林杨东林A A产品产品42421200120050400504002004-2-292004-2-29华南区华南区杨磊杨磊B产品品10102400240024000240002004-3-42004-3-4中南区中南区孙之孙之D D产品产品9 91200120010800108002004-3-82004-3-8中南区中南区赵志赵志A产品品40401200120048000480002004-3-122004-3-12西北区西北区杨东林杨东林E E产品产品3232410041001
14、31200131200你你的工作表含有大量的工作表含有大量数据，但是数据，但是你你知道这知道这些数字的含义吗？这些数字的含义吗？这些数据能够解答您的些数据能够解答您的问题吗？问题吗？21EXCEL在会计日常工作中的应用不普通的数据透视表数据透视表提供了一种快速且强大的方数据透视表提供了一种快速且强大的方式来分析数值数据、以不同的方式查看相式来分析数值数据、以不同的方式查看相同的数据以及回答有关这些数据的问题。同的数据以及回答有关这些数据的问题。22EXCEL在会计日常工作中的应用第四章 数据表和图表4.5数据透视表数据透视表4.5.1创建数据透视表创建数据透视表三步曲之一：确定报表类型三步曲之
15、一：确定报表类型23EXCEL在会计日常工作中的应用第四章 数据表和图表4.5数据透视表数据透视表4.5.1创建数据透视表创建数据透视表三步曲之二：确定数据源三步曲之二：确定数据源24EXCEL在会计日常工作中的应用第四章 数据表和图表4.5数据透视表数据透视表4.5.1创建数据透视表创建数据透视表三步曲之三：布局三步曲之三：布局25EXCEL在会计日常工作中的应用第四章 数据表和图表4.5数据透视表数据透视表4.5.1创建数据透视表创建数据透视表三步曲之三：确定显示位置三步曲之三：确定显示位置26EXCEL在会计日常工作中的应用第四章 数据表和图表4.5.5固定数据透视表格式固定数据透视表格
16、式4.5.1创建数据透视表创建数据透视表4.5.2调整数据透视表格式调整数据透视表格式4.5.3在数据透视表中设置公式在数据透视表中设置公式4.5.4其他编辑其他编辑调整汇总方式调整汇总方式显示或隐藏汇总行显示或隐藏汇总行数据透视表的更新和自动更新数据透视表的更新和自动更新27EXCEL在会计日常工作中的应用第四章 数据表和图表4.5.7多个数据透视表合并多个数据透视表合并28EXCEL在会计日常工作中的应用第四章 数据表和图表4.5.7多个数据透视表合并多个数据透视表合并29EXCEL在会计日常工作中的应用第四章 数据表和图表4.6图表图表4.6.1创建图表创建图表插入插入图表图表四步曲四步
17、曲设置图表类型设置图表类型设置数据源设置数据源设置图表选项设置图表选项设置图表位置设置图表位置30EXCEL在会计日常工作中的应用第四章 数据表和图表-四步曲四步曲31EXCEL在会计日常工作中的应用第四章 数据表和图表4.6.2双坐标图表双坐标图表单坐标图表单坐标图表常规设置的常规设置的双坐标图表双坐标图表双坐标图表双坐标图表32EXCEL在会计日常工作中的应用第四章 数据表和图表4.6.2双坐标图表双坐标图表1.添加系列添加系列2.修改系列格式修改系列格式33EXCEL在会计日常工作中的应用第五章 公式与函数公式与函数是Excel的精华所在,它为分析和处理数据提供了方便,特别是在处理大批量
18、数据和进行复杂数据分析等方面更能发挥它的强大功能。本章在介绍函数的用法时，着重介绍它们在财务工作中的应用。本章要点：IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函数的用法IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函数的实例应用34EXCEL在会计日常工作中的应用第五章 公式与函数公式与函数是Excel的精华所在,它为分析和处理数据提供了方便,特别是在处理大批量数据和进行复杂数据分析等方面更能发挥它的强大功能。本章在介绍函数的用法时，着重介绍它们在财务工作中的应用。本章要点：IF、SUM、COUNTIF、SUMPRODUCT和VLOOKU
19、P等常用函数的用法IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函数的实例应用35EXCEL在会计日常工作中的应用第五章 公式与函数5.1 IF函数IF函数是工作中最常用函数之一，它可以根据设置的条件进行运算或返回值。语法：=IF（逻辑表达式，TRUE，FALSE）逻辑表达式不成逻辑表达式不成立返回的值立返回的值逻辑表达式成逻辑表达式成立返回的值立返回的值返回值为返回值为TRUETRUE或或FALSEFALSE的逻辑表达的逻辑表达式式例例:=IF(53,:=IF(53,对对,不对不对) )例例:=IF(53,:=IF(5B2,C2B2,节约节约 ， 超支超支 ）38E
20、XCEL在会计日常工作中的应用第五章 公式与函数5.1.1单条件和多条件判断2.单条件判断并运算运算IF函数实例（P113）逻辑表达式不成逻辑表达式不成立进行运算的表立进行运算的表达式达式逻辑表达式成逻辑表达式成立时进行运算立时进行运算的表达式的表达式逻辑表达条件：逻辑表达条件：销售额是否超过销售额是否超过3 3万元万元C2=IFC2=IF（B230000,B2*0.015,B2*0.01B230000,B2*0.015,B2*0.01）39EXCEL在会计日常工作中的应用第五章 公式与函数5.1.1单条件和多条件判断3.单条件判断返回引用区域引用区域IF函数实例（P113）逻辑表达式不成逻辑
21、表达式不成立返回的区域立返回的区域逻辑表达式成逻辑表达式成立时返回的区立时返回的区域域逻辑表达条件：逻辑表达条件：A2A2是否等于销售一是否等于销售一部部=SUM(IF(A2=SUM(IF(A2=销售一部销售一部,B5:B9,E5:E9),B5:B9,E5:E9)40EXCEL在会计日常工作中的应用第五章 公式与函数5.1.1单条件和多条件判断4.多多条件判断IF函数实例（P113）逻辑表达式不成逻辑表达式不成立返回的表达式立返回的表达式逻辑表达式成逻辑表达式成立时返回的值立时返回的值逻辑表达条件：逻辑表达条件：B2B2或或C2C2任一为任一为0 0是是否成立否成立= =IF(OR(B2=0,
22、C2=0),IF(OR(B2=0,C2=0), ,(C2-B2)/C2),(C2-B2)/C2)41EXCEL在会计日常工作中的应用第五章 公式与函数5.1.1单条件和多条件判断4.多条件判断IF函数实例（P113）=IF(B220000,B2*1%,IF(B225000,B2*2%,=IF(B220000,B2*1%,IF(B225000,B2*2%,IF(B235000,B2*3%,B2*4%)IF(B235000,B2*3%,B2*4%)=IF(B220000,B2*1%,IF(=IF(B220000,B2*1%,IF(20000=20000=B225000,B2*2%,IB225000
23、,B2*2%,IF( F(25000=25000=B235000,B2*3%,B2*4%)B235000,B2*3%,B2*4%)常见的常见的错误错误常见的常见的错误错误42EXCEL在会计日常工作中的应用第五章 公式与函数5.1.1单条件和多条件判断4.多条件判断IF函数实例（P113）=IF(B210000,B2*1%,0)+IF(AND(B2=10=IF(B210000,B2*1%,0)+IF(AND(B2=10000),B2*2%,0)+IF(AND(B2=20000),B2*3%,000),B2*2%,0)+IF(AND(B2=20000),B2*3%,0)+IF(AND(B2=30
24、000),B2*4%,0)+IF(AND(0)+IF(AND(B2=30000),B2*4%,0)+IF(AND(B2=40000),B2*5%,0)+IF(AND(B260000,BB2=40000),B2*5%,0)+IF(AND(B2=50000),B2*6%,0)+IF(AND(B2=60000),2=50000),B2*6%,0)+IF(AND(B2=60000),B2*7%,0)+IF(AND(B2=70000),B2*8%,0)+IF(B2*7%,0)+IF(AND(B2=70000),B2*8%,0)+IF(B280000,B2*9%,0)B280000,B2*9%,0)43E
25、XCEL在会计日常工作中的应用第五章 公式与函数课堂练习：成绩表小结：这节课讲了IF函数的使用，有以下四种情况：单条件返回文本单条件进行运算单条件返回区域多条件判断IF函数在实际工作中应用很广，要注意不同函数中参数的含义。44EXCEL在会计日常工作中的应用第五章 公式与函数5.2SUM函数函数SUM函数是工作中最常用函数之一，几乎所有的函数是工作中最常用函数之一，几乎所有的表格中都有合并的运算。表格中都有合并的运算。语法：语法：=SUM（参数（参数1，参数，参数2，参数参数30）参数最多为参数最多为3030个个参数可以为引用，数值，参数可以为引用，数值，文本，表达式和数组文本，表达式和数组例
26、例:=SUM(5,3,2,1):=SUM(5,3,2,1)例例:=(a1:b1):=(a1:b1)45EXCEL在会计日常工作中的应用第五章 公式与函数5.2.1连续、不连续及交叉区域求和连续、不连续及交叉区域求和例例1：连续区域的求和：连续区域的求和=SUM(A1:C5)例例1：不连续区域的求和：不连续区域的求和=SUM(A1，B3，D22)例例1：交叉区域的求和：交叉区域的求和=SUM(1:3C:C)注意：这注意：这儿有空格儿有空格46EXCEL在会计日常工作中的应用第五章 公式与函数5.2.2多工作表自动汇总多工作表自动汇总是是SUM函数的三维应用函数的三维应用例：例：=SUM(1日日:
27、空白空白!C5)单引号的作用是单引号的作用是去掉工作表名的去掉工作表名的空格空格工作表的名称必工作表的名称必须加感叹号！须加感叹号！47EXCEL在会计日常工作中的应用第五章 公式与函数5.1 SUMIF函数函数SUMIF函数是根据指定条件对若干单元格求和。函数是根据指定条件对若干单元格求和。语法：语法：=SUMIF（条件范围，条件，求和范围）（条件范围，条件，求和范围）需要求和的实际范需要求和的实际范围，省略则对条件围，省略则对条件范围求和范围求和只能用单条件而不只能用单条件而不能用复合条件，可能用复合条件，可以使用通配符以使用通配符用于条件判断的单用于条件判断的单元格区域元格区域例例:=S
28、UMIF(B2:B9,“:=SUMIF(B2:B9,“副教副教授授”,D2:D9)”,D2:D9)例例:=SUMIF(D2:D9,“2000):=SUMIF(D2:D9,“2000)48EXCEL在会计日常工作中的应用第五章 公式与函数5.1 SUMIF函数函数求和范围求和范围条件条件条件范围条件范围打开打开EXCELEXCEL49EXCEL在会计日常工作中的应用第五章 公式与函数5.3.1单条件求和单条件求和问题问题1:根据根据B列销售金额求和列销售金额求和,要求对销售金额大于要求对销售金额大于2000的数值求和的数值求和SUMIF函数实例函数实例（P121）省略求和范围省略求和范围, ,对
29、条件范围进行对条件范围进行求和求和条件条件条件范围条件范围=SUMIF=SUMIF（B2:B9,“2000B2:B9,“2000）50EXCEL在会计日常工作中的应用第五章 公式与函数5.3.1单条件求和单条件求和问题问题2:根据商品名称求和根据商品名称求和,要求对商品名称为要求对商品名称为A1的的销售金额求和销售金额求和SUMIF函数实例函数实例（P121）求和范围求和范围条件条件条件范围条件范围=SUMIF=SUMIF（A2:A9,“A1“,B2:B9A2:A9,“A1“,B2:B9）51EXCEL在会计日常工作中的应用第五章 公式与函数5.3.1单条件求和单条件求和问题问题3:根据根据B
30、列销售金额求和列销售金额求和,要求对销售金额大于要求对销售金额大于D2的数值求和的数值求和SUMIF函数实例函数实例（P121）省略求和范围省略求和范围, ,对条件范围进行对条件范围进行求和求和条件条件条件范围条件范围=SUMIF=SUMIF（B2:B9,“&D2B2:B9,“&D2）52EXCEL在会计日常工作中的应用第五章 公式与函数5.3.1单条件求和单条件求和问题问题4:对对B列中大于平均数的销售金额求和列中大于平均数的销售金额求和SUMIF函数实例函数实例（P121）省略求和范围省略求和范围, ,对条件范围进行对条件范围进行求和求和条件条件条件范围条件范围=SUMIF(B2:B9,“
31、&AVERAGE(B2:B9)=SUMIF(B2:B9,“&AVERAGE(B2:B9)53EXCEL在会计日常工作中的应用第五章 公式与函数5.3.1单条件求和单条件求和问题问题5:求商品名称包含求商品名称包含”A”的销售金额之和的销售金额之和SUMIF函数实例函数实例（P121）求和范围求和范围条件条件条件范围条件范围=SUMIF=SUMIF（A2:A9,“A*“,B2:B9A2:A9,“A*“,B2:B9）54EXCEL在会计日常工作中的应用第五章 公式与函数5.3.1单条件求和单条件求和问题问题2:根据商品名称求和根据商品名称求和,要求对商品名称为要求对商品名称为A1的的销售金额求和销
32、售金额求和SUMIF函数实例函数实例（P113）求和范围求和范围条件条件条件范围条件范围=SUMIF=SUMIF（A2:A9,“A1“,B2:B9A2:A9,“A1“,B2:B9）55EXCEL在会计日常工作中的应用第五章 公式与函数5.3.1单条件求和单条件求和问题问题6:根据商品名称求第四五个字符为根据商品名称求第四五个字符为”A2”,且字且字符总长度为符总长度为6个字符的销售金额求和个字符的销售金额求和SUMIF函数实例函数实例（P121）求和范围求和范围条件条件条件范围条件范围=SUMIF=SUMIF（A2:A9,“?A2?“,B2:B9A2:A9,“?A2?“,B2:B9）56EXC
33、EL在会计日常工作中的应用第五章 公式与函数5.3.2多条件及区间求和多条件及区间求和问题问题1:符合入库数量大于符合入库数量大于4小于小于10的商品的商品,对其入库对其入库数量求和数量求和SUMIF函数实例函数实例（P122）=SUMIF(C2:C9,“4“)-SUMIF(C2:C9,“=10“)=SUMIF(C2:C9,“4“)-SUMIF(C2:C9,“=10“)41057EXCEL在会计日常工作中的应用第五章 公式与函数5.3.2多条件及区间求和多条件及区间求和问题问题2:B列品名分别为列品名分别为”AA”,”BB”,”CC”的销售数量的销售数量之和之和SUMIF函数实例函数实例（P1
34、22）=SUM(SUMIF(B2:B9,”AA”,”BB”,”CC”,C2:C9)=SUM(SUMIF(B2:B9,”AA”,”BB”,”CC”,C2:C9)58EXCEL在会计日常工作中的应用第五章 公式与函数5.3.2多条件及区间求和多条件及区间求和问题问题3:对品名分别为对品名分别为”AA”的手机入库数量进行求的手机入库数量进行求和和SUMIF函数实例函数实例（P122）=SUMIF(A2:A9,”AA=SUMIF(A2:A9,”AA手机手机”,D2:D9),D2:D9)注意要先添加一辅助列注意要先添加一辅助列59EXCEL在会计日常工作中的应用第五章 公式与函数5.3.3不相邻区域的求
35、和不相邻区域的求和SUMIF函数实例函数实例（P123）=SUMIF(A3:D11,”1=SUMIF(A3:D11,”1”,B3:E11),B3:E11)注意两个区域的大小要一致注意两个区域的大小要一致60EXCEL在会计日常工作中的应用第五章 公式与函数5.4 COUNTIF函数函数COUNTIF函数是根据指计算给定区域内满足特定函数是根据指计算给定区域内满足特定条件单元格数目。条件单元格数目。语法：语法：=COUNTIF（条件范围，条件）（条件范围，条件）可以为数字可以为数字, ,表达式表达式或文本或文本用于条件判断的单用于条件判断的单元格区域元格区域例例:=COUNTIF(B2:B9,“
36、:=COUNTIF(B2:B9,“副副教授教授”) ”)例例:=COUNTIF(D2:D9,“200:=COUNTIF(D2:D9,“2000)0)61EXCEL在会计日常工作中的应用第五章 公式与函数5.4 COUNTIF函数函数条件条件条件范围条件范围打开打开EXCELEXCEL62EXCEL在会计日常工作中的应用第五章 公式与函数5.4.1按条件计数按条件计数问题问题1:统计实发工资大于统计实发工资大于2500的人数的人数COUNTIF函数实例函数实例（P124）条件条件条件范围条件范围=COUNTIF=COUNTIF（E2:E7,“2500E2:E7,“2500）63EXCEL在会计日
37、常工作中的应用第五章 公式与函数5.4.1按条件计数按条件计数问题问题2:统计财务部的人数统计财务部的人数COUNTIF函数实例函数实例（P124）条件条件条件范围条件范围=COUNTIF=COUNTIF（A2:A7,”A2:A7,”财务部财务部” ”）64EXCEL在会计日常工作中的应用第五章 公式与函数5.4.2COUNTIF计数常见的错误计数常见的错误1.区域选取的影响区域选取的影响COUNTIF函数实例函数实例（P124）=COUNTIF=COUNTIF（B3:B8,C3:C8,”6”B3:B8,C3:C8,”6”）=COUNTIF=COUNTIF（B3:C8,”6”B3:C8,”6”
38、）65EXCEL在会计日常工作中的应用第五章 公式与函数5.4.2COUNTIF计数常见的错误计数常见的错误2.数字格式的影响数字格式的影响COUNTIF函数实例函数实例（P124）解决办法解决办法: :把文本数字转换成数值把文本数字转换成数值型型66EXCEL在会计日常工作中的应用第五章 公式与函数5.4.2COUNTIF计数常见的错误计数常见的错误3.长数字的影响长数字的影响COUNTIF函数实例函数实例（P124）解决办法解决办法: :在长数字中添加在长数字中添加*号号67EXCEL在会计日常工作中的应用第五章 公式与函数5.5 SUMPRODUCT函数函数SUMPRODUCT函数是在给
39、定的几组数组中函数是在给定的几组数组中,将数将数组间对应的元素相乘组间对应的元素相乘,并返回乘积之和。并返回乘积之和。语法：语法：=SUMPRODUCT（数组（数组1，数组，数组2,数组数组3，）数组参数必须具有相同的维数，否则函数数组参数必须具有相同的维数，否则函数SUMPRODUCTSUMPRODUCT将返回错误值：将返回错误值：“#VALUE#VALUE！”例例:=SUMPRODUCT(1,2,3,4,5)=?:=SUMPRODUCT(1,2,3,4,5)=?=1*2*3*4*5=120=1*2*3*4*5=12068EXCEL在会计日常工作中的应用第五章 公式与函数5.5 SUMPRO
40、DUCT函数函数数组数组2 2数组数组1 1打开打开EXCELEXCEL数组数组3 369EXCEL在会计日常工作中的应用第五章 公式与函数5.5.1库存金额的简便运算库存金额的简便运算不用设置金额列不用设置金额列,直接计算出总入库金额直接计算出总入库金额SUMPRODUCT函数实例函数实例（P126）数组数组2 2数组数组1 1=SUMPRODUCT(B2:B9,C2:C9)=SUMPRODUCT(B2:B9,C2:C9)数组参数必须具有相同的维数数组参数必须具有相同的维数70EXCEL在会计日常工作中的应用第五章 公式与函数5.5.2多条件计数和求和多条件计数和求和1.多条件同时成立多条件
41、同时成立计数计数:SUMPRODUCT(条件条件1)*(条件条件2)*(条件条件3)*(条件条件n)求和求和:SUMPRODUCT(条件条件1)*(条件条件2)*(条件条件3)*(条件条件n)*(要统计的数据区域要统计的数据区域)2.任一条件成立任一条件成立计数计数:SUMPRODUCT(条件条件1)+(条件条件2)+(条件条件3)+(条件条件n)求和求和:SUMPRODUCT(条件条件1)+(条件条件2)*(条件条件3)+(条件条件n)*(要统计的数据区域要统计的数据区域)SUMPRODUCT函数实例函数实例（P127）71EXCEL在会计日常工作中的应用第五章 公式与函数多条件计数和求和多
42、条件计数和求和例例5-17在入库明细汇总表中在入库明细汇总表中,根据要求计算根据要求计算问题问题1:计算供应商计算供应商A1的冰箱入库类型的品种数的冰箱入库类型的品种数.SUMPRODUCT函数实例函数实例（P126）条件条件2: 2:类别为冰箱类别为冰箱条件条件1: 1:供应商的名字为供应商的名字为A1A1=SUMPRODUCT=SUMPRODUCT(B3:B11=“A1”)*(C3:C11=“(B3:B11=“A1”)*(C3:C11=“冰箱冰箱”) )72EXCEL在会计日常工作中的应用第五章 公式与函数多条件计数和求和多条件计数和求和例例5-17在入库明细汇总表中在入库明细汇总表中,根
43、据要求计算根据要求计算问题问题2:计算供应商计算供应商A3的洗衣机入库数量的洗衣机入库数量.SUMPRODUCT函数实例函数实例（P126）条件条件3: 3:类别为洗衣机类别为洗衣机条件条件1: 1:供应商供应商的名字为的名字为A3A3=SUMPRODUCT(B3:B11=“A3”),(C3:C11=“=SUMPRODUCT(B3:B11=“A3”),(C3:C11=“洗衣洗衣机机”)*E3:E11)*E3:E11)统计数据统计数据: :入库数量入库数量73EXCEL在会计日常工作中的应用第五章 公式与函数多条件计数和求和多条件计数和求和例例5-17在入库明细汇总表中在入库明细汇总表中,根据要
44、求计算根据要求计算举一反三举一反三:1.计算供应商计算供应商A1或或A2的冰箱入库数量。的冰箱入库数量。2.计算供应商计算供应商A1的冰箱或彩电的品种数。的冰箱或彩电的品种数。SUMPRODUCT函数实例函数实例（P126）74EXCEL在会计日常工作中的应用第五章 公式与函数5.6 VLOOKUP函数函数VLOOKUP函数是在表格或数值组的首列查找指函数是在表格或数值组的首列查找指定的数值定的数值,并由此返回表格或数组当前行中指定列并由此返回表格或数组当前行中指定列处的数值。它是最常用的函数之一处的数值。它是最常用的函数之一功能：功能：1.指定位置查找和引用数据指定位置查找和引用数据2.表与
45、表的核对表与表的核对3.利用模糊运算进行区间查询利用模糊运算进行区间查询75EXCEL在会计日常工作中的应用第五章 公式与函数5.6 VLOOKUP函数函数语法语法:=VLOOKUP(查找目标查找目标,查找区域查找区域,相对列数相对列数,TRUE或或FALSE)要找的内要找的内容在查找容在查找区域中的区域中的哪一列哪一列? ?在哪儿查找在哪儿查找? ?注意注意: :查找内容必须在查找内容必须在查找区域的第一查找区域的第一列列! !要查找的要查找的内容内容例例:=VLOOKUP(B2,$D$2:$H$9,3,0):=VLOOKUP(B2,$D$2:$H$9,3,0)TRUE:TRUE:模糊查找模
46、糊查找,FALSE:,FALSE:精确查找精确查找, ,可以用其可以用其1 1和和0 0代替代替76EXCEL在会计日常工作中的应用第五章 公式与函数5.6 VLOOKUP函数函数相对列数相对列数查找区域查找区域查找目标查找目标打开打开EXCELEXCEL精确查找或精确查找或模糊查找模糊查找77EXCEL在会计日常工作中的应用第五章 公式与函数5.6.1单个区域查找单个区域查找问题问题1:要求在要求在C列列,从员工信息表中根据姓名查找其从员工信息表中根据姓名查找其级别级别.VLOOKUP函数实例函数实例（P129）= =VLOOKUP(B2,$G$9:$H$14,2,0)VLOOKUP(B2,
47、$G$9:$H$14,2,0)要找的内要找的内容在查找容在查找区域中的区域中的第第2 2列列在哪儿查找在哪儿查找? ?员工信息表员工信息表, ,注意注意绝对引用的使用绝对引用的使用! !要查找的要查找的内容内容: :张三张三精确查找精确查找, ,可以用可以用0 0代替代替78EXCEL在会计日常工作中的应用第五章 公式与函数5.6.1单个区域查找单个区域查找问题问题2:要求在要求在D,E列列,分别根据工资级别和姓名分别根据工资级别和姓名,从基从基本工资表和提成表查找相应的数值本工资表和提成表查找相应的数值.VLOOKUP函数实例函数实例（P130）= =VLOOKUP(C2,$G$2:$H$7
48、,2,0)VLOOKUP(C2,$G$2:$H$7,2,0)要找的内要找的内容在查找容在查找区域中的区域中的第第2 2列列在哪儿查找在哪儿查找? ?基本工资表基本工资表, ,注意注意绝对引用的使用绝对引用的使用! !要查找的要查找的内容内容: :工资工资级别级别精确查找精确查找, ,可以用可以用0 0代替代替79EXCEL在会计日常工作中的应用第五章 公式与函数5.6.2多个区域查找多个区域查找利用以前所学的函数利用以前所学的函数VLOOKUP函数实例函数实例（P130）= =VLOOKUP(A2,IF(C2=VLOOKUP(A2,IF(C2=公司公司1,$F$3:$G$6,$F$10:$G$
49、13),2,0)1,$F$3:$G$6,$F$10:$G$13),2,0)要找的内要找的内容在查找容在查找区域中的区域中的第第2 2列列在哪儿查找在哪儿查找? ?现在有两个表现在有两个表, ,需要判断需要判断的时候就要想到的时候就要想到IF IF函数函数要查找的要查找的内容内容: :姓名姓名精确查找精确查找, ,可以用可以用0 0代替代替80EXCEL在会计日常工作中的应用第五章 公式与函数5.6.3模糊查找计算个人所得税模糊查找计算个人所得税以前学过以前学过IF函数条件判断后再求值函数条件判断后再求值,但嵌套太多但嵌套太多,容容易出错易出错,这里运用这里运用VLOOKUP函数函数来解决这个问
50、题来解决这个问题VLOOKUP函数实例函数实例（P131）=C2=C2* *要找的内要找的内容在查找容在查找区域中的区域中的第第3 3列列在哪在哪儿查儿查找找? ?要查找要查找的内容的内容: :应税所应税所得得模糊查找模糊查找, ,可以省略可以省略应税应税所得所得VLOOKUP(C2,$G$2:$I$10,3)-VLOOKUP(C2,$G$2:$J$10,4VLOOKUP(C2,$G$2:$I$10,3)-VLOOKUP(C2,$G$2:$J$10,4) )81EXCEL在会计日常工作中的应用第五章 公式与函数5.6.4处理查找出现的错误处理查找出现的错误在利用在利用VLOOKUP函数查找时函
51、数查找时,常遇到下列几种常遇到下列几种查询错误查询错误:参数设置错误参数设置错误空格及不可见字符引起的错误空格及不可见字符引起的错误格式不一致引起的错误格式不一致引起的错误82EXCEL在会计日常工作中的应用第五章 公式与函数1.参数设置错误参数设置错误VLOOKUP函数实例函数实例（P131）错误原因错误原因: :选取选取查询区域错误查询区域错误错误原因错误原因: :省省略参数是模略参数是模糊查找糊查找公式公式1:=VLOOKUP(B10,A1:E5,3,0)1:=VLOOKUP(B10,A1:E5,3,0)公式公式2:=VLOOKUP(B11,B2:C5,3,0)2:=VLOOKUP(B1
52、1,B2:C5,3,0)错误原因错误原因: :选选取查询区域错取查询区域错误误公式公式3:=3:=VLOOKUP(B12,B2:E5,3)VLOOKUP(B12,B2:E5,3)83EXCEL在会计日常工作中的应用第五章 公式与函数2.空格及不可见字符引起的错误空格及不可见字符引起的错误VLOOKUP函数实例函数实例（P132）解决方法解决方法: :替换替换不可见字符不可见字符解决方法解决方法: :转转换格式换格式(1)(1)空格引起的错误空格引起的错误解决方法解决方法: :替替换空格换空格(2)(2)不可见字符引起的错误不可见字符引起的错误3. 3.数字格式不一致引起的错误数字格式不一致引起
53、的错误84EXCEL在会计日常工作中的应用第五章 公式与函数5.7 INDIRECT函数函数INDIRECT是一个非常重要的函数是一个非常重要的函数,它可以把随意它可以把随意组合或者插入变量的字符串转换成可以使用的引组合或者插入变量的字符串转换成可以使用的引用。用。功能：功能：返回由文字串指定的引用，并对引用进行计算，返回由文字串指定的引用，并对引用进行计算，显示其内容。显示其内容。85EXCEL在会计日常工作中的应用第五章 公式与函数5.7 INDIRECT函数函数语法语法:=INDIRECT(文本字符串文本字符串,引用类型引用类型)TRUETRUE：A1A1类型类型FALSEFALSE：R
54、1C1R1C1类型类型省略为省略为A1A1类型类型对单元格的引用或字符串，此单对单元格的引用或字符串，此单元格可以包含元格可以包含A1A1样式的引用，样式的引用，定义为引用的名称或对文字串单定义为引用的名称或对文字串单元格的引用。元格的引用。例例:=INDIRECT(“R4C4”,0):=INDIRECT(“R4C4”,0)例例:=INDIRECT(“A1”):=INDIRECT(“A1”)86EXCEL在会计日常工作中的应用第五章 公式与函数5.6 INDIRECT函数函数引用类型引用类型文本字符串文本字符串打开打开EXCELEXCEL87EXCEL在会计日常工作中的应用第五章 公式与函数5
55、.7.1行列转置行列转置以前我们学过用选择性粘贴以前我们学过用选择性粘贴,现在我们来学习用公现在我们来学习用公式进行行列的转置式进行行列的转置.INDIRECT函数实例函数实例（P134）C1=INDIRECT(A&COLUMN(A1)C1=INDIRECT(A&COLUMN(A1)A A结合后面的数字结合后面的数字组合成一个新的引组合成一个新的引用用, ,注意相对引用注意相对引用的使用的使用! !利用相对绝对的原理利用相对绝对的原理, ,把列数把列数取出和前面的取出和前面的”A”A”组合成一个组合成一个新的引用新的引用, ,注意相对引用的使注意相对引用的使用用! !88EXCEL在会计日常工
56、作中的应用第五章 公式与函数5.7.2日报表的自动累计日报表的自动累计日报表是每天必做的工作日报表是每天必做的工作,累计工作则是日报表中重要的一项。累计工作则是日报表中重要的一项。如果是比较复杂的日报表，手工输入累计值或每张逐一设置如果是比较复杂的日报表，手工输入累计值或每张逐一设置公式，是一件很麻烦的事。这时就要用到公式，是一件很麻烦的事。这时就要用到INDIRECT函数函数INDIRECT函数实例函数实例（P135）日）日=INDIRECT(DAY(C2)-1&=INDIRECT(DAY(C2)-1&日日!D13)+D12!D13)+D12利用取日期中的天数，减利用取日期中的天数，减去去1
57、 1再加上再加上“日日!D13”!D13”就得就得到了上前一天报表的本月到了上前一天报表的本月累计的累计的引用引用前一天报表的前一天报表的D13D13是前一天是前一天的本月累计，再加上今天的的本月累计，再加上今天的本日累计就得出今天的本月本日累计就得出今天的本月累计累计89EXCEL在会计日常工作中的应用第五章 公式与函数5.7.3二级下拉列表设置二级下拉列表设置二级下拉列表是指在选取一级下拉列表内容后，在后面二级二级下拉列表是指在选取一级下拉列表内容后，在后面二级下拉列表中可以显示相对应的子列表。光用我们以前学到的下拉列表中可以显示相对应的子列表。光用我们以前学到的数据有效性已经不够了，这儿
58、要用到数据有效性已经不够了，这儿要用到INDIRECT函数函数INDIRECT函数实例函数实例（P136）=INDIRECT(=INDIRECT(A2)A2)这里的这里的A2A2的内容是的内容是”河南省河南省“，但用，但用了了INDIRECTINDIRECT函数后，返回是的函数后，返回是的“河南河南省省“所对应的所对应的名称名称代表的区域代表的区域举一反三举一反三举一反三举一反三: : : :能不能做能不能做能不能做能不能做三级下拉列表三级下拉列表三级下拉列表三级下拉列表? ? ? ?90EXCEL在会计日常工作中的应用第五章 公式与函数5.8 其他数学函数其他数学函数本节介绍的本节介绍的RO
59、UND和和MOD函数用法简单函数用法简单,但用途却但用途却极为广泛极为广泛.5.8.1用用ROUND函数处理工资表的计算误差函数处理工资表的计算误差语法语法:=ROUND(数字数字,指定的位数指定的位数)如果指定的位数如果指定的位数大于大于0, 0,则舍入到指定的小数位则舍入到指定的小数位; ;如果指定的位数如果指定的位数等于等于0, 0,则舍入到最接近的整数则舍入到最接近的整数; ;如果指定的位数如果指定的位数小于小于0, 0,则在小数舍入则在小数舍入; ;例例:=ROUND(25.265,2)=25.27:=ROUND(25.265,2)=25.27例例:=ROUND(25.265,0)=
60、25:=ROUND(25.265,0)=25例例:=ROUND(25.265,-1)=30:=ROUND(25.265,-1)=3091EXCEL在会计日常工作中的应用第五章 公式与函数5.8.1 ROUND函数函数指定的位数指定的位数数字数字打开打开EXCELEXCEL92EXCEL在会计日常工作中的应用第五章 公式与函数5.8.1用用ROUND函数处理工资表的计算误差函数处理工资表的计算误差ROUND函数实例函数实例（P134）= =ROUND(G4,2ROUND(G4,2) )通过设置小数点位数通过设置小数点位数, ,只是显示上保留只是显示上保留两位小数两位小数, ,实质上单元内部的小数
61、位数实质上单元内部的小数位数并没有改变并没有改变, ,而用而用ROUNDROUND函数是实质上函数是实质上把多余的位数舍掉了把多余的位数舍掉了, ,而不仅仅是在显而不仅仅是在显示上。示上。93EXCEL在会计日常工作中的应用第五章 公式与函数5.8.2用用MOD函数隔行填充颜色函数隔行填充颜色功能：返回两数相除的余数，结果的正负号与被除数功能：返回两数相除的余数，结果的正负号与被除数相同。相同。语法语法:=MOD(被除数被除数,除数除数)例例:=MOD(4,2)=0:=MOD(4,2)=0例例:=MOD(5,2)=1:=MOD(5,2)=1例例:=MOD(-10,4)=-2:=MOD(-10,
62、4)=-2例例:=MOD(-10,-4)=-2:=MOD(-10,-4)=-294EXCEL在会计日常工作中的应用第五章 公式与函数5.8.2 MOD函数函数除数除数被除数被除数打开打开EXCELEXCEL95EXCEL在会计日常工作中的应用第五章 公式与函数5.8.2用用MOD隔行填充颜色隔行填充颜色MOD函数实例函数实例（P138）= =MOD(ROW(),2MOD(ROW(),2) )=0=0返回当前行返回当前行的行数的行数96EXCEL在会计日常工作中的应用第五章 公式与函数5.9 其他统计函数其他统计函数统计函数是工作中常用的函数统计函数是工作中常用的函数,以前我们学过以前我们学过S
63、UM,COUNT等函数。本节将介绍其他几个统计函数。等函数。本节将介绍其他几个统计函数。5.9.1用用COUNTA函数自动统计工资表人数函数自动统计工资表人数功能功能:返回参数组中非空值的数目。返回参数组中非空值的数目。语法语法:=COUNTA(参数参数1,参数参数2,参数参数3参数参数N)N N最大值为最大值为3030；可以进行多工作表的三维引用；可以进行多工作表的三维引用注意和注意和COUNTCOUNT函数的对比！函数的对比！COUNTCOUNT函数只统计数值型数据函数只统计数值型数据例例:=COUNTA(A1:B67):=COUNTA(A1:B67)97EXCEL在会计日常工作中的应用第
64、五章 公式与函数5.9.1 COUNTA函数函数参数参数2 2参数参数1 1打开打开EXCELEXCEL98EXCEL在会计日常工作中的应用第五章 公式与函数5.9.1用用COUNTA函数自动统计工资表人数函数自动统计工资表人数COUNTA函数实例函数实例（P134）=COUNTA(B2:B5)=COUNTA(B2:B5)不便于插入行不便于插入行=COUNTA(INDIRECT(B2:B&ROW()-1)=COUNTA(INDIRECT(B2:B&ROW()-1)利用利用INDIRECTINDIRECT函数把字符串转换为引用，函数把字符串转换为引用，利用利用ROWROW函数把当前行数减去函数把
65、当前行数减去1 1，得出上一，得出上一行的行数。行的行数。99EXCEL在会计日常工作中的应用第五章 公式与函数5.9.2用用MAX函数设置变动序号函数设置变动序号MAX和和MIN函数是分别求最大值和最小值的函数，函数是分别求最大值和最小值的函数，它们常在复杂的数组公式中出现。它们常在复杂的数组公式中出现。功能：功能：MAX求一组数中的最大值；求一组数中的最大值；MIN求一组数中求一组数中的最小值的最小值语法：语法：=MAX(数值数值1,数值数值2,)=MIN(数值数值1,数值数值2,)MAX和和MIN函数实例函数实例（P141）100EXCEL在会计日常工作中的应用第五章 公式与函数MAX和
66、和MIN函数实例函数实例（P141）设置序号，要求：设置序号，要求：序号随行的删除可自动调整为新的连续序号序号随行的删除可自动调整为新的连续序号在小计行、合计行和空行前不加序号在小计行、合计行和空行前不加序号=IF(OR(B6=,B6=IF(OR(B6=,B6=小计小计,B6=,B6=合计合计),MAX($A$1:A5)+1),MAX($A$1:A5)+1)注意绝对引注意绝对引用和相对引用和相对引用的使用用的使用101EXCEL在会计日常工作中的应用第五章 公式与函数5.9.3用用LARGE和和SMALL实现销售数量自动排名实现销售数量自动排名功能：功能：LARGE求一组数中的第求一组数中的第
67、N个最大值；个最大值；SMALL求一组数中的第求一组数中的第N个最小值个最小值语法：语法：=LARGE(一组数值或单元格区域，第一组数值或单元格区域，第N个最大值个最大值)=SMALL(一组数值或单元格区域，第一组数值或单元格区域，第N个最小值个最小值)LARGE和和SMALL函数实例函数实例（P142）=LARGE(B2:B17,1)=LARGE(B2:B17,1)=SMALL(B2:B17,1)=SMALL(B2:B17,1)当有两个第二大值相等的时候，一个会当有两个第二大值相等的时候，一个会作为第二大，另一个作为第三大作为第二大，另一个作为第三大102EXCEL在会计日常工作中的应用第五
68、章 公式与函数5.10 其他查找引用函数其他查找引用函数查找引用函数在单元格查询，数据表之间的取数、核查找引用函数在单元格查询，数据表之间的取数、核对方面有着极其广泛的用途，前面我们学过对方面有着极其广泛的用途，前面我们学过VLOOKUP函数，以下介绍其他查找引用函数函数，以下介绍其他查找引用函数5.10.1ROW和和COLUMN生成公式变动函数生成公式变动函数语法语法:=ROW(引用的单元格或单元格区域引用的单元格或单元格区域) =COLUMN(引用的单元格或单元格区域引用的单元格或单元格区域)如果引用的单元格或单元格区域省略如果引用的单元格或单元格区域省略, ,返回的为当前行号返回的为当前
69、行号; ;如果引用的是一个单元格如果引用的是一个单元格, ,返回的是引用单元格所在的行号返回的是引用单元格所在的行号或列号或列号; ;如果引用的是一个单元格区域如果引用的是一个单元格区域, ,返回的是单元格区域左上角返回的是单元格区域左上角所在的行号或列号。所在的行号或列号。103EXCEL在会计日常工作中的应用第五章 公式与函数5.10 其他查找引用函数其他查找引用函数如果公式在如果公式在A10单元格单元格例例:=ROW()=:=ROW()=？例例:=ROW(C25)=:=ROW(C25)=？例例:=ROW(D2:E10)=:=ROW(D2:E10)=？例例:=COLUMN()=:=COLU
70、MN()=？例例:=COLUMN(C25)=:=COLUMN(C25)=？例例:=COLUMN(D2:E10)=:=COLUMN(D2:E10)=？101025252 21 13 34 4104EXCEL在会计日常工作中的应用第五章 公式与函数5.10 其他查找引用函数其他查找引用函数ROW和和COLUMN函数实例函数实例（P141）C14=VLOOKUP($B14,$B$2:$F$9,C14=VLOOKUP($B14,$B$2:$F$9,2 2,0),0)C15=VLOOKUP($B15,$B$2:$F$9,C15=VLOOKUP($B15,$B$2:$F$9,COLUMN(B1)COLUM
71、N(B1),0),0)这两个公式的区别在于相对列数的不同。第一个公式直接这两个公式的区别在于相对列数的不同。第一个公式直接用数字用数字2 2，后面的公式必须要手工来改相对列数，这样不便，后面的公式必须要手工来改相对列数，这样不便于公式的复制；第二个公式用的是于公式的复制；第二个公式用的是COLUMN(B1),COLUMN(B1),这样随着这样随着公式公式向右向右的拖动，的拖动，列列发生改变，函数返回的值也随之改变，发生改变，函数返回的值也随之改变，就可以实现公式的轻松复制。就可以实现公式的轻松复制。105EXCEL在会计日常工作中的应用第五章 公式与函数5.10 其他查找引用函数其他查找引用函
72、数ROW和和COLUMN函数实例函数实例（P141）I3=LARGE($F$2:$F$9,1)I3=LARGE($F$2:$F$9,1)J3=LARGE($F$2:$F$9,ROW(A1)J3=LARGE($F$2:$F$9,ROW(A1)这两个公式的区别在于第这两个公式的区别在于第N N大值的不同。第一个公式直接大值的不同。第一个公式直接用数字用数字1 1，后面的公式必须要手工来改第，后面的公式必须要手工来改第N N大值，这样不便大值，这样不便于公式的复制；第二个公式用的是于公式的复制；第二个公式用的是ROW(A1),ROW(A1),这样随着公式这样随着公式向向下下的拖动，的拖动，行行发生改
73、变，函数返回的值也随之改变，就发生改变，函数返回的值也随之改变，就可以实现公式的轻松复制。可以实现公式的轻松复制。106EXCEL在会计日常工作中的应用第五章 公式与函数5.10 其他查找引用函数其他查找引用函数5.10.2MATCH和和INDEX实现双向查找实现双向查找MATCH是查询函数是查询函数,INDEX是引用函数，在实际查找是引用函数，在实际查找并返回值过程中，并返回值过程中，MATCH和和INDEX总是结对出现在总是结对出现在公式中。公式中。功能：功能：MATCH返回在指定方式下与指定数值匹配的数组中返回在指定方式下与指定数值匹配的数组中元素的相应位置元素的相应位置INDEX返回表
74、格、区域中的数值或数值的引用。返回表格、区域中的数值或数值的引用。107EXCEL在会计日常工作中的应用第五章 公式与函数5.10 其他查找引用函数其他查找引用函数5.10.2MATCH和和INDEX实现双向查找实现双向查找语法：语法：=MATCH(查找的值查找的值,查找区域查找区域,查找类型查找类型)查找的类型为三种：查找的类型为三种：-1-1，0 0，1 1如果为如果为1, 1,查找小于或等于查找值的最大数值；查找小于或等于查找值的最大数值；如果为如果为0, 0,查找等于查找值的第一个数值；查找等于查找值的第一个数值；如果为如果为-1,-1,查找大于或等于查找值的最小数值；查找大于或等于查
75、找值的最小数值；如果省略，则默认为如果省略，则默认为1 1在哪儿查找在哪儿查找? ?要查找的内容要查找的内容108EXCEL在会计日常工作中的应用第五章 公式与函数5.10 其他查找引用函数其他查找引用函数5.10.2MATCH和和INDEX实现双向查找实现双向查找语法：语法：=INDEX(区域区域,行数行数,列数列数)行数和列数：是指相对于该区域的行行数和列数：是指相对于该区域的行数和列数，而并非相对整个工作表的数和列数，而并非相对整个工作表的行数和列数。行数和列数。为单元格为单元格 区域区域或数组常数或数组常数MATCHMATCH和和INDEXINDEX函数实例函数实例（P144P144）
76、=INDEX($A$1:$H$6,MATCH(A11,$A$1:$A$6,0),M=INDEX($A$1:$H$6,MATCH(A11,$A$1:$A$6,0),MATCH(B11,$A$1:$H$1,0)ATCH(B11,$A$1:$H$1,0)109EXCEL在会计日常工作中的应用第五章 公式与函数行数行数为单元格为单元格 区域区域或数组常数或数组常数=INDEX($A$1:$H$6,MATCH(A11,$A$1:$A$6,0),M=INDEX($A$1:$H$6,MATCH(A11,$A$1:$A$6,0),MATCH(B11,$A$1:$H$1,0)ATCH(B11,$A$1:$H$1
77、,0)列数列数查找的类型为查找的类型为0, 0,查找等于查找值的第一个数值，第查找等于查找值的第一个数值，第一个结果是一个结果是2 2，表示行数为，表示行数为2 2；第二个结果为第二个结果为4 4，表示列数为，表示列数为4 4在哪儿查找在哪儿查找? ?要查找的内容：要查找的内容：部门、部门、3 3月费用计月费用计划划110EXCEL在会计日常工作中的应用第五章 公式与函数5.10 其他查找引用函数其他查找引用函数5.10.3OFFSET实现单元格区域移动实现单元格区域移动OFFSET函数总能返回一个变动的区域或变动的单元函数总能返回一个变动的区域或变动的单元格格,这就使公式中的单元格或单元格区
78、域这就使公式中的单元格或单元格区域,可以随着给可以随着给定条件值的变化而变化定条件值的变化而变化,从而达到灵活设置公式的目从而达到灵活设置公式的目的。的。功能功能:以引用的左上单元格为基准以引用的左上单元格为基准,按指定的行偏移、按指定的行偏移、列偏列、行数、列数返回一个新的引用。列偏列、行数、列数返回一个新的引用。111EXCEL在会计日常工作中的应用例例:=OFFSET(A1,1):=OFFSET(A1,1)？例例:=SUM(OFFSET(B2,2,2):=SUM(OFFSET(B2,2,2)？例例:=SUM(OFFSET(A1,1,3,3,3):=SUM(OFFSET(A1,1,3,3,
79、3)？第五章 公式与函数5.10 其他查找引用函数其他查找引用函数5.10.3OFFSET实现单元格区域移动实现单元格区域移动(实例实例P145)语法：语法：=OFFSET(引用引用,行偏移行偏移,列偏移列偏移,行数行数,列数列数)行数和列数：返行数和列数：返回区域的行数和回区域的行数和列数列数, ,如与引用如与引用的行数或列数相的行数或列数相同同, ,可省略可省略返回区域的左上单元格相对于引用的左返回区域的左上单元格相对于引用的左上单元格的行上单元格的行( (列列) )偏移数偏移数, ,向上向上( (左左) )为负值为负值向下向下( (右右) )为正值为正值, ,同一行同一行( (列列) )
80、为为0 0A2A2的值的值B2:C3B2:C3的和的和D2:F4D2:F4的和的和112EXCEL在会计日常工作中的应用第五章 公式与函数5.10 其他查找引用函数其他查找引用函数5.10.4用用ADDRESS和和HYPERLINK实现入库单快速查实现入库单快速查找找1.ADDRESS函数函数功能功能:按照给定的行号和列标按照给定的行号和列标,建立文本类型的单元格建立文本类型的单元格地址。地址。113EXCEL在会计日常工作中的应用例例:=ADDRESS(1,1,1):=ADDRESS(1,1,1)？例例:=ADDRESS(1,1,2):=ADDRESS(1,1,2)？例例:=ADDRESS(
81、1,1,3):=ADDRESS(1,1,3)？例例:=ADDRESS(1,1,4):=ADDRESS(1,1,4)？第五章 公式与函数5.10 其他查找引用函数其他查找引用函数5.10.3用用ADDRESS和和HYPERLINK实现入库单快速查找实现入库单快速查找语法：语法：=ADDRESS(行数行数,列数列数,引用类型引用类型,引用工作簿及工作表引用工作簿及工作表名称名称)1. 1.绝对引用绝对引用( (可省略可省略)2.)2.绝对行号绝对行号, ,相对列标相对列标3. 3.相对行号相对行号, ,绝对列标绝对列标 4. 4.相对引用相对引用“$A$1”“$A$1”“A$1”“A$1”“$A1
82、”“$A1”“A1”“A1”114EXCEL在会计日常工作中的应用第五章 公式与函数5.10 其他查找引用函数其他查找引用函数5.10.4用用ADDRESS和和HYPERLINK实现入库单快速查实现入库单快速查找找2.HYPERLINK函数函数功能功能:创建一个快捷方式创建一个快捷方式(跳转跳转),用以打开保存在网络用以打开保存在网络服务器、服务器、Intranet或或Internet上的文件。上的文件。115EXCEL在会计日常工作中的应用第五章 公式与函数5.10 其他查找引用函数其他查找引用函数5.10.3用用ADDRESS和和HYPERLINK实现入库单快速查找实现入库单快速查找语法语
83、法:=HYPERLINK(链接路径或文件名，链接处显示的文字链接路径或文件名，链接处显示的文字)实例实例 P146116EXCEL在会计日常工作中的应用第五章 公式与函数5.11 文本函数文本函数5.11.1字符串的查找和截取字符串的查找和截取功能功能:LEFT:取左边的取左边的N个字符个字符RIGHT:取右边的取右边的N个字符个字符MID:根据指定位置取指定位数字符根据指定位置取指定位数字符LEN:字符串的字符数字符串的字符数FIND:在指定字符串中查找指定字符的位置在指定字符串中查找指定字符的位置SEARCH:查找特定字符或文本串的位置查找特定字符或文本串的位置117EXCEL在会计日常工
84、作中的应用第五章 公式与函数5.11 文本函数文本函数语法语法:实例见实例见:P148LEFT(字符串或引用单元格字符串或引用单元格,N)RIGHT(字符串或引用单元格字符串或引用单元格,N)MID(字符串或引用单元格字符串或引用单元格,开始截取位数开始截取位数,长度长度)LEN(字符串或引用单元格字符串或引用单元格)FIND(查找的字符查找的字符,字符串或引用单元格字符串或引用单元格,开始查找位数开始查找位数)SEARCH(查找的字符查找的字符,字符串或引用单元格字符串或引用单元格,开始查找位数开始查找位数)说明说明:FIND区分大小写并且不允许使用通配符区分大小写并且不允许使用通配符,而而
85、SEARCH可以可以 例例:=LEFT(“ABCDEFG”,2):=LEFT(“ABCDEFG”,2)？例例:=RIGHT(“ABCDEFG”,2):=RIGHT(“ABCDEFG”,2)？例例:=MID(“ABCDEFG”,2,3):=MID(“ABCDEFG”,2,3)？例例:=LEN(“ABCDEFG”,):=LEN(“ABCDEFG”,)？例例:=FIND(“F”,“ABCDEFG”,1):=FIND(“F”,“ABCDEFG”,1)？例例:=SEARCH(“?f”,“ABCDEFG”,1):=SEARCH(“?f”,“ABCDEFG”,1)？ABABFGFGBCDBCD7 76 65
86、 5118EXCEL在会计日常工作中的应用第五章 公式与函数5.11 文本函数文本函数5.11.2SUBSTITUTE和和REPLACE实现指定内容替换实现指定内容替换功能功能:SUBSTITUTE:在某下文本中替换指定的文本在某下文本中替换指定的文本.REPLACE:在某一文本中替换指定位置处的任意文本在某一文本中替换指定位置处的任意文本119EXCEL在会计日常工作中的应用例例:=SUBSTITUTE(A2,”:=SUBSTITUTE(A2,”国税局国税局”,” ,”国家税务局国家税务局”) )例例:=REPLACE(A2,4,3,”:=REPLACE(A2,4,3,”国家税务局国家税务局
87、”) ”)第五章 公式与函数5.11 文本函数文本函数5.11.2SUBSTITUTE和和REPLACE实现指定内容替换实现指定内容替换语法：语法：=SUBSTITUTE(字符串或引用单元格字符串或引用单元格,要替换的文本要替换的文本,要要替换旧文本的新文本替换旧文本的新文本,替换掉第替换掉第N个旧的文本个旧的文本)语法：语法：=REPLACE(字符串或引用单元格字符串或引用单元格,要替换起始位置要替换起始位置,要要替换原文本的字符个数替换原文本的字符个数,新的文本新的文本)公式中的公式中的N N如果省略如果省略, ,默认替换全部的旧文本字符默认替换全部的旧文本字符120EXCEL在会计日常工
88、作中的应用第五章 公式与函数5.12 时间函数时间函数5.12.1用用TODAY和和NOW计算和更新库龄计算和更新库龄本小节介绍的是能分别显示当前日期函数本小节介绍的是能分别显示当前日期函数TODAY和和当前时间函数当前时间函数NOW,它们的特点可以随着日期和时它们的特点可以随着日期和时间的变化而变化间的变化而变化,但这种更新是在工作簿打开或工作但这种更新是在工作簿打开或工作表重新计算的前提下表重新计算的前提下.在下面几中常见情况下在下面几中常见情况下,工作表工作表会重新计算会重新计算.(1)双击工作表中的某个单元格双击工作表中的某个单元格(2)编辑任一个单元格内容编辑任一个单元格内容(3)按
89、按F9键键121EXCEL在会计日常工作中的应用第五章 公式与函数5.12 时间函数时间函数5.12.1用用TODAY和和NOW计算和更新库龄计算和更新库龄P150功能功能:TODAY:显示当前日期显示当前日期NOW:显示当前时间显示当前时间语法语法:TODAY()NOW()二者均没有参数二者均没有参数, ,但括号不能省略但括号不能省略122EXCEL在会计日常工作中的应用第五章 公式与函数5.12 时间函数时间函数5.12.1日期格式的拆分与组合日期格式的拆分与组合在对日期进行处理时在对日期进行处理时,常根据实际需要对日期进行拆常根据实际需要对日期进行拆分和组合分和组合功能功能:YEAR:返
90、回具体日期的年份返回具体日期的年份MONTH:返回具体日期的月份返回具体日期的月份DAY:返回具体日期的天数返回具体日期的天数DATE:根据已知年、月、日数值，返回具体日期根据已知年、月、日数值，返回具体日期WEEKDAY：转换日期为星期中的一天，一般用于判：转换日期为星期中的一天，一般用于判断是一周的第几天断是一周的第几天123EXCEL在会计日常工作中的应用第五章 公式与函数5.12.1日期格式的拆分与组合日期格式的拆分与组合语法语法:实例见实例见:P150YEAR(日期日期)MONTH(日期日期)DAY(日期日期)DATE(年、月、日年、月、日)WEEKDAY(日期，返回值的类型日期，返
91、回值的类型)例例:=YEAR(1989-07-08):=YEAR(1989-07-08)？例例:=MONTH(1989-07-08):=MONTH(1989-07-08)？例例:=DAY(1989-07-08):=DAY(1989-07-08)？例例:=DATE(1989:=DATE(1989，0707，0808）？）？例例:=WEEKDAY(19890708,1):=WEEKDAY(19890708,1)？19891989070708081989-07-081989-07-08124EXCEL在会计日常工作中的应用第五章 公式与函数5.12.1日期格式的拆分与组合日期格式的拆分与组合语法语法
92、:实例见实例见:P150WEEKDAY(日期，返回值的类型日期，返回值的类型)125EXCEL在会计日常工作中的应用第五章 公式与函数5.12 时间函数时间函数5.12.2用用DATEDIF计算日期间隔计算日期间隔DATEIF是是EXCEL的一个隐藏函数，且在的一个隐藏函数，且在EXCEL2002以前版本中，帮助文件没有它的介绍，但事实上它以前版本中，帮助文件没有它的介绍，但事实上它是一个十分有用的函数。是一个十分有用的函数。功能：可以计算两日期之间的天数、月数、年数功能：可以计算两日期之间的天数、月数、年数语法：语法：DATEDIF(开始日期开始日期,结束日期结束日期,单位代码单位代码)开始
93、日期要比结开始日期要比结束日期要早束日期要早, ,否则否则返回错误值。返回错误值。“Y”:“Y”:返回整年数返回整年数;“M”:;“M”:返回整月数返回整月数“D”:“D”:返回整天数返回整天数“MD”:“MD”:返回天数差返回天数差, ,忽略日期的年和月忽略日期的年和月“YM”:“YM”:返回月份差返回月份差, ,忽略日期的年和天数忽略日期的年和天数“YD”:“YD”:返回天数差返回天数差, ,忽略日期的年忽略日期的年126EXCEL在会计日常工作中的应用第五章 公式与函数5.12.3设置设置A1的值的值2010-12-22，B1的值为的值为2011-12-31例例:=DATEDIF(A1,
94、B1,“Y”)=:=DATEDIF(A1,B1,“Y”)=？例例:=DATEDIF(A1,B1,“M”)=:=DATEDIF(A1,B1,“M”)=？例例:=DATEDIF(A1,B1,“D”)=:=DATEDIF(A1,B1,“D”)=？例例:=DATEDIF(A1,B1,“MD”)=:=DATEDIF(A1,B1,“MD”)=？例例:=DATEDIF(A1,B1,“YM”)=:=DATEDIF(A1,B1,“YM”)=？例例:=DATEDIF(A1,B1,“YD”)=:=DATEDIF(A1,B1,“YD”)=？1 11212127EXCEL在会计日常工作中的应用第五章 公式与函数5.13
95、 IS类信息函数类信息函数IS类信息函数是一个具有相似特征函数的集合类信息函数是一个具有相似特征函数的集合,它们根据判断它们根据判断返回返回TRUE或或FALSE,常见的常见的IS函数有以下几个函数有以下几个:ISBLANK:判断是否为空格判断是否为空格,是则返回是则返回TRUE,否则返回否则返回FalseISERR:判断值是否任意错误值判断值是否任意错误值(除去除去#N/A)ISERROR:判断值是否任意错误值判断值是否任意错误值(#N/A、#VALUE！、！、#REF！、！、#DIV/0！、！、#NUM！、！、#NAME？)ISLOGICAL：判断值是否为逻辑值：判断值是否为逻辑值ISNA
96、：判断值是否为错误值：判断值是否为错误值#N/A （值不存在）（值不存在）ISNOTEXT：判断值是否为任意不是文本的项：判断值是否为任意不是文本的项ISNUMBER：判断值是否为数字：判断值是否为数字ISREF：判断值是否为引用：判断值是否为引用ISTEXT：判断值是否文本：判断值是否文本128EXCEL在会计日常工作中的应用第五章 公式与函数语法：语法：IS函数名函数名(数值或公式数值或公式)实例见实例见P152IS IS函数只能返回函数只能返回TRUETRUE或或FALSEFALSE，不能返回实际数，不能返回实际数值值129EXCEL在会计日常工作中的应用第五章 公式与函数5.14 综合
97、实例综合实例本节主要介绍函数在会计工作中的综合应用实例，并以同样本节主要介绍函数在会计工作中的综合应用实例，并以同样的问题提供了多个解决思路和方法。的问题提供了多个解决思路和方法。5.14.1多条件模糊求和多条件模糊求和在第在第5.5节介绍节介绍SUMPRODUCT函数时，详细地对多条件计数函数时，详细地对多条件计数以及求和做了说明，但是以及求和做了说明，但是SUMPRODUCT函数中不能直接使函数中不能直接使用通配符，所以无法独立完成多条件模糊计数以及求和。用通配符，所以无法独立完成多条件模糊计数以及求和。例：例：5-39见见P153要求根据销售明细表统计出张三销售打印机的数量。要求根据销售
98、明细表统计出张三销售打印机的数量。130EXCEL在会计日常工作中的应用第五章 公式与函数5.14 综合实例综合实例5.14.2工龄工资计算工龄工资计算例：例：5-40见见P1545.14.3工作日统计工作日统计工作日统计一般是除去周六或周日剩下的工作日天数。工作日统计一般是除去周六或周日剩下的工作日天数。例：例：5-41见见P1545.14.4金额填充金额填充在打印发票或其他票据金额时，为了能精确打印，常需要把金额拆分。在打印发票或其他票据金额时，为了能精确打印，常需要把金额拆分。例：例：5-42见见P1565.14.5费用多栏账转化费用多栏账转化从财务软件中导出的费用明细账多栏账，通常要占用三四页的宽度，这给从财务软件中导出的费用明细账多栏账，通常要占用三四页的宽度，这给打印和阅读带来了不便，如何把多栏账转化为更为直观的账表。打印和阅读带来了不便，如何把多栏账转化为更为直观的账表。例：例：5-43见见P157131EXCEL在会计日常工作中的应用小 结本节介绍了在财务工作中常用到的函数，其应用实例也是根据在财务中常遇到的问题而设置的。函数功能非常强大，实际工作中不要仅局限于函数的基本用法，而是要结合函数的特点去灵活运用。132EXCEL在会计日常工作中的应用
