Excel規(guī)劃求解在數(shù)學(xué)建模中的應(yīng)用

丁婧琦+李靜+丁亞軍

摘要：該文介紹了數(shù)學(xué)建模的步驟和線性規(guī)劃求解手工計(jì)算的過(guò)程。當(dāng)模型復(fù)雜、變量和約束條件較多時(shí)，計(jì)算工作量較大，且更容易出錯(cuò)。該文通過(guò)實(shí)例探討，應(yīng)用Excel的規(guī)劃求解功能可直觀、簡(jiǎn)捷地解決線性規(guī)劃、定值和方程組問(wèn)題等的求解。

關(guān)鍵詞：數(shù)學(xué)建模；Excel；規(guī)劃求解

中圖分類號(hào)：TP393 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1009-3044（2017）17-0092-02

高中數(shù)學(xué)必修5教科書中介紹了簡(jiǎn)單線性規(guī)劃問(wèn)題。線性規(guī)劃能解決生產(chǎn)成本控制、產(chǎn)品利潤(rùn)最大化、運(yùn)輸線路最佳選擇、經(jīng)濟(jì)管理等方面的實(shí)際問(wèn)題。解決問(wèn)題的根本在于建立相應(yīng)的數(shù)學(xué)模型，其步驟為：

①根據(jù)問(wèn)題所要達(dá)到的目標(biāo)，確定決策變量。

②由決策變量和所要達(dá)到目標(biāo)之間的函數(shù)關(guān)系確定目標(biāo)函數(shù)。

③由決策變量所受的限制條件確定決策變量中所要滿足的約束條件。

教科書中通過(guò)具體實(shí)例介紹了線性規(guī)劃問(wèn)題的圖解法，線性規(guī)劃求解的解題思路可歸納為畫、移、求、答，具體過(guò)程為：①畫：畫出約束條件（不等式組）所確定的平面區(qū)域和目標(biāo)函數(shù)所表示的平行直線系中的任意一條直線。②移：將直線平行移動(dòng)，以確定最優(yōu)解所對(duì)應(yīng)的點(diǎn)的位置。③求：通過(guò)解方程組求出最優(yōu)坐標(biāo)，代人目標(biāo)函數(shù)求出最優(yōu)解。④答：作出答案。

學(xué)校要求我們進(jìn)行研究性學(xué)習(xí)，運(yùn)用所學(xué)線性規(guī)劃知識(shí)解決一些實(shí)際問(wèn)題。在實(shí)際問(wèn)題解決過(guò)程中，計(jì)算過(guò)程繁瑣、計(jì)算量較大。作者對(duì)人教版數(shù)學(xué)必修5中所介紹的Excel規(guī)劃求解進(jìn)行研究學(xué)習(xí)，發(fā)現(xiàn)其不僅能解決線性規(guī)劃問(wèn)題，還能解決非線性規(guī)劃、定值問(wèn)題和方程組求解問(wèn)題等。

1 Excel規(guī)劃求解簡(jiǎn)介

“規(guī)劃求解”是Excel規(guī)劃求解加載宏的簡(jiǎn)稱。借助“規(guī)劃求解”可對(duì)直接或間接目標(biāo)單元格中公式相關(guān)聯(lián)的一組單元格中的數(shù)值進(jìn)行調(diào)整，最終在目標(biāo)單元格公式中求得期望的結(jié)果。在模型求解過(guò)程中，可以對(duì)“規(guī)劃求解”中的可變單元格數(shù)值應(yīng)用約束條件，約束條件可通過(guò)“規(guī)劃求解”中的限制條件來(lái)設(shè)置，約束條件可以引用其他影響目標(biāo)單元格公式的單元格。使用“規(guī)劃求解”可通過(guò)更改其他單元格來(lái)確定某個(gè)單元格的最大值或最小值。規(guī)劃求解可以用來(lái)解決線性規(guī)劃與非線性規(guī)劃優(yōu)化問(wèn)題。

2 Excel規(guī)劃求解操作步驟

利用Excel規(guī)劃求解分析工具求解問(wèn)題的步驟如下：

①建立問(wèn)題的數(shù)學(xué)模型。

②根據(jù)數(shù)學(xué)模型規(guī)劃建立Excel工作表。

③設(shè)置規(guī)劃求解參數(shù)。參數(shù)包括：設(shè)置可變單元格（確定決策變量）；設(shè)置目標(biāo)單元格（建立目標(biāo)函數(shù)）；設(shè)置約束條件（約束條件可以用線性等式或不等式表示，還有非負(fù)約束（≥）和整數(shù)約束（int））。

④單擊“求解”按鈕，進(jìn)行求解。

3 Excel規(guī)劃求解實(shí)例

3.1定值問(wèn)題求解

