Excel高级应用技术培训11课件

1、Neusoft Group Ltd.Date: 27. Sep 2006高级应用技术培训高级应用技术培训东软培训中心东软培训中心 陆元婕陆元婕2006年年11月月Neusoft Group Ltd.Date: 10. Nov 2006Excel高级应用技术培训11 陆元婕 1995年接触电子表格 Lotus1-2-3，MS Excel4.0，MS Excel97、2000、2002、2003 2000年开始发表办公软件应用文章 其中Excel函数教程（2001年）是国内较早的完整版函数应用网上教程 2002年开始实施办公软件培训 培训过的企业客户包括：埃森哲、爱立信、税务、东软 培训过的岗位包

2、括：财务、人力资源、销售、软件测试、呼叫中心讲师与Excel联系方式培训及认证证书SAP HR咨询顾问认证COPC注册协调员认证微软办公软件应用专家认证ISO9001内部审核员 Neusoft Group Ltd.Date: 10. Nov 2006Excel高级应用技术培训11Excel商业价值体现数据发布数据发布数据存储数据存储计算计算决策决策数据分析数据分析ExcelExcel商业价值商业价值体现体现Neusoft Group Ltd.Date: 10. Nov 2006Excel高级应用技术培训11单元格单元格 行列工作表行列工作表 条件格式条件格式公式公式 函数函数 模拟运算模拟运算

3、图表创建图表创建 艺术字艺术字 自选图形自选图形 筛选筛选 数据透视数据透视 数据分数据分析析 有效性有效性 宏宏 数据保数据保护护本课程重点内容Neusoft Group Ltd.Date: 10. Nov 2006Excel高级应用技术培训11培训日程安排第1天第2天09:0009:50Excel培训课程介绍及功能初体验 综合复习与练习10:0010:50表格编修与常规统计方法Excel函数分类介绍11:0011:50表格修饰与数据预警及保护Excel交互图表的绘制11:5013:00午休时间13:0013:50Excel图表绘制方法数据有效性及宏录制14:0014:50Excel常用函数

4、模拟运算及数据分析方法15:0015:50表格数据的筛选排序和数据导入Excel与Office整合应用16:0016:50使用数据透视表和数据透视图Excel高级应用技术培训11Part 创建电子表格创建电子表格 体验试算乐趣体验试算乐趣为什么要使用为什么要使用ExcelExcel处理数据处理数据ExcelExcel的窗口界面的窗口界面明确表格管理目标与结构关系明确表格管理目标与结构关系表格数据的输入与精度控制表格数据的输入与精度控制表格的简单编辑和调整表格的简单编辑和调整表格的简单修饰技巧表格的简单修饰技巧表格数据的保存与打印输出表格数据的保存与打印输出本节重点：本节重点：认识电子表格在处理

5、数据方面的能力认识Excel全新的工作界面和工具明确Excel新功能的作用认识创建表格的六个基本过程Neusoft Group Ltd.Date: 10. Nov 2006Excel高级应用技术培训11体验Excel电子表格案例1 住房贷款计算器控制住房贷款的月偿还额的主要因素包括：贷款额度、贷款年限及贷款利率；第1种测算方法为传统的处理方法，即采用显示多变量测算结果的表格来方便查询；第2种测算方法能充分展示电子表格的优势，即输入条件立刻得出计算效果。案例2 各部销售统计直观检查公式运算关系的能力借助修饰效果增强数据管理的能力突出显示表体结构安全的数据保护状态明确的预警提示作用展示Excel强

6、大的图表分析能力 案例3 工资计算表从复杂运算发到建立数学管理模型的能力随时随地挖掘和分析数据的能力网络数据共享和协同分析的能力明确表格与表单的关系表格的特点为单表头，一组相关信息占用表格的一行纪录表单是将表头子段名称转化为标签形式Neusoft Group Ltd.Date: 10. Nov 2006Excel高级应用技术培训11体验Excel功能 Excel经典图表演示 1 2 3 4 5 Excel数据透视功能演示 Excel查找引用函数演示 Excel数据保护功能演示 Excel数据有效性演示Neusoft Group Ltd.Date: 10. Nov 2006Excel高级应用技术

7、培训11Excel窗口结构OfficeOffice助手助手智能标记智能标记任务窗格任务窗格标题栏标题栏菜单栏菜单栏工具栏工具栏编辑栏编辑栏名称栏名称栏工作表标签工作表标签水平滚动条水平滚动条状态栏状态栏垂直滚动条垂直滚动条列标定位栏列标定位栏行标定位栏行标定位栏Neusoft Group Ltd.Date: 10. Nov 2006Excel高级应用技术培训11制表的六个基本过程制作过程重点技巧构思和设置结构控制表格结构的合理性数据项目拆（独立列）与逻辑关系建立和输入数据把握表格数据的准确性输入技巧与建立和复制公式关系编辑和修饰表格增强表格数据的可读性编辑对象、数字、对齐条件格式等简单使用和分

