Excel高级应用技术课件.ppt

1、2Excel能做什么？图表创建图表创建条件格式条件格式数据有效性数据有效性EXCELEXCEL1 12 23 34 4Factor 2Factor 2公式公式函数函数宏宏自动筛选自动筛选高级筛选高级筛选排序排序数据透视数据透视分类汇总分类汇总一、图表制作技巧2022-9-261.1.体验体验ExcelExcel电子表格电子表格2.2.表格创建的应用技巧表格创建的应用技巧3.3.图表的类型与分析目标图表的类型与分析目标4.4.用表格数据创建分析图表用表格数据创建分析图表5.5.图表的修饰与编辑技巧图表的修饰与编辑技巧6.6.关于图表应用的综合练习关于图表应用的综合练习案例：创建工资统计表 输入数

2、据内容输入数据内容 填充法输入填充法输入月份月份 复制粘贴导入数据复制粘贴导入数据 用语音进行数字校对用语音进行数字校对 工资统计表的编辑工资统计表的编辑 查找与替换查找与替换 表格区域命名表格区域命名 语言公式语言公式 快速定位快速定位 填充（自定义序列）选项填充（自定义序列）选项 行列的编辑与调整行列的编辑与调整 插入插入/删除行或列删除行或列 调整行高和列宽调整行高和列宽 工作表的控制工作表的控制 工作表的命名工作表的命名 工作表的添加和删除工作表的添加和删除 多工作表控制多工作表控制 间隔表间隔表Ctrl 连续表连续表Shift 统计工资发放情况统计工资发放情况 按月份进行单向统计按月

3、份进行单向统计 按季度进行双向统计按季度进行双向统计 按年份进行三维统计按年份进行三维统计4表格格式数字格式数字格式千元数字格式千元数字格式特殊货币符号的设置特殊货币符号的设置RMB#,#0.00文本型数字格式的设置文本型数字格式的设置电话电话号码、会计科目号码、会计科目文字格式文字格式单元格中个别文字格式修饰单元格中个别文字格式修饰艺术字艺术字对齐方式对齐方式跨列居中与合并居中跨列居中与合并居中自动换行与人工换行自动换行与人工换行边框和背景边框和背景自动套用格式自动套用格式自定义样式自定义样式保护工作表保护工作表 单元格的保护单元格的保护 工作表的保护工作表的保护 利用定位功能保护工作表的公

4、式利用定位功能保护工作表的公式 隐藏公式隐藏公式 允许用户编辑保护区域允许用户编辑保护区域条件格式条件格式 条件格式的预警效果条件格式的预警效果 条件格式的应用条件格式的应用 突出预警突出预警 公式应用公式应用多个工作表的批量修改多个工作表的批量修改5案例案例 修饰多部门的销售统计表修饰多部门的销售统计表 图表的类型与分析目标 直方图突出显示数据间差异的比较情况直方图突出显示数据间差异的比较情况 线形图分析数据间趋势的变化线形图分析数据间趋势的变化 饼形图描述数据间比例分配关系的差异饼形图描述数据间比例分配关系的差异 雷达图显示多组有内部关联的数据对比雷达图显示多组有内部关联的数据对比 组合图

5、表可进行多组数据关系的综合分析组合图表可进行多组数据关系的综合分析2022-9-266用表格数据创建图表的步骤 确定图表的分析目标确定图表的分析目标 明确表格数据的取值范围明确表格数据的取值范围 使用图表向导确定图表类型使用图表向导确定图表类型 对图表进行编辑和修饰对图表进行编辑和修饰2022-9-267数据添加删除的技巧：数据添加删除的技巧：通过菜单选项添加 直接用数据拖拉数据 删除图表中的数据图表的修饰技巧图表的修饰技巧 修改边框 修改数据点或图块 修饰文本或数字 改变坐标轴（显示方向、刻度）添加标记（数据点、数据表、备注）图表的组合编辑技巧 间隔数据的图表制作技巧间隔数据的图表制作技巧不

6、同类型的直方图表现不同的管理目标(柱形图、条形图)图表的修饰与编辑技巧 改变图表框的大小改变图表框的大小 移动图表框、图表对象移动图表框、图表对象 修饰图表中的对象修饰图表中的对象 图表框、标题框图表框、标题框 框线、轴线、刻度线、图形线框线、轴线、刻度线、图形线 系列图块、独立图块系列图块、独立图块 为图表添加标记为图表添加标记 数值型标记数值型标记 表格式标记表格式标记 备注式标记备注式标记2022-9-268关于图表应用的综合练习 设置第设置第2Y轴轴 用于将两组相差悬殊的数据组合在同张表中用于将两组相差悬殊的数据组合在同张表中 利用两个刻度分离两组数据利用两个刻度分离两组数据 使相差悬

