教你如何高效使用Excel.docx
《教你如何高效使用Excel.docx》由会员分享，可在线阅读，更多相关《教你如何高效使用Excel.docx（16页珍藏版）》请在冰豆网上搜索。
教你如何高效使用Excel
教你如何高效使用Excel（精品）
问:
老师您好，很荣幸采访到您。
作为中国Excel第一人，想请您从整体上评价一下Excel这个工具？
您和它有怎样的故事？
它到底有怎样的魅力，让这么多人为之“着迷”。
答:
首先声明一点：
我谈不上是什么中国Excel第一人，在业界有很多高手我是望尘莫及的，很多前辈值得我尊敬和虚心学习的。
从Excel诞生到现在，已经20个年头了。
现在Excel也已经深入到工作和生活的各个方面，可以这么说，现实工作中如果离开了Excel几乎什么都玩不转。
记得我第一次使用Excel是1995年，当时就觉得很神奇，居然可以在一个一个的格子里输入数据，还可以输入计算公式，自动计算出结果，这跟当时我使用的那个文字处理软件有天壤之别！
我也就被这款具有神奇计算功能的软件所吸引，从此就怀着浓厚的兴趣，深入了解和研究Excel，与她结下了不解之缘。
记得我做的第一个数据管理和分析模版，是使用Excel做的设备管理软件，做出来后觉得很有成就感，想要什么数据就要什么数据。
但是，一次跟外商谈判过程中，看到了老外做的Excel表格，不论是从结构布局、架构搭建、报表展示，还是表格的公式设置、出表速度，都是另一幅天地，相比之下，我自己做的表格就显得根本就不是表格了。
虚心向老外请教，才知道Excel是什么，怎么用，这才认识到自己的做法全错了。
这位老外老师给我的忠告就是：
做好基础表，熟练应用各种技能，就没有什么做不出来的！
不要在把表格设计的花里胡哨的，合并单元格是Excel使用的大忌！
我设计的第二个模版是基于Excel的财务管理系统，每个月建立一个工作簿，填写每个月发生的会计凭证，最后用函数、透视表快速制作总账科目试算平衡表、科目余额表、资产负债表、利润表、现金流量表，以及各种明细表，并跟踪分析每个月的收入、成本、费用、利润的变化。
这个模版用在了很多小型企业，当时帮助不少朋友实现了账务管理的自动化。
为了能够帮助更多的朋友，我就把这个模版的制作过程、Excel相关技能等，写成我的第一本书《Excel会计应用范例精解》。
这个模版用起来尽管解决了小企业财务管理的问题，但是运算速度比较慢，同时也无法运用到大数据量的企业。
后来，又把这个模版进行了改进，联合使用ExcelVBA和Access建立了更为高效自动化的财务管理系统，真正实现了财务从凭证、核算、汇总、分析的一体化。
为了能够跟大家分享这个系统，我又结合这个系统，写成了第二本专著《ExcelVBA+Access财务系统开发从基础到实践》。
可以这么说，这是国内第一次尝试将Excel与数据库整合起来做管理系统，这种尝试是成功的，很多购买了这本书的读者，开始将书中的理念和技能用到自己企业的财务管理中。
后来很多读者就建议我帮助他们企业开发其他Excel管理软件，比如人力资源管理系统、销售管理系统、合同管理系统、往来账管理系统等等。
当时自己仍在企业工作，白天上班，晚上就研究应用Excel，觉得这种生活很充实、很有用，既帮助了别人，又提高了自己，觉得Excel越用越奥妙无穷。
陆陆续续的，一些培训机构和企业开始邀请我去做Excel培训。
我记得我的第一堂培训课是给苏州工业园区的一家大型会计师事务所做的《Excel财务与管理高效应用》，主要是为他们的企业客户培训Excel财务管理和分析方面的技能。
在课堂上，每当展示一个Excel技能和高效财务应用，大家纷纷发出“太神奇了”、“太棒了”、“还可以这么用”、“自己连菜鸟都不是”等等的赞叹声，他们的赞叹声也就成了我的动力，从此我走上了专业的Excel研究和培训咨询之路，不断的总结Excel应用心得和经验，尽可能的帮助每个学员解决他们提出的各种实际问题，并每年把培训经验、精华案例、新工具、新应用进行总结和沉淀，开发了系列的Excel高效管理应用的课程，帮助企业的每个员工都能有机会提升自己的Excel使用技能，从而提升企业的整体管理效率，这些课程包括《Excel高效财务管理应用》系列课程、《Excel高效人力资源应用》系列课程、《Excel高效数据分析》系列课程、《Excel企业运营数据偏差分析》系列课程，并在全国各地为当地的培训机构和企业客户举行公开课培训和内训咨询。
从2008年开始，与上海E讯东方（）紧密合作，开始做系统、系列的Excel培训，包括现场公开课、网络课程、企业内训等等。
从2003年开始，与中国铁道出版社紧密合作，出版了系列的Excel高效应用的专著，包括《Excel高效办公应用大全：
方法，技巧及实际案例》，《Excel2003/2007函数和公式的使用艺术》，《Excel2003/2007/2010数据透视表从入门到精通》，《用图表说话：
Excel实用精美图表大制作》，《ExcelVBA高效办公实用宝典》，《ExcelVBA活用范例大辞典》，《ExcelVBA整合数据库应用从基础到实践》，《Excel人力资源高效管理从入门到精通》，《Excel会计与财务高效管理应用从入门到精通》，《Excel数据分析之道：
职场报告应该这么做》，《构建高效数据分析模版——Excel函数和动态图表高级应用》，《Excel高效财务管理和分析模版应用大全》等等近40余本书籍。
这些书籍，都是我多年培训精华案例积累和经验沉淀，所有的Excel技能介绍都紧密结合实际问题，因此书籍一上市就受到了读者的厚爱，很多读者从此就跟我成了好朋友。
比如广州的一位总经理，几乎买了我所有的Excel书籍，自学成材，已经结合自己企业的实际情况，开发了很多基于Excel的管理系统，老总的一句评价是：
Excel这么好用，买什么软件啊。
这句话说起来有点夸张，但也从侧面反映了Excel在企业中的高效应用，对企业的管理效率提升是是多么的有用，多么的重要。
问:
前面提到了老师工作非常忙碌，常年四处飞，去讲课，想请您讲一件Excel授课过程中，最难忘的一件事。
和大家分享一下。
答:
每次培训授课，都有不同的感受，都有感人的一幕。
说到最感人的一件事，就是2011年我在北京开设《Excel高效财务管理与分析模版》这个公开课时，一位甘肃的同学，坐了一天一夜的硬卧火车，千里迢迢来到北京听我的课，他求学的精神，对知识的渴望，让我深受感动。
从此，只要我在北京上课，他都会准时来到北京。
他跟我说的一句话就是：
跟着韩老师学习的都是我们实际工作中遇到的问题，很有亲切感，没有枯燥的理论，只有实际应用。
还有一次在上海复旦大学给上海的大型国有企业中层管理人员上《Excel高效数据分析之道》公开课，看到了一位白发苍苍的、年近60的总经理，非常认真的学习了两天。
课程结束时，他专门跟我握手道别，说这是我听到的最有用、最接地气的课程，以后我会派企业员工跟你好好学习Excel。
问:
前面提到了那位白发苍苍的总经理，说要派员工和韩老师好好学习Excel。
其实，这次专访，小文子也一直跟大家要认真和韩老师学习，相信每一位三茅的用户今天看到这篇完整采访稿，都是超值而幸运的，我们没有收取您一分钱，韩老师没有任何讲课费用，却带来了一餐饕鬄盛宴。
为什么用这个词，看完本期采访，您就明白了。
我们整理了大家上周将近两千个关于Excel的提问，首先采访进入第一节：
HR使用Excel的困惑。
很多朋友提到了这个问题，其中卡卡“james123”提问：
知道怎么用Excel，但是怎样把他与工作结合呢？
还有卡卡“selina1118”留言说：
麻烦小文子，代问韩老师，强大的Excel在平时的工作中只起到了基本的表格作用，没有很好的体现其强大的各项功能，怎么样让我们重新认识Excel？
最终使其更好地为我们服务？
请韩老师针对这两个问题，讲解一下。
答:
这个问题是困扰很多Excel使用者的问题之一，包括初学者和已经使用了多年的人。
很多人说自己会用Excel了，实际上并不会使用，至少我说话这么认为的。
这么说可能有点伤人，但却是如此。
很多人把Excel的使用仅限于输入数据，做简单的加减乘除，使用简单的SUM函数，做做简单的排序和筛选，简单的V数（其实VLOOKUP函数也是似懂非懂的）。
然而，这些知识和技能对于实际工作的帮助并不大，仍旧是加班加点的工作，每个月都是在那么几天给老板赶报告！
Excel作为一个强大的数据管理和分析工具，很多人把她当作了高级计算器在使用，非常可惜。
正如我在2014年9月2日写的第一篇文章中所说的那样，“很多人把 Excel高效办公认为是技巧和快捷键大集合。
但是，对Excel没有一个真正的认识，没有一个良好的基础，没有基本的实用技能，其结果就是不断在加班加点“高效”的处理重复事情！
”， “科学规范的基础表格，是Excel高效办公的基础。
我们不能随心所欲的设计表格，不能按自己的习惯输入数据。
基础表格的设计，要结合实际工作，从管理的角度来设计。
把复杂问题简单化，而不是把简单问题复杂化，是设计基础表格的重要原则。
很多人喜欢设计大而全的表格，喜欢按照领导的意图设计表格，这些都是不可取的！
大而全的表格是把简单问题复杂化的典型代表；按照领导的意图设计表格则是没搞清楚领导要的究竟是什么。
”
如何把Excel与工作结合起来呢？
首先，要注意Excel的功能之一是数据精细化管理。
一定要把自己应做的那几张表格设计好，但不能随心所欲的来设计。
在设计任何一个表格时，要弄清楚这个表格要干什么，要完成什么样的工作，还要考虑这个表格是不是要被别人的表格所引用，或者这个表格是不是要引用别人的表格。
我们每个人设计的任何一个表格，都是相互关联的，都是反映了企业的四个数据流的管理和分析（现金流、物流、人流、质量流），这样，你设计的表格还能乱来吗？
比如，HR中有一个非常重要的基础表《员工基本信息》，尽管这个表格保存的是每个员工的基本信息，但是员工基本信息同时也反映在了HR工作中的所有表格中（招聘、合同、考勤、培训、薪酬、绩效等），这个基础表就不能随意的来设计了，应当做成一个标准的数据库，特别要注意的是，表格中不能有合并单元格、不能有空行和空列、不能有非法日期，更不能有随意在员工姓名中间加空格。
在9月4日和9月10日的两篇文章中，我对这些问题进行了详细说明，同时也给出了一个详实的基础表设计案例，感兴趣的朋友可以前去围观。
其次，Excel的重要功能是高效数据量化分析，从海量的数据中发现问题、分析问题、解决问题。
设计好基础表格后，就可以根据基础表来制作各种分析报告，为HR管理提供数据支持和决策依据。
比如，我们可以根据设计好的基础表《员工基本信息》，进行员工属性分析、流动性分析、离职分析，并为招聘提供参考。
在9月12日的文章中，我给出了根据员工基本信息进行分析的报表和图表的样式。
不论是设计基础表，还是根据基础表进行数据分析，离不开正确的思路和熟练的技能。
就思路而言，在设计基础表时就要有一个正确的逻辑和思考。
比如，这个表格要保存那些基础数据？
用几列保存？
每个单元格数据怎么输入？
是用手工随意输入、还是使用有效性控制输入？
还是使用函数自动获取数据？
等等。
而在分析数据时，更要强调逻辑和思路了。
比如，你要给领导提交一份什么样的分析报告？
具体要说明什么问题？
要给领导提供什么样的建议？
下一步工作怎么做？
等等，都是要反映在你的分析报告中的。
分析数据的重点是发现问题、分析问题、解决问题，一份千篇一律的分析报告是没有说服力的。
在我们的HR工作中，要设计很多精细化管理表格，这些表格彼此都是关联的，任何一个表格数据的变动，在其他的表格中都会有反映。
遗憾的是，现实生活中，我们每个人设计的表格只能自己使用，别人要想从你的表格里取数的话，是费了老鼻子的劲了，而且自己也看不懂别人的表格。
这个问题的症结就是数据流动不畅，使得每个人每天都在忙忙碌碌，最终也不知道自己在忙碌什么。
其实，如果你这样想：
我们都是为别人而存在，我们自己并不是一个独体，大家彼此之间都应该有充分的协调，我所做的任何一件事都要考虑对方，我们的HR管理工作还会很难吗？
中医有一句话：
通则不痛，痛则不通。
用Excel做HR管理，就是要做精细化管理和量化分析，重要的是要设计高效的自动化管理和分析模版，这样才能从日复一日的重复劳动中解脱出来，从此脱离数字苦海，让自己去做更多更重要的工作，而不是每天花大量的精力折腾手中的那几张烂表。
9月2日至9月12日我写的几篇总结，就是给大家介绍了一个员工信息管理分析模版，想必大家还有印象吧。
对于薪酬管理和分析来说，我们也可以制作一个滚动分析模版。
薪酬就是人工成本分析的基础。
在产品的成本结构中，三大成本费用是直接材料、直接人工、制造费用，可见人工成本的管控是多么的重要。
那么，如何根据每个月的薪酬数据，对人工成本进行跟踪分析，及时发现异动项目和异动月份？
设计一个薪酬滚动跟踪分析模版就可以了。
下面的图就是一个根据每个月薪酬数据（分月保存的工作表）制作的薪酬滚动分析模版，在这个模版中，我们可以任意查看人工成本各个项目的变化，对薪酬结构进行分析，并进行预算差异分析。
图1 查看总人工成本的各月变化趋势
图2 查看周末加班费的各月变化趋势
图3 月份增加，数据自动汇总到报表和图表，实现数据的自动汇总和跟踪分析
图4 对指定部门、指定月份的人工成本进行动态分析
图5 各个部门的工资四分位值分析
图6 预算执行情况跟踪分析
问:
感谢老师细致的讲解。
我们进入第二个专题：
如何高效学习Excel。
依旧选两位卡卡的代表性问题。
1、卡卡“晓晓丹”提问：
Excel是我的心头之痛，当初就是因为对它的畏惧，让我与一个好工作擦肩而过！
！
！
现在虽然对她不陌生，但也不熟悉。
我是一个对数字极端不敏感的人，而且空间感极差，所以对于函数、公式是相当的畏惧，好在学习能力尚可，幸亏有部门的同伴们不厌其烦的讲解，加之度娘的强大，现在基本的办公室操作是没有问题的。
但是，我想问，如果此类人员要精进此项，要如何学习呢？
有没有快速、通俗、易懂的教程？
（当年我是笨到抄下来，用时就打开看）
2、卡卡“Helen88”说：
谢谢小文子的这次专访，是我期待已久的，小文子费心了。
其实excel是一个让我又爱又恨的东东，自从我亲眼看到我们领导神一般的excel操作后，才发现它的功能是如此强大，因此也产生的浓厚的兴趣。
但一直困扰我的问题是：
如何系统的学习excel?
（ps：
我现在也只是会一些基本公式，和做表功能，我知道它还有很多要学习的，例如：
函数、数据分析之类的，如何下手？
）所学理论知识在实际的工作中少会用到，时间一长就会遗忘，这个问题怎么克服呢？
PS.小文子即将阵亡。
。
。
看到大家的这些问题，真的替HR们捏把汗，也替韩老师捏把汗。
答:
如果要精通使用Excel，开始阶段没有什么窍门，就是要不断的强化学习、强化练习、不断应用，也就是3个字：
学、练、用。
学习Excel要分三步走：
规则、技能、应用。
第一步：
规则。
要深入了解Excel的各种规则，因为这些规则不了解和掌握的话，Excel就会被用的一团乱糟，最终也就变成了一个高级计算器。
学习Excel就好比学开车，第一堂课就是学交规，而Excel的第一堂课也是要学“E规”。
在每次培训课上，我并不是马上就介绍函数什么的，在前一个半小时基本上都是在给大家介绍Excel的基本理念和规则。
你一定要牢记，了解Excel的这些理念和规则是多么的重要！
E规之一：
要区分Excel的两个表格：
基础表格和报告表格。
基础表格是保存最基本数据的，要设计成数据库，千万不要弄出了很多合并单元格，弄出了多行标题，更不要把几种不同的数据保存在一列。
永远要记住：
基础表是做数据管理的，其设计的原则就是：
表格的结构要越简单越好。
下面的几个表格就是一个大多数人最喜欢设计的表格类型：
多行标题、多列标题、合并单元格、在列上重复保存数据、不同数据保存在了一列等等。
但是，要对这种表格的数据进行深入分析，或者进行数据引用，却是比较麻烦的。
一句话：
不科学不规范的基础表格，是一切问题的根源，很多人在使用Excel时，把自己修炼成了这样的“至高”境界：
“没有困难创造困难也要上”；“简单问题复杂化”！
图7 不规范表格：
合并单元格
图8 不规范表格：
不同类型的数据保存在一个单元格
图9 不规范表格：
合并单元格、空行、小计、多行标题
报告表格是数据分析的结果，是给别人看的分析报告，表格里面所有的数据不是手工输入的，而是公式自动计算出来的结果。
报告表格设计的基本原则就是：
越清楚越好。
我见过这样的“分析报告”：
结构逻辑混乱、问题描述不清，最后就是连自己都不知道这份报告是要给领导说什么了。
一个完整的、有说服力的分析报告，至少要有以下几部分组成：
报告的大标题，分析报表（表格），结果图形化（图表、动态图表）。
下图就是我在9月10日的文章中，就给出了如何根据员工基本信息数据做属性分析报告，如下图。
图10 员工属性分析报告
E规之二：
要正确处理Excel的三个数据。
在Excel单元格保存的数据，无非就是三种数据：
文本、日期/时间、数字。
但是，很多人在处理这些数据时，就是根据个人的喜好和习惯开始乱来了。
比如，在输入姓名中随意加空格（还振振有词的说：
为了让每个人的姓名对齐，这样多好看啊）；在输入日期时，输成了这样的格式：
2014.9.25，20140925，9.25（回答是：
一直习惯这样输了），其实，这些输入的数据都不是日期，而是文本，这样的日期是不能计算的。
试想，如果你设计的是一个合同管理表格，要计算合同到期日，要做合同的提前提醒，这样的“日期”数据将会让你止步不前。
日期在Excel里是一种特殊的数字，是一个序列号，1代表1900年1月1日，2代表1900年1月2日，依此类推。
2014年9月25日对应的数字就是41907。
正因为日期是数字，才能进行各种计算。
试想，你输入的是文本型的日期，如何计算呢？
如何知道合同到期没？
如何提前提醒？
Excel函数和公式是没有眼睛的，是认不出来这样的“日期”的。
下面给介绍一个修改表格中非法日期的极其使用的小技巧：
分列法。
下图中的入职日期基本上都是非法日期，这些日期都无法用来进行计算。
修改这些非法日期的具体步骤如下。
图11 入职日期基本上都是错误的
步骤1：
选择A列。
步骤2：
执行“分列”命令，打开“文本分列向导”对话框，单击2次“下一步”按钮，到“步骤之3”中，选择“日期”选项按钮，并在右侧的日期格式下拉列表中选择一个与工作表单元格日期匹配的格式，如下图。
图12选择“日期”选项按钮，并选择对应格式
步骤3：
单击“完成”按钮，就得到真正的日期，如下图。
图13 得到正确日期
忠告：
当你发现很多函数用不上时，要找基础表格的原因，不要认为函数很难学。
第二步：
技能。
在每次培训课上，我都会给大家说：
Excel最难学的不是函数，而是逻辑和思路。
前几天在广州给企业财务人员上《跳出财务看管理：
财务经理的Excel数据分析之道》公开课，有几个学生问我：
怎样才能快速掌握100个函数啊？
我问他们：
为什么要学这么多函数？
哪个老师给您说要学习100个函数？
你用得上这么多函数吗？
其实，学习函数的主要难点是逻辑思维，是思路。
当掌握了Excel的基本技能后，剩下的就是要结合实际问题、实际表格，深入研究问题的解决思路，找出解决方案了。
而就具体函数而言，掌握20-30个就够用了。
对于咱们HR人员来说，掌握10-15就行了。
没必要学那么多函数。
说实话，函数这个东东，不用就会忘记。
但是，训练出的逻辑和思路却是永远都不会忘记的。
学习Excel要掌握的主要技能包括：
逻辑思维能力（对数据的理解和思考）
分类汇总能力（数据归纳计算分析）
数据查找引用能力（数据归集和链接）
数据多维分析能力（数据透视表）
分析结果呈现能力（分析图表）
与管理软件高效整合应用能力（自动化数据分析模版）
具体到Excel的技能方面，主要是三大核心技能：
函数
透视表
图表
经常有人问我，韩老师，我们应该学那些函数呢？
回答如下是，不超过30个函数：
逻辑判断函数：
IF、AND、OR、IFERROR、ISERROR
分类汇总函数：
COUNTIF、COUNTIFS、SUMIF、SUMIFS、SUMPRODUCT
查找引用函数：
VLOOKUP、MATCH、INDEX、INDIRECT、OFFSET
文本函数：
LEN、LEFT、RIGHT、MID、TEXT
日期函数：
TODAY、EDATE、DATEDIF
你可以结合自己的工作，把这些函数中最常用的、最重要的几个熟练掌握应用。
比如，如果你是做员工信息管理的，就需要重点掌握IF、COUNTIF、COUNTIFS、SUMPRODUCT、VLOOKUP、MATCH、INDEX、LEFT、RIGHT、MID、TEXT等。
如果你是主管薪酬的，下面的几个函数就必须牢牢掌握了：
IF、VLOOKUP、MATCH、INDEX、INDIRECT、SUMIF、SUMIFS、SUMPRODUCT。
如果你是做合同管理的，几个日期函数和判断函数就要熟练运用了。
函数和公式是Excel的核心。
函数本身就是公式。
但是，对于具体的工作和问题，我们需要综合使用函数来解决问题。
因此，学习函数的重点不是函数本身，而是函数的各种应用。
不论是用什么函数，我们最后要的都是一个公式。
大家自己先想一想：
什么是公式？
公式的规则是什么？
在Excel里，有几种常见的运算？
如何合理设置公式中单元格的引用方式？
如何正确快速输入嵌套函数？
等等，这些基础又基础的Excel知识，却被很多人遗忘了。
数据透视表是Excel的一个非常强大的高效数据分析工具。
透视表的应用非常广泛，从简单的分类汇总，到大数据的深度多维分析，到大量工作表的快速汇总，甚至日常工作中的核对数据，都可以利用透视表快速实现。
在每次《利用Excel提升人力资源效率》公开课以及内训课上，我都会给学员介绍如何利用透视表对员工信息表、薪酬表进行深度分析，如何快速汇总分析12个月的工资表，如何快速核对社保和公积金，等等。
下面的报表就是利用透视表所做的员工属性分析报告、工资分析报告，12个月工资表汇总分析报告，以及核对社保的对比表。
这些案例和应用，都在我一本书里有详细的介绍：
《Excel高效人力资源管理从入门到精通（实用案例版）》，中国铁道出版社出版。
图14 透视表应用：
员工属性分析报告
图15 透视表应用：
工资分析报告
图16 透视表应用：
12个月工资表汇总分析报告
图17 透视表应用：
核对社保
第三步：
应用。
学习Excel的目的是应用，也就是用来解决实际问题，也就是为企业高效管理提供整体解决方案。
比如如何设计台账，如何分析数据，如何编制分析报告。
很多人也买了几本书，也每天都在折腾Excel表格，也掌握了几个函数和工具，但是，对于稍微复杂点的表格，就无从下手了，没有半点头绪，不知道该怎么做。
其实，Excel的精髓是逻辑和思路（这个是我一直特别强调的），但是思路从何而来？
公式如何做？
请大家牢记以下三句话：
“表格结构决定思路，思路决定函数，函数决定公式。
”
使用Excel的第一步是设计标准规范的基础表，然后才是利用函数公式进行各种计算分析，编制各种分析报告。
Excel的最高应用境界是构建高效数据管理和分析模版，让数据分析自动化，而不能花大量的时间总是在“高效”的做重复的事情。
我已经开发了很多高效数据分析模版的课程，也刚刚出版了几本关于高效数据分析模版的书籍，比如《Excel高效人力资源管理从入门到精通（实用案例版）》、《Exce
升级会员 