8、析有效运用数据的分析性组织数据、创建、编辑和修饰图表保存表格数据正确进行文件归档保存保存、另存为及安全性打印输出数据掌握表格数据打印输出小表格、大表格及超表格的打印技巧Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11数据输入的方法确定输入位置（鼠标光标与屏幕状态） 输入和定位工具空心十字光标用于选择待输入的单元格位置。单击选择，双击输入单元格光标表示当前输入的位置插入点光标该光标显示在单元格中，表示没有结束输入状态回车键工具菜单选项命令中可修改默认的移动位置方向键、编辑工具栏输入基础数据的技巧 常规输入方法 通过调用法输入数据 运算符号“=

9、” 通过公式法输入数据利润=销售额-成本公式的编写复制公式 十字光标“+”快速求和单方向求和、多组数据单方求和 保证输入准确性的基本原则能使用等号输入的数据，就不要手工填写Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11表格的简单编辑和调整针对单元格的编辑操作 选中一个单元格并改写其中内容F2键的使用、编辑栏的使用、双击单元格直接编辑 用“撤销”功能恢复错误操作文档关闭或保存后，撤销按钮将处于未激活状态，此时无法执行撤销 鼠标拖拉法移动或复制单元格内容带箭头的十字光标为移动，十字光标为复制 粘贴智能标记 清除单元格内容与删除单元格结构清除可

10、用快捷键“Del”针对其他编辑对象的插入操作 行、列的插入 行列互换 “Shift”键的使用用函数求解平均值Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11表格的简单修饰技巧数字格式的修饰 工具栏按钮快速修饰货币格式小数点精确位数百分数格式 使用对话框修饰单元格格式对话框中的数字选项卡文本与对齐方式 表头居中对齐 标题跨列居中框线背景的设置 快速添加表格框线 自定义表格分割线 突出显示表头区格式菜单格式菜单单元格对话框单元格对话框选择相应的选项卡选择相应的选项卡Neusoft Group Ltd.Date: 27. Sep 2006Exce

11、l高级应用技术培训11错误问题的解决 实现错误跟踪的技巧 顺藤摸瓜公式审核中“追踪”的应用 单元格的错误提示 设置和显示错误提示 工具菜单“选项”命令中“错误检查”选项卡 改变错误提示显示状态 更改错误的公式 将单元格内的文本内容更改为数字格式 智能标记快捷菜单转换为数字 错误内容的打印 页面设置中进行选择Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11表格数据的保存与打印输出 常规保存文件 新文档的命名保存 已命名文档的覆盖保存 已命名文档的另存 打印表格 预览表格 简单调整表格输出的版面位置 页边距居中调整 打印输出Excel高级应用技

12、术培训11Part 编修表格 体验常规统计方法创建工资统计表创建工资统计表建立表格结构输入数据内容保存文件修改工资统计表内容（编辑和修饰）修改工资统计表内容（编辑和修饰）针对单元格和表格区域的操作针对列和行的操作针对工作表的操作表格的其它操作技巧命名的作用和语言公式统计工资发放情况统计工资发放情况本节重点：本节重点：复习表格的设计和结构建立过程正确使用语音校对表格的编辑和调整命名的作用和使用技巧填充的作用和使用技巧有效处理日常统计Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11创建工资统计表分析管理目标 确定表格结构 建立按月统计的工资汇总

13、表 后期需要按部门汇总，故部门名称和员工姓名独立成列 按月统计，故每月工资记录独立成列输入数据内容 填充法输入表头（月份）：填充柄（+） 复制粘贴导入数据：选择性粘贴 语音校对：工具菜单 语音命令（声音命令模式，文本朗读）保存工资表到指定位置 另存为编号部门姓名一月二月三月四月五月六月001 办公室 陈鹏800.00800.00800.00800.00800.00880.00002 办公室 王卫平685.00685.00685.00685.00685.00753.50Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11修改工资统计表内容针对单元

14、格和表格区域的操作查找和替换：表格数据的批量修改用填充柄复制内容为表格区域命名：命名与修改（插入菜单-名称命令）针对列和行的操作添加、删除行或列调整行高或列宽（最佳列宽或行高的调整，粘贴时保留列宽）隐藏或显示行或列针对工作表的操作为工作表命名移动或复制工作表为工作表标签着色多工作表的控制（选择一组工作表，统一多统一多工作表结构工作表结构）隐藏或显示工作表标签命名的作用和语言公式快速定位语言公式自动填充功能的扩展应用填充的定义排列有规律的内容，通过填充可避免重复操作并保证快速而准确常规填充方法按时间序列按文本编号按数字顺序填充特殊填充方法“工具”菜单的 自定义序列导入自定义序列直接建立自定义序列

