已知单价和总金额，倒推数量

toopoor · 发表于 2011-7-6 16:28

已知B列的单价，以及C18单元格的总计150000，现在要倒推出A列的数量，而且要求各个数量之间的偏差不能超过30个。
先作为问题来提出，过几天贴出本人的解决办法。
数量单价合计
6.7 0
1.8 0
2.3 0
5.5 0
1.3 0
10.9 0
1.8 0
0.7 0
58.5 0
28.9 0
5.3 0
8.1 0
3.3 0
7.3 0
20.5 0
17.2 0
总计： 150000

iaiexcel · 发表于 2011-7-6 16:53

数量单价合计
820 6.7 5494
821 1.8 1477.8
832 2.3 1913.6
840 5.5 4620
818 1.3 1063.4
836 10.9 9112.4
835 1.8 1503
827 0.7 578.9
833 58.5 48730.5
843 28.9 24362.7
817 5.3 4330.1
842 8.1 6820.2
842 3.3 2778.6
818 7.3 5971.4
826 20.5 16933
832 17.2 14310.4
总计： 150000

iaiexcel · 发表于 2011-7-6 16:56

我用的是单变量求解
将某个单元格设定为基准单元格，A列诸格=这个基准单元格+rand()*30再取整
用单变量求解，得出的结果与15000有一点小差距
然后稍微一凑就OK了

toopoor · 发表于 2011-7-6 16:49

这道题要求使用excel的基本功能，不能使用vba。

seeme · 发表于 2011-7-6 16:44

试试迭加计算法

ctp_119 · 发表于 2011-7-6 16:59

原帖由 iaiexcel 于 2011-7-6 16:53 发表
数量       单价       合计
820       6.7       5494
821       1.8       1477.8
832       2.3       1913.6
840       5.5       4620
818       1.3       1063.4
836       10.9       9112.4
835       1.8       1503
827       0.7       578.9
833       58.5       48730.5
843       28.9       24362.7
817       5.3       4330.1
842       8.1       68 ...

这么快，是怎么算出来的呀，是不是单价求加权平均之后求数量均值，之后再推算呀？

iaiexcel · 发表于 2011-7-6 17:07

由于单变量求解的结果有偏差，我就做了一点弊，将单变量求解的过程录制了一个宏，反复运行了几次，最后一次的运气比较好，得出的结果是150011，而B5单元格正好是5.5……

Connie10086 · 发表于 2011-7-6 17:23

我要你的求解表格我要学习

iaiexcel · 发表于 2011-7-6 17:33

附件中包含一个宏，是用宏录制功能录制单变量求解的过程

toopoor · 发表于 2011-7-7 08:58

iaiexcel的解答不错，单变量求解用的很巧妙。
下面我贴出自己的解答方式。
我用的方法是excel的“规划求解”功能。
该功能默认情况下是隐藏的，excel2003版本，需要点击工具→加载宏，然后在弹出的窗口中勾选“规划求解加载宏”，excel2003的工具菜单中会出现“规划求解”菜单项。
excel2007需要点击左上角的office工具按钮，在弹出的菜单中点击下面的“excel选项”，在弹出的窗口中点击左侧的“加载项”，在右侧的的“管理”下拉列表中选择“加载项”，然后点击转到，勾选“规划求解加载项”。然后点击“数据”菜单，会在“分析”标签里面出现规划求解。

好了，现在我们调出来规划求解功能了。开始使用这个功能解决我们的问题吧。

假设你已经准备好了数据，我们开始设置公式。

第一步，在A19单元格输入公式=STDEV(A2:A17)，这个公式的意思是求A2到A17单元格的数据的标准偏差。在D19单元格输入公式=SUM(C2:C17)-C18，C2到C17单元格输入公式=A2*B2、=A3*B3……=A17*B17。
第二步，点击规划求解，在弹出的窗口中，设置如下：设置目标单元格为$D$19，等于选择“值为”0，可变单元格选择$A$2:$A$17，约束添加
① $A$19<=5，由于A19单元格的公式是=STDEV(A2:A17)，所以这个条件的意思是要求A2到A17单元格的标准偏差不大于5。
② $A$2:$A$17=整数（这个设置要注意，添加约束的时候，设置窗口的单元格引用位置引用$A$2:$A$17，右侧的约束值保留空白，中间的符号选择“int"，就是整数了。在右侧输入“整数”会出现错误的。）。
③ $A$2:$A$17>=0。这个条件要求A2到A17的答案不能是负数。

好了，设置好以后，点击求解。有时候他会弹出提示窗口，说到了最大的迭代次数，你可以点击继续，一般都会得出你满意的答案的。
下面附上附件。

规划求解.rar (4.55 KB, 下载次数: 452)

[ 本帖最后由 toopoor 于 2011-7-7 09:03 编辑 ]

		自动登录	找回密码
密码			免费注册

[原创] 已知单价和总金额，倒推数量

回复 7楼 iaiexcel 的帖子