利用Excel設(shè)計(jì)飼料配方

1、畢業(yè)論文論文題目 利用 Excel 設(shè)計(jì)飼料配方學(xué) 院 動(dòng)物科技學(xué)院專 業(yè) 生物科學(xué)（動(dòng)物方向 ） 年 級(jí)四 年 級(jí)姓名薛琳指導(dǎo)教師 呂 麗 華職稱副教 授（ 2008 年 5 月）山西農(nóng)業(yè)大學(xué)教務(wù)處制摘要在當(dāng)今飼料行業(yè)競(jìng)爭(zhēng)日益激烈的情況下， 多數(shù)的飼料企業(yè)都已經(jīng)使用電腦做配方。 許多大型的 飼料企業(yè)以及大規(guī)模的養(yǎng)殖場(chǎng)都早已經(jīng)應(yīng)用上專業(yè)配方軟件。但因?yàn)閷I(yè)飼料配方軟件價(jià)格高昂， 對(duì)于中小型飼料廠及一些養(yǎng)殖場(chǎng)難以接受。 但是 ,Microsoft Excel 卻可以利用其 “規(guī)劃求解 ”功能簡(jiǎn)單 有效地設(shè)計(jì)出飼料配方。本文即是論述如何運(yùn)用 Excel 來(lái)設(shè)計(jì)配方。具體則是講述了如何為 Excel

2、 加載 “規(guī)劃求解 ”工具，如何輸入設(shè)計(jì)飼料配方的原始數(shù)據(jù)， 怎樣加入優(yōu)化配方的約束條件，以及求 解最優(yōu)解 滿足要求的最低成本飼料配方 的方法等等一些問題。關(guān)鍵詞： 飼料；配方； Excel；規(guī)劃求解；畜牧業(yè)Design the Feedstuff Formulation by ExcelABSTRACT ： With the increasingly competition, many feedstuff enterprises have already used computers to design formulation. Many large feedstuff enterprise

3、s and large-scaled farms have already used professional formula software. The professional software is too expensive to afford it for the small scaled feed factory and some small farms. However, the Microsoft Excel makes use of Programming Solve function to design a feed formula easily and efficient

4、ly. This article studied how to use Excel to design feed formula. It showed how to add Programming Solve tool for the Excel, how to input the original data of feed formula, how to add the restricted conditions for the optimization of formula , andhow to get the optimal solution the lowest cost of th

5、e animal feed formula that satisfied the needs. Keywords: feed； formulation; Excel; programming solve; husbandry目錄1前言 . 11.1飼料配方設(shè)計(jì)歷史 . 11.2用電腦設(shè)計(jì)配方 . 11.3用 Excel 設(shè)計(jì)飼料配方 . 11.4Excel 規(guī)“劃求解 ”工具簡(jiǎn)介 . 12利用 Microsoft Excel 設(shè)計(jì)飼料配方 . 22.1 Excel 規(guī)“劃求解 ”的安裝 . 22.2運(yùn)用 Excel “規(guī)劃求解 ”工具設(shè)計(jì)飼料配方 . 32.2.1建立飼料配方數(shù)據(jù)庫(kù) . 32.

6、2.2建立飼料配方規(guī)劃表 . 32.2.3建立合計(jì)行 . 52.2.4 建立配方營(yíng)養(yǎng)水平行 . 62.2.5輸入規(guī)劃求解參數(shù) . 62.2.6規(guī)劃求解選項(xiàng) . 72.2.7利用 “規(guī)劃求解 ”工具優(yōu)化飼料配方 . 82.2.8實(shí)驗(yàn)結(jié)論 . 93討論 . 93.1 對(duì)使用 Excel “規(guī)劃求解 ”工具設(shè)計(jì)與優(yōu)化飼料配方的分析與總結(jié) . 93.1.1 Excel 規(guī)劃“求解 ”工具設(shè)計(jì)與優(yōu)化飼料配方的優(yōu)點(diǎn) . 93.1.2 Excel 設(shè)計(jì)最佳飼料配方的關(guān)鍵 . 103.1.3對(duì)配方成本進(jìn)行約束 . 103.1.4約束條件 . 103.2 Excel 設(shè)計(jì)配方的調(diào)整及優(yōu)化 . 103.2.1約束

