是XLM，不是流行的XML！

XLM宏函数非常“古老”，据说是Excel 5之前用于编程控制Excel的“语言”，数百个XLM宏函数提供了Excel的几乎全部功能。

但是，自从在Excel 5中引入更易学习且更强大的VBA后，XLM宏函数被VBA所取代，成为编程控制Excel的主要语言。

然而，XLM宏函数仍然能够在迄今为止的所有Excel版本中使用，并且有些功能似乎还必须要借助于XLM宏函数。由于XLM宏函数出现在Excel 5之前，因此有时也称之为Excel 4 XLM宏函数。

下面举几个使用XLM宏函数的例子。

在命名公式中使用XLM宏函数

在将公式定义为名称时，可以在公式中使用XLM函数，这可以实现通常要使用VBA才能实现的功能。注意，通常的工作表公式中不能使用XLM函数。

示例1：列出指定目录下的文件

在工作簿中创建命名公式。

单击“公式”选项卡“定义名称”命令，在“新建名称”对话框中输入以下内容：

名称：FileList

引用位置：=FILES(Sheet1!$A$1)

其中，FILES是一个XLM宏函数，可以指定一个带有文件说明的目录路径作为参数，用于返回该目录下符合文件说明的文件名组成的数组。

在Sheet1的单元格A1中，输入目录路径和文件说明，在任意列（本例中为列C），从第1行开始，输入公式：

=INDEX(FileList,ROW())

然后下拉该单元格至公式值返回#REF!，此时表明文件夹中的文件已找完，如下图1所示。

图1

现在，修改工作表Sheet1单元格A1中的值代表的文件夹，列C中的数据自动更新。

示例2：读取单元格背景色

在工作簿中创建命名公式。

单击“公式”选项卡“定义名称”命令，在“新建名称”对话框中输入以下内容：

名称：CellColor

引用位置：=GET.CELL(63,Sheet2!A1)+NOW()*0

其中，GET.CELL是一个XLM宏函数，可以获取关于单元格的各种信息，其中包括单元格背景填充色的索引值。

注意，在“引用位置”框所输入的公式中，由于是相对当前单元格左侧的单元格，因此在定义名称时，选取工作表Sheet2单元格B1。公式中的NOW()*0确保Excel每次重新计算时该名称公式也会重新计算。

获取工作表Sheet2的列A中单元格背景色的公式如图2所示。

图2

若数值单元格带有背景色，现在要汇总某背景色所在的单元格中的值，例如下图3所示的工作表，要计算橙色背景单元格的数值之和。

图3

在列B中使用公式：=CellColor获取列A中相应单元格的背景色索引值，在单元格E1中输入想要求和的单元格背景色索引值，在单元格E3中输入公式：

=SUMIF(B1:B8,”=” & E1,A1:A8)

示例3：获取工作表名

3-1 在工作簿中创建命名公式，以获取工作簿中所有的工作表名。

单击“公式”选项卡“定义名称”命令，在“新建名称”对话框中输入以下内容：

名称：AllSheets

引用位置：=GET.WORKBOOK(1+0*NOW())

该名称将获取工作簿中所有工作表名，即其值为工作簿中所有工作表名组成的数组。如下图4所示，在工作表单元格B1中输入公式：

=INDEX(AllSheets,ROW())

下拉至出现#REF!值，表明已列出全部的工作表名。

图4

3-2 在工作簿中创建命名公式，以获取当前单元格所在的工作表名。

单击“公式”选项卡“定义名称”命令，在“新建名称”对话框中输入以下内容：

名称：ThisSheet

引用位置：=GET.CELL(32+0*NOW(),INDIRECT(GetRC,FALSE))

注意，在“引用位置”公式中的INDIRECT(GetRC,FALSE)提取使用该名称的单元格。

接着，定义名称：GetRC

引用位置：=SUBSTITUTE(REFTEXT(!$A$1),1,””)

确定正在使用的行和列的字母。

