简单的数据可以用图来表示,但复杂的数据也可以用图来表示。如果图表是使用动态可钻取数据效果创建的,则单击交互式控件会相应地更改整个图表,并且单击整个图表中的系列将检索与该系列相关的其他数据。使用生成相应的子图,问题就解决了。这种效果可以使用控件、少量VBA代码和一些公式来实现(图1)。
1. 设置交互控件
在工作表的F1:F3 中输入月份、地区和产品,在H1:H2 中输入地区和产品,在I1:I2 中输入月份和产品,在J1 中输入月份和地区。 J2。
构建下拉列表1
点击“开发者工具插入表单控件组合框”,在适当位置绘制组合框控件,并将组合框的数据源区域设置为F1:F3,单元格链接设置为G1。
构建下拉列表2
点击“公式名称管理器新建”,名称为“z_cd”,参考位置为“=CHOOSE(Sheet1!$G$1,Sheet1!$H$1:$H$2,Sheet1!$I$1:$I$2, Sheet1 !$ J$1:$J$2)\”; 绘制另一个组合框控件,数据源区域为“z_cd”,单元格链接为G2。
在单元格G4 中键入=INDIRECT(CHOOSE($G$1,\\\’H\\\’,\\\’I\\\’,\\\’J\\\’) $G$2)(图2)。
2.创建数据钻取VBA代码
点击“开发工具Visual Basic”进入VBA编辑器,点击“插入类模块”,选择插入的“类1”,选择“视图属性窗口”,将类名改为“CEventChart”。点击该类进入编辑窗口,输入如图3所示的代码(图3)。
接下来点击“插入模块”,双击“模块1”,在代码编辑窗口中输入如图4所示的代码(图4)。
接下来,双击“ThisWorkBook”,在右侧编辑窗口中输入如图5所示的代码(图5)。
现在,当您单击图表中的某个系列时,该系列名称将显示在单元格G3 中。
暗示:
上述代码也可以通过网盘下载(链接:https://pan.baidu.com/s/1vZhfg86pAHg5tc4UB9E4lw 密码:2zba)直接复制粘贴使用。
3. 构建图表数据源
根据上述控件的当前索引值检索图表的数据源。
构建主图表数据源
创建三个新名称,分别命名为lb_11、lb_22 和lb_33。 lb_11 的参考位置为\”=INDEX(Sheet1!$A$1:$A$41,N(IF({1},SMALL(99*(MATCH(Sheet1!$)\”). A$2:$A$41,Sheet 1! $A$1:$A$41,)
创建新名称“lb_tmp”并在参考位置输入“=CHOOSE(Sheet1!$G$1,lb_11,lb_22,lb_33)”。
创建新名称“sum_1”,并在参考位置输入“=SUMIF(Sheet1!$A$2:$D$41,lb_11,Sheet1!$D$2:$D$41)”。
创建新名称“sum_2”,并在参考位置输入“=SUMIF(Sheet1!$B$2:$D$41,lb_22,Sheet1!$D$2:$D$41)”。
创建新名称“sum_3”,并在参考位置输入“=SUMIF(Sheet1!$C$2:$D$41,lb_33,Sheet1!$D$2:$D$41)”。
创建新名称“sum_tmp”,并在参考位置输入“=CHOOSE(Sheet1!$G$1,sum_1,sum_2,sum_3)”。
其中,lb_11、lb_22 和lb_22 均从每列中获取唯一值以形成数组。 lb_tmp 确定用作主图表的类别图例的数组。 sum_1、sum_2 和sum_3 分别对相应的量求和。 sum_tmp 确定将每个类别数组的哪个求和结果用作主图表的数据源。
子图数据源构建
创建一个新名称“lb_tmp_tmp”,参考位置为“=IF(Sheet1!$G$4=\\\’Month\\\’,lb_11,IF(Sheet1!$G$4=\\\’Region\\\’,lb_22,lb_33))”将做。
创建一个新名称“sum_tmp_tmp”。参考位为“=SUMIFS(Sheet1!$D$2:$D$41,IF(Sheet1!$G$1=1,Sheet1!$A$2:$A$41,IF(Sheet1!$G)”。$1=2,Sheet1 !$B$2:$B$41,工作表1!$C$2:$C$41)),工作表1!$G$3,IF(工作表1!$G$4=\\\’月份\\\’,工作表1!$A$2:$ A $41,IF( Sheet1!$G$4=\\\’区域\\\’,Sheet1!$B$2:$B$41,Sheet1!$C$2:$C$41)),lb_tmp_tmp)”;
其中,lb_tmp_tmp 确定用作子图类别图例的数组。 sum_tmp_tmp 根据主图表列表和子图表列表的选择对数量进行求和,并充当子图表的数据源。
4.插入并配置图表
插入两个饼图。右键单击第一个饼图,选择“选择数据”,在弹出窗口中单击“图例项”的“编辑”,将“=Sheet1!sum_tmp”设置为“水平(类别)”的系列值。进入。选择“轴标签”,点击“编辑”,在弹出窗口中输入“=Sheet1!lb_tmp”。右键单击第二个饼图,选择“选择数据”,在弹出窗口中单击“图例项”的“编辑”,将“=Sheet1!sum_tmp_tmp”设置为“水平(类别)”的系列值。进入。选择“轴标签”,点击“编辑”,在弹出窗口中输入“=Sheet1!lb_tmp_tmp”。
最后,将每个下拉列表框放在顶层,将其拖放到图表上相应的位置,选择单元格G5,然后键入“=CONCATENATE(G3,\\\’Each\\\’,G4,\\\’Sales \\ \’)\”选择图表标题,然后在公式编辑栏中输入=Sheet1!$G$5。这样,图表的标题将根据控件的选择而变化。不,是一样的。
本文和图片来自网络,不代表火豚游戏立场,如若侵权请联系我们删除:https://www.huotun.com/game/660641.html