15、使用自定义序列填充Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11统计工资发放情况按月份进行单向统计求和公式、自动求和按钮按季度进行双向统计自动求和按钮，快速求和按年份进行三维统计选择“三年汇总”工作表，定位于H4，单击自动求和按钮单击“2000年”工作表，选择H4按住Shift，再单击“2002年”，表示纵深选择3个工作表的H4观察编辑工具栏的公式正确后，回车Excel高级应用技术培训11Part 修饰表格 体验预警管理技巧为什么一定要修饰表格为什么一定要修饰表格未加修饰表格的缺陷表格修饰的种类如何使表格的数字内容更加易读如何使表格的数字

16、内容更加易读数字格式的类型常用数字格式的修饰如何保证标题结构可视性更强通过区域划分使表格更易阅读如何使表格数据更加安全如何使表格数据显示预警信息本节重点：本节重点：表格修饰的作用表格数据格式的作用和处理手段对齐方式和表体结构的可视手段边框背景和分割阅读的处理方法表格数据的安全控制和保护手段条件格式和预警信息的控制手段Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11未加修饰表格的缺陷无法突出表格数据间的内在关系可读性差保护性差结论：必须对表格进行必要的修饰以增强某单位各部门销售指标统计表制表日期：2005-10-4部门名称销售额成本利润利润率

17、部门一658700061880003990000.06057部门二636900053810009880000.15513部门三5896000100100048950000.83022合计188520001257000062820000.33323平均值6284000419000020940000.34864Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11表格修饰的种类表格修饰的种类表格修饰的目的数字格式增强不同类型数字的表现力和可读性文字格式突显表头管理项目与数据间的结构关系对齐方式表现表体结构边框和背景表现直观、个性化效果，以突出阅读信息

18、保护（工作表） 使表格处于正常运行的安全状态条件格式为了向某一个（或一类）特殊单元格提供预警信息Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11数字格式的类型数字格式数字格式功能功能常规常规系统默认值，以整数的形式表示。常规单元格格式不包括任何特定的数字格式，当单元格系统默认值，以整数的形式表示。常规单元格格式不包括任何特定的数字格式，当单元格宽度不足时，以科学计数法的形式显示宽度不足时，以科学计数法的形式显示数值数值专用于一般数字的表示。该格式由三部分组成：小数位数，千分位分隔符以及负数的显示专用于一般数字的表示。该格式由三部分组成：小数

19、位数，千分位分隔符以及负数的显示方式方式货币货币用于表示一般货币格式用于表示一般货币格式会计专用会计专用可对一列数值进行货币符号和小数点对齐可对一列数值进行货币符号和小数点对齐日期日期可以将数字转换为不同形式的日期格式，以利于查看可以将数字转换为不同形式的日期格式，以利于查看时间时间可以将数字转换为不同形式的时间格式，以利于查看可以将数字转换为不同形式的时间格式，以利于查看百分数百分数将单元格中的数字乘以将单元格中的数字乘以100，并以百分数（，并以百分数（%）形式显示）形式显示分数分数能将小数显示成分数形式，还可以设置小数点的位置能将小数显示成分数形式，还可以设置小数点的位置科学计数科学计数

20、以科学计数法显示数字，还可以设置小数点的位置以科学计数法显示数字，还可以设置小数点的位置文本文本在文本单元格格式中，将数字作为文本处理在文本单元格格式中，将数字作为文本处理特殊特殊可用于跟踪数据列表计数据库的值。主要用于在列表中显示邮政编码、中文大写数字和小可用于跟踪数据列表计数据库的值。主要用于在列表中显示邮政编码、中文大写数字和小写数字写数字自定义自定义以现有格式为基础，生成自定义的数字格式以现有格式为基础，生成自定义的数字格式Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11数字格式的类型（重点掌握）数字的精确值处理数字的精确值处理通过

21、数字选项卡或者格式工具栏的相应按钮处理通过数字选项卡或者格式工具栏的相应按钮处理负值的突出显示负值的突出显示数字选项卡处理数字选项卡处理ExcelExcel的五种负值显示格式：括号中显示红色数值；括号中的五种负值显示格式：括号中显示红色数值；括号中显示黑色数值；不带括号，但用红色表示数值；不带括号，但添加符号的显示黑色数值；不带括号，但用红色表示数值；不带括号，但添加符号的黑色数值；不带括号，但显示带负号红色数值黑色数值；不带括号，但显示带负号红色数值千元格式显示长数字千元格式显示长数字 数字选项卡自定义：在数字类型后面输入英文标点数字选项卡自定义：在数字类型后面输入英文标点“,” ,” 比如

