Excel动态图表应用.docVIP

/50281/484440 复杂的Excel动态图表应用1 首先看一下数据源： 根据分析需求，要制作可以A列、B列、月份选择的动态数据表。 例如某月中某个城市各个组别的销售图表包含柱形图及饼图，柱形图用以突出各个项目之间的对比情况，饼图以用对各个项目在整个数据中的占比情况，完成后效果如下： ? ??某个城市某个组别在各个月的销售图表： 本实例的图表重点在于两个选择项的同时对下拉列表的控制互动。为了便于理解及添加新的应用要求，因此在【名称管理器】中新建名称来解决这类问题。 此次实例中的名称管理器中的新建名称公式如下： 如果觉得有些生涩难懂，可以参考我之前的博文：Excel动态图表应用，与本文类似但应用的名称公式较为简单。 这些名称的主要功能是快速地、动态地赋值给图表的【系列】及【数据】、【标签】等。 名称管理器中的部分名称解释： （1）按钮_分公司：=图表!$M$3 ??????? 将三个分公司按钮分为一组，设置其属性【控制】单元格链接“图表!$M$3”。按钮_下拉列表分类链接至M1。 （2）标签：=CHOOSE(图表!$M$1,CHOOSE(图表!$M$3,组别_北京,组别_上海,组别_广州),月) ?????? 为图表【水平（分类）轴标签】做标识。CHOOSE选择函数是本动态图表的核心部分，“北京、上海、广州”这三个选项会链接至M3单元格，因此可以用M3单元格来控制标签标识。 （3）系列：=CHOOSE(图表!$M$1,系列_月,系列_组别) ?????? 服务于图表的【图例项（系列）】。它与其他几个名称中的函数类似嵌套了CHOOSE函数。标签和系列在图表中的位置【插入】选择图表类型创建图表，然后点击右键【选择数据】。如下图所示： ??????? 当选择编辑时需要输入完整的工作簿路径： （4）系列_月：=OFFSET(CHOOSE(图表!$M$3,区域_北京,区域_上海,区域_广州),0,图表!$M$2,COUNTA(CHOOSE(图表!$M$3,组别_北京,组别_上海,组别_广州)),1) ?????? 这个函数比较长，这里稍微解释一下，如果对函数构成不了解，可以按F1帮助一下。 Offset函数可以利用偏移量得到新的引用。范围根据选择按钮的链接单元格M3为基础，用CHOOSE函数对其进行选择置顶区域范围。后面的COUNTA函数同样通过按钮链接选择组别，并对其计数。 通过对名称的设置，就能很好的为图表服务，动态的体现数据的意义。当然也可以通过其他函数来实现同样的效果，本文下面有此图表应用附件，如有兴趣欢迎交流。 多坐标轴图表让数据更生动 在Excel中，我们常常用到图表。因为它可以直观的让数据更生动，更容易让人记住。直接生成的图表往往生硬，不合我们想表达的内容。有时候就需要做点小改变了，本博文简单的介绍下多坐标轴的妙用。让数据更生动。 数据源如下： ? 直接根据数据源创建出来的图表如下。可见1处显示的内容根本无法表达AB组的对比情况，因此点击2处的“切换行/列”。 ? ? 得到图表如下，但可以看到由于只有一个纵坐标轴，发货次数明细超越发货延时很多，无法显示延时的真实情况。 故此选中图表中发货延时AB组数据，然后按键盘“Ctrl+1”弹出“设置数据系列格式”窗口。选中“系列选项”，分别对主次坐标进行“分隔”、“无间距”进行设置。 设置完成后得到下图，AB小组的发货次数及发货延时对比图表就这样完成了。 如果觉得图表有点单调的话，那你可以选择你喜欢的“图表样式”、“设置图表区格式”进行美化，最终得到下面的图表。 统计时间出现次数 某表内有电话拨打时间，需要统计8:00至11:59、12:00至16:59、17:00至7:59这三个时间段的拨打次数填入D2至D4单元格内。 此时我们B2单元格，输入公式： =HOUR(A2) 。然后下拉填充至单元格A15，建立辅助列。 然后在D2中输入公式： =COUNTIF($B$2:$B$15,lt;12)-COUNTIF($B$2:$B$15,lt;7) 8:00至11:59——12点前的拨打次数减去小于7点的拨打次数，得到6次。? D3中输入公式： =COUNTIF($B$2:$B$15,lt;17)-COUNTIF($B$2:$B$15,lt;12) 12:00至16:59——17点前拨打的次数减去小于12点的次数，得到5次。 D4中输入公式： =COUNTIF($B$2:$B$15,lt;24)-COUNTIF($B$2:$B$15,lt;17)+COUNTIF($B$2:$B$15,lt;8) 17:00至7:59——24点前拨打的次数减去小于17点的次数，加上0点至7点拨打的次数，得到3次。 vlookup函数简单应用“合并计费” 2011-01-24 00:23:29 标