excel210多年前的XLM宏函数还有用吗-英雄云拓展知识分享

是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⑴ 在工作簿中创建命名公式，以获得工作簿中所有的工作表名。

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

名称：AllSheets

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

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

=INDEX(AllSheets,ROW())

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

图4

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

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

名称：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⑶ 在工作簿中创建命名公式，以获得当前工作表之前（即左边）的工作表名。

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

名称：PreviousSheet

援用位置：=INDEX(AllSheets,MATCH(ThisSheet,AllSheets,0)⑴)

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

图6

3⑷ 在工作簿中创建命名公式，以获得当前工作表以后（即右边）的工作表名。

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

名称：NextSheet

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

3⑸ 下面的公式获得当前工作表之前的工作表单元格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)))⑷)

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函数前，必须先激活图表。