5控件的组合。选中4个复选框控件，单击【格式】选项卡，选择【对齐】→【左对齐】+【纵向分布】，使得文本框快速对齐到适宜的位置。

单击【开发工具】选项卡，选择【插入】→【分组框】，在4个复选框控件的外围位置绘制分组框控件，并修改控件的标题为“选择项目”。设置完毕后，选择分组框与4个复选框，然后单击鼠标右键，单击【组合】选项，将这5个元素组合为一个整体（见图5-111）。

单击【插入】选项卡中的【文本框】，在插入的文本框中，编辑文字为“选择产品：”和“选择区域：”，设置文本框的字体、字号，并设置底纹颜色和边框均为无色。设置完毕后，将文本框与两个组合框控件进行组合（见图5-112）。

图5-111

至此，我们完成了动态图表中根据控件选择结果的不同，将控件的值绑定到单元格中的效果制作。而这些单元格的变化，在下一步中将作为绘制动态图表的参数来源，应用于具体的函数公式中，从而构建动态的制图数据源。这也是动态图表中能“动”的内核所在。

2制作绘图用的数据源

具体步骤如下。

1编制空白表。在a19：e31单元格区域中分别输入“月份”“销售成本”“销售毛利”“销售毛利率-平均值”“销售收入”等，并设置单元格边框的形式（见图5-112）。

2编写公式，将【汇总表】中制作的统计透视表的数据引用过来（见图5-113）。

图5-112

图5-113

【根据月份，查找3个产品的销售成本之和】

在b20单元格中输入公式（=vlookup（$a20，汇总表！$b：$t，2，0）+vlookup（$a20，汇总表！$b：$t，8，0）+vlookup（$a20，汇总表！$b：$t，14，0））。输入完成后，将其向下快速填充至b31单元格。

说明：vlookup中的第3个参数为返回值所在数据源区域中的列号（见图5-114）。

图5-114

同理，在c20单元格中输入公式（=vlookup（$a20，汇总表！$b：$t，2+2，0）+vlookup（$a20，汇总表！$b：$t，8+2，0）+vlookup（$a20，汇总表！$b：$t，14+2，0））。输入完成后，将其向下快速填充至c31单元格。

说明：vlookup中的第3个参数只是在b20公式的基础上+2，这是因为在数据源的【汇总表】中销售毛利所在的列号，是在销售成本后两列。

说明：若快速确认返回值所在数据源的列号，则可以在数据源上方，选中列标签后，从左向右拖曳鼠标。随着选中范围的增加，在鼠标的右下角，会出现一个【1048576rx14c】的提示，这代表着你已经选中了多少行（r）乘以多少列（c）。这样就避免了人工数列号的烦琐，提高了工作效率（见图5-115）。

图5-115

同理，在d20单元格中输入公式（=suvlookup（$a20，汇总表！$b：$t，2+3，0）+vlookup（$a20，汇总表！$b：$t，8+3，0）+vlookup（$a20，汇总表！$b：$t，14+3，0））3）。输入完成后，将其向下快速填充至d31单元格。

说明：vlookup中的第3个参数只是在b20公式的基础上+3，这是因为在数据源的【汇总表】中销售毛利率所在的列号，是在销售成本后3列。求和后，使用函数（su）3）使得计算的结果为三类产品的算数平均值。

输入公式后，选中d20：d31区域，单击【开始】选项卡中的【数字】，单击其右侧的小三角并选中【百分比】，快速完成单元格格式的设置。

同理，在e20单元格中输入公式=vlookup（$a20，汇总表！$b：$t，2+4，0）+vlookup（$a20，汇总表！$b：$t，8+4，0）+vlookup（$a20，汇总表！$b：$t，14+4，0）。输入完成后，将其向下快速填充至e31单元格，在此不做赘述。

3增加第一重嵌套，将函数公式关联上【产品类型】选择的结果（即c2单元格的值）。

修改b20单元格公式为复合公式：=if（or（$c$2=1，$c$2=4），vlookup（$a20，汇总表！$b：$t，2，0），0）+if（or（$c$2=2，$c$2=4），vlookup（$a20，汇总表！$b：$t，8，0），0）+if（or（$c$2=3，$c$2=4），vlookup（$a20，汇总表！$b：$t，14，0），0）。输入完成后，将其向下快速填充至b31单元格。

其中函数if（）和or（）用于与辅助列表中的复选框按钮形成联动，其中【4】表示为【合计】，也就是每个产品的数据都要参与计算。

同理，重复该步骤操作，依次修改“销售毛利”“销售毛利率-平均值”“销售收入”公式。

【销售毛利】

c20=if（or（$c$2=1，$c$2=4），vlookup（$a20，汇总表！$b：$t，2+2，0），0）+if（or（$c$2=2，$c$2=4），vlookup（$a20，汇总表！$b：$t，8+2，0），0）+if（or（$c$2=3，$c$2=4），vlookup（$a20，汇总表！$b：$t，14+2，0），0）

【销售毛利率-平均值】

d20=if（or（$c$2=1，$c$2=4），vlookup（$a20，汇总表！$b：$t，2+3，0），0）+if（or（$c$2=2，$c$2=4），vlookup（$a20，汇总表！$b：$t，8+3，0），0）+if（or（$c$2=3，$c$2=4），vlookup（$a20，汇总表！$b：$t，14+3，0），0）

