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 规划求解功能设计冷混水溶复合肥配方，不但提高了肥料配方计算速度，简化了手工配方计算的过程（可以代替人工计算，快速准确地获得计算结果），而且可以随农作物类型、基础原材料种类、生产工艺灵活调整，满足多样化的冷混水溶复合肥生产需求。该方法可以适应现代小型水溶肥企业配方设计要求，具有一定的实用和推广价值。