办公小技巧:制作随单元格选定而动的动态图表

CFan 电脑爱好者 2020-02-26 10:20应用 标签:图表 单元 技巧 办公

我们以前见到的动态图表,都是利用控件或者数据有效性,实现选择不同的项目类别。其实,动态图表还可以随着单元格的选定而动态变化,我们只需要利用CELL、INDIRECT、ADDRESS、MATCH、OFFSET等简单的函数,就能实现动态显示(图1)。

1823A-DTTB-1

以制作业务员销售业绩动态表为例。

首先,需要构造图表的数据源。在工作表的原数据基础上,在合适位置利用函数构造图表所需要的数据源。如在B12单元格中输入“=INDIRECT(ADDRESS(CELL("row"),1))”,用来获取当前单元格的值;在C12单元格中输入“=INDIRECT(ADDRESS(3,CELL("col")))”,用来获取当前单元格所在的项目类型;在D12单元格中输入“=MATCH($B$12,$A$4:$A$10,0)”,用来获取当前单元格在姓名数据中所处的索引值;在E12单元格中输入“=MATCH($C$12,$B$3:$C$3,0)”,用来获取当前单元格在项目类型中所处的索引值;在B13单元格中输入“=OFFSET($A$2,0,COLUMN(A1)*2-1,1,1)”,向右填充到M13,用来获取图表的X分类轴数据,即月份;在B14单元格中输入“=OFFSET($A$3,$D$12,$E$12+COLUMN(A1)*2-2,1,1)”,向右填充到M14,根据D12、E12的索引值获取具体的图表数据源。

添加图表。选取B13:M14单元格,插入一簇状柱形图。这样,所需要的图表就基本创建完成了(图2)。

1823A-DTTB-2

为了让单击单元格时图表动态变化更顺畅,需要添加几句简单VBA代码。选择“开发工具”选项卡,点击“Visual Basic”,在出现的VBA代码编辑窗口中,双击Sheet1,在右侧编辑窗口输入如图所示3行代码(图3)。

1823A-DTTB-3

小提示:如果窗口中没有“开发工具”选项卡,可以点击“文件→选项”,在弹出窗口中勾选“开发工具”就可以了。

接下来,根据需要设置好图表样式,添加图表标签等。选中F12单元格,输入“=CONCATENATE(B12,C12,"全年统计")”,选中图标标题,在公式编辑栏中输入“=F12”,这样图标标题就会跟随单元格的变化而变化。最后,点击“文件→另存为”,在弹出窗口的文件类型处选择“Excel 启用宏的工作簿(*.xlsm)”,进行保存就可以了。