Excel：按ID统计关联唯一企业数量并新增列展示的方法咨询

原始数据 #

预期效果 #

以下是不同工具的可行实现方案：

一、Excel 操作 #

1. 假设数据在A列（ID）、B列（企业），新增C列设为Count
2. 在C2单元格输入公式：=SUMPRODUCT(($A$2:$A$6=A2)/COUNTIF($B$2:$B$6,$B$2:$B$6&$A$2:$A$6))，下拉填充至所有行
   • 公式逻辑：通过COUNTIF计算每个「ID+企业」组合的出现次数，再用SUMPRODUCT统计每个ID下的唯一企业数量
3. 筛选：选中数据区域，点击「数据」→「筛选」，在Count列的筛选器中选择「大于或等于」并输入2，即可得到目标记录

二、SQL 实现 #

假设表名为company_data，字段为id和enterprise：

方法1：窗口函数直接筛选 #

SELECT 
    id,
    enterprise,
    COUNT(DISTINCT enterprise) OVER (PARTITION BY id) AS count
FROM company_data
WHERE COUNT(DISTINCT enterprise) OVER (PARTITION BY id) >= 2;

方法2：先聚合再关联 #

WITH id_unique_count AS (
    SELECT id, COUNT(DISTINCT enterprise) AS count
    FROM company_data
    GROUP BY id
    HAVING count >= 2
)
SELECT 
    t.id,
    t.enterprise,
    uc.count
FROM company_data t
JOIN id_unique_count uc ON t.id = uc.id;

三、Python Pandas 实现 #

import pandas as pd

# 构造数据（实际场景可通过pd.read_csv/pd.read_excel读取）
df = pd.DataFrame({
    'ID': [123, 123, 123, 456, 456],
    '企业': ['ABC', 'DEF', 'ABC', 'ABC', 'ABC']
})

# 新增Count列：按ID分组，统计每组内唯一企业的数量
df['Count'] = df.groupby('ID')['企业'].transform('nunique')

# 筛选Count≥2的记录
filtered_df = df[df['Count'] >= 2]

print(filtered_df)

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