基于Excel的陶瓷原料数据库及配方线性规划优化探索

刘 明，黄中文

（景德镇陶瓷大学，江西 景德镇 333403）

基于Excel的陶瓷原料数据库及配方线性规划优化探索

刘 明，黄中文

（景德镇陶瓷大学，江西 景德镇 333403）

本文在建立三元配方形式的陶瓷配方、原料的Excel表格数据库基础上，用Visual Basic制作了对数据库的查询的可视化界面，实现了用户对原料和配方的动态查询。同时，本文使用Excel电子表格自带的“规划求解“工具，建立了原料成本最低的配方优化数学模型，通过计算实例模拟表明，运用Excel线性规划功能进行陶瓷配方的优化设计是可行的。

陶瓷配方；Excel数据库；规划求解；Visual Basic程序设计

0 引 言

线性规划(Linear Programming，简记为LP)是运筹学的一个分支。它的研究对象实际上是在一组线性约束条件下，求某一线性目标函数的最小值问题。事实上，构成陶瓷配方原料的化学成份或物理性能指标与陶瓷配方绝大部分呈线性关系，因此可建立原料优化的线性方程组模型，实现配方优化计算。

随着计算机技术的发展，国内外研究陶瓷配方的计算机优化求解不断取得瞩目的成就，如：Lehman等人应用“特征化”的概念及其指标对坯料进行原料代换(即“重配”)，在“重配”计算中他们使用了线性规划技术，成本最低作为其目标函数，取得了很好的效果[1]。国内的高力明教授将LP技术用于玻璃、陶瓷等某些配料的计算，也取得了不少成果[2]。

Excel是微软公司推出的办公系统Office的一个组件，Office是被全世界使用最为广泛的办公软件。然而，在许多系列图书中对Excel 的“规划求解”功能都是一带而过的介绍，在实际应用中也没有得到很好的使用。由于Excel的运用广泛和使用简单，而且它具有容易掌握的数据库功能，本文探索了使用它对建立陶瓷原料数据库，并在此基础上运用它的线性规划功能进行配方优化计算，以期为同行在开发陶瓷新产品时进行配方计算时提供参考。

1 建立Microsoft Excel 陶瓷原料数据库

陶瓷原料可分为两种：天然矿物原料(如石英、长石、高岭土、石灰石、方解石、锆英石等)和化工原料(如ZnO、SnO2、硼砂等)。因此，本文建立的陶瓷坯釉料配方以及原料的数据库包含：坯料配方、釉料配方、粘土类原料、石英类原料、熔剂类原料和釉料制备所需要的一些化工原料，各类原料数据以不同的工作簿分开记录。

用Microsoft Excel建立工作溥是很简单的工作，这里只列出如图1所示的常用陶瓷原料配方数据库，其数据的来源大都来源于教材及网上信息，有部分可能缺乏准确性；特别是“价格”一项，随着市场波动变化大，但用户可以及时根据我国资源与市场的变化来调整各类原料的组成和价格，实现数据库的及时更新。为了用Visual Basic编程调用数据库，建立数据库时各个工作簿数据格式应该保持大体一致。

2 Excel“规划求解”的加载

使用 Excel 中的“规划求解”功能与大家熟悉的Excel 菜单项不同，在 Excel 的菜单中不能直接找到。“规划求解”是 Microsoft Office Excel 的一个加载项程序。要在 Excel 中使用规划求解，必需要先进行加载。以 Excel2003 为例说明其加载过程：

①单击“工具” ——“加载宏”，出现“可用加载宏(A)”对话框中。

②在“可用加载宏”框中，选中“规划求解加载项”复选框，然后单击“确定”。

③加载规划求解加载宏后，“规划求解”命令将出现在“工具”选项卡中(见图2)。这时就可以用“规划求解”对线行规划问题进行求解了。

图1 陶瓷原料配方数据库Fig.1 Example of the ceramic material database

图2 “规划求解”功能加载Fig.2 The load of Excel “Linear Programming Solve” function

3 程序设计

3.1 设计思路

