Excel Solver与Python版OR-Tools求解器结果差异原因排查

问题 #

使用OR-Tools 9.9.3963的CLP求解器实现线性规划模型，发现与Excel Solver的求解结果存在1的差异：Excel Solver结果为1,191,892,387，OR-Tools结果为1,191,892,386。Python代码如下：

from ortools.linear_solver import pywraplp

def LinearProgrammingExample():
    # 初始化求解器
    solver = pywraplp.Solver.CreateSolver('CLP')

    # 创建变量x1到x31
    infinity = solver.infinity()
    for x in range(1, 32):
        globals()[f"x{x}"] = solver.NumVar(0.0, infinity, f"x{x}")

    print('Number of variables =', solver.NumVariables())

    # 定义约束相关的分组变量
    l1  =  sum([x1,x8,x15,x22,x29,x30])
    l1_dl  =  sum([x1,x8,x15,x22])
    l1_bds  =  sum([x8])
    l1_hq  =  sum([x29,x30])

    l2  =  sum([x2,x9,x16,x23])
    l2_dl  =  sum([x2,x9,x16,x23])
    l2_bds  =  sum([x9])

    l3  =  sum([x3,x10,x17,x24])
    l3_dl  =  sum([x3,x10,x17,x24])
    l3_bds  =  sum([x10])

    l4_2  =  sum([x4,x11,x18,x25])
    l4_2_dl  =  sum([x4,x11,x18,x25])
    l4_2_bds  =  sum([x11])
    
    l4_3  =  sum([x5,x12,x19,x26])
    l4_3_dl  =  sum([x5,x12,x19,x26])
    l4_3_bds  =  sum([x12])

    l5  =  sum([x6,x13,x20,x27])
    l5_dl  =  sum([x6,x13,x20,x27])
    l5_bds  =  sum([x13])

    l6  =  sum([x7,x14,x21,x28])
    l6_dl  =  sum([x7,x15,x23,x30])
    l6_bds  =  sum([x14])

    # 定义资产分配分组
    c1  =  sum([x1,x2,x3,x4,x5,x6,x7])
    c2  =  sum([x8,x9,x10,x11,x12,x13,x14])
    c3  =  sum([x15,x16,x17,x18,x19,x20,x21])
    c4  =  sum([x22,x23,x24,x25,x26,x27,x28])
    c5  =  sum([x29])
    c6  =  sum([x30])

    # 参数定义
    pC11 = 0
    pJ3 = 0.5
    pK3 = 0
    pN3 = 0.5
    pM3 = 0
    pL3 = 0.5
    pAe3 = 0
    pAe4 = 0
    pAe5 = 0
    pAe6 = 0
    pAe7 = 0
    pAe8 =  0  
    pAe9 = 0
    pAe10 = 0
    pAe11 = 0

    # 添加约束
    solver.Add(c1 <= 2303087800) # c1
    solver.Add(c2 <= 0) # c2
    solver.Add(c3 <= 0) # c3
    solver.Add(c4 <= 0) # c4
    solver.Add(c5 <= 0) # c5
    solver.Add(c6 <= 0) # c6

    solver.Add(x31 <= 3585716787) # c7
    solver.Add(x31 <= 3585716787) # c8

    solver.Add(l1_dl - x31 *(pJ3-pC11) >= 1707141607) # c9
    solver.Add(l2_dl >= 0) # c10
    solver.Add(l3_dl >= 0) # c11
    solver.Add(l4_2_dl >= 0) # c12
    solver.Add(l4_3_dl >= 0) # c13
    solver.Add(l5_dl >= 0) # c14
    solver.Add(l6_dl >= 0) # c15

    solver.Add(l1_bds - x31* pK3 >= 0) # c16
    solver.Add(l2_bds >= 0) # c17
    solver.Add(l3_bds >= 0) # c18
    solver.Add(l4_2_bds >= 0) # c19
    solver.Add(l4_3_bds >= 0) # c20
    solver.Add(l5_bds >= 0) # c21
    solver.Add(l6_bds >= 0) # c22 

    solver.Add(l1_dl + pAe3 - x31*(1-pL3-pC11) >= 1707141607) #c23

    solver.Add(l2_dl + pAe4 >= 0) # c24
    solver.Add(l3_dl + pAe5 >= 0) # c25
    solver.Add(l4_2_dl + pAe8 >= 0) # c26
    solver.Add(l4_3_dl + pAe9 >= 0) # c27
    solver.Add(l5_dl + pAe10 >= 0) # c28
    solver.Add(l6_dl + pAe11 >= 0) # c29

    solver.Add(l1_hq - x31* pM3 <= 0) # c30

    solver.Add(l1 + pAe3 -(1-pN3-pC11)*x31 >= 1707141607) # c31
    solver.Add(l2 + pAe4 >= 0) # c32
    solver.Add(l3 + pAe5 >= 0) # c33
    solver.Add(l4_2 + pAe8 >= 0) # c34
    solver.Add(l4_3 + pAe9 >= 0) # c35
    solver.Add(l5 + pAe10 >= 0) # c36
    solver.Add(l6 + pAe11 >= 0) # c37

    for x in range(1, 32):
        solver.Add(globals()[f"x{x}"] >= 0)

    print('Number of constraints =', solver.NumConstraints())

    # 设置目标函数：最大化x31
    solver.Maximize(x31)

    # 求解模型
    status = solver.Solve()

    # 输出结果
    if status == pywraplp.Solver.OPTIMAL:
        print('Solution:')
        for v in solver.variables():
            print(v, "=", v.solution_value())
        print('Objective value =', solver.Objective().Value())
    else:
        print('The problem does not have an optimal solution.')

    # 输出求解信息
    print('\nAdvanced usage:')
    print('Problem solved in %f milliseconds' % solver.wall_time())
    print('Problem solved in %d iterations' % solver.iterations())

LinearProgrammingExample()

差异原因分析 #

1. 模型定义存在笔误（最可能原因） #

观察Python代码中l6_dl的定义：

l6_dl = sum([x7,x15,x23,x30])

而对应的l6分组是sum([x7,x14,x21,x28])，对比其他l*_dl的定义（比如l1_dl是l1的前4个变量），这里明显错误引用了不属于l6组的变量（x15、x23、x30）。如果Excel中的模型正确定义了l6_dl为x7,x14,x21,x28，两个模型的约束条件会完全不同，直接导致求解结果差异。

2. 浮点数计算精度与舍入差异 #

问题中的数值规模达到十亿级别，CLP和Excel Solver的浮点数计算逻辑、舍入策略不同：

• 即使模型完全一致，大规模数值下的微小精度误差可能被放大，导致最终整数结果差1。比如OR-Tools求解得到的x31可能是1191892386.9999999，显示时被截断为整数；而Excel Solver可能自动舍入为1191892387。
• 两个求解器采用的双精度浮点数迭代精度不同，CLP的默认计算精度可能更严格，导致结果更接近真实浮点值，而Excel可能有更宽松的精度处理。

3. 求解器终止容差设置不同 #

不同求解器的默认最优性容差和可行性容差存在差异：

• Excel Solver的默认容差可能更宽松，允许约束存在微小的违反，从而得到更大的x31值。
• OR-Tools的CLP默认容差更严格，确保解严格满足所有约束，因此得到的x31比Excel小1。

4. 变量类型约束差异 #

检查Excel模型是否将x31设置为整数变量：

• 如果Excel强制x31为整数，而Python代码中x31是连续数值变量（NumVar），OR-Tools求解的是连续线性规划，结果可能是浮点值，显示时被截断为整数；而Excel直接求解整数规划，得到整数结果。

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