Excel图表和函数公式高级应用:定义名称

定义名称是Excel图表和函数公式的一类高级应用,此类应用完全脱离工作表的单元格存储限制,是运行在Excel后台的一组逻辑计算或单元格区域引用。其可以被图表和其他函数直接调用,使用Excel定义名称制作交互式图表最为常见,也是交互式图表中最为灵活的一类应用。

动态更新

图14.2-1是图14.1-9完全定义名称的演绎,没有使用任何辅助单元格区域。这种方式的好处是可以更好地组织工作表的页面布局,并减少由于鼠标误操作带来的错误,但同时该方式也增加了制作难度,因为调试和修改这些定义名称并非易事。

完全利用定义名称以行为单位制作的交互图表

图14.2-1 完全利用定义名称以行为单位制作的交互图表

图14.2-1案例动态选择图表的源数据区域以行为单位,图14.2-2的案例则是一个以列为单位的案例,这两个案例的唯一不同是OFFSET函数中:上(下)偏移的行数、左(右)偏移的列数,以及返回引用区域的行/列数的赋值。

完全利用定义名称以列为单位制作的交互图表

图14.2-2 完全利用定义名称以列为单位制作的交互图表

图14.2-2的触发器此处使用的并非数据有效性,而是工作表控件,这种控件有别于窗体控件,工作表控件可以直接通过关联单元格实现触发驱动,而窗体控件则完全需要使用VBA代码来驱动。图14.2-3列出了在不同Excel版本中的这两种控件,❶是工作表控件,❷是窗体控件。Excel 2003调用这两个菜单,需要鼠标移至菜单栏空白处,鼠标右键分别勾选“窗体”和“控件工具箱”;Excel 2007和2010只需在Excel选项的自定义功能区中勾选开发工具即可。

交互图表使用的控件触发器

图14.2-3 交互图表使用的控件触发器

图14.2-4是图14.2-2案例的组合框触发器属性对话框。调用该对话框只需鼠标右键选中控件,并在弹出菜单中选取“设置控件格式”即可。在该对话框中的数据源区域对应于显示在下拉选框中的项目,此处可使用定义名称;单元格链接对应于下拉选框响应鼠标选取项的数值序号存储单元格。工作表控件也可指定宏,在单击控件时可触发执行VBA宏代码。

工作表控件组合框的属性对话框

图14.2-4 工作表控件组合框的属性对话框

当熟悉了定义名称和工作表控件,就可以将这两者结合起来使用,并在图表源数据中引用这些定义名称。此时图表就变得动态交互了,整个过程其实并不复杂,关键在于Excel函数公式的构建。

切片展示

大多数情况下,使用Excel制作交互式图表和切豆腐一样。图14.2-5案例的原始数据区就是一个具有代表意义的交互图表,鼠标每选取一项,图表上蓝色折线即会高亮标注此项代表的数据。此案例中所有数据都被按行放置到了图表中,以方便比较,但由于焦点只有一个,整个图表的呈现效果并不凌乱。

数据切片展示的交互式图表

图14.2-5 数据切片展示的交互式图表

这个案例的制作并不复杂,制作过程说明如下:

  1. 选择数据区,数据产生在行,生成折线图。
  2. 将这12个系列依次设置线形为最细,颜色为淡灰色,数据标记为无线无填充。
  3. 定义“名称”和“Y值”两个定义名称,公式详见图14.2-5左侧表格下方。
  4. 额外添加一个系列,系列SERIES公式见图14.2-5右侧下方箭头所指位置。
  5. 添加一个列表框工作表控件,数据源区域为“№”列数值,单元格链接为控件下方的H17单元格,选定类型为单选。

多层次

更多的情况是:我们所面对的数据,往往并非单纯的一个层次,可能的情况为多个层次组成。图14.2-6案例的原始数据就是由大类和小类两层结构组成的案例,该案例是某公司不同产品月度销售数据的动态展示图表,触发驱动采用了两个工作表组合框控件,来分别管理不同的层次结构。

多层次定义名称交互图表

图14.2-6 多层次定义名称交互图表

