Excel如何实现公式结果与位置分离输出，避免动态数据覆盖？

要实现「Projects Won - YTD」数据集自动跟在「Projects Won - Monthly」动态数据集之后（中间保留1行空行），可以通过动态数组公式结合行号定位完成，具体方案如下：

核心逻辑 #

1. 定位「Projects Won - Monthly」数据集的最后非空行
2. 计算YTD数据集的起始位置（最后非空行 + 2，确保中间留1行空行）
3. 让公式在起始位置前返回空值，起始位置及之后返回YTD数据

最终公式 #

将以下公式粘贴到A列任意单元格（建议放在A1，避免被初始数据覆盖）：

=LET(
    // 定位B列最后一个非空单元格的行号（即Monthly数据集的最后一行）
    last_monthly_row, IFERROR(LOOKUP(2, 1/(B:B<>""), ROW(B:B)), 1),
    // YTD数据集的起始行：Monthly最后一行 + 2（中间留1行空行）
    ytd_start_row, last_monthly_row + 2,
    // 当前公式所在的行号
    current_row, ROW(),
    // 你的原始YTD数据集公式
    ytd_data, IFERROR(VSTACK({"Date","Pursuit Lead","Project Name","Est. Amount"},CHOOSECOLS(FILTER(Success_Rate___Monthly___2024,(Success_Rate___Monthly___2024[Stage]="Closed Won")*(Success_Rate___Monthly___2024[Last Name]=I1)),{7,8,6,5})),"No projects won yet this fiscal year"),
    // 计算当前行对应YTD数据的索引
    ytd_row_index, current_row - ytd_start_row + 1,
    // 判断：当前行早于起始行则返回空，否则返回YTD对应行的数据
    IF(current_row < ytd_start_row, "", INDEX(ytd_data, ytd_row_index, COLUMN()))
)

关键部分说明 #

• LOOKUP(2, 1/(B:B<>""), ROW(B:B))：精准定位B列最后一个非空单元格的行号，完美适配动态溢出的数据集
• IFERROR(..., 1)：处理B列无数据的边界情况，默认YTD从第3行开始（1+2）
• INDEX(ytd_data, ytd_row_index, COLUMN())：根据当前行号匹配YTD数据的对应行和列，实现动态溢出填充
• LET函数整合所有变量，逻辑清晰且避免重复计算

注意事项 #

• 确保B列仅包含「Projects Won - Monthly」的动态数据，无其他无关非空单元格，否则会干扰行号定位
• 无论Monthly数据集从B列哪一行开始溢出，公式都能自动识别最后一行并调整YTD位置
• 公式会自动溢出填充，无需手动拖拽，当Monthly数据集长度变化时，YTD数据会实时调整位置

内容的提问来源于stack exchange，提问作者user14915635