Excel规划求解在陶瓷配方设计中的应用

陈鹏程 刘建新

摘 要：配方设计是陶瓷企业一项重要的综合性技术工作，关乎生产的稳定和陶瓷产品性能的优劣。本文将Excel办公软件中的规划求解功能引入陶瓷配方设计中，通过原料种类和成分输入、运算关系确定、变量约束条件设置和目标设置等步骤，可规划优选出符合约束条件和目标的原料配比，免去人工配比试错的工作，具有重要的推广价值。

关键词：规划求解;配方;陶瓷

1 概 述

陶瓷配方设计是在陶瓷制品的化学成分组成体系下，针对产品的生产和性能诉求，在物理、化学原理和生产工艺要求的指导下，对原材料进行配比计算以达到预期的产品组成设计。对陶瓷企业来说，产品的配方不仅是内部的核心机密，配方的设计也是一项重要的综合性技术工作。配方设计的好坏决定了生产的稳定性和产品物理、化学性能的優劣。在建筑陶瓷行业所用矿物原料品质和稳定性日益下降以及陶瓷企业新产品开发力度逐渐加大的背景下，针对因不同批次原料波动和新配方开发而引入的配方调试工作也不断增加。

规划求解是数学建模中的一项重要内容，其原理是在符合约束条件的要求下不断调整可变量，从而改变与可变量链接的其它数据，进一步影响目标值，最终使目标值达到设置的控制值，即为达到最优解。目标控制值可以是使产品成本达到最低，也可以是使化学组成与预期最接近。

在计算机和办公软件普及化、平民化的背景下，配方设计这种需要大量运算和试错的工作正好符合规划求解的特点。为此，本文将Excel办公软件中的规划求解功能引入陶瓷配方设计中，通过原料种类和成分输入、运算关系确定、变量约束条件设置和目标设置等步骤，可规划优选出符合约束条件和目标控制值的原料配比，加速配方开发工作的进程，具有重要的实用价值和推广意义。

2 实施步骤

本实施例以建筑陶瓷坯体配方设计为例，按步骤介绍规划求解在陶瓷配方设计中的应用。

2.1 软件设置

本文采用Excel 2010版本为规划求解的软件工具。默认情况下，软件未在主界面展示出"规划求解"功能按钮，需要按步骤开通：文件→选项→加载项→“Excel加载项”转到→勾选“规划求解加载项”，然后“规划求解”功能按钮即可在主界面“数据”选项卡下找到。

2.2 原料种类成分输入

原料种类可以是企业陶瓷坯体原料库中的所有可用原料。成分组成可包括常规“八大元素”氧化物含量和1000℃烧失量（已扣除原料含水率）。本例中，原料种类和成分在Excel中输入范例如图1所示。

在输入的原料初始数据中，组成总量往往不是准确的100，而且还包含了烧失量数据。而配方设计主要在于化学组成与预期的匹配，因此需要对图1中的数据进行转换，去掉烧失量项目，并使各化学组成的相对含量总和为100。转换后的数据如图2所示，配比设计是在转换后的数据基础上进行的。

最终各配比的总和也应为100，因此需要在“A23”单元格设置各原料对应配比的求和公式，方便后续设置约束条件。

2.3 运算关系确定

当各原料的配比所在单元格确定后，实际的配方，即化学组成应当是配比数值乘以相应原料的各个化学成分含量，然后将得到的各原料中同一化学成分的含量相加。按此运算关系在表格中设置相应的计算公式，并填入目标配方数值，如图3所示。

因图2中各原料的配比初值为0，所以图3中相应的结果为0。此时以配比为变量，总计中各成分的含量为因变量的运算关系已经确定。

2.4 变量约束条件设置

本实例中，变量即为图2所示的“A14：A22”单元格中的数值，这些数值需满足以下约束条件：

（1）大于等于0;

（2）均为整数;

（3）总和为100。

2.5 目标设置

目标的设置直接影响最后规划求解的结果。比如以成本最低为目标，那么在组合方案中当成本为最小值时，规划求解的运算过程就会停止。而在本例中，目标应当是图3中“总计”的各数值与“目标配方”中对应数值的差别最小时，即得到最优解。那么如何用公式来表示这种含多个单元格差别呢？根据最小二乘法的思想，有两种方案可以采用：

（1）将对应单元格的差取绝对值，然后将各个绝对值求和;

（2）将对应单元格的差取平方，然后将各个平方值求和。

为减少计算机的运算负担，本例中采用第1种方案较好。在陶瓷坯体配方设计中，Fe2O3和TiO2的含量均较少，而且对性能的影响可以忽略，所以这两种成分不纳入目标考量。因此目标设置方案就是在“C40”单元格建立公式，对“总计”和“目标配方”中对应化学成分含量的差的绝对值求和。并在“规划求解”设置界面中确定目标单元格为“C40”到“最小值”，求解方法选择“非线性GRG”，如图4所示。

2.6 规划求解

参数设置完成后，点击“求解”按钮，程序即可自动计算出符合约束条件和目标的配方方案，结果如图5所示（此时1#～9#原料的配比分别为14， 7， 5， 8， 23， 2， 5， 5， 31）。

从图5中可以看出，Al2O3，CaO，MgO成分与目标值还有较大差距，因此需要在目标单元格的公式中作相应的改进：将“总计”和“目标配方”中对这三种化学成分含量的差的绝对值乘以一个权重（分别乘以1.1，1.4和1.1），再次进行规划求解计算。结果如图6所示（此时1#～9#原料的配比分别为0，1，0，37，37，3，5，0，17）。

此时，实际配方与目标配方中除Fe2O3和TiO2外，各成分含量与目标配方的差别控制在0.1%左右，达到了较高的配方精度。得到该配比结果后，在实际生产中只需进行一个原料含水率的转换计算，即可得到生产配料称重的数据，大大加速了配方设计的进度。

3 讨 论

从以上实例中可看出，将规划求解用于陶瓷配方设计中可以完全代替人工试错的计算过程，方便、快速地达到目标结果。生产中某些特殊原材料的用量限制也可以通过约束条件进行设置，满足多样化的用料需求。当计算机无法在短时间内获得最优解时，则可以考虑对约束条件和目标设置进行调整后再进行规划求解计算。有些情况下，利用原料库已有的原材料化学组成进行多次规划求解均无法满足某些成分的含量要求时，则可以考虑采购该种成分含量高的原材料。对于一些价格比较低的原料，可以在约束条件中设置其配比的范围，避免因规划求解目标的唯一导向性，使最优解中该原料的配比较少。

综上，规划求解在陶瓷配方设计中的广泛应用，不仅能够快速得到符合要求的目标配方，而且对于原材料采购、低品质原料的应用、降低产品成本等方面均具有现实的指导意义。