ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 55751|回复: 48

[分享] Excel规划求解操作步骤初探

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2012-3-2 13:15 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:假设分析
本帖最后由 chen_pinghui 于 2012-3-11 15:42 编辑

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

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

求解结果出来后,显示在D列,截屏:
规划求解结果.jpg

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, 下载次数: 1796)



该贴已经同步到 chen_pinghui的微博

评分

4

查看全部评分

TA的精华主题

TA的得分主题

发表于 2012-3-2 22:21 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
顶一下。{:soso_e100:}

TA的精华主题

TA的得分主题

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

只是搞不清为什么要=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))))
难道说规划求解还得写公式啊
规划求解能不能像数据透视表那样,拖拉几下搞定?
望楼主指点,谢谢

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-3-3 01:20 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
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:}

TA的精华主题

TA的得分主题

发表于 2012-3-3 14:50 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
以前遇到这种问题往往束手无策,今天终于找到一个方法,抽空试试看,楼主辛苦了

TA的精华主题

TA的得分主题

发表于 2012-3-5 22:13 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2012-4-20 21:38 | 显示全部楼层
用了楼主的方法,还是不行,不能使用加载宏。现在手上就有个急茬,不知楼主可否帮忙解出。C列中就是几个数加总后得出的数值。

新建 Microsoft Excel 工作表.rar

2.11 KB, 下载次数: 69

TA的精华主题

TA的得分主题

发表于 2012-4-20 21:40 | 显示全部楼层
急急急啊,希望赶快解决才行

TA的精华主题

TA的得分主题

发表于 2012-4-21 19:32 | 显示全部楼层
本帖最后由 小不点鲨鱼 于 2012-4-21 19:32 编辑

要学习一下

TA的精华主题

TA的得分主题

发表于 2012-5-21 09:41 | 显示全部楼层
有一点,想问一下,
“如果结果对话框显示"规划求解找不到有用的解",选点"取消"。重新开始求解最接近结果。c1公式修改为:=ABS(SUMPRODUCT(A1:A19,B1:B19)-51860.68)  求解步骤修改为:设置目标单元格为$C$1——点选"最小值"——输入需规划求解的数 51860.68”,这部分当选择最小值事,那个输入数值部分已经变成灰色,请问是在那里输入规划求解的数 51860.68?
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 00:46 , Processed in 0.051882 second(s), 11 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表