EXCEL加行 / 列数据图表自动更新 |
您所在的位置:网站首页 › 改变图表什么后系统会自动更新图表 › EXCEL加行 / 列数据图表自动更新 |
碎碎念:最近打工……因为不能用外部软件,所以疯狂学习用Excel画表中,整个就是一个痛苦面具,为了防止自己忘了,还是记录一下吧! 一、函数知识补充详见:我另一篇文章,本场景下我们主要会用到:OFFSET、COUNT、MATCH三个函数 EXCEL的部分简单函数_一睡不起的曲奇的博客-CSDN博客 二、场景应用【EXCEL加行 / 列数据图表自动更新】 1.图例(系列)项在行,分类在列目标:在图表下方新增行数据,图表自动更新 方法一:Ctrl+T选中图表(推荐) Ctrl+T选中数据,创建表后再绘制图表,即可实现 方法二:添加复选框 参考文章:http://jingyan.baidu.com/article/ca00d56c682590e99eebcfaa.html 2.图例(系列)项在列,分类在行【例】图表如下所示: 统计23年一年的月别销量数据, 别将其与22年销量进行对比,并绘制出同比增长曲线,绘制结果如图1所示; 新增六月份数据,得到图2: 图1图2在一般的绘图操作时,如果更改已选中的数据区域内的数据,图表会自动更新,但是每次新增一个月份的数据,已有的绘图数据区域不会自动扩展,因而图表无法自动更新。 目标:在图表右侧新增列数据,图表自动更新(图1→自动更新→图2) STEP1:建立名称CTRL+F3打开名称管理器,新建: 在新建中填写名称、工作薄范围(推荐选中所处Sheet),在引用位置中填以下公式: =OFFSET($B$3,0,1,1,COUNT($C$3:$N$3)) 含义: 由$B$3为参照,向下挪1格,向左挪1格,取1*COUNT($C$3:$N$3)面积的数据,也就是下图中绿框内的内容COUNT($C$3:$N$3)代表数字非空格数,如,在C3:N3之间1-5月销售数据取值非空,则取5
(此处补充Offset函数和Count函数要点) - 优点:该函数帮助我们实现了自动定位了以B3(23年1月销售数据)为起点,C3:N3中非空的数据部分。因而实现了在右侧挨着添加数据,图表数据的范围自动更新。 - 缺点:只能从23年1月B3数据作为起点(因为OFFSET函数的第3个参数取了1),所以只能增加后列,却无法减少前列。 若把1月的数据删除,则图表会呈现1-4月数据,而不是2-5月。那么有什么办法可以解决这个情况?即我们应当使用一个函数替换第3个参数位置的1,使其可以定位到表格中第一个非空位置作为起点。 所以此处我们要解决的问题——定位一行/一列的的第一个非空位置。 EXCEL的部分简单函数_一睡不起的曲奇的博客-CSDN博客(详细见该文章MATCH函数部分) 取=MATCH(TRUE,$C$3:$N$3"",) 则对SO_23的引用位置更新为: =OFFSET($B$3,0,MATCH(TRUE,$C$3:$N$3"",),1,COUNT($C$3:$N$3)) 同理建立名称: SO_22 =OFFSET(SO!$B$4,0,MATCH(TRUE,SO!$C$4:$N$4"",),1,COUNT(SO!$C$4:$N$4)) SO_M =OFFSET(SO!$B$2,0,MATCH(TRUE,SO!$C$2:$N$2"",),1,COUNT(SO!$C$2:$N$2)) SO_MOM=OFFSET(SO!$B$5,0,MATCH(TRUE,SO!$C$5:$N$5"",),1,COUNT(SO!$C$5:$N$5)) STEP2:绘制图表- 先使用最普通的绘制方式,插入,推荐的图表: - 右键,点击选择数据: - 在图例项(系列)选中23年,编辑,将其系列值=前面新建的名称SO_23(因为前面已经指定该名称在Sheet-SO中,因而后续会自动更新为SO!SO_23) - 同理将22年与同比都分别更换为SO_22与SO_MOM - 再编辑水平(分类)轴标签,使其取SO_M 最终得到的图表即可实现自动更新啦! |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |