excel二十多年前的XLM宏函数还有用吗

办公教程导读

收集整理了【excel二十多年前的XLM宏函数还有用吗】办公软件教程，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3424字，纯文字阅读大概需要5分钟。

办公教程内容图文

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