Excel按分类统计球员人数时如何去重 避免同球员重复计数

问题说明 #

现有记录球员、球衣号码、擅长运动专项及专项对应分类的数据集，此前使用如下公式实现了逗号分隔数字类别的总计数，但存在同一名球员参与同一分类下多个项目时被重复计数的问题：

=SUM(--(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,SUBSTITUTE($D$3:$D$16,",","</s><s>"))&"</s></t>","//s[.=" & K3 & "]")<>""))

需求为统计指定分类下参与任意对应运动的球员人数，同一名球员即使参与多个该分类下的项目，仅计数1次。
工作表参考截图：

可用公式 #

根据你使用的Excel版本选择对应公式即可，使用前请替换两个自定义区域：

• 「专项名称区域」：替换为工作表中存储所有运动专项名称的单元格范围
• 「专项分类区域」：替换为工作表中存储每个专项对应所属分类的单元格范围
  公式默认参数：K3为当前要统计的目标分类所在单元格，A3:A16为球衣号（球员唯一标识）所在区域，D3:D16为球员擅长的逗号分隔专项所在区域，可根据实际表结构调整。

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

直接在统计结果单元格输入如下公式即可，无需额外数组确认操作：

=COUNTA(UNIQUE(FILTER($A$3:$A$16,BYROW($D$3:$D$16,LAMBDA(playersp,SUM(--ISNUMBER(SEARCH(TRANSPOSE(FILTER(专项名称区域,专项分类区域=K3)),playersp)))>0))))

公式逻辑：逐行判断每个球员填报的专项中，是否包含至少1个目标分类下的专项，筛出所有符合条件的球员球衣号，去重后统计总数量，天然避免同一球员多项目重复计数。

2019及更早不支持动态数组的旧版Excel #

输入如下公式后，按下Ctrl+Shift+Enter三键确认数组公式生效：

=SUMPRODUCT((MMULT(--ISNUMBER(SEARCH(TRANSPOSE(IF(专项分类区域=K3,专项名称区域,""))),$D$3:$D$16)),ROW(INDIRECT("1:"&COUNTIF(专项分类区域,K3)))^0)>0/COUNTIF($A$3:$A$16,$A$3:$A$16&""))

注意事项 #

• 如果专项名称存在短名称被长名称包含的情况（比如同时存在“球”“足球”两个专项），可以将SEARCH判断替换为FILTERXML拆分单元格后精确匹配的逻辑，避免统计错误
• 如果用姓名作为球员唯一标识，将公式中引用球衣号列（A列）的范围替换为姓名列范围即可

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