這是應(yīng)公眾號的一位粉絲的要求寫的一篇文章：

當(dāng)然，我也很早就想講一講宏表函數(shù)及其應(yīng)用，借此機(jī)會，就詳細(xì)介紹一下吧。

在開始正式內(nèi)容之前，有一點(diǎn)必須先強(qiáng)調(diào)一下。由于宏表函數(shù)開始的很早，又很快就被后來的技術(shù)代替了，很多人會覺得宏表函數(shù)比較落后。其實不是這樣的，宏表函數(shù)背后的東西直到現(xiàn)在還在應(yīng)用。

本文包含一下內(nèi)容：

1. 什么是宏表函數(shù)

2. 真正宏表函數(shù)的運(yùn)行方式

3. 幾個有意思的宏表函數(shù)應(yīng)用

4. 老技術(shù)煥發(fā)了“青春”

什么是宏表函數(shù)？

你一定總聽到有很多高手說起宏表函數(shù)吧。是不是對這個名詞覺得又神秘又高級？當(dāng)看到“Get.Cell”函數(shù)時，你是不是覺得太厲害了？再看到它們需要在名稱中使用，它們的形象是不是都高大了許多??？

其實Get.Cell只是眾多的宏表函數(shù)中的一個。這些函數(shù)基本上都可以退出歷史舞臺了。只剩下少數(shù)幾個還可以發(fā)揮一點(diǎn)余熱（我在本文后面會介紹一些有意思的應(yīng)用）。

那么，什么是宏表函數(shù)呢？

這個要從頭講起。

很久很久以前，那時還是耶穌紀(jì)元1992年，微軟的Windows 3.1差不多所有的PC機(jī)的操作系統(tǒng)，Office更是幾乎每一臺機(jī)器都會裝的應(yīng)用程序。Excel的版本也來到了4.0，史稱Excel 4.0。為了更好的擴(kuò)展Excel的能力，隨著Excel 4.0，微軟推出了“宏”，英文叫做Macro。不過此Macro不是大家現(xiàn)在熟悉的Macro。這個Macro就被叫做Excel 4.0 Macro，也被稱為XLM Macro（這是因為包含Excel 4.0宏的文件必須被保存為擴(kuò)展名為.XLM的文件）。

這個Excel 4.0 Macro跟我們現(xiàn)在熟悉的宏不同，只能在一個特殊的工作表中使用，這個工作表叫做Excel 4.0宏表，所以這些函數(shù)叫做Excel 4.0宏表函數(shù)，簡稱宏表函數(shù)（因為也沒有其他版本的Excel宏表，所以不會沖突??）。

很快，微軟就自己否決了這個“宏”方案。在1993年推出的Excel 5.0中，推出了VBA Macro（就是我們現(xiàn)在熟悉的宏）來代替Excel 4.0 Macro。但是為了向后兼容，后續(xù)版本的Excel一直保留著對Excel 4.0 Macro的完全支持。只是因為Excel的公式越來越強(qiáng)大，VBA發(fā)展的也越來越完善，Excel 4.0 Macro提供的功能已經(jīng)不太用的上了。現(xiàn)在只有有限的一些宏表函數(shù)會被用來代替VBA代碼的功能。（本文后面會介紹一些應(yīng)用案例）

現(xiàn)在，在任何一個版本的Excel中，還可以插入一個宏表（至少在Excel 2016以前，都可以這么做）：

在工作表標(biāo)簽上，點(diǎn)擊鼠標(biāo)右鍵：

點(diǎn)擊插入，在對話框中，選擇“MS Excel 4.0宏表”：

點(diǎn)擊確定，就插入了一個宏1的工作表：

那些宏表函數(shù)就是在這個“宏表”中運(yùn)行的。

真正的宏表函數(shù)的例子

在剛才創(chuàng)建的宏表中的A1單元格中，輸入公式：

但是這個公式的使用就跟普通的Excel公式不同了！你按回車完成公式輸入后，并沒有想普通Excel一樣有個結(jié)果，而是仍然在顯示公式。

宏表公式的執(zhí)行是這樣的：

在公式所在的單元格上點(diǎn)擊鼠標(biāo)右鍵，

點(diǎn)擊最下面的“執(zhí)行”，彈出對話框，

