欧非资源网:安全、免费、专业放心的资源下载站! 最新软件|软件分类

您的位置:欧非资源网 > Excel专区 > Excel函数 > 带你认识大佬超爱的excel中的OFFSET函数

带你认识大佬超爱的excel中的OFFSET函数

时间:2020-03-28 17:40作者:admin来源:未知人气:502我要评论(0)

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

怎么实现这种转变?很简单,就是做动态图表。动态图表就是老板选择不同的区域,图表就展示不同的数据。要实现需要三步。第一步做下拉菜单供老板选择;第二步做一个根据选择,动态变化的数据区域;第三步根据动态数据区域插入图表。动态数据区域常用VLOOKUP函数实现,但今天不走寻常路,我们利用OFFSET函数完成动态数据区域。

第一步:做下拉选择

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

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

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

解析

利用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也会随之变化。

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

第三步:创建图表

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

实现透视表与数据源同步更新

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

第一步:定义名称

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

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

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

解析

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

第二步:更改数据源

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

第三步:刷新同步

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

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

Excel教程,Excel中两个实用案例,带你认识大佬超爱的OFFSET

 

利用OFFSET函数我们实现了动态图表,以及透视表与数据源联动。

带你认识大佬超爱的excel中的OFFSET函数的下载地址:
  • 本地下载

  • 相关阅读 Excel有哪些常用的数学函数?​Excel取消表格中虚线的两种方法Excel最常见的「错误值」,这些含义你都知道吗?实现快速找出Excel表格中两列数据不同内容的3种方法!如何利用Excel一键提取身份证的这些重要信息,公式直接套用!Excel如何制作动态红绿灯,工作可不要亮红灯哦Excel身份证号大探索excel如何根据日期按月汇总计算公式Excel浪漫表白公式,发给心仪的她/他Excel表格如何自动求和

    文章评论
    发表评论

    热门文章 excel 两表数据快速对比,高手都是这样做,四种方法随你选.xlsm是什么文件格式,以及xlsm文件怎么打开的方法excel if函数如何多个条件并列excel中计算加权平均数的公式:用SUMPRODUCT和SUM函数计算加权平均

    最新文章 Excel有哪些常用的数学函数?​Excel取消表格中虚线的两种方法 Excel最常见的「错误值」,这些含义你都知道吗?实现快速找出Excel表格中两列数据不同内容的3种方法!如何利用Excel一键提取身份证的这些重要信息,公式直接套用!Excel如何制作动态红绿灯,工作可不要亮红灯哦

    人气排行 excel 两表数据快速对比,高手都是这样做,四种方法随你选.xlsm是什么文件格式,以及xlsm文件怎么打开的方法excel if函数如何多个条件并列excel中计算加权平均数的公式:用SUMPRODUCT和SUM函数计算加权平均excel中IF条件函数10大用法完整版,全会是高手,配合SUMIF,VLOOKUPexcel中COUNTIFS函数9种高级用法详解,条件统计重复值,告别加班涨工如何解除Excel VBA工程密码excel 如何根据身份证号码提取户籍所在省份地区函数公式

    盖楼回复X

    (您的评论需要经过审核才能显示)