Excel的規(guī)劃求解工具擁有強(qiáng)大的功能，可以幫助我們解決線性規(guī)劃問題和運(yùn)籌學(xué)等實(shí)際問題。本系列經(jīng)驗(yàn)將以Excel2010為例詳細(xì)介紹規(guī)劃求解在各方面的應(yīng)用。上一篇經(jīng)驗(yàn)講解了如何使用“規(guī)劃求解”功能解決

Excel的規(guī)劃求解工具擁有強(qiáng)大的功能，可以幫助我們解決線性規(guī)劃問題和運(yùn)籌學(xué)等實(shí)際問題。本系列經(jīng)驗(yàn)將以Excel2010為例詳細(xì)介紹規(guī)劃求解在各方面的應(yīng)用。上一篇經(jīng)驗(yàn)講解了如何使用“規(guī)劃求解”功能解決高中數(shù)學(xué)中簡單的線性規(guī)劃問題。本篇經(jīng)驗(yàn)將從一個簡單的應(yīng)用題開始，介紹“線性求解”工具在實(shí)際問題中的運(yùn)用。

問題背景

某工廠要在A、B、C三條流水線上生產(chǎn)甲、乙兩種新產(chǎn)品。每生產(chǎn)一單位甲產(chǎn)品需要占用流水線A的1工時，占用流水線B的3工時；每生產(chǎn)一單位乙產(chǎn)品需要占用流水線B的2工時，占用流水線C的2工時。而流水線A、B、C每天可用于生產(chǎn)這兩種新產(chǎn)品的時間分別是4工時、18工時、12工時。已知一單位的甲產(chǎn)品的利潤為300元，一單位的乙產(chǎn)品的利潤為500元。問工廠應(yīng)當(dāng)如何安排這兩種新產(chǎn)品的生產(chǎn)計(jì)劃，以獲得最大的利潤？

建立模型

這個問題只有兩個變量和三個約束條件，可以較容易地列出約束條件，并通過在平面直角坐標(biāo)系中畫圖求出最大利潤點(diǎn)。對于變量和約束條件較多的問題，在使用“規(guī)劃求解”工具進(jìn)行計(jì)算時則更加困難。

首先我們設(shè)產(chǎn)品甲和乙的生產(chǎn)數(shù)量分別為x單位和y單位。要求的最大值（即每日總利潤）可表示為z300x 500y。接下來列出約束條件：

• 流水線A每日可用工時的限制：x ≤ 4 （1x 0y ≤ 4）
• 流水線B每日可用工時的限制：3x 2y ≤ 18
• 流水線C每日可用工時的限制：0x 2y ≤ 12

另外還有一個非負(fù)約束條件：x ≥ 0，y ≥ 0。

使用Excel進(jìn)行計(jì)算

在Excel中建立表格如圖所示，將已知條件、變量和目標(biāo)值分別用藍(lán)色、橙色和綠色填充，以便于理解。

在F3:F5區(qū)域輸入約束條件，即錄入約束條件中不等式左邊的內(nèi)容。以F3單元格為例，約束條件是x ≤ 4 （1x 0y ≤ 4），則在F3單元格內(nèi)輸入公式“SUMPRODUCT(D3:E3,D7:E7)”。

對于F4和F5單元格，可以使用F3單元格直接向下拖動填充。但是需要注意的是，D7:E7區(qū)域要添加絕對引用，避免拖動填充時這個區(qū)域隨之變動。

還需輸入目標(biāo)值H9單元格的公式。對于目標(biāo)函數(shù)z300x 500y，需要在H9單元格輸入部分“300x 500y”。D9和E9單元格分別為“300”和“500”，x和y的值分別在D7和D9單元格。同樣使用SUMPRODUCT函數(shù)，在H9單元格輸入公式“SUMPRODUCT(D7:E7,D9:E9)”。

運(yùn)行“規(guī)劃求解”工具，選擇數(shù)據(jù)后點(diǎn)擊“求解”按鈕。計(jì)算完成后，最后得到變量x和y的值，以及最大利潤。

根據(jù)計(jì)算結(jié)果可知，產(chǎn)品甲和乙每日分別生產(chǎn)2單位和6單位，可以獲得最大利潤3600元。

希望本文對大家有所幫助！