办公应用培训(EXCEL)课件.ppt

1、办公应用培训办公应用培训(EXCEL)2EXCEL-大纲EXCEL能做什么，为什么要学。EXCEL基础概念数据操作公式与函数数据分析图表制作3EXCEL-能做什么数据共享、程序开发数据共享、程序开发简单动画、协同办公简单动画、协同办公提高效率、潜力无限提高效率、潜力无限图表演示、统计图表演示、统计(做报告，做演示做报告，做演示)辅助计算、分析辅助计算、分析(算产量，造工资算产量，造工资)制作表格、表单制作表格、表单(做对账单做对账单)4数据操作图表与图形公式与函数数据分析宏与VBA录入与导出标准图形公式基础排序录制运行宏常规编辑组合图表函数应用筛选VBA基础格式化图表美化函数嵌套列表EXCEL

2、对象高级标记高级图表数组公式数据透视表窗体与控件自定义环境交互式图表自定义函数假设分析类模板打印高级分析EXCEL-内容构成5EXCEL图表-带时间轴的成因分布（看比例）6EXCEL图表-带时间轴的成因分布（看数量）7EXCEL图表-退货的成因和趋势分析8EXCEL图表-退货成因分布饼图9EXCEL图表-销售欠款折线图10EXCEL图表-销售回款欠款指数散点图11EXCEL图表-典型条形图应用12EXCEL图表-高级条形图13EXCEL图表-高级双轴曲线图14EXCEL图表-双色柱形图15EXCEL图表-瀑布图16EXCEL图表-绘图区分割17EXCEL图表-甘特图18EXCEL图表-各种常用

3、图形的应用饼 图：成分相对关系。条形图：项目相对关系。柱形图：时间序列相对关系，强调数量的级别。折线图：时间序列相对关系，强调角度的运动和图像的变换（趋势）。散点图：频率相对关系。雷达图：艾尔尼尔森审核、ABB审核其它图：如甘特图、鱼骨图、控制图、环形图等等。19EXCEL-日常表单的改进回款表的改进。发货记录表的改进。对账任务表的改进。记账测算表。生产任务统计表的改进。产品规格数据表的改进。图纸自动生成器。考勤表的改进？生产排程与调度？20EXCEL-老司机与菜鸟21EXCEL-表格外观设计的不良习惯看过上面的表格，大家作何感想呢？标题：居中？不居中？表头：使用多行标题、斜线标题字体：标题使

4、用了花哨的华文彩云字体、单位名称使用了轻佻的华文行楷、数据区域字体使用的楷体，而且数据区域字体不统一字号：不统一，数据区域存在多种字号边框：所有单元格都加边框，边框格式不统一、残缺不全，无关的单元格也设置了边框22EXCEL-表格外观设计的不良习惯底色：单元格底色五颜六色对齐：对齐格式不统一列宽：部分列的列宽不够，数字显示不完整行高：高度不够，未适当留白；数字填满单元格整行整列设格式处处想强调，到处加底色，批注未隐藏网格线未隐藏23EXCEL-何为漂亮表格布局合理、结构清晰、简洁干净、美观大方漂亮表格几大要素：字体、字号、颜色（字体颜色、填充颜色）、边框、下划线、间距、对齐及缩进。24EXCE

5、L-何为漂亮表格如何做到布局合理注意数据的逻辑性：相关或同类的数据应排列在一起重要的字段或记录应靠前考虑数据记录有无排序要求注意版式的选择垂直？水平？当横向一屏显示不全时，则考虑纵向排列。以尽量不横向翻屏为原则。另特别注意：标题的位置（居中否）、合计行列位置25EXCEL-何为漂亮表格如何做到结构清晰归类：将同一类别的记录归组在一起间距：根据实际需要拉取合适行距、列距边框：不是所有表格都必须用边框，应根据不同层级选择边框 要点：1、边框是用来结构化表格，体现层级的；2、尽量使用开放式表格；3、用边框线条的粗细来区分层级，明细级数据时可不用边框。26EXCEL-何为漂亮表格字体数据区域与标题使用

