Excel 應用 by Alvin Cho: 2月 2010

2010年2月23日星期二

A012 匯入 XML 資料 - 以 Google Earth KML 為例

XML 是國際通用的資料格式, 一般來說只有程式開發人員需要了解 XML 的細節, 但是 Excel 提供很方便的方式讓使用者可以利用 XML 的資料. 今天先談怎麼匯入和使用 XML 資料, 後續會介紹一些 XML 資料的應用.

目前已經有很多軟體使用 XML 格式了, Google Earth 的 KML 就是一種 XML. 我們先 Google 一下 World Heritage KML, 找到 Google Earth 社群 KeyHole BBS 的一篇文章有聯合國教科文組織 UNESCO 的全部世界文化遺產的詳細資料. 將這個檔案下載後儲存.

Google Earth 的另一種檔案格式 KMZ 其實只是 KML 的壓縮檔, 下載後解壓縮就是 KML.

選擇開發人員/XML/匯入

選擇剛剛下載的KML檔案 (7229.KML), 匯入到工作表之後就有所有世界遺產的資料了.

如果不需要這麼多資料, 我們先將原 Sheet1 的資料清除, 再選擇開發人員/XML/來源

選擇 Placemark 的 description, name, 和 coordinates, 拉到工作表上

再選擇開發人員/XML/重新整理資料, 這三個欄位的資料就匯入到工作表了

2010年2月11日星期四

F003-2 計算有補上班日的工作日-WorkdayPlus

先前在F003-1 計算有補上班日的工作日-NetWorkdaysPlus 和 A011 用目標搜尋求解困難的公式我們可以利用 NetworkdaysPlus 函數反求有補上班日的工作天日期. 但是這種方法不能用在同一個工作表的多個儲存格上. 我們必須要想辦法將它寫成 VBA 函數才可以.

我們要做 WorkdayPlus 函數有四個參數:

start_date 日期: 參考日期
days 數字: 要找的向前或向後多少工作天數
holidays 範圍(選擇性): 包含額外的假日清單
workdays 範圍(選擇性): 包含額外的補班日清單

有了NetWorkdayPlus 函數, 我們就不再反覆搜尋假日和補班日的清單了. 演算法如下

將 start_date 加上 days, 成為第一個猜測的結果 result
檢查 start_date 和 result 之間的工作天數 (用 NetWorkdaysPlus), 與 days 的差異有幾天 diff
如果 diff 不是零, 將 result 加上 diff 之後, 重新執行第二個步驟
如果 diff 是零, result 就是結果

試算的工作表如下. 我們計算 2010/1/1 起算的第30個工作天日期, 結果是 2010/2/11.

將演算法寫成 VBA. 另外我還修改了 NetWorkdaysPlus 的參數型態, start_date 和 end_date 原本是 Range, 但為了要讓 WorkdayPlus 呼叫, 改為純量值的 Date.

我們就可以在工作表上使用函數型式的 WorkdayPlus 了.

下載: 範例檔案

2010年2月10日星期三

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程式裡的任意範圍做目標搜尋

延伸習作

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

2010年2月9日星期二

F003-1 計算有補上班日的工作日-NetWorkdaysPlus

Excel提供幾個跟工作天有關的函數, 常用的是 NetWorkdays 和 Workday. NetWorkdays 可以計算兩個日期之間的工作天數, 而 Workday 是計算某一日期之前或後幾個工作日的日期.

以下的範例說明這兩個函數的用法. 我們在 B4 儲存格裡面用NETWORKDAYS函數計算兩個日期之間的工作日. 2010年全年的工作日有261天. 這個函數可以帶入假日清單做為第三個參數, 在加入假日清單之後, B5 儲存格傳回的結果是 252個工作天. 在 WORKDAY 的範例裡面, B8儲存格正確地傳回 2/22 為下一個工作日.

在台灣比較麻煩的是幾乎每年都有補上班的日子, 在 Excel 的函數裡並沒有辦法做到. 2010年的2/16星期六是補上班日, 但是 WORKDAY函數沒有辦法考慮到這一點.

我們先試做一個工作表, 再將這個工作表的流程改為 VBA 函數. 要直接做 WORKDAY函數比較困難, 我們從 NetWorkdays 開始.

NetWorkdays 簡單的作法是將總天數 - 周末天數 - 假日天數 + 補上班日數. 但 Excel 會檢查假日清單裡的日期是不是周末, 如果是周末的話並不會扣除. 所以我們在實作的時候也要考慮到假日和補上班日是否重複計算.

B5 總天數: 兩個日期直接相減是相隔的天數, 再加一就是包含前後日期的天數
B10 周末天數: 周末天數的計算比較複雜. 我們需要計算的是

總共有多少個完整的星期, 每個完整的星期會有兩個周末日
在完整的星期之外, 這段日期區間還多出幾天
再根據首日的星期數和多出的天數得到會多出多少個周末

用 WEEKDAY 可以得知開始日是星期幾, 第二個參數2表示星期一傳回值是 1, 星期二傳回2, ... 星期六傳回6, 星期日傳回7. 如果B7+B9的值大於6, 表示多出的幾天會遇到周末(請用筆實際試算一下). 多出的周末不能超過兩天, 也不能超過多出的天數 (請思考一下為什麼).

最後我們使用 SumProduct (請參考A0010 使用 SumProduct 函數統計多重條件的合計)計算在假日清單中非周末的天數, 以及補上班日中周末的天數. 我們就可以得到 B13 的結果了.

將這個試算表改為 VBA.

可以直接在工作表中使用 NetWorkdaysPlus 這個函數.

下載: 範例檔案

2010年2月5日星期五

A0010 使用 SumProduct 函數統計多重條件的合計

SumProduct 函數的基本功能是做兩個範圍個別儲存格相乘後相加. 利用 Excel 的範圍條件式, 我們可以用 SumProuct 來計算符合特定條件的數值合計.

假設我們有以下的投資組合, 希望計算虧損大於一定金額的股票. 要計算有幾檔股票符合條件, 可以使用 CountIF. 第二個參數 "<1000" 必須要加雙引號.

如果要計算部位的合計, 因為我們原始的資料並沒有持有部位(股數乘市價), 我們可以用 SumProduct 來完成. SumProduct 參數中, (F2:F8<-1000) 會產生一串 True/False 值的範圍, Excel 會把 True 值轉為1, False 轉為 0, 再與後面的範圍 C2:C8 與 E2:E8 相乘後相加.