Excel高级应用技术

Excel高级应用Excel高级应用技术共25页，您现在浏览的是第1页!2Excel能做什么？图表创建条件格式数据有效性EXCEL1234图表Factor2计算公式函数宏筛选自动筛选高级筛选排序分析数据透视分类汇总Excel高级应用技术共25页，您现在浏览的是第2页!一、图表制作技巧2022/12/10体验Excel电子表格表格创建的应用技巧图表的类型与分析目标用表格数据创建分析图表图表的修饰与编辑技巧关于图表应用的综合练习Excel高级应用技术共25页，您现在浏览的是第3页!案例：创建工资统计表输入数据内容填充法输入——月份复制粘贴导入数据用语音进行数字校对工资统计表的编辑查找与替换表格区域命名语言公式快速定位填充（自定义序列）选项行列的编辑与调整插入/删除行或列调整行高和列宽工作表的控制工作表的命名工作表的添加和删除多工作表控制间隔表Ctrl连续表Shift统计工资发放情况按月份进行单向统计按季度进行双向统计按年份进行三维统计4Excel高级应用技术共25页，您现在浏览的是第4页!图表的类型与分析目标直方图突出显示数据间差异的比较情况线形图分析数据间趋势的变化饼形图描述数据间比例分配关系的差异雷达图显示多组有内部关联的数据对比组合图表可进行多组数据关系的综合分析2022/12/105Excel高级应用技术共25页，您现在浏览的是第5页!图表的修饰与编辑技巧改变图表框的大小移动图表框、图表对象修饰图表中的对象图表框、标题框框线、轴线、刻度线、图形线系列图块、独立图块为图表添加标记数值型标记表格式标记备注式标记2022/12/106Excel高级应用技术共25页，您现在浏览的是第6页!EXCEL填写报表的15种不良习惯：报表题目、表头不完整或语意不确切（多在时间、主体和报表内容等方面）；报表布局、数据项目顺序不合理；钩稽关系不明确甚至错误；数据缺失、漏填漏报；忘记注明金额单位和制表人等项目；部门名称或员工姓名出现错字、别字；部门名称或员工姓名等项目用字每次不能保持一致；为了美化格式或由于输入手误，字串中间、前后使用空格；为求得格式好看，大量使用“合并居中”功能；不论是文字数据还是数字数据一概“居中对齐”；文字和数字的字体、大小和格式不合适、不统一；行列的宽、高不适合，或是过宽或是过窄以至数字不能正确显示；字体或行列使用多种颜色，且搭配失当、杂乱；大量使用框线致使版面呆板；时间项目不按照EXCEL系统的标准格式填写，且未能保持统一；标点符号全角、半角混用（如左右括号的输入）；公式计算结果如为错误值符号，一概保留，不加以清除。2022/12/107Excel高级应用技术共25页，您现在浏览的是第7页!二、函数应用、高级算法及宏处理2022/12/10公式的创建与引用关系函数的分类几种常用函数介绍函数功能的自学方法宏Excel高级应用技术共25页，您现在浏览的是第8页!Excel函数简介Excel函数其实是预定义的公式，它们使用称为参数的特定数值按特定的顺序或结构进行计算。嵌套函数就是指在某些情况下，您可能需要将某函数作为另一函数的参数使用。函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。公式选项板帮助创建或编辑公式的工具，还可提供有关函数及其参数的信息。单击编辑栏中的"编辑公式"按钮，或是单击"常用"工具栏中的"粘贴函数"按钮之后，就会在编辑栏下面出现公式选项板。2022/12/109Excel高级应用技术共25页，您现在浏览的是第9页!统计函数求平均值AverageTrimmeanAveragea求区域中数据的频率分布FREQUENCY求数据集的满足不同要求的数值的函数求数据集的最大值MAX与最小值MIN求数据集中第K个最大值LARGE与第k个最小值SMALL求数据集中的中位数MEDIAN求数据集中出现频率最多的数MODE用来排位的函数RANK统计学常用函数方差VAR等10Excel高级应用技术共25页，您现在浏览的是第10页!Vlookup2022/12/1011Excel高级应用技术共25页，您现在浏览的是第11页!逻辑函数来判断真假值，或者进行复合检验的Excel函数，我们称为逻辑函数。AND、OR、NOT、FALSE、IF、TRUEIF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果。2022/12/1012示例：制作美观简洁的人事分析表Excel高级应用技术共25页，您现在浏览的是第12页!数学函数与求和有关的函数SumSumifSumproduct四舍五入函数Round数字相乘，并返回乘积值Product计算给定区域内满足特定条件的单元格的数目Countif2022/12/1013Excel高级应用技术共25页，您现在浏览的是第13页!认识宏什么是宏宏就是所有指令的集合宏的使用时机重复性高的操作减少错误发生2022/12/1014关于宏的演示Excel高级应用技术共25页，您现在浏览的是第14页!表格格式数字格式千元数字格式特殊货币符号的设置——"RMB"#,##0.00文本型数字格式的设置‘——电话号码、会计科目文字格式单元格中个别文字格式修饰艺术字对齐方式跨列居中与合并居中自动换行与人工换行边框和背景自动套用格式自定义样式保护工作表单元格的保护工作表的保护利用定位功能保护工作表的公式隐藏公式允许用户编辑保护区域条件格式条件格式的预警效果条件格式的应用突出预警公式应用多个工作表的批量修改15案例

