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

1、    excel規(guī)劃求解在陶瓷配方設(shè)計(jì)中的應(yīng)用    陳鵬程 劉建新摘 要：配方設(shè)計(jì)是陶瓷企業(yè)一項(xiàng)重要的綜合性技術(shù)工作，關(guān)乎生產(chǎn)的穩(wěn)定和陶瓷產(chǎn)品性能的優(yōu)劣。本文將excel辦公軟件中的規(guī)劃求解功能引入陶瓷配方設(shè)計(jì)中，通過(guò)原料種類和成分輸入、運(yùn)算關(guān)系確定、變量約束條件設(shè)置和目標(biāo)設(shè)置等步驟，可規(guī)劃優(yōu)選出符合約束條件和目標(biāo)的原料配比，免去人工配比試錯(cuò)的工作，具有重要的推廣價(jià)值。關(guān)鍵詞：規(guī)劃求解;配方;陶瓷1 概 述陶瓷配方設(shè)計(jì)是在陶瓷制品的化學(xué)成分組成體系下，針對(duì)產(chǎn)品的生產(chǎn)和性能訴求，在物理、化學(xué)原理和生產(chǎn)工藝要求的指導(dǎo)下，對(duì)原材料進(jìn)行配比計(jì)算以達(dá)到預(yù)期的產(chǎn)

2、品組成設(shè)計(jì)。對(duì)陶瓷企業(yè)來(lái)說(shuō)，產(chǎn)品的配方不僅是內(nèi)部的核心機(jī)密，配方的設(shè)計(jì)也是一項(xiàng)重要的綜合性技術(shù)工作。配方設(shè)計(jì)的好壞決定了生產(chǎn)的穩(wěn)定性和產(chǎn)品物理、化學(xué)性能的優(yōu)劣。在建筑陶瓷行業(yè)所用礦物原料品質(zhì)和穩(wěn)定性日益下降以及陶瓷企業(yè)新產(chǎn)品開(kāi)發(fā)力度逐漸加大的背景下，針對(duì)因不同批次原料波動(dòng)和新配方開(kāi)發(fā)而引入的配方調(diào)試工作也不斷增加。規(guī)劃求解是數(shù)學(xué)建模中的一項(xiàng)重要內(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ì)算機(jī)和辦公軟件普及化、平民化的背景

3、下，配方設(shè)計(jì)這種需要大量運(yùn)算和試錯(cuò)的工作正好符合規(guī)劃求解的特點(diǎn)。為此，本文將excel辦公軟件中的規(guī)劃求解功能引入陶瓷配方設(shè)計(jì)中，通過(guò)原料種類和成分輸入、運(yùn)算關(guān)系確定、變量約束條件設(shè)置和目標(biāo)設(shè)置等步驟，可規(guī)劃優(yōu)選出符合約束條件和目標(biāo)控制值的原料配比，加速配方開(kāi)發(fā)工作的進(jìn)程，具有重要的實(shí)用價(jià)值和推廣意義。2 實(shí)施步驟本實(shí)施例以建筑陶瓷坯體配方設(shè)計(jì)為例，按步驟介紹規(guī)劃求解在陶瓷配方設(shè)計(jì)中的應(yīng)用。2.1 軟件設(shè)置本文采用excel 2010版本為規(guī)劃求解的軟件工具。默認(rèn)情況下，軟件未在主界面展示出"規(guī)劃求解"功能按鈕，需要按步驟開(kāi)通：文件選項(xiàng)加載項(xiàng)“excel加載項(xiàng)”轉(zhuǎn)到勾選“規(guī)

4、劃求解加載項(xiàng)”，然后“規(guī)劃求解”功能按鈕即可在主界面“數(shù)據(jù)”選項(xiàng)卡下找到。2.2 原料種類成分輸入原料種類可以是企業(yè)陶瓷坯體原料庫(kù)中的所有可用原料。成分組成可包括常規(guī)“八大元素”氧化物含量和1000燒失量（已扣除原料含水率）。本例中，原料種類和成分在excel中輸入范例如圖1所示。在輸入的原料初始數(shù)據(jù)中，組成總量往往不是準(zhǔn)確的100，而且還包含了燒失量數(shù)據(jù)。而配方設(shè)計(jì)主要在于化學(xué)組成與預(yù)期的匹配，因此需要對(duì)圖1中的數(shù)據(jù)進(jìn)行轉(zhuǎn)換，去掉燒失量項(xiàng)目，并使各化學(xué)組成的相對(duì)含量總和為100。轉(zhuǎn)換后的數(shù)據(jù)如圖2所示，配比設(shè)計(jì)是在轉(zhuǎn)換后的數(shù)據(jù)基礎(chǔ)上進(jìn)行的。最終各配比的總和也應(yīng)為100，因此需要在“a23”單

