利用Excel制作饲料配方优化系统

     线性规划是解决资源合理调配的应用数学分支，它通过满足线性等式或不等式的约束条件来求解线性目标函数的最大值或最小值。 Office的组件之一Excel自身提供的“加载宏”中有“规划求解”一项，可以解决各种线性规划任务。用线性规划对饲料配方任务求解时，不需用饲料配方专用软件，而是在Excel界面下，通过鼠标或键盘操作，即可得出最低成本的饲料配方最优解，且约束条件不受限制，适用于各种中小型饲料厂、规模养殖场进行优化饲料配方计算。本文将如何利用Excel制作饲料配方优化系统作一详细介绍。
    
     1　编制饲料配方优化线性模型工作表
     在饲料配方任务线性求解中，一般是目标函数(S，即饲料配方成本)取最小值，而待解变量x，即每种饲料在配方中的比例是非负的。线性规划模型如下：
     求目标函数(S)=c1x1+c2x2+…cjxj+…cnxn的最小值。
     约束条件
     a11x1+a12x2+...a1nxn☆b1
     a21x1+a22x2+...a2nxn☆b2
     .........
     am1x1+am2x2+...amnxn☆bm
     x1>0,x2>0...xn>0
     上式中cj是原料价格(元/kg)，xj是各种饲料原料，S是饲料配方成本，aij是j种饲料第i种营养成分的含量或对饲料原料进行约束的系数1，☆表示>，=，<。bj是饲养标准或受约束饲料的值。i=1，2...m，j=1，2... n。
     选定一个工作表，并命名为“线性模型”，将以上模型排列在Excel工作表中，如表1。
表1：饲料配方优化线性模型工作表

     表1中B3:N24是aij值，Q3:Q24是bj值，B25:N25是cj值，B26:N26是给定原料的初始值，O3:O24是对应于初始值时线性方程组左侧的值，O25是给定原料初始值时的目标函数值(S)。在O3中输入公式：=$B$26*B3+$C$26*C3+ $D$26*D3+…$M$26*M3+$N$26*N3。选中O3，并向下复制公式至O25。

    2　制作饲料配方结果工作表
    选定一个工作表，并命名为“饲料配方”，制表如表2。
表2： 饲料配方结果工作表 
  
    在A4中输入公式：=规划模型B2， A5：=规划模型C2，同理在A6:A16输入公式。在B4中输入公式：=规划模型B26*100，B5：=规划模型C26*100，同理在B6:B16中输入公式。在C4中输入公式：=规划模型B25，C5：=规划模型C25，同理在C6:C16中输入公式。在D4中输入公式：=B4*C4，并向下复制公式至D16。在F4中输入公式：=规划模型O3，并向下复制公式至F15。在B17中输入函数：=SUM（B4:B16）， 在D17中输入函数：=SUM（D4:D16），在F18中输入函数：=NOW()。

12

上一页：应用Excel制作饲料生产管理系统
下一页：工厂化养猪的仔猪寄养