王佩丰24节Excel学习笔记——第二十一讲:经典Excel动态图表实现原理
【以 Excel2010 系列学习,用 Office LTSC 专业增强版 2021 实践】
【本章技巧】
- 使用公式记得要绝对引用;
- 定义好的名称,引用时要使用文件名+!+定义名,不能写错,否则无结果;
- 利用offset 函数解决数据透视表做好后,原数据值添加了数据,导致数据透视表无法更新问题。
- 记住这个公式,取数据透视表中所有的数据,=OFFSET($A$1,0,0,COUNTA($A:$A),11),使用时把它定义名称公式里即可重复使用。
解释:=OFFSET($A$1,0,0,COUNTA($A:$A),11)
以A1为基准,向下0行,向右0列,意思是包含表头,用0就是不用动,取多少行呢?用函数counta($A:$A),算一下非空值行,取多少列是固定的,此处比如说是11列。 - 使用offset做动态图表取值。
一、动态图表实现原理
1、理解图表中的数据系列
点空白处,插入折线图,在空白折线图上点右键,选择数据源,左侧图列项有两个数据源系列,即彩盒系列和宠物用品系列,每个系列对应一列或一行数据。基于这一对一的关系,可以对数据源进行动态处理。
2、手工修改系列中的数值与坐标轴数据
选中图表,右键选择数据源,可以对图例项进行添加或编辑或删除操作,也可以对水平分类轴标签进行编辑数据处理。
3、小试牛刀-利用IF创建简单的动态图表
步骤:
1、打开Excel,找到文件-选项-自定义功能,勾选开发工具
2、在任意位置添加两个复选框
3、点击右键,可对复选框进行文本内容修改,或都移动位置
4、这里设置好名称好,点设置控件格式,指定一个单元格,看选中与不选中的值各是什么
5、基于此操作可使用IF判断,如果为True,就显示哪些值,如果为False就不显示值
6、写判断语句:=IF($G$2,$B$2:$B$13,$F$2:$F$13),记得使用绝对引用
7、通过自定义名称,将 if 语句公式定义名称
8、分别定义好两个数据系列,此处为:彩盒、宠物用品
9、点空白处插入折线空白图表,在图表上点右键,选择数据源,添加彩盒和宠物用品系列数据引用的位置,此处是引用定义的名称,注意此处位置要写文件名!定义名称,写错将不会有数据!
10、完成添加后如下图所示,可以美化一下,将复选框拿到图列旁边,可去掉复选框的中文字,然后把图置于底层,就可显示复选框。
二、利用offset函数与控件创建动态图
1、Offset函数概述
语法:以某个点为基准,下移N行,右移N列,取N行,取N列
类似经纬度:东京北纬30度,向下多少,再向右多少?
2、Offset函数的动态引用示例
例:数据透视表自动更新,使用自定义名称,利用offset函数写好数据区域公式,插入数据透视表时,直接引用定义名称,生成的数据表如果数据源有变化,直接右键刷新即可。
记住这个公式,取数据透视表中所有的数据,=OFFSET($A$1,0,0,COUNTA($A:$A),11),使用时把它定义名称公式里即可重复使用。
解释:=OFFSET($A$1,0,0,COUNTA($A:$A),11)
以A1为基准,向下0行,向右0列,意思是包含表头,用0就是不用动,取多少行呢?用函数counta($A:$A),算一下非空值行,取多少列是固定的,此处比如说是11列。
案例:定义名称:数据区域,公式为:=OFFSET($A$1,0,0,COUNTA($A:$A),11)
3、动态图表1 永远返回最后10行数据
4、动态图表2 通过控件控制图表数据
步骤:
1、点开发工具,插入2个滚动条
2、设置滚动条控制属性
3、写公式,定义名称,链接数据源