初学者对自己信心满满是好事，但有时会过于乐观，前一分钟还不知道数据透视表为何物，下一分钟就说自己精通了。

来，这里有4个数据透视表，如何通过筛选业务人员，实现联动更新数据？

《只需两招让你的表格瞬间高大上》看过这篇文章的读者，立马想到了这不就是使用数据透视表+切片器完成的吗？

这个太简单了，分分钟搞定。

但你真正动手的时候，做出来的是这样子。切片器只显示一列，而且筛选的时候，只有一个数据透视表变动而已。

不动手得来的知识终究不是你的！想象跟实际总是有差距的。

Step 01 创建4个数据透视表，如果不懂创建去看昨天的文章《吃惊！将拖拉的坏习惯植入Excel，居然能提升效率百倍！》。

Step 02 点击任意数据透视表，插入切片器，勾选业务，确定。

默认出来的切片器是一列，而且没有对多个数据透视表进行关联，需要重新设置。

Step 03 设置切片器的列为2，并调整手工调整高度和宽度。

Step 04最重要的一步，在选项中点击报表连接，将需要关联的数据透视表全部勾选上，确定。

设置完毕，现在就可以多个数据透视表联动了。

前几天有个同事问我，统计活动中奖名单，能不能快速的选择省份后，自动带出该省内的市区，这样操作起来更快。也就是下面这种效果：

接着，我们就来看看是怎么实现的吧。

步骤演示：

首先，你需要另外把各省的市区行政划分整理在旁边表格内，此处仅以2个省为例：

然后，选择D3：D12，菜单栏-数据-数据有效性-设置序列来源为G2：H2；

接着，再选定G2：G18，选择菜单栏-公式-定义的名称-根据所选内容创建，在弹出的页面勾选“首行”复选框。

最后，选取E3：E15单元格区域，执行“数据-数据有效性”，设置允许-序列，然后来源输入=indirect（D3）确定。 indirect函数就是可以把“单元格地址字符串”或“定义的名称字符串”转化为引用内容，通过以上步骤就可以解锁excel二级联动这个神操作了。

当然，excel函数学习门槛比较高。而有一个更简单的方法，用在线表单大师只需要轻松拖到下鼠标就能创建一个登记表了。一个地址字段，就可以轻松解决上面的问题啦。

不信登录官网体验试试~~

切片器对于数据透视表的筛选非常高效，正常情况下一个切片器只能控制当前数据透视表，今天的技巧是一个切片器，控制两个或多个透视表。

如下所示：已经创建了一个切片器，控制的是各个区域的数量和金额，从筛选器中筛选了10月份，会显示杭州和金华地区10月的数量和金额

在下方后面又插入了一个各种商品的数量和金额汇总，现在的需求是，同样一个时间切片器，既可以控制上面的透视表，又可以控制下面的透视表

当切片器筛选的内容是10月时，上面的透视表显示杭州和金华地区10月份的数量和金额，而下面的透视表也会自动显示各种商品10月份的数量和金额。

操作步骤：

首先创建你需要的数据透视表，比如下面两个

然后点击任意一个透视表，在选项卡中插入切片器，插入月份的切片器，如下所示：

选择切片器，点击切片器上方的选项卡中的连接，勾选对应工作表的其他数据透视表即可进行连接

如果不需要联动时，对切片器设置，取消对应勾选即可！

这样一个切片器，就能控制两个或多个以上的透视表了，不用创建两个一模一样的切片器来控制了

你学会了么，欢迎留言讨论，期待您的转发分享

--------------

欢迎关注，更多精彩内容持续更新中...

Excel如何进行多层次联动数据有效性设置

谢谢邀请。我是汤帅，一个PPT自由设计师，承接PPT定制。

数据有效性对于大家而言并不陌生，而且很熟悉，但是运用自如的就是比较困难。在网上一搜索高级的数据有效性需要用到VBA进行设置，这无可厚非，毕竟口令能解决的不需要花费太多时间去专研不必要的方法，两者最终结果都一样。

