Array Constrain Excel：用 TAKE 或 INDEX 限制数组

=TAKE(
    FILTER('P&L'!C5:O250,'P&L'!B5:B250="基准情景"),
    12,
    13
)

该公式从 P&L 工作表中返回前 12 条匹配记录和 13 列数据。它是 Excel 中最接近 Google Sheets ARRAY_CONSTRAIN 的实现方式，但两者的语法和边界情况并不完全相同。

截至 2026 年 7 月，Microsoft 文档显示，TAKE 可用于 Microsoft 365、Excel 网页版、Excel for Mac 和 Excel 2024。如果工作簿需要在 Excel 2021 或更早的桌面版本中正常运行，仍应准备基于 INDEX 的兼容方案。

Excel 中与 ARRAY_CONSTRAIN 对应的函数是什么？

Google Sheets 使用以下公式限制数组大小：

=ARRAY_CONSTRAIN(source_range,number_of_rows,number_of_columns)

Excel 中最直接的对应写法是：

=TAKE(source_range,number_of_rows,number_of_columns)

例如，一份银团贷款 DCF 模型包含 60 个预测期间和 15 种情景，而授信报告只需要前 20 个季度及前 6 个输出字段：

=TAKE('DCF Output'!B6:P65,20,6)

与通过 IF 包装结果、将多余单元格显示为空白，或让图表引用过大的数据区域相比，TAKE 更适合控制动态数组。它会直接限制溢出数组的尺寸，因此下游公式接收到的是一个 20 行 × 6列的结果，而不是附带大量空字符串的大范围数据。

Microsoft 在 TAKE 函数文档中将其定义为从数组开头或末尾返回指定数量的连续行或列。

Excel 数组限制方法对比

如果不存在版本兼容问题，TAKE 通常是首选。如果财务团队、银行、投资机构或被投企业可能使用较早的 Excel 桌面版本打开文件，INDEX 则更稳妥。

当报告结构确实固定时，手动指定区域也可以接受。真正的风险在于，所谓“固定”区域可能在季度经营分析会前被临时插入两个月的数据，却没有同步更新公式。

如何按行数和列数限制 Excel 数组？

TAKE 的第二个参数控制行数，可选的第三个参数控制列数：

=TAKE('Returns Analysis'!B8:Q67,12,8)

该公式从左上角开始返回 12 行和 8 列。

使用负数参数时，Excel 会从底部或右侧开始提取：

=TAKE('Cash Flow'!B6:M65,-12,-4)

该公式返回最后 12 行和最后 4 列。当现金流预测每月持续扩展时，这种写法尤其适合构建滚动流动性视图。

输出尺寸始终明确。假设一份回报分析包含 15 种情景和 60 个期间，TAKE(...,12,8) 将固定溢出 96 个单元格。与在展示页中横向复制 IF(COLUMN()<=8,...) 判断相比，这种结构更容易复核和审计。

需要特别注意：参数 0 并不表示返回零行。根据 Microsoft 文档，Excel 会返回 #CALC!，用于表示空数组。因此，如果行数来自假设参数，而且可能为零，应设置最小值：

=TAKE('Operating Model'!B7:P66,MAX(1,Assumptions!$B$12),8)

如何限制筛选后的 Excel 数组？

将 TAKE 放在 FILTER、SORT 或 UNIQUE 外层时，实用性更高。内层函数负责生成结果集，TAKE 则限制最终进入报告工作表的数据量。

假设一张包含 186 个 SKU 的边际贡献率明细表需要为季度经营报告提供数据。管理层只关注边际贡献率最低的 10 个 SKU，而不是完整的运营明细：

=TAKE(
    SORTBY(
        FILTER(
            'SKU Economics'!A6:H191,
            'SKU Economics'!H6:H191<Assumptions!$B$18
        ),
        FILTER(
            'SKU Economics'!H6:H191,
            'SKU Economics'!H6:H191<Assumptions!$B$18
        ),
        1
    ),
    10,
    8
)

