《Excel实用技能及其在管理中的应用》

1、Excel实用技能及其在管理中的应用韩小良资深实战型 Excel 培训讲师和应用解决方案专家韩小良 老师 简介韩小良，资深实战型Excel培训讲师、应用解决方案专家和管理系统软件开发设计师，对Excel及Excel VBA在企业管理中的应用有着较深的研究和独特的认识，对Excel及VBA在管理中高级应用培训有着丰富的实战经验，尤其是Excel及VBA在企业财务、会计、销售、人力资源、金融财务等管理中的应用。韩老师具有丰富的实际管理经验和极强的Excel应用开发能力。从事Excel研究及应用多年，已经为数百家中外企业进行了Excel培训和应用方案解决咨询服务，出版了20余部关于Excel应用方面

2、的著作，并陆续开发出了财务、销售、人力资源、工资等管理、客户管理、进销存等基于Excel的管理软件，应用于数十家企业，取得了明显的经济效益。主要服务客户包括：中国银行总部、Google（中国）总部、百度总部、索尼爱立信、苏州华星会计师事务所、中储股份、北京国华能源投资、浙江中烟集团、索恩照明、美国Halliburton公司、法国Saint-Gobain公司、瑞典HALDEX汽车产品、瑞士SEFAR科技、上海海斯特叉车、德国GIF研发中心、丹麦Vestas风力技术、江苏交通研究院、无锡华润上华科技、南京华德火花塞、中化(宁波)集团、德国卓伦机械(天津)、晶澳太阳能（上海）、日本（F.tech）伟

3、福科技工业（武汉）、贵州银燕集团、南京南瑞继保电器、上海医药集团、南京劲草时装、等等数百家中外企业。韩小良 主要著作1.Excel会计应用范例精解，电子工业出版社2.Excel VBA +Access财务系统开发从基础到实践，电子工业出版社3.Excel在投资理财中的应用，电子工业出版社4.Excel VBA工资管理应用案例详解，中国铁道出版社5.Excel在财务管理与分析中的应用，中国水利水电道出版社6.运用Excel VBA创建高效财务管理模型，中国铁道出版社7.Excel VBA行政与人力资源管理应用案例详解，中国铁道出版社8.Excel VBA财务管理应用案例详解，中国铁道出版社9.运

4、用Excel VBA进行高效投资决策，中国铁道出版社10.Excel VBA从入门到精通，中国铁道出版社11.Excel VBA实用技巧大全，中国铁道出版社12.Excel VBA与数据库整合应用范例精解，科学出版社13.Excel VBA销售管理系统开发入门与实践，科学出版社14.Excel VBA整合数据库应用从基础到实践，电子工业出版社15.Excel在销售与财务管理中的应用，电子工业出版社16.Excel VBA应用开发（技巧精华版），电子工业出版社17.Excel企业管理应用案例精萃，电子工业出版社18.Excel +SQL Server数据库管理技术详解，机械工业出版社19.Exc

5、el在纳税管理与筹划中的应用，科学出版社20.Excel高效办公：方法、技巧和实用模型，中国铁道出版社21.Excel VBA （ 2003/2007）高效办公实用宝典，中国铁道出版社22.用图表说话：Excel实用精美图表大制作，电子工业出版社23.Excel数据透视表从入门到精通，中国铁道出版社24.Excel 2003/2007函数和公式的使用艺术，中国铁道出版社25.Excel VBA（ 2003/2007）活用范例大辞典，中国铁道出版社如何高效使用Excel，实现科学管理？掌握常用函数和工具设计规范表格快速制作汇总统计分析报表和图表熟练使用数据透视表用图表说话HR数据日常管理本课程目

6、标宗旨：提高利用Excel进行日常管理和数据处理与分析的效率快速制作各种数据汇总统计分析报表和图表目标：掌握Excel的常用操作技巧掌握规范整理表格的常用方法和技巧掌握制作科学规范表单的方法和技巧掌握常用数据分析工具的使用方法掌握常用函数的使用方法重点掌握利用数据透视表处理分析数据的方法、技巧和各种应用掌握制作精美实用图表的基本方法课程目录Excel数据表格的设计与规范常用函数及其灵活运用快速制作汇总、统计分析报表数据透视表的灵活应用用图表说话制作精美实用图表在PPT上多维展示需要的数据和信息综合案例练习与测验学员的实际问题研究与答疑Excel财务表格的设计与规范个性化Excel操作界面，提高