7、條件的確定 . 103.2.2從不同角度調(diào)整配方以求優(yōu)化配方 . 103.2.3調(diào)整高能量原料的用量 . 103.2.4 調(diào)整兩種營(yíng)養(yǎng)特性相似原料的配比 .113.3利用 Excel 設(shè)計(jì)濃縮飼料配方 .114結(jié)束語(yǔ) .114.1Excel 設(shè)計(jì)飼料配方總結(jié) .114.2對(duì)飼料配方軟件行業(yè)的展望 .11參考文獻(xiàn) . 13致謝 . 1420世紀(jì) 20 年1964年，隨著薛琳：利用 Excel 設(shè)計(jì)飼料配方利用 Excel 設(shè)計(jì)飼料配方1前言在當(dāng)今飼料行業(yè)， 飼料企業(yè)間競(jìng)爭(zhēng)空前激烈， 而這種競(jìng)爭(zhēng)中又主要是飼料配方的競(jìng)爭(zhēng)。 只有依靠?jī)?yōu)質(zhì)的配方，以節(jié)約成本，提高性價(jià)比，才能使飼料企業(yè)生存發(fā)展壯大。所以

8、飼 料配方設(shè)計(jì)在飼料公司的生產(chǎn)經(jīng)營(yíng)中占了舉足輕重的地位。為了節(jié)約飼料生產(chǎn)成本、提高 配方設(shè)計(jì)的效率與準(zhǔn)確性，很多飼料廠都早已放棄手工配方設(shè)計(jì)，而采用電腦配方。在大 型飼料企業(yè)，主要是使用專業(yè)的配方設(shè)計(jì)軟件。而其他的中小型飼料企業(yè)及一些規(guī)模養(yǎng)殖 場(chǎng)因?yàn)橘Y金問題，則宜于采用 Microsoft Excel 的“規(guī)劃求解 ”功能設(shè)計(jì)其配方。1.1飼料配方設(shè)計(jì)歷史1875， John Barwell 在美國(guó)依利諾州建立世界上第一家飼料加工廠。到 代，飼料配方設(shè)計(jì)方法有：對(duì)角線法、聯(lián)立方程法、試差法等等手工方法。 電腦的流行，開始有了電腦設(shè)計(jì)飼料配方 1 。1.2用電腦設(shè)計(jì)配方電腦配方具有巨大優(yōu)勢(shì)， 它

9、能全面考慮營(yíng)養(yǎng)、 成本和效益， 還可提供大量的參考信息， 最重要的是，它節(jié)約了大量的人力物力，大為解放了配方師。如今著名的配方設(shè)計(jì)軟件有國(guó)外的 Format、Brill 、Mixit 和國(guó)產(chǎn)的 Refs、CMIX 等。但 這些專業(yè)配方軟件都價(jià)格相對(duì)較高，對(duì)于中國(guó)眾多的中小型飼料廠及一些規(guī)模養(yǎng)殖場(chǎng)不太 適用2。1.3用 Excel 設(shè)計(jì)飼料配方Microsoft Excel 2000 及其后續(xù)版本的 “規(guī)劃求解 ”功能可以很好地解決中小型飼料廠及 一些規(guī)模養(yǎng)殖場(chǎng)因?yàn)閮r(jià)格高昂不愿購(gòu)買專業(yè)配方設(shè)計(jì)軟件問題。線性規(guī)劃是應(yīng)用數(shù)學(xué)中解決資源合理調(diào)配問題的一個(gè)分支，它是通過(guò)滿足線性等式或 不等式的約束條件來(lái)

10、求解線性目標(biāo)函數(shù)的最大值或最小值 3。Excel“工具”菜單|“加載宏 ”選項(xiàng)中有“規(guī)劃求解 ”一項(xiàng)，可以解決各種線性規(guī)劃任務(wù)。用 Excel 線性規(guī)劃對(duì)飼料配方任務(wù)求解時(shí)，不必要使用飼料配方專業(yè)軟件，僅需要在 Excel 界面下，通過(guò)鼠標(biāo)或鍵盤的操作，即可得出飼料配方最低成本的最優(yōu)解，而且約束條件不 受限制，非常適用于各種中小型飼料廠、規(guī)模 養(yǎng)殖 場(chǎng)進(jìn)行優(yōu)化飼料配方計(jì)算。1.4Excel 規(guī)“劃求解 ”工具簡(jiǎn)介利用 MS Excel “規(guī)劃求解 ”工具，可對(duì) Excel 工作表上與目標(biāo)單元格中的公式有直接或 間接聯(lián)系的一組單元格的數(shù)值進(jìn)行調(diào)整，最終為目標(biāo)單元格中的公式找到優(yōu)化的結(jié)果 4山西農(nóng)