如果 Assumptions!$B$18 中设置的边际贡献率阈值为 38.5%，该公式会先筛选低于阈值的 SKU，再按边际贡献率从低到高排序，最后返回最多 10 行、8 列数据。

函数顺序非常重要。如果先应用 TAKE，再使用 SORTBY，Excel 会先选取源数据中的前 10 行，然后只对这 10 行进行排序。这回答的是另一个问题，却很容易在审核中被忽略，因为输出结果看起来仍然十分规整。

这一模式也适用于跨工作表的日期筛选：

=TAKE(
    FILTER(
        'P&L'!B:C,
        ('P&L'!B:B>=Assumptions!$B$3)*
        ('P&L'!B:B<=Assumptions!$B$4)
    ),
    12,
    2
)

如果需要的是汇总值，而不是返回数组，则继续使用常规的跨工作表汇总公式：

=SUMIFS('P&L'!C:C,'P&L'!B:B,">="&Assumptions!$B$3)

限制展示数据并不会改变汇总逻辑。它控制的是数据呈现方式和下游溢出依赖，而不是底层数据总体。

不使用 TAKE，如何限制 Excel 数组？

如果需要使用动态数组，但不能使用 TAKE，可以组合使用 INDEX 和 SEQUENCE：

=INDEX(
    'DCF Output'!B6:P65,
    SEQUENCE(MIN(20,ROWS('DCF Output'!B6:P65))),
    SEQUENCE(,MIN(6,COLUMNS('DCF Output'!B6:P65)))
)

两个 MIN 判断可以防止请求的行数或列数超过源区域尺寸。当敏感性分析有时只有 4 个有效情景，而不是预期的 6 个时，这项保护尤为重要。

对于较早版本的数组计算方式，可以使用 INDEX 定义区域右下角：

='DCF Output'!B6:INDEX('DCF Output'!B6:P65,20,6)

该公式创建一个从 B6 开始，到源区域第 20 行、第 6 列单元格结束的引用。它结构紧凑，适合嵌套在接受区域引用的函数中：

=SUM('DCF Output'!B6:INDEX('DCF Output'!B6:P65,20,6))

这种写法并不会在所有场景下完全复制现代动态数组的溢出行为。测试时应覆盖报表实际接收者所使用的 Excel 版本，而不能只验证模型负责人电脑上的版本。

何时应将 DROP 与 TAKE 组合使用？

TAKE 从数组的一端提取数据，DROP 则从一端删除数据。两者组合后，可以截取数组中间的指定窗口。

例如，一份 5 年期预测模型包含前 12 个月的历史数据，以及之后 60 个月的预测数据。要返回预测第 2 年，可先删除前 24 列，再提取接下来的 12 列：

=TAKE(DROP('Monthly Model'!C10:BZ45,,24),,12)

Microsoft 的 DROP 函数文档说明，负数参数会从数组末尾排除指定数量的行或列。因此，在限制 DCF 展示区域之前，DROP 也可用于移除尾部的终值计算列。

季度经营报告可以使用相同模式，从持续滚动的模型中提取 8 个季度：

=TAKE(
    DROP('Quarterly Summary'!B5:AZ30,,Assumptions!$B$20),
    26,
    8
)

其中，Assumptions!$B$20 控制起始列。随着报告窗口滚动，输出仍保持为 26 行 × 8 个季度。

为什么财务模型需要限制数组尺寸？

根据 Microsoft 的 Excel 规格与限制，一个 Excel 工作表最多支持约 104.86 万行和约 1.64 万列。但这并不意味着应在每个工作表中使用整列动态数组。

当实际明细只有 4800 笔交易时，整列公式仍可能检查超过 100 万个单元格。如果再复制到 20 种情景中，工作簿将耗费大量计算资源，只为避免明确指定数据范围。