6、不同字体汉字建议使用宋体和微软雅黑字体数字建议使用Arial、Arial Narrow或Times New Roman字体以下是各字体的比较27EXCEL-字体效果展示28EXCEL-何为漂亮表格字号标题建议使用22-26号字体表头建议使用12-16号字体数据正文建议使用10-11号字体批注使用系统默认字体即可，另批注建议隐藏底色底色不能太暗也不能太亮颜色与字体颜色相协调底色种类不易过多29EXCEL-何为漂亮表格行距、列距根据实际表格需要调整行距、列距调整原则：以一个单元格能显示完当前数据为优先参考，另需兼顾表格打印预览效果。如遇数据实在太长，可尝试自动换行或自动缩小字体。30EXCEL-何

7、为漂亮表格如何做到表格美观和谐就是美字体协调颜色协调版面干净尽量少用批注删除表格之外单元格的内容和格式备注置于表尾31EXCEL-表格美化应考虑的问题考虑表格用途考虑是否打印考虑标题是否写明重点考虑报表使用者的偏好形成自己的风格32EXCEL-表格美化之普通型33EXCEL-表格美化之清爽型34EXCEL-表格美化之简约商务型35EXCEL-表格美化之典雅商务型36EXCEL-窗口工作表标签工作表标签列标列标活动单元格活动单元格行号行号工具栏工具栏标题栏标题栏任务窗格任务窗格菜单栏菜单栏绘图工具栏绘图工具栏名称框名称框公式编辑框公式编辑框37EXCEL-工作簿与工作表工作薄：是一个Excel文

8、件，扩展名为.xls，由工作表组成。最多可存放255个工作表。默认工作簿名为Book1，其中包含三个工作表Sheet1、Sheet2和Sheet3。工作表：是以列和行的形式组织和存放数据的表格，由单元格组成。每一个工作表都用一个工作表标签来标识。工作簿与工作表的关系可以理解为一本书与书中各页的关系。38EXCEL-工作表的操作1.插入工作表2.选择和选取工作表3.重命名工作表4.移动/复制工作表5.删除工作表6.隐藏工作表7.保护工作表 8.拆分工作表9.冻结工作表39EXCEL-行和列行和列：一个工作表一共有256列及65536行。列标：用字母序列表示，从A到IV。行号：用阿拉伯数字表示，从

9、1到65536。40EXCEL-单元格单元格：行和列的相交点，是工作表的最小单位，可以是字符串、数据、公式等不同类型的内容，最多可容纳32000个字符。单元格的位置：单元格根据其所处的列标和行号来命名。列标在前、行号在后。比如：A5，C9。活动单元格：每个工作表中只有一个活动单元格，活动单元格被粗边框包围着。填充柄：活动单元格的右下角有一个小黑色方块，称为填充柄。41EXCEL-自动填充示例初始选择初始选择扩展序列扩展序列1，2，34，5，69：0010：00，11：00，12：00MonTue，Wed，Thu星期一星期二，星期三，星期四JanFeb，Mar，Apr一月，四月七月，十月，一月1

10、月1日，3月1日5月1日，7月1日，9月1日1999，20002001，2002，2003Text1，textAText2，textA，Text3，textA产品1产品2，产品342EXCEL-单元格格式单元格格式：除对齐、字体、边框等类似于WORD的格式外，“数字”格式需特别注意。43EXCEL-单元格格式之数值型数值型可以用于算术计算的数据。包括常说的“数字”、日期型、时间性和逻辑值，类型为1，默认为右对齐.限制：Excel只能精确15位有效数字，超过部分以0表示，如果是小数则直接去掉数值过大或者过小，Excel会用科学计数法表示（字母E）单元格可以输入的最值：a 最大的数值：9.9999

11、9999999999E+307b 最大的正数：1.79769313486231E+308c 最小的正数：2.229E-308d 最大的负数：-2.250738585073E-308e 最小的负数：-1.79769313486231E+30844EXCEL-单元格格式之日期和时间型日期和时间型日期和时间以一种特殊的数值形式存储，实际上，被叫做序列值。该值大于等于0，小于等于2958466。所以，实际上，日期和时间的函数类型为1。限制：不能输入小于系统默认值的日期：1111年11月11日，这样将不会被识别为日期。允许的最大的时间为9999:59:59.9999说明：Excel对于时间的计算基本单位

12、为天。将1小时算成1/24天。输入时间的方法建议为：2016/10/13 14:15:1645EXCEL-单元格格式之文本型文本型：常说的 文本和文本型的数值。函数类型为2。默认为左对齐。限制：不能参与计算，但是可以比较大小。说明：快递单号、身份证号码、税号等应该设置为文本型。46EXCEL-单元格格式之文本型货币格式：数值前添加货币符号。会计格式：对齐小数点和货币符号。自定义：多用于特殊的日期时间等，mm“月”dd“日”和m“-”d。47EXCEL-单元格输入注意事项输入字符型数字在数字前加单引号(身份证号、电话号码加零)输入日期，要按“年/月/日（年-月-日）”的格式输入时间，要按“时:分

13、 am（pm）”格式【时间和字母之间要空格】，当然也有其他输入格式：时:分:秒 时:分 时:分:秒 am（pm）Ctrl+；输入当前日期Ctrl+shift+；输入当前时间数据宽度：数值超过11位，自动以科学计数法输入。例如：输入“123456789012”系统显示“1.23457E+11”输入数值时可以用逗号“，”分节 如：“7,357,542”输入纯小数值。以“.”开头。数值前自动补“0”负数前面输入负号或（数值），正号省略数字以（%）结尾，则按百分数处理输入分数时前面加0和空格。如“0 1/4”48EXCEL-公式和函数公式是对工作表中的数值执行计算的等式，可以用于返回信息、操作单元格内

14、容以及测试条件等。公式始终以等号（=）开头，后面跟一个表达式；表达式是常量、引用、函数和运算符的组合，其计算结果为单个值。1.常量2.引用3.函数4.运算符5.公式示例 =54+B4+SUM(A2：A5)&”件”49EXCEL-公式和函数之运算符常用运算符包括算术运算符、比较运算符、文本连接运算符和引用运算符。常用算术运算符有：+（加号）（减号）（减号）*（星号）/（正斜杠）%（百分号）（脱字号）。比较运算符有：=（等号）（大于号）=（大于等于号）=（小于等于号）（不等号）。文本连接运算符有：&。引用运算符有：:（冒号）,（逗号）（空格）。运算符优先级1 :、(空格)和,引用运算符：区域引用、

15、交叉引用、联合引用2 算术运算符：负号3%算术运算符：百分比4 算术运算符：乘方5 *和/算术运算符：乘和除6 +和 算术运算符：加和减7&文本运算符：文本连接8 =、=、比较运算符：比较两个值50EXCEL-公式和函数之单元格引用相对引用：公式中的相对单元格引用（如A1），是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也将随之改变。如果多行或多列地复制或填充公式，引用会自动调整。默认情况下，新公式使用相对引用。绝对引用：公式中的绝对单元格引用（如$A$1）总是在特定位置引用单元格。如果公式所在单元格的位置改变，绝对引用将保持不变。如果多行或多列地复制或填充公

16、式，绝对引用将不作调整。默认情况下，新公式使用相对引用，可能需要将它们转换为绝对引用。例如，如果将某个源单元格中的绝对引用复制或填充到目标单元格中，则这两个单元格中的绝对引用相同。混合引用：混合引用具有绝对列和相对行或绝对行和相对列。绝对引用列采用$A1、$B1等形式。绝对引用行采用A$1、B$1等形式。如果公式所在单元格的位置改变，则相对引用将改变，而绝对引用则保持不变。如果多行或多列地复制或填充公式，相对引用将自动调整，而绝对引用则不作调整。若要在相对引用、绝对引用和混合引用之间快速切换，可在单元格或编辑栏中选择公式包含的单元格引用，然后按F4键。例如，按F4可在B2、$B$2、B$2和$

17、B2之间快速切换。51EXCEL-公式和函数之单元格引用三维引用：三维引用包含单元格或区域引用，前面加上感叹号“!”和工作表名称的范围。三维引用的语法格式如下：工作表名称!单元格地址外部引用：外部引用也称为链接，是对另一个Excel工作簿中的工作表的单元格或单元格区域的引用，也可以是对另一个工作簿中的已定义名称的引用。通过创建外部引用，可以引用另一个工作簿中的单元格的内容。既可以引用特定的单元格区域或为单元格区域定义的名称，也可以为外部引用定义名称。名称是代表单元格、单元格区域、公式或常量值的单词或字符串。包含对其他工作簿的外部引用的公式具有两种显示方式，具体取决于源工作簿（为公式提供数据的工