这个图表的制作过程并不复杂,但是相对较为繁杂。关键的部分是定义名称,首先图表系列的引用数据采用的是两层OFFSET函数来响应触发选择,其次是第2层触发使用的工作表组合框控件,其数据源区域为定义名称赋值引用,而非单元格引用。以下是详细制作步骤:

  1. 选择数据区行表头和下方第一行单元格,数据产生在行生成柱形图,并美化为图14.2-6右上侧图表样式。
  2. 将横坐标设置为分类坐标,并设置坐标格式为:[=39814]yyyy”-“m;”‘”m。
  3. 定义“Data”和“DataList”两个定义名称,公式详见图14.2-6右侧最下方。
  4. 修改图表系列SERIES公式为图14.2-6橙红色箭头所指位置。
  5. 添加两个组合框工作表控件,第1个控件数据源区域为数据区下方的“分组”列数值,单元格链接为D19单元格;第2个控件数据源区域为定义名称“DataList”,单元格链接为D20单元格。

多视角

图14.2-6案例亦可能面临如下的使用情况,即除了表达横向比较外,也可能会有单个月份的产品纵向比较的需求。此时等于在图14.2-6案例基础上又增加了1个比较维度,其为“月份”和“具体月份”两个层次,且这个维度表达在图表上必须为横向比较的条形图表,图14.2-7案例右上角的图表即是该需求的实现。

多层次多角度定义名称交互图标

图14.2-7 多层次多角度定义名称交互图标

这个图表的制作过程建立在图14.2-6案例实现基础上,额外增加了一个图表系列来实现纵向比较。由于是柱形和条形图的组合,因此该图表是一个主次坐标图表,在处理数值轴坐标时,这个案例使用了XY散点来模拟,否则效果不尽如人意。当在第一个下拉列表框中单击“月份”,则显示条形图表系列,其余选项为柱形系列,第2个下拉列表框亦跟着变更选项为月份列表。此处当显示条形系列时,为了将柱形系列隐藏,柱形系列的分类标志和数值指向了空单元格引用。以下是制作方法:

1.选择数据区行表头和下方第一行单元格,数据产生在行生成柱形图,并美化为图14.2-6右上侧图表样式。

2.将横坐标设置为分类坐标,并设置坐标格式为:[=39814]yyyy”-“m;”‘”m。

3.新增“月份”、“辅助X1”、“辅助Y1”、“辅助X2”、“辅助Y2”5个辅助列,涉及公式如下:

“辅助X1”:=0.5+7/5*(ROW()-19)

“辅助Y1”:=IF($B$19=4,25,7)

其他的参照图14.2-7设置即可。

4.分别定义“AItem”、“BItem”、“ItemList”、“AData”、“BData”、“Xdata”、“Ydata”、“XErrData”和“YErrData”9个定义名称,公式如下:

5.修改柱形图表系列SERIES公式为:

6.新增图表系列,并更改系列图表类型为条形图,修改图表系列SERIES公式详见图14.2-7橙红色箭头所指位置。

7.将新增图表系列的填充色如图14.2-7右上侧的图表样式美化。

8.再新增一个XY散点图表系列,用来模拟数值轴刻度。该图表系列的系列公式为:

具体使用数据见“辅助X1”、“辅助Y1”。“辅助X2”、“辅助Y2”两个辅助列分别用来设置XY散点误差线X的正误差值和误差线Y的负误差值,依次使用“XErrData”和“YErrData”两个定义名称。

9.添加两个组合框工作表控件,第1个控件数据源区域为数据区下方的“分组”列数值,单元格链接为B19单元格;第2个控件数据源区域为定义名称:“DataList”,单元格链接为B20单元格。

该案例的制作步骤相对繁杂,读者需要花些时间来理解定义名称和各个系列的设置。以上的过程仅是一个非常简单的描述,具体的实际操作,需要读者参照案例,反复演练学习。

提示


定义名称的命名字符需注意:

1)不能以数字开头,或单元格地址、以数字作为名称,如2Data或B3都不可以。字符中不可包含字母R、C、r、c,因为和单元格的R1C1引用样式冲突。名称中不能包含空格,但可用下划线或点号代替,其它非字符尽量避免使用。

2)字符最大不能超过255个字符。

3)勿使用保留字段,比如:Print_Titles和Print_Area。

发布者

Excel22

专为职场萌新准备的免费全面的Excel入门及提高学习网站,也可作为Excel老司机杂耍各种函数的宝典 —— Excel22.com 网址超好记

发表评论

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