ARTICLE · 1296408

Excel效率神器-宏表函数使用

发布时间：2026-11-02 15:45:45 最近访问：2026-11-02 15:45:45

Excel效率神器-宏表函数使用

点击上方「蓝字」关注我们

一、什么是宏表函数

宏表函数是 Excel 4.0（1992 年）引入的遗留函数体系，属于 VBA 的前身。虽然从 Excel 5.0 起已被 VBA 逐步取代，但微软为兼容旧版文件仍保留了调用入口。它们无法像普通函数那样直接在单元格中输入，必须通过“定义名称”间接调用，且文件需保存为 .xlsm 格式。

核心限制速查

限制项	说明
调用方式	只能通过“定义名称”使用，不能直接键入单元格
保存格式	必须另存为“Excel 启用宏的工作簿 (*.xlsm)”
自动更新	默认不刷新，需按 `Ctrl+Alt+F9` 或连接 `&T(NOW())` 强制刷新
性能	运行速度较慢，不宜大规模使用

二、使用前的必要设置

部分 Excel 版本默认禁用了 Excel 4.0 宏。如果定义名称后公式返回错误，请检查：

1.点击文件 → 选项 → 信任中心 → 信任中心设置。

2.进入宏设置。

3.勾选 “启用 Excel 4.0 宏（当启用 VBA 宏时）” 。

三、案例一：用 EVALUATE 计算文本算式

场景

A 列存放着以文本形式表达的算式（如 3*4+5、(100-20)/4），需要批量算出结果。

操作步骤

第 1 步：定义名称

1.选中 B1 单元格（或任意空白单元格）。

2.按 Ctrl+F3 打开“名称管理器”，点击“新建”。

3.名称输入：计算

4.引用位置输入：=EVALUATE(Sheet1!$A1)

5.点击确定。

第 2 步：调用公式

在 B1 单元格输入：=计算，然后向下填充。

效果

A 列（文本算式）	B 列（计算结果）
`3*4+5`	`17`
`(100-20)/4`	`20`
`2^10`	`1024`

注意事项

EVALUATE 对文本算式的长度限制为 251 个字符。
若 A 列内容变化后 B 列未更新，按 Ctrl+Alt+F9 强制重算。
如果想让结果自动刷新，可将引用位置改为：=EVALUATE(Sheet1!$A1)+NOW()*0 。

四、案例二：用 GET.CELL 按背景色筛选/统计

场景

A 列是任务列表，部分单元格被手动标了黄色背景表示“紧急”。需要把所有黄色单元格提取到 C 列。

操作步骤

第 1 步：定义名称获取颜色代码

1.按 Ctrl+F3 → 新建。

2.名称输入：颜色

3.引用位置输入：=GET.CELL(63,Sheet1!$A1)

4.确定。

第 2 步：辅助列提取颜色值

在 B1 输入 =颜色，向下填充。B 列会显示每个单元格的背景色代码（黄色通常为 6 或 44，取决于具体色值）。

第 3 步：筛选并提取

对 B 列筛选出黄色对应的代码，将对应 A 列的值复制到 C 列即可。

GET.CELL 常用参数

参数	返回内容
6	单元格中的公式文本
63	背景色代码
24	字体颜色代码
46	是否含批注（TRUE/FALSE）

提示：GET.CELL 的结果不会因单元格格式改变而自动更新，修改颜色后需按 Ctrl+Alt+F9 重算。

五、案例三：用 FILES 制作文件目录

场景

某个文件夹下有大量文件，希望在 Excel 中生成可点击打开的目录列表。

操作步骤

第 1 步：定义名称

1.按 Ctrl+F3 → 新建。

2.名称输入：文件列表

3.引用位置输入：

=FILES("C:\你的文件夹路径\*.*")&T(NOW())

将路径替换为实际文件夹路径。

4.确定。

第 2 步：提取文件名

在 A1 输入：=IFERROR(INDEX(文件列表,ROW(A1)),"")，向下填充足够多的行（比如 100 行），以预留新增文件的位置。

第 3 步：生成超链接

在 B1 输入：

=IFERROR(HYPERLINK("C:\你的文件夹路径\"&A1,A1),"")

向下填充。点击 B 列的文件名即可直接打开对应文件。

注意事项

FILES 函数最多返回 256 个文件名，且无法获取嵌套子文件夹中的文件。

&T(NOW()) 的作用是强制刷新，否则新增文件后列表不会更新。

六、案例四：用 GET.WORKBOOK 提取工作表名称

场景

工作簿中有多个工作表，需要在“目录页”中列出所有表名。

操作步骤

第 1 步：定义名称

1.按 Ctrl+F3 → 新建。

2.名称输入：表名列表

3.引用位置输入：=GET.WORKBOOK(1)&T(NOW())

4.确定。

第 2 步：提取表名

在 A1 输入：

=IFERROR(INDEX(表名列表,ROW(A1)),"")

向下填充。返回的结果格式为 [工作簿名.xlsx]Sheet1。

第 3 步：去掉工作簿名前缀（可选）

如果使用 Excel 365，可直接用 TEXTAFTER 提取 ] 之后的内容：

=TEXTAFTER(表名列表,"]")

如果是旧版 Excel，可用 REPLACE + FIND 组合，或手动分列处理。

七、常见问题与替代建议

问题	解决方式
公式返回 `#VALUE!`
结果不更新	按 `Ctrl+Alt+F9`，或在定义名称时加 `&T(NOW())`
保存后公式丢失	必须另存为 `.xlsm` 格式
EVALUATE 算式太长	限制 251 字符，超长需拆分或改用 VBA