22、比如#,#0,#,#0,货币数字格式货币数字格式直接设置货币符号直接设置货币符号自定义货币符号自定义货币符号 英文标点英文标点”中输入文本，比如中输入文本，比如“人民币人民币#,#0元元日期型数字格式日期型数字格式可以通过自定义改变可以通过自定义改变ExcelExcel默认的日期格式，比如将默认的日期格式，比如将20052005年年9 9月月1 1日日自定义为自定义为yyyy“yyyy“年年”mm“mm“月月”dd“dd“日日”;，即可显示为即可显示为20052005年年0909月月0101日日文本型数字格式文本型数字格式设置为文本格式，或者输入数字先输入英文标点单引号设置为文本格式，或者输入

23、数字先输入英文标点单引号“”隐藏数字隐藏数字在自定义格式中输入三个分号在自定义格式中输入三个分号“;”;”Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11对齐方式的用途对齐方式对齐方式用途说明用途说明水水平平对对齐齐常规常规居左排列，用于修饰文字或数字型内容居左排列，用于修饰文字或数字型内容靠左（缩进）靠左（缩进）居左排列，可设置缩进，只用于修饰文字型内容居左排列，可设置缩进，只用于修饰文字型内容居中居中居中排列，可用于修饰表头文字型内容居中排列，可用于修饰表头文字型内容靠右（缩进）靠右（缩进）居右排列，可设置缩进，只用于修饰数字或文字型内

24、容居右排列，可设置缩进，只用于修饰数字或文字型内容填充填充相同内容填满整个单元格，以防他人输入内容相同内容填满整个单元格，以防他人输入内容两端对齐两端对齐多行文字时，除最后一行居左，其他行均撑满单元格多行文字时，除最后一行居左，其他行均撑满单元格跨列居中跨列居中内容跨越多个单元格而居中，用于表格标题的居中内容跨越多个单元格而居中，用于表格标题的居中分散对齐（缩进）分散对齐（缩进） 文字之间拉开距离，左右两端顶格，可设置缩进文字之间拉开距离，左右两端顶格，可设置缩进垂垂直直对对齐齐靠上（缩进）靠上（缩进）居上排列，可设置缩进居上排列，可设置缩进居中居中居中排列，用于修饰表头文字型内容居中排列，用

25、于修饰表头文字型内容靠下（缩进）靠下（缩进）居下排列，可设置缩进居下排列，可设置缩进两端对齐两端对齐多列文字时，除最后一列居上，其余列均撑满单元格多列文字时，除最后一列居上，其余列均撑满单元格分散对齐（缩进）分散对齐（缩进） 文字之间拉开距离，上下两端顶格，可设置缩进文字之间拉开距离，上下两端顶格，可设置缩进Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11边框和背景的作用与修饰技巧 为了能够清晰的阅读表格数据，通常将表格分为表头、数据及统计三区。可以使用格线和背景进行修饰，以突出显示效果 使用“自动套用格式”快速修饰表格格式 直接使用自动套

26、用格式 使用选项个性化套用格式 自定义样式设置 格式菜单的样式命令 绘图框线的添加 格式工具栏边框按钮 单击绘图边框，调出边框工具栏 选择线条样式 选择需要添加边框的边框网格Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11保护表格的作用和对象 为什么要保护表格数据 电子表格最大的特点就是其管理数据的灵活性和易用性。但是，灵活性在管理活动的某种情况下将转化为弱势，即安全性差 为了解决填表人和用表人之间的矛盾，电子表格通过格式的保护功能加以实现 如何保护表格数据 Excel提供的保护措施包括：锁定单元格（输入）和隐藏单元格（公式）两类 通常情况

27、下，运行状态的表格（完成了结构设计、输入、编辑及修饰的制表过程，准备定期使用的表格）通常不可以随意修改表格结构（包括表头和统计区预先设置的公式），故需要对表格结构进行保护Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11设置保护的方法保护的内容 保护工作簿 保护工作表 保护工作表的部分内容保护的设置 设置保护格式 设置保护密码 设置允许用户编辑权限 对单元格的隐藏保护单元格保护，设定隐藏，然后再保护技巧 使用“定位”功能批量保护单元格 保护并隐藏单元格的公式（隐藏+保护+;）Neusoft Group Ltd.Date: 27. Sep 20