這里的A1即A1單元格，也就是說要運(yùn)行的宏是A1單元格中的宏。這個對話框還可以通過開發(fā)工具選項卡中的宏按鈕打開，

在“宏”對話框中點(diǎn)擊執(zhí)行，Excel就會執(zhí)行A1中的宏。

結(jié)果呢？

這個函數(shù)（PROTECT.DOCUMENT）的作用是用來進(jìn)行工作表的保護(hù)，我們點(diǎn)擊審閱菜單，就可以看到，很神奇，這個工作表被保護(hù)了：

點(diǎn)擊撤銷工作表保護(hù)，彈出對話框：

輸入我們公式中的密碼“myPassword”，成功撤銷保護(hù)。

很簡單吧。??

再來一個例子。

剛才這個例子是操作工作表的，那么我們怎么在工作表中輸入值呢？

我們可以使用函數(shù)：Formula，在宏表單元格A1中，輸入公式：

這個公式的意思是在Sheet2的A1單元格中輸入值，這個值等于Sheet2的D1單元格中的值乘以2。

運(yùn)行一下這個宏，我們看到結(jié)果：

也很簡單，對吧??。

如果有多個公式怎么執(zhí)行？

假設(shè)我們在A1，A2，B1單元格中有三個宏表函數(shù)，都在Sheet1的的單元格中輸入內(nèi)容，其中，

• A1中的函數(shù)在C1中輸入A1的顏色代碼

• A2中的函數(shù)在C2中輸入文本“test”

• B1中的函數(shù)在C3中輸入文本“test1”
  

同樣，選擇執(zhí)行后，我們看結(jié)果：

可以看到，只有A1和A2的公式被執(zhí)行了，B1的公式?jīng)]有執(zhí)行。

如果想看的更仔細(xì)，可以點(diǎn)擊單步執(zhí)行：

這個執(zhí)行過程告訴我們，只有第一列的公式是被執(zhí)行的。

如果第一列的兩個公式不連續(xù)，隔著一個空格，會怎么樣？

簡單的實驗就會告訴我們答案：空行不耽誤后面公式的執(zhí)行，它會一直執(zhí)行下去的？????

如果想讓它停下怎么辦？使用公式：

再次執(zhí)行，就會發(fā)現(xiàn)，執(zhí)行完A2的公式就停止了，A3的公式?jīng)]有被執(zhí)行。

從這幾個例子可以看出，這些公式就好像我們在VBA中寫的代碼，所以只會執(zhí)行一列中的公式，也因此，中間有空行不會導(dǎo)致執(zhí)行的中斷（在任何編程語言中，都不會用空行表示中斷）。

還有一點(diǎn)需要強(qiáng)調(diào)：在VBA中，也可以調(diào)用這些宏表函數(shù)。看上去很厲害，是不是？

確實很厲害。不過唯一的問題是你很難知道都有什么公式呢？這些公式怎么用呢？幫助資料不好找。

實際上，公式有非常多，下面一個截圖給你震撼一下：

如果你需要，可以通過以下方式獲得這個Excel 4.0 Macro 參考文檔（共506頁，全英文）：

關(guān)注本公眾號，點(diǎn)擊底部菜單“聯(lián)系客服”，與客服取得聯(lián)系，索取“Excel 4.0 Macro”參考文檔

一些有意思的應(yīng)用

現(xiàn)在這些宏表函數(shù)還在某些地方發(fā)揮作用。盡管很多可以使用現(xiàn)在的Excel函數(shù)代替，不過仍然可以找到一些很有意思的應(yīng)用。

比如，用的最多的是Get.Cell。

我們怎么使用呢？

這個Get.Cell是用來返回一個單元格的各種屬性的，它有兩個參數(shù)，一個是屬性代碼，另外一個是單元格，比如Get.Cell(63,Sheet1!A1)就是返回Sheet1的A1單元格的填充顏色代碼的。

比如，在宏表的A1單元格中輸入公式：

這個公式的意思所以取得Sheet1的A1單元格的填充顏色代碼，記錄在Sheet1的C1單元格中，

執(zhí)行一下這個宏，結(jié)果是這樣的，

當(dāng)然了，由于每次在宏表中寫公式比較麻煩（微軟也想淘汰宏表），所以我們可以在名稱中使用宏表公式。這也是現(xiàn)在大部分人介紹宏表公式時采用的方法。

