Excel中按分组变量进行加权随机抽样的实现方法

针对按A(60%)、B(20%)、C(15%)、D(5%)权重抽取10个不重复ID的需求，以下是Excel和Python（Pandas）两种常用工具的实现方法：

Excel 实现步骤 #

1. 确定各分组抽样数量 #

总抽取数为10，按权重计算得：

• A组：6个，B组：2个，C组：1个，D组：1个

2. 生成随机标记避免重复 #

在空白列（如C列）输入=RAND()，按Ctrl+Enter为所有ID生成唯一随机数，用于后续排序抽样。

3. 排序抽取（通用方法） #

• 选中所有数据（分组列、ID列、随机数列），按「分组列」升序、「随机数列」升序排序。
• 分别提取A组前6个、B组前2个、C组前1个、D组前1个的ID，复制到目标列即可。

3. 动态数组一键生成（Excel 365/2021+） #

直接在目标列第一个单元格输入以下公式，会自动溢出所有抽样ID：

=LET(
    data, A:B,
    groups, INDEX(data,,1),
    ids, INDEX(data,,2),
    sample_nums, XLOOKUP(groups, {"A","B","C","D"}, {6,2,1,1}),
    sampled, BYROW(UNIQUE(groups), LAMBDA(g, TAKE(SORT(FILTER(ids, groups=g), RANDARRAY(COUNTA(FILTER(ids, groups=g)))), XLOOKUP(g, {"A","B","C","D"}, {6,2,1,1})))),
    FLATTEN(sampled)
)

Python（Pandas）实现代码 #

假设你的数据已整理为包含group（分组）和id（ID列）的DataFrame：

import pandas as pd

# 替换为你的实际数据读取方式，比如pd.read_excel("your_data.xlsx")
data = pd.DataFrame({
    'group': ['A']*20 + ['B']*10 + ['C']*8 + ['D']*5,
    'id': range(43)
})

# 定义权重与总抽样数
weight_map = {'A': 0.6, 'B': 0.2, 'C': 0.15, 'D': 0.05}
total = 10

# 计算各分组需抽取的数量
sample_counts = {g: round(total * w) for g, w in weight_map.items()}

# 按分组无重复抽样
sampled_data = data.groupby('group').apply(
    lambda df: df.sample(n=sample_counts[df.name], replace=False)
).reset_index(drop=True)

# 将抽样ID写入原数据的新列
data['sampled_id'] = data['id'].where(data['id'].isin(sampled_data['id']))

# 查看结果
print("抽样ID:", sampled_data['id'].tolist())
# 保存结果到Excel
# data.to_excel("sampled_result.xlsx", index=False)

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