Excel函數(shù)的應用

wyg

求之甚矣，今得之，高興不已

angfasiliao

我下載了，此表格對我很有用，就是看不懂，還請老師幫忙給于指點。謝謝了

dq1856

謝謝！真是雪中送炭啊。無數(shù)個日夜里曾是這么的思念，沒曾想到在這里邂逅了。

zjouln

感謝樓主,本人正愁不知上哪找這方面的資料.但不知能否打開?

ivanli721

o k 多謝了 多謝了

ZYK_88

先下載看看,以后好好學習!

hjx7671

利用 Excel設計飼料配方
  劉琴  汶川縣農(nóng)牧水利局
陳志遠  屈剛  阿壩州中等職業(yè)技術學校
摘要：將飼料配合中的“試差法”與excel的運算、模板、和規(guī)劃求解功能等結合，設計限定條件下的最低成本飼料配方，并以此為模板實現(xiàn)對飼料配方的快捷準確調(diào)整。
關鍵詞：excel   規(guī)劃求解  飼料配方
在畜禽養(yǎng)殖中，飼料成本一般要占舍飼養(yǎng)殖總成本的70%左右。降低養(yǎng)殖成本的運作空間主要在于降低飼料成本，而降低飼料成本最有效途徑，首先是日糧提供營養(yǎng)的種類、數(shù)量及養(yǎng)分間相互影響關系等方面與畜禽的需求達到供需理想吻合，在確保畜禽生產(chǎn)潛力充分發(fā)揮的同時使飼料養(yǎng)分物盡其用；其次是充分利用廉價飼料，本地飼料，盡可能少的耗用飼料資源，節(jié)約飼料開支。這也正是飼料配方設計要解決的問題。所謂飼料配方設計，就是運用一定的計算方法，根據(jù)飼料原料的營養(yǎng)成分含量，飼料價格、可利用飼料資源貯備情況和配方設計要求，畜禽的營養(yǎng)需求及其對特殊飼料的限制等，產(chǎn)生配方中各原料比例或量的一種運算過程。
在生產(chǎn)中，畜禽的營養(yǎng)需求、飼料原料價格、預期生產(chǎn)水平、經(jīng)營策略性調(diào)整都在不斷發(fā)生變化，因而配方需要經(jīng)常性調(diào)整以保持飼料供給符合養(yǎng)殖利益最大化的經(jīng)營要求。但配方設計調(diào)整要同時考慮多種變量因素，計算煩雜，計算量大，現(xiàn)在大型飼料公司已使用專用飼料配方軟件設計配方，但配方軟件價格高，還不能在小型配合飼料廠和中小型養(yǎng)殖場普及應用。我們這里主要探討借助Excel 的運算、模板和規(guī)劃求解功能設計飼料配方，并以此為模板實現(xiàn)對配方快捷、準確的調(diào)整計算。這一方法可在中小型飼料廠、養(yǎng)殖場應用。
設計飼料配方操作步驟：
1.              制作本地區(qū)常用飼料營養(yǎng)價值表保存待用
分別制作豬、雞、肉牛、乳牛、羊等的常用飼料營養(yǎng)成分表。主要在我國現(xiàn)行飼養(yǎng)標準中查找，同時將一些零散的本地飼料養(yǎng)分分析結果列入表中（如圖1）。
2.   查畜禽飼養(yǎng)標準表，確定配料對象的營養(yǎng)需求。如
20Kg體重生長育肥豬營養(yǎng)需要（見表1）：
表1  20Kg體重生長育肥豬營養(yǎng)需要
DE
(MJ/kg)        CP
%        Ca
%        P
%        CF
%        Cu (mg/kg)        Fe
(mg/kg)        Se
(mg/kg)        賴氨酸
％        蛋 光氨酸
％
12.97        16        0.6        0.5        4        4.36        60        0.3        0.75        0.38
3.   制作配方模板
3.1 將所選飼料營養(yǎng)價值表、飼料原料價格，配料對象營養(yǎng)需要等按下列模型制成配方求解計算表，并根據(jù)配料經(jīng)驗確定初始配比（如圖2）。
3.2計算初始值
將光標移到E17，在工具欄上點擊“粘貼函數(shù)”，選擇數(shù)學與三角函數(shù)中的SUMPRODUCT（若干數(shù)組中彼此對應元素乘積的和），輸入第一數(shù)組$P2:$P16和對應數(shù)組E2：E16，然后除100。把光標放在E17格右下角位置上，當光標變成“+”時往右拖O17，這樣從E17至O17格都輸入了相應的公式并計算出了對應數(shù)值。當改變配料組成時，配料養(yǎng)分、價格都隨之變化，通過對比營養(yǎng)供需滿足情況和價格，調(diào)整配料比例即可得到較滿意的配方。若將配料比例改為配料的量就可用于配合日糧，特別適于配制牛羊日糧
3.3 線性規(guī)劃求解限制條件下的最低成本配方
3.3.1選中“工具”菜單，選中“規(guī)劃求解”命令，出現(xiàn)“規(guī)劃求解參數(shù)”對話框(如圖3)。
3.3.2在[設置目標單元格]編輯框中，輸入目標單元格的名稱$O$17，然后點擊[最小值]選項。
3.3.3在[可變單元格]中，輸入$P$2:$P$16。
3.3.4在[約束]窗口中單擊添加按鈕，產(chǎn)生“添加約束”對話框，在[單元格引用位置]和[約束值]輸入欄中輸入約束條件如(圖4)。本例在[單元格引用位置]上先輸入$E$17:$F$17；然后選擇下拉窗口中的“=”；接著在[約束值]上輸入$E$18:$F$18。點擊添加按鈕，繼續(xù)添加約束條件$G$17:$H$17≥$G$18:$H$18；$P$11≥1；$P$14=0.2； $P$15=1；$P$17=100；$P$2≥5；$P$4≥3；$P$5≥2；$P$6≥3；$P$7≤8；$P$7≥5；$P$8≥5；$P$9≥10；$P$9≤20。輸完約束條件以后關閉窗口，回到“規(guī)劃求解參數(shù)”窗口。

圖5 規(guī)劃求解選項對話框
3.3.5 點擊選項按鈕，進入“規(guī)劃求解選項”對話框，（圖5）選中“采用線形模型”、“假定非負”、“正切函數(shù)”、“向前差分”、“牛頓法”，然后點擊確定按鈕，回到“規(guī)劃求解參數(shù)”對話框。

圖6 規(guī)劃求解結果對話框
3.3.6單擊求解按鈕，開始計算，進入“規(guī)劃求解結果”對話框（如圖6），在該對話框中將顯示是否找到滿足條件的最優(yōu)解，如果找到，會有運算結果報告，敏感性報告和極限值報告顯示，可全部選擇后保存。系統(tǒng)默認為運算結果報告，后兩個報告可作調(diào)整配方參考。
如果顯示未找到有用的解，表明限定條件過于苛刻，或相互矛盾。如粗纖維限定值較小，而糠麩原料限定用量卻較高；同一原料的上下約束范圍過小，將限制營養(yǎng)指標值的達到。這時需要對限定條件進行調(diào)整，再進行計算。未找到有用的解時，將顯示近似計算值，也有一定參考價值。
4. 保存模板備用
計算結果保存后就可作為同類型畜禽飼料配方的模板使用，設計同類型配方，或因原料價格變化等需要變動配方時，不必重新設計計算，只需在此模板上調(diào)整限制條件，輸入新的原料價格求解，即可快捷得到計算結果。

心香一瓣

很需要 這方面的資料，多謝！

wzg9811

感謝您的分享