Excel进阶:动态图表条件排序巧实现


利用动态图表来表达数据,想表达哪些数据,点个交互按钮就可以完成。但如果想按指定条件对动态图表中的数据系列进行排序,又如何实现呢?其实,借助定义名称的函数公式产生的内存数组作为图表数据源,再一定程度地使用辅助区域,就可以实现动态图表排序(图1)。

《Excel进阶:动态图表条件排序巧实现》


1. 设置交互控件

在图表的数据源所在工作表的F2、F3、F4单元格中分别输入销售、任务、完成率,从而构建交互式下拉列表所需要的菜单内容。在工作表中选择“开发工具”选项卡,点击“插入”按钮“表单控件”下的“组合框”,在工作表合适位置按钮鼠标左键画出一个组合框控件,右击插入的组合框,选择“设置控件格式”,在弹出窗口的“控件”选项卡,数据源区域选择“F2:F4”,单元格链接选择“J1”,这样,当点击列表中的菜单时,J1中就会出现该菜单的索引值。再插入3个单选选项按钮,右击插入的单选按钮,选择“编辑文字”, 分别输入“正常”、“升序”、“降序”,再次右击第1个单选按钮,选择“设置控件格式”,在弹出对话框的“控制”选项卡中值处选择“选择”,单元格链接处选择“J2”,其他两个单选按钮的值处选择“未选择”,单元格链接处都选择“J2”,这样,当在这3个单选按钮间切换时,J2单元的值就会发生变化,在“1”、“2”、“3”这3个数之间进行切换(图2)。

《Excel进阶:动态图表条件排序巧实现》

小提示:控件的链接单元格也可根据需要选择其他的空白单元格。

2. 构建图表数据源

图表的数据源是根据控件所链接单元格的值决定的,是由定义名称所建立的。选择“公式”选项卡,点击“名称管理器”,在弹出窗口点击“新建”,名称处输入“xztj”,引用位置处输入“=CHOOSE($J$1, $B$2:$B$7, $C$2:$C$7,$D$2:$D$7)”;再新建,名称为“pxlx”,引用位置处为“=CHOOSE($J$2,ROW($2:$7),SMALL(xztj /1%%%+ROW($2:$7),ROW($1:$6)),LARGE(xztj /1%%%+ROW($2:$7),ROW($1:$6)))”;再新建,名称为“hanghao”,引用位置处为“=–RIGHT(pxlx,2)”;再新建,名称为“dian”,引用位置处为“=T(OFFSET($A$1,hanghao-1,))”;再新建,名称为“xiaoshou”,引用位置处为“=N(OFFSET($B$1,hanghao-1,))”;再新建,名称为“renwu”,引用位置处为“=N(OFFSET($C$1,hanghao-1,))”;再新建,名称为“wanchenglv”,引用位置处为“=N(OFFSET($D$1,hanghao-1,))”。这样,图表所需要的数据源就构建完成了(图3)。

《Excel进阶:动态图表条件排序巧实现》

3. 设置图表

将数据区域选定,插入柱形图,右击图表,选择“更改图表类型”,在弹出窗口选择“组合”,“完成率”处选择“带数据标记的折线图”,勾选其“次坐标”。选定图表中的折线图,右击选择“设置数据系列格式”,在“填充与线条”选项卡,线条中选择“无线条”,标记的“数据标记选项”中选择“内置”,类型处选择“菱形”,大小为30,纯色填充,50%透明度(图4)。

《Excel进阶:动态图表条件排序巧实现》

接下来,右击图表,选择“选择数据”,在弹出窗口的“图例项”处,选定“销售”,点击“编辑”,在系列值处输入“=Sheet1!xiaoshou”,选定“任务”,系列值更改为“=Sheet1!renwu”,选定“完成率”,系列值更改为“=Sheet1!wanchenglv”;在“水平(分类)轴标签”处,选定“A店”,点击“编辑”,在弹出窗口中输入“=Sheet1!dian”,其他标签的轴标签区域也都改成“=Sheet1!dian”。这样,图表与控件之间就有交互效果了(图5)。

《Excel进阶:动态图表条件排序巧实现》

4. 美化图表

选定图表中的完成率系列,添加数据标签,标签居中,右击标签,选择“设置数据标签格式”,在标签选项中,数字类别处选择“百分比”,小数位数1位;根据需要更改图例的位置,当然也可根据需要添加其他的数据标签。在J3单元格中输入“=”各店 ” & CHOOSE($J$1,$B$1,$C$1,$D$1) &IF($J$2=1,”正常”,IF($J$2=2,”升序”,”降序”)) & ” 统计数据””,然后选择图表标题,在公式编辑栏中输入“=Sheet1!$J$3”,这样图表标题就会跟随控件的选择而进行变化了(图6)。

《Excel进阶:动态图表条件排序巧实现》

最后,右击每个控件,选择“置于顶层”,然后将这些控件拖动到图表相应的位置就可以了。


点赞

发表评论

电子邮件地址不会被公开。 必填项已用*标注