当前位置：首页 > news >正文

Excel秘技：用宏表函数获取打开的工作簿名与按颜色求和

news 2026/5/12 18:15:36

当Excel内置功能无法满足特殊需求时，如列出所有打开的文件或按单元格背景色求和，隐藏的宏表函数便成为破局关键。本文将揭示两个经典应用场景的解决方案。

在Excel的隐秘角落，宏表函数作为“上古神器”依然有效。它们无法直接输入单元格，但通过“定义名称”即可调用，能解决许多常规函数束手无策的问题。下面通过两个典型案例，展示其独特价值。

一、一键获取所有打开的工作簿名

场景需求

快速生成当前Excel程序中所有已打开工作簿的实时列表，便于在多文件协同工作时进行导航或管理。

解决方案：DOCUMENTS函数

操作步骤：

定义名称：按Ctrl+F3打开名称管理器，点击“新建”。
- 名称：簿
- 引用位置：=DOCUMENTS()
- DOCUMENTS()是宏表函数，用于返回所有打开工作簿的名称数组。
生成列表：在任意单元格（如A2）输入以下公式，并向下填充：

=IFERROR(INDEX(簿, ROW(A1)), "")

- 第2个...工作簿名。
- IFERROR(..., "")：当提取完所有名称后，公式返回空值，避免显示错误。

效果与特点：

列表会动态反映打开的Excel文件状态，新打开或关闭工作簿后，按F9键即可刷新列表。
此方法无需VBA，避免了启用宏的安全警告，非常适合在受限环境中使用。

二、突破限制：对指定背景色的单元格求和

场景需求

在数据表（如B5:B18为数量）中，部分单元格因特殊含义被标记了背景色（如黄色高亮）。现需要快速对所有这些有颜色的单元格进行求和，而Excel没有内置函数可以直接实现。

解决方案：GET.CELL函数

核心原理：
利用GET.CELL(63, reference)宏表函数，它可以返回指定单元格的背景色编号。无色单元格返回0，有颜色的单元格则返回一个特定的非零数字（数字与颜色的对应关系由Excel内部调色板决定）。

操作步骤：

定义辅助名称：
- 名称：CELL
- 引用位置：=GET.CELL(63, 根据颜色汇总!$B5) + INT(RAND())
- 关键点解析：
  - GET.CELL(63, ...)：获取$B5单元格的背景色代码。
  - INT(RAND())：RAND()是易失函数，每次计算都会产生一个介于0到1之间的小数，INT(RAND())的结果恒为0。此处添加的目的是利用其易失性，强制带有宏表函数的定义名称在按F9时能够重新计算，从而在背景色改变后能更新结果。
创建颜色判断列：在C5单元格输入公式=CELL，并向下填充至C18。此列将显示B列对应行的背景色代码。
执行条件求和：在目标单元格（如B19）输入以下数组公式（在旧版Excel中需按Ctrl+Shift+Enter输入）：

=SUM(IF(C5:C18, B5:B18))

- 公式逻辑：IF(C5:C18, B5:B18)会判断C5:C18区域。在Excel中，非零数值视作TRUE，零值视作FALSE。因此，此函数会仅返回那些C列有颜色代码（非零）所对应的B列数值，最后用SUM对这些值求和。

重要补充：为何要加`INT(RAND())`？

宏表函数如GET.CELL的结果默认不会自动重算，即使更改了单元格颜色，之前的结果也可能保持不变。通过连接一个RAND()这样的易失函数（它本身结果会变，但INT(RAND())永远为0），可以“欺骗”Excel在每次工作表计算时都重新执行整个定义名称的运算，从而保证颜色判断的实时性。

三、方案总结与对比

功能	所用宏表函数	关键技巧	传统替代方案
获取打开的工作簿名	`DOCUMENTS()`	定义名称+`INDEX`提取	手动记录或VBA
按单元格背景色求和	`GET.CELL(63)`	定义名称+易失函数触发更新	手动筛选后求和或VBA