Excel規劃求解在冷混水溶復合肥配方設計中的應用

劉宇鋒，譚裕模，周 斌，霍 豐

（1.廣西壯族自治區農業科學院 農業資源與環境研究所，廣西 南寧 530007；2.廣西新知耕農業科技有限公司，廣西 南寧 530007；3.廣西壯族自治區農業機械研究院有限公司，廣西 南寧 530007）

水溶肥料是經水溶解或稀釋，用于灌溉施肥、葉面施肥、無土栽培、浸種蘸根等用途的固體或液體肥料［1］，具有水肥同步、準確供肥、節本增效等顯著優點，在現代農業生產中得到越來越廣泛的應用。冷混水溶復合肥是指在水溶肥生產過程中，基礎原料的混配不產生或較少產生熱量，其具有工藝流程與生產過程簡單、配方靈活、水肥利用率高等優點。冷混水溶復合肥配方設計是指根據水溶復合肥的化學組成和性能要求，以及冷混水溶復合肥生產工藝的要求，對原材料進行配比計算以達到預期的產品組成。

配方設計的運算方法有手工計算和計算機計算2 種方法。手工計算過程煩瑣，結果準確性較差；計算機計算有線性規劃法、目標規劃法、模糊規劃法等，其中應用最普遍的是線性規劃法。線性規劃求解原理是在滿足約束條件的要求下不斷調整可變量，從而改變與可變量關聯的其他數據，進一步影響目標值，最終使目標值達到設置的控制值，即達到最優解［2-3］。Excel規劃求解法通過滿足線性等式或不等式的約束條件來求解目標函數，簡化了計算過程，具有計算快速、準確率高、操作便捷等特點。

以某農作物冷混水溶復合肥配方設計為例，以Microsoft Excel 2016版為基礎，介紹Excel規劃求解在冷混水溶復合肥配方設計中的應用。

1 冷混水溶復合肥配方設計原則與原料

1.1 配方設計原則

冷混水溶復合肥設計目標不僅要滿足某農作物對氮、磷、鉀等營養物質的需要及相互平衡，生產工藝可行，能穩定存在，而且要求原料成本最小化。

依據冷混水溶復合肥設計目標可確定設計肥料配方的3個原則：（1）以1 t肥料為標準，計算肥料原料（溶質）與水（溶劑）的總質量；（2）某農作物對N、P2O5、K2O 吸收的質量比約為3 ∶2 ∶4；（3）冷混水溶復合肥生產工藝條件下，水溶復合肥總溶質質量分數不能高于24%，否則將出現溶質鹽析現象。

1.2 原料

依據原料獲得難易程度、水溶性、采購成本，確定采用尿素、磷酸一銨和氯化鉀分別作為N、P2O5、K2O養分的供應原料，3種原料的養分類型與質量分數如表1所示。采用經過凈化的工業級軟水作為溶劑。

表1 原料養分類型與質量分數

2 線性規劃求解數學模型建立

冷混水溶復合肥配方設計問題可理解為線性規劃求解問題，可用線性規劃計算滿足配方設計目標和原則的配方組合。線性規劃配方設計有下列幾個基本假定：（1）只有一個目標函數，該目標函數是自變量的線性函數；（2）自變量是配方中各原料的用量，原料養分含量可轉化為自變量的線性函數，每個線性函數為一個約束條件，所有線性函數構成數學模型的約束條件集；（3）優化配方是在滿足約束條件下的自變量組合。

設1 t某農作物冷混水溶復合肥中尿素、磷酸一銨、氯化鉀和水的質量分別為x1、x2、x3、x4kg，依據該水溶肥設計3原則，分別設置以下數學模型：

3 Excel添加“規劃求解”模塊

Microsoft Excel 2016在默認狀態下，未提供“規劃求解”功能，需加載“規劃求解”項。具體方法為單擊“文件”→“選項”→“加載項”，在“管理”復選框中，選擇“Excel加載項”→“轉到”，在“加載宏”對話框中，勾選“規劃求解加載項”復選框，點擊“確定”后，Microsoft Excel 2016 在“數據”菜單選項卡工具欄中安裝“規劃求解”模塊。

4 設計Excel規劃求解工作表

