Google Sheets多区域未完成任务人员筛选公式优化求助

核心思路 #

1. 将完成度表格的二维数据扁平化，建立「机构+区域」与未完成状态的映射关系
2. 自动处理联系人列表中的All角色，匹配对应机构的所有未完成区域
3. 通过行号匹配，筛选出所有符合条件的人员信息

优化后公式 #

=ARRAYFORMULA(
  LET(
    // 处理完成度表格，生成未完成的「机构|区域」映射列表
    comp_data, PercentageofCompletness!A2:F,
    comp_headers, INDEX(comp_data, 1, 2:6),
    comp_insts, INDEX(comp_data, 2:ROWS(comp_data), 1),
    comp_flat, FLATTEN(comp_insts & "|" & comp_headers),
    comp_status, FLATTEN(INDEX(comp_data, 2:ROWS(comp_data), 2:6)),
    incomplete_map, FILTER(comp_flat, comp_status="Zero" OR comp_status="Not Valid"),
    
    // 处理联系人列表，转换「All」角色为对应机构的所有未完成区域
    contact_data, IMPORTDETAILS!A2:D,
    contact_inst, INDEX(contact_data,,1),
    contact_role, INDEX(contact_data,,4),
    contact_match_keys, IF(
      contact_role="All",
      contact_inst & "|" & TRANSPOSE(FILTER(comp_headers, (comp_status="Zero" OR comp_status="Not Valid") * (comp_insts=contact_inst))),
      contact_inst & "|" & contact_role
    ),
    // 扁平化匹配键，关联原联系人行号
    contact_flat_keys, FLATTEN(contact_match_keys),
    contact_flat_rows, FLATTEN(IF(contact_role="All", ROW(contact_data)-1, ROW(contact_data)-1)),
    
    // 筛选并去重匹配的联系人信息
    matched_rows, UNIQUE(FILTER(contact_flat_rows, COUNTIF(incomplete_map, contact_flat_keys)>0)),
    FILTER(contact_data, ROW(contact_data)-1 IN matched_rows)
  )
)

公式说明 #

• LET函数：通过变量命名简化逻辑，提升公式可读性
• 未完成映射生成：将完成度表格的二维结构转成一维的「机构|区域」字符串，仅保留Zero或Not Valid的条目
• All角色处理：自动为标记All的联系人生成对应机构所有未完成区域的匹配键，无需手动逐个指定
• 行号匹配筛选：通过扁平化后的行号关联，确保All角色的联系人能被正确匹配到所有对应未完成区域，最终去重输出完整人员信息

注意事项 #

• 确保完成度表格的区域表头（如Management、Radiology）与联系人列表的区域名称完全一致（含大小写、空格）
• 新增任务区域时，只需在完成度表格中添加表头，公式会自动适配

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