Excel規(guī)劃求解在陶瓷配方設(shè)計中的應(yīng)用

陳鵬程 劉建新

摘 要：配方設(shè)計是陶瓷企業(yè)一項重要的綜合性技術(shù)工作，關(guān)乎生產(chǎn)的穩(wěn)定和陶瓷產(chǎn)品性能的優(yōu)劣。本文將Excel辦公軟件中的規(guī)劃求解功能引入陶瓷配方設(shè)計中，通過原料種類和成分輸入、運算關(guān)系確定、變量約束條件設(shè)置和目標(biāo)設(shè)置等步驟，可規(guī)劃優(yōu)選出符合約束條件和目標(biāo)的原料配比，免去人工配比試錯的工作，具有重要的推廣價值。

關(guān)鍵詞：規(guī)劃求解;配方;陶瓷

1 概 述

陶瓷配方設(shè)計是在陶瓷制品的化學(xué)成分組成體系下，針對產(chǎn)品的生產(chǎn)和性能訴求，在物理、化學(xué)原理和生產(chǎn)工藝要求的指導(dǎo)下，對原材料進(jìn)行配比計算以達(dá)到預(yù)期的產(chǎn)品組成設(shè)計。對陶瓷企業(yè)來說，產(chǎn)品的配方不僅是內(nèi)部的核心機(jī)密，配方的設(shè)計也是一項重要的綜合性技術(shù)工作。配方設(shè)計的好壞決定了生產(chǎn)的穩(wěn)定性和產(chǎn)品物理、化學(xué)性能的優(yōu)劣。在建筑陶瓷行業(yè)所用礦物原料品質(zhì)和穩(wěn)定性日益下降以及陶瓷企業(yè)新產(chǎn)品開發(fā)力度逐漸加大的背景下，針對因不同批次原料波動和新配方開發(fā)而引入的配方調(diào)試工作也不斷增加。

規(guī)劃求解是數(shù)學(xué)建模中的一項重要內(nèi)容，其原理是在符合約束條件的要求下不斷調(diào)整可變量，從而改變與可變量鏈接的其它數(shù)據(jù)，進(jìn)一步影響目標(biāo)值，最終使目標(biāo)值達(dá)到設(shè)置的控制值，即為達(dá)到最優(yōu)解。目標(biāo)控制值可以是使產(chǎn)品成本達(dá)到最低，也可以是使化學(xué)組成與預(yù)期最接近。

在計算機(jī)和辦公軟件普及化、平民化的背景下，配方設(shè)計這種需要大量運算和試錯的工作正好符合規(guī)劃求解的特點。為此，本文將Excel辦公軟件中的規(guī)劃求解功能引入陶瓷配方設(shè)計中，通過原料種類和成分輸入、運算關(guān)系確定、變量約束條件設(shè)置和目標(biāo)設(shè)置等步驟，可規(guī)劃優(yōu)選出符合約束條件和目標(biāo)控制值的原料配比，加速配方開發(fā)工作的進(jìn)程，具有重要的實用價值和推廣意義。

2 實施步驟

本實施例以建筑陶瓷坯體配方設(shè)計為例，按步驟介紹規(guī)劃求解在陶瓷配方設(shè)計中的應(yīng)用。

2.1 軟件設(shè)置

本文采用Excel 2010版本為規(guī)劃求解的軟件工具。默認(rèn)情況下，軟件未在主界面展示出"規(guī)劃求解"功能按鈕，需要按步驟開通：文件→選項→加載項→“Excel加載項”轉(zhuǎn)到→勾選“規(guī)劃求解加載項”，然后“規(guī)劃求解”功能按鈕即可在主界面“數(shù)據(jù)”選項卡下找到。

2.2 原料種類成分輸入

原料種類可以是企業(yè)陶瓷坯體原料庫中的所有可用原料。成分組成可包括常規(guī)“八大元素”氧化物含量和1000℃燒失量（已扣除原料含水率）。本例中，原料種類和成分在Excel中輸入范例如圖1所示。

在輸入的原料初始數(shù)據(jù)中，組成總量往往不是準(zhǔn)確的100，而且還包含了燒失量數(shù)據(jù)。而配方設(shè)計主要在于化學(xué)組成與預(yù)期的匹配，因此需要對圖1中的數(shù)據(jù)進(jìn)行轉(zhuǎn)換，去掉燒失量項目，并使各化學(xué)組成的相對含量總和為100。轉(zhuǎn)換后的數(shù)據(jù)如圖2所示，配比設(shè)計是在轉(zhuǎn)換后的數(shù)據(jù)基礎(chǔ)上進(jìn)行的。

最終各配比的總和也應(yīng)為100，因此需要在“A23”單元格設(shè)置各原料對應(yīng)配比的求和公式，方便后續(xù)設(shè)置約束條件。

2.3 運算關(guān)系確定

