再談利用Excel自配飼料配方

李雨來

（河北省玉田縣畜牧水產局，河北 唐山 064100）

閱讀了貴刊在2011第10期刊登了王學敏、任守文二位老師的《利用Excel表格自配豬飼料配方》一文后，收益很大，但Excel的功能很強大，我認為有地方可以略加改動一下，便可以省去好多公式的輸入，還有可以利用Excel的規劃求解功能，使我們的飼料成本最小化，以便降低飼料成本，以追求更大的效益，下面談一下自已的看法，請大家指教。

1 關于文中公式的看法

在文中第四點操作一節中，在C11單元格中輸入“=(B2*C2+B3*C3+B4*C4+B5*C5+B6*C6+B7*C7+B8*C8)/100”中，我們可以利用sumpoduct函數“計算對應數組或區域乘積的和”功能來實現上述功能，以省去輸入公式的麻煩，上式可改成sumproduct(B2∶ B8,C2∶ C8)/100，此外，“100”可用C11代替，這樣我們可以直接計劃下料單的營養成份，而不必一定為100，比如我們可為500 kg/批，就可以直接計算了。對于以后D11---J11單元格中的公式也可以用Excel中公式復制功能進行復制，而不必一個個輸入，但上述公式要稍作改動，即sumproduct($B$2:$B$8,C$2:C$8)/100,在B前后加上$表示對單元格的絕對引用，在C后加一個$表示相對引用，這樣在復制公式的時候前面的$B$2∶$B$8就保持不變，而后面就會對應變成C$2∶C$8、D$2∶D$8……J2∶J8，上述操作非常省事，只要將鼠標放到要復制單元格的右下角，當其變成黑色實心十字時向右拖動即可。

2 利用Excel的規劃求解功能，使我們的飼料成本最小化

我們以文中的原料（將骨粉替換成石粉，因為現在很少有用骨粉）為例，我們可按以下方案設計表格：

1）在營養成份一行中我們可以輸入公式＝sumproduct(B$2∶B$10,$m$2∶$m$10)/100,后面幾個單元格直接復制就可以了，在單價中輸入公式sumproduct(M2∶M10,J2∶J10)，其次我們可以根據營養標準確定一下我們的配方允許的營養含量的最大值與最小值分別填入最大值與最小值2行中（如圖1所示）。

2）根據自已以往的經驗在上限、下限2列中填定每種原料在所制作配方中允許的最大值與最小值，填寫完成后如圖2所示。

3）填寫完成后，我們就可以利用excel進行規劃求解了，首先（以excel2003為例），我們點上面工具欄下拉菜單中的規劃求解項(如圖3所示)，出現對話框，我們就可以填選定條件，對于上面的例子，我們選目標單元格為單價所在單元格（J11），選項選最小值，可變單元格選M2——M11，約束條件點添加如下條件：營養成份中B11—I11＜ ＝B12—I12，完成后點確定；再依次添加B11—I11＞＝ B13—I13；配比列中M2—M11＜＝K2—K11；M2—M11＞＝L2—L11，如圖4所示。

4）點求解，便計算出價格最低且滿足我們要求的配方如圖5所示。如果我們應用的EXCEL中沒有規劃求解項，我們可以點工具加載宏中選上規劃求解點確定就可以了。