劉 明，黃中文

（景德鎮(zhèn)陶瓷大學(xué)，江西 景德鎮(zhèn) 333403）

基于Excel的陶瓷原料數(shù)據(jù)庫(kù)及配方線性規(guī)劃優(yōu)化探索

劉 明，黃中文

（景德鎮(zhèn)陶瓷大學(xué)，江西 景德鎮(zhèn) 333403）

本文在建立三元配方形式的陶瓷配方、原料的Excel表格數(shù)據(jù)庫(kù)基礎(chǔ)上，用Visual Basic制作了對(duì)數(shù)據(jù)庫(kù)的查詢的可視化界面，實(shí)現(xiàn)了用戶對(duì)原料和配方的動(dòng)態(tài)查詢。同時(shí)，本文使用Excel電子表格自帶的“規(guī)劃求解“工具，建立了原料成本最低的配方優(yōu)化數(shù)學(xué)模型，通過(guò)計(jì)算實(shí)例模擬表明，運(yùn)用Excel線性規(guī)劃功能進(jìn)行陶瓷配方的優(yōu)化設(shè)計(jì)是可行的。

陶瓷配方；Excel數(shù)據(jù)庫(kù)；規(guī)劃求解；Visual Basic程序設(shè)計(jì)

0 引 言

線性規(guī)劃(Linear Programming，簡(jiǎn)記為L(zhǎng)P)是運(yùn)籌學(xué)的一個(gè)分支。它的研究對(duì)象實(shí)際上是在一組線性約束條件下，求某一線性目標(biāo)函數(shù)的最小值問(wèn)題。事實(shí)上，構(gòu)成陶瓷配方原料的化學(xué)成份或物理性能指標(biāo)與陶瓷配方絕大部分呈線性關(guān)系，因此可建立原料優(yōu)化的線性方程組模型，實(shí)現(xiàn)配方優(yōu)化計(jì)算。

隨著計(jì)算機(jī)技術(shù)的發(fā)展，國(guó)內(nèi)外研究陶瓷配方的計(jì)算機(jī)優(yōu)化求解不斷取得矚目的成就，如：Lehman等人應(yīng)用“特征化”的概念及其指標(biāo)對(duì)坯料進(jìn)行原料代換(即“重配”)，在“重配”計(jì)算中他們使用了線性規(guī)劃技術(shù)，成本最低作為其目標(biāo)函數(shù)，取得了很好的效果[1]。國(guó)內(nèi)的高力明教授將LP技術(shù)用于玻璃、陶瓷等某些配料的計(jì)算，也取得了不少成果[2]。

Excel是微軟公司推出的辦公系統(tǒng)Office的一個(gè)組件，Office是被全世界使用最為廣泛的辦公軟件。然而，在許多系列圖書(shū)中對(duì)Excel 的“規(guī)劃求解”功能都是一帶而過(guò)的介紹，在實(shí)際應(yīng)用中也沒(méi)有得到很好的使用。由于Excel的運(yùn)用廣泛和使用簡(jiǎn)單，而且它具有容易掌握的數(shù)據(jù)庫(kù)功能，本文探索了使用它對(duì)建立陶瓷原料數(shù)據(jù)庫(kù)，并在此基礎(chǔ)上運(yùn)用它的線性規(guī)劃功能進(jìn)行配方優(yōu)化計(jì)算，以期為同行在開(kāi)發(fā)陶瓷新產(chǎn)品時(shí)進(jìn)行配方計(jì)算時(shí)提供參考。

1 建立Microsoft Excel 陶瓷原料數(shù)據(jù)庫(kù)

陶瓷原料可分為兩種：天然礦物原料(如石英、長(zhǎng)石、高嶺土、石灰石、方解石、鋯英石等)和化工原料(如ZnO、SnO2、硼砂等)。因此，本文建立的陶瓷坯釉料配方以及原料的數(shù)據(jù)庫(kù)包含：坯料配方、釉料配方、粘土類(lèi)原料、石英類(lèi)原料、熔劑類(lèi)原料和釉料制備所需要的一些化工原料，各類(lèi)原料數(shù)據(jù)以不同的工作簿分開(kāi)記錄。