28、06Excel高级应用技术培训11条件格式的使用 条件格式的作用 通过条件格式的设置，当数据超过一定条件时及时显示预警信息 条件格式的设置 格式菜单的条件格式命令 简单条件的设置 多重条件的设置 公式条件的设置 条件格式的应用 数据的预警 快速画表 整行数据标识例Excel高级应用技术培训11Part 创建图表 体验数据分析效果图表在数据分析中的作用图表在数据分析中的作用用表格数据创建分析图表用表格数据创建分析图表图表的编辑技巧图表的编辑技巧图表框的常规编辑技巧图表框的特殊编辑修饰图表以增强数据的表现力修饰图表以增强数据的表现力图表中的修饰对象和定位标志图表内各类对象的修饰有效的控制图表打印有

29、效的控制图表打印图表制作的技巧与综合运用图表制作的技巧与综合运用本节重点：本节重点：明确图表分析的目标与正确选择表格数据的关系合理确定图表类型以正确表现分析的目标图表的编辑、修饰及打印Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11图表的类型与分析目标 直方图突出显示数据间差异的比较情况 线形图分析数据间趋势的变化 饼形图描述数据间比例分配关系的差异 雷达图显示多组有内部关联的数据对比 组合图表可进行多组数据关系的综合分析Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11用表格数据创建图表

30、的步骤 确定图表的分析目标 明确表格数据的取值范围 使用图表向导确定图表类型 对图表进行编辑和修饰数据添加删除的技巧：数据添加删除的技巧： 通过菜单选项添加 直接用数据拖拉数据 删除图表中的数据 图表的修饰技巧图表的修饰技巧 修改边框 修改数据点或图块 修饰文本或数字 改变坐标轴（显示方向、刻度） 添加标记（数据点、数据表、备注） 图表的组合编辑技巧 间隔数据的图表制作技巧间隔数据的图表制作技巧不同类型的直方图表现不同的管理目标(柱形图、条形图) Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11图表的修饰技巧 改变图表框的大小 移动图表框、

31、图表对象 修饰图表中的对象 图表框、标题框 框线、轴线、刻度线、图形线 系列图块、独立图块 为图表添加标记 数值型标记 表格式标记 备注式标记Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11图表综合运用T0100200300400500600700800Q1Q2Q3Q4Quarterly Results0100200300400OctNovDec020040060080010001200Region 1Region 2Region 3JanuaryMayExcel高级应用技术培训11Part 用数学算法建立管理模型用数学算法建立管理模型公式

32、在拖拉复制中的特殊算法公式在拖拉复制中的特殊算法公式复制与引用关系相对引用案例绝对引用和应用案例混合引用和应用案例函数应用与高级算法处理函数应用与高级算法处理函数的分类几种常用函数介绍函数功能的自学方法本节重点：本节重点：复制公式和相对引用绝对引用案例与技巧混合引用案例与技巧三类典型函数和应用函数的套用技巧函数的学习方法其他算法简介Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11函数在Excel中的作用 简化公式 对某个区域求和（Sum函数） 实现其他方法无法实现的计算 对某个区域进行条件求和（Sumif函数） 提高编辑数据的速度 实现判断

33、功能 根据不同条件在单元格中显示不同结果（IF函数）Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11公式函数-公式的创建与引用关系创建公式的方法：“=”号普通的四则运算单向统计双向统计三维统计引用关系：“$”绝对引用 相对引用混合引用项项 目目数数 额额占占 收收 入入 % %销销 售售 收收 入入1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0固固 定定 成成 本本2 2 0 0 0 0 0 0 0 0 0 0 0 0= = B B 3 3 / / $ $ B B $ $ 2 2变变 动动 成成 本本1 1 5 5 0 0

34、0 0 0 0 0 0 0 0= = B B 4 4 / / $ $ B B 2 2营营 销销 费费 用用3 3 0 0 0 0 0 0 0 0 0 0 0 0= = B B 5 5 / / B B $ $ 2 2管管 理理 费费 用用1 1 0 0 0 0 0 0 0 0 0 0 0 0= = B B 6 6 / / $ $ B B $ $ 2 2利利 润润= = B B 2 2 - - S S U U M M ( ( B B 3 3 : : B B 6 6 ) )= = B B 7 7 / / B B 2 2利利 润润 率率= = B B 7 7 / / B B 2 2Neusoft Gr

35、oup Ltd.Date: 27. Sep 2006Excel高级应用技术培训11Excel函数在工作表中的商业应用 Excel函数的应用方式和目的 如何发现Excel中的函数 函数在Excel中的书写位置 Excel函数的商用场景 自定义Excel函数 如何实现Excel函数的调试 各类Excel函数概要介绍Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11Excel函数简介Excel函数其实是预定义的公式，它们使用称为参数的特定数值按特定的顺序或结构进行计算。函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。嵌套函数就

