今天为大家介绍怎样才算是精通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 暂不深表.
数据透视图和普通的图表几乎没什么不同, 只不过能和一个数据透视表彼此联通, 控制表的同时, 可以影响到图的展示内容. 不作为重点.
这一章通常来说, 最常用的还是 计算字段 和 计算项. 望勤为操演.
老衲痛感逝者如斯, 不舍昼夜, 因而决定提前讲说章五. 也就是乾坤大挪移心法.
请各位注视自己的 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).
第二步: 写代码, 炒回锅肉. 写完了之后点上面的三角箭头
第三步: 回锅肉装盘亮相:
够短平快吧?~~
从回锅肉到全世界, 中间的困难主要在于对各个食材 (对象, 类库) 的熟悉程度. 再往上则是编程思维(例如编程 Pattern). 因此老衲也不打算写太多了。
循序渐进, 必至大道.
VSTO (Visual Studio Tools for Office) 是 VBA 的升华版. 顾名思义, 这个 VSTO 是在 Visual Studio 里面使用的 (也就是老衲刚才说的全套顶级大厨厨房).
Visual Studio 作为微软诸神齐心协力创造的大神级 IDE, 在易用性上是无与伦比的.
不巧的是, 老衲的机器上没有安装 Visual Studio(C 盘空间不足, 残念 * 1024....... 主要原因是, 老衲并非开发人员.......)
好消息是, 如果少侠跟随老衲已经走到了这里, VSTO 也不会是什么难事.
讲解一下 Excel(2013) 的局限性, 如是我闻:
1. Excel(2013) 及之前, 大概占用普通 Windows 电脑内存的 25%-30%, 即容易崩溃, 或出现各种不稳定症状. 尤其是 32 位 Windows. 该数字出于老衲的经验. 因而一个大内存是很有必要的.
2. Excel(2013) 的一张工作表, 可以容纳 1,048,576 行 乘以 16,384 列. 那么如果少侠家资丰厚, 随便拿出两百万行数据怎么办? 请使用 SQL server express(express 版本是免费的) 来辅助进行数据预处理. Access 理论上也可行, 但是有失简陋, 所以老衲向来不用, 从而知之甚少.
3. 一个单元格能放 32,767 个字符.
4. 回退能退 100 步. 但使用了 VBA 就不好说了.
5. 一个下拉列表单里能放 10000 个选项.
6. 一个函数里面最多使用 255 个参数.
7. 公式里面的函数嵌套, 可以嵌套 64 层. (说 7 层的那是还没更新到 2013, 请加速更新)
8. 公式的长度不能超过 8192 字符.
9. 一个图表里最多可以放 255 个数据系列. (一般来说放二三十个就已经糊满了)
10. 面积图, 不能用平滑曲线. 需要使用别的方式实现.
11. 柱状图, 不能同时两个柱状的数据系列分别对应主坐标轴和次坐标轴. 需要变为一个柱状图, 一个折线图.
一愚之得, 不敢自专, 望各位看过之后, 或有裨益. 余心安矣.
祝各位在未来生活愉快, 工作顺利.
至此，怎样才算是精通Excel三部曲已经全部更新完结，各位少侠如有从头到尾梳理了一下自己对 Excel 的认识. 查漏补缺,必定能对 Excel 的理解又明彻几分.
想查看前两部分的同学看这里：
怎样才算是精通Excel | 第一部分：总纲、基础操作、函数/公式