用Microsoft Excel建立工作溥是很簡(jiǎn)單的工作，這里只列出如圖1所示的常用陶瓷原料配方數(shù)據(jù)庫(kù)，其數(shù)據(jù)的來(lái)源大都來(lái)源于教材及網(wǎng)上信息，有部分可能缺乏準(zhǔn)確性；特別是“價(jià)格”一項(xiàng)，隨著市場(chǎng)波動(dòng)變化大，但用戶可以及時(shí)根據(jù)我國(guó)資源與市場(chǎng)的變化來(lái)調(diào)整各類(lèi)原料的組成和價(jià)格，實(shí)現(xiàn)數(shù)據(jù)庫(kù)的及時(shí)更新。為了用Visual Basic編程調(diào)用數(shù)據(jù)庫(kù)，建立數(shù)據(jù)庫(kù)時(shí)各個(gè)工作簿數(shù)據(jù)格式應(yīng)該保持大體一致。

2 Excel“規(guī)劃求解”的加載

使用 Excel 中的“規(guī)劃求解”功能與大家熟悉的Excel 菜單項(xiàng)不同，在 Excel 的菜單中不能直接找到?！耙?guī)劃求解”是 Microsoft Office Excel 的一個(gè)加載項(xiàng)程序。要在 Excel 中使用規(guī)劃求解，必需要先進(jìn)行加載。以 Excel2003 為例說(shuō)明其加載過(guò)程：

①單擊“工具” ——“加載宏”，出現(xiàn)“可用加載宏(A)”對(duì)話框中。

②在“可用加載宏”框中，選中“規(guī)劃求解加載項(xiàng)”復(fù)選框，然后單擊“確定”。

③加載規(guī)劃求解加載宏后，“規(guī)劃求解”命令將出現(xiàn)在“工具”選項(xiàng)卡中(見(jiàn)圖2)。這時(shí)就可以用“規(guī)劃求解”對(duì)線行規(guī)劃問(wèn)題進(jìn)行求解了。

圖1 陶瓷原料配方數(shù)據(jù)庫(kù)Fig.1 Example of the ceramic material database

圖2 “規(guī)劃求解”功能加載Fig.2 The load of Excel “Linear Programming Solve” function

3 程序設(shè)計(jì)

3.1 設(shè)計(jì)思路

本文用Visual Basic設(shè)計(jì)了一個(gè)結(jié)合Excel規(guī)劃功能與數(shù)據(jù)庫(kù)調(diào)用的程序系統(tǒng)，圖3為該系統(tǒng)的主界面工作流程。

3.2 主界面設(shè)計(jì)

主界面的設(shè)計(jì)要方便用戶對(duì)陶瓷坯釉料方案和各類(lèi)原料的選擇，并能對(duì)選取的數(shù)據(jù)進(jìn)行增添、刪除，主界面如圖4所示。主界面主要包括option、ComboBox、MshFlexGrid、ssTab、 CommandButton、label、ProgressBar等控件，以及一些文本框和標(biāo)簽控件，以實(shí)現(xiàn)以下功能：

①Option單選控件：建立控件數(shù)組，option1(0)判定為坯料方案，option1(1)為釉料。

②Frame結(jié)構(gòu)區(qū)域控件：設(shè)定上下2個(gè)區(qū)域，分別為參數(shù)輸入?yún)^(qū)域和參數(shù)輸出區(qū)域。

③ComboBox下拉框控件：?jiǎn)螕艉?，在下拉框中選擇各類(lèi)原料的種類(lèi)數(shù)目。

④MshFlexGrid網(wǎng)格控件：建立多個(gè)此控件，分別寫(xiě)入數(shù)據(jù)庫(kù)中包含的：配方、長(zhǎng)石料、粘土料、石英料、其他原料數(shù)據(jù)記錄；同時(shí)實(shí)現(xiàn)單擊某條數(shù)據(jù)，能對(duì)此條數(shù)據(jù)記錄進(jìn)行選擇、刪除等操作。

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

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