18、作簿）是打开还是关闭的。当源工作簿打开时，外部引用包含用方括号括起的工作簿的名称，然后是工作表名称和感叹号（!），接着是公式要计算的单元格。例如:=SUM(Budget.xlsxAnnual!C10:C25)当源工作簿未打开时，外部引用包括完整路径。例如：=SUM(C:ReportsBudget.xlsxAnnual!C10:C25)52EXCEL-公式错误值与解决方法错 误 值错 误 原 因#单元格所含的数字、日期或时间比单元格宽，或者单元格的日期、时间公式产生了一个负值，就会产生#错误。#VALUE!1.在需要数字或逻辑值时输入了文本，Microsoft Excel 不能将文本转换为正确的

19、数据类型。#DIV/O!1.输入的公式中包含明显的除数为零（0），如=5/0。2.#NAME?1.在公式中输入文本时没有使用双引号。Microsoft 将其解释为名称，但这些名称没有定义。2.函数名的拼写错误。#N/A 1.内部函数或自定义工作表函数中缺少一个或多个参数。2.在数组公式中，所用参数的行数或列数与包含数组公式的区域的行数或列数不一致。3.在没有排序的数据表中使用了VLOOKUP，HLOOKUP或MATCH工作表函数查找数值#REF!删除了公式中所引用的单元或单元格区域#NUM!1.由公式产生的数字太大或太小，2.在需要数字参数的函数中使用了非数字参数#NULL!在公式的两个区域中

20、加入了空格从而求交叉区域，但实际上这两个区域无重叠区域。53EXCEL-数组公式数组公式的建立方法1.选中需要保存数组公式结果的单元格或单元格区域。2.输入公式的内容。3.按Ctrl+Shift+Enter键。4.在公式中用数组作为参数时，所有的数组必须是同维的。如果数组参数或数组区域的维数不匹配，Excel会自动扩展该参数。54EXCEL-数组应用某商场秋季进行换季服装大降价，所有服装都在打折，打折的比例随时都在调整，计算打折后的新价格。如图所示55EXCEL-名称名称的意义在Excel中，同一个工作簿中的名称是共享的，在一个工作表中定义的名称，可以被另一个工作表的公式引用，这使公式具有更强

21、大的功能，它能够利用单元格的名称在不同的工作表中查找到正确的单元格或单元格区域。名称给单元格的引用带来了许多方便，因为它使人们不必记住单元格在工作表中的引用位置，用名称就能找到它。56EXCEL-名称应用通过名称查找蔬菜单价通过名称查找蔬菜单价指定指定A2:B12最左边最左边为名称为名称57EXCEL-公式和函数之函数的分类加载宏和自动化函数：用于加载宏、自定义函数等。多维数据集函数：用于联机分析处理（OLAP）数据库的函数。数据库函数：按照特定条件对现有数据进行分析的函数。日期和时间函数：分析并处理日期和时间。工程函数：用于工程分析的函数。财务函数：进行财务运算的函数。信息函数：用于确定存储

22、在单元格中的数据类型的函数。逻辑函数：用于判断真假值或复合检验的函数。查找和引用函数：在现有数据中查找特定数值和单元格的引用函数。数学和三角函数：对现有数据进行数字取整、求和、求平均值以及复杂的函数运算。统计函数：对当前数据区域进行统计分析。文本函数：对字符串进行提取、转换的函数。58EXCEL-错误信息函数函数名函数功能ISBLANK（X）判定X是否为空白单元格。ISERR（X）判定X是否为任意错误值（除去#N/A）。ISERROR（X）判定X是否为任意错误值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!）。ISLOGICAL（X）判定X是否为

23、逻辑值。ISNA（X）判定X是否为错误值#N/A（值不存在）。ISNONTEXT（X）判定X是否不是文本的任意项（注意此函数在值为空白单元格时返回 TRUE）。ISNUMBER（X）判定X是否为数字。ISREF（X）判定X是否为引用。ISTEXT（X）判定X是否为文本。59EXCEL-数学函数之取整函数INT(Number)用途：将任意实数向下取整为最接近的整数。语法：INT(实数)参数：Number 为需要处理的任意一个实数。实例：如果A1=16.24、A2=-28.389，则公式“=INT(A1)”返回16，=INT(A2)返回-29。生活应用：有实发工资金额，还有100元、50元、10元

