谷歌表格如何基于双日期与空单元格条件跨表统计参训员工数

问题说明 #

• 有多张格式统一的员工培训信息表，单表记录员工培训开始日期、培训结束日期、离职状态
• 汇总表通过IMPORTRANGE函数拉取所有团队培训表的数据
• 需统计当前在培员工数，统计规则：
  • 培训开始日期 ≤ 当日
  • 培训结束日期 ≥ 当日
  • 离职列单元格为空
• 现有公式返回结果始终为0，原公式如下：

=ArrayFormula(COUNTIFS('tracker'!$B$2:$B, "<="&TODAY(), 'tracker'!$C$2:$C, ">="&TODAY(), 'tracker'!$D$2:$D,""))

故障排查与解决 #

核心原因：IMPORTRANGE拉取的日期默认是文本格式，无法和日期类型的TODAY()函数返回值正常比对 #

你可以先在空白单元格输入=ISDATE('tracker'!B2)验证，如果返回FALSE即可确认是格式问题，有两种解决方式：

1. 调整列格式：选中tracker表的B、C列，点击顶部菜单「格式」→「数字」→「日期」，把所有日期转为标准日期格式，原公式即可正常运行
2. 不修改原表格式，直接在公式中转换格式，调整后公式如下：

=COUNTIFS(
  ARRAYFORMULA(DATEVALUE('tracker'!$B$2:$B)), "<="&TODAY(),
  ARRAYFORMULA(DATEVALUE('tracker'!$C$2:$C)), ">="&TODAY(),
  'tracker'!$D$2:$D, ""
)

多表汇总直接调用IMPORTRANGE的写法 #

如果你的数据没有落地到tracker工作表，是直接拉取多个团队表的数据，可直接把IMPORTRANGE作为参数传入，多表数据用{}拼接即可，示例：

=COUNTIFS(
  ARRAYFORMULA(DATEVALUE({IMPORTRANGE("团队1表格ID","表名!B2:B");IMPORTRANGE("团队2表格ID","表名!B2:B")})), "<="&TODAY(),
  ARRAYFORMULA(DATEVALUE({IMPORTRANGE("团队1表格ID","表名!C2:C");IMPORTRANGE("团队2表格ID","表名!C2:C")})), ">="&TODAY(),
  {IMPORTRANGE("团队1表格ID","表名!D2:D");IMPORTRANGE("团队2表格ID","表名!D2:D")}, ""
)

注：首次使用IMPORTRANGE时需要点击弹窗的「允许访问」完成授权。

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