下拉菜单有时候因为原始数据过多，它的作用就并不是很明显。选项过于繁复，理性输入人会舍弃下拉选取数据，而是选择直接输入数据，以节省选择时间，提高工作效率。所以此时数据有效性的弊端就凸显出来，如果能根据上一个选择而联动筛选出本次所想要的数据，这是非常理想的，也是本次想向大家介绍的一种方法。

回顾一下数据有效性的设置方法，步骤：数据→数据有效性→序列，来源，框选需要的区域。

移除点击此处添加图片说明文字

图1

然后直接进入本次主体，所谓联动，就是当我第一个选择了广东省，第二个可以根据第一个选择筛选出与第一个下支的数据，第三个可以根据第二个筛选出相应的数据，以此类推。本次利用的例子是关于服饰，以类型联动出颜色，再得到相应的金额。

移除点击此处添加图片说明文字

图2

一、设置一级数据有效性

第一步是一级的款型设置，本次数据有效性利用到的公式是=OFFSET($A$2,,,COUNTA(A:A)-1)。

这个公式利用OFFSET动态提取数据，利用定位作用，而且范围是A列非空行数个数。这里OFFSET函数对行和列没有设置值，意味着不向下和不向右取值。

移除点击此处添加图片说明文字

图3

二、设置二级联动数据有效性，增加颜色。

第二步是将款式的颜色作为数据源，不过本次的颜色可能有重复，有重复的，大家可以建立一个辅助列，将此数据进行删除重复项处理。本次数据有效性利用到的公式是=OFFSET($C$1,MATCH(G2,B:B,)-1,,COUNTIF(B:B,G2))。使用OFFSET动态提取C列颜色的数据，而且更加直观。唯一缺点是重复的数据需要增加辅助列进行筛选。

移除点击此处添加图片说明文字

图4

三、设置第三级联动数据有效性，增加价钱。

第三步是将款式的价钱作为数据源，不过本次的价钱可能有重复，有重复的数据，大家可以建立辅助列，将此数据进行删除重复项处理。

本次数据有效性利用的公式是=OFFSET($D$1,MATCH(H2,C:C,)-1,,COUNTIF(C:C,H2))。

移除点击此处添加图片说明文字

图5

Excel的技能是需要大家不断练习才能掌握，不经常练习大家很容易会淡忘。希望大家能互勉共同进步。

今日互动：你觉得还有其他的思路吗？

P.P.S：觉得回答得还行，就点赞吧！

我是汤帅，知名PPT定制设计师，职场技能优秀回答者。学点新鲜有趣的职场技能，关注我准没错。

>

导读：

在EXCEL知识的海洋里，办公室工作的每个人，从小白到高手，每个阶段，都有必须掌握的技巧和公式，而有一条通向高手的毕竟之路，就是indirect函数，今天重点讲解一下。

作者简介：

MOS办公软件国际认证大师级

10年办公应用经验，企业部门负责人

函数释义：

INDIRECT(ref_text,[a1])

此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身，请使用此函数，INDIRECT为间接引用。

题引：

在上图中，F1单元格值为100，G2单元格值为F1，如果我们在H2输入公式=inderect（G2），得到的结果，是100，而不是F1，这是因为这个函数不是直接的引用函数，而是作为一种迂回存在，先得到G2只为F1，而后返回F1的值。

功能简介：5种用法，助你成就高手之路

用法1：二级下拉菜单制作

图二

如图二，制作步骤：

在D列输入列标题，部门，而后在部门下面，新建公司的部门， 这里只做两个部门，品质部和销售部以D列新建的两个部门为列标题，而后新建两列，并在每个部门下面，输入部门的人员名单选中基础数据区域，就是部门区域，而后点击公式，根据所选内容创建，在弹出的窗口，我们选中首行选中首行后，就产生了三个名称区域，1，名称为部门，包含销售部，品质部；2，销售部，包含销售部人员；3品质部，包含品质部人员开始引用，选中B列，而后点击数据，有效性，而后允许值改为序列，在公式那里，输入=部门，因为部门下面，有品质部和销售部，所以我们再输入B列部门的时候，下拉菜单的序列，才显示的是销售部，品质部选中C列，而后点击数据，有效性，序列，而后输入公式=indirect(B1)这里的B1，因为要进行变化的所以，不能用绝对引用，如果写作indirect(B$1),就是错误的，如果这样写，永远返回以B1为名称的值，就是部门用法2：创建开始区域插入行列不受影响的数据区域

