如何自動化 Excel 求解器

已發表: 2021-10-23

在如何充分利用 Excel 求解器中，我介紹了增強模型和增強分析能力的技術。我收到最多問題的主題之一是自動求解器來求解多個值。

上一篇有簡單的說明，但是沒有專門講如何從頭到尾實現。在本文中，我們將從頭到尾深入介紹概念和方法。最後，您將能夠自動化您自己的任何個人模型並擴展您的分析。

我們將如何做

我們將利用 Excel 中的宏記錄器來處理大部分實際代碼。記錄宏工具將允許 Excel 將我們的手動步驟轉換為 VBA 代碼。

然後，我們將確定要更改的關鍵代碼段。然後我們將創建一個循環來動態選擇和替換這些值並再次運行求解器。

這將是半技術性的，但通過這種開發風格，我們可以專注於過程和概念，而不是專注於如何編寫代碼。

設置宏記錄器

宏記錄功能位於功能區的開發人員選項卡中。這在默認情況下不可用，但很容易實現。如果您導航到功能區選項，您將看到開發人員選項卡的複選框。

下面的屏幕截圖適用於 Mac，但基於 PC 的 Excel 是類似的。

如何讓開發人員使用 excel 功能區

如果您尚未安裝求解器插件，您可以通過插件菜單安裝它。 Microsoft 支持站點提供適用於所有平台的說明，在 Excel 中加載求解器加載項。

錄製宏

設置完成後，我們就可以開始錄製了！使用所有公式和參考適當地設置您的工作區。在構建求解器模型之前，您需要將所有內容設置到要點。

返回功能區上的開發人員選項卡。您會看到一張帶有紅點的紙。單擊該按鈕以打開記錄器。

在excel中記錄宏按鈕

為您的宏命名並根據需要填寫說明。

命名你的 excel 宏

一旦你點擊確定，錄音機就會開始。

完成設置求解器的步驟並求解。完成後，返回開發人員選項卡並停止錄製。它與您用於開始錄製的按鈕位置相同。

停止在 excel 位置錄製宏

現在我們有了基本代碼，我們可以清理它並為下一步做準備。

清理代碼

現在我們可以編輯代碼，刪除任何不必要的部分，並確保我們只使用宏錄製的基本部分。

您可以通過 Excel 的 VBA 編輯器編輯代碼。要訪問您錄製的代碼，請單擊宏按鈕。

如何在excel中查看宏

在新菜單中導航到您的宏，然後單擊編輯。

在excel中編輯宏代碼的位置

現在我們打開了代碼編輯器，我們可以開始進行調整。

刪除不必要的代碼

如果您在錄製時碰巧點擊了一堆其他東西，您現在可以清除它們，並留下類似於下面的內容。如果有很多額外的代碼並且您擔心破壞它，您可以隨時重新錄製。

錄製後的示例求解器 VBA 代碼

設置求解器參考

在我們繼續之前的最後一步，轉到“工具”>“參考”並選擇“求解器”。如果你不這樣做，包將不會加載到宏中，你會得到一個錯誤。

您現在可以更改參數，打開宏菜單，選擇您的宏，點擊運行，它將更新結果。

讓我們根據求解器模型排列代碼。你會看到它是如何排列的。語言是不同的，但你可以拼湊出它的全部含義。

比較求解器參數與宏代碼

接下來，我們將隔離需要更改的部分並結束我們的自動化。

沖洗並重複——建立循環

現在我們有了一個有效的求解器。我們需要多次重複。讓我們一起舉一個例子並鞏固計劃。

我們在第一個模型中以 1,000 美元的預算開始，但我們希望以 100 美元至 2,500 美元的增量做同樣的事情。

為宏示例設置表

讓我們潛入吧！我們在 B 列中有一個值列表，在 C 列中有我們的輸出。如果我們寫出這個過程，它看起來像，

將總支出設置為 B10 中的值。
求解模型。
將輸出放在 C10 中。
選擇下一個 B 值。
在 C 行設置輸出。
對 B 中的所有值重複步驟 2-5。

開始循環

循環是重複操作直到達到邏輯終點的編碼結構。一個循環在這裡會派上用場，重複相同的過程，在每個值上運行求解器，直到我們到達該值的末尾。

對於本示例，我們將通過行數定義終點。 B10:B25 有 16 個值。

我們將開始我們的循環並將其包裹在我們的求解器代碼中。我們將設置一個變量來保存循環編號 (i) 並遍歷每個值，每次求解循環。

啟動excel宏的循環代碼

重置求解器

我們將在代碼的開頭添加另一個條件。由於我們希望每次迭代都重新運行求解器，因此我們將在每個循環中重置它。這將清除設置並啟動新模型。

我們可以通過SolverReset來做到這一點。

重置求解器代碼 excel 宏

更新單元格引用

我們現在解決了 16 次，但需要更新我們的單元格引用。雖然我們最初引用了 B10，但每次運行時我們需要向下移動一個單元格以更新限制。我們可以通過 offset 函數來做到這一點。我們可以更新“$B$10”以引用單元格，然後偏移 i 行（第一次運行為 0，第二次運行為 1 行，依此類推）。每次循環運行時， i 增加一。

在excel中更新宏的單元格引用代碼