7、Excel操作效率快速准确地审核别人递交的Excel表格关联报表间的链接与引用规范整理从数据库导入的数据Excel表格数据的美化整理表格的其他实用方法和技巧设计清晰、美观、实用的Excel数据管理表格个性化Excel操作界面，提高Excel操作效率养成使用Excel的好习惯设置统一字体和字号（比如11号的Arial字体）设置工作簿的默认保存/打开位置合理设计Excel表单，分清楚报告型表单和管理型表单的区别等等自定义Excel操作界面添加常用的操作按钮（格式按钮、增大/缩小字号按钮、选择性粘贴按钮、自动筛选按钮、等等）显示常用工具栏熟练使用常用的快捷键等等快速准确地审核别人递交的Excel表格

8、查找并修改错误的、非法的数据方法：利用数据有效性和圈释无效数据的方法案例01 圈释无效数据案例02 查找非法日期审核公式的正确性：引用了哪些单元格数据？数据间的勾稽关系是否正确？方法：使用公式审核工具栏案例03 审核公式查找并修改错误值或公式方法：利用定位、错误检查、公式审核的方法，快速查找错误值或出现错误的公式案例04 快速查找错误数据的单元格关联报表间的链接与引用同一个工作簿之间不同工作表间的链接与引用引用格式：工作表名!单元格地址不同一个工作簿之间不同工作表间的链接与引用引用格式：路径工作簿.xls工作表名!单元格地址建议：引用其它工作簿数据时，最好把该工作簿先打开。应用扩展：利用报表间

9、的链接与引用格式，我们可以使用INDIRECT函数动态查询引用其他工作表数据，很方便地制作动态汇总报表，快速汇总数据。规范整理从数据库导入的数据从数据库导出的数据很多情况下都需要进行整理。例如：数据分列修改非法日期将文本型数字转化为纯数字删除数字中的特殊字符查找重复数据对比分析两个表格的数据数据分列有些情况下，从数据库导入的数据是一列数据，需要根据实际情况进行分列。案例05 数据分列问题：分列后的日期如何处理？ 分列前的数据分列后的数据修改非法日期非法的日期有很多种，比如2009.1.15，20090115，90115等。可根据实际情况，采用分列、查找/替换、函数等方法进行转换。案例06 修改

10、非法日期 小知识：Excel对日期和时间的处理方式案例研究：对于下图的年、月、日3个数字，如何将其整合为真正的日期？工号部门姓名性别进公司时间年月日100001公司本部王金明男200881100002公司本部吴亚平女200471100003公司本部宋少华男200471100004公司本部龚国华男2007112110001公司本部陈刚男2007417将文本型数字转化为纯数字有些情况下，从数据库导入的数字是文本型数字。文本型数字无法使用 SUM函数等进行计算， 需要转换为纯数字。方法有很多：方法1：利用智能标记方法2：利用VALUE函数方法3：利用公式（两个负号或者乘以1或除以1）利用4：利用选择

11、性粘贴的批量修改功能（乘以1或者除以1）案例07 将文本型数字转化为纯数字删除数字中的特殊字符某些场合下，从数据库导入的数据中含有看不见也打印不出来的特殊字符，造成无法进行计算分析。解决方法1、查找/替换2、利用有关函数案例08 删除数字中的特殊字符查找重复数据对于重复数据，需要将其查找出来，或者删除多余的重复数据，使数据清单保持唯一性。方法：条件格式高级筛选数据透视表案例09 查找重复数据 对比分析两个表格的数据自己有一个表格，别人又发给您一个表格，如何快速查找这两个表格数据的差异？方法：数据透视表案例10 对比分析两个表格的数据 Excel表格数据的美化Excel表格数据的美化，是一个非常

12、重要的工作，不仅可以使报表美观、整洁，也易于察看分析数据例如：缩位显示数字日期和时间格式的设置计算误差的处理不显示工作表或者某个数据区域的零值缩位显示数字如果工作表金额数据很大，原封不动显示实际金额会使报表很难看。我们可以利用自定义数字格式的方式，将数字缩小1千倍、1万倍、10万倍、1百万倍、1千万倍等等显示。这种缩位显示不改变原数字大小。方法：使用自定义数字格式代码：缩小千位显示数字：0.00，缩小万位显示数字：0!.0,缩小十万位显示数字：0!.00,缩小百万位显示数字：0.00,缩小亿位显示数字：0.00,案例11：缩位显示数字（缩小1万倍）日期和时间的格式设置日期和时间是一种特殊的数字