如图1，当我们计算总和的时候，用到了sum公式，当我们在中间插入一行，发现没有问题，很多亲们就认为会了，其实不然。

当我们在引用区域的首行，插入一行，而后我们就可以看到，原来引用的区域，是无法自动调节的，这时原来的sum(F2:F12),变成了sum(F3:F12)，首行引用区域变了，就导致了总和的变化，不准确，那么再这种情况下，如何创建固定引用范围呢？

正确做法：将原来的公式，调整为=sum(indirect("F2"):F13)

这样在indirect("F2")的引用区域内，是不会自动变化的，不会因为插入，或是删除，而改变，就达到了固定数据区域的目的

用法3：一列转三列

如果需要转4列，就讲原来的3，改为4，而后将2，改为3，就可以了，这里不做解释，大家理解下

公式：=INDIRECT("B"&3*ROW(C6)-2+COLUMN(C6))&""

用法4：多工作表合并

用到的公式：=INDIRECT(B$1&"!B"&ROW())

用法5：多工作表合并汇总求和

用到的公式：=SUMPRODUCT(SUMIF(INDIRECT({"销售部","品质部","采购部","公关部"}&"!a:a"),B2,INDIRECT({"销售部","品质部","采购部","公关部"}&"!B:B")))

这个公式，是有难度的，是indirect函数和sum的配合，(INDIRECT({"销售部","品质部","采购部","公关部"}&"!a:a")，这部分，其实就是为了返回每个工作表的A列，而后和关键词B2对比，求出每个工作表B列对应的值的汇总，刚开始的亲们，理解不了，只要知道有这种用法，知道用就行了

评论+转发，让更多的人学会

今天就做到这里，希望对在办公室工作的亲们，有所帮助，如有疑问，大家一起讨论！

在日常工作中，经常需要将相似结构或内容多个表格进行合并汇总。当数量庞大时人工进行计算比较繁琐，工作量大且易犯错，使用Excel中的“合并计算”功能可以轻松完成这项任务。

合并计算的数据源可以是同一工作表中的不同表格，也可以是同一工作簿中的不同工作表，还可以是不同工作簿中的表格。现针对第一种情形进行演示说明：

如图1所示的两个结构相同的数据表“表一”和“表二”，利用合并计算可以轻松将这两个表格进行合并，具体步骤如下。

图1 原始表

步骤1选中B10单元格，作为合并计算后结果的存放起始位置，再单击【数据】选项卡【数据工具】命令组的【合并计算】命令按钮，打开【合并计算】对话框，如图2所示。

图2 打开【合并计算】对话框

步骤2激活【引用位置】编辑框，选中“表一”的B2:D6单元格区域，然后在【合并计算】对话框中单击【添加】按钮，所引用的单元格区域地址会出现在【所有引用位置】列表框中。使用同样的方法将“表二”的F2:H6单元格区域添加到【所有引用位置】列表中。

步骤3依次勾选【首行】复选框和【最左列】复选框，然后单击【确定】按钮，即可生成合并计算结果表，如图3所示。

图3 生成合并计算结果表

注意：

1.在使用按类别合并的功能时，数据源列表必须包含行或列标题，并且在“合并计算”对话框的【标签位置】组合框中勾选相应的复选框。

2.合并的结果表中包含行列标题，但在同时选中【首行】和【最左列】复选项时，所生成的合并结果表会缺失第一列的列标题。

3.合并后，结果表的数据项排列顺序是按第一个数据源表的数据项顺序排列的。

4.合并计算过程中不能复制数据源表的格式。如果要设置结果表的格式，可以使用【格式刷】将数据源表的格式复制到结果表中。

Excel中通过数据有效性设置下拉菜单功能可以帮助我们节省很多输入时间，通过选取下拉菜单中的值来实现输入数据，非常快捷、方便、并且能最大限度减少差错的发生。但是日常工作中，我们常需要一个下拉菜单，让后面的下拉菜单依据前面的下拉菜单的内容的改变而改变（也就是多级联动、动态更新的下拉菜单）。

目前网上能看到的教程大多是通过逐级进行定义名称，在通过indirect函数引用名称的方式实现联动菜单的效果。但此种方式有很大的局限性。具体来说有以下几点：

定义名称繁琐。前一级菜单有多少个选项，就需要定义多少个名称，如果有上千个选项就需要定义上千个名称，工作量太大。

选项可能不能定义为名称，名称的格式有要求：如必须以下划线或文字、字母开头，不能出现特殊的符号等，这样就限制了选项的范围。

更新选项不便。若选项需要调整，需要逐级修改定义名称或范围，这对于需要经常更新的工作场景下显得非常不便。

那么有没有一种方法，可以避免上述的问题呢？答案是有的。今天我就这一方法首次公布于众，希望能给你的工作带来帮助……

我们先来看下最终的效果：（四级动态选择，若需要增加、减少选项只需在AreaCode工作表中进行操作，再选择全部刷新选项即可，更新维护非常简单。）

多级联动菜单演示效果

这一方法的实现思路是：

利用数据透视表的去重功能得到不重复的选项数据。

利用OFFSET函数实现对选项数据的动态范围引用，并将该动态区域定义为名称。

利用数据有效性将名称作为数据源，从而形成联动效果。

下面我们就分步骤来看下如何实现这一效果：

第1步：将数据源定义为名称，方便后续建立透视表

第2步：在辅助表中建立数据透视表，为OFFSET函数提供数据

第3步：构建省份的动态引用

第4步：创建第2个透视表为城市提供数据

第5步：构建城市的动态引用

第6步：构建区县的动态引用

第7步：检查各项参数

第8步：新建名称

第9步：新建的名称及引用位置

第10步：为每个选项设置数据有效性

结语：其实excel多级联动菜单原理非常简单，只需要熟悉offset、match、countif、counta等函数的用法，就能轻松实现这一效果。在此基础之上，你还可以做出多级联动列表框的效果，是不是很酷呢？快来试试看吧！

多级联动列表框演示效果

千万别学excel

搞定Office每周三更新

「搞定Office」是黑马公社全新的七大版块之一，每周三更新，教授Office等办公软件的各种应用技巧。

◆◆◆

Excel表格如何实现二级下拉菜单的联动

黑马说：有时候我们需要为表格做下拉菜单，一级的下拉菜单你可能直接用数据验证或者数据有效性就可以实现，那今天黑马要教给大家的是有关二级菜单的联动，Office达人可要看过来了哦！

BY：Andy

◆◆◆

图文说明

效果展示

点击这里“市”下方的下拉菜单后，这里就会有“成都、北京、杭州、上海”四个选项，当我们点击成都以后，在“区”下方单元格的就会相应的出现成都的区。

同样，当我们在市这里选择了杭州，或者是北京、上海等，在区这里就会出现对应城市的区县。

这样二级联动下拉菜单是如何实现的呢？今天黑马就教大家来实现这样的菜单栏效果！

indirect函数

今天所用到的是上周介绍过的indirect函数，如果想要了解上期视频的小伙伴可点击下方蓝色文字：如果你有100个表格需要统计，那indirect函数会让你快的倍爽

下面黑马就来教教大家如何实现上述所说的二级下拉菜单的联动！

首先选中表格中的基础数据，如果列之间没有对齐，需要把空白区域去除掉。点击键盘上的Ctrl+G，就会弹出下面的定位窗口。

然后点击下方的定位条件，选择常量，然后点击确定。这样操作之后，我们就只选中了我们有数据的单元格。

然后这个时候，我们不要点击其他地方。直接点击上方菜单栏中的“公式” -->"根据所选内容创建"，对其名称进行定义，选择“首行”。因为我们这里的第一行单元格是“市”，所以选择首行。

这个时候，我们就可以在“定义名称”菜单中看见我们定义的城市：成都、北京、上海、杭州，以及其在下方对应的有关的区所在的单元格位置。

然后我们需要对一级下拉菜单进行设置，一级下菜单只是引用的是第一行的数据，我们还需要对其进行定义。选中第一行的数据，点击菜单栏中的“定义名称”，在输入区域名称这里输入“市”，然后点击确定。可以看到在定义名称这里，就多了一个市。

定义完成后，选中市下方的单元格，点击“数据”，在数据这里有一个数据验证（在2010版Excel之前叫做数据有效性），点击它。在允许选项中选中“列表”（在2010版Excel之前叫做序列），然后在“源”这里输入“=市”，点击确定即可。

通过以上操作，一级菜单就被设置好了，接下来我们来看看二级下拉菜单如何设计。

在二级下拉菜单中我们需要用到数据验证（数据有效性），以及indirect函数。点击“数据验证”（或者是数据有效性），在允许这里点击列表（或者是序列），然后在源这里输入“=indirect()”，因为我们需要直接引用F4这个单元格中的数据，所以我们需要将鼠标移至括号中，然后点击这个单元格。点击确定后，这里会提示一个错误提醒，可无需理会，直接点击“是”。

然后我们来看看现在的表格，在市这里点击“北京”，然后在区下方就会出现对应的区县名称。

那如果有时候我们有多个单元格需要进行下拉菜单设置，那怎么办呢？如果我们直接向下拉的话，就会发现后面的二级下拉菜单引用的数据其实还是来自于第一个单元格。比如在第一个市下方单元格中选择上海，我们刚刚直接下拉的所有单元格都是来自上海的区县，而不是其对应的杭州的区县。

因为这里我们设置的是对单元格进行绝对引用，这里我们需要进行修改。点击“数据验证”（“数据有效性”），将源下方indirect函数后面的第二个美元符号删除即可。

删除之后，可以再次操作刚刚所直接下拉的其他单元格中的二级菜单，发现区和县就相互对应了。

这就是今天介绍二级联动下拉菜单的使用方法，学会了制作这个，是不是对Excel又更熟练了呢？

Excel中的切片器不光是能控制一张数据透视图，还可以同时控制多张透视图，以达到透视图联动的效果，今天就跟大家分享下透视图联动的制作方法。

1，创建一张透视图，并插入切片器

创建的详细方法，可以看我昨天发的那篇文章，这里就不做详细讲解。

以昨天完成的那张透视图为例。

最终完成的效果如下：

2，创建第二个数据透视图

创建完成后，将透视图由柱状图，修改为饼状图。

具体操作如下：

备注：该透视图使用的数据源与第一张透视图的数据源为同一个数据源。

3，插入第二张数据透视图的切片器

透视图创建完成后，将该透视图剪切到第一张透视图旁边。

然后对第二张透视图插入切片器。同时调整好切片器的位置。

插入切片器的方法，在昨天的那篇文章中有详细介绍，这里就不再重复了。

具体操作如下：

4，设置切片器的联动功能

目前一个切片器只能控制一张透视图。

为了让一个切片器可以控制两张透视图，需要做以下操作。

步骤：

点击选中“年份”那个切片器击右键，在弹出的菜单中选择“报表连接”在弹出的对话框“数据透视表连接”中，增加勾选饼状图对应的那个数据透视表。点击确认。

这样“年份”那个切片器，就可以同时控制这两种透视图了。

具体操作如下：

以上就是一个切片器控制两张透视图的制作方法，如果想要控制三张，四张甚至跟多的透视图，其实操作是一样的。

今天的分享就到这里，以后大家就可以尝试在工作当中使用Excel中的切片器来创建联动的数据透视图了。

作者：睿轩教育

导语：Excel 2010 的切片器可以进行多数据透视图表的联动分析，让使用者能够清晰的看出各类数据的发化情况。

操作步骤：

1. 选择所需要迚行联动分析的数据透视图表，单击【插入】-【切片器】，选择用于切片分析的字段，例如“地区”和“产品名称”，单击【确定】生成切片器。

2. 结果如下图所示。

3. 将切片器和数据透视表进行连接，即可实现联动分析。单击选中一个切片器，在【切片器工具】下选择【数据透视表连接】。

4. 勾选需要联劢癿透规表。

5. 此时切片器就可以控制多个透视表的数据，实现联动功能。结果对比图如下。（可将切片器拖动到数据透视图表的旁边，更便于查看和操作）。