11、業(yè)大學(xué)本科畢業(yè)論文（設(shè)計(jì)）運(yùn)用 MS Excel“規(guī)劃求解 ”工具時(shí)的一些術(shù)語(yǔ)： 可變單元格：需要重新確定數(shù)值的自變量所在單元格。簡(jiǎn)言之，就是說(shuō)規(guī)劃求解中可 修改其數(shù)值的單元格。在 “規(guī)劃求解 ”操作后，最優(yōu)值就會(huì)代替了可變單元格中的初始值 5。目標(biāo)單元格：即公式結(jié)果（因變量）所在單元格。配方時(shí)，規(guī)劃求解即是要求解其取 預(yù)期的最優(yōu)值時(shí)可變單元格的取值 5。約束條件：在規(guī)劃求解配方時(shí)根據(jù)想要得到的目標(biāo)配方所提出的一些條件。 絕對(duì)引用：隨著公式的位置變化，所引用單元格位置不變化的一種引用 5。2利用 Microsoft Excel 設(shè)計(jì)飼料配方“規(guī)劃求解 ”是 Office2000及其后續(xù)版本提供

12、的一個(gè)加載宏。宏是 MS Office 為了用戶 一些任務(wù)自動(dòng)化而設(shè)計(jì)的一個(gè)功能。2.1 Excel“規(guī)劃求解 ”的安裝“規(guī)劃求解 ”一般安裝在 “工具”菜單中，顯示為 “規(guī)劃求解 ”選項(xiàng)。 如果“工具”中無(wú)“規(guī)劃求解 ”選項(xiàng)，通常是由于 “加載宏”中沒選該選項(xiàng)。在這種情形下， 可先鼠標(biāo)依次選擇 “工具”菜單| “加載宏”選項(xiàng)，出現(xiàn)圖 1所顯示的對(duì)話框。在可用“加載宏”選項(xiàng)中選擇 “規(guī)劃求解”，如圖 1 所示。然后單擊確定，在稍候片刻后 一般即可加載成功 “規(guī)劃求解 ”工具 6。如果在圖 2的可用加載宏中未有 “規(guī)劃求解 ”選項(xiàng)，說(shuō)明有可能：1）這臺(tái)電腦在安裝 Office 時(shí)沒選擇加載宏的

13、“規(guī)劃求解 ”。這時(shí)，可以重新運(yùn)行 Office 安裝文件，選擇 Excel 選項(xiàng)，在加載宏區(qū)段中選擇 “規(guī)劃求解 ”，然后重新安裝。安 裝完閉即應(yīng)該可出現(xiàn) “規(guī)劃求解 ”選項(xiàng) 7。2）所用 MS Excel 是盜版。建議使用正版。圖 1 “加載宏 ”對(duì)話框薛琳：利用 Excel 設(shè)計(jì)飼料配方2.2運(yùn)用 Excel “規(guī)劃求解 ”工具設(shè)計(jì)飼料配方目標(biāo)：設(shè)計(jì)一個(gè)海蘭褐商品種商品蛋雞產(chǎn)蛋高峰期配合飼料。要求維持產(chǎn)蛋率 90%以上，飼養(yǎng)標(biāo)準(zhǔn)應(yīng)達(dá)到代謝能 12.14Mj/kg ，粗蛋白 16.00%，鈣 3.85%，有效磷 0.48%，食鹽 0.37%8 。要求配制 100kg 的配合飼料?，F(xiàn)有 7

14、種飼料原料， 即玉米、豆粕、麥麩、磷酸氫鈣、石粉、食鹽和添加劑， 價(jià)格（元 /千克）分別為： 1.578、2.70、0.90、3.30、0.25、 1.00、55.00,它們的養(yǎng)分含量可從中國(guó) 飼料數(shù)據(jù)庫(kù)查到 9 。2.2.1建立飼料配方數(shù)據(jù)庫(kù) 把設(shè)計(jì)配方所用到的原始數(shù)據(jù)輸入 Excel 工作表中，輸完后即圖 2 所示。2.2.2建立飼料配方規(guī)劃表飼料配方規(guī)劃表，即 “飼料配方優(yōu)化計(jì)算 ”函數(shù)關(guān)系表。它是指建立目標(biāo)單元格、可變 單元格和約束條件間的數(shù)量對(duì)應(yīng)關(guān)系。建立后即為圖 3 所示。其中，配方成本 （目標(biāo)函數(shù)）單元格為 H23。它是各種原料成本的總和， 即 H16：H22 的加和。各種原料的