當(dāng)各原料的配比所在單元格確定后，實際的配方，即化學(xué)組成應(yīng)當(dāng)是配比數(shù)值乘以相應(yīng)原料的各個化學(xué)成分含量，然后將得到的各原料中同一化學(xué)成分的含量相加。按此運算關(guān)系在表格中設(shè)置相應(yīng)的計算公式，并填入目標(biāo)配方數(shù)值，如圖3所示。

因圖2中各原料的配比初值為0，所以圖3中相應(yīng)的結(jié)果為0。此時以配比為變量，總計中各成分的含量為因變量的運算關(guān)系已經(jīng)確定。

2.4 變量約束條件設(shè)置

本實例中，變量即為圖2所示的“A14：A22”單元格中的數(shù)值，這些數(shù)值需滿足以下約束條件：

（1）大于等于0;

（2）均為整數(shù);

（3）總和為100。

2.5 目標(biāo)設(shè)置

目標(biāo)的設(shè)置直接影響最后規(guī)劃求解的結(jié)果。比如以成本最低為目標(biāo)，那么在組合方案中當(dāng)成本為最小值時，規(guī)劃求解的運算過程就會停止。而在本例中，目標(biāo)應(yīng)當(dāng)是圖3中“總計”的各數(shù)值與“目標(biāo)配方”中對應(yīng)數(shù)值的差別最小時，即得到最優(yōu)解。那么如何用公式來表示這種含多個單元格差別呢？根據(jù)最小二乘法的思想，有兩種方案可以采用：

（1）將對應(yīng)單元格的差取絕對值，然后將各個絕對值求和;

（2）將對應(yīng)單元格的差取平方，然后將各個平方值求和。

為減少計算機(jī)的運算負(fù)擔(dān)，本例中采用第1種方案較好。在陶瓷坯體配方設(shè)計中，F(xiàn)e2O3和TiO2的含量均較少，而且對性能的影響可以忽略，所以這兩種成分不納入目標(biāo)考量。因此目標(biāo)設(shè)置方案就是在“C40”單元格建立公式，對“總計”和“目標(biāo)配方”中對應(yīng)化學(xué)成分含量的差的絕對值求和。并在“規(guī)劃求解”設(shè)置界面中確定目標(biāo)單元格為“C40”到“最小值”，求解方法選擇“非線性GRG”，如圖4所示。

2.6 規(guī)劃求解

參數(shù)設(shè)置完成后，點擊“求解”按鈕，程序即可自動計算出符合約束條件和目標(biāo)的配方方案，結(jié)果如圖5所示（此時1#～9#原料的配比分別為14， 7， 5， 8， 23， 2， 5， 5， 31）。

從圖5中可以看出，Al2O3，CaO，MgO成分與目標(biāo)值還有較大差距，因此需要在目標(biāo)單元格的公式中作相應(yīng)的改進(jìn)：將“總計”和“目標(biāo)配方”中對這三種化學(xué)成分含量的差的絕對值乘以一個權(quán)重（分別乘以1.1，1.4和1.1），再次進(jìn)行規(guī)劃求解計算。結(jié)果如圖6所示（此時1#～9#原料的配比分別為0，1，0，37，37，3，5，0，17）。

此時，實際配方與目標(biāo)配方中除Fe2O3和TiO2外，各成分含量與目標(biāo)配方的差別控制在0.1%左右，達(dá)到了較高的配方精度。得到該配比結(jié)果后，在實際生產(chǎn)中只需進(jìn)行一個原料含水率的轉(zhuǎn)換計算，即可得到生產(chǎn)配料稱重的數(shù)據(jù)，大大加速了配方設(shè)計的進(jìn)度。

3 討 論

從以上實例中可看出，將規(guī)劃求解用于陶瓷配方設(shè)計中可以完全代替人工試錯的計算過程，方便、快速地達(dá)到目標(biāo)結(jié)果。生產(chǎn)中某些特殊原材料的用量限制也可以通過約束條件進(jìn)行設(shè)置，滿足多樣化的用料需求。當(dāng)計算機(jī)無法在短時間內(nèi)獲得最優(yōu)解時，則可以考慮對約束條件和目標(biāo)設(shè)置進(jìn)行調(diào)整后再進(jìn)行規(guī)劃求解計算。有些情況下，利用原料庫已有的原材料化學(xué)組成進(jìn)行多次規(guī)劃求解均無法滿足某些成分的含量要求時，則可以考慮采購該種成分含量高的原材料。對于一些價格比較低的原料，可以在約束條件中設(shè)置其配比的范圍，避免因規(guī)劃求解目標(biāo)的唯一導(dǎo)向性，使最優(yōu)解中該原料的配比較少。

綜上，規(guī)劃求解在陶瓷配方設(shè)計中的廣泛應(yīng)用，不僅能夠快速得到符合要求的目標(biāo)配方，而且對于原材料采購、低品質(zhì)原料的應(yīng)用、降低產(chǎn)品成本等方面均具有現(xiàn)實的指導(dǎo)意義。