 这是应公众号的一位粉丝的要求写的一篇文章： 当然，我也很早就想讲一讲宏表函数及其应用，借此机会，就详细介绍一下吧。 在开始正式内容之前，有一点必须先强调一下。由于宏表函数开始的很早，又很快就被后来的技术代替了，很多人会觉得宏表函数比较落后。其实不是这样的，宏表函数背后的东西直到现在还在应用。 本文包含一下内容：
 01 什么是宏表函数？ 你一定总听到有很多高手说起宏表函数吧。是不是对这个名词觉得又神秘又高级？当看到“Get.Cell”函数时，你是不是觉得太厉害了？再看到它们需要在名称中使用，它们的形象是不是都高大了许多😂？ 其实Get.Cell只是众多的宏表函数中的一个。这些函数基本上都可以退出历史舞台了。只剩下少数几个还可以发挥一点余热（我在本文后面会介绍一些有意思的应用）。 那么，什么是宏表函数呢？ 这个要从头讲起。 很久很久以前，那时还是耶稣纪元1992年，微软的Windows 3.1差不多所有的PC机的操作系统，Office更是几乎每一台机器都会装的应用程序。Excel的版本也来到了4.0，史称Excel 4.0。为了更好的扩展Excel的能力，随着Excel 4.0，微软推出了“宏”，英文叫做Macro。不过此Macro不是大家现在熟悉的Macro。这个Macro就被叫做Excel 4.0 Macro，也被称为XLM Macro（这是因为包含Excel 4.0宏的文件必须被保存为扩展名为.XLM的文件）。 这个Excel 4.0 Macro跟我们现在熟悉的宏不同，只能在一个特殊的工作表中使用，这个工作表叫做Excel 4.0宏表，所以这些函数叫做Excel 4.0宏表函数，简称宏表函数（因为也没有其他版本的Excel宏表，所以不会冲突😁）。 很快，微软就自己否决了这个“宏”方案。在1993年推出的Excel 5.0中，推出了VBA Macro（就是我们现在熟悉的宏）来代替Excel 4.0 Macro。但是为了向后兼容，后续版本的Excel一直保留着对Excel 4.0 Macro的完全支持。只是因为Excel的公式越来越强大，VBA发展的也越来越完善，Excel 4.0 Macro提供的功能已经不太用的上了。现在只有有限的一些宏表函数会被用来代替VBA代码的功能。（本文后面会介绍一些应用案例） 现在，在任何一个版本的Excel中，还可以插入一个宏表（至少在Excel 2016以前，都可以这么做）： 在工作表标签上，点击鼠标右键： 点击插入，在对话框中，选择“MS Excel 4.0宏表”： 点击确定，就插入了一个宏1的工作表： 那些宏表函数就是在这个“宏表”中运行的。 02 真正的宏表函数的例子 在刚才创建的宏表中的A1单元格中，输入公式： =PROTECT.DOCUMENT(TRUE,TRUE,"myPassword",TRUE)但是这个公式的使用就跟普通的Excel公式不同了！你按回车完成公式输入后，并没有想普通Excel一样有个结果，而是仍然在显示公式。 宏表公式的执行是这样的： 在公式所在的单元格上点击鼠标右键， 点击最下面的“执行”，弹出对话框， 这里的A1即A1单元格，也就是说要运行的宏是A1单元格中的宏。这个对话框还可以通过开发工具选项卡中的宏按钮打开，
