Excel宏表函數大全—現在才知道原來不用VBA也可以完成這些騷操作

1、什麼是宏表函數

宏表函數是又稱excel4.0函數，是Excel第4個版本的函數，為了考慮兼容性，現在的版本依然可以調用該函數。宏表函數是一類非常特殊的函數，你在Excel的函數列表中找不到它們，但它們確實存在，而且功能異常強大，在許多應用中不可或缺。

2、宏表函數有什麼用處？

宏表函數可以實現現有版本的函數或技巧無法完成的功能，比如取單元格填充色值、獲取工作表的名稱列表等。

3、怎麼使用宏表函數

宏表函數不能在工作表單元格中直接使用，需要在名稱管理器中先定義一個名稱，然後在單元格中使用該名稱。

4、Excel宏表函數列表

Get.Cell的用法

函數定義: Get.Cell(類型號,單元格(或範圍))

其中類型號，即你想要得到的信息的類型號，經試驗，範圍為1-66，也就是說這個函數可以返回一個單元格里66種信息。

以下是類型號及其所代表的信息

1 - 返回絕對引用 //引用樣式由Excel參數決定，可以用工作表函數 CELL("address"); CELL("address",REF)

2 - 返回行號 //可以用工作表函數 CELL("row"); CELL("row",REF); ROW(REF)

3 - 返回列號(數字) //可以用工作表函數 CELL("col"); CELL("col",REF); COLUMN(REF)

4 - 返回數據類型(1-數值或空單元格,2-文本,4-邏輯,16-錯誤值) //基本可以用工作表函數TYPE，除了針對活動單元格的情形。注意與CELL("type")不同

5 - 返回值 // 直接用 =單元格地址，完美的替代是CELL("contents"), CELL("contents",REF)

6 - 返回公式或值 //如果單元格不含公式，則與5相同。 公式中的引用樣式與Excel的設定相同，而宏表函數GET.FORMULA則必然採用R1C1引用樣式

7 - 返回數字格式 //常規格式對應的代碼與Excel的語言設定有關，可以定義名稱擁帶宏表函數的公式=INDEX(GET.WORKSPACE(37),26) 得到 該常規格式對應的當前代碼。

8 - 返回水平對齊方式編號 //即單元格格式中水平對齊下拉列表中的序號(1-常規,2-靠左,3-居中,4-靠右,5-填充,6-兩端對齊,7-跨列居中,8-分散對齊)

9 - 返回左邊框線型編號 //編號表見附表1，下面3個也是

10 - 返回右邊框線型編號

11 - 返回上邊框線型編號

12 - 返回下邊框線型編號

13 - 返回背景圖案編號 //編號表見附表2

14 - 返回鎖定狀態(True鎖定，False未鎖定)

15 - 返回隱藏狀態(True隱藏，False未隱藏) //注意不是隱藏行列，是指的保護工作表時是否隱藏公式(編輯欄內容)，對應 單元格格式>>保護>>隱藏 這項設定。

16 - 返回列寬及其自適應性 //1行2列數組，第一項是數值，即列寬的設定值，第二項為邏輯值，表示是否是原始的自適應列寬，注意並不是說等於默認的列寬就是True，當你改過該列的列寬後就不會是True了。沒有可替代的函數，甚至VBA似乎也無法準確取得第二項的值(前提是不調用這個宏表函數)

17 - 返回行高

18 - 返回首字元的字體名稱 //只與首字元有關，後面幾個類似

19 - 返回首字元的字體磅值

20 - 返回首字元的粗體狀態

21 - 返回首字元的斜體狀態

22 - 返回首字元的單下劃線狀態 //注意只對「單下劃線」返回True，其他類型的下劃線都返回False，

23 - 返回首字元的刪除線狀態

24 - 返回首字元的顏色編號 //參見附表3，0表示"默認"顏色

25 - 返回首字元的空心狀態 //用於Mac，Windows下無實際顯示效果，但是保留設定

26 - 返回首字元的陰影狀態 //用於Mac，Windows下無實際顯示效果，但是保留設定

27 - 返回手動分頁狀態(0-無,1-上方,2-左側,3-左側和上方) //只針對手動分頁符，忽略自動分頁符

28 - 返回行的級數 //不曾建立分級列表的話就是第1級

