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

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

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

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

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

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

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

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

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

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

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

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

這個(gè)要從頭講起。

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

這個(gè)Excel 4.0 Macro跟我們現(xiàn)在熟悉的宏不同，只能在一個(gè)特殊的工作表中使用，這個(gè)工作表叫做Excel 4.0宏表，所以這些函數(shù)叫做Excel 4.0宏表函數(shù)，簡(jiǎn)稱(chēng)宏表函數(shù)（因?yàn)橐矝](méi)有其他版本的Excel宏表，所以不會(huì)沖突??）。

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

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

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

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

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

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

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

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

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

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

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

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

這里的A1即A1單元格，也就是說(shuō)要運(yùn)行的宏是A1單元格中的宏。這個(gè)對(duì)話框還可以通過(guò)開(kāi)發(fā)工具選項(xiàng)卡中的宏按鈕打開(kāi)，

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

結(jié)果呢？

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

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

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

很簡(jiǎn)單吧。??

再來(lái)一個(gè)例子。

剛才這個(gè)例子是操作工作表的，那么我們?cè)趺丛诠ぷ鞅碇休斎胫的兀?/span>

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

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

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

也很簡(jiǎn)單，對(duì)吧??。

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

假設(shè)我們?cè)贏1，A2，B1單元格中有三個(gè)宏表函數(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í)行：

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

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

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

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

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

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

還有一點(diǎn)需要強(qiáng)調(diào)：在VBA中，也可以調(diào)用這些宏表函數(shù)?？瓷先ズ軈柡Γ遣皇?？

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

實(shí)際上，公式有非常多，下面一個(gè)截圖給你震撼一下：

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

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

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

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

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

我們?cè)趺词褂媚兀?/span>

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

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

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

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

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

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

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

結(jié)果是一樣的：

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

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

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

例如，定義一個(gè)名稱(chēng)GetFiles，

這個(gè)名稱(chēng)就返回給定目錄下的所有文件。

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

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

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

定義名稱(chēng)RoundVal：

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

注意，名稱(chēng)中公式是相對(duì)引用。

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

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

為了避免這個(gè)問(wèn)題，將名稱(chēng)公式修改為：

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

定義名稱(chēng)GetSH：

然后使用公式：

將返回第二個(gè)工作表的名稱(chēng)：

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

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

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

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

定義名稱(chēng)Calc：

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

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

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

它被一些黑客盯上了。

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

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

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

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

實(shí)際上，這些所謂的宏表函數(shù)根本就是微軟為Excel開(kāi)發(fā)者提供的底層C API。甚至都沒(méi)有換馬甲，因?yàn)槿绻闳ゲ镋xcel C API的文檔（如果你找得到的話），就會(huì)發(fā)現(xiàn)，這兩者一模一樣。

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

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

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

好了，就分享到這里了！