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

EXCEL在日常工作中的应用第一章 EXCEL文件管理新建文件工作表的隐藏文件的安全与保护快速打印指定表格第二章 单元格编辑录入相同的内容下拉列表录入录入内容的限制限制数字格式或大小文本长度单多条件限定限制重复输入录入区域的限制第二章 单元格编辑选取使用定位选取选择性粘贴粘贴数值粘贴运算隐藏查找第三章 条件格式3.1条件格式的设立、添加设立条件格式添加条件3.2定义条件单元格数值条件公式条件3.3条件格式实例应用3.4小结第三章 条件格式3.1条件格式的设立、添加设立条件格式添加条件3.2定义条件单元格数值条件公式条件3.3条件格式实例应用3.4小结第三章 条件格式3.1条件格式的设立、添加条件格式的设立、添加设立条件格式设立条件格式操作步骤：选中区域操作步骤：选中区域格式格式 条件格式条件格式 输入条件输入条件选择格式选择格式添加条件添加条件在在条件设置条件设置对话框中，单击添加按钮对话框中，单击添加按钮注：条件格式最多可以设置三个注：条件格式最多可以设置三个第三章 条件格式3.2定义条件定义条件单元格数值单元格数值：用于简单的数值对比：用于简单的数值对比公式公式：用于设置较为复杂的单元格内容：用于设置较为复杂的单元格内容单元格数值单元格数值条件条件公式公式条件条件第三章 条件格式3.2定义条件定义条件单元格数值单元格数值：用于简单的数值对比：用于简单的数值对比公式公式：用于设置较为复杂的单元格内容：用于设置较为复杂的单元格内容单元格数值单元格数值条件条件公式公式条件条件第三章 条件格式3.3条件格式实例应用条件格式实例应用工工龄分析的颜色提示龄分析的颜色提示应收账款催款提醒应收账款催款提醒3.2.3合同到期提醒合同到期提醒监视重复录入监视重复录入格式化账簿格式化账簿代码录入的错误显示代码录入的错误显示动态显示销售额排行动态显示销售额排行隐藏公式中的错误值隐藏公式中的错误值第三章 条件格式3.3条件格式实例应用条件格式实例应用代码录入的错误显示代码录入的错误显示条件条件:1.代码位数不等于五位代码位数不等于五位 2.代码位数不等于八位代码位数不等于八位公式公式:=AND(LEN($B2)5,LEN($B2)8,$B20)第三章 条件格式3.3条件格式实例应用条件格式实例应用动态显示销售额排行动态显示销售额排行条件条件:突出显示前突出显示前N名商品的销售额名商品的销售额公式公式:=$D2=LARGE($D$2:$D$10,5)最大值函数最大值函数,MAX求出一个最大值求出一个最大值,LARGE可以求可以求第第N个最大值个最大值.第三章 条件格式3.3条件格式实例应用条件格式实例应用隐藏公式中错误值隐藏公式中错误值条件条件:把所有错误值隐藏把所有错误值隐藏公式公式:=ISERROR(D2)判断值是否为任意错误值（判断值是否为任意错误值（#N/A，VALUE！）第三章 条件格式3.4小结小结本章对条件的创建、条件的设置作了详细介绍，同时也本章对条件的创建、条件的设置作了详细介绍，同时也列举了大量应用实例。读者从实例中不难看出，如果想列举了大量应用实例。读者从实例中不难看出，如果想用好条件格式，掌握公式及函数的使用是非常重要的。用好条件格式，掌握公式及函数的使用是非常重要的。习题：习题：1、如何设置公式条件、如何设置公式条件2、如何突出显示重复录入内容？、如何突出显示重复录入内容？3、如何突出显示一列数据中最大前三个数字？、如何突出显示一列数据中最大前三个数字？4、如何添加和删除条件格式？、如何添加和删除条件格式？第四章 数据表和图表4.1排序排序数据表排序数据表排序隔行插入空行隔行插入空行4.2分列分列拆分整列为多列拆分整列为多列长文本型数字的导入长文本型数字的导入转化字符为日期格式转化字符为日期格式4.3自动筛选自动筛选自动筛选的实现自动筛选的实现一次删除所有重复记录一次删除所有重复记录第四章第四章 数据表和图表数据表和图表4.3自动筛选自动筛选自动筛选的实现自动筛选的实现一次删除所有重复记录一次删除所有重复记录添加一辅助列添加一辅助列,输入公式输入公式:=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高级筛选高级筛选高级筛选功能灵活性强高级筛选功能灵活性强,和自动筛选相比有如下特和自动筛选相比有如下特点点:可以把筛选结果复制到其他位置可以把筛选结果复制到其他位置;需要设置条件区域需要设置条件区域,而且可以使用更多条件而且可以使用更多条件;可筛选不重复记录可筛选不重复记录;筛选符合条件的记录筛选符合条件的记录1.输入条件区域输入条件区域规则规则:(1)标题行和源区域一样标题行和源区域一样 (2)同行不同列的条件是并列关系同行不同列的条件是并列关系;(3)同列不同行的条件是或者关系同列不同行的条件是或者关系第四章 数据表和图表2.设置筛选项目设置筛选项目.复制标题行到要显示筛选结果的第一行复制标题行到要显示筛选结果的第一行.复制和手工输入有什么区别复制和手工输入有什么区别?3.数据数据筛选筛选高级筛选高级筛选数据源区域设置条件的区域第四章 数据表和图表筛选本列不重复记录筛选本列不重复记录筛选两区域重复记录筛选两区域重复记录筛选两表中不重复记录筛选两表中不重复记录=COUNTIF($D$16:$D$24,D3)=0第四章 数据表和图表4.5数据透视表数据透视表数据透视表是一种对数据清单快速建立汇总的动数据透视表是一种对数据清单快速建立汇总的动态总结报告态总结报告,它可以随时调换行列的位置而进行不它可以随时调换行列的位置而进行不同形式的汇总同形式的汇总,是是Excel提供的一个极为有效的汇提供的一个极为有效的汇总工具。数据透视表在销售数据汇总、出入库汇总工具。数据透视表在销售数据汇总、出入库汇总及明细账汇总等方面有着广泛应用总及明细账汇总等方面有着广泛应用一个普通的数据表日期日期地区地区姓名姓名产品名称产品名称销量销量销售单价销售单价销售金额销售金额2004-1-12004-1-1中南区中南区赵志赵志B产产品品100100240024002400002400002004-1-102004-1-10西北区西北区杨东林杨东林B B产品产品25252400240060000600002004-1-152004-1-15华南区华南区杨磊杨磊D D产品产品45451200120054000540002004-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中南区中南区赵志赵志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产品产品323241004100131200131200你你的工作表含有大量的工作表含有大量数据，但是数据，但是你你知道这知道这些数字的含义吗？这些数字的含义吗？这些数据能够解答您的些数据能够解答您的问题吗？问题吗？不普通的数据透视表数据透视表提供了一种快速且强大的方数据透视表提供了一种快速且强大的方式来分析数值数据、以不同的方式查看相式来分析数值数据、以不同的方式查看相同的数据以及回答有关这些数据的问题。同的数据以及回答有关这些数据的问题。第四章 数据表和图表4.5数据透视表数据透视表创建数据透视表创建数据透视表三步曲之一：确定报表类型三步曲之一：确定报表类型第四章 数据表和图表4.5数据透视表数据透视表创建数据透视表创建数据透视表三步曲之二：确定数据源三步曲之二：确定数据源第四章 数据表和图表4.5数据透视表数据透视表创建数据透视表创建数据透视表三步曲之三：布局三步曲之三：布局第四章 数据表和图表4.5数据透视表数据透视表创建数据透视表创建数据透视表三步曲之三：确定显示位置三步曲之三：确定显示位置第四章 数据表和图表固定数据透视表格式固定数据透视表格式创建数据透视表创建数据透视表调整数据透视表格式调整数据透视表格式在数据透视表中设置公式在数据透视表中设置公式其他编辑其他编辑调整汇总方式调整汇总方式显示或隐藏汇总行显示或隐藏汇总行数据透视表的更新和自动更新数据透视表的更新和自动更新第四章 数据表和图表多个数据透视表合并多个数据透视表合并第四章 数据表和图表多个数据透视表合并多个数据透视表合并第四章 数据表和图表4.6 图表图表创建图表创建图表插入插入图表图表四步曲四步曲设置图表类型设置图表类型设置数据源设置数据源设置图表选项设置图表选项设置图表位置设置图表位置第四章 数据表和图表-四步曲四步曲第四章 数据表和图表4.6.2 双坐标图表双坐标图表单坐标图表单坐标图表常规设置的常规设置的双坐标图表双坐标图表双坐标图表双坐标图表第五章 公式与函数公式与函数是Excel的精华所在,它为分析和处理数据提供了方便,特别是在处理大批量数据和进行复杂数据分析等方面更能发挥它的强大功能。本章在介绍函数的用法时，着重介绍它们在财务工作中的应用。本章要点：IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函数的用法IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函数的实例应用第五章 公式与函数公式与函数是Excel的精华所在,它为分析和处理数据提供了方便,特别是在处理大批量数据和进行复杂数据分析等方面更能发挥它的强大功能。本章在介绍函数的用法时，着重介绍它们在财务工作中的应用。本章要点：IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函数的用法IF、SUM、COUNTIF、SUMPRODUCT和VLOOKUP等常用函数的实例应用第五章 公式与函数5.1 IF函数IF函数是工作中最常用函数之一，它可以根据设置的条件进行运算或返回值。语法：=IF（逻辑表达式，TRUE，FALSE）逻辑表达式不成逻辑表达式不成立返回的值立返回的值逻辑表达式成逻辑表达式成立返回的值立返回的值返回值为返回值为TRUE或或FALSE的逻辑表的逻辑表达式达式例例:=IF(53,对对,不对不对)例例:=IF(5B2,节约节约，超支超支）第五章 公式与函数5.1.1 单条件和多条件判断2.单条件判断并运算运算IF函数实例（P113）逻辑表达式不成逻辑表达式不成立进行运算的表立进行运算的表达式达式逻辑表达式成逻辑表达式成立时进行运算立时进行运算的表达式的表达式逻辑表达条件：逻辑表达条件：销售额是否超过销售额是否超过3万元万元C2=IF（B230000,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函数实例逻辑表达式不成逻辑表达式不成立返回的表达式立返回的表达式逻辑表达式成逻辑表达式成立时返回的值立时返回的值逻辑表达条件：逻辑表达条件：B2或或C2任一为任一为0是是否成立否成立=IF(OR(B2=0,C2=0),(C2-B2)/C2)第五章 公式与函数5.1.1 单条件和多条件判断4.多条件判断IF函数实例=IF(B220000,B2*1%,IF(B225000,B2*2%,IF(B235000,B2*3%,B2*4%)=IF(B220000,B2*1%,IF(20000=B225000,B2*2%,IF(25000=B235000,B2*3%,B2*4%)常见的常见的错误错误常见的常见的错误错误第五章 公式与函数5.1.1 单条件和多条件判断4.多条件判断IF函数实例（P113）=IF(B210000,B2*1%,0)+IF(AND(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)+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)例例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)例例:=SUMIF(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函数实例函数实例求和范围求和范围条件条件条件范围条件范围=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个字符的销售金额求和个字符的销售金额求和SUMIF函数实例函数实例（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函数实例函数实例=SUM(SUMIF(B2:B9,”AA”,”BB”,”CC”,C2:C9)第五章 公式与函数5.3.2 多条件及区间求和多条件及区间求和问题问题3:对品名分别为对品名分别为”AA”的手机入库数量进行求的手机入库数量进行求和和SUMIF函数实例函数实例=SUMIF(A2:A9,”AA手机手机”,D2:D9)注意要先添加一辅助列注意要先添加一辅助列第五章 公式与函数5.3.3 不相邻区域的求和不相邻区域的求和SUMIF函数实例函数实例=SUMIF(A3:D11,”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）第五章 公式与函数5.4.1 按条件计数按条件计数问题问题2:统计财务部的人数统计财务部的人数COUNTIF函数实例函数实例条件条件条件范围条件范围=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.长数字的影响长数字的影响COUNTIF函数实例函数实例解决办法解决办法:在长数字中添加在长数字中添加*号号第五章 公式与函数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第五章 公式与函数库存金额的简便运算库存金额的简便运算不用设置金额列不用设置金额列,直接计算出总入库金额直接计算出总入库金额SUMPRODUCT函数实例函数实例数组数组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函数实例函数实例第五章 公式与函数多条件计数和求和多条件计数和求和例例5-17 在入库明细汇总表中在入库明细汇总表中,根据要求计算根据要求计算问题问题1:计算供应商计算供应商A1的冰箱入库类型的品种数的冰箱入库类型的品种数.SUMPRODUCT函数实例函数实例条件条件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函数实例函数实例第五章 公式与函数5.6 VLOOKUP函数函数VLOOKUP函数是在表格或数值组的首列查找指函数是在表格或数值组的首列查找指定的数值定的数值,并由此返回表格或数组当前行中指定列并由此返回表格或数组当前行中指定列处的数值。它是最常用的函数之一处的数值。它是最常用的函数之一功能：功能：1.指定位置查找和引用数据指定位置查找和引用数据2.表与表的核对表与表的核对3.利用模糊运算进行区间查询利用模糊运算进行区间查询第五章 公式与函数5.6 VLOOKUP函数函数语法语法:=VLOOKUP(查找目标查找目标,查找区域查找区域,相对列数相对列数,TRUE或或FALSE)要找的内要找的内容在查找容在查找区域中的区域中的哪一列哪一列?在哪儿查找在哪儿查找?注注意意:查找内容必须查找内容必须在查找区域的第在查找区域的第一列一列!要查找的要查找的内容内容例例:=VLOOKUP(B2,$D$2:$H$9,3,0)TRUE:模糊查模糊查找找,FALSE:精确精确查找查找,可以用其可以用其1和和0代替代替第五章 公式与函数5.6 VLOOKUP函数函数相对列数相对列数查找区域查找区域查找目标查找目标打开打开EXCEL精确查找或精确查找或模糊查找模糊查找第五章 公式与函数5.6.1 单个区域查找单个区域查找问题问题1:要求在要求在C列列,从员工信息表中根据姓名查找从员工信息表中根据姓名查找其级别其级别.VLOOKUP函数实例函数实例=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.7 INDIRECT函数函数INDIRECT 是一个非常重要的函数是一个非常重要的函数,它可以把随它可以把随意组合或者插入变量的字符串转换成可以使用的意组合或者插入变量的字符串转换成可以使用的引用。引用。功能：功能：返回由文字串指定的引用，并对引用进行计算，返回由文字串指定的引用，并对引用进行计算，显示其内容。显示其内容。第五章 公式与函数5.7 INDIRECT函数函数语法语法:=INDIRECT(文本字符串文本字符串,引用类型引用类型)TRUE：A1类型类型FALSE：R1C1类型类型省略为省略为A1类型类型对单元格的引用或字符串，此单对单元格的引用或字符串，此单元格可以包含元格可以包含A1样式的引用，样式的引用，定义为引用的名称或对文字串单定义为引用的名称或对文字串单元格的引用。元格的引用。例例:=INDIRECT(“R4C4”,0)例例:=INDIRECT(“A1”)第五章 公式与函数5.6 INDIRECT函数函数引用类型引用类型文本字符串文本字符串打开打开EXCEL第五章 公式与函数5.7.1 行列转置行列转置以前我们学过用选择性粘贴以前我们学过用选择性粘贴,现在我们来学习用公现在我们来学习用公式进行行列的转置式进行行列的转置.INDIRECT函数实例函数实例C1=INDIRECT(A&COLUMN(A1)A结合后面的数字结合后面的数字组合成一个新的引组合成一个新的引用用,注意相对引用注意相对引用的使用的使用!利用相对绝对的原理利用相对绝对的原理,把列数把列数取出和前面的取出和前面的”A”组合成一组合成一个新的引用个新的引用,注意相对引用的注意相对引用的使用使用!第五章 公式与函数5.7.2 日报表的自动累计日报表的自动累计日报表是每天必做的工作日报表是每天必做的工作,累计工作则是日报表中重要的一项。累计工作则是日报表中重要的一项。如果是比较复杂的日报表，手工输入累计值或每张逐一设置如果是比较复杂的日报表，手工输入累计值或每张逐一设置公式，是一件很麻烦的事。这时就要用到公式，是一件很麻烦的事。这时就要用到INDIRECT函数函数INDIRECT函数实例函数实例=INDIRECT(DAY(C2)-1&日日!D13)+D12利用取日期中的天数，减利用取日期中的天数，减去去1再加上再加上“日日!D13”就就得到了上前一天报表的本得到了上前一天报表的本月累计的月累计的引用引用前一天报表的前一天报表的D13是前一天是前一天的本月累计，再加上今天的的本月累计，再加上今天的本日累计就得出今天的本月本日累计就得出今天的本月累计累计第五章 公式与函数5.7.3 二级下拉列表设置二级下拉列表设置二级下拉列表是指在选取一级下拉列表内容后，在后面二级二级下拉列表是指在选取一级下拉列表内容后，在后面二级下拉列表中可以显示相对应的子列表。光用我们以前学到的下拉列表中可以显示相对应的子列表。光用我们以前学到的数据有效性已经不够了，这儿要用到数据有效性已经不够了，这儿要用到INDIRECT函数函数INDIRECT函数实例函数实例=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例例:=ROUND(25.265,-1)=30第五章 公式与函数 ROUND函数函数指定的位数指定的位数数字数字打开打开EXCEL第五章 公式与函数用用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第五章 公式与函数 MOD函数函数除数除数被除数被除数打开打开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)第五章 公式与函数 COUNTA函数函数参数参数2参数参数1打开打开EXCEL第五章 公式与函数用用COUNTA函数自动统计工资