在Excel中，創(chuàng)建名稱：

這里，將GET.CELL函數(shù)定義成為名稱GetColor，在Excel中可以直接引用這個名稱：

結(jié)果是一樣的：

這個方法美中不足的是，如果你修改了A1的填充顏色，C1并不能跟著改變。必須重新輸入一下這個C1的公式才行。

下面介紹的一些應(yīng)用。

有一個宏表函數(shù)FILES，可以取得某個目錄下所有的文件名稱列表：FILES

例如，定義一個名稱GetFiles，

這個名稱就返回給定目錄下的所有文件。

可以使用公式，=INDEX(GetFiles,1)返回第一個文件名稱。

注 現(xiàn)在這個功能可以使用Power Query完成。

我們知道，由于Excel顯示位數(shù)和實際數(shù)值位數(shù)的差別，數(shù)值加起來有時有點(diǎn)誤差，我們可以使用GET.CELL函數(shù)來處理。

定義名稱RoundVal：

這個公式就可以將B2單元格的值按照顯示位數(shù)截取：

注意，名稱中公式是相對引用。

在上面的公式中，當(dāng)B2中的值修改了之后，C2（引用了名稱RoundVal）并沒有跟著修改，必須重新輸入公式：=RoundVal才行。

這是宏表函數(shù)的原理決定的。

為了避免這個問題，將名稱公式修改為：

現(xiàn)在公式隨時可以變化結(jié)果了。這里我們利用了易變函數(shù)的特點(diǎn)（參見文章Excel表格為什么那么慢已經(jīng)應(yīng)該如何解決（四）一類特殊的函數(shù)-易變函數(shù)（volatile function)）

定義名稱GetSH：

然后使用公式：

將返回第二個工作表的名稱：

在網(wǎng)上眾多介紹宏表函數(shù)的文章中，總會提到EVALUATE。使用這個函數(shù)干什么呢？我們看幫助文檔中的說明：

從說明看，這個公式與在編輯欄中選擇公式的一部分，然后按F9作用一樣（這個操作可以看視頻：【Excel公式技巧】如何調(diào)試Excel公式）。

我們通過一個例子了解一下這個公式的用法。假設(shè)我們有這樣的內(nèi)容：

這些文本實際上是一個一個的可以計算的表達(dá)式，如果前面有“=”，直接就會計算出結(jié)果。現(xiàn)在沒有“=”，我們可以使用EVALUATE計算這些表達(dá)式。

定義名稱Calc：

然后在B1中輸入公式：=Calc，并往下拖拽：

老技術(shù)又煥發(fā)了“青春”

應(yīng)該說，宏表函數(shù)代表的是很老的技術(shù)了?；颈贿z忘的差不多了，即使在網(wǎng)上還有一些文章介紹類似GET.CELL等函數(shù)，實際上這些功能基本上被CELLS，INFO等函數(shù)代替的差不多了。但是最近一兩年，宏表函數(shù)（確切的說，是Excel 4.0 Macro）的熱度有點(diǎn)上升，因為：

它被一些黑客盯上了。

仔細(xì)想象，還是很有道理的。

首先，這完全是Excel本身的功能，所以警惕性會很低。

其次，這項技術(shù)很老了，以至于沒有任何安全軟件和安全機(jī)制去考慮進(jìn)行這方面的檢查，這就意味著使用這些技術(shù)制作的惡意軟件基本不會被事先阻止。

最后，如果你仔細(xì)研究了那500多頁的文檔，就會發(fā)現(xiàn)，這些函數(shù)提供了非常豐富而強(qiáng)大的能力，讓你可以完全操作Excel，甚至操作系統(tǒng)。

實際上，這些所謂的宏表函數(shù)根本就是微軟為Excel開發(fā)者提供的底層C API。甚至都沒有換馬甲，因為如果你去查Excel C API的文檔（如果你找得到的話），就會發(fā)現(xiàn)，這兩者一模一樣。

所以，還是要小心這個宏表函數(shù)啊??

（其實，也不用過分擔(dān)心，畢竟只要意識到了，防范還是不太難的）。

而且，這么強(qiáng)大的功能，如果利用好了，可以做很多原來做不到的事情，比如，不用自己寫VBA代碼了！

好了，就分享到這里了！