李雨來(lái)

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

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

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

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

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

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

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

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

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

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