教你掌握Excel中最为重要的逻辑 —链接-CDA数据分析师官网

热线电话：13121318867

教你掌握Excel中最为重要的逻辑 —链接

2016-08-12

如果把Excel比作武侠小说中的“剑”，那么按照武侠小说中御“剑”能力的高低程度可以大致将剑客分为以下几个等级：

Level 1（剑客）：小说中常见的劫匪甲乙丙，此类剑客多是初入江湖，会照着剑谱比划几种简单的招式但缺乏对招式的理解及融会贯通，对剑的理解只停留在表面的“形”上。在此级别的Excel使用者对Excel的基本功能已经有所了解，但还不熟，还没有达到灵活高效地应用Excel解决业务问题的程度。
Level 2（剑侠）：例如金庸小说《笑傲江湖》中五岳剑派盟主左冷禅，左冷禅其实算得上是狠角色了，不仅熟练掌握大量剑招还能够对传统剑招进行改良优化，可以说在“形”上是不争的高手，但对“剑道”的参悟程度不高，没有达到“形”、“神”兼备的水平。此级别的Excel使用者经过大量的工作经验积累，已经具备高快好省地利用Excel各种功能解决业务问题的水平，是Excel使用上的高手，但还不具备随心所欲“玩转”Excel的能力，对某些方法停留在会用但不清楚为什么这么用的状态。
Level 3（剑圣）：令狐冲、西门吹雪、叶孤城等级别人物，虽然上述三人出于不同作者，御剑能力谁高谁低不好定论，但我们不难从金庸及古龙两位作者对此三人剑术的描述上得知此三人不仅精于剑招，而且已参悟“剑道”，已经达到“心中有招却已忘招”的境地。此级别剑圣可随机应变地将一个剑招演变成无数种御敌招式，用快准狠玄来描述他们的剑法最为恰当，所以和此级别剑圣过招一般胜败只在一瞬间。此级别的Excel使用者能够按照个人喜好随心所欲地驾驭Excel，他们有能力将Excel“玩”到不像Excel的程度。此级别的Excel使用者和Level 2级别的使用者最大的区别在于此级别的使用者已经参悟到Excel中的“神”—“Excel中链接的逻辑”，并将此逻辑与已掌握的Excel技能相结合，从而达到了在Excel中“形神合一”的境地。本文的主要目的就是帮助大家理解“链接的逻辑”，为大家突破Excel使用瓶颈提供线索。不过在进入正式内容之前，让我们再来了解一下Level 4，最高级别剑神的程度吧。
Level 4（剑神）：独孤求败、张三丰、《越女剑》中的阿青等传说级人物，独孤求败是草木竹石均可为剑、张三丰是太极剑法刚中带柔、阿青则创造了“三千越甲不可敌”的神话，此级别剑神的御剑水平已经远远超出了平常人的认知，此级别的Excel使用者作者也只能单屏想象去揣摩他们的能力。作者心中的这些Excel神级人物应该是这样的：他们应该对Excel的每个细节都烂熟于胸，他们应该从开发Excel的底层代码中了解了构成Excel各个对象的算法逻辑，他们应该能把Excel拆了然后再按照自己喜好将需要的组件重新拼凑起来，或许某天他们会在Excel上开发出一套微型Windows系统也说不定。

通过上文我们了解了当Level 2的Excel使用者想要升级到Level 3时，最需要理解的是Excel中“链接”的逻辑，那么Excel中链接的逻辑究竟是什么呢？在回答这个问题之前，我们先来了解一下Excel的构成，像下边的马赛克画是由无数个具有不同颜色、不同大小以及不同形状的马赛克颗粒按照一定排列顺序构成的一样。

一个Excel工作表也是由多个具有不同大小、不同填充色、不同数值的单元格构成的。从Excel构成的角度讲，我们将单元格称为“单元格对象”。在Excel文件中除了“单元格对象”外，还有工作表、工作簿、图表、图片、形状、切片器、透视表/透视图、表格控件等等多种不同种类的对象，每个对象都是相对独立的，只有让不同的对象与对象间拥有互相参照彼此信息的能力，才能让不同对象结合在一起成为一个整体。这种对象与对象间相互参照信息的能力就是本文要为大家介绍的Excel最为重要的逻辑—不同对象间的“链接”。