本文用Visual Basic设计了一个结合Excel规划功能与数据库调用的程序系统，图3为该系统的主界面工作流程。

3.2 主界面设计

主界面的设计要方便用户对陶瓷坯釉料方案和各类原料的选择，并能对选取的数据进行增添、删除，主界面如图4所示。主界面主要包括option、ComboBox、MshFlexGrid、ssTab、 CommandButton、label、ProgressBar等控件，以及一些文本框和标签控件，以实现以下功能：

①Option单选控件：建立控件数组，option1(0)判定为坯料方案，option1(1)为釉料。

②Frame结构区域控件：设定上下2个区域，分别为参数输入区域和参数输出区域。

③ComboBox下拉框控件：单击后，在下拉框中选择各类原料的种类数目。

④MshFlexGrid网格控件：建立多个此控件，分别写入数据库中包含的：配方、长石料、粘土料、石英料、其他原料数据记录；同时实现单击某条数据，能对此条数据记录进行选择、删除等操作。

图3 主界面工作流程图Fig.3 The flow working chart of the main interface

图4 主界面效果图Fig.4 The rendering of the main interface

⑤Sstab多页控件：实现单击具体一页，当前显示MshFlexGrid网格对应的数据。

⑥CommandButton按钮控件：添加“读入数据”按钮，单击后实现具体动作。

⑦Progressbar控件：写入数据时间较长时，显示写入进度。

4 程序运行

4.1 模拟实例

文献[4]介绍了如何采用MATLAB 和LINDO软件，进行求解某釉料成本最低的最佳配方。其配方及所用原料化学组成见图5，为验证本系统的可靠性，本文采用此配方进行模拟运行。

4.2 运行VB程序，建立数据表格

现依照图5表格中所给条件，运行本系统。步骤如下：

①进入到主界面后，在“选择产品”控件上选“釉料”。照条件所给，选择各类原料种数：长石料、粘土料、石英料各1种，其他原料3种；单击“读入数据”按钮，系统会将数据库数据读入。

②在“多页控件”中的各个网格中依次选择配方方案、原料种类(为便于查看，在本数据库中将图5表格中的原料预设名称为“范例”)。

③单击“开始优化”按钮，系统自动打开一个Excel文件：Book1。数据区域如图6所示。

4.3 规划求解

4.3.1 根据已知条件，建立LP数学模型：

图5 某釉料配方及所用原料化学组成[4]Fig.5 The chemical composition of a glaze batch formula and raw materials

在本数学模拟计算中，按照工艺条件要求，设定氧化锌含量2%，并根据经验将Fe2O3、MgO舍去不计，规划目标取配方所用各原料的总价最小，根据本配方所用原料组成(见图6)，本模拟对象的LP数学模型如下：

约束条件：

图6 模拟计算数据结构Fig.6 The structure of database in the simulated model

目标函数(总价)：

工艺要求：氧化锌为0.02，X6=0.02；Fe2O3、MgO含量则舍去。

约束值：X1+X2+X3+X4+X5≤0.98

式中，X1、X2、X3、X4、X5、X6分别为所选用原料长石、粘土、石英、白垩、锆石英、氧化锌的重量百分含量。

4.3.2 Excel规划求解过程

在Excel界面，单击“工具”—“规划求解”，逐一填入条件(参见图7)。

① 单元格表示：目标为“总价”取最小值，在Excel表中双击“总价”数值所在单元格B15(参见图8)，输入“=SUM(B12*B14+C12*C14+D12* D14+E12*E14+F12*F14+G12*G14)”。因Excel表中14行为各原料的“优化用量”，12行为原料对应“单价”，输入函数即表示“ ”

②决策变量的表示：单元格B14~G14代表模型中的决策变量X1~X6的最优解，该配方的价格需要计算机不断的把X1~X6可能的数值代入到目标函数计算。在“规划求解”这些单元格称为“可变单元格”。 B14~G14的值要满足“>=0”

③ 约束的表示：为保证“实际配方”无限接近于“实际配方”，任一化学成分在两者之间的差值应该趋于零。即以单元格O3为例，O3对应的值“>=0”且“<=”N3的值。

