怎么才算精通Excel？斗胆谈谈本人对“精通Excel“的理解

龙逸凡 · 发表于 2016-9-2 17:13

这是本人回答知乎的一个问题，转贴过来，欢迎大家一起讨论、批评指正。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

前言
看到这个问题很久了，一直想斗胆谈谈本人对“精通Excel“的理解，今天终于成文，大家看完了如果同意本人观点，就点个赞，不同意，欢迎指正讨论，但请勿拍砖，很痛的。
本人不是高手，只是一个用Excel多年的大表哥，充其量只是掌握了20%功能的半桶水而已，套用本人图书后记中的一句话“本人一直在努力地抬头仰望，却发现视线所及之处，才仅仅是Excel的半山腰“。

正文
怎么才算精通Excel？能编制多层嵌套非常复杂的公式，就是精通Excel吗？能编写一段VBA代码，用代码实现Excel没有的功能，就是精通Excel吗？
首先，我们先来看三张喝饮料的图片，看看Excel菜鸟是什么样，普通Excel用户是什么样，精通Excel的人又是什么样。

然后，我们再来看二个例子，
第一个：
请统计下表中图书销售数量、金额的合计。

凡对Excel的脾气有点了解的人都知道，用于计算的数字不能和文字混搭，否则会造成Excel计算的脑梗塞。但是当没摸清Excel脾气的新手们，在“没有困难制造困难也要上”提出这样的问题时，一些Excel高手不是指导规劝新手们从规范做起，而是列出诸如下面的公式，我们能说这些高手们是真的高手吗？
=SUMPRODUCT(SUBSTITUTE(D4:D8,"元","")*1)

第二个：
又比如，表哥龙逸凡要对销售员的销售情况做一个登记台账，他做出的销售台账如下图：

由于表格设计不规范，如果要统计某产品的销售数量销售金额，不能使用SUMIF函数，龙逸凡编制的求和公式是复杂的数组公式，如下：

=SUM((LOOKUP(ROW(4:22),ROW(4:22)/(A4:A22>0),A4:A22)=K3)*D4:F22)
复制代码

此公式很复杂，思路也很巧妙，但是，我们能说龙逸凡精通Excel吗？
如果将上面的表格做成规范的清单式表格，那么用一个简单的条件求和函数SUMIF就能统计出来：

从上面的二个例子出发，谈谈我对“怎么才算精通Excel”的理解。
窃以为，我们至少要做到以下四点，才算得上精通Excel：

规范。要有规范的数据处理理念和操作；

广度。要全面掌握Excel的常用功能；

深度，深入研究过各种常用功能，对常用功能会拓展应用。

灵活，对已掌握的知识能融会贯通，会灵活运用。

下面分别说明。

一、规范：

要轻松驾驭Excel，把Excel发挥到极致，首先要做到规范。
不要以为Excel是任我们摆弄的玩偶，殊不知，它也是有脾气的。什么数据用什么格式、什么功能用什么表格布局，都要顺着它的性子，以从根源上简化数据的处理。只有让Excel顺心了，它才能充分发挥其洪荒之力为我们所用。如果没有正确的数据处理理念、做不到表格规范、数据规范，而是完全靠霸王硬上弓的粗暴技术编制高超精巧的公式来完成相关运算，其实是一种基本功不扎实的表现。
真正精通Excel的高手懂得“上医治未病，中医治欲病，下医治已病”的道理。只有将“规范”的理念贯穿始终，才能把表格玩得行云流水天马行空。障碍应消灭于萌芽之处，而不是任由其变异膨胀再想办法消灭。
孙子曰：“古之所以谓善战者，无智名，无勇功，胜于易胜也“。Excel，道理亦然。

二、广度：

Excel博大精深，不能玩转个十七八般武艺，好意思说自己是高手吗？咱们来自我评估一下吧：
基本功：
不但精通应用各种高级筛选、分类汇总、合并计算、数据透视表等常规的基本功能，对那些不怎么常用的功能如单变量求解、模拟运算表及分析工具库等加载项也能熟练应用，对新功能如、Power Pivot、Power View、Power BI也能及时学习并掌握。
函数技：
Excel中400多个函数除了那些专业函数没用过，其他的函数都能信手拈来。别说普通的函数嵌套组合应用，那怕是让普通用户感觉烧脑无比的复杂数组公式也能一次性成功编写出来。
图表诀：
不但熟练掌握常用的柱形图、折线图，饼图，能将这些图表的各种元素玩出各种花样，组合出新颖而又贴切的图表，还能使用熟练应用不太常用的散点图、曲面图等，并能利用散点图作为辅助工具，绘制出复杂的图表。
VBA：
不但精通常见的VBA属性对象方法，还能熟练应用SQL、字典、ADO、API、类模块。什么？VBA落伍了？没什么，VSTO也已熟练掌握。
精通Excel的高手，知识面一定是广博的。遇到问题，能马上给出解决方法。精通Excel的高手，既要知道条条大路通罗马，更要知道在什么背景下选哪条路才更高效便捷。

三、深度

做为精通Excel的高手，Excel知识掌握得既精又深才是应有的标准。Excel很多看似“肤浅”的功能，其实都蕴藏着更为深邃的技法。
比如查找替换功能，只要使用过Excel的人，都会认为自己会使用此功能，但实际上，查找功能的一些选项或功能很多人都没用过，比如查找结果能排序。作为Excel高手应该能掌握常见功能的方方面面，并能将其放挥出别样的用途。

