作者：靳伟 来源：知乎
老衲多年前遭遇异缘, 获得 Excel 五轮真经一部, 其中 Excel 心法共有五层, 每层除了心法之外还有招式. 老衲对 Excel 虽然所知甚少, 但觉得只要苦练心法, 层层递进, 直达第五层, 应该就算是精通 Excel 了.
(不过根据老衲多年以来面试经验, 一般自称精通 Excel 的, 大多都是初入江湖的小朋友, 功力一般在一二层之间. 此外, Excel 是数据处理工具, 用 Excel 打游戏或绘画者, 虽然可能是豪侠巨擘, 但我觉得不应该作为精通 Excel 这件事的参照系.)
Excel 五轮心法:
总纲
章一. 基础应用 / Short Cut
章二. 函数 / 公式
章三. 图表 / 图形
章四. 数据透视表
章五. VBA / VSTO
跋: Excel 局限性和小技巧
先贤有言, 三生万物. 可见万物皆数也. 雷蒙三圣雷奥兹云, 数理昭昭, 惟精惟一. Excel 心法, 以纷繁之数始, 需去其杂芜糟粕, 截之以齐; 续而密经实纬, 攒之以方; 再而形诸图表, 文饰藻绘, 方可示人. 至于心法高处, 名为 VBA, 千变万化, 言语不可尽其妙也.
下图就是雷奥兹 (Ray Ozzie), Excel 大法开山祖师, 和比尔门, 鲍尔默并称雷蒙三圣.
翻译一下:
Excel 操作上, 第一步是对数据进行清洗, 去除不合规格的脏数据, 将数据调整成整齐合理的格式. 然后添适宜的数据辅助列, 补充数据维度. 最后是将处理好的数据以美观的图 / 表形式向他人展示. 最高级的功能叫做 VBA, VBA 的使用非常灵活强大, 不是几句话能说清楚的.
Excel 招式繁多, 先给各位施主一个直观印象. 一般止于二级菜单. 重点功能老衲用星号标记:
基础操作中的入门法门是数据整理. 这个是最基本的柱础, 腰马合一, 力从地起. 但很不幸的, 大多号称精通 Excel 的少侠们尚未具备这个意识.
原始数据一般都长成这样.
这是个糟糕的数据样本, 但是还不是最糟的. 从不同的人手里收集原始数据的时候, 这种情况特别常见.
好的数据格式是:
世间任何功夫都是由浅入深, 循序渐进, 数据整理就是其中最基本最重要的入门招式. 不过入门招式, 往往也意味着很辛苦, 别无捷径, 唯手熟心细尔.
数据整理之起式: 清洗
吾宗神秀大师有云: 身是菩提树，心如明镜台，时时勤拂拭，莫使有尘埃.
心需拂拭, 同理, 数需清洗. 使其平熨齐整, 利于后续使用.
清洗的对象, 简称脏数据. 一般有如下几种情况:
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的第一部分，后面还有两部分会陆续推送，干货值得收藏。