36、是指在某些情况下，您可能需要将某函数作为另一函数的参数使用。公式选项板帮助创建或编辑公式的工具，还可提供有关函数及其参数的信息。单击编辑栏中的编辑公式按钮，或是单击常用工具栏中的粘贴函数 按钮之后，就会在编辑栏下面出现公式选项板。Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11Excel中函数的类型 财务函数 日期与时间函数 数学与三角函数 统计函数 查找与引用函数 数据库函数 文本函数 逻辑函数 信息函数 工程函数300多个函数Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11挖宝，函数

37、在哪里Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11在Excel中插入函数的方法 手工输入函数函数 在录入时可以访问到该函数的帮助 Excel将自动补充函数末尾的括号 使用Ctrl+Shift+A可以看到函数的参数说明 插入函数对话框 可以根据需求查找函数 将给出该函数的参数对话框Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11如何在函数中输入参数 不带参数Rand() Today() Now() 使用名称作为参数Sum(Sales) Sales是该工作簿中某个区域的名称 整行或整列作

38、为参数Sum(C:C) 使用值作为参数Left(A1,1) 表达式作为参数ASIN(D1+D2) 使用函数作为参数INT(RAND()*10) 使用数组作为参数Or(B1=3,4,5)Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11函数出现在Excel中的位置Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11Excel中的函数审核工具 跟踪单元格 引用单元格 从属单元格 跟踪错误 Excel公式求值Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技

39、术培训11在使用函数时应了解 单元格的引用问题 相对地址引用 =A1+A2+A3 绝对地址引用 =$A$1+$A$2 相对地址的引用 =$A1+A$2 三维地址引用 =Sheet!A1+A2 运算符的优先级问题 实际数值与显示数值不一致Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11在Excel中可能出现的错误值#DIV0! 数据被零除，为了避免这种错误可以使用IF函数#N/A 数据无效或没有定义#NAME? 包含了没有定义的范围或单元格#NULL! 试图引用没有重叠区域的2个单元格区域#NUM! 无效参数，或者返回的数值超出Excel定义

40、范围#REF! 无效的单元格引用#VALUE! 数据类型错误，或者输入函数时出现输入错误Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11函数的自学方法Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11Excel函数分类1 数据库函数当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库工作表函数。2 日期与时间函数日期与时间函数通过日期与时间函数，可在公式中分析和处理日期值和时间值。3 工程函数工程工作表函数用于工程分析。4 财务函数财务函数可以进行一般的财务计算，如确定贷款的支付额

41、、投资的未来值或净现值等5 信息函数可以使用信息工作表函数确定存储在单元格中的数据的类型。6 逻辑函数使用逻辑函数可以进行真假值判断，或者进行复合检验。7 查询和引用函数查询和引用函数当需要在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用时，可以使用查询和引用工作表函数。8 数学和三角函数数学和三角函数通过数学和三角函数，可以处理简单的计算，例如对数字取整、计算单元格区域中的数值总和或复杂计算。9 统计函数统计函数统计工作表函数用于对数据区域进行统计分析。10 文本函数文本函数通过文本函数，可以在公式中处理文字串。11 用户自定义函数如果要在公式或计算中使用特别复杂的计算，而工作表

42、函数又无法满足需要，则需要创建用户自定义函数。Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11日期与时间函数函数函数用途用途语法语法DATEDIFDATEDIF计算两个日期之间的天数、月数或年数DATEDIF(start_date,end_date,unit)YEARFRAC返回代表start_date和end_date之间的天数的年分数YEARFRAC(start_date,end_date,basis)EDATE返回用于表示开始日期之前或之后月数的日期的序列号EDATE(start_date,months)EOMONTH返回指定个数月之

