如何让公式自动识别团队编号变化并仅应用于对应团队

需求说明 #

• 有带编号的团队列表（A列为团队编号，B列为团队成员的分号分隔值）
• 需要自动检测团队编号变化，计算每个团队的所有成员共同拥有的值
• 不使用VBA/宏，原IF(A2<>A3;...)的方法无法适配数十行的动态数据

改进后的动态公式 #

针对动态团队范围的需求，修改原公式为自动适配当前团队所有成员的版本（以Excel 365/2021为例，根据地区设置调整分隔符，以下为分号版本）：

=LET(
    team; A2;
    teamRng; FILTER(B:B; A:A=team);
    col; TOCOL(TEXTSPLIT(TEXTJOIN(";";TRUE;teamRng);";";;;TRUE;TRUE));
    commonVals; TOCOL(IF(BYROW(UNIQUE(col);LAMBDA(x;SUM(--(x=col))))=ROWS(teamRng);UNIQUE(col);1/0);3);
    TEXTJOIN(";";;;commonVals)
)

公式逻辑解析 #

• team; A2：捕获当前行的团队编号
• teamRng; FILTER(B:B; A:A=team)：动态筛选出该团队所有成员的B列值，自动适配团队行数的增减
• col; TOCOL(...)：将团队所有成员的分号分隔值拆分为单个值的一维数组
• commonVals; TOCOL(...)：统计每个唯一值的出现次数，筛选出出现次数等于团队成员总数的数值（即所有成员共享的值）
• TEXTJOIN(...)：将共同值重新用分号拼接输出

使用方法 #

1. 在C2单元格输入上述公式
2. 下拉公式至所有数据行，每个团队的行都会自动计算对应团队的共同值
3. 当新增/删除团队成员、修改团队编号时，公式会自动更新结果

注意事项 #

• 需使用支持LET/FILTER/TEXTSPLIT等函数的Excel版本（365/2021及以上）
• 确保A列团队编号无空值，B列的分号分隔格式统一

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