修饰多部门的销售统计表

Excel高级应用技术共25页，您现在浏览的是第15页!用表格数据创建图表的步骤确定图表的分析目标明确表格数据的取值范围使用图表向导确定图表类型对图表进行编辑和修饰2022/12/1016案例：用图表展示销售情况数据添加删除的技巧：▪

通过菜单选项添加▪

直接用数据拖拉数据▪

删除图表中的数据

图表的修饰技巧▪

修改边框▪

修改数据点或图块▪

修饰文本或数字▪

改变坐标轴（显示方向、刻度）▪

添加标记（数据点、数据表、备注）▪

图表的组合编辑技巧

间隔数据的图表制作技巧不同类型的直方图表现不同的管理目标(柱形图、条形图)

Excel高级应用技术共25页，您现在浏览的是第16页!关于图表应用的综合练习设置第2Y轴用于将两组相差悬殊的数据组合在同张表中利用两个刻度分离两组数据使相差悬殊的数据更易于阅读综合练习进度表2022/12/1017Excel高级应用技术共25页，您现在浏览的是第17页!练习在“各部销售统计”的“一月”、“二月”、“三月”三张工作表中，将其中的数字型单元格区域的“B4:D6”设置为“货币”格式，并添加”RMB”保护工作表并隐藏公式设置条件预警信息18Excel高级应用技术共25页，您现在浏览的是第18页!公式的创建与引用关系创建公式的方法：“=”号引用关系：“$”绝对引用相对引用混合引用2022/12/1019Excel高级应用技术共25页，您现在浏览的是第19页!Excel函数分类1数据库函数当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库工作表函数。2日期与时间函数通过日期与时间函数，可在公式中分析和处理日期值和时间值。3工程函数工程工作表函数用于工程分析。4财务函数财务函数可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值等5信息函数可以使用信息工作表函数确定存储在单元格中的数据的类型。6逻辑函数使用逻辑函数可以进行真假值判断，或者进行复合检验。7查询和引用函数当需要在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用时，可以使用查询和引用工作表函数。8数学和三角函数通过数学和三角函数，可以处理简单的计算，例如对数字取整、计算单元格区域中的数值总和或复杂计算。9统计函数统计工作表函数用于对数据区域进行统计分析。10文本函数通过文本函数，可以在公式中处理文字串。11用户自定义函数如果要在公式或计算中使用特别复杂的计算，而工作表函数又无法满足需要，则需要创建用户自定义函数。2022/12/1020Excel高级应用技术共25页，您现在浏览的是第20页!查询与引用函数引用的作用在Excel中引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一单元格的数值。还可以引用同一工作簿不同工作表的单元格、不同工作簿的单元格、甚至其它应用程序中的数据。Vlookup函数可以根据搜索区域内最左列的值，去查找区域内其它列的数据，并返回该列的数据，对于字母来说，搜索时不分大小写。所以，函数VLOOKUP的查找可以达到两种目的：一是精确的查找。二是近似的查找。2022/12/1021Excel高级应用技术共25页，您现在浏览的是第21页!文本/日期/时间函数大小写转换函数LOWERUPPERPROPER取出字符串中的部分字符LEFTMIDRIGHT去除字符串的空白TRIM字符串的比较EXACT合并文本函数CONCATENATE将数值转换为按指定数字格式表示的文本TEXT取出当前系统时间/日期信息NOWTODAY取得日期/时间的部分字段值HOURDAYMONTH

YEAR2022/12/1022Excel高级应用技术共25页，您现在浏览的是第22页!示例的函数解释出生日期CONCATENATE("19",MID(E17,7,2),"/",MID(E17,9,2),"/",MID(E17,11,2))司龄（年资）CONCATENATE(DATEDIF(H17,TODAY(),"y"),"年",DATEDIF(H17,TODAY(),"ym"),"个月")司龄（年资）TEXT(DATEDIF(H17,TODAY(),"y"),"00")&"年"&TEXT(DATEDIF(H17,TODAY(),"ym"),"00")&"个月"性别IF(VALUE(RIGHT(E17,3))/2=IN