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