如何精通excel

作者：秦伟来源：知乎

卢俊多年前遇到李燕，获得了一份excel 5轮真经。其中excel心法共有5层，每层除心法外还有公式。卢俊对Excel知之甚少，但我认为只要努力练习，层层向前，直行到五楼就是Excel。

excel五轮深法：

总纲

章一.基本应用程序/Short Cut

张二。函数/公式

张三。图表/图形

张四。透视表

张五。VBA/VSTO

: excel限制和提示

总纲先贤有马，三生万物。可见万物都是数。雷蒙德珊珊雷欧兹云，维修清晰，但精密的唯一。Excel心法从复杂的数字开始，要去那个杂七杂八的渣滓一起剪。陆续密经室委，收集，收集成正方形。然后制作图表，画出文酿酒类，才能展示人。心法高的地方叫VBA，千变万化，话不能说尽其妙。

下图是雷欧兹(Ray Ozzie)、excel大法开山始祖、比尔文、鲍尔默、雷蒙德三星。

翻译：

excel工作的第一步是清理数据，清除不符合规格的脏数据，将数据调整为整洁合理的格式。然后添加适当的数据辅助列以补充数据维。最后是用漂亮的图片/表格给别人看处理过的数据。最高级的功能称为VBA。VBA的使用非常灵活、有力，不能说几句话。

excel技术有很多种。首先给施主一个直观的印象。通常在二级菜单上停止。重点功能狍子用星号标记了：

章1。基本操作/Shortcuts基本操作的入门法律文本是数据整理

原始数据一般是这样生长的。

这是一个不好的数据样本，但还不是最差的。从别人手里收集原始数据时，这种情况尤为常见。

好的数据格式是：

世界上的任何努力都是肤浅的，按部就班地进行，数据整理是其中最基本、最重要的入门手法。但是入门手法往往意味着辛苦，没有捷径，只懂手。

数据整理式：清洗

吴宗欣寿和尚把云：当作菩提树，心像明镜台，经常勤擦。不要让灰尘产生。

心要擦，同样，数字要洗。熨烫整齐，有利于后续使用。

清洁大象，简称脏数据。一般情况下，

1.同名异物：例如，公司内有两个耳鸣，如果不加区别地导入数据并合并统计数据，可能会出现问题。

2.动物名：(如性别)，有人用男女写，有人用M/F写，有人干脆用0/1写。

3.单位落地是：例如，金额、人民币和美元混合在一起，那绝对是灾难

4.规格不符合：例如，身份证号码是9527。

5.形式混乱：最典型的是日期！例如，10/6/11、11年10月6日、11年6月10日或10年6月11日，因为美式日期、英式日期和中式日期各不相同！

老衲生平目睹过的日期惨案足足有 12306 桩那么多! 另外一种是分位符. 美利坚的分位符是 "," 而欧罗巴诸国的分位符是 "." 如果是一位叫 Chateaubriand 的美国同事发过来一个数字 "123.456", 根本说不清楚这是一百挂零, 还是十万有余. 老衲生平目睹过的分位符惨案足足有 1024 桩那么多!

假使少侠天资过人, 心如明镜, 眼疾手快, 刷刷刷刷检出来 "123.456" 个脏数据, 怎么处理呢?

数据整理之承式: 规制

做数据之前, 先要和其他人协商好, 各个数据都是什么格式, 不同数据表之间的格式是否要统一, 之间是否有依赖关系. 如果数据不满足依赖关系如何处理.

例如先约定好, 性别一律写成 "男 / 女". 如果写成 M/F 的, 那么 M 就当成男性, F 就当成女性来处理 (使用替换, 或者使用中间映射表). 写成 Nan/Ny 的, 直接当脏数据抛弃掉.

数据整理之转式: 分组

在数据预处理中, 分组是一个很重要的手段, 例如各位少侠要面对的是本公司的工资表, 想看看整体是否失衡, 可以将资历分为中低高三组 (日企), 对应人员的工资进行汇总; 但具体资历分层的节点的把握, 则需要小心, 必要时还需要反复尝试. 例如可分成

