Excel规划求解操作步骤初探

chen_pinghui

Excel应用中，经常遇到一些实际问题需要用到规划求解,很多Excel初学者不熟悉具体操作流程，写此贴做简要介绍与大家分享。

财务问题：公司到账3笔金额51860.68  192756.2  89343，来自于19个项目数据，现需求这三笔金额都是从哪些项目到账。

以上面例子作规划求解操作步骤初探，并期待EH论坛众高手予以指正。

1）假定19个数据在a1:a19，在c1输入：=SUMPRODUCT(A1:A19,B1:B19)，在d1输入数组公式（按ctrl+shift+enter三键结束）：=IF(ROW(A1)>SUM($B$1:$B$19),"",INDEX(A:A,SMALL(IF($B$1:$B$19=1,ROW($1:$19),4^8),ROW(A1))))，公式下拉到d19

2）Excel2003菜单——工具——规划求解——打开对话框——设置目标单元格为$C$1——点选"值"——输入需规划求解的数51860.68——可变单元格选择——$B$1:$B$19——添加约束 共三个:$B$1:$B$19>=0  $B$1:$B$19<=1  $B$1:$B$19=整数——确定——求解。求解过程耗时较长，仅录制操作步骤：


求解结果出来后，显示在D列，截屏：


3）如果结果对话框显示"规划求解找不到有用的解"，选点"取消"。重新开始求解最接近结果。c1公式修改为：=ABS(SUMPRODUCT(A1:A19,B1:B19)-51860.68)  求解步骤修改为：设置目标单元格为$C$1——点选"最小值"——输入需规划求解的数 51860.68——可变单元格选择——$B$1:$B$19——添加约束 共一个:  $B$1:$B$19=二进制——确定——求解

4）求解后的结果显示在d列，点选"保存规划求解结果"，确定。

PS：补充说明步骤中可能出现的疑问细节：

a、Excel2003工具栏中没有规划求解，则点击加载宏，再勾选规划求解；Excel2007点击左上角Excel圆形标志图标，Excel选项，加载项，在管理下拉列表中选择"Excel加载项"，点击"转到"，加载宏下勾选"规划求解"，确定，然后在菜单——"数据"选项卡就找到"规划求解"了；Excel2010在文件——选项——自定义功能区——勾选"开发工具"——在"开发工具"选项卡里点击"加载项"——勾选"规划求解加载项"——确定。

b、关于约束，不能手动输入"整数"这种汉字，在选择>= <=的时候，选择"int”即为”=整数”，选择”bin”即为”=二进制”。

附件供大家练手 ^_^
规划求解示例.rar (4.19 KB, 下载次数: 1804)

2012-3-11 15:39 上传

点击文件名下载附件

该贴已经同步到 chen_pinghui的微博

youliqi

顶一下。{:soso_e100:}

heike2004

学习了
一直搞不清规划求解的操作步骤流程，经楼主讲解有了一点初步认识

只是搞不清为什么要=SUMPRODUCT(A1:A19,B1:B19)又=IF(ROW(A1)>SUM($B$1:$B$19),"",INDEX(A:A,SMALL(IF($B$1:$B$19=1,ROW($1:$19),4^8),ROW(A1))))
难道说规划求解还得写公式啊
规划求解能不能像数据透视表那样，拖拉几下搞定？
望楼主指点，谢谢

chen_pinghui

heike2004 发表于 2012-3-2 22:37
学习了
一直搞不清规划求解的操作步骤流程，经楼主讲解有了一点初步认识

指点，真不敢当，不是谦虚。
=SUMPRODUCT(A1:A19,B1:B19) 求解过程必须要的公式，因为求解设置的目标单元格就是c1，规划求解的原理，线性与非线性什么的，我都答不上来。{:soso_e112:}

=IF(ROW(A1)>SUM($B$1:$B$19),"",INDEX(A:A,SMALL(IF($B$1:$B$19=1,ROW($1:$19),4^8),ROW(A1))))
这个公式可以不要，仅仅是为了更加直观看到求解后的结果，它引用的只是b列为1的对应数据，如果数据量较大，免得用眼去一个一个核对而已。再深层的道理，我也说不出来，旨在大家一起探讨。呵呵~

有你们两位回帖关注，我已经很开心了。如有不正确的地方，一定要各位高手指正。{:soso_e181:}

ft951357

以前遇到这种问题往往束手无策，今天终于找到一个方法，抽空试试看，楼主辛苦了

hanweicao

l楼主受累了
知道一点点规划求解的知识了！

豆绿

用了楼主的方法，还是不行，不能使用加载宏。现在手上就有个急茬，不知楼主可否帮忙解出。C列中就是几个数加总后得出的数值。

豆绿

急急急啊，希望赶快解决才行

小不点鲨鱼

要学习一下

dl0506

有一点，想问一下，
“如果结果对话框显示"规划求解找不到有用的解"，选点"取消"。重新开始求解最接近结果。c1公式修改为：=ABS(SUMPRODUCT(A1:A19,B1:B19)-51860.68)  求解步骤修改为：设置目标单元格为$C$1——点选"最小值"——输入需规划求解的数 51860.68”，这部分当选择最小值事，那个输入数值部分已经变成灰色，请问是在那里输入规划求解的数 51860.68？