配方计算的方法要求，“优化用量”总和接近1。为此，“约束值”所在值对应单元格O14输入“=SUM(B14:F14)”，当O14的值越接近1，精度越高。

④决策变量限制表示。

在“实际配方”中，任一化学成分的百分数，单元格O3输入“=SUM(B3*$B$14+C3*$C$14+D 3*$D$14+E3*$E$14+F3*$F$14+G3*$G$14)”使用“$”实现绝对引用，下拉单元格是时，“$”后跟的地址不变，实现O3～O11公式自动填充。

图7 规划求解参数Fig.7 The parameters for formula calculation

图8 本系统运行结果Fig.8 The Result of This Calculate

表1 文献[4]成本最低最佳配料配方Tab.1 The best glaze batch formula at the lowest cost

在设计中, 如果最优解的价格偏高,可以在允许范围内调整某些原料的用量, 如限制价格比较高的锆英石和氧化锌的用量, 增加价格比较低的原料用量, 如粘土和石英。

如果设置约束条件不能得到“最优解”, 往往是因为设置的约束条件过分苛刻, 一般只要修改约束条件即可, 例如把最佳配方比之和的约束条件适当设置在 0.9 与 1 之间一般就可以求到最优解。

4.4 运行结果与分析

图8为本系统模拟的运行结果，将它与文献[4]所得优化结果(见表1)比较，本系统优化后配方的“总价”为821.009元，与文献[4]提供的816.48-823.68范围一致，各原料配比也与文献[4]所得结果很接近，证明了本系统程序的可行性。

5 结 论

(1)Excel具备强大的规划求解功能，用VB编程实现陶瓷配方的优化是可行且较为简单。

(2)通过实例模拟运行，优化结果与实例很接近，说明本文所开发的陶瓷配方优化系统对工艺科技人员研发陶瓷产品新配方有一定的指导作用。

(3)本系统界面清晰、美观，用户操作简便，“基于Excel的陶瓷配方优化系统”有一定的实用价值。

[1] LEHMAN R L, et al. Reformulation of white bodies using characterization and linear programming methods and techniques. Am. Ceram. Soc. Bull., 1984, 63(8): 1039

[2] 高力明. 线性规划在硅酸盐工业配料计算中的应用[J]. 玻璃与搪瓷, 1986, 4: 1-7, 13.

GAO L M. Glass & Enamel, 1986, 4: 1-7, 13.

[3] 蔡英骥,马铁成, 王梦林, 等. 陶瓷原料最优化掺和计算机软件的研制[J]. 中国陶瓷, 1994, 33(7): 41-44.

CAI Y Y, MA T C, WANG M L, et al. China Ceramic, 1994, 33(7): 41-44.

[4] 邓美兰, 孙国梁, 唐燕超, 等. 陶瓷配方设计的灰色优化方法[J]. 中国陶瓷, 2006, 42(7): 33-36.

DENG M L, SUN G L, TANG Y C, et al. China Ceramic, 2006, 42(7): 33-36.

Ceramic Material Databank and Linear Programming of the Dispensation Based on Excel

LIU Ming, HUANG Zhongwen
(Jingdezhen Ceramic Institute, Jingdezhen 333403, Jiangxi, China)

In this paper the ceramic material databank has been set up by using Excel, and a Visual interface for inquiring the databank has also been designed by using the Visual Basic language. So the user can dynamically inquire the databank. On the other hand, using the Programming_solving tool provided by Excel, the mathematical model at the lowest cost of raw material is created. The results of the computer simulation indicated that the Linear Programming technology can efficiently solve the optimized ceramic material dispensation.

ceramic formula; Excel databank; Programming Solver; Visual Basic Programming

TQ174.4

A

1000-2278(2016)06-0729-06

10.13957/j.cnki.tcxb.2016.06.027

2016-04-12。

2016-05-27。

刘明(1965-)，男，副教授。

Received date: 2016-0 4-12. Revised date: 2016-05-27.

Correspondent author:LIU Ming(1965-), male, Associate professor.

E-mail:573613619@qq.com