我们都知道当数据过多的时候，我们制作Excel图表就会显得非常的复杂，图表上面的内容就会特别多。Excel老玩家就会想到用切片器制作动态可变化的图表来显示。今天我们就来学习一下一个比......
2023-01-08
带你认识大佬超爱的excel中的OFFSET函数
Excel函数
2023-01-08
如果老板看惯了你一直上报的平淡表格（如下），现在你突然展现给他的是可以动态查询的图表（如下），你说能否击中老板挑剔的心？能否让老板惊讶激赏？
怎么实现这种转变？很简单，就是做动态图表。动态图表就是老板选择不同的区域，图表就展示不同的数据。要实现需要三步。第一步做下拉菜单供老板选择；第二步做一个根据选择，动态变化的数据区域；第三步根据动态数据区域插入图表。动态数据区域常用VLOOKUP函数实现，但今天不走寻常路，我们利用OFFSET函数完成动态数据区域。
第一步：做下拉选择
1.选中J1单元格，点击“数据”选项卡下的“数据验证”。
2.在“数据验证”窗口下方的“设置”选项里，“允许”选择“序列”，来源选择五个销售区域所在的单元格“=$A$2:$A$6”，点击确定。
第二步：建立动态更新的辅助数据区域
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的位置为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变化，平均值区域$B$8:$G$8也会随之变化。
第三步：创建图表
5.我们根据设置好的辅助行创建图表。选择标题B1:G1和辅助行B7:G8区域，点击”插入”选项卡下的”图表”组里的“二维柱形图”。
6.在K1单元格输入“=J1&"销售数据"”。
点击图表标题框，在编辑栏输入“=Sheet2!$K$1” ,这样图表标题就和数据验证区域同步更新了。
7.点击“图表工具”下方“设计”选项卡下的“更改图表类型”。
8.在“更改图表类型”窗口，点击“所有图表”选项下的“组合”，将平均值所在的系列2修改成“折线图”。
9.最后将图表图例删除，把辅助数据B7:G8和K1单元格字体修改成白色不可见，就完成了。
实现透视表与数据源同步更新
数据透视表要想实现和数据源同步的更新，除了之前给大家介绍过超级表可以实现外，我们也可以用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函数我们实现了动态图表，以及透视表与数据源联动。
带你认识大佬超爱的excel中的OFFSET函数的下载地址：
相关文章
- 详细阅读
-
excel 合并上百个工作簿数据，你花了一天别人只用了10秒详细阅读
在工作中，可能许多朋友都会碰到一个情况，那就是工作簿和工作表数据的合并操作。如何将上百个工作簿快速合并到一个表格中，许多朋友可能会觉得不可思议。今天我们就来教大家学习一......
2023-01-08
-
Excel文本函数，excel拆分和组合函数，掌握这些就够了！详细阅读
今天在这里为你分享5个Excel文本函数，这些拆分和组合函数，你一定会用上的。①LEFT函数公式：=LEFT(A2,1)在Excel表格中，需要想要拆分汉字，想从哪里开始就从那哪里开始。首先选定单元格......
2023-01-08
-
Excel函数一键变翻译，学会了这个方法，再也用苦思冥想了！详细阅读
相信大家也和我一样，才开始看到Excel可以当做翻译软件的时候会很好奇，这究竟是怎样做到的？其实，这个方法并不是很难，它是由一个函数公式而制作出来的，好了，首先我们一起来看看成......
2023-01-08
-
Excel函数公式大全，办公室人员必备！详细阅读
函数可以说是所用快捷方法中最为简单的一种方法，为什么很多人认为函数用起来很难了？主要是因为它拥有很长的函数公式，记不住。其实不管是学Excel函数，还是学习其他的一些快捷方法......
2023-01-08