如何在Excel中将数据分为4列，使各列总和接近总值的1/4

手动贪心分组法（快速上手） #

先算出基础数据：这组数字总和是5087，每列目标总和约为1271.75。

步骤：

1. 把所有数字从大到小排序，优先分配大数值，避免最后大数字无法平衡列总和。
2. 初始化4个空列，每次把当前最大的数字放到当前总和最小的列里，重复操作直到所有数字分配完毕。

按此方法得到的一组近似结果（总和差值极小）：

• 列1（总和1272）：100, 95, 92, 85, 79, 76, 74, 66, 60, 56, 47, 39, 38, 37, 32, 22, 19, 12, 9, 5, 1
• 列2（总和1270）：100, 95, 92, 85, 79, 76, 74, 69, 60, 54, 46, 39, 38, 37, 32, 21, 19, 11, 9, 5, 4
• 列3（总和1273）：97, 93, 92, 85, 79, 78, 75, 65, 62, 58, 51, 45, 38, 37, 34, 26, 20, 18, 11, 9, 4
• 列4（总和1272）：96, 91, 91, 90, 85, 84, 82, 80, 80, 78, 76, 71, 65, 62, 61, 60, 57, 51, 50, 45, 42, 40, 35, 34, 33, 31, 29, 23, 22, 20, 18, 17, 15, 8, 3

Excel规划求解法（精准最优） #

如果需要更精准的平衡结果，使用Excel的规划求解工具：

准备工作 #

1. 在A列粘贴所有原始数字（A1到A95）。
2. 用D:G列作为分配标记：D2:D96、E2:E96、F2:F96、G2:G96分别对应列1到列4的分配（1表示该数字分到对应列，0表示不分）。
3. 计算各列总和与差值：
   • D97输入公式：=SUMPRODUCT(A1:A95,D2:D96)，下拉到G97，得到4列的总和。
   • H97输入公式：=MAX(D97:G97)-MIN(D97:G97)，用来衡量4列总和的差值，我们的目标是最小化这个值。

配置规划求解 #

1. 打开「数据」选项卡的「规划求解」（未找到的话，前往Excel选项→加载项→勾选「规划求解加载项」）。
2. 设置参数：
   • 目标单元格：$H$97，目标选择「最小值」。
   • 可变单元格：$D$2:$G$96。
3. 添加约束条件：
   • $D$2:$G$96 = 二进制（限制单元格只能是0或1）。
   • 对第2行到第96行分别添加约束：$D$n:$G$n = 1（n为行号），确保每个数字只分到一列。
4. 点击「求解」，等待计算完成，即可得到差值最小的分组方案。

提示 #

• 规划求解可能给出局部最优解，多运行几次可能得到更均衡的结果。
• 数据量较大时，求解时间会稍长，请勿中途中断。

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