【销售收入】

e20=if（or（$c$2=1，$c$2=4），vlookup（$a20，汇总表！$b：$t，2+4，0），0）+if（or（$c$2=2，$c$2=4），vlookup（$a20，汇总表！$b：$t，8+4，0），0）+if（or（$c$2=3，$c$2=4），vlookup（$a20，汇总表！$b：$t，14+4，0），0）

4关联产品区域。在前面的公式中，vlookup函数的第2个参数一直都是【汇总表！$b：$t】。而需要跟【地区组合框】联动，就需要对应地引用不同的数据源区域作为vlookup函数的查找引用数据源区域了。

首先需要根据c6单元格的值的不同，将每个地区对应的单元格区域函数编写出来。然后，把这个多重嵌套的函数合并到前面已经设置完成的函数公式中。

选择任意一个空白单元格，如：在f23单元格中输入公式=if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42））。编写完毕后，将该公式复制一份，然后对本表中的汇总表！$b：$t进行查找、替换（见图5-116）。

图5-116

查找内容：汇总表！$b：$t（见图5-117），替换为if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42））。替换后，b20单元格的公式结果如下：

b20=if（or（$c$2=1，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），2，0），0）+if（or（$c$2=2，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），8，0），0）+if（or（$c$2=3，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），14，0），0）

图5-117

至此，我们完成了绘图所用数据源的引用与控件的关联。

同理，更改“销售毛利”“销售毛利率-平均值”“销售收入”的公式。

【销售毛利】

c20=if（or（$c$2=1，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），2+2，0），0）+if（or（$c$2=2，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），8+2，0），0）+if（or（$c$2=3，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），14+2，0），0）

【销售毛利率—平均值】

d20=suif（or（$c$2=1，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），2+3，0），0）+if（or（$c$2=2，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），8+3，0），0）+if（or（$c$2=3，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），14+3，0），0））3

【销售收入】

e20=if（or（$c$2=1，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），2+4，0），0）+if（or（$c$2=2，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），8+4，0），0）+if（or（$c$2=3，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），14+4，0），0）

若要与复选框内容形成联动，则修改公式如下。

【销售成本】

b20=if（$c$11=true，if（or（$c$2=1，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），2，0），0）+if（or（$c$2=2，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），8，0），0）+if（or（$c$2=3，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），14，0），0），＂＂）

【销售毛利】

c20=if（$c$12=true，if（or（$c$2=1，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），2+2，0），0）+if（or（$c$2=2，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），8+2，0），0）+if（or（$c$2=3，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），14+2，0），0），＂＂）

【销售毛利率-平均值】

d20=if（$c$13=true，suif（or（$c$2=1，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），2+3，0），0）+if（or（$c$2=2，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），8+3，0），0）+if（or（$c$2=3，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），14+3，0），0））if（$c$2=4，3，1），＂＂）

【销售收入】

e20=if（$c$10=true，if（or（$c$2=1，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），2+4，0），0）+if（or（$c$2=2，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），8+4，0），0）+if（or（$c$2=3，$c$2=4），vlookup（$a20，if（$c$6=1，汇总表！$b$7：$t$18，if（$c$6=2，汇总表！$b$19：$t$30，汇总表！$b$31：$t$42）），14+4，0），0），＂＂）

至此，完成了根据控件变化而变化的动态数据源，下一步就开始正式的商务图表绘制工作了。

3绘制动态图表

具体步骤如下。

1创建图表。选择绘图所用数据源a19：e31单元格区域，单击【插入】选项卡，之后依次单击【推荐的图表】→【所有图表】→【组合】，将【系列名称】中的销售毛利率-平均值的图表类型修改为【带数据标记的折线图】并勾选【次坐标轴】复选框，将【系列名称】中销售收入的图表类型修改为【簇状柱形图】（见图5-118）。

图5-118

2组合图表及控件元素。将图表标题修改为“年度销售分析”，单击【设计】选项卡，之后单击【添加图表元素】→【图例】→【右侧】（见图5-119）。

将“制作绘图用的控件组”中制作的复选框放置于图表区域合适的位置，并将其显示层次置于顶层。

3美化图表。选中已经插入的组合图，单击【图表工具】→【格式】选项卡，之后选择【形状填充】→【纹理】，选择【蓝色面巾纸】的样式（见图5-120）。

图5-119

图5-120

单击【设计】选项卡中的【选择数据】，在打开的【选择数据源】对话框的【图例项（系列）】区域中，调整销售收入的显示顺序，使其在图表区域中呈现于第一列（见图5-121）。

图5-121

然后参考前面关于图表美化的相关方法，如填充颜色、边框、标记点的设置技巧等对整个动态图表进行细节的美化（见图5-90）。这里不用固化于本书呈现的视觉效果中，只要大家掌握了动态图表的制作技巧，就能够绘制出各式各样“高大上”的动态图表，甚至可以绘制第6章将要介绍的bi看板。

当然，工作是具有延续性的，如果你们公司的领导确认了每年度的业务分析图表样式，则你所绘制的图表必须符合这种统一的标识规范。这种图表也许还有很多需要改进的地方，但建议先按照统一的规范要求完成工作。之后额外给领导提供一个b计划，也就是经过你个人设计的图表报告，这样会更好。总之，要在数据准备、工作按要求完成的基础上再做提升。