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

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

• 類型：辦公軟件
• 大小：2.2GB
• 語言：簡體中文
• 時間：2024-09-12

查看詳情

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

• 類型：辦公軟件
• 大?。?/span>280MB
• 語言：簡體中文
• 時間：2026-04-16

查看詳情

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

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

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

在Excel中有一個工具“單變量求解”（Excel的單變量求解，快速計算含公式的目標(biāo)值所對應(yīng)的變量?。?，它僅允許我們輸入一個變量，而無法實現(xiàn)多個變量的求解，因此我們在此例中使用“規(guī)劃求解”這一工具。

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

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

2. 打開“加載項”對話框后，勾選“規(guī)劃求解加載項”，點擊“確定”。

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

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

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

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

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

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

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

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

我們勾選“還原初值”，然后點擊“確定”。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

推薦閱讀：輕松搞定方程式的計算! Excel隱藏的強大工具使用攻略

最新評論