6Excel在财务与管理中的高效运用精品文档

E韩x小ce良l在财务与管理中的高效运用
韩小良
快速汇总多个结构完全相同的工作表
• 当多个工作表的结构相同时，可以使用合并计算工具快速 进行合并计算，同时还可以创建分类汇总和分级显示。
• 案例研究:
– 案例09 快速合并多个结构相同的分报表(当前工作簿) – 案例10 快速合并多个结构相同的分报表(不同工作簿)
– SUMPRODUCT 函数：计算乘积和，可以用于多条件求和与多条件计数
= SUMPRODUCT(数组1，数组2，数组3，……..)
– SUBTOTAL 函数：对数据进行分类汇总，主要用于筛选和列表 = SUBTOTAL(函数类型，计算区域1，计算区域2，……..)
• 案例06 高效求和汇总
E韩x小ce良l在财务与管理中的高效运用
E韩x小ce良l在财务与管理中的高效运用
韩小良
韩小良老师的主要著作
1.《用图表说话：Excel实用精美图表大制作》，电子工业出版社 2.《Excel数据透视表从入门到精通》，中国铁道出版社 3.《Excel高效办公应用大全：方法、技巧及实际案例》，中国铁道出版社 4.《Excel 2019/2019函数和公式的使用艺术》，中国铁道出版社 5.《Excel VBA (2019/2019)高效办公实用宝典》，中国铁道出版社 6.《Excel VBA( 2019/2019)活用范例大辞典》，中国铁道出版社 7.《运用Excel VBA进行高效投资决策》，中国铁道出版社 8.《Excel VBA从入门到精通》，中国铁道出版社 9.《Excel VBA实用技巧大全》，中国铁道出版社 10.《Excel VBA与数据库整合应用范例精解》，科学出版社 11.《Excel VBA销售管理系统开发入门与实践》，科学出版社 12.《Excel VBA整合数据库应用从基础到实践》，电子工业出版社 13.《Excel在销售与财务管理中的应用》，电子工业出版社 14. 《Excel企业管理应用案例精萃》，电子工业出版社 15.《Excel VBA应用开发(技巧精华版)》，电子工业出版社
E韩x小ce良l在财务与管理中的高效运用
韩务管理：方法、技巧和实用模型》 《Excel高效人力资源管理：方法、技巧和实用模型》 《Excel高效管理应用：方法、技巧和实用模型》 《让你的分析报告更有说服力——Excel高效数据处理与分析》 《Excel及Excel VBA高级金融建模》 《Excel VBA快速入门与实战演练》 《Excel与数据库整合应用：方法、技巧和实际案例》
E韩x小ce良l在财务与管理中的高效运用
韩小良
快速修改非法日期
• 日期是特殊的数字，是从1开始的序列号。日期永 远是大于零的正整数。
• 正确输入日期
– 正确输入日期：2019-11-17，或者 2019/11/17 – 错误的输入日期方式：2019.11.17，或者 20191118
• 很多情况下，从系统导入的日期数据是文本，并 不是真正的日期，必须进行修改（案例01）
E韩x小ce良l在财务与管理中的高效运用
韩小良
数据透视表的灵活应用
• 数据透视表的基本概念 • 数据透视表的类型 • 准备工作 • 创建数据透视表的基本方法和注意事项 • 设置数据透视的格式 • 利用数据透视表快速编制各类统计分析报表 • 综合应用1：对比分析两个表格数据 • 综合应用2：进销存数据查询
韩小良
数据的灵活查询
• 查找函数有很多，其中使用最多的是VLOOKUP函数、 INDEX函数、MATCH函数、INDIRECT函数。
• VLOOKUP函数：根据首列数据进行查询（案例07）
=VLOOKUP(查找依据，查找区域，指定列位置，逻辑值)
• INDEX函数：根据行索引和列索引进行查询（案例08）
韩小良
快速分列数据
• 有些情况下，从数据库导入的数据是一列数据， 需要根据实际情况进行分列。
• 方法
分列前
– 方法1：使用“分列”工具的数据
– 方法2：实用文本函数
• 案例04 数据分列
分列后 的数据
E韩x小ce良l在财务与管理中的高效运用
韩小良
快速填充数据
• 有些时候，从系统导入的数据表格有大量 的空白单元格，需要进行填充。
E韩x小ce良l在财务与管理中的高效运用
韩小良
韩小良老师的主要著作(续)
16.《Excel +SQL Server数据库管理技术详解》，机械工业出版社 17.《Excel在纳税管理与筹划中的应用》，科学出版社 18.《Excel会计应用范例精解》，电子工业出版社 19.《Excel VBA +Access财务系统开发从基础到实践》，电子工业出版社 20.《Excel在投资理财中的应用》，电子工业出版社 21.《Excel VBA工资管理应用案例详解》，中国铁道出版社 22.《Excel在财务管理与分析中的应用》，中国水利水电道出版社 23.《运用Excel VBA创建高效财务管理模型》，中国铁道出版社 24.《Excel VBA行政与人力资源管理应用案例详解》，中国铁道出版社 25.《Excel VBA财务管理应用案例详解》，中国铁道出版社 26.《Excel 2019/2019数据透视表 从入门到精通 》，中国铁道出版社 27.《Excel 高效管理表单设计从入门到精通》，中国铁道出版社 28.《Excel 人力资源高效管理从入门到精通》，中国铁道出版社 29.《Excel 数据分析之道：让分析报告更有说服力》，中国铁道出版社
• 主要服务客户包括：中国银行、中国民生银行、浦发银行南京药集团、上 海万家基金、上海电信、上海太平洋资产管理公司、索尼爱立信、中储股份、 北京国华能源投资、浙江中烟集团、天津索恩照明、天津Vestas风力技术、卓 伦机械(天津)、北京奥瑞金种业、苏州瀚得汽车产品、苏州珈农生化(丁家宜)、 苏州赛发过滤科技、强生医疗器械（苏州）、上海海斯特叉车、苏州GIF研发 中心、无锡华润上华科技、中化(宁波)集团、伟福科技工业(武汉)、贵州银燕 集团、江苏交通科学研究院、南瑞集团、南京劲草时装、南京华德火花塞、上 海麦考林国际邮购、常州瓦卢瑞克钢管、广东欧普照明、广东移动、上海晶澳 太阳能光伏、上海博莱科信谊药业、上海SKF、嘉兴阿克苏诺贝尔涂料、扬州 亚普汽车部件、江苏德邦化工集团、南京朗诗集团、金螳螂、郑州煤机股份、 上海三菱电梯、豪森药业、辉瑞制药、扬州太平洋重工、南益地产集团、圣戈 班、等等数百家中外企业。
E韩x小ce良l在财务与管理中的高效运用
韩小良
课程目录
• 实用高效的数据整理技巧 • 常用函数的高效运用 • 快速汇总多个工作簿和工作表数据 • 数据透视表工具的灵活应用 • 其他应用
E韩x小ce良l在财务与管理中的高效运用
韩小良
实用高效的数据整理技巧
• 快速修改非法日期 • 快速转换文本型数字 • 快速删除数据中的特殊字符 • 快速分列数据 • 快速填充数据
E韩x小ce良l在财务与管理中的高效运用
韩小良
快速汇总结构不同的多个工作表 （特殊情况）
• 利用多重合并计算数据区域的数据透视表，可以快速把数十个甚至上百个工 作表的数据汇总在一起，然后在此基础上进行相关的统计分析。
• 注意：要汇总的多个工作表数据行数和列数可以不同，但从数据区域的第2列 开始必须是数字。
E韩x小ce良l在财务与管理中的高效运用
韩小良
快速转换文本型数字
• 在有些情况下，从数据库导入的数字是文本型数 字。文本型数字无法使用SUM等函数进行计算， 也无法利用数据透视表等工具进行分析，需要转 换为纯数字。
– 方法1：利用智能标记 – 方法2：利用VALUE函数 – 方法3：利用公式（两个负号或者乘以1或除以1） – 方法4：利用选择性粘贴的批量修改功能（乘以1或者
韩小良
高效求和汇总
• 低效率的加减公式，既不科学，也容易出错。在很多情况下，可以使用 有关的求和函数来解决。
• 常用的求和函数
– SUM函数：无条件求和。但SUM函数还有另类用法，比如快速计算表格中 的小计总和 ； 快速对多个工作表求和
– SUMIF 函数：单条件求和
=SUMIF(条件判断区域，条件值，实际求和区域)
• 案例12 快速汇总多个工作表数据(一般情况)
E韩x小ce良l在财务与管理中的高效运用
韩小良
快速汇总多个有关联的工作表
• 有些情况下，几个工作表中分别保存不同的数据，但他们通过一个关键列数 据（比如工号）联系起来。
• 要将这样几个相关联的工作表数据进行汇总，可使用Microsoft Query。 • 案例13 快速汇总多个有关联的工作表数据
E韩x小ce良l在财务与管理中的高效运用
韩小良
准备工作
• 制作数据透视表的数据区 域必须是数据清单
– 数据区域第一行为列标题
– 列标题不能重名
– 数据区域中不能有空行和空 列
– 数据区域中不能有合并单元 格
■ ■ ■ ■ ■ ■■ ■ ■ ■ ■ ■ ■ ■ ■■ ■ ■ ■ ■ ■ ■ ■ ■ ■■ ■ ■ ■ ■ ■ ■ ■ ■ ■ ■■ ■ ■ ■ ■ ■
Excel在财务与管理 中的高效运用
韩小良
资深实战型 Excel 培训讲师和应用解决方案专家
E韩x小ce良l在财务与管理中的高效运用
韩小良
韩小良 老师 简介
除以1） – 方法5：利用分列工具
• 案例02 将文本型数字转化为纯数字
E韩x小ce良l在财务与管理中的高效运用
韩小良
快速删除数据中的特殊字符
• 数据中的很多“垃圾”字符会影响到数据 的处理和分析，应当删除。
• 比如：
– 删除数据中的空格 – 删除字符中的看不见、打印不出的特殊字符
（案例03）
E韩x小ce良l在财务与管理中的高效运用
• 根据具体情况：
– 填充为上一行的数据 – 填充为数字0
• 案例05 快速填充数据
E韩x小ce良l在财务与管理中的高效运用
韩小良
常用函数的高效运用
• 一个表格中，处理分析最多的是数据求和 汇总和数据查询，因此必须熟练掌握相关 求和汇总函数和查找函数的使用。
• 高效求和汇总
• 数据的灵活查询
E韩x小ce良l在财务与管理中的高效运用
• 韩小良，资深实战型Excel培训讲师、应用解决方案专家和管理系统软件开发 设计师，对Excel及Excel VBA在企业管理中的应用有着较深的研究和独特的认 识，对Excel及VBA在管理中高级应用培训有着丰富的实战经验，尤其是Excel 及VBA在企业财务、会计、销售、人力资源、金融财务等管理中的应用。韩老 师具有丰富的实际管理经验和极强的Excel应用开发能力。从事Excel研究及应 用多年，已经为数百家中外企业进行了Excel培训和应用方案解决咨询服务， 出版了近30余部关于Excel应用方面的著作，并陆续开发出了财务、销售、人 力资源、工资等管理、客户管理、进销存等基于Excel的管理软件，成功应用 于数十家企业。
= INDEX(查找区域，指定行位置，指定列位置)
• MATCH函数：对数据进行定位（案例08）
= MATCH(查找依据，指定行位置，指定列位置)
E韩x小ce良l在财务与管理中的高效运用
韩小良
快速汇总多个工作簿和工作表数据
• 快速汇总多个结构完全相同的工作表 • 快速汇总结构不同的多个工作表（特殊情况） • 快速汇总结构不同的多个工作表（一般情况） • 快速汇总多个有关联的工作表
E韩x小ce良l在财务与管理中的高效运用
韩小良
数据透视表的基本概念
• 什么是数据透视表？ • 数据透视表能做什么？ • 怎样灵活使用数据透视表？
E韩x小ce良l在财务与管理中的高效运用
韩小良
数据透视表的类型
• 普通区域数据透视表 • 合并区域数据透视表 • 外部数据源的数据透视表 • OLAP数据透视表
• 案例11 快速汇总多个工作表数据(特殊情况)
三个分公司 数据汇总到 一张工作表
E韩x小ce良l在财务与管理中的高效运用
韩小良
快速汇总结构不同的多个工作表 （一般情况）
• 一般情况下，要汇总的各个工作表数据可能含有多列文本，此时就不 能采用普通的多重合并计算数据区域的数据透视表来进行汇不总了。
• 最好的解决方法：使用导入外部数据+SQL语句。