excel二十多年前的XLM宏函數還有用嗎

excel二十多年前的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-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函數前，必須先激活圖表。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。