案例：下图的表格，工作表已保护，请批量清除C2:H23单元格区域的数字，而保留有灰色底色单元格的公式
由于表格已保护，无法使用定位功能，这时我们可以使用查找功能的通配符及查找结果排序功能，间接实现定位的定位常量、数字、公式等功能。利用查找功能批量选定常量（数字）单元格，从而批量将其清除。由于知乎不能传动图，具体操作演示，参见网易云课堂免费课程《查找替换居然还能这样用》
Excel偷懒的技术：查找替换居然还能这样用
做为精通Excel的高手，对函数来讲，要深入掌握各类函数的知识点，要知其然也知其所以然。比如深入理解SUMIF第三参数的定位原理，LOOKUP的查找原理。
SUMIF第三参数的定位原理：
SUMIF函数第三参数单元格区域起作用的就是左上角那个单元格，此单元格的作用是定位定点，只要有此定位点，SUMIF会自动以此单元格为原点，按照第一参数区域符合条件的单元格的坐标，找到同样坐标位置的单元格，并对其数值求和。
我们可以利用此特点，完成看似难以完成的任务
SUMIF函数应用案例1：错行求和

SUMIF函数应用案例2：错行求和

SUMIF函数应用案例3：错列求和

LOOKUP函数查找原理
LOOKUP函数是运用二分法，在已经升序排列的数组里查找目标值，如果数组的中间位置的值且小于等于查找值，它会继续按二分法进行查找，直到查找到相等的数或查找完“按规则应该查找的位置”（不是查找完所有的值），如果还找不到相等的数，就返回结束查找前最近一个符合条件的值。如果有多个符合条件，则返回最后一个符合条件的值。
关于LOOKUP查找原理与方法，就不举例说明了，大家可参见龙逸凡的博文《深入理解LOOKUP：LOOKUP函数的查找原理》查看详细解释及实例，链接：
深入理解LOOKUP：LOOKUP函数的查找原理

案例：又比如，要统计下表某片区1－6月的销售额，该用什么公式呢？

网上的文章给的几个公式是这样的：
高手公式：

=SUMPRODUCT(SUMIF(OFFSET(A2:A9,,ROW(2:7)*0),A12,OFFSET(A2:A9,,ROW(2:7))))
复制代码

高高手的公式

=SUMPRODUCT(SUBTOTAL(9,OFFSET(C1:H1,ROW(1:8),))*(A2:A9=A12))
复制代码

高高高手的公式

=SUMPRODUCT(MMULT(C2:H9,ROW(2:7)^0)*(A2:A9=A12))
复制代码

实际上，用不着这么复杂，只需一个SUMPRODUCT函数就可解决：

=SUMPRODUCT((A2:A9=A12)*C2:H9)
复制代码

四、灵活

精通Excel的人，会灵活运用常规的功能、函数，能将常规功能发挥出别样的用途，能将普通的函数进行组合出令人拍案叫绝的用法。

案例1：比如上文中的运用查找功能实现定位功能，

案例2：使用“填充－两端对齐”和定位功能提取字符串中的数字
解题思路：先将A列宽度设为小于一个字符，然后选定A列，“填充－两端对齐”，分离出A列的数字和汉字，再用定位功能删除非数字字符的单元格，即可提取出各单格中的数字。

案例3：不使用函数，在练习题027工作表的B3:C9单元格引用sheet1表格中各人员的实际金额，要求批量操作。
本题实际上是间行引用，故不能直接下拉填充，习惯了函数公式的高手，习惯性的会使用OFFSET、INDEX等函数，实际上这道题，只需使用简单的功能就可实现：

方法一：
在本工作表B3，B4单元格输入字符串Sheet1!B3、Sheet1!B5，C列类同，然后下拉填充，再用查找替换将Sheet替换为＝Sheet
方法二：
在B3输入公式=Sheet1!B3、B5输入公式=Sheet1!B5，C列类同，下拉填充，然后定位－空值，删除空白单元格，下面单元格上移，
方法三：
筛选，复制－选择性粘贴（链接），要操作二次。

案例4：要对下图的各部门进行小计求和，
一般的用户只会一个部门一个部门的设置求和区域，实际上如果灵活应用sum公式，选定E2：15单元格，然后输入下面的公式也可以批量求和。

=SUM(D2:$D$15)-SUM(E3:$E$15)
复制代码

这个公式巧妙的应用了“借用”的思想－借用了后面单元格的计算结果。如果只是录入录入单元格，那公式计算结果是错的。此思路非常巧妙！

案例5：批量向下求和的公式
我们知道，要批量输入向上求和的公式，只要先使用F5快捷键定位空白单元格，然后Alt+=就可批量输入向上批量求和的公式，但要批量向下求和似乎是个难以完成的任务。实际上我们使用常见的subtotal和sum函数，结合“借用”的思维，巧妙的实现了向下求和。

对真正的剑道高手来说，飞花摘叶皆可伤人，草木竹石均可为剑，甚至可以妙渗造化，剑在心中，无剑无我，无迹可寻，无坚不摧！
对真正的Excel高手来说，Excel问题的解决方案，俯拾皆是，顺手拈来。手中无招，心中却有招，无往不至！无敌天下！

作者：龙逸凡
链接：https://www.zhihu.com/question/24277854/answer/116702513
来源：知乎
著作权归作者所有，转载请联系作者获得授权。