Excel 求解器不遵守二元约束
我在 Excel 中创建了一个模型以与 Solver 结合使用。它大部分有效。唯一的问题是 Solver 并没有真正正确地将约束为二元的单元格处理。当找到解决方案后，一些数字实际上是 0.9999996、1.0000000003、0.0000017 等等。
我在 Mac OS X 上使用 Excel 2004。有什么想法吗？
I created a model in Excel to use in conjunction with Solver. It mostly works. The only problem is that Solver is not really treating the cells constrained as binary properly. When it's done finding a solution, some of the numbers are actually 0.9999996, 1.0000000003, 0.0000017, and so on.
I am using Excel 2004 on Mac OS X. Any ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
如果二进制值远离 0 或 1，您可能需要转到“求解器”窗口中的选项并检查“忽略整数约束”框是否已选中。
If the binary values are far from 0 or 1, you might have to go to options in the Solver window and check if the box "Ignore integer constraints is checked".
由于计算机的有限精度和优化算法的性质，求解器内置了公差。我会尝试选择“求解器参数”对话框上的“选项”按钮，然后增加精度（例如从默认值 0.000001 到 0.00000001） 。我不确定这是否有效，但值得一试。
我的公司制作了一个与 Excel 连接的商业级求解器，称为 What'sBest。如果以上方法不行的话可以考虑一下。您可以在 www.lindo.com 上找到更多信息
Due to the finite precision of computers and the nature of the optimization algorithms, the solver has tolerances built in. I would try selecting the Options button on the Solver Parameters dialogue box and then increase the Precision (say from the default of 0.000001 to 0.00000001). I am not sure if this will work, but it is worth a try.
My company makes a commercial grade solver hooked to Excel called What’sBest. If the above does not work you could consider it. You can find more info at www.lindo.com
如果求解器无法在允许的时间范围或迭代次数内找到解决方案（或者如果没有可能的正确答案），它将不会返回二进制答案。
一种可能的解决方法是设置一些单元格（可能是那些带有您正在计算的单元格中的最小值），使用约束
<=1和>=0而不是二进制。您提供的图片没有显示，所以我不太确定您想要什么。（对于未设置为二进制的单元格，设置一个具有和积的单元格可能会有所帮助；请参见下文。）
例如，如果您尝试求解总计 22（无解），您可以从：
如果将第二列中的所有值设置为二进制，则最终会得到：
如果将前 3 个设置为二进制，最后两个设置为
<=1&>=0，您最终得到：最后两行的和积 = 4，然后您可以使用它手动将最后两行之一设置为 1，另一个设置为 0，具体取决于是否您想要的答案略低于或高于目标。
If solver is unable to find a solution within the time frame or number of iterations allowed (or if there is no correct answer possible) it will not return binary answers.
One possible work-around, depending on the type of problem you are trying to solve and how exact an answer you need, is to set a few cells (probably the ones with the smallest values in the cells you are calculating for) with the contstraints
<=1and>=0rather than binary. The image you provided is not showing up, so I'm not exactly sure what you were after.(It may be helpful to set up a cell with a sumproduct for the cells not set as binary; see below.)
As an example, if you are trying to solve for a total of 22 (which has no solution), you start with:
If you set all the values in the second column as binary, you will end up with:
If you set the first 3 as binary, and the last two as
<=1&>=0, you end up with:The sumproduct for the last two rows = 4, which you could then use to manually set one of the last two as 1 and the other 0, depending on whether you want an answer slightly below or above the target.
我遇到了类似的问题，可以通过转到求解器选项然后检查“使用自动缩放”来解决它。我不确定这是否能解决您的问题，但值得一试。
I had similar issue and could resolve it by going to solver options and then checking "Use Automatic scaling". I am not sure this will fix your issue or not but worth a try.
可能不是您的问题，但请注意，选项中有一个
Ignore Integer Constraints复选框，在 Excel Solver 中默认处于选中状态。至少我的 Excel 2011 for Mac 副本是这样。Likely not your problem, but be aware there is an
Ignore Integer Constraintscheckbox in Options that is checked by default in Excel Solver. At least that's the case in my copy of Excel 2011 for Mac.我遇到了同样的事情，取消选中忽略整数约束框为我解决了这个问题。
I ran into the same thing and unchecking the Ignore Integer Constraints box solved this problem for me.