限制数组还可以减少溢出冲突。Microsoft 的动态数组与溢出行为说明指出，溢出区域被占用时会返回 #SPILL!。对于管理仪表板而言，预留固定 12 行的区域，比预留一个刷新后可能从 9 行突然增长到 43 行的筛选结果更容易管理。

另一个容易被忽视的价值是模型治理。假设估值摘要规定展示 8 家可比公司，并计算出 14.2x 的 EBITDA 倍数中位数，那么将展示结果限制为 8 行，就意味着必须制定明确的排序和筛选规则。未设上限的名单则可能不断扩张，即使自动化流程表面上仍然运行正常。

哪些情况下不应限制 Excel 数组？

不要使用 TAKE 掩盖源数据逻辑不完整的问题。如果现金续航敏感性分析因源区域少覆盖 6 行而遗漏新招聘人员，限制经过整理的输出只会进一步隐藏错误。

在完整性本身就是审核目标时，也不应限制数组。债务余额、关联方往来抵销以及三张财务报表勾稽检查，都应覆盖完整且受控的数据总体。为了让溢出区域更整洁，却漏掉 420 万元的租赁负债，显然得不偿失。

对于 Top N 结果同样需要谨慎。TAKE(FILTER(...),10) 只会按照源数据顺序返回前 10 条符合条件的记录。如果决策需要识别最大风险敞口、最低利润率或最早到期项目，应先明确排序，再提取前 10 条记录。

如需了解 Google Sheets 中的相关溢出数组模式，可参阅面向财务模型的 ARRAYFORMULA 实用指南。无论使用哪种平台，核心原则都相同：即使函数不同，也应主动控制计算结果的形状和尺寸。

常见问题

Excel 是否有与 Google Sheets ARRAY_CONSTRAIN 完全相同的函数？

没有同名函数，但 Microsoft 365 和 Excel 2024 中的 TAKE 是最接近的替代方案。使用 TAKE(array, rows, columns) 即可限制返回数组的行数和列数。

Excel 2021 可以使用 TAKE 吗？

通常不能。需要兼容 Excel 2021 或更早桌面版本时，可使用 INDEX 与 SEQUENCE，或通过 INDEX 定义区域终点。交付共享模型前，应在接收方实际使用的 Excel 版本中测试。

TAKE 请求的行数超过源数组时会怎样？

TAKE 不会凭空补充行或列，而是返回源数组中实际存在的内容。为了让兼容公式的边界行为更明确，可以使用 MIN 将请求数量限制在 ROWS 和 COLUMNS 返回的范围内。

如何返回数组最后 12 行？

将 TAKE 的行数参数设为负数：

=TAKE(A2:H100,-12)

该公式返回数组最后 12 行。列数也可使用负数，从数组右侧提取指定数量的列。

为什么 TAKE 公式返回 #CALC!？

常见原因是请求返回零行或零列。Excel 不支持空数组，因此 TAKE(array,0) 会返回 #CALC!。如果参数可能为零，可使用 MAX(1,参数单元格) 设置最小返回数量。

如何避免 TAKE 返回错误的 Top N 结果？

应先排序，再使用 TAKE。例如，使用 TAKE(SORTBY(array,sort_array,-1),10) 返回排序后的前 10 条记录；直接使用 TAKE(FILTER(...),10) 只会提取符合条件结果中的前 10 行。

总结：限制 Excel 数组的正确方法

如果工作簿运行在 Microsoft 365 或 Excel 2024 中，应优先使用 TAKE。如需创建滚动窗口，可将其与 DROP 组合；如果需要限制最终排序结果的行数或列数，则应将 TAKE 放在 FILTER 或 SORTBY 外层。

如果需要兼容 Excel 2021 或更早的桌面版本，则应使用 INDEX。无论选择哪种方法，都应基于明确的报告要求限制数组，例如 12 个月、8 个季度、10 个 SKU 或 6 种情景。“只是为了让溢出结果放得下”不能作为可靠的建模规范。

查看 ModelMonkey 方案--同时支持 Google Sheets 和 Excel。