15、成本是各自含量與其價(jià)格的乘積。 各種原料的用量所在單元格 B16:B21 是可變單元格。而飼料添加劑在配合飼料中的用量一般是固定的，即它是非可變單元格。 C16： G21為約束每件。圖 2 飼料配方設(shè)計(jì)的原始數(shù)據(jù)山西農(nóng)業(yè)大學(xué)本科畢業(yè)論文(設(shè)計(jì))圖 3 飼料配方規(guī)劃表具體建立步驟為：1)先在 B16： B22 中輸入一個(gè)經(jīng)驗(yàn)配方。如圖 3 中的配方。它是配方規(guī)劃方案的初 值。2)C16 單元格，輸入 “ =B16*C6”，確認(rèn)。如此，在飼料配方與飼料原料玉米就建立了 玉米用量與所提供的能量間的數(shù)量對(duì)應(yīng)關(guān)系。3)自動(dòng)填充 C17：C22 的代謝能：?jiǎn)螕?C16，后把光標(biāo)移動(dòng)到 C16 的右下角，等

16、光 標(biāo)變成“+后”，按住鼠標(biāo)左鍵向下拖到 C22，放開鼠標(biāo) 10。這樣， C17：C22 就如同 C16 一樣建立了原料用量與所提供的能量之間的數(shù)量聯(lián) 系。4)D16：G22 的輸入，類似于 C16：C22 輸入的過(guò)程。都是先把 D16、E16、F16和 G16中鍵入相應(yīng)公式， 然后利用 Excel 的自動(dòng)填充功能再把 D17：D22、E17：E22， F17：F22 中自動(dòng)填充入公式。填充過(guò)程如圖 4 所示。圖 4 配方規(guī)劃表中營(yíng)養(yǎng)成分自動(dòng)填充B23薛琳：利用 Excel 設(shè)計(jì)飼料配方5)H16=B16*B6 。 H16 為要配制的 100kg的配合飼料中玉米的成本。 輸入后，飼料配方與飼料

17、原料玉米即建立了玉米用量與玉米成本之間的數(shù)量聯(lián)系。6)利用 Excel 自動(dòng)填充功能，為飼料配方與各種飼料原料建立原料用量與所需成本 間的數(shù)量對(duì)應(yīng)關(guān)系。即圖 5。圖 5 填充各種原料的成本2.2.3建立合計(jì)行第 23 行即為合計(jì)行。它的每一個(gè)單元格都是該單元格上面的各個(gè)單元格內(nèi)數(shù)字的總 和。具體建立過(guò)程：1) 單擊 B23，再單擊編輯欄的插入函數(shù)按鈕 ，彈出插入函數(shù)對(duì)話框。圖 6“插入函數(shù) ”對(duì)話框2) 選擇 SUM 函數(shù)，彈出函數(shù)選擇對(duì)話框，如圖 7，之后點(diǎn)確定即可。這樣，在 中即顯示出飼料配方中各種原料的總和，在此為 100(kg)11 。山西農(nóng)業(yè)大學(xué)本科畢業(yè)論文(設(shè)計(jì))3) 同上，利用自

18、動(dòng)填充功能，把 C23：H23 輸入相應(yīng)的合計(jì)公式，如圖 8圖 8“合計(jì)行 ”自動(dòng)填充2.2.4建立配方營(yíng)養(yǎng)水平行 一般飼料標(biāo)準(zhǔn)給出的都是每千克配合飼料中的各種養(yǎng)分的含量。在此，為便于操作， 也把第 23行合計(jì)數(shù)據(jù)計(jì)算為每單位的配合飼料中的養(yǎng)分的含量。即把 23 行每個(gè)單元格的 數(shù)據(jù)都除以 B23 內(nèi)的數(shù)字。具體操作為：B24=B23/B23,C24=C23/B23,D24=D23/B23E24=E23/B23,F24=F23/B23,G24=G23/B23,H24=H23/B23 2.2.5輸入規(guī)劃求解參數(shù)在全部完成以上工作后，就可以輸入規(guī)劃求解參數(shù)了。具體步驟為，選擇 “工具”菜單|“規(guī)劃

