Excel进阶：动态图表条件排序巧实现[多图]

利用动态图表来表达数据，想表达哪些数据，点个交互按钮就可以完成。但如果想按指定条件对动态图表中的数据系列进行排序，又如何实现呢？其实，借助定义名称的函数公式产生的内存数组作为图表数据源，再一定程度地使用辅助区域，就可以实现动态图表排序（图1）。 1. 设置交互控件 在图表的数据源所在工作表的F2、F3、F4单元格中分别输入销售、任务、完成率，从而构建交互式下拉列表所需要的菜单内容。在工作表中选择“开发工具”选项卡，点击“插入”按钮“表单控件”下的“组合框”，在工作表合适位置按钮鼠标左键画出一个组合框控件，右击插入的组合框，选择“设置控件格式”，在弹出窗口的“控件”选项卡，数据源区域选择“F2:F4”，单元格链接选择“J1”，这样，当点击列表中的菜单时，J1中就会出现该菜单的索引值。再插入3个单选选项按钮，右击插入的单选按钮，选择“编辑文字”， 分别输入“正常”、“升序”、“降序”，再次右击第1个单选按钮，选择“设置控件格式”，在弹出对话框的“控制”选项卡中值处选择“选择”，单元格链接处选择“J2”，其他两个单选按钮的值处选择“未选择”，单元格链接处都选择“J2”，这样，当在这3个单选按钮间切换时，J2单元的值就会发生变化，在“1”、“2”、“3”这3个数之间进行切换（图2）。 小提示：控件的链接单元格也可根据需要选择其他的空白单元格。 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）。 3. 设置图表 将数据区域选定，插入柱形图，右击图表，选择“更改图表类型”，在弹出窗口选择“组合”，“完成率”处选择“带数据标记的折线图”，勾选其“次坐标”。选定图表中的折线图，右击选择“设置数据系列格式”，在“填充与线条”选项卡，线条中选择“无线条”，标记的“数据标记选项”中选择“内置”，类型处选择“菱形”，大小为30，纯色填充，50%透明度（图4）。 接下来，右击图表，选择“选择数据”，在弹出窗口的“图例项”处，选定“销售”，点击“编辑”，在系列值处输入“=Sheet1!xiaoshou”，选定“任务”，系列值更改为“=Sheet1!renwu”，选定“完成率”，系列值更改为“=Sheet1!wanchenglv”；在“水平(分类)轴标签”处，选定“A店”，点击“编辑”，在弹出窗口中输入“=Sheet1!dian”，其他标签的轴标签区域也都改成“=Sheet1!dian”。这样，图表与控件之间就有交互效果了（图5）。 4. 美化图表 选定图表中的完成率系列，添加数据标签，标签居中，右击标签，选择“设置数据标签格式”，在标签选项中，数字类别处选择“百分比”，小数位数1位；根据需要更改图例的位置，当然也可根据需要添加其他的数据标签。在J3单元格中输入“="各店 " & CHOOSE($J$1,$B$1,$C$1,$D$1) &IF($J$2=1,"正常",IF($J$2=2,"升序","降序")) & " 统计数据"”，然后选择图表标题，在公式编辑栏中输入“=Sheet1!$J$3”，这样图表标题就会跟随控件的选择而进行变化了（图6）。 最后，右击每个控件，选择“置于顶层”，然后将这些控件拖动到图表相应的位置就可以了。