作者：靳伟 来源：知乎
上篇为大家介绍了怎样才算是精通Excel第一部分，详情请点击：怎样才算是精通Excel | 第一部分：总纲、基础操作、函数/公式，今天为大家介绍怎样才算是精通Excel第二部分，也是最重要的部分：图/表。
各位善男子善女人久等. 老衲今天为各位解说五轮真经的第三层, 又称无上正等正觉图形图表经. 如是我闻:
图表经第一级: 表格
主要入口在这里:
也可以使用这个:
表格创建完成后, 点击表格中的内容, 会出现一个新的密法空间:
这些东西都是干嘛的呢?
首先是表名称. 子曰:“名不正, 则言不顺; 言不顺, 则事不成; 事不成, 则饮西北风". 达尔文在加拉帕戈斯群岛发现的奇行种生物程序猿, 对名称就非常关注, 程序猿对名称的关注主要是认为能方便后续使用. 更直观, 也不容易出错.
以上图出现过的公式距离
=VLOOKUP([对应级数], 个税速算表, 4,FALSE)
"个税速算表" 就是一个表格的名字, 这样的话选择范围就不是一个类似于 =D30:F37 这样很难记忆的字符串, 而变成一个非常容易理解的对象. 而 [对应级数] 这种列名也一目了然, 如果不加命名, 就得换成 = F22:F26, 还要考虑绝对地址和相对地址, 非常麻烦.
起个好名字就成功了一半哦~
切片器: 切片器诞生于 2010 年. 其实就是一种更美观的筛选.
那筛选又是什么:
筛选一共只有两路 18 式. 属于最简易的功夫, 但是日常防身非常实用, 希望各位有时间能多多操演, 不过没时间的话, 老板也会逼着你天天操演, 所以这个不必多解释.
此外再说一下表格的另外一个好处: 生成透视表特别方便.
如果是普通数据, 如果想要生成数据表, 必须全部选中:
但如果是表格的话, 随便选中表中任意一个单元格, 即可开始操作:
既然事已至此, 老衲顺道说一下条件格式这个惠而不费的功能, 自己使用还是给他人展示都非常美观:
下面做个集大成的演示 (哎呀呀, 老衲狗眼被晃瞎了, 看来只能明日再叙了)
图表经第二级：图表
图表主练手少阳三焦经, 内力不深, 招式繁多. 以老衲愚见, 这级偏向华而不实. 不过既然至此, 老衲依旧为诸位善男子善女人逐一解说.
图表共有十式, 常用者六, 不常用者四. 看起来招式并不多对吧?
但其中每一式下可能有若干变招, 故而常见的总数是: 52 式 (自定义式未计入)
常用图式, 可通过 Excel 上面的 tips 来理解 (将鼠标 hover 在某图式上就可以看到):
后面不太常用的四式可以稍微详细点说说.
散点图是展示两变量关系强弱的图形.
老衲举个蒸栗 (正例). 一个国家的人均寿命和该国家的人均 GDP 有没有关系呢?
找了一份 2014 年的公开数据 (不一定完全正确哦)
就用它作图 (注意老衲的手法, 千万注意, 否则图是画不出来的):
Duang:
能看出来点规律吗? 似乎不是很容易对吧. 喝! 目下才是真正显示手腕的时刻!
再看一遍:
看来钱可通神这四个字果然是有些道理.
刚才的例子也表明, 真正控制图表的, 不在图上, 而是在属性格式里.
那老衲再举一个例子, 民主指数和人均 GDP(购买力平价法) 之间的关系:
咦, 这个相关性就要差一些了. 有很多有钱的一点也不民主, 但民主的基本还算有钱.
散点图在老衲看来只是一个半成品, 它不能显示数据所属的 "系列", 结果就是上面这一大片圆点, 你是无法直观识别每个点是属于谁的.
这个时候就必须配合第三方标签工具来完成: 例如 XY Chart Labeler
顺道再说一句, 如果施主打算自己也写这么个 XY Chart Labeler, 功力需要达到第五层心法的第二级, 也就是 VSTO. 掌握了 VSTO, Excel 世界的大门就算正式打开了, 理论上就没有什么能够阻止施主了.
这是散点图的一种变招, 散点图只能选取两列, 而泡泡图必须选取三列, 第三列就是用来计算泡泡面积的, 继续用上面的数据做例子, 我们不但要看看民主指数和人均 GDP 的关系, 还得看看这个国家的总体量, 省得被一群小国忽悠:
这三列全都选中, 然后选择泡泡图:
结果如何, 各位不妨亲自试试 ^_^
雷达图主要是对两个 (多个) 对象的多个属性进行综合比较的时候使用. 一般来说越圆, 各项指标就越均衡; 圈的面积越大, 综合实力就越高.
注意事项: 属性值作为行 (hang), 对象作为列. 这样默认就能输出正确的雷达图. (样例数据来自汽车之家, 老衲不是车托)
好, 出图了:
如果数据写成了这样:
那默认出图就会是这样:
但其实也没啥问题. 这时候需要右键点击图片: 选择数据
然后切换一下行列就 OK 了
选择数据是非常基本而关键的知识点. 万望各位施主对此能提高重视.
曲面图主要是表现什么呢....... 老衲个人理解是.......... 这个主要表现 z = f''(f(x), f'(y) ) 这样的计算式 (这完全不是中文好吗?! 老衲也知道啊, 但是就是不知道中文怎么讲啊!!)
曲面图和 "模拟运算表" 可谓是天生的一对, 地造的一双. 什么? 你已经忘了什么叫 "模拟运算表"? 那其实也没啥关系, 只要你不是科研 / 教学人员, 那曲面图和模拟运算表在实际工作中, 遭遇到的概率小于万分之一.
不过行文至此, 图还是要上的 (背后的数据就是使用模拟运算表得来的).
是不是很酷炫? 还能 3D 旋转哦. 但这个图究竟能干啥呢? 让老衲吃包辣条再思考一下.......
顾名思义, 就是做出股价分析的图表. 但老衲生平从不炒股 (因为没钱), 都不知道这个东西怎么看.....
画一张还是可以的:
这张图对应的数据是这样的:
最多 5 列, 分别是交易量, 开盘价, 高点, 低点, 收盘价. 顺序不能随便改哦.
预祝炒股的各位同学今年好收成.
关于图表的干货终于要来了! 那就是怎么把图表变漂亮~ 追求美乃是人之天性, 老衲但做浅说.
请看老衲标记的部分, 再加上空白的大背景, 一般而言能控制的便是这些. 想要变漂亮就要从每一个标记点上做道场:
一旦开始对图表进行操作, 千万不要触碰快速布局和图表样式模板哦, 否则:
然后就要确定一个所谓美观的范例. 如果不知道猫长什么样, 笔下又怎么可能画出一只猫呢? 不知道漂亮的 Excel 图表是什么样子, 那即便对所有的操作无比精熟, 又怎么可能做出一张漂亮的 Excel 图表呢?
(A 同学默默地交给老衲一张图: 我觉得这张图很帅, 就要这样的吧)
老衲花了半天功夫, 终于找到了一个合适的示例: 电影票房火箭飞升, 虽然立意不高, 但是配色看着还挺喜庆~
老衲这里没有原始数据, 所以绘图不能 100% 复刻. 先把已有的数据列出来.
然后标记上对应的颜色 (需要使用第三方取色器, 老衲使用的是 QQ 截图......)
来画个八维太极圆环图吧 (饼图的变招)
好像哪里不对, 这和原图没有任何相似之处啊.
切换一下行列 (忘了如何切换行列的请往前翻):
好像有点意思了, 不过这也太丑了. 丑得让老衲又开始思考苦海无边, 回头是岸的道理了.....
但这就是 Excel 自带模板的真面目......
好, 图表美化经中真正的大招来了: 天罡地火 辅助列!!!
为了弥补 Excel 自带模板的先天不足, 制图时, 很可能需要添加一些数据, 来实现美观的效果.
原有数据 (提前切换了行列) 添加辅助列之后变成这样:
至于辅助列里面的数据怎么来的, 很遗憾地告诉大家, 是老衲随意编造的. 这个其实需要一些尝试和反复修改.
请看大图!
似乎更丑了.......
吃包辣条冷静了一下, 老衲觉得问题主要是图中每个环都瘦比飞燕, 如果变成杨玉环想必会漂亮得多. 那就来试试吧:
于是乎:
眼尖的同学是不是可以看出一点眉目了?
接下来就简单了:
1. 去掉每个环区的边框, 使其彼此紧凑. 2. 扇区起始角度顺时针移动 30 度. 3. 将辅助列的部分改成 "无填充"
填上一个底色让大家看清晰一些:
是不是有点意思了 ^_^
然后就是给每个环区赋上颜色. 看结果吧:
然后添加文本说明 (老衲只加一个示意吧, 迪塞尔的光头照请各位意会)
和原图比较一下:
是不是感觉有几分神似?
老衲又找到一个例子: 这是经济学人典型的红蓝配. 蓝色走渐变, 红色是点缀, 左上一枝红杏出墙, 待老衲做将来:
先分析一下要点:
可见这张图虽然很小巧紧凑, 乍看也无惊艳之处, 其实作者非常严谨, 功力至少达到了第四层.
现在编造一点数据准备复刻, 出图啦:
这个不用 DNA 鉴定了, 肯定不是同一个人生的, 呵呵.
首先要把进口数字乘以 - 1, 然后再绘图:
这回就顺眼多了, 以下就是以此修改刚才标出来的元素, 例如标题, 副标题, 图例等等.
现在有几分姿色了吧~
继续调整各项细节:
1. 移动标题位置, 更新字体
2. 插入矩形对象, 填充红色, 去除边框
3. 插入文本框, 输入文本, 生成副标题
4. 调整图例位置, 宽度及字体字号
5. 将左边数据轴移除
6. 在 Balance 上添加次数据轴, 并调整上限下限和间隔单位. 右侧次坐标轴字体字号调整
7. 调整柱状图的数据系列的分类间距 (219% -> 70%)
8. 在源数据上改动年份写法
9. 添加数据来源, 数据备注.
10. 添加一条线, 置为红色, 拖到 0 的位置上.
好, 各位施主请看!
各位可以和原图比较一下, 看看是否相似.
图表美化的价值, 在老衲看来并不很大. 因为小公司一般不要求美化效果, 大公司反而有专人 (美工) 支持. 故而此事上, 知晓大概, 不求甚解就好了.
第三层表格 / 图表的主要内容都说得差不多了. 迷你图很简单, 一试便知. 今天剩下的时间, 就讲讲第三层另一个屠龙神技吧: 般若白象功 Power View.
使用这个功能, 能生成所谓的动态图表, 怎么个动态法呢?
就是选中某个数据系列或者筛选项时, 对应的数据系列会变色 / 高亮.
还有就是使用一个切片器 (还记得这是什么吗?) 可以同时控制多个图表 (即所谓的联动)
当然如果真的有这种需求存在, 那 Power View 应该还是有点价值的. 在老衲的生涯里, 这种需求非但极少, 而且均属锦上添花的作用, 最后都是用 VBA 解决的......
另外一个略有用的功能就是, Power View 能根据地名自动绑定 Bing 地图.
老衲伪造了一份美国各州人口表 (使用了 Randbetween 函数):
将这两列选中后, 点击 "Power View", 这个时候就可以去泡咖啡了.
大概两分钟吧, 终于生成了一个全新的工作表 (Sheet):
这个还是挺方便的.
但是老衲从前见过的地图, 一般要求画成这样 (这个老衲当年也是用 VBA 解决的):
所以老衲称之为屠龙神技, 还是有自己的道理的. 顺便一句, PowerView 的功能, 是用 silverlight 实现的 (可以理解成微软家山寨 flash), 而 silverlight 已经被微软判了死刑........ 春草碧色, 春水渌波, 送君黄泉, 伤如之何, 伤如之何, 阿弥陀佛........