13、，可以设置成各种显示格式。 案例12 将日期显示为需要的格式案例13 显示超过24小时的累计时间计算误差的处理Excel的计算误差非常令人恼火，在有些情况下会产生意想不到的后果。例如，在单元格输入公式“= 6.1-6.2+1”，看看结果是否0.9解决方法：使用ROUND函数，或者设置Excel选项。案例14 如何解决Excel的计算误差 不显示工作表或者某个数据区域的零值当工作表存在大量的零值时，会使报表很难看，可以将这些零值隐藏。这些零值可能是输入的，但大多数是查询公式的结果。方法：设置Excel选项（隐藏整个工作表的零值）自定义数字格式/条件格式（隐藏某个区域的零值）整理表格的其他实用方法

14、和技巧不规范表格的重新整理和构架搭建批量修改数据复制可见单元格数据删除数据区域内的所有空行删除字符串中的空格并排显示多个工作表快速选择单元格区域的小技巧快速选择工作表的小技巧不规范表格的重新整理和构架搭建对于不规范的表格，例如多表头，有合并单元格，等等，需要进行相应的整理。方法：根据情况，可以使用复制/粘贴，函数、VBA等方法。案例15 取消合并单元格并快速填充数据错误的表格正确的表格批量修改数据如果需要对某些区域的数据进行批量修改，可以使用选择性粘贴工具。既可以用于连续单元格区域，也可以用于不连续单元格区域。复制可见单元格数据当需要仅仅复制可见单元格数据，而剔出掉被隐藏的数据时，需要先定位这

15、些可见单元格，然后再进行复制粘贴。定位可见单元格的方法：使用“定位条件”对话框使用快捷组合键“Alt+;”删除数据区域内的所有空行数据区域内空行的存在，会影响到数据的统计分析，必须将其删除。删除方法：查找/删除筛选辅助列排序删除字符串中的空格如果字符串的前后或者内部有空格，可能会影响到以后的数据分析，比如查不到数据，筛选不出数据，等等。删除字符串中空格的方法：查找/替换使用TRIM函数并排显示多个工作表当需要同时查看和比较分析多个工作表数据时，可以新建几个窗口，然后再将这些窗口并排显示。问题：如何将某个工作表分成几个窗格，以便由于察看比较该工作表不同区域的数据？快速选择单元格区域的小技巧当需要

16、选择的单元格区域很大，或者在工作表很远的位置时，使用鼠标点选的方法是比较费劲的。快捷方法：利用名称框快速选择工作表的小技巧当工作簿有数十个或者上百个工作表时，采用单击工作表标签滚动条的方法是效率较低的。可以使用快捷菜单来快速选择某个工作表。小技巧：如何禁止别人随意打开并查看您的工作表？不显示工作表标签掩盖工作表标签设计清晰、美观、实用的Excel数据管理表格正确了解和使用两类基本的Excel表格设计科学规范Excel数据管理表格的基本原则设计科学规范Excel数据管理表格的基本方法案例演示与练习正确了解和使用两类基本的Excel表格要正确了解和使用两类基本的Excel表格：报告型表格：是给别人

17、看的，可以根据需要设计成任意的样式。管理型表单：是保存数据、进行数据分析用的，要设计成数据清单的形式。说明：在大多数情况下，报告型表格可以以管理型表单为基础，利用查找函数进行制作。如果使用VBA，还可以以报告型表格为数据录入界面，完成管理型表单数据的录入，或者制作查询报告。有些情况下，我们必须设计结构非常复杂的表格，比如成本分析表格，此时，就只能使用相关函数进行计算分析了。管理型表格报告型表格设计科学规范Excel表单的基本原则设计目标明确：表单是做什么用的？结构合理：表单要表达什么信息？信息表达结构？要整洁美观：可读性如何？（数据格式）；结构可视性如何？ （对齐格式）；结构框架性如何？（框线

18、、背景颜色） 要重视颜色的作用！表格格式的灵活性如何？（条件格式）禁止或避免输入垃圾数据。可以重复使用和共享让各个部门或人员按照您的要求填写数据实现网上多人协作编辑数据设计科学规范Excel表单的基本方法控制数据的输入，让别人按照您的要求和格式输入数据：使用数据有效性，可以进行各种输入规则的设置快速准确输入某些信息：使用查找函数，使用名称美化表格：合理设置单元格格式使用条件格式动态设置格式禁止输入垃圾数据：使用保护工作表方法禁止别人操作某些特殊单元格：采用局部保护工作表每个部门只能在自己的区域输入编辑数据：设置工作表的多用户编辑区域禁止别人随意插入、删除、重命名工作表：使用VBA语句等等案例演

