Excel在财务与管理中的高效运用

韩小良课程目录实用高效的数据整理技巧常用函数的高效运用快速汇总多个工作簿和工作表数据数据透视表工具的灵活应用其他应用韩小良实用高效的数据整理技巧快速修改非法日期快速转换文本型数字快速删除数据中的特殊字符快速分列数据快速填充数据韩小良快速修改非法日期日期是特殊的数字，是从1开始的序列号。日期永远是大于零的正整数。正确输入日期正确输入日期：2010-11-17，或者2010/11/17错误的输入日期方式：，或者20101118很多情况下，从系统导入的日期数据是文本，并不是真正的日期，必须进行修改（案例01）韩小良快速转换文本型数字在有些情况下，从数据库导入的数字是文本型数字。文本型数字无法使用SUM等函数进行计算，也无法利用数据透视表等工具进行分析，需要转换为纯数字。方法1：利用智能标记方法2：利用VALUE函数方法3：利用公式（两个负号或者乘以1或除以1）方法4：利用选择性粘贴的批量修改功能（乘以1或者除以1）方法5：利用分列工具案例02将文本型数字转化为纯数字韩小良快速删除数据中的特殊字符数据中的很多“垃圾”字符会影响到数据的处理和分析，应当删除。比如：删除数据中的空格删除字符中的看不见、打印不出的特殊字符（案例03）韩小良快速分列数据有些情况下，从数据库导入的数据是一列数据，需要根据实际情况进行分列。方法方法1：使用“分列”工具方法2：实用文本函数案例04数据分列

分列前的数据分列后的数据韩小良快速填充数据有些时候，从系统导入的数据表格有大量的空白单元格，需要进行填充。根据具体情况：填充为上一行的数据填充为数字0案例05快速填充数据韩小良常用函数的高效运用一个表格中，处理分析最多的是数据求和汇总和数据查询，因此必须熟练掌握相关求和汇总函数和查找函数的使用。高效求和汇总数据的灵活查询韩小良高效求和汇总低效率的加减公式，既不科学，也容易出错。在很多情况下，可以使用有关的求和函数来解决。常用的求和函数SUM函数：无条件求和。但SUM函数还有另类用法，比如快速计算表格中的小计总和；快速对多个工作表求和SUMIF函数：单条件求和=SUMIF(条件判断区域，条件值，实际求和区域)SUMPRODUCT函数：计算乘积和，可以用于多条件求和与多条件计数=SUMPRODUCT(数组1，数组2，数组3，……..)SUBTOTAL函数：对数据进行分类汇总，主要用于筛选和列表

=SUBTOTAL(函数类型，计算区域1，计算区域2，……..)案例06高效求和汇总韩小良数据的灵活查询查找函数有很多，其中使用最多的是VLOOKUP函数、INDEX函数、MATCH函数、INDIRECT函数。VLOOKUP函数：根据首列数据进行查询（案例07）=VLOOKUP(查找依据，查找区域，指定列位置，逻辑值)INDEX函数：根据行索引和列索引进行查询（案例08）=INDEX(查找区域，指定行位置，指定列位置)MATCH函数：对数据进行定位（案例08）=MATCH(查找依据，指定行位置，指定列位置)

韩小良快速汇总多个工作簿和工作表数据快速汇总多个结构完全相同的工作表快速汇总结构不同的多个工作表（特殊情况）快速汇总结构不同的多个工作表（一般情况）快速汇总多个有关联的工作表韩小良快速汇总多个结构完全相同的工作表当多个工作表的结构相同时，可以使用合并计算工具快速进行合并计算，同时还可以创建分类汇总和分级显示。案例研究:案例09快速合并多个结构相同的分报表(当前工作簿)案例10快速合并多个结构相同的分报表(不同工作簿)

韩小良快速汇总结构不同的多个工作表（特殊情况）利用多重合并计算数据区域的数据透视表，可以快速把数十个甚至上百个工作表的数据汇总在一起，然后在此基础上进行相关的统计分析。注意：要汇总的多个工作表数据行数和列数可以不同，但从数据区域的第2列开始必须是数字。案例11快速汇总多个工作表数据(特殊情况)

