二维码 购物车
部落窝在线教育欢迎您!

再不让你的表动一动,年终加“薪”又是梦:

不拘一格OFFSET动态表

 

作者:夏雪来源:部落窝教育发布时间:2018-11-07 17:10:18点击:85

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。
编按:

立冬了,冷了!屋里没暖气?动一动,跺跺脚,搓搓手,人就暖和了。立冬了,又一年要完了!还没涨薪?让你的表动一动不同以往,同步更新,动态展现,不但自己省事,老板也喜欢开“薪”……

 

 

第一动:打动,动起来的图表打动老板

老板看惯了你一直上报的平淡表格(如下),现在你突然展现给他的是可以动态查询的图表(如下),你说能否击中老板挑剔的心?能否让老板惊讶激赏?

你现在的汇报                      你明天的汇报

  

 

怎么实现这种转变?很简单,就是做动态图表。

动态图表就是老板选择不同的区域,图表就展示不同的数据。要实现就三步,一步做下拉菜单供老板选择,一步做根据选择动态变化的数据区域,一步根据动态数据区域插入图表。

动态数据区域常用VLOOKUP函数实现,但今天不走寻常路,我们利用OFFSET函数完成动态数据区域。

第一步:做下拉选择

1.选中J1单元格,点击“数据”选项卡下的“数据验证”。

 

 

2.在“数据验证窗口下方的设置选项里,“允许”选择“序列”,来源选择五个销售区域所在的单元格=$A$2:$A$6,点击确定。

 

Excel教程

 

第二步:建立动态更新的辅助数据区

3.接下来就是根据J1单元格的值来动态更新图表。J1选择北京区域。在B7单元格输入=OFFSET(B1,MATCH($J$1,$A$2:$A$6,0),0)。然后公式往右填充至G7单元格。这样B7:G7单元格返回的就是北京区域1-6月的销售额。

 

 

解析:

利用OFFSET以“B1”为参考系,偏移的行数为使用MATCH函数获取$J$1$A$2:$A$6的位置,偏移列数为0表示不偏移。如图J1的值是北京区域,在$A$2:$A$6的位置为1OFFSET返回的值为以“B1”为参考系,向下偏移一行的引用。这样随着选择区域$J$1的不断变化,B7:G7单元格就获取到对应区域的销售数据。

4.然后设置平均线的数据,在B8单元格输入=AVERAGE($B$7:$G$7),获取$B$7:$G$7的平均值。然后公式往右填充至G8单元格。如果选择区域$J$1变化,则$B$7:$G$7变化,平均值也会随之变化。

 

Excel教程公众号

 

第三步:创建图表

5.我们根据设置好的辅助行创建图表。选择标题B1:G1和辅助行B7:G8区域点击插入选项卡下的”图表”组里的“二维柱形图”。

 

 

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

 

 

点击图表标题框,在编辑栏输入“=Sheet2!$K$1 ,这样图表标题就和数据验证区域同步更新了。

 

 

7.点击“图表工具下方“设计选项卡下的更改图表类型

 

 

8.在“更改图表类型窗口,所有图表选项下的组合,将平均值所在的系列修改成折线图

 

 

9.最后将图表图例删除,把辅助数据B7:G8K1单元格字体修改成白色不可见,就完成了。

 

 

 

第二动:联动,让透视表与数据源同步更新

    数据透视表要想实现和数据源同步的更新,之前给大家介绍过超级表可以实现。除此外,我们也可以用OFFSET实现。如图,右侧透视表是根据左侧数据源插入的。

 

 

现在我们需要数据源更新后,透视表也能同步更新,如下:

 

 

第一步:定义名称

1.点击“公式”选项卡下的“定义的名称”选项组里的“定义名称”。

 

 

2.在“新建名称”窗口,名称栏输入数据,“引用位置”输入下列公式

=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

 

 

解析:

Sheet1是数据所在的工作表。函数表示以Sheet1!$A$1为参考系,不偏移(偏移行和列为空),动态返回整个表格数据。COUNTA(Sheet1!$A:$A)用于获取表格数据的行数,COUNTA(Sheet1!$1:$1)用于获取表格数据的列数。它们获取的结果是动态的,随着表格行列数的增加或减少而变化。

 

第二步:更改数据源

3.单击透视表上任意单元格,出现“数据透视表工具”。然后点击“数据透视表工具”下方“分析选项卡里的“更改数据源”。

 

 

4.在“更改数据透视表数据源窗口,将/区域修改成刚定义的名称“数据”,点击确定。

 

 

第三步:刷新同步

5.接下来在数据最后一行添加数据。

 

 

6.鼠标右击透视表,选择“刷新”命令,数据透视表就完成更新啦。

 

 

利用Offset函数我们实现了两“动”,一动,利用动态图表打动老板,好开“薪”,二动,透视表与数据源联动,自己更省事。Ok,快点让你的表格动起来吧!冬天来了,虽然避免不了冷手冷脚,但加加薪暖心就好!

 

 

素材下载请加QQ群:264539405

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

http://www.itblw.com/ewebeditor/uploadfile/20180914164059384001.png

相关推荐:

动态图表入门《一看就会的excel动态图表入门篇

数据同步更新的其他做法《excel数据透视表动态刷新数据的三种方法

带控件的动态图表《控件制作Excel条形图

动态甘特图制作《Excel制作动态甘特图大揭秘!

图片说明:

首图运用了《老板求加薪》图书封面素材。