Excel中如何基于COUNTIF()结果查找特定文本对应的任务编号

前提假设：你的A列数据遵循「任务编号行（含Task#X格式文本）的下一行为该任务对应状态行」的规则，方案均基于该逻辑编写，若实际对应规则不同可按需调整范围匹配逻辑。

1. 状态数量统计 #

你现有的COUNTIF公式已经可以正常使用，如果要避免状态文本被误匹配到任务描述内容，可按需调整通配符：

=COUNTIF(A2:A100, "*work in progress*") & " work in progress"

2. 对应任务编号提取 #

Excel 365/2021及以上版本（支持动态数组） #

可直接返回所有符合条件的任务编号，多个结果自动向下溢出显示：

=LET(
  t_range,A2:A100,
  s_range,A3:A101,
  FILTER(
    MID(t_range,SEARCH("Task#",t_range),IFERROR(SEARCH(" ",t_range&" ",SEARCH("Task#",t_range))-SEARCH("Task#",t_range),LEN(t_range)-SEARCH("Task#",t_range)+1)),
    ISNUMBER(SEARCH("work in progress",s_range)),
    "无符合条件的任务"
  )
)

如果需要将所有编号合并到单个单元格显示，嵌套TEXTJOIN即可：

=TEXTJOIN("、",TRUE,
  LET(
    t_range,A2:A100,
    s_range,A3:A101,
    FILTER(
      MID(t_range,SEARCH("Task#",t_range),IFERROR(SEARCH(" ",t_range&" ",SEARCH("Task#",t_range))-SEARCH("Task#",t_range),LEN(t_range)-SEARCH("Task#",t_range)+1)),
      ISNUMBER(SEARCH("work in progress",s_range)),
      ""
    )
  )
)

Excel 2019及更早旧版本 #

需借助辅助列实现：

1. 新增B列为辅助列，在B2单元格输入公式后下拉到B100，提取符合条件的任务编号：

=IF(AND(ISNUMBER(SEARCH("Task#",A2)),ISNUMBER(SEARCH("work in progress",A3))),MID(A2,SEARCH("Task#",A2),IFERROR(SEARCH(" ",A2&" ",SEARCH("Task#",A2))-SEARCH("Task#",A2),LEN(A2)-SEARCH("Task#",A2)+1)),"")

2. 结果合并：

• 若为Excel 2019版本，直接用TEXTJOIN合并结果：

=TEXTJOIN("、",TRUE,B2:B100)

• 若为2016及更早无TEXTJOIN的版本，可在C列单独列示所有结果，C2输入以下公式，按Ctrl+Shift+Enter三键确认数组公式后下拉：

=IFERROR(INDEX(B:B,SMALL(IF(B$2:B$100<>"",ROW($2:$100),9999),ROW(A1))),"")

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