19、示与练习设计一个日常费用管理台账表单，基本要求:1、标准的数据清单（流水账）2、不能空行输入3、只有某行全部单元格都输入数据后才能出现边框4、选择输入部门名称5、只能选择输入某个部门下的员工姓名6、只能输入某年的日期7、只有在A列至D列都输入数据后才能输入金额数据8、根据不同的费用项目，有不同的费用限制9、只有在上一行都输入完毕数据后，才能开始输入新的纪录10、不能在数据区域外的其他单元格输入“垃圾”数据案例16 日常费用管理：设计科学的Excel表单 常用函数及其灵活运用关于单元格引用如何快速输入嵌套函数和公式保护和隐藏公式逻辑判断函数和信息函数计数函数求和函数查找函数文本函数日期函数四舍五

20、入函数关于单元格引用什么是引用在进行数据计算时，用户既可以输入数值，也可以输入某数值所在的单元格地址，还可以输入单元格的名称，这就是引用。引用方式 相对引用绝对引用 半相对引用（半绝对引用） 混合引用 三维引用 引用快速转换小技巧循环按【F4】键，就会依照相对引用绝对引用列相对行绝对列绝对行相对相对引用这样的顺序循环下去。 如何快速输入嵌套函数和公式大多数的实际问题，需要联合使用几个函数来解决，也就是必须创建嵌套函数公式。快速准确嵌套函数公式有两种方法：方法1、联合使用名称框和函数参数对话框方法2、先分解函数和公式，最后再综合成一个公式案例案例06 修改非法日期保护和隐藏公式普通显示和隐藏：显

21、示公式计算结果或公式本身按【Ctrl+】组合键真正隐藏和保护只保护有公式的单元格实施步骤较为繁琐逻辑判断函数和信息函数逻辑判断函数IF函数根据条件对数据进行处理AND函数将几个条件连结成与条件OR函数将几个条件连结成或条件信息函数：IS类函数ISNUMBER函数：判断是否为数字ISERROR函数：判断是否为错误值N函数：将数据转换为数字说明：IF函数很多情况下是单独使用，但在有些情况下需要与其他函数联合使用。联合使用IF函数和ISERROR函数可处理公式的错误，尤其是在利用查找函数查找数据时出现错误的处理上。应用案例案例17 计算个人所得税计数函数COUNT函数统计数据区域内含有数字的单元格个

22、数 注意：日期和时间也是数字COUNTA函数统计数据区域内含有非空白单元格的个数COUNTIF计算区域中满足给定条件的单元格的个数案例18 统计订单数和订单总额 求和函数SUM函数无条件求和要注意SUM函数的使用条件：当单元格有错误值时无法计算，等。SUMIF函数单条件求和案例18 统计订单数和订单总额问题研究及解决方案：如何在满足多个条件下进行计数和求和计算？比如要统计周德宇在彩电上的订单总数和订单总额？解决方法：使用多条件的计数、求和数组公式使用数据透视表查找函数CHOOSE函数根据序号位置查找对应数据MATCH函数查找指定数据在数据区域的相对位置INDEX函数从数据区域中查找指定的行号和

23、列号交叉处的数据。VLOOKUP函数根据查找值在数据区域的首列的位置来确定指定列的数值。 ROW函数和COLUMN函数获取行号和列号OFFSET函数动态引用单元格或单元格区域INDIRECT函数将字符串转换为引用或名称查找函数（续）应用案例案例19 VLOOKUP函数：制作库存查询系统根据指定的库存代码，将某库存商品的信息查找出来。 查找函数（续）应用案例案例20 CHOOSE函数：跨表查询根据指定的月份数字，将该月份工作表数据查找并显示出来。 说明：这个问题还可以使用IF函数，但不如CHOOSE函数简单文本函数常用函数LEFT函数：从左取指定个数的字符RIGHT函数：从右取指定个数的字符MI

24、D函数：从中间某位置取指定个数的字符LEN函数：获取字符串的长度TRIM函数：去除文本两端的空格CLEAN函数：去除文本中打印不出的特殊字符案例演示 案例21 从身份证号码中获取员工基本信息 日期和时间函数常用函数TODAY函数：获取当天日期NOW函数：获取当天日期和当前时间DATE函数：将代表年、月和日的三个数字组合成日期YEAR函数、MONTH函数、DAY函数：取出日期的年、月、日三个数字WEEKDAY函数：获取某个日期是星期几分析工具库里的日期函数，在Excel 2003中，必须加载分析工具库才能使用：EOMONTH函数：获取某个月的月底日期WEEKNUM函数：获取某个日期是某年的第几周