通程商場(chǎng)國(guó)慶節(jié)搞購(gòu)物滿1000元送1000元購(gòu)物券的歡樂(lè)購(gòu)活動(dòng)，小麗看中了商品A（17元），商品B（28元），商品C（53元），商品D（65元），想選這些商品各若干件（>=3）剛好拼湊1000元以便領(lǐng)取1000元購(gòu)物券，琢磨了很長(zhǎng)時(shí)間，沒(méi)有成功?，F(xiàn)在我們通過(guò)計(jì)算機(jī)來(lái)幫助小麗解決其煩惱，其過(guò)程如下：

①先建立數(shù)學(xué)模型（17*a+28*b+53*c+65*d=1000），在Ex-cel工作表中建立如圖1所示的表格。

②在D2單元格中輸入公式“=B2*C2+B3*C3+B4*C4+B5*C5”（將C2：C5單元格作為未知數(shù)a，b，e，d）。

③在“數(shù)據(jù)”選項(xiàng)卡中單擊“規(guī)劃求解”選項(xiàng)，打開如圖2所示的對(duì)話框。在對(duì)話框中設(shè)置目標(biāo)為$D$2，單擊目標(biāo)值，在文本框內(nèi)輸入1000。在可變單元格中輸入$C$2：$C$5，單擊“添加”按鈕，添加兩個(gè)約束條件：$C$2：$C$5=整數(shù)，$C$2：$C$5>=3。

④單擊“求解”按鈕，即可得到如圖3所示的結(jié)果。

3.2利潤(rùn)最大化問(wèn)題

某工廠想生產(chǎn)拉蓋式書桌和普通式書桌各若干張，每張拉蓋式書桌需用松木10根、雪松4根、楓木15根，普通式書桌需用松木20根、雪松16根、楓木10根?，F(xiàn)有松木200根、雪松128根、楓木220根，每張拉蓋式書桌利潤(rùn)為115元，每張普通式書桌的利潤(rùn)為90元，問(wèn)怎樣生產(chǎn)才能使得工廠的利潤(rùn)最大？

分析：建立數(shù)學(xué)模型。設(shè)生產(chǎn)拉蓋式書桌x臺(tái)，普通式書桌y臺(tái)，總利潤(rùn)為Z元。目標(biāo)函數(shù)為：z_max=115x+90y。

求解過(guò)程如下：

①根據(jù)數(shù)學(xué)模型在Excel工作表中建立如圖4所示的表格，B2、C2單元格為兩種書桌生產(chǎn)數(shù)量。

②分別在E2、E3、E4單元格中輸入公式“=B2*B6+C2"C6”、“=B3*B6+C3*C6”、“=B4*B6+C4*C6”，在B7單元格中輸入目標(biāo)函數(shù)公式"=B5*B6+C5*C6”。

③在“數(shù)據(jù)”選項(xiàng)卡中單擊“規(guī)劃求解”選項(xiàng)，打開規(guī)劃求解參數(shù)對(duì)話框。在對(duì)話框中設(shè)置目標(biāo)為$B$7，選擇最大值，將可變單元格設(shè)置為$B$6：$C$6，添加約束條件：$E$2<=$D$2，$E$3<=$D$3，$E$4<=$D$4。

④單擊“求解“按鈕，即可得到如圖5所示的結(jié)果。

3.3方程組求解問(wèn)題

利用Excel規(guī)劃求解也可以解方程組。下面以"Excel規(guī)劃求解”解方程組為例來(lái)介紹其操作過(guò)程。

過(guò)程如下：

①根據(jù)方程組規(guī)劃Excel工作表如圖6所示，A2：A4單元格對(duì)應(yīng)變量x、y，C2：C4單元格對(duì)應(yīng)于三個(gè)方程的值。

②分別在C2：C4單元格中輸入公式“=A2+2*A3-4*A4”、“=5*A2-A3-3*A4”、“=6*A2+A3-A4”。

③在“數(shù)據(jù)”選項(xiàng)卡中單擊“規(guī)劃求解”選項(xiàng)，打開規(guī)劃求解參數(shù)對(duì)話框。在對(duì)話框中設(shè)置目標(biāo)為$C$2，選擇目標(biāo)值，在文本框內(nèi)輸入9，將可變單元格設(shè)置為SA$2：$A$4，添加約束條件：$C$3=7，$C$4=22。

④單擊“求解”按鈕，即可得到如圖7所示的結(jié)果。

4結(jié)束語(yǔ)

通過(guò)本次研究性學(xué)習(xí)，了解了Excel規(guī)劃求解不僅能解決線性規(guī)劃問(wèn)題，還能解決非線性規(guī)劃、定值問(wèn)題和方程組求解問(wèn)題等。在探究實(shí)驗(yàn)過(guò)程中，充分體驗(yàn)了“從實(shí)際問(wèn)題到數(shù)學(xué)問(wèn)題”的建構(gòu)過(guò)程，“從具體到一般”的抽象思維過(guò)程。學(xué)會(huì)了分析問(wèn)題方法，提高了利用計(jì)算機(jī)解決實(shí)際問(wèn)題的能力。體驗(yàn)了數(shù)學(xué)來(lái)源于生活，服務(wù)于生活，體驗(yàn)了數(shù)學(xué)、計(jì)算機(jī)在生活、生產(chǎn)中的作用。