29 - 返回列的級數 //不曾建立分級列表的話就是第1級

30 - 返回是否位於分級列表的匯總行

31 - 返回是否位於分級列表的匯總列

32 - 返回工作表全名 //包括工作簿文件名，即[book1.xls]Sheet1形式，當工作簿主名(第一個小數點之前的部分)與工作表名相同，此時僅返回工作簿名稱，如book1.xls。 效果與宏表函數Get.Document(1)一樣。與CELL("filename",REF)的區別是，後者包括完整路徑，而Get.Cell不帶路徑。

33 - 返回自動換行狀態

34 - 返回左邊框線顏色編號

35 - 返回右邊框線顏色編號

36 - 返回上邊框線顏色編號

37 - 返回下邊框線顏色編號

38 - 當圖案為實心時，返回單元格的背景色編號；其他時候返回圖案前景色編號

39 - 當圖案為實心時，返回單元格的圖案前景色編號；其他時候返回圖案背景色編號

40 - 返回樣式名稱

41 - 不經翻譯返回單元格的公式 //某些語言的excel，函數名稱與英文版不同，參數41與6的區別應該就在此處

42 - 返回單元格左邊界相對窗口左邊界的偏移

43 - 返回單元格上邊界相對窗口上邊界的偏移

44 - 返回單元格右邊界相對窗口左邊界的偏移

45 - 返回單元格下邊界相對窗口上邊界的偏移

46 - 返回是否含批註

47 - 返回是否含聲音批註 //該功能只適用於Excel5和95，自97版本開始這個功能被取消

48 - 返回是否含公式

49 - 返回是否含數組公式

50 - 返回垂直對齊方式 //即單元格格式中垂直對齊下拉列表中的序號(1-靠上,2-居中,3靠下，4-兩端對齊,5-分散對齊)

51 - 返迴文字方向(0-水平,1-垂直,2-向上(90度),3-向下(-90度),4-其他)

52 - 返回前綴字元 //若「1-2-3常用鍵」功能關閉，則只有"這一種前綴，也就是強制文本型；當「1-2-3常用鍵」功能打開，有三種前綴，^表示居中(跨列居中也會返回這個前綴)，"為靠右(填充也會顯示這個前綴)，其他都是"(輸入時表示左對齊)。在「1-2-3常用鍵」功能打開時，除了跨列居中，其他的判斷為文本型的單元格都會再編輯框中顯示一個前綴符，對於跨列居中，雖然不顯示，但是用該宏表函數還是會返回^。不過在「1-2-3常用鍵」功能關閉時，只有用戶確實輸入過前綴的單元格才會保留前綴，全都變成"(與對齊無關)，這時候此宏表函數也只對輸入過前綴的返回"，不然返回空字元串。注意，這裡的行為與CELL("perfix")不同，後者忽略「1-2-3常用鍵」這個設置，對於判斷為文本型的單元格，按照對齊方式返回相應的符號。

53 - 返回單元格的實際顯示值，文本型 //對於靠單元格數字格式設置所定義的結果以及因容量限制而形成的#####等都會照實返回，不過不識別自動換行，不會為在相應位置添加換行符。

54 - 返回數據透視表名，不在透視表中則返回#N/A

55 - 返回在數據透視表中的位置(0-行標題，1-列標題，2-頁標題，4-行項目，5-列項目，6-頁項目，7-數據項標題，8-表格主體)，不在透視表中則返回#N/A

56 - 返回欄位名，不在透視表中則返回#N/A

57 - 返回首字元的上標狀態

58 - 返回首字元的字形 //常規、傾斜、加粗、加粗 傾斜

59 - 返回首字元的下劃線類型(1-無，2-單下劃線，3-雙下劃線，4-會計用單下劃線，5-會計用雙下劃線)

60 - 返回首字元的下標狀態

61 - 返回在數據透視表中的項目名，不在透視表中則返回#N/A

62 - 返回帶工作簿名稱的工作表名 //與32不同的是，無論何種情況都是[book1]sheet1形式

63 - 返回圖案背景色

64 - 返回圖案前景色

65 - 返回兩端分散對齊狀態

66 - 返回工作簿名稱

使用演示：

1）、獲取單元格填充顏色的顏色代碼

新建一個名為cellcolor的名稱