三个分公司数据汇总到一张工作表韩小良快速汇总结构不同的多个工作表（一般情况）一般情况下，要汇总的各个工作表数据可能含有多列文本，此时就不能采用普通的多重合并计算数据区域的数据透视表来进行汇不总了。最好的解决方法：使用导入外部数据+SQL语句。案例12快速汇总多个工作表数据(一般情况)

韩小良快速汇总多个有关联的工作表有些情况下，几个工作表中分别保存不同的数据，但他们通过一个关键列数据（比如工号）联系起来。要将这样几个相关联的工作表数据进行汇总，可使用MicrosoftQuery。案例13快速汇总多个有关联的工作表数据

韩小良数据透视表的灵活应用数据透视表的基本概念数据透视表的类型准备工作创建数据透视表的基本方法和注意事项设置数据透视的格式利用数据透视表快速编制各类统计分析报表综合应用1：对比分析两个表格数据综合应用2：进销存数据查询韩小良数据透视表的基本概念什么是数据透视表？数据透视表能做什么？怎样灵活使用数据透视表？韩小良数据透视表的类型普通区域数据透视表合并区域数据透视表外部数据源的数据透视表OLAP数据透视表韩小良准备工作制作数据透视表的数据区域必须是数据清单数据区域第一行为列标题列标题不能重名数据区域中不能有空行和空列数据区域中不能有合并单元格每列数据为同一种类型的数据整理数据区域修改非法日期把文本型数字转换为纯数字删除数据区域内的所有空行和空列取消合并单元格并进行填充去掉字符串前后的空格删除数字中的特殊字符删除不必要的小计行和总计行将二维表格整理为数据清单等等韩小良创建数据透视表的基本方法和注意事项利用数据透视表向导（案例14）按照向导步骤进行操作缺点：无法实现数据源的动态更新，除非使用动态数据区域名称解决方法：（1）利用导入数据方法；（2）利用OFFSET函数定义动态名称利用导入数据方法按照导入数据向导进行操作优点：可以实现数据源的动态更新；可以在不打开其他工作簿的情况下创建数据透视表可以以数据库的数据创建数据透视表缺点：删除数据项目后，会遗留“假”项目名称利用数据库查询方法按照数据库查询向导进行操作优点：不用导入数据，就可以制作需要的报表可以有选择性地对某些符合条件的数据制作报表可以在不打开其他工作簿的情况下创建数据透视表可以以数据库的数据创建数据透视表韩小良设置数据透视的的格式在数据透视表上对数据透视表进行重新布局修改字段名称设置字段的数字格式取消数据透视表的行或列汇总取消字段的分类汇总让数据透视表数据按照某一字段进行排序合并数据标志设置错误值的显示方式更新数据透视表的数据韩小良利用数据透视表快速编制各类统计分析报表布局透视表设置字段的分类汇总方式（案例14）设置字段的显示方式（案例14）自定义数据透视表（案例14）组合字段（案例15）制作明细表快速汇总大量工作表数据韩小良综合应用1：对比分析两个表格数据两个工作表有什么不同？如何快速把两个工作表的差异数据找出来？案例16对比分析两个表格数据

韩小良综合应用2：进销存数据查询如何快速了解各个物料、商品等的入库、出库以及库存情况？利用数据透视表，可以非常容易地制作出进销存数据查询表格。案例17进销存数据查询韩小良其他应用图表制作的注意事项和典型图表演示宏和VBA简介Excel报表和图表与Word/PPT的链接韩小良图表制作的注意事项和典型图表演示用图表准确表达出你的观点绘制图表基本方法及注意事项图表的修饰与美化用组合图表表达更加复杂的信息让图表按照您的要求灵活显示案例18组合图表演示案例案例19动态图表演示案例韩小良宏和VBA简介什么是宏？什么是VBA？如何学习宏和VBA？宏和VBA的应用：提高数