Google Sheets如何按条件提取首个匹配行数据自动填充项目分析表

前提假设：project analysis表A列为项目名称，数据从A2行开始，B列存项目全局开始日期，C列存项目首个完成阶段的结束日期；raw sheet表A列为项目名称（Projects）、B列为阶段开始日期（start date）、C列为阶段结束日期（end date）、D列为阶段状态（status），阶段数据从第2行开始存储。

1. 自动填充全局开始日期 #

在project analysis表B2单元格输入以下公式，下拉填充所有项目行即可：

=XLOOKUP(A2, 'raw sheet'!A:A, 'raw sheet'!B:B, "无匹配项目", 0, 1)

公式逻辑：按从上到下的顺序匹配raw sheet中第一个和当前项目名一致的记录，返回对应阶段的开始日期，刚好符合「取项目第一条记录开始日期」的规则。
如果使用的是不支持XLOOKUP的旧版本Google Sheets，可替换为INDEX+MATCH组合：

=INDEX('raw sheet'!B:B, MATCH(A2, 'raw sheet'!A:A, 0))

2. 自动填充首个完成阶段的结束日期 #

在project analysis表C2单元格输入以下公式，下拉填充所有项目行即可：

=XLOOKUP(1, ('raw sheet'!A:A=A2)*('raw sheet'!D:D="Done"), 'raw sheet'!C:C, "无已完成阶段", 0, 1)

公式逻辑：多条件匹配raw sheet中第一个项目名一致、且状态为Done的记录，返回对应阶段的结束日期，逻辑会自动处理项目还没有完成阶段的情况，返回自定义提示。
也可以用FILTER+INDEX组合实现相同效果：

=IFERROR(INDEX(FILTER('raw sheet'!C:C, 'raw sheet'!A:A=A2, 'raw sheet'!D:D="Done"),1), "无已完成阶段")

可选：整列自动填充无需手动下拉 #

如果不想每次新增项目都手动下拉公式，可以把公式套入ARRAYFORMULA放在表头行，实现整列自动匹配：

• B1（全局开始日期表头单元格）公式：

=ARRAYFORMULA(IF(A:A="",,IF(A:A="此处替换为你A列的项目表头文字", "start date", XLOOKUP(A:A, 'raw sheet'!A:A, 'raw sheet'!B:B, "无匹配项目", 0, 1))))

• C1（结束日期表头单元格）公式：

=ARRAYFORMULA(IF(A:A="",,IF(A:A="此处替换为你A列的项目表头文字", "end date", XLOOKUP(A:A&"Done", 'raw sheet'!A:A&'raw sheet'!D:D, 'raw sheet'!C:C, "无已完成阶段", 0, 1))))

注意事项 #

• 公式中的工作表名、列范围如果和你实际表格设置不一致，请对应修改
• 如果公式返回数字不显示日期，选中对应列后点击顶部菜单栏「格式」→「数字」→「日期」即可调整显示格式
• 如果raw sheet数据量超过10万行，建议把整列引用（比如A:A）替换为实际数据范围（比如A2:A10000），提升公式运行速度

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