在H20單元格輸入公式：=cellcolor即可得到44，注意宏表函數沒有()，所以當你輸=cellcolor()時是錯誤的！

輸入自定義名稱

當將類型號改為66時表示獲取當前工作簿的名稱

其他的參數就不在進行演示，大家有興趣可以自行測試。

Evaluate是一個可以實現文本算式批量計算的宏表函數，需要注意的是每個文本表達式不得超過255個字元，超過了255，常規計算返回結果為#VALUE!，下面演示一下它的具體用法：

1）、計算單個文本表達式

新建一個js的自定義名稱，如下圖所示：

在H23單元格輸入公式：=js即可得到計算結果13

2）、計算多個文本表達式

計算多個文本表達式的話只需要把單元格改為文本表達式所在的單元格範圍即可，具體步驟如下：

下鄉拖拉公式即可：

GET.WORKBOOK的用法

語法結構：

GET.WORKBOOK(type_num, name_text)

Type_num 指明要得到的工作簿信息類型的數。

Name_text 是打開的工作簿的名字。如果name_text被省略,默認為活動工作簿。

類型數與其所代表的的含義如下：

1 正文值的水平數組,返回工作簿中所有表的名字。

2 通常返回錯誤值#N/A。

3 正文值的水平數組,返回工作簿中當前選擇的表的名稱。

4 工作簿中表的數。

5 如果工作簿含有發送名單，返回TRUE；否則返回FALSE。

6 正文值的水平數值，返回所有未接收到文件的接受者的名字。

7 以文本形式返回當前發送名單的主題線。

8 以文本形式返回發送名單的信息正文。

9 如果文件被發送,一個接一個被接收，返回1；如果全部一次被發送，返回2。

10 如果［發送名單］對話框中選擇［返回送畢信息］選擇框,返回TRUE；否則返回FALSE。

11 如果當前接收發送當前文件,返回TRUE；否則返回FALSE。

12 如果［發送名單］對話框中選擇［跟蹤狀態］選擇框,返回TRUE；否則返回FALSE。

13 工作簿［發送名單］：0 =不被發送1 =進展中的路徑選擇,或對於用戶工作簿已被發送。2 =發送已完成。

14 如果工作簿結構被保護,返回TRUE；否則返回FALSE。

15 如果工作簿窗口被保護,返回TRUE；否則返回FALSE。

16 以文字形式返回工作簿的名稱，不包括驅動器，目錄或文件， 或窗口編號，等價於GET.DOCUMENT(1)

17 如果文件為只讀，返回TRUE，否則返回FALSE。等價於GET,DOCUMENT(34)

18 如果表被防寫，返回TRUE，否則返回FALSE。等價於GET.DOCUMENT(35)

19 當前文件允許寫的用戶的名字,等價於GET.DOCUMENT(36)。

20 對應於顯示在［另存為］對話框中的文件的文件類型的數字。等價於GET.DOCUMENT(37)。

21 如在［另存為］對話框中選擇了［建立備文件］選擇框，返回TRUE；否則，返回FALSE。等價於GET.DOCUMENT(40)。

22 如在［選擇］對話框的［重新計算設置］標籤中選擇了［保存外部鏈接值］選擇框，返回TRUE。等價於GET.DOCUMENT(43)。

23 如果工作簿有一個蘋果開放合作環境(OCE)返回TRUE；否則返回FALSE。如未安裝OCE(mailer),返回#N/A。

24 如果工作簿在最後一次保存後被改變,返回TRUE。若未改變(當被關閉,不提示保存)，返回FALSE。

25 以文字形式水平數組，返回Power Talk(mailer)的To線上的接收。

26 以文字形式水平數組，返回Power Talk(mailer)的Cr線上接收。

27 以文字形式水平數組,返回Power Talk(mailer)的Bxx線上的接收。

28 以文字形式返回Power Talk(mailer)的主題。

29 以文字形式水平數組,返回Power Talk(mailer)的外殼。

30 如果Power Talk(mailer)從另一用戶［*(相對於剛增加但不是發送)。

31 作為一系列數返回Power Talk mailer)被發送的日期和時間，如(mailer)未被發送，返回錯誤值#N/A。

32 以文本形式返回Power Talk(mailer)的發送者的名字,如(mailer)未被發送,返回錯誤值#N/A。