7、殊的数据更易于阅读使相差悬殊的数据更易于阅读 综合练习综合练习 进度表进度表2022-9-269EXCEL填写报表的15种不良习惯：1.报表题目、表头不完整或语意不确切（多在时间、主体和报表内容等方面）；报表题目、表头不完整或语意不确切（多在时间、主体和报表内容等方面）；2.报表布局、数据项目顺序不合理；钩稽关系不明确甚至错误；报表布局、数据项目顺序不合理；钩稽关系不明确甚至错误；3.数据缺失、漏填漏报；忘记注明金额单位和制表人等项目；数据缺失、漏填漏报；忘记注明金额单位和制表人等项目；4.部门名称或员工姓名出现错字、别字；部门名称或员工姓名出现错字、别字；5.部门名称或员工姓名等项目用字每次

8、不能保持一致；部门名称或员工姓名等项目用字每次不能保持一致；6.为了美化格式或由于输入手误，字串中间、前后使用空格；为了美化格式或由于输入手误，字串中间、前后使用空格；7.为求得格式好看，大量使用为求得格式好看，大量使用“合并居中合并居中”功能；功能；8.不论是文字数据还是数字数据一概不论是文字数据还是数字数据一概“居中对齐居中对齐”；9.文字和数字的字体、大小和格式不合适、不统一；文字和数字的字体、大小和格式不合适、不统一；10.行列的宽、高不适合，或是过宽或是过窄以至数字不能正确显示；行列的宽、高不适合，或是过宽或是过窄以至数字不能正确显示；11.字体或行列使用多种颜色，且搭配失当、杂乱；

9、字体或行列使用多种颜色，且搭配失当、杂乱；12.大量使用框线致使版面呆板；大量使用框线致使版面呆板；13.时间项目不按照时间项目不按照EXCEL系统的标准格式填写，且未能保持统一；系统的标准格式填写，且未能保持统一；14.标点符号全角、半角混用（如左右括号的输入）；标点符号全角、半角混用（如左右括号的输入）；15.公式计算结果如为错误值符号，一概保留，不加以清除。公式计算结果如为错误值符号，一概保留，不加以清除。2022-9-2610练习 在在“各部销售统计各部销售统计”的的“一月一月”、“二月二月”、“三月三月”三张工作表中，将其中的数字型单元三张工作表中，将其中的数字型单元格区域的格区域的

10、“B4:D6”设置为设置为“货币货币”格式，并格式，并添加添加”RMB”保护工作表并隐藏公式保护工作表并隐藏公式 设置条件预警信息设置条件预警信息11二、函数应用、高级算法及宏处理2022-9-261.1.公式的创建与引用关系公式的创建与引用关系2.2.函数的分类函数的分类3.3.几种常用函数介绍几种常用函数介绍4.4.函数功能的自学方法函数功能的自学方法5.5.宏宏公式的创建与引用关系 创建公式的方法：创建公式的方法：“=”号号 引用关系：引用关系：“$”绝对引用绝对引用 相对引用相对引用 混合引用混合引用2022-9-2613Excel函数简介 Excel函数函数 其实是预定义的公式，它们

11、使用称为参数的特定数值按特定的顺其实是预定义的公式，它们使用称为参数的特定数值按特定的顺序或结构进行计算。序或结构进行计算。嵌套函数嵌套函数 就是指在某些情况下，您可能需要将某函数作为另一函数的参数就是指在某些情况下，您可能需要将某函数作为另一函数的参数使用。使用。函数的结构函数的结构 以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。号。公式选项板公式选项板 帮助创建或编辑公式的工具，还可提供有关函数及其参数的信息。帮助创建或编辑公式的工具，还可提供有关函数及其参数的信息。单击编辑栏中的单击编辑栏中的编辑公式编辑公式按钮，或是

12、单击按钮，或是单击常用常用工具栏中的工具栏中的粘粘贴函数贴函数 按钮之后，就会在编辑栏下面出现公式选项板。按钮之后，就会在编辑栏下面出现公式选项板。2022-9-2614Excel函数分类1 1 数据库函数数据库函数当需要分析数据清单中的数值是否符合特定条件时，可以使用数当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库工作表函数。据库工作表函数。2 2 日期与时间函数日期与时间函数 通过日期与时间函数，可在公式中分析和处理日期值和时间值。通过日期与时间函数，可在公式中分析和处理日期值和时间值。3 3 工程函数工程函数工程工作表函数用于工程分析。工程工作表函数用于工程分析。4 4 财

13、务函数财务函数财务函数可以进行一般的财务计算，如确定贷款的支付额、投资财务函数可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值的未来值或净现值等等5 5 信息函数信息函数可以使用信息工作表函数确定存储在单元格中的数据的类型。可以使用信息工作表函数确定存储在单元格中的数据的类型。6 6 逻辑函数逻辑函数使用逻辑函数可以进行真假值判断，或者进行复合检验。使用逻辑函数可以进行真假值判断，或者进行复合检验。7 7 查询和引用函数查询和引用函数当需要在数据清单或表格中查找特定数值，或者需要查找某一单当需要在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用时，可以使用查询和引用工

