Excel 进阶指南：分享 3 种脑洞大开的链接技巧

如果把Excel比作武侠小说中的「剑」，那么我们大致可以将Excel使用者分为以下几个等级：

Level 1 剑客：

小说中常见的劫匪甲乙丙，会照着剑谱比划几种简单的招式，对剑的理解只停留在表面的「形」上。

在此级别的Excel使用者对Excel的基本功能已经有所了解，但还不熟，还没有达到灵活高效地应用Excel解决业务问题的程度。

Level 2 剑侠：

例如金庸小说《笑傲江湖》中五岳剑派盟主左冷禅，不仅熟练掌握大量剑招还能够对传统剑招进行改良优化，在「形」上已经是不争的高手，但对「剑道」的参悟程度不高。

此级别的Excel使用者经过大量的工作经验积累，已经具备高快好省地利用Excel各种功能解决业务问题的水平，是Excel使用上的高手。但还不具备随心所欲「玩转」Excel的能力，对某些方法停留在会用但不清楚为什么这么用的状态。

Level 3 剑圣：

令狐冲、西门吹雪、叶孤城等级别人物，此三人不仅精于剑招，而且已参悟“剑道”，已经达到“心中有招却已忘招”的境地。

此级别的Excel使用者能够按照个人喜好随心所欲地驾驭Excel，他们有能力将Excel「玩」到不像Excel的程度。与Level2级别最大的区别在于，他们已经参悟到Excel中的「神」—— 即Excel中链接的逻辑，并将此逻辑与已掌握的Excel技能相结合，从而达到了在Excel中「形神合一」的境地。

本文的主要目的就是帮助大家理解「链接的逻辑」，为大家突破Excel使用瓶颈提供线索。

Level 4 剑神：

独孤求败、张三丰、《越女剑》中的阿青等传说级人物，此级别剑神的御剑水平已经远远超出了平常人的认知。

作者心中的这些Excel神级人物应该是这样的：他们应该对Excel的每个细节都烂熟于胸，他们应该从开发Excel的底层代码中了解了构成Excel各个对象的算法逻辑，他们应该能把Excel拆了然后再按照自己喜好将需要的组件重新拼凑起来，或许某天他们会在Excel上开发出一套微型Windows系统也说不定。

         Lv2 - Lv3 进阶要点：

如何理解Excel中「链接的逻辑」

想要让自己的Excel水平有所突破，从Lv2升级到Lv3，最为关键的是要理解的是Excel中「链接」的逻辑，那么Excel中链接的逻辑究竟是什么呢？

在回答这个问题之前，我们先来了解一下Excel的构成：

下图的马赛克画是由无数个具有不同颜色、不同大小以及不同形状的马赛克颗粒按照一定排列顺序构成的。一个Excel工作表也是由多个具有不同大小、不同填充色、不同数值的单元格构成的。

  

从Excel构成的角度讲，我们将单元格称为「单元格对象」。

在Excel文件中除了「单元格对象」外，还有工作表、工作簿、图表、图片、形状、切片器、透视表/透视图、表格控件等等多种不同种类的对象。

每个对象都是相对独立的，只有让不同的对象与对象间拥有互相参照彼此信息的能力，才能让不同对象结合在一起成为一个整体。

这种对象与对象间相互参照信息的能力就是本文要为大家介绍的Excel最为重要的逻辑——不同对象间的「链接」。

在Excel中，在不考虑VBA编程的情况下主要通过两种渠道实现不同对象间信息传递的任务，第一种渠道是「公式」，第二种渠道是「名称定义」。除此之外，还可以利用切片器、图片链接等形式在不同对象间创建链接。

举个最简单的例子，在“B2”单元格中输入公式“=A1”，就可以将B2单元格对象与A1单元格对象链接在一起，完成将A1单元格中的值传递到B2单元格中的任务。

分享 3 种脑洞大开的链接技巧

看到这里有读者可能会想：前边玄乎其玄的说了半天原来这就叫链接啊，这谁不会啊，上当了！不过先别急着下结论，接下来要为大家介绍3种脑洞大开的链接技巧，不知你是否还了解呢？

链接技巧进阶 1 —— 单元格与图片的链接

如果能够把单元格区域中的显示内容实时的反映在图片中，就可以随心所欲地安排展示空间、改变展示布局及效果，利用此功能可以在Excel上绘制美观的仪表盘。

方法：选中单元格区域后Ctrl+C – 使用“链接的图片”粘贴方式进行粘贴，这样粘贴后的图片就可以动态参照单元格区域显示信息，当单元格区域内的信息发生变化时，图片也会发生变化。

使用此类链接方式可以轻松地将类似下图的表格信息重排版成杜邦分析的树形结构图：

表格数据

 

整理后的杜邦树形结构图

 

链接技巧进阶 2 —— 单元格区域、图表与控件间的链接：

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

 

创建链接的逻辑如下图所示：

方法：

1.先创建合适的控件并指定控件的参考区域与返回值

2.参照控件返回值，使用OFFSET或类似可以返回单元格区域数据的函数将图表数据源所需要的数据进行封装

3.将第二步骤中创建的函数指定为某个名称定义

4.最后将图表的数据源指定为定义好的名称

通过上述四个步骤便可以制作出类似以下动态图表的图表了：

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

 

链接技巧进阶 3 —— 单元格区域—图片—图表的链接

使用此类链接方式可以将两种不同种类的图表嵌套在一起，从而生成一个全新的图表，在全新的图表中会同时拥有两个图表的信息，能够为观测者带来更多的信息参考价值。

比如下图的环形图与折线图的嵌套图表就是基于此类链接方式来的。

上图的制作步骤为：

1. 先用原始数据生成主图的折线图与副图的环形图

2. 将副图环形图放在某个单元格区域内，用图片链接的粘贴方式生成此单元格区域内的图片

3. 将生成的副图图片复制粘贴到应在的主图折线图中的节点处

※ 为了自动重复以上步骤生成并粘贴副图表到每个主图表的相应节点处，上述案例中使用了简单的VBA程序

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

 

关于「链接」的逻辑，今天就先分享到这里，如果你还想了解更多操作的细节，或有实际工作中遇到的疑问想请教，诚意推荐本文作者李奇老师在网易云课堂的免费直播，现在报名还可以获得数据分析师（Excel+SQL）免费课程！

Part 1 ： 免费直播公开课

微软Excel MVP带你了解Excel中最为重要的逻辑 —「链接」

12月18日 周二 20:00

直播讲师

李奇  网易云课堂微专业特邀讲师

微软Excel MVP（最有价值专家） 

中国电子表格应用大会主席

直播大纲

1、从原理上理解Excel的构成

— Excel是无数对象构成的整体

— Excel对象与对象间可以互通有无

 

2、链接技巧进阶1：数据透视表与单元格的链接

— 用GetPivotdata函数动态引用数据透视表中汇总值

 

3、链接技巧进阶2：单元格与图片的链接

— 如何制作动态图片

 

4、链接技巧进阶3：单元格区域、图表与控件间的链接

— 如何制作控件动态图表

Part 2 ： 免费体验课

《数据分析师（Excel+SQL）》

课程大纲

1. 数据分析概述及使用工具介绍

2. MySQL部分加工处理

3. Power Query与Power Pivot的处理过程

4. 制作不同平台的分析展示界面

参与方式

扫描下方二维码，

加入网易云课堂「数据分析师（Excel+SQL）」社群

即可获取所有免费福利

为了保证学习体验

本次学习社群限时开放

数量有限，进群从速哦

点击阅读原文，预约直播。