再談利用Excel自配飼料配方

李雨來

（河北省玉田縣畜牧水產(chǎn)局，河北 唐山 064100）

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

1 關(guān)于文中公式的看法

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

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

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

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

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

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

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