在Excel中，在不考虑VBA编程的情况下主要通过两种渠道实现不同对象间信息传递的任务，第一种渠道是“公式”，第二种渠道是“名称定义”。除此之外，还可以利用切片器、图片链接等形式在不同对象间创建链接。最简单的例子，在“B2”单元格中输入公式“=A1”，就可以将B2单元格对象与A1单元格对象链接在一起，完成将A1单元格中的值传递到B2单元格中的任务。看到这里有读者可能会想“前边玄乎其玄的说了半天原来这就叫链接啊，这谁不会啊，上当了”。不过请先别急着下结论，接下来要为大家介绍的几种脑洞大开的链接技巧你是否还了解呢？

链接技巧进阶1 数据透视表与单元格的链接：
如果有这么一种方法，可以将数据透视表中的值引用到单元格中，那么将省去编写及修改SUMIF、SUMPRODUCTOR等条件汇总公式的麻烦，同时还能减少因使用条件汇总类函数造成的计算负担。在“数据透视表对象”与“单元格对象”间的链接渠道是GETPIVOTDATA函数，GETPIVOTDATA函数用来返回存储在数据透视表中的数据，其函数表达式为：
GETPIVOTDATA（data_field,pivot_table,field1,item1,field2,item2, …）
data_field: 要引用的数据透视表中值字段的字段名
Pivot_table: 数据透视表的起始单元格地址
Field1,Item1: 要引用值在数据透视表中的条件

请参照以下截图中信息了解GETPIVOTDATA函数的使用方法：

链接技巧进阶2 单元格与图片的链接：
如果能够把单元格区域中的显示内容实时的反映在图片中，就可以随心所欲地安排展示空间、改变展示布局及效果，利用此功能可以在Excel上绘制美观的仪表盘。
方法是选中单元格区域后Ctrl+C – 使用“链接的图片”粘贴方式进行粘贴，这样粘贴后的图片就可以动态参照单元格区域显示信息，当单元格区域内的信息发生变化时，图片也会发生变化。使用此类链接方式可以轻松地将类似下图的表格信息重排版成杜邦分析的树形结构图：

表格数据

整理后的杜邦树形结构图

链接技巧进阶3 单元格区域、图表与控件间的链接：
通过公式及名称定义，可以将单元格区域的数据与图表、控件关联在一起，当改变控件选项时图表数据源所参照的单元格区域也会随之改变，从而达到图表的动态展示效果。

创建链接的逻辑如上图所示：
1. 先创建合适的控件并指定控件的参考区域与返回值
2. 参照控件返回值，使用OFFSET或类似可以返回单元格区域数据的函数将图表数据源所需要的数据进行封装
3. 将第二步骤中创建的函数指定为某个名称定义
4. 最后将图表的数据源指定为定义好的名称
通过上述四个步骤便可以制作出类似以下动态图表的图表了。

当改变上图控件中的销售人员选项时，图表会自动显示与新选项一致的结果。

链接技巧进阶4 图表--单元格区域--图片--图表的链接：
使用此类链接方式可以将两种不同种类的图表嵌套在一起，从而生成一个全新的图表，在全新的图表中会同时拥有两个图表的信息，能够为观测者带来更多的信息参考价值。比如下图的环形图与折线图的嵌套图表就是基于依据此类链接方式来的。

上图的制作步骤为：
1. 先用原始数据生成主图的折线图与副图的环形图
2. 将副图环形图放在某个单元格区域内，用图片链接的粘贴方式生成此单元格区域内的图片
3. 将生成的副图图片复制粘贴到应在的主图折线图中的节点处
※为了自动重复以上步骤生成并粘贴副图表到每个主图表的相应节点处，上述案例中使用了简单的VBA程序

使用此类链接不仅可以制作上述折线图与环形图（或饼图）的嵌套图表，还可以制作如在地图上添加柱形图、条形图，在散点图中添加饼图等等嵌套图表，有多少创意与需求就能制作出多少创新型图表。

此文作为《教你掌握Excel中最为重要的逻辑 —“链接”》系列文章中的第一部分，就先为大家介绍这么多内容。接下来还会陆续为大家介绍更多内容，希望感兴趣的朋友持续关注网易云的公众号。

以上所有相关技能以及更多的Excel商业智能及数据分析相关内容可以通过报名经管之家（原人大经济论坛）举办的CDA数据分析员课程：https://www.cda.cn/kecheng/2.html

亦可报名经管之家与网易云课堂共同推出的《数据分析师（初级）》微专业课程进行学习，
http://study.163.com/topics/data_analysis/

上述案例中提到的相关Excel文件以上传至QQ：1. 数据分析交流群-CDA（247547034） 2. 云课堂数据分析交流群（203386194）中，欢迎加群后下载文件。

另外，CDA也开设了微店商城，感兴趣的小伙伴欢迎微信扫码，直达店铺！