Excel VBA：求解器似乎没有“随着时间的推移”进行优化使用日期变量？
我有一个优化问题，我尝试用求解器来解决。其中，一个决策变量是包含日期的单元格。基于时间序列，求解器应选择最佳时间点。然而，虽然它改变了其他决策单元，但它永远不会改变这个决策单元！我什至尝试删除其他决策变量（保持它们不变） - 仍然没有变化。
为了帮助您锁定我的问题，我创建并附加了一个 Excel 工作表（此处：下载链接）有一个简化的问题，求解器应该从股票价格的时间序列中选择价格最高的日期。 （我知道这个特定问题可以很容易地用 MAX 公式解决，但我真正的优化问题要复杂得多，不允许使用这个解决方案。）
求解器仍然不会改变初始日期值的任何内容 - 即使在这个非常简单的设置中！
从数学角度来看，当前问题可以描述为（非线性）阶跃函数，其中特定时间点（x 轴上）对应于不同的价格（y 轴上的阶跃）。
是否有任何技巧可以让求解器处理时间（日期）值？ 如果您有幸找到一组正确的求解器设置以使其获得正确的解决方案（即 2009 年 1 月 10 日），我将非常高兴听到它们。
编辑：我发现非平滑不连续函数的非线性问题（就像这个问题，基本上是一个楼梯函数）通常很难通过任何（非线性）线性优化器来解决。精通此事的人可以帮助我以可解决的方式重新表述问题吗？我在堆栈溢出上发现了类似的主题建议的解决方案（参见第一个答案），但不知道如何将其应用于我的问题。
谢谢， 史蒂夫
I have an optimization problem that I try to tackle with solver. Among others, one decision variable is a cell containing a date. Based on a time series, Solver should pick the optimum point in time. However, while it changes the other decision cells, it never changes this one! I even tried dropping the other decision variables (holding them constant) - still no change.
To help you have a lock into my problem, I created and attach an Excel sheet (here: download link) with a simplified problem, where from a time series of stock prices Solver should pick the date with the highest price. (I am aware that this particular problem could easily be solved with the MAX formula, but my real optimization problem is much more complex and wouldn't allow for this solution.)
Solver still doesn't change anything at the initial date value - even in this very simplified setting!
From a mathematical point of view, the present problem could be characterized as a (non-linear) step function, where particular points in time (on the x axis) correspond to different prices (steps, on the y axis).
Is there any tricks involved to get solver to handle time (date) values?
If you have luck to find the right set of Solver settings to make it get to the correct solution (which would be Jan 10, 2009), I'll be more than happy to hear about them.
Edit: I found out that non-linear problems with non-smooth discontinuous functions (like this problem, which is basically a staircase function) are generally difficult to tackle by any (non-)linear optimizer. Can someone savvy in the matter help me reformulate the problem in a solvable way. I found a similar topic on stack overflow with a suggested solution (see first answer), but don't know how to apply it to my problem.
Thanks,
Steve
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我通过添加约束 $C$7 >= $C$13 并使用进化方法，在 Excel 2010 中解决了您的电子表格问题。您可能可以找出您提到的数学方法，但您可能需要在 VBA 而不是 Solver 对话框中执行此操作。
I got your spreadsheet to solve in Excel 2010, by adding the constraint $C$7 >= $C$13, and using the Evolutionary Method. You can probably figure out the Math method you mentioned, but you may need to do it in VBA instead of the Solver dialog.