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

Excel函数 2023-01-08

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

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

动态更新

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

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

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

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

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

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

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

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

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

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

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

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

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

切片展示

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

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

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

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

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

多层次

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

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

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

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

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

多视角

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

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

图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个定义名称，公式如下：

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