今天,小鱼和大家分享的是 WPS 表单控件以及动态图表的制作方法,实现基本的数据看板。
表单控件
在 Excel 中,也可以像在网页中定义和使用表单,通过表单控件可以实现表单与图表之间的交互,比如:
1、通过下拉菜单更新图表;
2、通过点击 checkbox 更新图表内容;
3、通过拖拉进度条更新图表;
综上所述,通过表单控件,我们可以实现控件与图表的交互。
想要使用表单控件,首先需要为 WPS 安装一个表单控件的插件 wps.vba。在安装前,需要先将 WPS 打开的所有文件关闭。
安装完成之后,在插入菜单栏下的窗体选项中,除了窗体属性和编辑代码是灰色之外,其它表单控件全部是可用状态了。
右键组合框,就可以在工作表中绘制出一个表单控件了:
右键插入的表单控件,选择【设置对象格式】:
数据源区域选择部门列中的数据,单元格链接为 A9 单元格,并且自动帮我们转换成绝对引用:
点击确定之后,就可以通过组合组合框控件来选择部门了,被选择部门的索引将会返回到链接单元格 A9
。人事部在数据源区域中的索引为 1,故单元格 A9 的值为 1:
接下来,我们将结合 Match 和 Index 函数,在参数表中动态显示部门以及部门的男女员工人数、各学历人数统计。
MATCH 与 INDEX 函数
接下来,我们将使用组合框、MATCH 与 INDEX 函数完成如下的动态参数表:
在 A13 单元格录入如下公式:
A13 单元格录入的公式为:
=INDEX($A$3:$J$8,$A$9,MATCH(A$12,$A$2:$J$2,0))
其中,$A$3:$J$8
为 Index 函数的查找区域,使用绝对引用;$A$9
为 Index 函数定位单元格的行索引,也就是通过下拉菜单链接的单元格,使用的也是绝对引用;Index 函数最后一个参数列索引,使用 Match 函数的返回值。
Match 函数的表达式为:
= MATCH(查找的值,查找的区域,查找模式)
Match 函数的第一个参数为 A$12
,为参数表中的第一个标题 “部门” ,使用的是混合引用,锁定行。查找的区域为元数据表的二级标题区域,引用方式为绝对引用。
使用自动填充完成剩余单元格的查找填充:
当下拉表单中的部门发生变化时,参数表将完成动态更新。
动态图表
至此,我们已经完成了可以动态更新的参数表,选中参数表中的数据,插入相应的图表,即可完成动态图的制作。
比如,动态统计各部门男女员工的占比情况、各学历员工人数占比情况:
研发部员工情况:
总结
表单控件用于 Excel 中数据的筛选按钮,是构建交互式图表的重要组成,比如:组合框、复选框、滚动条、选项按钮等。
MATCH 与 INDEX 函数是数据查询的组合函数,结合表单控件可以制作动态的数据源。
动态图表使用动态数据源作为基础,通过筛选器改变参数表中的值,从而形成交互动态图表的效果。
另外,文件需要另存为 “启用宏工作簿”,并且重新打开文件时需要选择启用宏,才能实现宏功能的效果。
网友评论