A011 用目標搜尋求解困難的公式

在F003-1 計算有補上班日的工作日-NetWorkdaysPlus 我們完成了可以包含補上班日的 NetWorkdaysPlus 函數. 這個函數的計算並不複雜, 我們也不需要用到 Excel 內建的 NetWorkdays 函數. 但是 Workday 函數的演算法會比較複雜一點, 原因是我們必須要來回地在假日和補上班日清單中檢查我們要的結果日期是不是假日或補班日. 我們先把演算法擺一邊, 用 Excel 的目標搜尋功能來幫我們找到所需要的解答.

在我們沒有寫出 WorkdayPlus 的情況下, 可以用 NetWorkdaysPlus 來倒推我們需要的日期. 在  B7 儲存格的公式裡, 我們指定開始日B2和一個假設的結束日 B5, 計算出這兩天之間的工作天數是231天, 但我們希望找到工作天數是B3的日期.

選擇 目標搜尋

Excel 會變動 變數儲存格 的值, 一直到 目標儲存格的值等於目標值為止, 我們需要 Excel 變動 B5 的日期, 一直到 NetWorkdaysPlus 傳回的值是3為止. 所以我們使用以下的設定

• 目標儲存格: B7
• 目標值: 3 (常數)
• 變數儲存各: B5

按確定之後, Excel 告訴我們找到與目標值3相等的解, 日期是 2010/1/6.

使用目標搜尋的功能讓我們可以不需要設計 Workday 的函數, 也可以找到 Workday 的值. 但目標搜尋有一些缺點:

• 如果有多個解答時, 只能找到一個解;
• 利用非線性的趨近方式找到的值其實是區域解, 而不是最佳解;
• 最重要的是, 它的計算是依賴工作表的設計, 不能讓VBA程式裡的任意範圍做目標搜尋

延伸習作

1. 利用目標搜尋求解 X ^ 2 = c (c 是可以指定的常數) 的 X 值
2. 利用目標搜尋求解多項式或其他自訂的函數, 例如 3 X^5 + 2 X^4 - X^3 + 5 X^2 - 6 X + 5=0