24、、1元。在发现金工资时我要用到分钱。比方说某人工资5573元，那么100元我们要发多少张？50元多少张？10元多少张？5元多少张？1元多少张呢？60EXCEL-数学函数之取整函数ROUND(Number,Num_digits)用途：按指定位数四舍五入某个数字。语法：ROUND(需四舍五入的数字，保留的小数位)注意：如果保留的小数位大于0，则四舍五入到指定的小数位；如果等于0，则四舍五入到最接近的整数；如果小于0，则在小数点左侧按指定位数四舍五入。实例：如果A1=65.25，则公式“=ROUND(A1，1)”返回65.3；=ROUND(82.149，2)返回82.15；=ROUND(21.5，-

25、1)返回20；Round(A1,0)返回65。61EXCEL-数学函数之取整函数ROUNDUP(Number,Num_digits)用途：按指定位数向上舍入某个数字。语法：ROUNDUP(需向上舍入的数字，保留的小数位)。其它用法如ROUND()函数。典型应用于计算箱数，人数，天数等。ROUNDDOWN(Number,Num_digits)用途：按指定位数向下舍入某个数字。62EXCEL-数学函数之求和函数SUM(Number1,Number2,.)SUMIF(Range,Criteria,Sum_range)对range单元格区域中的数据进行Criteria条件检查，然后对满足条件行的sum

26、_range同行进行求和。SUMIFS(sum_range,range1,criteria1,range2,criteria2)其中range1,range2,是计算关联条件的 1 至 127 个区域；Criteria1,criteria2,是数字、表达式、单元格引用或文本形式的 1 至 127 个条件，用于定义要对哪些单元格求和。这些区域与条件是对应的，即Criteria1是用于range1区域的条件，criteria2是用于range2的条件，以此类推。Sum_range是求和区域。63EXCEL-SUMIF演示H7:H19=E,E,D,E,E,A,C,B,C,A,D,A,BM7=A，在函

27、数中，自动转换成数组形式，即，在函数中，自动转换成数组形式，即A,A,A,A,A,A,A,A,A,A,A,A,AE,E,D,E,E,A,C,B,C,A,D,A,BA,A,A,A,A,A,A,A,A,A,A,A,A0,0,0,0,0,1,0,0,0,1,0,1,01）按次序逐对进行比较。）按次序逐对进行比较。2）相符（）相符（TRUE）为）为1，不相符（，不相符（FALSE）为）为0。|64EXCEL-SUMIF演示0,0,0,0,0,1,0,0,0,1,0,1,0第步比较运算得出的结果：第步比较运算得出的结果：结果11=1 01=065EXCEL-以数组的角度理解函数应用SUMIF（Range

28、，Criteria，Sum_Range）Range：判断区域，一维数组Criteria：条件，可以是固定值Sum_Range：求和区域，一维数组运算步骤及注意点：1）Range与Criteria进行逻辑运算，得出结果2）结果与Sum_Range进行相乘运算3）Range与Sum_Range的范围大小必须一致66EXCEL-SUMIF与SUMIFS应用演示（1）在J3单元格中输入公式：=SUMIF($D$3:$D$10，I3，$E$3:$E$10)(2)将此公式向下复制到将此公式向下复制到J4、J5、J6（1）在K3单元格输入公式：=SUMIF($D$3:$D$10，I3，$G$3:$G$10)

29、(2)将此公式向下复制到将此公式向下复制到K4、K5、K6在J10单元格中输入统计李本成销售的彩电总数量的计算公式：=SUMIFS($E$3:$E$13,$D$3:$D$13,$I10,$A$3:$A$13,J$9)(2)将此公式向下角复制到将此公式向下角复制到L13，67EXCEL-SUM函数中引用问题如果参数为数组或引用，那么只有其中的数字被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。例如，设A1的值为“9”，A2为true，则公式SUM(A1,A2,20)的计算结果为20，而不是30。因为本公式中包括两个引用A1、A2，而A1的值为文本，A2的值为逻辑值，它们在计算时被

30、忽略，最终就只有一个数值20参与运算。68EXCEL-函数之多条件乘积求和函数SUMPRODUCT（array1,array2,array3,.）Array1,array2,array3,.为 2 到 30 个数组，其相应元素需要进行相乘并求和。本身支持数据间运算。条件、求和区域，都是一维数组，必须是单行/列，而不能是多行/列。非数值型的数组元素按0处理。各数组维度必须相同，否则返回错误。区域计算要求：计算A、B、C三列对应数据乘积的和。公式：=SUMPRODUCT(A2:A6,B2:B6,C2:C6)计算方式：=A2*B2*C2+A3*B3*C3+A4*B4*C4+A5*B5*C5+A6*B

