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.1单元格数值条件 3.2.2公式条件,第三章 条件格式,3.2定义条件 单元格数值：用于简单的数值对比 公式 ：用于设置较为复杂的单元格内容 3.2.1单元格数值条件 3.2.2公式条件,第三章 条件格式,3.3条件格式实例应用 3.3.1工龄分析的颜色提示 3.2.2应收账款催款提醒 3.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！）,第三章 条件格式,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 双坐标图表,单坐标图表,常规设置的双坐标图表,双坐标图表,第五章 公式与函数,公式与函数是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(53,“不对“,“对“),第五章 公式与函数,5.1 IF函数,逻辑表达式不成立返回的值,逻辑表达式成立返回的值,返回值为TRUE或FALSE的 逻辑表达式,打开EXCEL,第五章 公式与函数,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）,逻辑表达式不成立返回的区域,逻辑表达式成立时返回的区域,逻辑表达条件： 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(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“),4,10,第五章 公式与函数,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,第五章 公式与函数,5.5.1库存金额的简便运算 不用设置金额列,直接计算出总入库金额 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,第五章 公式与函数,5.8.1 ROUND函数,指定的位数,数字,打开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函数,除数,被除数,打开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 COUNTA函数,参数2,参数1,打开EXCEL,第五章 公式与函数,5.9.1用COUNTA函数自动统计工资表人数 COUNTA函数实例,=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函数实例,=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返回在指定方式下与指定数值匹配的数组中元素的相应位置 INDEX返回表格、区域中的数值或数值的引用。,第五章 公式与函数,5.10 其他查找引用函数 5.10.2 MATCH和INDEX实现双向查找 语法：=MATCH(查找的值,查找区域,查找类型),查找的类型为三种：-1，0，1 如果为1,查找小于或等于查找值的最大数值； 如果为0,查找等于查找值的第一个数值； 如果为-1,查找大于或等于查找值的最小数值； 如果省略，则默认为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),MATCH(B11,$A$1:$H$1,0),列数,查找的类型为0,查找等于查找值的第一个数值，第一个结果是2，表示行数为2； 第二个结果为4，表示列数为4,在哪儿查找?,要查找的内容： 部门、3月费用计划,第五章 公式与函数,5.10 文本函数 5.10.1 字符串的查找和截取 功能: LEFT:取左边的N个字符 RIGHT:取右边的N个字符 MID:根据指定位置取指定位数字符 LEN:字符串的字符数 FIND:在指定字符串中查找指定字符的位置 SEARCH:查找特定字符或文