如何在Excel中按固定数值总和精准分组行数据？

需求说明 #

将表格中B列数值总和精确为5的行进行分组：

• 单个数值为5的行单独成组
• 若数值超过5，或无法找到其他未分组数值凑成总和5，则该行无分组值
• 分组配对可灵活互换（如1和4、2和3均可配对）

样本数据 #

实现步骤（使用Excel公式） #

需添加两个辅助列：C列（已分组标记）和D列（分组编号）

1. 已分组标记列（C列）

在C2单元格输入以下公式，下拉填充至所有行：

=IF(OR(B2>5,SUMIFS(B:B,C:C,"未分组",B:B,5-B2)=0),"未分组",IF(B2=5,"已分组",IF(COUNTIFS($C$2:C2,"未分组",$B$2:B2,5-B2)>=1,"已分组","未分组")))

公式逻辑：

• 若B列数值>5，或找不到未分组的对应数值（5-B2），标记为「未分组」
• 若B列数值=5，直接标记为「已分组」
• 若前面存在未分组的对应数值，标记为「已分组」

2. 分组编号列（D列）

在D2单元格输入以下公式，下拉填充至所有行：

=IF(C2="未分组","",IF(B2=5,MAX($D$1:D1)+1,IF(COUNTIFS($C$2:C2,"未分组",$B$2:B2,5-B2)>=1,MAX($D$1:D1)+1,VLOOKUP(5-B2,$B$2:$D2,3,FALSE))))

公式逻辑：

• 未分组的行显示空值
• 单个数值为5的行，生成新的分组编号（当前最大分组号+1）
• 配对行：若首次出现配对组合，生成新分组号；后续配对项则匹配对应数值的分组号

预期结果 #

应用公式后，D列会生成对应分组编号，最终分组结果如下：

• Group 1：Item A、Item D（1+4=5）
• Group 2：Item B、Item C（2+3=5）
• Group 3：Item E（5=5）
• Group 4：Item F、Item I（1+4=5）
• Group 5：Item G、Item H（2+3=5）
• Group 6：Item J（5=5）

注意事项 #

• 公式依赖数据顺序，若需要不同的配对逻辑，可先对B列排序后再应用公式
• 若存在B列数值为0的行，需在公式中添加排除条件（如AND(B2>0,...)）
• 确保数据区域无空行，否则公式可能出现错误

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