在“宏”对话框中点击执行，Excel就会执行A1中的宏。 结果呢？ 这个函数（PROTECT.DOCUMENT）的作用是用来进行工作表的保护，我们点击审阅菜单，就可以看到，很神奇，这个工作表被保护了：
点击撤销工作表保护，弹出对话框：
输入我们公式中的密码“myPassword”，成功撤销保护。 很简单吧。😁 再来一个例子。 刚才这个例子是操作工作表的，那么我们怎么在工作表中输入值呢？ 我们可以使用函数：Formula，在宏表单元格A1中，输入公式： =FORMULA(Sheet2!D1*2,Sheet2!A1)这个公式的意思是在Sheet2的A1单元格中输入值，这个值等于Sheet2的D1单元格中的值乘以2。 运行一下这个宏，我们看到结果：
也很简单，对吧😁。 如果有多个公式怎么执行？
假设我们在A1，A2，B1单元格中有三个宏表函数，都在Sheet1的的单元格中输入内容，其中，
同样，选择执行后，我们看结果：
可以看到，只有A1和A2的公式被执行了，B1的公式没有执行。 如果想看的更仔细，可以点击单步执行：
这个执行过程告诉我们，只有第一列的公式是被执行的。 如果第一列的两个公式不连续，隔着一个空格，会怎么样？
简单的实验就会告诉我们答案：空行不耽误后面公式的执行，它会一直执行下去的？😀😣 如果想让它停下怎么办？使用公式： =HALT()
再次执行，就会发现，执行完A2的公式就停止了，A3的公式没有被执行。 从这几个例子可以看出，这些公式就好像我们在VBA中写的代码，所以只会执行一列中的公式，也因此，中间有空行不会导致执行的中断（在任何编程语言中，都不会用空行表示中断）。 还有一点需要强调：在VBA中，也可以调用这些宏表函数。看上去很厉害，是不是？ 确实很厉害。不过唯一的问题是你很难知道都有什么公式呢？这些公式怎么用呢？帮助资料不好找。 实际上，公式有非常多，下面一个截图给你震撼一下：
如果你需要，可以通过以下方式获得这个Excel 4.0 Macro 参考文档（共506页，全英文）： 关注本公众号，点击底部菜单“联系客服”，与客服取得联系，索取“Excel 4.0 Macro”参考文档
03 一些有意思的应用 现在这些宏表函数还在某些地方发挥作用。尽管很多可以使用现在的Excel函数代替，不过仍然可以找到一些很有意思的应用。 比如，用的最多的是Get.Cell。 我们怎么使用呢？ 这个Get.Cell是用来返回一个单元格的各种属性的，它有两个参数，一个是属性代码，另外一个是单元格，比如Get.Cell(63,Sheet1!A1)就是返回Sheet1的A1单元格的填充颜色代码的。 比如，在宏表的A1单元格中输入公式： =FORMULA(GET.CELL(63,Sheet1!A1),Sheet1!C1)这个公式的意思所以取得Sheet1的A1单元格的填充颜色代码，记录在Sheet1的C1单元格中， 执行一下这个宏，结果是这样的，
当然了，由于每次在宏表中写公式比较麻烦（微软也想淘汰宏表），所以我们可以在名称中使用宏表公式。这也是现在大部分人介绍宏表公式时采用的方法。 在Excel中，创建名称：
这里，将GET.CELL函数定义成为名称GetColor，在Excel中可以直接引用这个名称：
结果是一样的：
这个方法美中不足的是，如果你修改了A1的填充颜色，C1并不能跟着改变。必须重新输入一下这个C1的公式才行。 下面介绍的一些应用。 01 取得文件列表 有一个宏表函数FILES，可以取得某个目录下所有的文件名称列表：FILES 例如，定义一个名称GetFiles，
这个名称就返回给定目录下的所有文件。 可以使用公式，=INDEX(GetFiles,1)返回第一个文件名称。 注 现在这个功能可以使用Power Query完成。 02 四舍五入问题 我们知道，由于Excel显示位数和实际数值位数的差别，数值加起来有时有点误差，我们可以使用GET.CELL函数来处理。 定义名称RoundVal：
这个公式就可以将B2单元格的值按照显示位数截取：
注意，名称中公式是相对引用。 03 一个小技巧 在上面的公式中，当B2中的值修改了之后，C2（引用了名称RoundVal）并没有跟着修改，必须重新输入公式：=RoundVal才行。 这是宏表函数的原理决定的。 为了避免这个问题，将名称公式修改为： =GET.CELL(53,Sheet1!B2)+NOW()*0
现在公式随时可以变化结果了。这里我们利用了易变函数的特点（参见文章Excel表格为什么那么慢已经应该如何解决（四）一类特殊的函数-易变函数（volatile function)） 04 返回所有的工作表名称列表 定义名称GetSH：
然后使用公式： =INDEX(GetSH,2)将返回第二个工作表的名称：
05 神秘的EVALUATE 在网上众多介绍宏表函数的文章中，总会提到EVALUATE。使用这个函数干什么呢？我们看帮助文档中的说明：
从说明看，这个公式与在编辑栏中选择公式的一部分，然后按F9作用一样（这个操作可以看视频：【Excel公式技巧】如何调试Excel公式）。 我们通过一个例子了解一下这个公式的用法。假设我们有这样的内容：
这些文本实际上是一个一个的可以计算的表达式，如果前面有“=”，直接就会计算出结果。现在没有“=”，我们可以使用EVALUATE计算这些表达式。 定义名称Calc：
然后在B1中输入公式：=Calc，并往下拖拽：
06 老技术又焕发了“青春” 应该说，宏表函数代表的是很老的技术了。基本被遗忘的差不多了，即使在网上还有一些文章介绍类似GET.CELL等函数，实际上这些功能基本上被CELLS，INFO等函数代替的差不多了。但是最近一两年，宏表函数（确切的说，是Excel 4.0 Macro）的热度有点上升，因为： 它被一些黑客盯上了。 仔细想象，还是很有道理的。 首先，这完全是Excel本身的功能，所以警惕性会很低。 其次，这项技术很老了，以至于没有任何安全软件和安全机制去考虑进行这方面的检查，这就意味着使用这些技术制作的恶意软件基本不会被事先阻止。 最后，如果你仔细研究了那500多页的文档，就会发现，这些函数提供了非常丰富而强大的能力，让你可以完全操作Excel，甚至操作系统。 实际上，这些所谓的宏表函数根本就是微软为Excel开发者提供的底层C API。甚至都没有换马甲，因为如果你去查Excel C API的文档（如果你找得到的话），就会发现，这两者一模一样。 所以，还是要小心这个宏表函数啊😁 （其实，也不用过分担心，毕竟只要意识到了，防范还是不太难的）。 而且，这么强大的功能，如果利用好了，可以做很多原来做不到的事情，比如，不用自己写VBA代码了！ 好了，就分享到这里了！ |
|
|