25、DATEDIF函数：常用于计算年龄和工龄YEARFRAC函数：常用于计算年龄和工龄四舍五入函数主要函数ROUND函数：四舍五入ROUNDUP函数：向上进位ROUNDDOWN函数：向下进位应用解决实际值与显示不一致问题解决计算误差问题快速制作需要的各种汇总、统计、分析报表 数据透视表工具的灵活应用对于大多数的工作表数据，以及数据库数据，利用数据透视表来制作需要的各种汇总、统计、分析报表，是非常快捷和灵活的。不仅可以迅速汇总计算分析某个工作表数据，还可以迅速汇总多个工作表甚至多个工作簿数据。还可以对数据进行各种分组和组合，使报表内容更加丰富。快速制作需要的各种汇总、统计、分析报表 数据透视表工具的

26、灵活应用准备工作创建数据透视表数据透视表结构及术语数据透视表的类型设置数据透视的格式和选项，得到漂亮的汇总报表自定义数据透视表：添加计算字段和计算项快速创建地区销售报表快速创建商品销售报表快速创建周报、月报、季报和年报表快速创建环比分析报表快速创建同比分析报表快速创建明细分类报表快速汇总多个工作表和多个工作簿的数据 准备工作制作数据透视表的数据区域必须是数据清单数据清单的概念数据区域第一行为列标题列标题不能重名数据区域中不能有空行和空列数据区域中不能有合并单元格每列数据为同一种类型的数据整理数据区域删除数据区域内的所有空行和空列取消合并单元格并进行填充去掉字符串前后的空格删除数字中的特殊字符删

27、除不必要的小计行和总计行将二维表格整理为数据清单等等创建数据透视表利用数据透视表向导（案例22）按照向导步骤进行操作缺点：无法实现数据源的动态更新，除非使用动态数据区域名称利用导入数据方法按照导入数据向导进行操作优点：可以实现数据源的动态更新；可以在不打开其他工作簿的情况下创建数据透视表可以以数据库的数据创建数据透视表缺点：删除数据项目后，会遗留“假”项目名称利用数据库查询方法按照数据库查询向导进行操作优点：不用导入数据，就可以制作需要的报表可以有选择性地对某些符合条件的数据制作报表可以在不打开其他工作簿的情况下创建数据透视表可以以数据库的数据创建数据透视表数据透视表结构及术语什么是数据透视表

28、数据透视表结构及术语字段项行字段列字段页字段数据字段数据区拖放区域字段列表【数据透视表】工具栏：非常重要的工具数据透视表的类型普通数据区域的数据透视表多重合并计算数据区域的数据透视表外部数据的数据透视表OLAP多维数据集的数据透视表设置数据透视的的格式和选项：得到漂亮的汇总报表在数据透视表上对数据透视表进行重新布局修改字段名称设置字段的数字格式取消数据透视表的行或列汇总让数据透视表数据按照某一字段进行排序合并数据标志设置错误值的显示方式更新数据透视表的数据自定义数据透视表：添加计算字段和计算项为数据透视表设置自定义计算项目添加自定义计算项目修改自定义计算项目删除自定义计算项目为数据透视表设置自

29、定义计算字段添加自定义计算字段修改自定义计算字段删除自定义计算字段案例练习1：添加商品的平均单价 快速创建地区销售报表创建地区销售报表，分析市场占有率，为制定销售策略提供基础。快速创建商品销售报表创建商品销售报表，分析商品的销售状况，为产品生产计划提供依据 快速创建周报、月报、季报和年报表快速创建每周报表、月度报表、季度报表和年度报表，分析公司的生产销售情况，并进行预测 月报、季报和年报表分析每年、季度和月的销售情况快速创建环比分析报表制作环比增长分析报表，分析每个年度中各月相对于上个月的增长变化情况。这种报表适用于企业经营稳定，不受季节影响的场合。 环比分析报表环比分析图表快速创建同比分析报

30、表制作同比增长分析报表，分析某两个年度中各月同期的对比增长情况。这种报表适用于企业产品受季节影响较大的场合 同比分析报表同比分析图表快速创建明细分类报表双击某个汇总数据单元格，就可以迅速得到明细报表。例如，单元格C6，就得到“房天琦”在“北京”的所有销售记录明细表。快速汇总多个工作表和多个工作簿的数据方法和注意事项快速汇总多个工作表数据（特殊情况）快速汇总多个工作表数据（一般情况）方法和注意事项利用数据透视表，可以快速把数十个甚至上百个工作表的数据汇总在一起，然后在此基础上进行相关的统计分析。方法：方法1：使用多重合并计算数据区域的数据透视表方法2：使用导入数据工具+SQL语句注意事项：如果使用方法1，那么：要汇总的表格从第2列开始必须是数字。各个表格的列数和行数可以不同。如果表格从第2列开始不是数字，就需要采用方法2快速汇总多个工作表数据（特殊情况）案例23： 要汇