14、作表函数。元格的引用时，可以使用查询和引用工作表函数。8 8 数学和三角函数数学和三角函数通过数学和三角函数，可以处理简单的计算，例如对数字取整、通过数学和三角函数，可以处理简单的计算，例如对数字取整、计算单元格区域中的数值总和或复杂计算。计算单元格区域中的数值总和或复杂计算。9 9 统计函数统计函数统计工作表函数用于对数据区域进行统计分析。统计工作表函数用于对数据区域进行统计分析。1010 文本函数文本函数通过文本函数，可以在公式中处理文字串。通过文本函数，可以在公式中处理文字串。1111 用户自定义函数用户自定义函数如果要在公式或计算中使用特别复杂的计算，而工作表函数又无如果要在公式或计算

15、中使用特别复杂的计算，而工作表函数又无法满足需要，则需要创建用户自定义函数。法满足需要，则需要创建用户自定义函数。2022-9-2615统计函数 求平均值求平均值Average Trimmean Averagea 求区域中数据的频率分布求区域中数据的频率分布FREQUENCY 求数据集的满足不同要求的数值的函数求数据集的满足不同要求的数值的函数 求数据集的最大值求数据集的最大值MAX与最小值与最小值MIN 求数据集中第求数据集中第K个最大值个最大值LARGE与第与第k个最小值个最小值SMALL 求数据集中的中位数求数据集中的中位数MEDIAN 求数据集中出现频率最多的数求数据集中出现频率最多的

16、数MODE 用来排位的函数用来排位的函数RANK 统计学常用函数统计学常用函数 方差方差VAR等等16查询与引用函数 引用的作用 在Excel中引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一单元格的数值。还可以引用同一工作簿不同工作表的单元格、不同工作簿的单元格、甚至其它应用程序中的数据。Vlookup函数 可以根据搜索区域内最左列的值，去查找区域内其它列的数据，并返回该列的数据，对于字母来说，搜索时不分大小写。所以，函数VLOOKUP的查找可以达到两种目的：一是精确的查找。二是近似的查找

17、。2022-9-2617Vlookup2022-9-2618文本/日期/时间函数1.大小写转换函数大小写转换函数LOWER UPPER PROPER2.取出字符串中的部分字符取出字符串中的部分字符LEFT MID RIGHT3.去除字符串的空白去除字符串的空白 TRIM4.字符串的比较字符串的比较 EXACT5.合并文本函数合并文本函数 CONCATENATE6.将数值转换为按指定数字格式表示的文本将数值转换为按指定数字格式表示的文本 TEXT7.取出当前系统时间取出当前系统时间/日期信息日期信息 NOW TODAY8.取得日期取得日期/时间的部分字段值时间的部分字段值 HOUR DAY MO

18、NTH YEAR2022-9-2619逻辑函数 来判断真假值，或者进行复合检验的Excel函数，我们称为逻辑函数。AND、OR、NOT、FALSE、IF、TRUE IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果。2022-9-2620示例：制作美观简洁的人事分析表示例：制作美观简洁的人事分析表示例的函数解释出生日期出生日期CONCATENATE(19,MID(E17,7,2),/,MID(E17,9,2),/,MID(E17,11,2)司龄（年资）司龄（年资）CONCATENATE(DATEDIF(H17,TODAY(),y),年年,DATEDIF(H17,TODAY(),y

19、m),个月个月)司龄（年资）司龄（年资）TEXT(DATEDIF(H17,TODAY(),y),00)&年年&TEXT(DATEDIF(H17,TODAY(),ym),00)&个月个月性别性别IF(VALUE(RIGHT(E17,3)/2=INT(VALUE(RIGHT(E17,3)/2),女女,男男)21数学函数 与求和有关的函数与求和有关的函数 Sum Sumif Sumproduct 四舍五入函数四舍五入函数Round 数字相乘，并返回乘积值数字相乘，并返回乘积值 Product 计算给定区域内满足特定条件的单元格的数目计算给定区域内满足特定条件的单元格的数目 Countif2022-9-2622函数的自学方法2022-9-2623认识宏 什么是宏什么是宏 宏就是所有指令的集合宏就是所有指令的集合 宏的使用时机宏的使用时机 重复性高的操作重复性高的操作 减少错误发生减少错误发生2022-9-2624宏的录制与执行 宏的录制宏的录制 通过通过“工具工具”菜单菜单“宏宏”的的“录制新宏录制新宏”进行录制进行录制 宏的执行宏的执行 通过通过“工具工具”菜单菜单“宏宏”来执行来执行 设置快捷键执行设置快捷键执行 宏的安全性设置为宏的安全性设置为“中中”2022-9-2625