19、求解”選項(xiàng)，進(jìn)入 “規(guī)劃求解參數(shù)”對(duì)話框。當(dāng)輸入 參數(shù)完畢后，就會(huì)成為圖 9 所示。圖 9“規(guī)劃求解參數(shù) ”對(duì)話框?qū)Α耙?guī)劃求解參數(shù) ”對(duì)話框的操作如下：1)在“設(shè)置目標(biāo)單元格 ”后的框里面輸入目標(biāo)函數(shù)所在的單元格。在本文中為H23。再進(jìn)行下一項(xiàng)操作時(shí)， “H23”會(huì)自動(dòng)轉(zhuǎn)換為 “$H$23”即,由相對(duì)引用方式自動(dòng)轉(zhuǎn)換為 絕對(duì)引用方式。以下皆同。2)在“等于 ”項(xiàng)中，選擇最小值。因?yàn)槟繕?biāo)單元格是表示配方的總成本，成本以最小 圖 7“函數(shù)參數(shù) ”對(duì)話框值為最優(yōu)解。3)在“可變單元格 ”框中輸入可變單元格的名稱，即 B16：B21。因?yàn)樘砑觿┮话闶枪?定量，不參與優(yōu)化，所以可變單元格不包括 B22

20、單元格。4)在約束選項(xiàng)卡中，單擊 “添加 ”按鈕。屏幕彈出窗口： “添加約束 ”對(duì)話框。如圖 10薛琳：利用 Excel 設(shè)計(jì)飼料配方圖 10“添加約束 ”對(duì)話框在上述對(duì)話框中，依次輸入以下各項(xiàng)的約束條件。i.各種原料的用量都必須大于或等于零： 在“單元格引用位置 ”框中輸入 “B16： B21”，然后單擊符號(hào)框旁邊的 “ ”，會(huì)列出“=”、“=” 然后在“約束值”框中輸入 0，如圖 11。再單擊“”，然后會(huì)自動(dòng)進(jìn)入“規(guī)劃求解參數(shù) ”對(duì)話框的 “約束”列表框。圖 11 添加約束 : 各種原料的用量都必須大于或等于零ii.配方總重量約束： 類似上述 1）的步驟，在 “單元格引用位置 ”框中輸入

21、“B23”,在“ ”的選 項(xiàng)中選擇 “=，”“約束值”框中鍵入 “100,”再單擊 “”。iii.飼養(yǎng)標(biāo)準(zhǔn)約束： “單元格引用位置 ”框中輸入 “C2：4 G24”，“ ”的選項(xiàng)中選擇 “=”，“約 束值”框中錄入 “B3:F3,”再單擊“”。輸入完畢后，可檢查是否輸入正確，如否，可選定有誤的約束項(xiàng)，再單擊圖 9 中的 。屏幕將彈出 “改變約束 ”對(duì)話框，在其中即可修改有誤的約束項(xiàng)。圖 9 即為約束條件添加完全后 “規(guī)劃求解參數(shù) ”對(duì)話框所顯示內(nèi)容。從中，可以看出， 在此配方共有 3 個(gè)約束條件，分別是：各種原料在飼料配方中的用量是非負(fù)的，配方 總量約束為 100，飼料配方各養(yǎng)分水平不能低于營(yíng)

22、料標(biāo)準(zhǔn)的要求 12。 2.2.6規(guī)劃求解選項(xiàng)在圖9的規(guī)劃求解參數(shù)對(duì)話框中單擊 ，即進(jìn)入“規(guī)劃求解選項(xiàng) ”對(duì)話框。 在“規(guī)劃求解選項(xiàng) ”對(duì)話框中，選中 “采用線性模型 ”、 “假定非負(fù) ”，如圖 12。8山西農(nóng)業(yè)大學(xué)本科畢業(yè)論文（設(shè)計(jì)）圖 12 “規(guī)劃求解選項(xiàng) ”對(duì)話框然后回車，又返回到 “規(guī)劃求解參數(shù) ”對(duì)話框。2.2.7利用“規(guī)劃求解 ”工具優(yōu)化飼料配方 檢查輸入的原始數(shù)據(jù)以及 “規(guī)劃求解參數(shù) ”等的輸入，確定無(wú)誤后即可以使用 “規(guī)劃求 解”工具求解最優(yōu)飼料配方了。具體操作較為簡(jiǎn)單，只需要單擊圖 9“規(guī)劃求解參數(shù) ”中的求解按鈕即可。隨即計(jì)算機(jī) 開始自動(dòng)運(yùn)算。 稍候片刻，計(jì)算機(jī)便可以彈出 “

23、規(guī)劃求解結(jié)果 ”對(duì)話框，如圖 13 所示。同時(shí)， 在 Excel 表中顯示出求解結(jié)果。如圖 14 所示。圖 12 “規(guī)劃求解結(jié)果 ”對(duì)話框圖 13 規(guī)劃求解結(jié)果薛琳：利用 Excel 設(shè)計(jì)飼料配方在“規(guī)劃求解結(jié)果 ”對(duì)話框中，默認(rèn)選中 “保存規(guī)劃求解結(jié)果 ”，單擊 “確定”按鈕，就會(huì) 出現(xiàn)圖 14 結(jié)果報(bào)告界面。從圖 14 可以看到，利用 Excel 設(shè)計(jì)出的配方為下表 1 所示。而配方成本為 229.2456 元 /100kg,或 2.292456 元 /kg。表 1 Excel 設(shè)計(jì)配方結(jié)果原料名稱飼料配方 /kg玉米 071262.11533麥麩0.622146豆粕 071224.414