⑤Sstab多頁(yè)控件：實(shí)現(xiàn)單擊具體一頁(yè)，當(dāng)前顯示MshFlexGrid網(wǎng)格對(duì)應(yīng)的數(shù)據(jù)。

⑥CommandButton按鈕控件：添加“讀入數(shù)據(jù)”按鈕，單擊后實(shí)現(xiàn)具體動(dòng)作。

⑦Progressbar控件：寫(xiě)入數(shù)據(jù)時(shí)間較長(zhǎng)時(shí)，顯示寫(xiě)入進(jìn)度。

4 程序運(yùn)行

4.1 模擬實(shí)例

文獻(xiàn)[4]介紹了如何采用MATLAB 和LINDO軟件，進(jìn)行求解某釉料成本最低的最佳配方。其配方及所用原料化學(xué)組成見(jiàn)圖5，為驗(yàn)證本系統(tǒng)的可靠性，本文采用此配方進(jìn)行模擬運(yùn)行。

4.2 運(yùn)行VB程序，建立數(shù)據(jù)表格

現(xiàn)依照?qǐng)D5表格中所給條件，運(yùn)行本系統(tǒng)。步驟如下：

①進(jìn)入到主界面后，在“選擇產(chǎn)品”控件上選“釉料”。照條件所給，選擇各類(lèi)原料種數(shù)：長(zhǎng)石料、粘土料、石英料各1種，其他原料3種；單擊“讀入數(shù)據(jù)”按鈕，系統(tǒng)會(huì)將數(shù)據(jù)庫(kù)數(shù)據(jù)讀入。

②在“多頁(yè)控件”中的各個(gè)網(wǎng)格中依次選擇配方方案、原料種類(lèi)(為便于查看，在本數(shù)據(jù)庫(kù)中將圖5表格中的原料預(yù)設(shè)名稱為“范例”)。

③單擊“開(kāi)始優(yōu)化”按鈕，系統(tǒng)自動(dòng)打開(kāi)一個(gè)Excel文件：Book1。數(shù)據(jù)區(qū)域如圖6所示。

4.3 規(guī)劃求解

4.3.1 根據(jù)已知條件，建立LP數(shù)學(xué)模型：

圖5 某釉料配方及所用原料化學(xué)組成[4]Fig.5 The chemical composition of a glaze batch formula and raw materials

在本數(shù)學(xué)模擬計(jì)算中，按照工藝條件要求，設(shè)定氧化鋅含量2%，并根據(jù)經(jīng)驗(yàn)將Fe2O3、MgO舍去不計(jì)，規(guī)劃目標(biāo)取配方所用各原料的總價(jià)最小，根據(jù)本配方所用原料組成(見(jiàn)圖6)，本模擬對(duì)象的LP數(shù)學(xué)模型如下：

約束條件：

圖6 模擬計(jì)算數(shù)據(jù)結(jié)構(gòu)Fig.6 The structure of database in the simulated model

目標(biāo)函數(shù)(總價(jià))：

工藝要求：氧化鋅為0.02，X6=0.02；Fe2O3、MgO含量則舍去。

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

式中，X1、X2、X3、X4、X5、X6分別為所選用原料長(zhǎng)石、粘土、石英、白堊、鋯石英、氧化鋅的重量百分含量。

4.3.2 Excel規(guī)劃求解過(guò)程

在Excel界面，單擊“工具”—“規(guī)劃求解”，逐一填入條件(參見(jiàn)圖7)。

① 單元格表示：目標(biāo)為“總價(jià)”取最小值，在Excel表中雙擊“總價(jià)”數(shù)值所在單元格B15(參見(jiàn)圖8)，輸入“=SUM(B12*B14+C12*C14+D12* D14+E12*E14+F12*F14+G12*G14)”。因Excel表中14行為各原料的“優(yōu)化用量”，12行為原料對(duì)應(yīng)“單價(jià)”，輸入函數(shù)即表示“ ”

②決策變量的表示：?jiǎn)卧馚14~G14代表模型中的決策變量X1~X6的最優(yōu)解，該配方的價(jià)格需要計(jì)算機(jī)不斷的把X1~X6可能的數(shù)值代入到目標(biāo)函數(shù)計(jì)算。在“規(guī)劃求解”這些單元格稱為“可變單元格”。 B14~G14的值要滿足“>=0”