GET.CELL(32,…)获取包含使用上面的INDIRECT提取的单元格所在的工作表的名称，包含有工作簿名。例如，在工作簿ExcelReveal07.xlsm工作簿的工作表Sheet5任一单元格中，输入公式：=ThisSheet，其结果是：[ExcelReveal07.xlsm]Sheet5，如图5所示。

图5

3-3 在工作簿中创建命名公式，以获取当前工作表之前（即左侧）的工作表名。

单击“公式”选项卡“定义名称”命令，在“新建名称”对话框中输入以下内容：

名称：PreviousSheet

引用位置：=INDEX(AllSheets,MATCH(ThisSheet,AllSheets,0)-1)

其中，MATCH函数获取当前工作表的索引值，减1得到当前工作表左侧工作表的索引值。然后，INDEX函数获取该工作表的名称。如下图6所示。

图6

3-4 在工作簿中创建命名公式，以获取当前工作表之后（即右侧）的工作表名。

单击“公式”选项卡“定义名称”命令，在“新建名称”对话框中输入以下内容：

名称：NextSheet

引用位置：=INDEX(AllSheets,MATCH(ThisSheet,AllSheets,0)+1)

3-5 下面的公式获取当前工作表之前的工作表单元格A1中的值：

=INDIRECT(“‘” & PreviousSheet& “‘!” & CELL(“address”,A1))

下面的公式获取当前工作表之后的工作表单元格A1中的值：

=INDIRECT(“‘” & NextSheet& “‘!” & CELL(“address”,A1))

示例4：给命名公式传递参数

在工作簿中创建命名公式。

单击“公式”选项卡“定义名称”命令，在“新建名称”对话框中输入以下内容：

名称：Myref

引用位置：

=MID(GET.CELL(6,INDIRECT(GetRC,FALSE)),FIND(“ROW(“,GET.CELL(6,INDIRECT(GetRC,FALSE)))+4,FIND(“)”,GET.CELL(6,INDIRECT(GetRC,FALSE)))-FIND(“ROW(“,GET.CELL(6,INDIRECT(GetRC,FALSE)))-4)

Myref在公式中查找“ROW(”并接受找到的位置与第一个闭括号之间的全部文本作为有效的单元格引用，即评估当前单元格中的公式字符串，提取一个单元格地址。

注意，在公式中使用了上文创建的名称GetRC。

下面是一些可能与命名公式Myref结合使用的一些示例。定义名称：

名称：IsFormula

引用位置：=GET.CELL(48,INDIRECT(Myref))+0*NOW()

名称：CellColor

引用位置：=GET.CELL(63,INDIRECT(Myref))+0*NOW()

名称：RowIsHidden

引用位置：=IF(GET.CELL(17,INDIRECT(Myref))=0,TRUE,FALSE)+0*NOW()

名称：RowHeight

引用位置：=GET.CELL(17,INDIRECT(Myref))+0*NOW()

在工作表中使用：

=IF(ROW(B2),CellColor)

显示单元格B2的背景色的索引值。

=IF(ROW(B2),RowHeight)

显示单元格B2所在行的行高。

在VBA中使用XLM宏函数

示例：定位图表中的形状

如下图7所示，在工作表Sheet7中，需要在图表区中将箭头从绘图区的左上角指向第3个柱状顶部中间位置。

图7

代码如下：

说明：

使用XLM的GET.CHART.ITEM函数来获取图表中柱状顶部中间的位置。该函数的语法：

CHART.ITEM(x_y_index, point_index, item_text)

其中：

参数x_y_index的值为1时返回X坐标，为2时返回Y坐标。

参数point_index取决于当前激活的对象，其值为一个从1到8的数字，用于表示对象中的某个特定顶点。例如，2表示矩形对象（如柱状图中的列）顶部的中间位置。

参数item_text指定要定位到的对象。例如Plot表示绘图区域，S2P4表示图表中第2数据系列的第4个数据点。

使用GET.CHART.ITEM函数前，必须先激活图表。

阅读全文