VBA+公式 轻松制作动态图表

CFan2018-11-19 09:11应用 标签:图表 公式 制作 动态

在Excel中制作动态图表,传统的方法是利用表单控件与单元格链接,从而获取图表所需要数据。其实,不用控件,只需几句VBA代码,加上几个HYPERLINK公式,也可以轻松制作出动态图表。

1810A-DTTB-1

首先打开存放制作图表所需数据的工作表,选择“开发工具”选项卡,点击Visual Basic,在出现的VBA代码编辑窗口中,点击“插入→模块”,输入如下三行代码。

Function techart(rng As Range)

Sheet1.[J2]=rng.Value

End Function

1810A-DTTB-2

退出VBA窗口,在工作表的J2单元格任意输入数据源中已存在的矿泉水名称,如“百岁山”,在J3单元格输入“=HLOOKUP($J$2,B$2:G3,ROW()-1,FALSE)”,并下拉填充至所需要的单元格,如J14。选中J2:J14单元格数据区域,选择“插入”选项卡,插入折线图。右击图表中的折线系列,选择“设置数据系列格式”,在系列选项的“填充与线条”中,设置无线条,点击“标记”选项卡,数据标记选项下,选择“内置”,选择所需要的类型,设置好大小、填充及线条颜色等;右击图表系列,选择“添加数据标签”,设置好数据标签的位置、字体、大小等。选中图表,选择“设计”选项卡,添加图表元素,选择“线条→垂直线”,右击图表中添加的垂直线,选择“设置垂直线”,设置好所需要的颜色和粗细;删除图表中的网格线,设置好图表的背景色。调整好图表大小,并与相应单元格对齐。

1810A-DTTB-3

接下来,在L16单元格输入“=IFERROR(HYPERLINK(techart(B2)),"*"&B2&"*"&REPT(" ",35))”,在O16单元格输入“=IFERROR(HYPERLINK(techart(C2)),"*"&C2&"*"&REPT(" ",35))”,这样,单元格就都显示出了相应矿泉水的品牌名称。同理,在L17:O18单元格中输入相应公式以显示其他矿泉水品牌名称。最后,在L1单元格中输入“=CONCATENATE("某批发部 ",$J$2," 品牌矿泉水销售统计")”,选中L1:Q1单元格区域,对齐方式为“跨列居中”,设置好填充色及字体颜色、大小等,选中L16:O18单元格,设置好它们的填充色及文字颜色、大小等。

1810A-DTTB-4

这样,当鼠标点击单元格中的品牌名称时,图表发生了相应变化,图标标题等也发生了相应变化。动态图表制作完成。