作者：靳伟 来源：知乎
今天为大家介绍怎样才算是精通Excel第三部分，也是最难的部分：数据透视表、VBA/VSTO
数据透视表
数据透视表 (
pivot table
) 这个] 翻译比较古怪. 不过名称不是大问题, 只要理解数据透视表能做什么即可.
数据透视表是一种简易报表, 可以对不同的数据行列进行数据汇总.
数据透视表的入口在此:
生成了 Pivot table 之后的主要控制区:
开始举例, 老衲这回还得请出王二和李明来:
此时前世孽缘来了, 一个自称是老板的人, 让你算一下李明和王二现在卖出的东西的平均价格是多少.
用公式 sumif 是可以实现的, 但现在有更好的办法来了.
选中这个表格, 插入数据透视表:
然后输入一个
计算字段
(计算字段 Calculated Field 是 Pivot table 中的重点功能, 要着重注意):
然后在右边拖一拖:
我们用 Sumif 核算一下
看来没什么问题 ^_^
这时候老板又发话了, 按照颜色和销售人员各统计一下总销售金额. (高达八成的老板都是这样) 怎么办呢? 很简单, 再拖一下:
就是这么方便, 就是这么任性!
老板继续发话, "你这么搞完全没有理解我身为老板的一片苦心! 我是让你制作两个表, 一个统计人员, 一个统计颜色, 然后给我一个过滤表单, 这样我可以按日期看人员和颜色的变化趋势."
这个说来很简单, 只需将 Pivot table 整个圈中, 复制黏贴, 然后改一下字段即可.
过滤项呢, 也是将字段拖入到筛选器即可:
但现在有两个 pivot table, 是否有方法同时操作两个 pivot table 呢?
有, 那就是
切片器
(从前也提起过哦)
选中一个 Pivot table, 添加一个切片器.
添加切片器之后, 右键选择 "报表连接", 继续添加连接的 pivot table, 两个都选中:
这样, 用这个切片器, 就可以达成一个切片器来控制多个 pivot table 的目标:
顺道说一下, "日程表" 也是切片器的一种, 只不过外观是特别优化过的罢了:
眼尖心细的少侠会发现, 在 PivotTable 操作中, 有几个选项一直是灰色的, 例如:
还有:
这是因为它们都需要特殊的奇门兵器和外道功夫:
老衲由于近来深研佛法三宝 (合称 PPT), 一时没有准备
OLAP
真经, 所以这节暂时跳过, 待机缘成熟再来补完.
至于
Power Pivot
, 各位可以直接视之为 "不服跑个分儿" 版的数据透视表. 严格点说, 它预期起到的作用是简易的数据库 (例如 Access), 而工作方式比较像数据透视表.
另外想要在 Excel 中突破一张表最多 100 万行的限制, 也得仰仗这位的大肚能容. 在 Power Pivot 中, 一张表的最大行数为 20 亿行.
但老衲还是认为, 如果必须应对上亿行的数据, 学习一下数据库 -- 例如 SQL server, Oracle, MySQL -- 是很有必要的, 好过使用这个 Power Pivot. 所以这个 Power Pivot 暂不深表.
数据透视图
和普通的图表几乎没什么不同, 只不过能和一个数据透视表彼此联通, 控制表的同时, 可以影响到图的展示内容. 不作为重点.
这一章通常来说, 最常用的还是
计算字段
和
计算项
. 望勤为操演.
VBA / VSTO
老衲痛感逝者如斯, 不舍昼夜, 因而决定提前讲说章五. 也就是乾坤大挪移心法.
请各位注视自己的 Excel, 是否能找到我神功入口?
找不到也正常, 毕竟是奇门秘籍, 一般都藏之名山大川, 幽谷白猿之中. 请从这里找寻:
"开发工具" 一定要选中才行.
这个里面常用的又是 "代码" 和 "控件"
举个栗子, 请各位看个大概:
1. 点击
录制宏:
然后对 Volume 列进行排序操作:
然后点击一下 "
停止录制
":
然后点击左侧的
Visual Basic
:
可以看到代码了:
Sub 宏4()'
ActiveWorkbook.Worksheets("K线图").ListObjects("表13").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("K线图").ListObjects("表13").Sort.SortFields.Add Key:= _
Range("表13[[#All],[Volume]]"), SortOn:=xlSortOnValues, Order:=xlDescending _
, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("K线图").ListObjects("表13").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
这就是 VBA 奥义最简单的例子.
各位少侠中, 可能有不少人第一次发现这个入口. 对于编程 (没错, 就是编程) 可能也不太了解, 所以有一些基本概念要澄清一下:
这些基本概念分别是:
对象, 属性与方法, 集合
.
因为老衲遁入空门前是一名光荣的人民厨师, 所以就用厨师来做一下比喻.
一个厨师, 就是一个对象. 一群厨师 (同类别的对象), 就是一个集合. 集合也是对象的一种. 像 "顺峰颐和园路东口分店后厨大师傅们" 就是一个集合, 也是一种对象.
我们来定义一个厨师:
Dim someone as Chef
现在在代码世界就有了一个叫 someone 的厨师了.
那此 Someone 有哪些属性呢? 例如身高 178cm, 体重 73kg, 月工资 15000 (顺峰店啊, 人均消费过千, 大师傅没有上万的薪水, 你们还愿意去吃吗?
身高 = someone.Height
'这时候身高 = 178, 前面这个 ' 号要注意, 这个是注释, 开头写上' 号的一行, 在程序眼里是不存在的, 只是方便奇行种程序猿 (达尔文在加拉戈帕斯群岛发现的哦) 来阅读的.
体重 = someone.Weight
'这时候体重 = 73
工资 = someone.Salary
'这时候工资 = 15000
那 "顺峰颐和园路东口分店后厨大师傅们" 也可以是一个对象, 为了举例方便, 大师傅集合就叫 Chefs.
Dim 顺峰颐和园路东口分店后厨大师傅们 as Chefs
那顺峰颐和园路东口分店后厨大师傅们 有哪些属性呢? 例如总人数 15 人, 每月工作时间 21.75 天
顺峰颐和园路东口分店后厨大师傅们. count = 15 (等于号, 表示是赋值操作, 把右边的值写到左边去, 前提是左边的东西可读写, 刚才这句话相当于为大师傅强行指派了一个总数, 当然通常来说, 这个 count 很可能是只读的)
顺峰颐和园路东口分店后厨大师傅们. WorkDays = 21.75
好, 对象, 集合, 属性三个都说过了, 那
方法
又是什么?
方法是对象能做出的行动.
例如 someone 这个对象, 本质上是一名光荣的人民厨师. 他能干什么呢?
someone.fries()'炒 / 炸
someone.boil()'煮
someone.stew()'炖
.....'英文能表达的动作太少了, 完全不适用于中式厨师啊
.....
那 "顺峰颐和园路东口分店后厨大师傅们" 这个对象呢?
顺峰颐和园路东口分店后厨大师傅们. work()
顺峰颐和园路东口分店后厨大师傅们. Dismiss()
.....
.....'还有扯淡吹水等动作就不逐一列举了.
有这些概念, 少侠基本就明白自己面对的是些什么东西了.
然后老衲还要以厨师工作举例, 说明一下语言 (VBA) 和 IDE 是什么.
编程都有语言, 语言本质上就是一种人类和机器相互沟通的工具, 人类告诉机器怎么来运作, 如何执行动作, 创作出猪肉料理来. 在厨师界, VBA 就是中式菜, C# 就是西式菜, Java 就是印尼菜. 不同的语言在思路上是很不一样的, 例如 Java 讲的就是普适口味, 不再众口难调; VBA 讲的就是快熟快上; Erlang 讲的就是明火多灶; 但是目标都是把饭做熟.
那 IDE(Integrated Development Environment) 是什么? 当然就是厨房啦. 有全套厨具, 灶台, 烟机, 还有一个宝贝哦: 全方位支持的速查菜谱.
Excel 自带一个 VBA 的 IDE, 虽说简陋得紧, 一副从大清朝穿越过来的样子, 不过好歹也算功能齐全, 聊可一用. (到了 VSTO 可就是使用全套顶级大厨厨房了, 保证乐不思蜀~)
这个厨房分为几个常用功能区:
菜单区: 这个是控制枢纽, 各个命令的总入口都在这里.
工程区: 在这里可以直观地管理 / 组织你的代码.
属性区: 对于在工程区选中的对象, 在属性区可以直观地显示该对象的各个属性.
代码区: 就是写入 VBA 并调试执行的地方
监视窗口: 对于运行中的对象, 我们想知道它在中间状态中的各个属性值变化, 可以通过这个窗口来观察. 如果自己做过牛排, 或许知道探针式温度计. 这两个道理很相近.
老衲说了这么许多基础知识, 各位施主恐怕已经昏昏欲睡了吧. 没事, 咱迅速炒个回锅肉, 大家精神一下:
第一步: 起火, 找一个新锅 (建一个新 Module).
第二步: 写代码, 炒回锅肉. 写完了之后点上面的三角箭头
第三步: 回锅肉装盘亮相:
