劉 明，黃中文

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

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

劉 明，黃中文

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

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

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

0 引 言

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

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

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

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

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

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

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

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

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

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

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

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

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

3 程序設(shè)計

3.1 設(shè)計思路

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

3.2 主界面設(shè)計

主界面的設(shè)計要方便用戶對陶瓷坯釉料方案和各類原料的選擇，并能對選取的數(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個區(qū)域，分別為參數(shù)輸入?yún)^(qū)域和參數(shù)輸出區(qū)域。

③ComboBox下拉框控件：單擊后，在下拉框中選擇各類原料的種類數(shù)目。

④MshFlexGrid網(wǎng)格控件：建立多個此控件，分別寫入數(shù)據(jù)庫中包含的：配方、長石料、粘土料、石英料、其他原料數(shù)據(jù)記錄；同時實(shí)現(xiàn)單擊某條數(shù)據(jù)，能對此條數(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多頁控件：實(shí)現(xiàn)單擊具體一頁，當(dāng)前顯示MshFlexGrid網(wǎng)格對應(yīng)的數(shù)據(jù)。

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

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

4 程序運(yùn)行

4.1 模擬實(shí)例

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

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

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

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

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

③單擊“開始優(yōu)化”按鈕，系統(tǒng)自動打開一個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é)模擬計算中，按照工藝條件要求，設(shè)定氧化鋅含量2%，并根據(jù)經(jīng)驗將Fe2O3、MgO舍去不計，規(guī)劃目標(biāo)取配方所用各原料的總價最小，根據(jù)本配方所用原料組成(見圖6)，本模擬對象的LP數(shù)學(xué)模型如下：

約束條件：

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

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

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

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

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

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

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

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

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

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

配方計算的方法要求，“優(yōu)化用量”總和接近1。為此，“約束值”所在值對應(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)絕對引用，下拉單元格是時，“$”后跟的地址不變，實(shí)現(xiàn)O3～O11公式自動填充。

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

如果設(shè)置約束條件不能得到“最優(yōu)解”, 往往是因為設(shè)置的約束條件過分苛刻, 一般只要修改約束條件即可, 例如把最佳配方比之和的約束條件適當(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é)果(見表1)比較，本系統(tǒng)優(yōu)化后配方的“總價”為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)化是可行且較為簡單。

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

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

[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è)配料計算中的應(yīng)用[J]. 玻璃與搪瓷, 1986, 4: 1-7, 13.

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

[3] 蔡英驥,馬鐵成, 王夢林, 等. 陶瓷原料最優(yōu)化摻和計算機(jī)軟件的研制[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] 鄧美蘭, 孫國梁, 唐燕超, 等. 陶瓷配方設(shè)計的灰色優(yōu)化方法[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