建立Excel 規劃求解模型，關鍵是設置好目標單元格、可變單元格和合理的約束條件［2］。目標單元格是單元格、具體數值、運算符號的組合，目標單元格內是以“=”開始的一組數學公式。可變單元格相當于線性方程中的自變量，可變單元格直接或間接地與目標單元格相關聯，Excel 通過內部算法不斷調整可變單元格中的數值，直到滿足約束條件并且使目標單元格的數值達到最佳目標值。約束條件是影響目標單元格與可變單元格的內在關系，可變單元格通過約束條件與目標單元格相關聯［4-5］。

打開Excel工作簿，設計A1：E5單元格區域為工作區域，在A1：E1 單元格分別設輸入“序號”、“自變量名稱”、“自變量含義”、“Excel 形式方程式”、“計算結果”5 個名稱，分別表示A 至E 列數據含義（見圖1）。

圖1 Excel轉換形式方程組單元格輸入形式

圖1 中，A2：A5 單元格中“1”、“2”、“3”、“4”分別表示數學模型方程式代號。B2：B5 單元格中“x1”、“x2”、“x3”、“x4”，分別表示數學模型方程組中所設4個自變量的名稱。C2：C5單元格中“尿素”、“磷酸一銨”、“氯化鉀”、“水”，分別表示數學模型方程組中所設4個自變量的含義。D2：D5單元格為目標單元格。E2：E5 單元格設為可變單元格，也是Excel 規劃求解后計算結果的保存之處。

依據數學模型方程式5 至8與自變量預設可變單元格（圖1），在D2：D5 依次輸入“=E2+E3+E4+E5-1000”、“=92*E2-161*E3”、“=123.6*E3-60*E4”、“=76*E2+76*E3+76*E4-24*E5”。輸入的數學模型方程式均為相對引用。

5 規劃求解操作過程

在Microsoft Excel 2016“數據”菜單選項卡點擊“規劃求解”模塊，彈出“規劃求解”對話框。Excel 規劃求解模塊中目標單元格均為絕對引用。“目標單元（T）”設置為“$D$2”，表示絕對引用D2單元格的Excel形式轉化方程式。“到：”后選擇“目標值（V）”，然后在其后的空格內輸入“0”，表示目標單元格中數值等于0，即第一個方程式等于0。將可變單元格（B）設置為“$E$2：$E$5”，表示計算結果在制定的4個單元格內，分別對應自變量x1、x2、x3、x4。在“遵守約束”中分別添加“$D$2=0”、“$D$3=0”、“$D$4=0”、“$D$5=0”，表示在第1 個方程式等于0 的同時，其他3 個方程式也等于0。

選擇“使無約束變量為非負數（K）”，選擇求解方法（E）中的“單純線性規劃”，點擊“求解（S）”，彈出“規劃求解結果”對話框，選擇保留“規劃求解的解”。點擊“確定”，在E2：E5 可變單元格獲得方程組的解（見圖2）。

圖2 規劃求解計算結果

依據冷混水溶復合肥生產實際，對規劃求解計算結果取舍，x1、x2、x3、x4分別為87、50、103、760。

6 計算結果驗證

將計算結果代入數學模型（1）至（4）中進行驗證：

計算結果達到設定數學模型的要求，規劃求解過程正常。表明將尿素、磷酸一銨、氯化鉀與水分別 按87 kg、50 kg、103 kg 和760 kg 充 分 有 效 混合，能獲得預期濃度的冷混水溶復合肥。

7 結論

在肥料配方設計時，若原料之間關系不合理，約束條件存在缺陷，原料種類過多或過少，沒有考慮肥料生產的工藝要求，沒有考慮原料水溶條件下的溶解度限制等，都可能會在Excel 規劃求解配方分析中出現“無解”的情況。須認真研究作物養分需求規律以及肥料原料的基本性狀，對約束條件和目標設置做適當調整后，再進行規劃求解計算，以更加適應農作物、生產工藝及市場的實際情況，更加符合設計目標。

應用Excel 規劃求解功能設計冷混水溶復合肥配方，不但提高了肥料配方計算速度，簡化了手工配方計算的過程（可以代替人工計算，快速準確地獲得計算結果），而且可以隨農作物類型、基礎原材料種類、生產工藝靈活調整，滿足多樣化的冷混水溶復合肥生產需求。該方法可以適應現代小型水溶肥企業配方設計要求，具有一定的實用和推廣價值。