本期我們來(lái)了解一下Excel的“規(guī)劃求解”，這是一個(gè)相對(duì)較復(fù)雜的分析工具，但是在對(duì)數(shù)據(jù)的預(yù)測(cè)分析上，可謂功能強(qiáng)大，不僅可以針對(duì)多個(gè)變量進(jìn)行方案的設(shè)計(jì)，同時(shí)在此工具中整合了“方案管理”的功能，為我們展示不同的方案以便進(jìn)行對(duì)比，尋求符合工作實(shí)際需求的方案。

微軟Office LTSC 2021專業(yè)增強(qiáng)版 簡(jiǎn)體中文批量許可版 2024年09月更新

• 類型：辦公軟件
• 大?。?/span>2.2GB
• 語(yǔ)言：簡(jiǎn)體中文
• 時(shí)間：2024-09-12

查看詳情

WPS Office 2025 V12.1.0.24657 官方最新正式版 32位/64位

• 類型：辦公軟件
• 大?。?/span>273MB
• 語(yǔ)言：簡(jiǎn)體中文
• 時(shí)間：2026-01-13

查看詳情

我們以如圖所示的項(xiàng)目數(shù)據(jù)為例。

所期望的需求是：當(dāng)B5單元格中的“Manuals”為12，并且E7單元格的“duration”為20時(shí)，是否E20單元格中的“Profit”仍能達(dá)到一個(gè)比較理想的結(jié)果。

當(dāng)修改B5單元格中的數(shù)據(jù)為12后，E7單元格的數(shù)據(jù)變?yōu)?0，要使其變成20，我們可以嘗試對(duì)B7至B9單元格中的“staff”、“Working day”和“Working week”進(jìn)行修改。

在Excel中有一個(gè)工具“單變量求解”（Excel的單變量求解，快速計(jì)算含公式的目標(biāo)值所對(duì)應(yīng)的變量?。?，它僅允許我們輸入一個(gè)變量，而無(wú)法實(shí)現(xiàn)多個(gè)變量的求解，因此我們?cè)诖死惺褂?ldquo;規(guī)劃求解”這一工具。

點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡，在“分析”區(qū)域，點(diǎn)擊“規(guī)劃求解”。若在選項(xiàng)卡區(qū)域沒有此工具，可以通過(guò)以下方式來(lái)添加。

1. 打開“Excel選項(xiàng)”對(duì)話框，點(diǎn)擊“加載項(xiàng)”，再點(diǎn)擊管理Excel加載項(xiàng)的“轉(zhuǎn)到”。

2. 打開“加載項(xiàng)”對(duì)話框后，勾選“規(guī)劃求解加載項(xiàng)”，點(diǎn)擊“確定”。

添加好“規(guī)劃求解”工具后，點(diǎn)擊此工具按鈕，打開其對(duì)話框。

首先“設(shè)置目標(biāo)”，選擇E7單元格，即對(duì)應(yīng)的“duration”數(shù)據(jù)。

其次，設(shè)置目標(biāo)值為20。在此我們有三種選擇：最大值、最小值和目標(biāo)值，可根據(jù)實(shí)際需要進(jìn)行設(shè)置。

再次，選擇“通過(guò)更改可變單元格”，即我們之前提到的多個(gè)變量。此例中我們選擇的B7至B9單元格，如果變量是不相鄰的單元格，可以通過(guò)按住Ctrl鍵來(lái)添加或逗號(hào)隔開。

點(diǎn)擊“求解”后，會(huì)轉(zhuǎn)到另一個(gè)對(duì)話框“規(guī)劃求解結(jié)果”，或者說(shuō)得到一個(gè)方案。

然而，我們看到此方案下雖然“duration”目標(biāo)值達(dá)到20，并且通過(guò)調(diào)整B7至B9單元格中的數(shù)據(jù)來(lái)達(dá)到此目標(biāo)，但“Projected Profit”并不理想。

盡管我們不會(huì)采納此方案，但我們?nèi)韵Ｍ軐⑵浔４妫虼水?dāng)我們點(diǎn)擊“保存方案”時(shí)，會(huì)跳轉(zhuǎn)到另一個(gè)對(duì)話框，要求我們?yōu)榇朔桨该?，我們將其命名?ldquo;20 Weeks”，即此方案是“duration”為20的情況。

點(diǎn)擊“確定”后，回到“規(guī)劃求解結(jié)果”對(duì)話框，在此我們有多種可選操作：“保留規(guī)劃求解的解”、“還原初值”、“返回規(guī)劃求解對(duì)話框”、“制作報(bào)告大綱”等。

我們勾選“還原初值”，然后點(diǎn)擊“確定”。

為了方便之后的數(shù)據(jù)對(duì)比，我們將初值的方案添加到“方案管理器”（Excel的方案管理器，快速查看多種方案下的數(shù)據(jù)變化！）中，即“duration”為30的情況。

我們繼續(xù)通過(guò)“規(guī)劃求解”來(lái)找到更加合適的方案，但這次我們有一些對(duì)變量的約束，即“staff”不超過(guò)12，“Working day”不超過(guò)8，“Working week”不超過(guò)6。

點(diǎn)擊“規(guī)劃求解”中的“添加”按鈕。

在“添加約束”對(duì)話框中，“單元格引用”為B7單元格，選擇小于等于（<=），“約束”為12。

以此類推，繼續(xù)添加另外兩個(gè)變量的約束?；氐?ldquo;規(guī)劃求解”對(duì)話框后，我們?cè)?ldquo;遵守約束”框中可看到已添加的變量約束。

點(diǎn)擊“求解”后，我們看到“duration”為21，三個(gè)變量也分別進(jìn)行了相應(yīng)的調(diào)整，但是“Projected Profit”仍不理想。

保存當(dāng)前的方案，并命名為“21 Weeks”。

回到“規(guī)劃求解結(jié)果”對(duì)話框中，勾選“還原初值”和“返回規(guī)劃求解對(duì)話框”。

這一次我們將改變“設(shè)置目標(biāo)”，將其設(shè)為“Projected Profit”，并且計(jì)算出其為“最大值”的方案。

點(diǎn)擊“求解”后，我們看到“Projected Profit”為所能達(dá)到的最大值，在當(dāng)前三個(gè)變量被約束的情況下，然而“duration”則變成了42，相當(dāng)于上一個(gè)方案的兩倍。

保存當(dāng)前的方案并命名為“42 Weeks”。

回到“規(guī)劃求解結(jié)果”對(duì)話框中，我們這次“保留規(guī)劃求解的解”，點(diǎn)擊“確定”。

我們最后再次點(diǎn)擊“方案管理器”，通過(guò)“摘要”來(lái)展示不同的方案所得到的結(jié)果。

新生成的“方案摘要”工作表如下。

以上即是我們本期的主要內(nèi)容——規(guī)劃求解，要想熟練使用好此工具，還得應(yīng)用到實(shí)際工作的案例中去，多多練習(xí)。

推薦閱讀：輕松搞定方程式的計(jì)算! Excel隱藏的強(qiáng)大工具使用攻略