24、63石粉9.338211磷酸氫鈣2.132139食鹽0.377551添加劑1合計(jì)1002.2.8實(shí)驗(yàn)結(jié)論從圖 14 可以看出，所設(shè)計(jì)的配方的養(yǎng)分含量分別為： 代謝能 12.14Mj/kg、粗蛋白 16%、 鈣 3.85%、有效磷 0.48%、食鹽 0.37%。而要求的飼養(yǎng)標(biāo)準(zhǔn)為：代謝能 12.14Mj/kg 、粗蛋白 16.00%、鈣 3.85%、有效磷 0.48%、食鹽 0.37%，全部正好達(dá)到標(biāo)準(zhǔn)。而且，經(jīng)過(guò)數(shù)學(xué)方 法粗略檢驗(yàn)，所設(shè)計(jì)的配方是在所給出的原料、原料價(jià)格條件下，滿足飼養(yǎng)標(biāo)準(zhǔn)的最低成 本的配方。從這個(gè)實(shí)驗(yàn)可以看出，利用 Excel 來(lái)設(shè)計(jì)飼料配方是非常便利的：它只需要在很短的 時(shí)

25、間內(nèi)，就能夠根據(jù)所要求的飼養(yǎng)標(biāo)準(zhǔn)、原料養(yǎng)分和價(jià)格數(shù)據(jù)計(jì)算出最佳配方。充分體現(xiàn) 出 Excel 設(shè)計(jì)飼料配方簡(jiǎn)單、經(jīng)濟(jì)、準(zhǔn)確。所以，在中小型飼料企業(yè)以及一些規(guī)模養(yǎng)殖場(chǎng)中推廣使用 Excel 設(shè)計(jì)配方是一件很有 實(shí)際價(jià)值的事情，它省去了這些地方購(gòu)買專業(yè)配方軟件的資金，省去了他們?nèi)ト斯ぴO(shè)計(jì)配 方的難度，省去了他們購(gòu)買現(xiàn)成配方的成本。3討論3.1對(duì)使用 Excel “規(guī)劃求解 ”工具設(shè)計(jì)與優(yōu)化飼料配方的分析與總結(jié)3.1.1 Excel 規(guī)“劃求解 ”工具設(shè)計(jì)與優(yōu)化飼料配方的優(yōu)點(diǎn)利用 MS Excel“規(guī)劃求解 ”工具來(lái)優(yōu)化飼料配方，大為減輕了設(shè)計(jì)飼料配方的工作量， 明顯地提高了設(shè)計(jì)飼料配方的效率，提高

26、了配方設(shè)計(jì)的準(zhǔn)確性。使用 Excel “規(guī)劃求解 ”工具計(jì)算飼料配方極為快速， 只要輸入了配方的原料品種、 價(jià)格、山西農(nóng)業(yè)大學(xué)本科畢業(yè)論文(設(shè)計(jì))配比以及要求的約束條件， Excel 可在幾乎瞬間給出計(jì)算的結(jié)果，顯示于計(jì)算機(jī)顯示屏上。 而且，利用該方法設(shè)計(jì)的飼料配方，可以隨時(shí)方便地進(jìn)行各種調(diào)整。比如調(diào)整飼養(yǎng)標(biāo) 準(zhǔn)、原料成分或價(jià)格?？捎兄谶m應(yīng)市場(chǎng)變化的需要。用 MS Excel “規(guī)劃求解 ”工具優(yōu)化飼料配方，與數(shù)學(xué)手工計(jì)算原理無(wú)異，但卻可以高速 高效率進(jìn)行。反應(yīng)了科技的強(qiáng)大力量。3.1.2 Excel設(shè)計(jì)最佳飼料配方的關(guān)鍵利用 Excel 設(shè)計(jì)最佳飼料配方的最關(guān)鍵之處不是輸入公式求解之類，而

