excel折线图自动更新数据源(职场经验分享-excel自动更新图表曲线)

背景

相信有时工作中会碰到这样的情况:需要用excel定期更新数据和图表,例如监控每个月的材料价格、更新每个月的销售数据等。常规操作是每次更新在最后增加行或者列,填入最新数据,再改变曲线图的数据范围。这篇将介绍一种自动更新曲线图的方法,即填入最新数据后,曲线图(或其它类型图表)将自动更新,无需手动调整数据范围。

操作步骤

首先建立你要绘制曲线的数据表格,简单起见,以钢筋价格监控为例,表格如下所示:

excel折线图自动更新数据源(职场经验分享-excel自动更新图表曲线)(1)

在excel上方的菜单栏,找到“公式”里面的“名称管理器”,鼠标左键单击:

excel折线图自动更新数据源(职场经验分享-excel自动更新图表曲线)(2)

excel折线图自动更新数据源(职场经验分享-excel自动更新图表曲线)(3)

此时需要新建几个名称,与接下来要绘制曲线所用到的数据相关。例如本例中需要绘制钢筋信息价和市场价的价格趋势图,则对应图表的横轴为日期、纵轴为价格,以及两条曲线(分别为钢筋信息价和钢筋市场价)。由此可知,绘制该图形共需要三个数据:日期、钢筋信息价、钢筋市场价,则对应新建三个名称,点击上图中的“新建”,如下图所示:

excel折线图自动更新数据源(职场经验分享-excel自动更新图表曲线)(4)

名称可以自拟,方便起见可以直接与表头一致(第1行),以“日期”为例,名称中填“日期”,引用位置处填入:

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

excel折线图自动更新数据源(职场经验分享-excel自动更新图表曲线)(5)

另外两个名称分别设置为:

“钢筋信息价”:

“=OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)”

“钢筋市场价”:

“=OFFSET(Sheet1!$C$1,1,0,COUNTA(Sheet1!$C:$C)-1,1)”

excel折线图自动更新数据源(职场经验分享-excel自动更新图表曲线)(6)

OFFSET函数在“下拉菜单的设置”篇章中已有介绍,在此不再详述:

(职场经验分享-excel设置下拉菜单)

COUNTA函数为计数函数。

“COUNTA(Sheet1!$B:$B)”的意思就是B列不为空白的表格数量,这也是自动更新图表的关键,-1的原因在于表头名称占了一行,需要减一。

以上“Sheet1”是指本工作表的名称。

excel折线图自动更新数据源(职场经验分享-excel自动更新图表曲线)(7)

名称建立完成后可以开始绘图,表头菜单栏选择“插入”,选择合适的图表类型,插入即可,这里选择曲线图。

excel折线图自动更新数据源(职场经验分享-excel自动更新图表曲线)(8)

如果直接插入图表,显示效果如下,显然我们并不需要绘制没有填数据的部分:

excel折线图自动更新数据源(职场经验分享-excel自动更新图表曲线)(9)

在曲线图的位置鼠标右键,选择“选择数据”,进入后对图例项和水平轴标签进行编辑。

excel折线图自动更新数据源(职场经验分享-excel自动更新图表曲线)(10)

以钢筋信息价为例,将系列值设置为之前已经建立的对应名称,即:

“=自动更新图表曲线.xlsx!钢筋信息价”

“自动更新图表曲线.xlsx”为该excel的名称,“钢筋信息价”即为之前建立的名称。

excel折线图自动更新数据源(职场经验分享-excel自动更新图表曲线)(11)

excel折线图自动更新数据源(职场经验分享-excel自动更新图表曲线)(12)

同理编辑钢筋市场价:“=自动更新图表曲线.xlsx!钢筋市场价”

水平轴标签:“=自动更新图表曲线.xlsx!日期”

到此,设置完成,此时曲线图如下所示:

excel折线图自动更新数据源(职场经验分享-excel自动更新图表曲线)(13)

如果将后几个月数据填入后,曲线图也将自动更新,如下所示:

excel折线图自动更新数据源(职场经验分享-excel自动更新图表曲线)(14)

最后可以对曲线图进行适当美化,根据实际情况即可,举例如下:

excel折线图自动更新数据源(职场经验分享-excel自动更新图表曲线)(15)


COUNTA函数详细介绍可参考以下链接:

https://www.wps.cn/learning/room/d/329548

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页