Excel 中整列引用的隐藏成本：了解 3 种替代方法优化工作簿性能
Excel 中的整列引用会严重影响性能，通常会迫使程序处理超过一百万行的范围。因此，它们可能会显著降低工作簿的运行速度。因此，停止使用 A:A 引用，改为创建动态范围。
百万行税的隐性成本
整列引用（A:A）可能会迫使 Excel 处理 1,048,576 行，尤其是在进行数组式计算或评估每个单元格的函数时。以这个 50 行的数据集为例。在单元格 D1 中，你想使用 UNIQUE 函数列出 B 列中所有的人名。
为了确保公式能够获取添加到第51行、第52行及后续行的任何新数据，你可以使用整列引用：
=UNIQUE(B:B)这个公式会检查 B 列中的所有单元格——包括活动范围下方的空行，这些空行由异常的零表示。
零测试：证明责任
如果你想准确地看到在输入整列引用时你让 Excel 处理的内容，可以在 D1 单元格中输入：
=B:B当你向下滚动时，你会看到超过一百万个尾随零，这些零表示从第51行开始的B列中的空白单元格。
虽然有些函数会忽略这些空白格，但有些函数会将它们视为有效输入，返回零值，这不仅使结果杂乱无章，还表明引擎被迫处理空白内容。
现代 Excel 使用“范围筛选”来跳过空行，但它依赖于已用范围是干净的。如果第 10,000 行包含一个多余的空格，Excel 会将整个空白区域视为有效数据。此外，公式仍然必须与整个范围交互以验证边界。
这里有三个内置工具可以帮助你防止这种行为。
解决方案 1：将您的数据转换为 Excel 表格
避免百万行限制的最可靠方法是使用 Excel 表格——一种动态对象，它会完全改变 Excel 计算数据的方式。
表格用明确的边界替代了整列引用的巨大范围。如果你的数据有50行，表格对象的长度正好是50行，因此当公式引用表格时，Excel 的计算引擎会将注意力限制在该容器内。
首先，确保您的数据集有列标题。接下来，选择范围内的任意单元格，按 Ctrl+T，在“创建表格”对话框中，确保勾选“我的表格有标题”。然后，点击“确定”。
表格使用结构化引用，这不仅更易于阅读，还能锁定数据的维度。因此，要列出 B 列中使用的所有唯一名称，您需要将公式从无限制的列扫描更改为针对表格的搜索：
=UNIQUE(Table1[Manager])请注意，结果底部的零已经消失，因为公式中只引用了表格范围内的数据。
要更改表格的名称（例如将 Table1 改为 T_Managers），请选择范围内的任意单元格，打开“表格设计”选项卡，然后在表格名称字段中输入新名称。除了许多其他好处之外，这还能让公式更易于阅读。
这种方法比整列引用更好，有两个原因：
- 零浪费计算：由于表格有明确的结束，Excel不需要处理数据下方的空白区域。
- 自我管理的范围： 当你向第51行添加数据时，表格边界会自动向下扩展一行，而且你的公式会紧贴这个范围，无需任何手动调整。
解决方案 2：使用 TRIMRANGE 函数
如果你不想将 Excel 数据转换为正式表格，TRIMRANGE 函数是下一个最佳选择。它让你像以前一样使用整列引用，但会告诉 Excel 仅裁剪包含实际数据的单元格范围。
TRIMRANGE 函数仅适用于使用 Microsoft 365 版 Excel、网页版 Excel 以及 Excel 移动和平板应用的用户。
TRIMRANGE 有三个参数：
=TRIMRANGE(range,[trim_rows],[trim_cols])
在哪里：
- range（必填）是要修剪的范围。
- trim_rows（可选）告诉 Excel 是否去掉开头的空行（1）、末尾的空行（2）或两者（3）。
- trim_cols（可选）告诉 Excel 是否去掉开头的空白列（1）、结尾的空白列（2）或两者（3）。
要将此逻辑应用到您的数据集，请输入：
=UNIQUE(TRIMRANGE(B:B,2))
这里，"2" 强制公式去除末尾的空单元格。由于公式中只引用了一个列，因此无需引入 trim_cols 参数。
如果你双击单元格 D1，视觉选择框仍会环绕整个列。这是一个用户界面显示功能，可以让你轻松看到函数所基于的源范围。然而，TRIMRANGE 函数是对计算引擎的内部指令，因此视觉选择并不反映公式实际处理的优化范围。
这就是为什么这是处理整列引用的一个好方法的原因：
- 动态且整洁的引用： 您可以获得整列引用的灵活性——它仍然能够找到您之后添加的新数据。然而，计算引擎仅在 50 行活动数据上执行。
- 网格灵活性：您可以获得类似表格的性能，而无需更改工作表的视觉格式
解决方案 3：使用 trim ref 操作符
Excel 的修剪引用运算符提供了与 TRIMRANGE 函数完全相同的好处，但需要输入的内容更少。
TRIM 引用运算符的可用性与 TRIMRANGE 函数相同：Microsoft 365 版 Excel、网页版 Excel 以及 Excel 移动和平板应用。
通过在范围的冒号两边添加一个句点 (.)，你可以告诉 Excel 从该方向去掉空单元格：
句号应该加在哪里 | 哪些空格被修剪 |
|---|---|
冒号之后 | 尾随空格 |
冒号前 | 前导空格 |
冒号的两边 | 前导和尾随空格 |
所以，继续使用之前的同一列表，输入：
=UNIQUE(B:.B)
冒号后的部分告诉 Excel 在包含数据的最后一行停止——在本例中是第 50 行。
这是一个好方法，因为：
- 极简公式：您可以获得高性能的优势，而无需更改电子表格的视觉格式或在额外函数中包装范围。
- 精确控制： 你可以轻松选择并更改要忽略的空白，通过将点放置在与冒号相对的适当位置来实现。
- 清理数据结果：此方法可防止现代功能拾取尾随零，确保您的结果保持准确且无虚假数据。
除了优化你的公式外，还要记住这些习惯，以保持你的 Excel 工作簿高效：
- 限制过度格式化：不要对整列应用边框或颜色。
- Ctrl+End 技巧： 使用此快捷键可以找到 Excel 认为数据结束的位置。如果它跳到第 1,000,000 行，那说明你有“幽灵单元格”。
- 重置网格：如果有空白单元格，选择空行，右键点击，然后选择“删除”（不要只是按退格键）。然后，立即保存您的工作簿，以重置最后一个单元格并缩小文件大小。