掌握excel表格汇总操作技巧助力高效数据管理
2189
2023-02-08
本文目录一览:
以下图为例,A1:A30些随机数,要求取其中某几个单元格的值,求和为一个固定数,假设是200。
操作方法如下
步骤1:在C1输入公式:
=SUMPRODUCT(A1:A30,B1:B30)
如下图
步骤2:数据规则求解,设置目标框中自动为$C$1,如果不是,请更改,“目标值”框中输入200,“通过更改可变单元格”框中选择B1:B30,再单击“添加”按钮,如下图
步骤3:单元格引用框中选择B1:B30,约束选择"bin"(表示二进制数字0或1),再单击确定按钮。
步骤4:通过步骤3的设置后,“遵守约束”框就增加了B1:B30为二进制的约束,再单击“求解”按钮,如下图
步骤5:单击“规则求解结果”的“确定”按钮,如下图:
现在可以看到,固定求和为200的数字有46+48+49+13这几个单元格加起来刚好为200,如下图中的黄色单元格(B列中标识为1)。
知识扩展:
如果数据菜单中没有“规划求解”菜单,则单击“开发工具”加载项,勾选“规划求解加载项”,再单击“确定”按钮,如下图
设置步骤如下:
1、单击“文件——选项——加载项——(Excel加载项)转到”,出现“加载宏”对话框,如下图所示。选择“规划求解加载项”,单击“确定”。
2、此时,在“数据”选项卡中出现带有“规划求解”按钮的“分析”组,如下图所示。
3、使用Excel求解线性规划问题时,电子表格是输入和输出的载体,因此设计良好的电子表格,更加易于阅读。
4、然后将其复制到下方相应的单元格中。单击“数据——分析——规划求解”,出现如下图所示的“规划求解参数”对话框,设计相应的参数。
6、并且单击“添加”按钮,添加相应的约束,如下图所示。
7、设置好参数后,单击“规划求解参数”对话框中的“求解”按钮,结果如下图所示。
精确凑数据
领导给小王同志12个金额,让他凑数据,凑成26005元和33459元。
左拼右凑这个金额,凑了一个通宵,还没凑对,十分着急,同事3分钟就给解决了
?在Excel中调出线性规划
我们在Excel选项里面,找到加载项
然后勾选规划求解加载项,点击确定
?我们在C1单元格输入公式:
=SUMPRODUCT(A2:A13*B2:B13)
?前面加载了加载项之后,在数据选项卡下,就有了规划求解进行相关设置后,运行得到最终的结果,操作动图如下所示:
其中的设置是,设置目标是C1单元格,目标值是26005,可变的单元格区域是B2:B13,遵守约束是B2:B13是二进制
最终B2:B13单元格中的数据为1的这些值累加起来,正好就能得到我们需要的26005了
剩下的数据正好就是33459元了。
模糊凑数据
如果给定的一个金额是系统也不可能准确的凑出来,Excel一直在计算的过程中的时候,可以随时按ESC退出
或者我们改变公式,使得进行模糊的凑数据接近这个值,我们现在要把这些数据最接近30000
我们可以在C1输入公式:
=ABS(SUMPRODUCT(A2:A13,B2:B13)-30000)
然后在线性规划中的设置是:
C1是最小值
然后运行,这个时候,会一直在那里转,这个时候,我们需要按ESC,然后
保留求解
得到了一组结果。。这个例子还是找到了正好等于30000的数据。。如果不等于的话,那么会得出一个最接近的结果。
当然,平时不用这个功能的时候,需要把这个功能给关闭了,否则每次打开Excel的速度会变慢一点
今天的技巧,你学会了么?自己动手试试吧~
举例说明通过Excel解决线性规则问题。
1、Excel中通过规划求解的方法解决线性规划问题excel线性规划,而默认情况下excel线性规划,在“数据”选项下excel线性规划,没有“规划求解”项,需要在左上角的“文件”选项下,“Excel选项”中的“加载项”中,将“非活动应用程序项”下的“规划求解加载项”选中,通过下方的“转到”,添加到“分析工具库”里。
2、完成以上操作后,“数据”选项下的“分析”中,就出现了“规划求解”项。
3、在A1:A5单元格中,找出哪些数加起来总和为222的求解案例中,在B6单元格输入=SUMPRODUCT(A1:A5,B1:B5)
4、点击“规划求解”,设置目标单元格为B6,目标值为222,可变单元格为B1:B5,并添加可变单元格约束条件为B1:B5为二进制(即非0即1),选择“单纯线性规则”,按“求解”;
5、Excel计算并返回“规划求解结果”,按“确定”,保留解。
6、其他更多线性规划中求最优方案(最大值、最小值)也是同样的方式,首先建立目标单元格与可变单元格的规则(目标函数),并确定目标函数所在单元格及要求(最大值、最小值或目标值),明确决策变量(可变单元格),并为可变单元格添加约束条件,然后选择“线性规划求解”的求解方法,按“求解”,由Excel自动完成求解过程。
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。