专栏名称: 数据化管理
零售业数据分析及研究者,【数据化管理】咨询顾问、培训师,自由职业。
目录
相关文章推荐
闹闹每日星运  ·  情人节的恩爱,全被这些星座秀了 ·  4 天前  
闹闹每日星运  ·  周运 | 闹闹12星座周运势:0217~0223 ·  昨天  
中国航务周刊  ·  我国又一全自动化集装箱码头,对外开放! ·  昨天  
中国航务周刊  ·  马士基与这家大货主,签署10年海运服务协议! ·  昨天  
海西晨报  ·  刚刚通知,停航!厦门…… ·  2 天前  
51好读  ›  专栏  ›  数据化管理

怎样才算是精通Excel | 第一部分:总纲、基础操作、函数/公式

数据化管理  · 公众号  ·  · 2017-06-04 08:05

正文


作者:靳伟    来源:知乎


老衲多年前遭遇异缘, 获得 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 表:








请到「今天看啥」查看全文