31、6*C6即三个区域A2:A6,B2:B6,C2:C6同行数据积的和。用于多条件求和。对于计算符合某一个条件的数据求和，可以用SUMIF函数来解决。如果要计算符合2个以上条件的数据求和，用SUMIF函数就不能够完成了。这就可以用函数SUMPRODUCT。Sumproduct(A2:A8=E2)+(B2:8=F2)*(C2:C8)/Sumproduct(A2:A8=E2)*(B2:8=F2)*(C2:C8)=条件间的相隔符（星号）可以理解为“并且”，既N个条件都符合的情况下，对求和区域进行累加。按此推理，也可以求“或者”的情况，函数中用“”表示“或者”，”或者”的逻辑运算次序要低于“并且”，请留意

32、此点！69EXCEL-函数之多条件乘积求和函数条件1条件2求和区域条件一返回结果：条件一返回结果：0，0，1，0，1，0，1条件二返回结果：条件二返回结果：1，1，0，0，0，0，1相乘相乘|70EXCEL-函数之多条件乘积求和函数条件1条件2求和区域条件1、条件2、求和区域相乘后的结果71EXCEL-函数之多条件乘积求和函数Sumproduct（条件1）（条件2）条件1结果：0,1,0,1,1,0,1,1,0,1条件2结果：0,1,0,0,1,1,1,0,1,0相乘后结果：0,1,0,0,1,0,1,0,0,0并且：同时为1时才是1，其它都是0或者：同时为0时才是0，其它都是1少了“求和区域

33、”72EXCEL-函数之多条件乘积求和函数73EXCEL-函数之分类汇总求和函数SUBTOTAL(function_num,ref1,ref2,.)Function_num 为 1 到 11（包含隐藏值）或 101 到 111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。ref1refn参数为要对其进行分类汇总计算的第1至29个命名区域或引用。必须是对单元格区域的引用。忽略隐藏值包含隐藏值相当于函数1011AVERAGE 平均值1022COUNT 非空值单元格计数1033COUNTA 非空值单元格计数（包括字母）1044MAX 最大值1055MIN 最小值1066PROD

34、UCT 乘积1077STDEV 标准偏差值（忽略逻辑值和文本）1088STDEVP 标准偏差值1099SUM 求和11010VAR 给定样本的方差（忽略逻辑值和文本）74EXCEL-函数之分类汇总求和函数1）主要用途：对有筛选的数据区域进行求和、求个数、求最大值、求最小值、求平均值等。）主要用途：对有筛选的数据区域进行求和、求个数、求最大值、求最小值、求平均值等。筛选后，求和函数的差别筛选后，求和函数的差别Sum函数在求和时，永远只能求整个区域的值，遇到有筛选时，缺陷就出现，此时，需要用函数在求和时，永远只能求整个区域的值，遇到有筛选时，缺陷就出现，此时，需要用Subtotal函数替函数替代，

35、才可避免求和错误。其它求个数、最大值、最小值等，同样道理。代，才可避免求和错误。其它求个数、最大值、最小值等，同样道理。75EXCEL-平均值函数平均值函数AVERAGE，AVERAGEA AVERAGE(n1，n2，n255)AVERAGEA(n1，n2，n255)其中，n1，n2，n255是要计算平均值的参数，该函数最多允许有255个参数。参数可以是数字，或者是涉及数字的名称、数组或引用。两函数都是求参数的平均值。AverAge不对文本和逻辑值类数据进行计算；AverageA要对文本和逻辑函数进行平均值计算：文本被视为0，逻辑值true被视为1，false被视为0，空文本（“”）也作为0计

36、算。76EXCEL-条件平均值函数EXCEL2007提供以下两个带条件求平均值函数。AVERAGEIF(average_range,range1,criteria1)AVERAGEIFS(average_range,range1,criteria1,range2,criteria2)其中average_range是要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用；range1,range2,是计算关联条件的 1 至 127 个区域；Criteria1是用于range1的条件,criteria2是用于range2的条件,它可以是数字、表达式、单元格引用或文本形式的 1 至

