如何在Excel单个单元格中复用公式仅递增项目工作表编号？

核心思路 #

通过Excel动态数组函数生成连续项目编号，结合单元格引用函数动态指向对应工作表，最后合并所有结果，彻底避免重复编写冗长公式。

最终公式（新版Excel直接回车） #

=TEXTJOIN("", TRUE, IF(AND(INDIRECT("'Project "&SEQUENCE(19,1,2)&"'!$K$2")<=TODAY(), TODAY()<=INDIRECT("'Project "&SEQUENCE(19,1,2)&"'!$U$2"), COUNTIF(INDIRECT("'Project "&SEQUENCE(19,1,2)&"'!$A$6:$A$100"), Sheet1!A6)=1), ", P"&SEQUENCE(19,1,2), ""))

公式拆解 #

1. SEQUENCE(19,1,2)：生成2到20的连续数字（共19个），对应Project 2到Project 20的编号。
2. INDIRECT("'Project "&[编号]&"'!$K$2")：动态拼接工作表名称，自动引用对应项目表的K2、U2单元格及A6:A100区域。
3. IF判断逻辑：对每个项目执行原公式的条件校验，满足时输出", P{N}"（N为项目编号），不满足则输出空文本。
4. TEXTJOIN("", TRUE, ...)：将所有非空结果拼接成完整字符串，TRUE参数自动忽略空值，避免多余分隔符。

兼容旧版Excel的写法（需按Ctrl+Shift+Enter作为数组公式输入） #

=TEXTJOIN("", TRUE, IF(AND(INDIRECT("'Project "&ROW(INDIRECT("2:20"))&"'!$K$2")<=TODAY(), TODAY()<=INDIRECT("'Project "&ROW(INDIRECT("2:20"))&"'!$U$2"), COUNTIF(INDIRECT("'Project "&ROW(INDIRECT("2:20"))&"'!$A$6:$A$100"), Sheet1!A6)=1), ", P"&ROW(INDIRECT("2:20")), ""))

优化说明 #

原公式中两个IF的判断条件完全一致，因此合并为单个IF直接输出目标字符串，减少冗余计算。若需严格保留原公式的拆分逻辑（先输出", "再输出"P{N}"），可使用下方公式（效率略低）：

=TEXTJOIN("", TRUE, IF(AND(INDIRECT("'Project "&SEQUENCE(19,1,2)&"'!$K$2")<=TODAY(), TODAY()<=INDIRECT("'Project "&SEQUENCE(19,1,2)&"'!$U$2"), COUNTIF(INDIRECT("'Project "&SEQUENCE(19,1,2)&"'!$A$6:$A$100"), Sheet1!A6)=1), ", ", ""), IF(AND(INDIRECT("'Project "&SEQUENCE(19,1,2)&"'!$K$2")<=TODAY(), TODAY()<=INDIRECT("'Project "&SEQUENCE(19,1,2)&"'!$U$2"), COUNTIF(INDIRECT("'Project "&SEQUENCE(19,1,2)&"'!$A$6:$A$100"), Sheet1!A6)=1), "P"&SEQUENCE(19,1,2), ""))

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