Excel技术需求：统计同Business_registration_ID对应其他位置的唯一注册号数

方法一：使用动态数组公式（适用于Excel 365/2021及以上版本） #

假设数据位于A:C列（A=BusinessId，B=Business_registration_ID，C=Location_ID），计算列放在D列，表头为“其他位置使用的唯一Business_registration_ID数量”。

在D2单元格输入以下公式，回车后公式会自动填充到所有行：

=LET(
    currentReg, B2,
    currentLoc, C2,
    regLocs, UNIQUE(FILTER($C$2:$C$8, $B$2:$B$8=currentReg)),
    otherLocs, FILTER(regLocs, regLocs<>currentLoc),
    otherRegs, FILTER($B$2:$B$8, ISNUMBER(XMATCH($C$2:$C$8, otherLocs))),
    COUNT(UNIQUE(FILTER(otherRegs, otherRegs<>currentReg)))
)

公式逻辑说明： #

• currentReg和currentLoc提取当前行的注册ID与位置ID；
• regLocs筛选当前注册ID对应的所有唯一位置；
• otherLocs排除当前行的位置，得到该注册ID关联的其他位置；
• otherRegs提取这些其他位置中出现的所有注册ID；
• 最后过滤掉当前注册ID，统计剩余唯一注册ID的数量，无符合项则返回0。

方法二：使用Power Query（适用于所有支持Power Query的Excel版本） #

若Excel版本不支持动态数组，或数据量较大，Power Query更高效：

1. 选中数据区域，点击数据选项卡→从表格/区域，将数据导入Power Query编辑器；
2. 添加自定义列，提取当前注册ID对应的所有唯一位置，公式：

   List.Distinct(Table.SelectRows(#"Changed Type", (x)=>x[Business_registration_ID]=[Business_registration_ID])[Location_ID])
   

   命名为Reg关联位置；
3. 添加自定义列，排除当前行位置，得到其他关联位置，公式：

   List.RemoveItems([Reg关联位置], {[Location_ID]})
   

   命名为其他关联位置；
4. 添加自定义列，提取其他关联位置中的所有注册ID，公式：

   Table.SelectRows(#"Changed Type", (x)=>List.Contains([其他关联位置], x[Location_ID]))[Business_registration_ID]
   

   命名为其他位置的RegID；
5. 添加自定义列，统计符合条件的唯一注册ID数量，公式：

   List.Count(List.Distinct(List.RemoveItems([其他位置的RegID], {[Business_registration_ID]})))
   

   命名为其他位置使用的唯一Business_registration_ID数量；
6. 删除中间辅助列，点击关闭并上载，将结果导出到Excel表格。

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