办公小技巧:光标过处 图表交互

CFan 电脑爱好者 2021-09-22 08:48应用 标签:光标 图表 技巧 办公

说起Excel交互式图表的制作,很多人首先会想到利用控件来实现。其实,我们也可以不使用控件,只用3行简短的VBA代码,再加少量函数,就能实现光标过处、图表变化的交互效果,这样便可以省去使用控件的繁琐设置过程。在本文中,我们将实现当光标在各部门间移动时,对应图表自动变换的效果(图1)。

2007A-JHTB-1

1. 设置交互代码

打开数据工作表,切换到“开发工具”选项卡(如果没有这个选项卡,点击“文件→选项”,在弹出的“Excel选项”窗口中选择“自定义功能区”,然后勾选“开发工具”就可以了),点击“Visual Basic”,在出现的VBA窗口中,点击“插入→模块”,在代码窗口输入图中所示3行简单代码(图2)。

2007A-JHTB-2

小提示:

这段代码的核心部分是中间一句,其意思是让p1单元格获取tmp这个参数所对应的单元格的值。

2. 改造原数据

要想图表具有交互性,还需要对原数据进行一下改造。在A2单元格输入“=IFERROR(HYPERLINK(changetmp(A2)),"A部门")”,在A3单元格输入“=IFERROR(HYPERLINK(changetmp(A3)),"B部门")”,本列单元格的输入内容依此类推(图3)。

2007A-JHTB-3

小提示:

在输入公式过程中会出现警告提示,这时,直接点击“确定”即可。

3. 构造图表数据源

切换到“公式”选项卡,点击“名称管理器”,在弹出窗口点击“新建”,在新弹出窗口“名称”处输入“TEMP”,“引用位置”处输入“=OFFSET(Sheet1!$A$1,MATCH(Sheet1!$P$1,Sheet1!$A$2:$A$5,0),1,1,12)”。这样,就为图表构建了数据源(图4)。

2007A-JHTB-4

4. 建立图表

选中一空白单元格,插入一个带数据点的折线图。右击这个空白图表,选择“选择数据”,在新弹出窗口的“图例项”处点击“添加”,在弹出窗口的“系列值”处输入“=SHEET1!TEMP”,确定;在“水平(分类)轴标签”处点击“编辑”,轴标签区域选择“=Sheet1!$B$1:$M$1”。这样,图表就构建完成了,当鼠标在各部门滑过,图表交互的效果就出现了(图5)。

2007A-JHTB-5

在Q1单元格输入“=$P$1&"2019年1-12月销售变化情况"”,选中图表标题,在公式编辑栏输入“=Sheet1!$Q$1”(或者将光标点击到公式编辑栏后,用鼠标点取Q1单元格)。这样,图表标题也会跟随变化(图6)。

2007A-JHTB-6

5. 美化图表

为了让图表看起来更加漂亮,需要对图表执行进一步的美化处理。删除图表的横向网格线,右击图表的纵坐标轴,选择“设置坐标格式”,在坐标轴选项中,“最大值”处设置为“120”,删除纵坐标轴;切换到“图表设计”选项卡,点击“添加图表元素→数据标签→上方”,设置好标签所需要字体、颜色及大小;选中图表中的数据点,在右侧的“设置数据系列格式→填充与线条→标记”选项卡中,“标记选项”处选择“内置”,“类型”处选择图片,在弹出窗口选择所需要的图片,“边框”处选择“无线条”;设置好横坐标轴的字体颜色及大小等(图7)。

2007A-JHTB-7

6. 保存文件

因为交互过程用到了VBA代码,所以需要将文件保存成启用宏的文件。点击“文件→另存为”,在弹出窗口输入所需文件名,保存类型处选择“Excel启用宏的工作簿”,点击“保存”就可以了(图8)。

2007A-JHTB-8

应用扩展:

其实,图表数据源的获取还可通过VLOOKUP函数,在Q2单元格中输入“=VLOOKUP($P$1,$A$2:$M$5,COLUMN()-14,FALSE)”,并向右拖动至AA2单元格,这样也可以获得图表所需要的数据源(图9)。

2007A-JHTB-9