43、前或之后的月的末日的序列号EOMONTH(start_date,months)DAY将序列号转换为月的日期DAY(serial_number)MONTH将序列号转换为月MONTH(serial_number)YEAR将序列号转换为年YEAR(serial_number)NOW返回当前日期和时间的序列号NOW( )TODAY返回今天日期的序列号TODAY( )NETWORKDAYS返回两个日期之间的所有工作日个数NETWORKDAYS(start_date,end_date,holidays)WORKDAY返回指定个数工作日之前或之后日期的序列号WORKDAY(start_date,days,h

44、olidays)WEEKDAY将序列号转换为一星期的某天WEEKDAY(serial_number,return_type)WEEKNUM将序列号转换为代表该星期为一年中的第几周的数字WEEKNUM(serial_num,return_type)Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11文本函数函数函数用途用途语法语法EXACT检查两个文本值是否相同EXACT(text1,text2)FIXED将数字格式设置为具有固定小数位数FIXED(number,decimals,no_commas)LEFT返回文本值中最左边的字符LEFT(te

45、xt,num_chars)LEFTB(text,num_bytes)MID在文本字符串中，从您所指定的位置开始返回指定数量的字符MID(text,start_num,num_chars)MIDB(text,start_num,num_bytes)RIGHT返回文本值中最右边的字符RIGHT(text,num_chars)RIGHTB(text,num_bytes)LOWER将文本转换为小写LOWER(text)PROPER将文本值的每个字的首字母大写PROPER(text)UPPER将文本转换成大写形式UPPER(text)REPT按照给定的次数重复显示文本REPT(text,number_t

46、imes)VALUE将文本参数转换为数字VALUE(text)T将其参数转换为文本T(value)TEXT设置数字格式并将其转换为文本TEXT(value,format_text)CONCATENATE将几个文本项合并为一个文本项CONCATENATE (text1,text2,.)TRIM从文本删除空格TRIM(text)Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11数学函数函数函数用途用途语法语法CEILING 将参数 Number 沿绝对值增大的方向，舍入为最接近的整数或基数CEILING(number,significance)F

47、LOOR 将参数 Number 沿绝对值减小的方向去尾舍入，使其等于最接近的 significance 的倍数FLOOR(number,significance)COUNTIF 计算给定区域内满足特定条件的单元格的数目EVEN 返回沿绝对值增大方向取整后最接近的偶数EVEN(number)INT 返回实数舍入后的整数值INT(number)ODD 返回对指定数值进行舍入后的奇数ODD(number)ROUND 返回某个数字按指定位数舍入后的数字ROUND(number,num_digits)ROUNDDOWN 靠近零值，向下（绝对值减小的方向）舍入数字ROUNDDOWN(number,num_

48、digits)ROUNDUP 远离零值，向上（绝对值增大的方向）舍入数字ROUNDUP(number,num_digits)SUM 返回某一单元格区域中所有数字之和SUMIF 根据指定条件对若干单元格求和SUMPRODUCT 在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11查找引用函数函数函数用途用途语法语法INDEX使用索引从引用或数组选择值INDEX(reference,row_num,column_num,area_num)INDIRECT返回由文本值指定的引用INDIRECT

49、(ref_text,a1)MATCH在引用值或数组中查找值MATCH(lookup_value,lookup_array,match_type)OFFSET从给定引用值返回引用偏移量OFFSET(reference,rows,cols,height,width)HLOOKUP查找数组的首行，并返回特定单元格的值HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)VLOOKUP查找数组首列，移动到行并返回单元格的值VLOOKUP(lookup_value,table_array,col_index_num,range_looku

50、p)ROW返回引用的行个数ROW(reference)ROWS返回引用中包含的行个数ROWS(array)COLUMN返回引用的列个数COLUMN(reference)COLUMNS返回引用中包含的列个数COLUMNS(array)TRANSPOSE返回数组的转置TRANSPOSE(array)Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11统计函数函数函数用途用途语法语法AVERAGE返回参数的平均值AVERAGE(number1,number2,.)AVERAGEA返回参数的平均值，包括数字、文本和逻辑值AVERAGEA(value1,

51、value2,.)TRIMMEAN返回数据集的内部平均值TRIMMEAN(array,percent)COUNT计算参数列表中数字的个数COUNT(value1,value2,.)COUNTA计算参数列表中值的个数COUNTA(value1,value2,.)COUNTBLANK计算区间内的空白单元格个数COUNTBLANK(range)COUNTIF计算满足给定标准的区间内的非空单元格的个数COUNTIF(range,criteria)MAX返回参数列表中的最大值MAX(number1,number2,.)MIN返回参数列表中的最小值MIN(number1,number2,.)MODE返回数

52、据集中出现最多的值MODE(number1,number2,.)MEDIAN返回给定数字的中值MEDIAN(number1,number2,.)RANK返回某数在数字列表中的排位RANK(number,ref,order)LARGE返回数据集中第k个最大值LARGE(array,k)SMALL返回数据集中的第k个最小值SMALL(array,k)PERCENTRANK返回数据集中值的百分比排位PERCENTRANK(array,x,significance)FREQUENCY以向量数组的形式返回频率分布FREQUENCY(data_array,bins_array)Neusoft Group

53、Ltd.Date: 27. Sep 2006Excel高级应用技术培训11Excel函数应用案例统计某一部门加班费Sumif函数查找函数VlookupHlookupLookup计算员工的年龄Year做减法Datedif（在Excel中找不到的函数）Datedif(start_date,end_date,code)CodeY,M,DYM,MDNeusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11Excel函数应用案例统计单元格区域中的唯一值Countif可应用在条件格式或数据有效性中对单元格区域中的n个最大值或最小值进行运算Max MinLarge