27、是在于制作準(zhǔn) 確及時(shí)的數(shù)據(jù)庫(kù)。只有數(shù)據(jù)庫(kù)設(shè)計(jì)合理、準(zhǔn)確、可信，才能依靠 Excel 的規(guī)劃求解功能， 設(shè)計(jì)出理想的有較高經(jīng)濟(jì)價(jià)值的飼料配方 13。3.1.3對(duì)配方成本進(jìn)行約束 比如可以依據(jù)飼料生產(chǎn)盈利的目標(biāo)數(shù)額來(lái)計(jì)算出相應(yīng)的飼料成本，從而作為一個(gè)配方 成本約束條件進(jìn)行規(guī)劃求解，從而滿足飼料公司的利潤(rùn)目標(biāo)。3.1.4約束條件在“規(guī)劃求解 ”中可以最多指定 500個(gè)約束條件。這一般都可以滿足飼料配方設(shè)計(jì)的需 要。3.2 Excel 設(shè)計(jì)配方的調(diào)整及優(yōu)化3.2.1約束條件的確定1) 飼料適口性：有怪味的原料必須限量使用。2) 原料可消化性：消化性不佳的原料應(yīng)限制用量。3)毒性原料：必須限定用量，如規(guī)

28、定上限用量。4)在飼料標(biāo)準(zhǔn)中以一個(gè)范圍規(guī)定用量的原料，根據(jù)其特性選擇取用高限還是低限， 如鈣可取低限，磷、食鹽等也可用低限，而對(duì)于粗纖維以及粗灰分應(yīng)該取高限。 這樣取值，是為了利于保證能量和蛋白質(zhì)等的優(yōu)先滿足。5)組成飼料配方的原料種類越少越好，在一般情況下，飼料配方中使用的飼料原料 種類不宜超過(guò) 15 種。6)當(dāng)使用動(dòng)植物油脂時(shí)，因?yàn)橛椭^(guò)多時(shí)不宜加工，而且會(huì)影響顆粒飼料質(zhì)量，使 配合飼料不易于保存，而是易于霉變。所以，一般油脂用量應(yīng)控制在3%以內(nèi)。7)飼料配方的總質(zhì)量一般是按 1 千克設(shè)計(jì)，這樣有利于使用。8)盡量少用等號(hào)約束，因?yàn)榈忍?hào)約束項(xiàng)易于導(dǎo)致無(wú)解情況。如必須要使用，可嘗試 運(yùn)用同時(shí)

29、使用上限、下限的兩端約束。9)限制大體積原料的用量，否則有可能無(wú)法滿足畜禽的營(yíng)養(yǎng)需要。3.2.2從不同角度調(diào)整配方以求優(yōu)化配方 當(dāng)所得到的配方某一種原料用量為零，而又不想讓它為零時(shí)，可以嘗試減少初步優(yōu)化 結(jié)果中用量多而且與該原料同類的其它原料的用量，從而使該原料用量大于零，否則，如 果直接去約束該原料的用量，有可能致使無(wú)解情況的發(fā)生。3.2.3調(diào)整高能量原料的用量10薛琳：利用 Excel 設(shè)計(jì)飼料配方用動(dòng)植物油脂易于增加能量水平，但用量由于上面講到的油脂的特性不可以多用。而 大量使用玉米生產(chǎn)濃縮料，又不能讓養(yǎng)殖戶認(rèn)可。所以，只能是依靠其它的能量水平相對(duì) 較高的原料，放寬對(duì)它們的約束條件。例如

30、，可以嘗試提高花生餅等的用量。 3.2.4調(diào)整兩種營(yíng)養(yǎng)特性相似原料的配比原料營(yíng)養(yǎng)特性相似情況下，如調(diào)整它們的配比合適，可以使它們的優(yōu)缺點(diǎn)互補(bǔ)，從而 提高其飼料性價(jià)比。3.3利用 Excel 設(shè)計(jì)濃縮飼料配方濃縮飼料即是全價(jià)配合飼料扣除能量飼料后的剩余部分。所以，設(shè)計(jì)濃縮飼料的配方 可以有兩種辦法：1) 先設(shè)計(jì)出全價(jià)配合飼料配方，然后把能量飼料從其中扣除，余下的再折合成百分 比含量，即成濃縮料的配方。2) 根據(jù)用戶能量飼料的特性與數(shù)量，確定濃縮料與能量飼料的比例，結(jié)合飼料標(biāo)準(zhǔn) 確定濃縮料中各種養(yǎng)分應(yīng)達(dá)到的水平，即濃縮料的質(zhì)量標(biāo)準(zhǔn)，最后根據(jù)這個(gè)標(biāo)準(zhǔn) 設(shè)計(jì)出濃縮飼料配方。4結(jié)束語(yǔ)4.1Excel 設(shè)

