Google Sheets中非零单元格对应角色与云服务合并及自动维护公式需求

一、单列合并文本输出（格式：云服务: 角色1, 角色2） #

假设你的数据结构为：

• A列：角色/组名称（A2开始为数据，A1为表头）
• B列及以后：各云服务名称（B1、C1...为服务名，B2、C2...为布尔值TRUE/FALSE，标记角色是否属于该服务）

使用以下单单元格动态公式，支持自动识别新增/删除的角色或服务列，自动忽略无对应角色的服务：

=TEXTJOIN(CHAR(10), TRUE, BYCOL(INDIRECT("B1:"&ADDRESS(1, COUNTA(1:1))), LAMBDA(col, 
  LET(
    service_name, INDEX(col, 1),
    assigned_roles, TEXTJOIN(", ", TRUE, FILTER(A:A, OFFSET(col, 1, 0)=TRUE)),
    IF(assigned_roles<>"", service_name&": "&assigned_roles, "")
  )
)))

公式说明： #

• INDIRECT("B1:"&ADDRESS(1, COUNTA(1:1)))：动态获取第一行所有云服务列，新增服务列时自动纳入
• BYCOL：遍历每个服务列，对列内数据执行逻辑处理
• FILTER(A:A, OFFSET(col,1,0)=TRUE)：筛选出当前服务下的所有关联角色
• TEXTJOIN：将同一服务的角色用逗号分隔，再与服务名拼接，最终所有结果用换行符分隔

二、双列拆分输出（云服务列 + 角色列） #

同样基于上述数据结构，使用动态数组公式生成拆分后的两列结果，自动适配行列变化：

=LET(
  services, INDIRECT("B1:"&ADDRESS(1, COUNTA(1:1))),
  roles, INDIRECT("A2:A"&COUNTA(A:A)),
  all_mappings, {FLATTEN(TRANSPOSE(services)), FLATTEN(roles)},
  valid_mappings, FILTER(all_mappings, FLATTEN(INDIRECT("B2:"&ADDRESS(COUNTA(A:A), COUNTA(1:1))))=TRUE),
  IFERROR(valid_mappings, "无对应角色")
)

公式说明： #

• LET：定义变量简化公式结构，提升可读性
• TRANSPOSE(services)：将服务行转为列，与角色列交叉生成所有可能的映射组合
• FILTER：筛选出标记为TRUE的有效角色-服务关联
• 新增角色/服务时，INDIRECT会自动扩展范围，无需手动调整公式

额外优化（参照完整性） #

1. 数据验证：给B2及以后的单元格设置下拉选项（仅允许TRUE/FALSE），避免无效输入
2. 条件格式：给云服务表头列（B1、C1...）设置规则：若该列除表头外全为FALSE，则高亮标记，提示"无对应角色"

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