a. 工作 1 年以下,

b. 工作 1 年 – 3 年

c. 工作 3 年 – 5 年

d. 工作 5 年以上

跑出来一看, wow, 公司是大学生创业基金支援的, 全部员工都是工作 1 年以下……

这个时候就得按更细粒度的月来进行划分了.

数据整理之合式: 聚类

聚类则更灵活, 例如最早登记报册的只有员工的姓名工号, 乱糟糟一大把, 业余活动组织不起来怎么办?

这个时候找 IT 要一下各人上班的时候的浏览网页, 从网页记录推算一下各人爱好, 然后按照爱好进行聚类, 变成篮球俱乐部, 羽毛球娱乐部, DOTA 俱乐部….. 这以后的工作就好开展了.

上述四种, 强调意识, 不限于方法.

老衲一生中, 对于 Excel 最喜欢的功能是表格格式, 不单单是因为美观整齐, 表格格式还集成了筛选, 排序, 甚至冻结窗格的功能, 叫表格格式这么平淡的名字, 若依老衲, 应该起名叫 "般若波罗蜜多" 格

在表格格式内别有乾坤:

然后说说神鬼莫测七招式: 数据工具

先说说分列. 这种密集大魔王造出的数据, 大家想必都很常见吧. 贴到 Excel 里面还依然是密密麻麻令人头晕目眩.

只需将之选中, 使用 "分列" 式:

Duang(此处应该有掌声)

接下来是 " 速填 " 式, 说实话, 这是个相对比较无聊的招式. 如果功力达到第二层, 使用公式函数, 取而代之乃是易如反掌.

不过既然至此, 老衲继续用上面的例子:

从 "代码缩略" 下面一直选到底, 点击 "快速填充" . Duang:

删除重复项实而不华, 特别推荐. 非常简单, 不赘述.

数据验证意正身正, 实用, 略繁琐, 多人协作时很推荐. 不赘述.

合并计算和 "快速填充" 有点相似, 如果功力递进到第四层, 使用数据透视表来取代之简直是易如反掌. 且便捷不止十倍. 先不详述.

模拟分析是堪称第一层的屠龙绝技, 看似厉害, 其实用处很小. 而且这个模拟分析和公式 / 函数关联极大, 留待第二章详述.

关系则是一种高不成低不就的招式. 不能说没用, 但它的功能呢, 其一可以使用辅助列取代, 其二可以使用 VLOOKUP 取代, 其三可以使用数据库取代. 等说到数据透视表的时候再详述不迟.

中级法门是数据导入, 早期数据清洗什么的, 早在入库前就已经有人替你完成了, 便当之至:

连个 SQL server 看看:

能练到这一级, 少侠, 你功力已然不浅了. 想必 SQL 这种中等功夫你也有过粗练.

至于 MDX 这种小无相功, 功力也不在 Excel 五轮心经之下, 但修习全凭机缘, 老衲先不多讲了.

注意事项:

Excel 初级法门中有一道奇毒, 名为 "合并单元格", 想老衲数度往生极乐, 多缘于 "合并单元格" 之手. 作为原始数据, 尽量不要使用 "合并单元格", 这个功能在后续处理数据的时候会带来大量的麻烦. 足以令人呕血三升. "合并单元格" 一般是在最后一步, 确定数据不再修改的时候才可以使用.

另外和别的人协作处理数据的时候, 最好将处理好的原始数据和呈现数据一起提交给他人, 方便他人未来进一步修改.

Shortcuts 是用来省时间的, 相比来说只能算是小技巧. 最好的参考资料就是微软的官方说明书.

至于哪些 Shortcuts 比较重要, 个人觉得是单元格位置的操作, 能避免在万千数据里频繁地拖动滚动条.

章二. 函数 / 公式