37、 127 个条件，用于定义要对哪些单元格求平均值。77EXCEL-日期函数TODAY()返回当前系统设定的日期。DATEDIF(start_date,end_date,unit)Start_date 为一个日期，它代表时间段内的第一个日期或起始日期。End_date 为一个日期，它代表时间段内的最后一个日期或结束日期。Unit 为所需信息的返回类型。注：结束日期必须大于起始日期可以应用于交货期，工作进度等计算。78EXCEL-逻辑函数IF函数：IF(Logical_test,Value_if_true,Value_if_false)AND函数：AND(Logical1,Logical2,.)O

38、R函数：OR(Logical1,Logical2,.)NOT函数：NOT(Logical)79EXCEL-查找函数LOOKUPLookup函数向量型查找(在一列或一行中查找某个值)语法：(lookup_value,lookup_vector,result_vector)Lookup函数数组型查找语法：lookup(lookup_value,array)Lookup函数与其它查找函数相比特点有：1.lookup函数没有精确匹配，如果没有相等的值则取小于look_value最大的值；2.vlookup或hlookup可以指定列或行取值，而lookup第二个语法总是选择行或列的最后一个数值；3.向量

39、型查找时自动忽略错误值；4.lookup函数必须look_vector或array按升序排序；5.look_vector和result_vector范围必须一致。80EXCEL-LOOKUP的几种经典应用利用特性二查找：查找A列中最后一个数值（=LOOKUP(9E307,A:A)；利用特性三查找：查找E2:E13区域中与A列相应单元格匹配的值C2=IF(ISERROR(LOOKUP(1,0/FIND(A2,$E$2:$E$13),$E$2:$E$13)=TRUE,”,LOOKUP(1,0/FIND(A2,$E$2:$E$13),$E$2:$E$13)81EXCEL-查找函数LOOKUPLOOK

40、UP函数也可以利用数组的运算进行查找。LOOKUP函数的最大局限在于不能精确查找和匹配。82EXCEL-查找函数LOOKUPLOOKUP函数也可以利用数组的运算进行反向查找。83EXCEL-查找函数LOOKUP最常用途：对评分结果进行多级别的分类。在供应商评价、员工评价工作中经常使用，较之一般的IF函数简洁明了，且效率高。条件范围 函数运算时，检验目标值函数运算时，检验目标值A2是否处在是否处在X的各个范围，是返回的各个范围，是返回1，否返回，否返回0，形成一个数组，形成一个数组84EXCEL-查找函数VLOOKUPVlookup（lookup_value,table_array,col_in

41、dex_num,range_lookup）Lookup_value：条件值，是值，非区域，比如A1，而 非A1:A100，即使A1:A100，也只选择左上角值A1Table_array：搜索的区域/范围，是一个多维数组，如A1:D100之类Col_index_num：需要返回的值所在列，从搜索范围的起始列开始算Range_lookup：匹配方式，一般使用精确匹配，即0或FALSE例子：1）Vlookup（D2，A1:C100，2，0）2）Vlookup（”848K 15293”，A1:C100，2，0）3）Vlookup（D2&E2，A1:C100，2，0）解释：1）根据D2单元格的值，在A1

42、:C100中搜索，并返回从A列算起，第2列（即B列）的值。2）根据”848K 15293”，在A1:C100中搜索，并返回从A列算起，第2列（即B列）的值。3）根据D2与E2单元格合并后的值，在A1:C100中搜索，并返回从A列算起，第2列（即B列）的值。如果在查找区域table_array含有多个与lookup_value相等的值，即含有重复项，只能返回首个lookup_value值所对应的结果如果table_array中没有与lookup_value相等的值，函数将返回错误值#N/A85EXCEL-VLOOKUP函数中通配符的使用E3=VLOOKUP(*&E3&*,$A$3:$B$10,2

43、,0)还有Search Countif Hlookup Match Sumif函数也支持通配符。86EXCEL-数组区域位置转换正常情况下，VLOOKUP函数只能从左往右查找，要实现从右往左查找，需要通过一定的方法重新构建第二个参数“查找范围”的区域。公式中CHOOSE(2,1,$B$2:$B$11,$C$2:$C$11)是最关键的组成部分。CHOOSE中使用了数组，返回的结果也会是一个数组，在此用2和1把CHOOSE中的区域1（$B$2:$B$11）,区域2（$C$2:$C$11）进行了位置交换；最终形成了“457桥组合螺母”,“31030704E”;“车架总成”,“TU3421WW280”

44、;这样一个区域。87EXCEL-辅助列和COLUMN函数的使用F2=IF(ISERR(VLOOKUP($E2&COLUMN(A1),$A:$C,3,0)=TRUE,”,VLOOKUP($E2&COLUMN(A1),$A:$C,3,0)88EXCEL-查找函数MATCHMatch（lookup_value，lookup_array，match_type）Lookup_value：是值，非区域，例如A1，而非A1:A8。根据这个值，进行逐一比对。Lookup_array：是一个区域，一维数组，只能是一行或一列，不能多行多列。例如，A1:A100，或A1:G1，而不能A1:D10Match_type

45、：匹配方式，精确匹配、模糊匹配，一般使用精确匹配，用数值0表示。函数解释：在lookup_array中，查找与lookup_value相符的值，并返回它所在的行/列数。如果lookup_array存在两个或以上的lookup_value值，函数只会返回首个lookup_value的所在行/列数。MATCH函数用于在指定区域内按指定方式查询与指定内容所匹配的单元格位置；LOOKUP函数用于在指定区域内查询指定内容多对应的匹配区域内单元格的内容。89EXCEL-查找与引用函数OFFSETOFFSET(reference,rows,cols,height,width)OFFSET(参考单元格，移动的

46、行数，移动的列数，所要引用的行数，所要引用的列数）90EXCEL-统计个数的函数COUNT(x1,x2,x30)COUNTA(x1,x2,x30)COUNTBLANK(range)Count 统计数字的个数CountA统计数字和文本的个数CountBlank统计空白单元格的个数91EXCEL-统计个数的函数92EXCEL-统计个数的函数COUNTIF函数用法：COUNTIF(range,criteria)功能：统计出range中满足条件criteria的数据个数。Excel 2007还提供了一个多条件统计函数COUNTIFS，它可以一次对多个不同区域进行不同条件的计数，其用法如下：COUNTI

47、F(range1，criteria1，range2，criteria2，range127，criteria127)93EXCEL-统计个数的函数有学生成绩表如图所示。统计其中总分220分以下的人数；统计总分200分以上，物理80分以下，地理70分以下，化学65分以下的人数；统计姓王的同学人数。94EXCEL-函数之统计函数RANK(number,ref,order)函数返回一个数字在一组数中的排位，即位次LARGE(array,k)函数返回数据集中第k个最大值SMALL(array,k)返回数据集中第 k 个最小值MEDIAN(number1,number2,.)函数计算出给定数据的中值MOD

48、E(number1,number2,.)函数返回一组数中出现次数最多的数95EXCEL-函数之统计函数有学生成绩表如图中A1:F10区域所示。计算各学生成绩的名称、百分比排位、第3名和倒数第3名总分、中间成绩、出现次数最多的分数及在38%总分位置的分数大致是多少等数据。在G3中输入公式：=RANK(F3,$F$3:$F$10)向下复制此公式可计算名次在H3中输入公式：=PERCENTRANK($F$3:$F$10,F3,2)向下复制此公式可计算名次=LARGE(F3:F10,3)=SMALL(F3:F10,3)=MEDIAN(F3:F10)=MODE(C3:E10)=PERCENTILE(F3

49、:F10,0.38)96EXCEL-常用文本函数比较字符串EXACT(Text1,Text2)删除文本中的空格TRIM(Text)计算字符串长度LEN(Text)查找字符串FIND(Find_text,Within_text,Start_num)SEARCH(Find_text,Within_text,Start_num)从文本中取子串LEFT(Text,Num_chars)RIGHT(Text,Num_chars)MID(Text,Start_num,Num_chars)替换文本REPLACE(Old_text,Start_num,Num_chars,New_text)数字转换为文本TEXT

50、(Value,Format_text)97EXCEL-排序排序：使工作表中数据按照某一列或某些列内容的顺序来排列，使表格数据更有条理。Excel2003中的数据排序有默认排序和自定义排序两种。默认排序就是按照Excel2003默认的排序规则排列，通常是进行升序或降序排列。具体操作如下：选定工作表中需要排序的数据（可包含标题行，也可不包含），然后选择【数据】【排序】命令；通过【主要关键字】下拉列表框，选择排序关键字，确定右侧【升序】或【降序】单选按钮；若【主要关键字】所在列有重复关键字，可再通过【次要关键字】下拉列表框，选择进一步排序的关键字，右侧单选按钮同上；保持【我的数据区域】选项组中默认选