33 以文字形式返回顯示在［摘要信息］對話框中的文件的標題。

34 以文字形式返回顯示在［摘要信息］對話框中的文件的主題。

35 以文字形式返回顯示在［摘要信息］對話框中的文件的作者。

36 以文字形式返回顯示在［摘要信息］對話框中的文件的關鍵字。

37 以文字形式返回顯示在［摘要信息］對話框中的文件的注釋。

38 活動工作表的名字。

演示用法：

1）、所有工作表列表

新建一個自定義名稱workbooks

在G32單元格輸入公式：=workbooks得到如下結果

將公式改為=INDEX(workbooks,ROW(A1))即可得到所有的工作表名稱

2）、獲取工作表數量workbookscount

新建一個名為workbookscount的自定義名稱

在G32單元格輸入公式：=workbookscount得到如下結果

3就是此工作簿的工作表個數

其餘的用法就不在意義演示

這個宏表函數的用法就是獲取指定目錄下的某類文件類型名稱

語法結構：Files(文件類型）

演示用法：

新建一個Excelfile的自定義名稱，如下所示

在G32單元格輸入公式：=INDEX(Excelfile,ROW(A1))並先下拖拽即可得到如下結果

GET.DOCUMENT的用法

語法結構：

GET.DOCUMENT(type_num, name_text)

Type_num 指明信息類型的數。下表列出 type_num 的可能值與對應結果。

Type_num 返回

1 如果工作簿中不只一張表，用文字形式以「［book1］sheet1」的格式返回工作表的文件名。否則，只返回工作簿的文件名。工作簿文件名不包括驅動器，目錄或窗口編號。通常最好使用 GET. DOCUMENT(76)

和 GET. DOCUMENT(88) 來返回活動工作表和活動工作簿的文件名。

2 作為文字，包括 name_text 的目錄的路經。如果工作簿name_text 未被保存，返回錯誤值 #N/A

3 指明文件類型的數

1 = 工作表

2 = 圖表

3 = 宏表

4 = 活動的信息窗口

5 = 保留文件

6 = 模塊表

7 = 對話框編輯表

4 如果最後一次存儲文件後表發生了變化，返回TRUE；否則，返回FALSE。

5 如果表為只讀，返回TRUE；否則，返回FALSE。

6 如果表設置了口令加以保護，返回TRUE；否則， 返回FALSE。

7 如果表中的單元格，表中的內容或圖表中的系列被保護，返回TRUE；否則，返回FALSE。

8 如果工作簿窗口被保護，返回TRUE；否則，返回FALSE。

下面四個 type_num 的數值只用於圖表。

Type_num 返回

9 指示主圖表的類型的數。

1 = 面積圖

2 = 條形圖

3 = 柱形圖

4 = 折線形

5 = 餅形

6 = XY (散點圖)

7 = 三維面積圖

8 =三維柱形圖

9 = 三維折線圖

10 = 三維餅圖

11 = 雷達圖

12 = 三維等形圖

13 = 三維曲面圖

14 = 圓環圖

10 指示覆蓋圖表類型的數，同以上主圖表的 1，2，3，4，5，6,11 和 14。沒有覆蓋圖表的情況下返回錯誤值 #N/A

11 主圖表系列的數

12 覆蓋圖表系列的數

下列 Type_num 的值用於工作表，宏表，在適當的時候用於圖表。

Type_num 返回

9 第一個使用行的編號。如文件是空的，返回零。

10 最後一個使用行的偏號。如文件是空的，返回零。

11 第一個使用列的編號。如文件是空的，返回零。

12 最後一個使用列的編號。如文件是空的 ，返回零。

13 窗口的編號。

14 指明計算方式的數。

1 = 自動生成 有

2 = 除表格外自動生成

3 = 手動

15 如果在［選項］對話框的［重新計算設置］標籤下選擇［迭代］選擇框，返回TRUE；否則，返回FALSE。

16 迭代間的最大數值。

17 迭代間的最大改變

18 如果在［選項］對話框的［重新計算設置］標籤下選擇［更新過程引用］選擇框，返回TRUE；否則，返回

FALSE。

19 如果在［選項］對話框的［重新計算設置］標籤下選［以顯示值為準］選擇框，返回TRUE；否則，返回

FALSE。

20 如果在 Options 對話框的［重新計算設置］標籤下選擇［1904 日期系統選擇框，返回TRUE；否則，返回

FALSE。

Type_num 是21-29之間的數， 對應於 Microsoft Excel 先前版本的四種默認字體。提供這些值是為了宏的兼容性。

下列 Type_num 數值應用於工作表，宏表和指定的圖表。

Type_num 返回

30 以文字形式返回當前表合併引用的水平數組. 如果列表是空的，返回錯誤值 #N/A

31 1至11 之間的一個數，指明用於當前合併的函數。對應於每個數的函數列於下面 CONSOLIDATE 函數中，默認函數為SUM

32 三項水平數組，用於指明 Data Consolidate 對話框中選擇框的狀態。如果此項為TRUE，選擇選擇框.

如果此項為FALSE，清除選擇框. 第一項指明［頂端行］選擇框，第二項指［最左列］選擇框,第三項指［與源數據鏈接］選擇框。

33 如果選擇了［選項］對話框的［重新計算設置］標籤下的［保存前重新計算］選擇框，返回TRUE；否則，返回FALSE。

34 如工作簿定義為只讀，返回TRUE；否則，返回FALSE。

35 工作簿為防寫，返回TRUE；否則，返回FALSE。

36 如文件設置了防寫口令，並以可讀/可寫方式打開，返回最初使用防寫口令存文件的用戶的名字。如文件以只讀形式打開，或文件未設置口令，返回當前用戶的名字。

37 對應於顯示在［另存為］對話框中的文檔的文件類型。所有 Microsoft Excel 可識別的文件類型列於

SAVE.AS函數中。

38 如選擇了［分級顯示］對話框中的［明細數據的下方選擇框，返回TRUE；否則，返回FALSE。

39 如果選擇了［分級顯示］對話框中的［明細數據的右側］選擇框，返回TRUE；否則，返回FALSE。

40 如果選擇了［另存為］對話框中的［建立備份文件］選擇框，返回TRUE；否則，返回FALSE。

41 1至3中的一個數字，指明是否顯示對象：

1 = 顯示所有對象

2 = 圖和表的位置標誌符

3 = 所有對象被隱藏

42 包括表中所有對象的水平數組，如無對象，返回錯誤值 #N/A

43 如果在［選項］對話框的［重新計算設置］標籤下選擇了［保存外部鏈接值］選擇框，返回TRUE；否則，返回FALSE。

44 如文件中的對象被保護，返回TRUE；否則，返回FALSE。

45 0至3中的一個數，指明窗口同步化方式。

0 = 不同步

1 = 水平方向上同步

2 = 垂直方向上同步

3 = 水平方向，垂直方向上均同步

46 七項水平數組，用於列印設置，可由 LINE. PRINT 宏函數完成。

- 建立文字

- 左邊距

- 右邊距

- 頂邊距

- 底邊距

- 頁長

- 用於指明列印時輸出是否格式化的邏輯值，格式化為TRUE, 非格式化為FALSE。

47 如果在［選項］對話框的［轉換］標籤中選擇了［轉換表達式求值］選擇框，返回TRUE；否則，返回FALSE

。

48 標準欄寬度設置

下列 type_num 值對應於列印與頁的設置。

Type_num 返回

49 開始頁的頁碼，如未指明或在［頁面設置］對話框的［頁］標籤下的［起始頁號］文字框輸入了「自動」，返回錯誤值#N/A

50 當前設置下欲列印的總頁數，其中包括注釋，如果文件為圖表，值為1

51 如只列印注釋時的總頁數。如文件為圖表類型，返回錯誤值 #N/A

52 在當前指定的單位中，指明邊距設置(左，右，頂，底)的四項水平數組。

53 指明方向的數字:

1 = 縱向

2 = 橫向

54 文本串的頁眉，包括格式化代碼。

55 文本串的腳註，包括格式化代碼。

56 包括兩個邏輯值的水平數組，對應於水平垂直方向置中。

57 如列印行或列的上標題，返回TRUE；否則，返回FALSE。

58 如列印網格線，返回TRUE；否則，返回FALSE。

59 如表以黑白方式列印，返回TRUE；否則，返回FALSE。

60 1至3中的一個數,指明列印時定義圖表大小的方式。

1 = 屏幕大小

2 = 調整到

3 = 使用整頁

61 指明重排頁命令的數:

1 = 先列後行

2 = 先行後列

如文件為圖表類型,返回錯誤值#N/A

62 擴縮比,未指定時為100%。如當前印表機不支持此項操作或文件為圖表類型時，返回錯誤值#N/A。

63 一個兩項水平數組,指明其報表需按比例換算，以適合的頁數印出 ,第一項等於寬度(如未指明寬度按比例縮放,返回#N/A)第二項等於高度(如未指明高度按比例縮放,返回#N/A)。如文件為圖表類型,返回#N/A

64 行數的數組,相應於手動或自動生成頁中斷下面的行。

65 列數的數組。相應於手動或自動生成的頁中斷右邊的列。

附註 GET.DOCUMENT(62)和GET.DOCUMENT(63)互相排斥,如果其中一個返回一個數值,另外一個返回錯誤值#N/A。

下列type_num數值對應不同文件設置。

Type_num 返回

66 Microsoft Excel for Windows 中，如果在［選項］對話框的［轉換］標籤中選擇了［轉換公式項］選擇框,返回TRUE;否則,返回FALSE。

67 Microsoft Excel 5.0版本下,通常返回TRUE。

68 Microsoft Excel 5.0版本下,通常返回簿的文件名。

69 如果在［選項］對話框的［查看］標誌中選擇了［自動分頁線］，返回TRUE;否則,返回FALSE。

70 返迴文件中所有數據透視表的文件名

71 返回表示文件中所有類型的水平數組。

72 返回表示當前表顯示的所有圖表類型的水平數組。

73 返回表示當前工作表每一個圖表中系列數的水平數組。

74 返回控制的對象標識符，控制當前執行中的由用戶定義的對話框編輯表中獲得焦點的控制(以對話框編輯表為基礎)。

75 返回對象的對象標識符,對象正在執行中的由用戶定義的對話框編輯表中的默認按樞(以對話框編輯表為基礎)。

76 以［Book1］sheel的形式返回活動表或宏表的文件名。

77 以整數的形式返回頁的大小:

1=Letter 8.5x11 in

2 = Letter Small 8.5 x 11 in

5 = Legal 8.5 x 14 in

9 = A4 210 x 297 mm

10 = A4 Small 210 x 297 mm

13 = B5 182 x 257 mm

18 = Note 8.5 x 11 in

78 返回列印解析度,為一個二項水平數組。

79 如在［頁面設置］對話框的［工作表］標籤中選擇［草稿質量］選擇框返回TRUE;否則,返回FALSE。

80 如在［頁面設置］對話框的［工作表］標籤下選擇了［附註］選擇框，返回TRUE;否則,返回FALSE。

81 做為一個單元格的引用,從［頁面設置］對話框的［工作表］標籤返回列印區域。

82 做為一個單元格引用從［頁面設置］對話框的［工作表］標籤回列印標題。

83 如果工作表為方案而被保護起來,返回TRUE;否則,返回FALSE。

84 返回表中第一個循環引用的值,如無循環引用,返回錯誤值#N/A。

85 返回表的高級篩選方式狀態。這種方式頂部設有向下的箭頭,如數據精單通過選擇［篩選］,再從［數據］菜單選擇［高級篩選］被篩選,返回TRUE;否則,返回FALSE。

86 返回表的自動篩選方式狀態。這種方式頂部有向下的箭頭,如選擇了［篩選］,再從［數據］菜單選擇［自動篩選］,篩選向下的箭頭被顯示出來,返回TRUE;否則,返回FALSE。

87 返回指示表的位置的數字,第一張表位置為1。計算中包含隱藏起來的表。

88 以「book1」的形式返回活動工作簿的文件名。

演示用法：

1）、獲取列印總頁數

新建一個名為pages的自定義名稱

在G67單元格輸入公式：=pages即可得到如下結果

這個函數的類型號有點多，用法比較多，大家可以自行百度，這裡不做詳細介紹，只演示一下怎麼用即可

之前羅列指定路徑下的文件名稱，一般都需要運用VBA才可以完成的，只要我們掌握了宏表函數FILES的用法之後，這樣的事就變得輕而易舉了，所以只需要掌握宏表函數，你也能夠完成大神一樣的騷操作！！！！