5、元格設(shè)置各原料對(duì)應(yīng)配比的求和公式，方便后續(xù)設(shè)置約束條件。2.3 運(yùn)算關(guān)系確定當(dāng)各原料的配比所在單元格確定后，實(shí)際的配方，即化學(xué)組成應(yīng)當(dāng)是配比數(shù)值乘以相應(yīng)原料的各個(gè)化學(xué)成分含量，然后將得到的各原料中同一化學(xué)成分的含量相加。按此運(yùn)算關(guān)系在表格中設(shè)置相應(yīng)的計(jì)算公式，并填入目標(biāo)配方數(shù)值，如圖3所示。因圖2中各原料的配比初值為0，所以圖3中相應(yīng)的結(jié)果為0。此時(shí)以配比為變量，總計(jì)中各成分的含量為因變量的運(yùn)算關(guān)系已經(jīng)確定。2.4 變量約束條件設(shè)置本實(shí)例中，變量即為圖2所示的“a14：a22”單元格中的數(shù)值，這些數(shù)值需滿足以下約束條件：（1）大于等于0;（2）均為整數(shù);（3）總和為100。2.5 目標(biāo)設(shè)置目標(biāo)

6、的設(shè)置直接影響最后規(guī)劃求解的結(jié)果。比如以成本最低為目標(biāo)，那么在組合方案中當(dāng)成本為最小值時(shí)，規(guī)劃求解的運(yùn)算過(guò)程就會(huì)停止。而在本例中，目標(biāo)應(yīng)當(dāng)是圖3中“總計(jì)”的各數(shù)值與“目標(biāo)配方”中對(duì)應(yīng)數(shù)值的差別最小時(shí)，即得到最優(yōu)解。那么如何用公式來(lái)表示這種含多個(gè)單元格差別呢？根據(jù)最小二乘法的思想，有兩種方案可以采用：（1）將對(duì)應(yīng)單元格的差取絕對(duì)值，然后將各個(gè)絕對(duì)值求和;（2）將對(duì)應(yīng)單元格的差取平方，然后將各個(gè)平方值求和。為減少計(jì)算機(jī)的運(yùn)算負(fù)擔(dān)，本例中采用第1種方案較好。在陶瓷坯體配方設(shè)計(jì)中，fe2o3和tio2的含量均較少，而且對(duì)性能的影響可以忽略，所以這兩種成分不納入目標(biāo)考量。因此目標(biāo)設(shè)置方案就是在“c40”

7、單元格建立公式，對(duì)“總計(jì)”和“目標(biāo)配方”中對(duì)應(yīng)化學(xué)成分含量的差的絕對(duì)值求和。并在“規(guī)劃求解”設(shè)置界面中確定目標(biāo)單元格為“c40”到“最小值”，求解方法選擇“非線性grg”，如圖4所示。2.6 規(guī)劃求解參數(shù)設(shè)置完成后，點(diǎn)擊“求解”按鈕，程序即可自動(dòng)計(jì)算出符合約束條件和目標(biāo)的配方方案，結(jié)果如圖5所示（此時(shí)1#9#原料的配比分別為14， 7， 5， 8， 23， 2， 5， 5， 31）。從圖5中可以看出，al2o3，cao，mgo成分與目標(biāo)值還有較大差距，因此需要在目標(biāo)單元格的公式中作相應(yīng)的改進(jìn)：將“總計(jì)”和“目標(biāo)配方”中對(duì)這三種化學(xué)成分含量的差的絕對(duì)值乘以一個(gè)權(quán)重（分別乘以1.1，1.4和1.1

8、），再次進(jìn)行規(guī)劃求解計(jì)算。結(jié)果如圖6所示（此時(shí)1#9#原料的配比分別為0，1，0，37，37，3，5，0，17）。此時(shí)，實(shí)際配方與目標(biāo)配方中除fe2o3和tio2外，各成分含量與目標(biāo)配方的差別控制在0.1%左右，達(dá)到了較高的配方精度。得到該配比結(jié)果后，在實(shí)際生產(chǎn)中只需進(jìn)行一個(gè)原料含水率的轉(zhuǎn)換計(jì)算，即可得到生產(chǎn)配料稱重的數(shù)據(jù)，大大加速了配方設(shè)計(jì)的進(jìn)度。3 討 論從以上實(shí)例中可看出，將規(guī)劃求解用于陶瓷配方設(shè)計(jì)中可以完全代替人工試錯(cuò)的計(jì)算過(guò)程，方便、快速地達(dá)到目標(biāo)結(jié)果。生產(chǎn)中某些特殊原材料的用量限制也可以通過(guò)約束條件進(jìn)行設(shè)置，滿足多樣化的用料需求。當(dāng)計(jì)算機(jī)無(wú)法在短時(shí)間內(nèi)獲得最優(yōu)解時(shí)，則可以考慮對(duì)約束條件和目標(biāo)設(shè)置進(jìn)行調(diào)整后再進(jìn)行規(guī)劃求解計(jì)算。有些情況下，利用原料庫(kù)已有的原材料化學(xué)組成進(jìn)行多次規(guī)劃求解均無(wú)法滿足某些成分的含量要求時(shí)，則可以考慮采購(gòu)該種成分含量高的原材料。對(duì)于一些價(jià)格比較低的原料，可以在約束條