54、 SmallNeusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11数组公式 数组公式 是用于建立可以产生多个结果或对可以存放在行和列中的一组参数进行运算的单个公式。 数组公式的特点就是可以执行多重计算,它返回的是一组数据结果。 参数 数组公式最大的特征就是所引用的参数是数组参数,包括区域数组和常量数组。 输入 同时按下CTRL+SHIFT+ENTER 典型应用 求满足多组条件的数据的和（SUM+IF） 求某组数据的频率分布（FREQUENCY）Excel高级应用技术培训11Part 表格数据的筛选和排序整理表格数据以便统计分析整理表格数据以便统计

55、分析统一数据类型填补空白单元格正确修饰单元格内容的格式对表格数据的筛选分析对表格数据的筛选分析自动筛选数据高级筛选表格数据的排序组合表格数据的排序组合排序原则排序方法特殊排序技巧 本节重点：本节重点：统计数据的准备工作表格数据的筛选表格数据的排序Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11数据的汇总与筛选 Excel数据分析的基本方法与工具 排序的方法与技巧 自动筛选与高级筛选 分类汇总的方法 数据有效性的设置与应用Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11表格数据整理要点 统

56、一数据类型 同列各单元格的内容必须是同类型数据 大型表格增加编号列，以便恢复原始排序顺序 填补空白单元格 避免上表头行中出现空白 上表头和数据区之间不能使用空行 正确修饰单元格内容的格式 避免空白内容（除非确定无值） 勿输入多余的空格（影响排序和筛选等）Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11默认排序次序在按升序排序时，Microsoft Excel 使用如下次序（在按降序排序时，除了空白单元格总是在最后外，其他的排序次序反转）： 数字数字：数字从最小的负数到最大的正数进行排序。 按字母先后顺序排序按字母先后顺序排序：在按字母先后顺

57、序对文本项进行排序时，Excel 从左到右一个字符一个字符地进行排序。例如，如果一个单元格中含有文本“A100”，则这个单元格将排在含有“A1”的单元格的后面，含有“A11”的单元格的前面。 文本以及包含数字的文本，按下列次序排序文本以及包含数字的文本，按下列次序排序:0 1 2 3 4 5 6 7 8 9 （空格） ! # $ % & ( ) * , . / : ; ? _ | + A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 撇号撇号 () 和连字符和连字符 (-) 会被忽略。会被忽略。但例外情况是：如果两个文本字符串除了连

58、字符不同外其余都相同，则带连字符的文本排在后面。 逻辑值逻辑值 ：在逻辑值中，FALSE 排在 TRUE 之前。 错误值错误值：所有错误值的优先级相同。 空格空格 ： 空格始终排在最后。Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11数据筛选-有关筛选 什么是筛选 筛选是查找和处理区域中数据子集的快捷方法。 筛选区域仅显示满足条件 的行，该条件由用户针对某列指定。 Microsoft Excel 提供了两种筛选区域的命令： 自动筛选，包括按选定内容筛选，它适用于简单条件 高级筛选，适用于复杂条件 筛选的特点 与排序不同，筛选并不重排区域。筛

59、选只是暂时隐藏不必显示的行。 Excel 筛选行时，您可对区域子集进行编辑、设置格式、制作图表和打印，而不必重新排列或移动。Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11数据筛选-自动筛选和高级筛选的区别 自动筛选 使用自动筛选命令时，自动筛选箭头 显示于筛选区域中列标签的右侧。 您可以使用自定义自动筛选，以显示含有一个值或另一个值的行。您也可以使用自定义自动筛选以显示某个列满足多个条件的行。 高级筛选 高级筛选命令可像自动筛选命令一样筛选区域，但不显示列的下拉列表，而是在区域上方单独的条件区域中键入筛选条件 。 条件区域允许根据复杂的条

60、件进行筛选。Neusoft Group Ltd.Date: 27. Sep 2006Excel高级应用技术培训11数据筛选-复杂筛选条件示例a单列上具有多个条件 如果对于某一列具有两个或多个筛选条件，那么可直接在各行中从上到下依次键入各个条件。多列上具有单个条件 若要在两列或多列中查找满足单个条件的数据，请在条件区域的同一行中输入所有条件。某一列或另一列上具有单个条件 若要找到满足一列条件或另一列条件的数据，请在条件区域的不同行中输入条件。两列上具有两组条件之一 若要找到满足两组条件（每一组条件都包含针对多列的条件）之一的数据行，请在各行中键入条件。一列有两组以上条件 若要找到满足两组以上条件的行，请