【原】用Excel数据验证制作动态图表

  文 前 推 荐   

用复选框控件制作动态图表

隐藏数据图表消失的解决方法

批量修改文件名

Excel跨文件查询与泄密

编按：

介绍可同时进行行筛选和列筛选的动态图表，其中列筛选通过数据验证制作的下拉菜单实现。

在销售分析会议上，小美经常需要根据领导的要求，快速生成指定月份和产品的销售数据对比图表。

就当前数据，用普通方法只能动态显示行数据（即月份），无法实现对列数据（产品）的动态筛选，如下图所示。

如何才能同时对行和列进行动态筛选呢？

筛选月份，可在月份列添加筛选按钮，然后勾选指定月份即可。产品筛选，则可用数据有效性制作下拉菜单来进行。最后根据月份、产品类型利用函数引用数据。下面介绍具体操作。

Step1 为月份添加筛选

定位到G1单元格输入公式“=A1”，下拉填充引用月份数据。接着选中G列，点击“数据/筛选”添加筛选按钮即可选择指定月份的数据了。

Step2 提取去重产品数据

产品的筛选通过数据验证下拉列表进行。定位到M1单元格输入公式“=UNIQUE(A1:K1,1,1)”，如此在M1:P1就只会出现H1:K1中未选择的产品名称，可以避免重复输入。

Step3 设置数据验证

定位到H1单元格，点击“数据/数据验证/设置/序列”，将来源设置为“=$M$1:$P$1”，即上述去重后的数据区域。然后选中H1向右填充到K1单元格。

Step4 引用产品数据

定位到H2单元格输入公式“=IFERROR(VLOOKUP($G2,$A$1:$E$7,MATCH(H$1,$A$1:$E$1,0)),"")”，向下、向右填充公式。先使用MATCH函数确定H1单元格在$A$1:$E$1中的位置，然后将其作为VLOOKUP函数引用列序数。这样在H1选择具体产品后，就可以引用其对应月份的销售数据。

Step5 生成图表

选中G1:K7区域，点击“插入/图表/柱形图”，插入一个柱形图。在H1:K1选择不同产品名称，在图表中就会自动增加产品的柱形图。

Step6 生成动态图表标题

定位到M2单元格输入公式“=TEXTJOIN("/",TRUE,H1:K1)&"销售对比"”，点击图表的标题，在地址栏输入“=M2”，完成动态标题的制作。

完成上述操作后，可以根据自己需要对图表进行美化。

现在，小美可以根据领导要求在G列筛选月份，在H1:K1选择不同产品类型即可。

用数据验证下拉菜单做动态图表就介绍到这。

当然你也可以用复选框来做，效果如下。

课件下载方式

扫码入群，下载本文教程配套的练习文件。

宠 粉 福 利

 2元领取：全套Excel技巧视频+200套模板