③ 約束的表示：為保證“實(shí)際配方”無(wú)限接近于“實(shí)際配方”，任一化學(xué)成分在兩者之間的差值應(yīng)該趨于零。即以單元格O3為例，O3對(duì)應(yīng)的值“>=0”且“<=”N3的值。

配方計(jì)算的方法要求，“優(yōu)化用量”總和接近1。為此，“約束值”所在值對(duì)應(yīng)單元格O14輸入“=SUM(B14:F14)”，當(dāng)O14的值越接近1，精度越高。

④決策變量限制表示。

在“實(shí)際配方”中，任一化學(xué)成分的百分?jǐn)?shù)，單元格O3輸入“=SUM(B3*$B$14+C3*$C$14+D 3*$D$14+E3*$E$14+F3*$F$14+G3*$G$14)”使用“$”實(shí)現(xiàn)絕對(duì)引用，下拉單元格是時(shí)，“$”后跟的地址不變，實(shí)現(xiàn)O3～O11公式自動(dòng)填充。

圖7 規(guī)劃求解參數(shù)Fig.7 The parameters for formula calculation

圖8 本系統(tǒng)運(yùn)行結(jié)果Fig.8 The Result of This Calculate

表1 文獻(xiàn)[4]成本最低最佳配料配方Tab.1 The best glaze batch formula at the lowest cost

在設(shè)計(jì)中, 如果最優(yōu)解的價(jià)格偏高,可以在允許范圍內(nèi)調(diào)整某些原料的用量, 如限制價(jià)格比較高的鋯英石和氧化鋅的用量, 增加價(jià)格比較低的原料用量, 如粘土和石英。

如果設(shè)置約束條件不能得到“最優(yōu)解”, 往往是因?yàn)樵O(shè)置的約束條件過(guò)分苛刻, 一般只要修改約束條件即可, 例如把最佳配方比之和的約束條件適當(dāng)設(shè)置在 0.9 與 1 之間一般就可以求到最優(yōu)解。

4.4 運(yùn)行結(jié)果與分析

圖8為本系統(tǒng)模擬的運(yùn)行結(jié)果，將它與文獻(xiàn)[4]所得優(yōu)化結(jié)果(見(jiàn)表1)比較，本系統(tǒng)優(yōu)化后配方的“總價(jià)”為821.009元，與文獻(xiàn)[4]提供的816.48-823.68范圍一致，各原料配比也與文獻(xiàn)[4]所得結(jié)果很接近，證明了本系統(tǒng)程序的可行性。

5 結(jié) 論

(1)Excel具備強(qiáng)大的規(guī)劃求解功能，用VB編程實(shí)現(xiàn)陶瓷配方的優(yōu)化是可行且較為簡(jiǎn)單。

(2)通過(guò)實(shí)例模擬運(yùn)行，優(yōu)化結(jié)果與實(shí)例很接近，說(shuō)明本文所開(kāi)發(fā)的陶瓷配方優(yōu)化系統(tǒng)對(duì)工藝科技人員研發(fā)陶瓷產(chǎn)品新配方有一定的指導(dǎo)作用。

(3)本系統(tǒng)界面清晰、美觀，用戶操作簡(jiǎn)便，“基于Excel的陶瓷配方優(yōu)化系統(tǒng)”有一定的實(shí)用價(jià)值。

[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] 高力明. 線性規(guī)劃在硅酸鹽工業(yè)配料計(jì)算中的應(yīng)用[J]. 玻璃與搪瓷, 1986, 4: 1-7, 13.

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

[3] 蔡英驥,馬鐵成, 王夢(mèng)林, 等. 陶瓷原料最優(yōu)化摻和計(jì)算機(jī)軟件的研制[J]. 中國(guó)陶瓷, 1994, 33(7): 41-44.

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

[4] 鄧美蘭, 孫國(guó)梁, 唐燕超, 等. 陶瓷配方設(shè)計(jì)的灰色優(yōu)化方法[J]. 中國(guó)陶瓷, 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