31、計(jì)飼料配方總結(jié)用 Excel 設(shè)計(jì)飼料配方優(yōu)點(diǎn)：簡(jiǎn)單、經(jīng)濟(jì)、快速、高效、便利。Excel 作為目前世界上最常用的辦公軟件之一，其操作簡(jiǎn)單流暢，界面美觀大方，易 于為各種學(xué)歷人士掌握其基本操作。而使用 Excel 去設(shè)計(jì)飼料配方，其操作也并不復(fù)雜， 與 Excel 常用操作方法并無(wú)多大本質(zhì)區(qū)別。所以，利用 Excel 設(shè)計(jì)飼料配方是個(gè)大多數(shù)人 都可以學(xué)習(xí)掌握的技能。事實(shí)上，許多的技術(shù)操作都是僅僅是一個(gè)熟能生巧的東西只要做得多了，自然就 會(huì)變得精通，變?yōu)閷＜?。?duì)于運(yùn)用 Excel 設(shè)計(jì)飼料配方更是如此，因?yàn)檫@個(gè)過(guò)程，并無(wú)需 什么復(fù)雜的計(jì)算機(jī)操作，也沒有什么深?yuàn)W的原理讓人必須理解。而對(duì)于飼料配方設(shè)計(jì)

32、中， 曾經(jīng)的最困難部分規(guī)劃求解最低成本配方，在如今，借助電腦，依賴程序，早已經(jīng)不 必要配方師親自動(dòng)手去計(jì)算、檢查。于是，對(duì)于在應(yīng)用數(shù)學(xué)中深?yuàn)W的規(guī)劃理論，在此，借 助電腦的幫助，人腦早已經(jīng)不必須去理解。配制配方，所要做的，就是輸入正確有用的原 始數(shù)據(jù)，然后單擊確定，就這么簡(jiǎn)單，這就是人類現(xiàn)在的處境電腦發(fā)達(dá)、人腦弱化。從這方面，再次看到了 Microsoft Office 的功能強(qiáng)大與豐富。4.2對(duì)飼料配方軟件行業(yè)的展望相比較使用專業(yè)飼料配方軟件，使用 Microsoft Excel 設(shè)計(jì)飼料配方比較簡(jiǎn)單明了，易 于上手。而且最重要的是它的成本低廉，易于為廣大的中小型飼料廠及養(yǎng)殖場(chǎng)接受11山西農(nóng)業(yè)

33、大學(xué)本科畢業(yè)論文（設(shè)計(jì)）但是未來(lái)的飼料行業(yè)絕對(duì)會(huì)是大型飼料企業(yè)博弈的場(chǎng)所，而非是當(dāng)今不計(jì)其數(shù)的中小 型飼料廠混戰(zhàn) ,致使市場(chǎng)混亂，產(chǎn)品良莠不齊，社會(huì)資源巨大浪費(fèi)。如此，在飼料配方設(shè)計(jì)上，也必定會(huì)是專業(yè)配方軟件成為市場(chǎng)主流。因?yàn)榇笮惋暳掀?業(yè)要在市場(chǎng)上競(jìng)爭(zhēng)，就必須在配方設(shè)計(jì)的每一個(gè)細(xì)節(jié)上去小心地進(jìn)行成本和效果的核算、 分析，以求最大限度地控制成本、提高性價(jià)比。而這只能依靠專業(yè)配方軟件的幫助！12薛琳：利用 Excel 設(shè)計(jì)飼料配方參考文獻(xiàn)1吳金龍 .計(jì)算機(jī)飼料配方技術(shù)及常見問題分析 .山東飼料 ,2004,Vol.10:18-242劉光磊 ,孫健,王寶維 .計(jì)算機(jī)飼料配方技術(shù) .山東家禽 ,20

34、04,Vol.12:38-393晏林 .線性規(guī)劃及其程序設(shè)計(jì) .文山師范高等專科學(xué)校學(xué)報(bào) ,2002,Vol.14,No.1:61-644田玉民 ,袁曉春 ,何麗濤 .應(yīng)用 Microsoft Excel 自動(dòng)設(shè)計(jì)飼料配方 .湖南飼料 ,2005,Vol.2:16-205Excel Home Excel 應(yīng)用大全 .北京 :人民郵電出版社 ,2008.89-1036徐夕水 ,鄧醒潤(rùn) ,陳慧華 ,邱雅潔 .利用 Microsoft Excel2002 電子表格線性規(guī)劃奶牛日糧配方示例.中國(guó)草食動(dòng)物 ,2004,Vol.24,No.1:30-327 Gene M.Pesti,Andrew F.Seila.The Use