只要在上方公式区输入的, 等号之后的内容, 都属于公式 (绿色). 而函数 (红色), 则是后面带一对括号的那些内容.

这一节的初级要点是熟悉 Excel 现有的函数库

其中比较普世的是以下四类:

数量不多, 经常去 [该网站因为政策法规不予展示] 上搜一下, 就知道用法了.

这个常用函数里面有一个人气堪比 AKB48 的, 那就是 VLOOKUP(以及他的妹妹HLOOKUP)

VLOOKUP 其实就是建立两个表的关联, 将 B 表的内容, 自动导入到 A 表:

以下是用法详解：

中级要点是如何将这么多函数做成一个复杂的公式.

复杂的公式, 核心就是函数的嵌套, 函数里面套函数, 招中有招, 直至八八六十四招. 如长江大河, 滔滔不绝. 函数的嵌套最多可以套 64 层 (Excel 2013, 从前 Excel 2003-2007 为最多套 7 层).

下图这个例子就是一个简单的多层嵌套, 主要是 if 逻辑上的嵌套.

不过心有五蕴, 人有三昧, 简言之就是地球人还没有进化成三体星人的形态, 嵌套的数量一多, 就会令人心毒盛起, 头晕目眩, 前列腺紧张, 根本看不清楚自己在写什么.

臣子恨, 何时雪?

怎么办? 中间列!

这回老衲举一个实用的例子, 个税计算:

正统的个税计算算法是这样的:

写成公式是这样的:

把公式摘出来给各位欣赏一下:

=IF([月工资]-3500<=0,0,IF([月工资]-3500<=1500,([月工资]-3500)*0.03,IF([月工资]-3500<=4500,([月工资]-3500)*0.1-105,IF([月工资]-3500<=9000,([月工资]-3500)*0.2-555,IF([月工资]-3500<=35000,([月工资]-3500)*0.25-1005,IF([月工资]-3500<=55000,([月工资]-3500)*0.3-2755,IF([月工资]-3500<=80000,([月工资]-3500)*0.35-5505,IF([月资]-3500>80000,([月工资]-3500)*0.45-13505,0))))))))

看到这个公式是不是感到口干舌燥, 头晕目眩, 前列腺紧张?

但如果使用中间列, 将公式拆解, 每个子部分做成一列, 就会立刻神清气爽:

最后将不需要的列隐藏起来, Mission complete~

然后该说说模拟运算了.

公式可以拖动, 其中参考的单元格在拖动的时候位置也会变动. 下图就是老衲正在拖数据的瞬间:

一松手:

这种拖数据, 虽然很简便, 但也有一个问题, 就是只能向着一个方向拖, 或上或下, 或左或右. 假设现在有一个数据要求, 有两个变量, 相当于让你同时向下向右拖动怎么办?

例如不同利率, 不同年限下房贷的问题 (这真是一个令人悲伤的例子, 施主请看破红尘吧):

普通公式也可以做到, 但是就是需要向右拖 N 次, 或者向下拖 M 次. 等你拖好, 妹子已经下班, 和别人一起吃麻辣烫去了. 模拟运算则可以一下子把这个 6*6 的结果全算出来.

操作很繁琐, 接下来的内容请点赞, 给施主增加信心:先在左上角放一个本息合计公式:

然后选中所有的可变利率及可变年限:

然后选择 "模拟运算表"

点击确定之后就可以 Duang 了:

唉, 这果然是一个令人伤心的例子.

高级要点是如何自定义一个函数.

刚才的例子, 为了计算日期对应的季度, 使用了一个漫长的公式. 现在看看这个, 一个函数就直接命中靶心, 赏心悦目~

这个 Quarter 函数, 少侠的 Excel 里面是找不到的, 因为这是老衲自创. 它的真实面目是这样的：

没错, 这就是第五层心法乾坤大挪移第一级, 也就是 VBA.

此部分仅为精通excel的第一部分，后面还有两部分会陆续推送，干货值得收藏。