excel图表学习的折中偏移动态表(如何制作Excel表格的动态数据图表)

初冬天气很冷！房子里没有暖气？动一动，跺跺脚，搓搓手，人就暖和了。立冬了，又一年要过去了！你还没有加薪吗？让您的手表以不同的方式移动，同步更新并动态显示。你不仅省事，老板还喜欢付钱…

第一招:移动，移动图移动老板。老板习惯于阅读你一直在报告的普通表格（如下图）。现在你突然给他看一个可以动态查询的图表（如下图）。你觉得能击中老板挑剔的心吗？你能给老板一个惊喜并奖励他吗？

你现在的报告，你明天的报告。

如何实现这种转变？很简单，做动态图表就行。

动态图表是指老板选择不同的区域，图表显示不同的数据。要实现这一点，需要三个步骤:第一步是制作下拉菜单供老板选择，第二步是选择动态变化的数据区域，第三步是根据动态数据区域插入图表。

动态数据区通常由VLOOKUP函数实现，但今天我们不走寻常路。我们使用偏移函数来完成动态数据区。

第一步:进行下拉选择。1.选择J1单元格，然后单击数据选项卡下的数据验证。

2.在数据验证窗口底部的设置选项中，为“允许”选择“序列”，选择五个销售区域所在的单元格“=$A$2:$A$6”作为源，然后单击确定。

步骤2:建立动态更新的辅助数据区。3.下一步是根据J1单元格的值动态更新图表。J1选择“北京地区”。在单元格B7中输入“= offset（B1，match（$ j $ 1，$ a $2: $ a $6，0）”。然后公式被填充到右侧的G7单元格中。这样，cell B7:G7返回了Beijing从1月到6月的销售额。

分析:

使用偏移量以“B1”为参考系，偏移量行数是通过使用MATCH函数获得$J$1在$A$2:$A$6中的位置，偏移量列数为0，表示没有偏移量。如图J1所示，值为“北京地区”，$A$2:$A$6的位置为1。OFFSET返回的值是以“B1”为参考系统的参考值，该值向下移动一行。这样，随着选择区域$J$1的不断变化，单元格B7:G7将获得相应区域的销售数据。

4.然后设置平均线的数据，并在单元格B8中输入“= AVERAGE（$B$7:$G$7“，以获得$ B $ 7:$ G $ 7的平均值。然后公式被填充到G8单元格的右侧。如果选择区域$J$1发生变化，那么$B$7:$G$7也会发生变化，平均值也会发生变化。

第三步:制作一张图表。我们根据设置的辅助行创建一个图表。选择标题B1:G1和辅助线B7:G8，然后单击“插入”选项卡下图表组中的“2D柱形图”。

6.在单元格K1中输入“=J1&“销售数据“。

单击图表标题框并输入“=Sheet2！$K$1“，以便图表标题与数据验证区域同步更新。

7.单击图表工具下设计选项卡下的更改图表类型。

8.在“更改图表类型”窗口中的“所有图表”选项下，单击“组合”以更改平均值属于“折线图”的系列。

9.最后，删除图表的图例，并将辅助数据B7:G8和K1单元格的字体更改为白色和不可见，这样就完成了。

第二个动作:联动，让透视表可以与数据源同步更新。为了实现透视表与数据源的同步更新，我已经向您介绍了超级表是可以实现的。此外，我们还可以使用OFFSET。如图所示，右边的透视表是根据左边的数据源插入的。

现在我们需要更新数据源，数据透视表也可以同步更新，如下所示:

步骤1:定义名称1。在“公式”选项卡下的“定义名称”选项组中单击“定义名称”。

2.在“新名称”窗口中，在“名称”栏中输入“数据”，并在“参考位置”中输入以下公式

= OFFSET（sheet 1！$A$1，，，COUNTA（sheet 1！$A:$A）、COUNTA（sheet 1！$1:$1))

分析:

“Sheet1”是数据所在的工作表。该函数由“Sheet1！”表示$A$1“是引用系统，动态返回整个表格数据，没有偏移（偏移的行和列为空）。COUNTA（sheet 1！$A:$A）用于获取表数据的行数，COUNTA（sheet 1！$1:$1）用于获取表格数据的列数。它们获得的结果是动态的，并且随着表中行数和列数的增加或减少而变化。

第二步:更改数据源3。单击数据透视表上的任意单元格以显示数据透视表工具。然后在“数据透视表工具”下的“分析”选项卡中单击“更改数据源”。

4.在“更改数据透视表数据源”窗口中，将表/区域更改为新定义的名称数据，然后单击“确定”。

第三步:刷新同步5。接下来，在最后一行数据中添加数据。

6.右键单击数据透视表并选择“刷新”命令，数据透视表将被更新。

使用偏移功能，我们实现了两个“移动”，一个移动，使用动态图表来打动老板，轻松打开“工资”和两个移动，并将透视表与数据源链接起来，这对于我们自己来说更方便。好的，让你的表单快速移动！冬天来了，虽然避免不了手脚冰凉，但涨工资暖心就好！

相关学习推荐:excel教程

这几篇文章你可能也喜欢：

• 干货分享:word和excel文档如何转换（如何将Word和Excel转换成同一个pdf）
• Excel表格协作:与同事共享工作簿并制作表格！
• Excel图表学习制作折线图和柱形图组合图表（excel2003条形图和折线图组合）
• 实用Excel技巧分享:13种日期输入法，你知道几个！(excel日期输入法)
• Excel跨表提取，微软查询KO所有函数

本文由主机参考刊发，转载请注明：excel图表学习的折中偏移动态表(如何制作Excel表格的动态数据图表) https://zhujicankao.com/110859.html