0 引子

20多年以前,我初出茅庐,进入单位。抱着一本excel书籍使劲看。后来也慢慢搞懂了什么是单元格格式、怎么写sum、average、countif、sumif公式等等。再后来,能够帮着单位的师傅解答一个个问题时,心里非常高兴。
之后,又学习了条件格式、数据有效性;学习了分类汇总,数据透视表等,再后来费了很长时间研究明白了vlookup函数,终于解决了自己工作中的难题,大大提高了“生产力”
最近又跟着伍昊先生的书籍:《你早该这么玩Excel(Wi-Fi版)》复习了一遍。现在虽然号称是大数据时代,但是我们很多日常分析的表格,excel还是可以做的。而且数据量再大,如果你没有系统的分析概念,也会无从下手。 (京东销售:你早该这么玩Excel(Wi-Fi版)
你要打磨一项技能,任何时候开始都不晚。

1 收获

1.1 核心就是以数据源为主的三表结构

这次学到的核心的收获,就是一个观念。即:源数据表,参数表,分类汇总表
而这三张表中,最基础的是源数据表。只有源数据表数据很规范,后面的数据透视表才能发挥强大的威力。源数据表中存放各类原始数据,每一列都要清晰,每一行也要容易填写,最好是让填表的人可以从左到右很容易的填写。更多相关的办法就是使用数据有效性结合参数表来制作下拉选项,甚至多级下拉列表。
参数表就是你表格中用到的基本不会改变的数据,例如省份名称、职称名称等等。
分类汇总表我们都可以通过数据透视表来很容易的得到,然后用于数据分析。

此外,还要去从业务的视角理解表格。
你可以试一下,分析如下描述,设计一个表格:
1、老板说,我想分析全国销售趋势。那么一张源数据表该包含哪些字段?
2、经理karen说,“我最主要是希望和供应商谈判的时候,对报价信息做到心中有数。我要知道同一家供应商不同时期的价格对比,以及不同供应商同一时期对同一种皮料的报价数据对比。”

1.2 面向业务的数据透视表

第二点值得分享的是数据透视表。如果我们数据源的表格,字段清晰、数据规范(简单点说,就是一列数据内,没有职称又填高工、又填高级工程师类似的问题),那么就可以很容易的通过数据透视表来分析。
如果你不知道该如何拖拽数据透视表的列和行,慢下来即可。伍昊先生举了个例子,他的一个朋友请教他,如果要在D列里面实现A列和B列两个数值的相加,然后再减去C列单元格的值,该如何写公式?他告诉朋友,你只要一边慢慢的说、一边做就好了。
其实很简单,慢下来,把你的文字翻译成为表格语言就好了。
数据透视表也是这样。例如:我想知道不同省份、不同会计类别、不同行业在不同营业状态下的全年营业收入总和。还要注意,为了表格容易被理解,列字段不要超过两个。即:要让表格站着,而不是躺着。

1.3 自动提醒的条件格式

作者举了几个例子,都很不错。一个是显示分数是否及格。快捷键Alt+O,D即可调出条件格式。 结合公式即可实现。另一个是标识三门课都大于85分的学生,其中用了公式countif。 练习题:你考虑一下,下图如何实现? enter image description here

1.4 规范数据源的数据有效性

我们下发的表格,很多时候返回来的数据很不规范,那么如何可以尽可能的让填写者返回规范数据呢?
数据有效性是一个不错的选择。它可以用来以列表形式提供输入下拉框,也可以规定整数或小数等等。配合公式,还可以做更多花样。例如根据前一列选择,返回不同的列表。
通过列表来实现下拉框输入,例如下图,选中C2单元格,然后按Alt+D,再加L快捷键,即可调出数据有效性对话框,在其中选择列表,按照图示输入,试一下看看C2单元格里面发生了什么变化吧。
enter image description here

下面是一个与公式结合,限定输入为特定格式的例子。试一下,看看你能不能实现类似的功能?引申一下,如果要限制输入身份证,该怎么办呢?
enter image description here

1.5 方便录入的快捷键

以前也用,但是不够那么认真。Ctrl+1调出单元格格式,Alt+D+P调出数据透视表,Ctrl+↓+→ 快速选中区域。 Alt+D+L,调出数据有效性。
Ctrl+·(左侧键盘波浪号一起的那个)用于显示单元格公式,可以用来显示公式,然后来验证数据有效性区域或条件格式里面的公式是否满足你的要求。

2 练习及示例文档

2.1 随书附带示例文档

因为书籍再版的原因,原书《你早该这么玩Excel(Wi-Fi版)》附带的链接已失效。后来从excelwu的新浪博客转帖的,这里是可以正常下载的链接: 下载资料:https://pan.baidu.com/s/1nwGy82X 密码:gmn2

2.2 微软官方示例文档

放在我的知识星球里面。

2.3 如何去找示例文档

前往bing搜索,“examples filetype:xlsx site:download.microsoft.com”即可查找合适的例子。有些是英文的。

3 数据相关文档

在作者的微信公众号:Excel伍老湿里面找到了一些不错的内容,就不转贴了,请自行搜索吧。

3.1 吴明辉:数据中的商机

3.2 吴明辉:如何打造智能时代的企业中台