简单的数据可以用图表表示,但更复杂的数据不能用图表表示。如果您将图表设置为动态和可钻取的,当您单击交互式控件时,整个图表将相应地发生变化。
当你点击总图中的系列,会得到与这个系列相关的其他数据,然后用其他数据生成相应的子图,问题就可以解决了。这种效果可以通过使用控件、少量的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的引用位置为"=INDEX(Sheet1!$A$1:$A$41,N(IF({1},SMALL(99*(MATCH(Sheet1!$A$2:$A$41,Sheet1!$ A $ 1:A $ 41,)行(Sheet1!$2:$41))行(Sheet1!$2:$41),行(间接(' Sheet1!1:'SUM(1/COUNTIF(Sheet1!$A$2:$A$41,Sheet1!$ A $ 2:$ A $ 41))))))))”;lb_22、lb_33的引用位置与lb_11的类似,
创建一个名为“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决定哪个求和结果作为主图表的数据源。
(2)建立子图表数据源
新建一个名为“lb_tmp_tmp”的名字,引用位置为“=IF(Sheet1!$G$4=' month 'lb_11,IF(Sheet1!$G$4=' region 'lb _ 22,lb _ 33))";
引用位为"=SUMIFS(Sheet1!2美元:41美元,如果(Sheet1!$G$1=1,Sheet1!$ A $ 2:A $ 41,如果(Sheet1!$G$1=2,Sheet1!$B$2:$B$41,第一张!$C$2:$C$41))、Sheet1!$G$3,如果(Sheet1!$G$4='月份,Sheet1!$ A $ 2:A $ 41,如果(Sheet1!$G$4='地区,Sheet1!$B$2:$B$41,第一张!$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 statistics ')”,选择图表的标题。
输入"=Sheet1!5美元.这样,图表的标题会随着控件的选择而改变。不一样。