Excel Solver线性条件不满足求助：替代IF函数实现使用/未使用标识

Got it, the core problem here is that Excel’s IF() function introduces non-linearity into your model, which violates the linearity requirement for linear programming solvers. Let’s fix this with a standard linear programming workaround using auxiliary binary variables—here’s a step-by-step breakdown:

Step 1: Add a Binary Auxiliary Variable #

Create a new cell (let’s name it Use) and set it as a binary decision variable in Solver (this means it can only take values 0 or 1). This variable will directly represent whether your original decision variable (Dec.Var) is active (1) or inactive (0).

Step 2: Add Linear Constraints to Link Use and Dec.Var #

You need two simple linear constraints to enforce the exact logic you want: "If Dec.Var ≠ 0, Use = 1; if Dec.Var = 0, Use = 0":

• Constraint 1: Dec.Var ≤ M * Use
  Here, M is a sufficiently large number that’s bigger than the maximum possible value Dec.Var could take in your model. For example, if Dec.Var can never exceed 1000, set M to 1000. This ensures that if Use is 0, Dec.Var must be 0 (since 0 ≤ M*0 = 0).
• Constraint 2: Dec.Var ≥ ε * Use
ε is a tiny positive number (like 0.0001). This constraint guarantees that if Dec.Var is greater than 0, Use has to be 1—because ε*1 is a small threshold, and any positive Dec.Var will satisfy this, while Use=0 would force Dec.Var ≤ 0 (combining with the first constraint, this locks Use to 1 when Dec.Var is non-zero).

Step 3: Replace the IF() Function with the Auxiliary Variable #

Instead of using =IF(Dec.Var=0,0,1), just reference the Use cell directly. For your cost calculation, it becomes =Use * Unit_Cost—this is a fully linear expression that Solver can handle without violating linearity rules.

Quick Notes: #

• Don’t make M unnecessarily large (it can cause numerical instability in the solver), but ensure it’s big enough that it doesn’t artificially limit Dec.Var’s valid values.
• Choose ε small enough that it doesn’t impact your model’s practical outcomes, but not so small that Excel runs into precision issues.

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