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/重新整理資料, 這三個欄位的資料就匯入到工作表了

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

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

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

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

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

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

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

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

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

下載: 範例檔案

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

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 周末天數: 周末天數的計算比較複雜. 我們需要計算的是

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

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

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

將這個試算表改為 VBA.

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

下載: 範例檔案

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

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

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

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

損益的合計我們可以用  SumIF.

這樣子的試算表很沒有彈性. 我們應該把條件設定為可以變動的. 我們將儲存格 B11的值改為-1000, 後面的公式也需要改一下. 如果條件是在字串裡, 要改為 &B11

第二個條件持有超過3000股的部份可以自行實作, 有差別的部份只有損益, 因為條件判斷的儲存格與要相加的儲存格不同, SumIF 要加第三個參數.

最後我們用 SumProduct 來做需要兩個以上條件的合計. 檔數的部份我們直接將兩個條件寫在參數裡, 就類似 Count 計數的功能.

部位和損益的公式只要在後面乘上要加總的值就可以了

延伸實作

1. 試著加入一個持有部位下限的條件.
2. 如果 B11 輸入的值大於0, 該怎麼處理?

A009 Excel 公式的保護

很多人都想要保護自己辛苦工作的成果, 不希望花費許多心血的 Excel 檔案被別人再利用. 今天我們先談談 Excel 最基本的 保護工作表 這個功能.

首先我們有一個很重要的 Excel 檔案, 裡面有我們不想讓別人看見的公式.

打開儲存格格式, 選擇最右邊的頁籤 保護. 鎖定 表示使用者不能移到鎖定的儲存格, 選擇 隱藏的話儲存格裡的公式不會顯示在公式列上.

我們將 B2:B4 三個儲存格解除鎖定, 將放面積公式的 B4 設定為不鎖定但隱藏, B5體積公式不修改. 設定完畢之後選擇 保護工作表. 這些保護設定一定要選擇保護工作表才會生效. 預設值可以選取鎖定的儲存格, 我們把第一項取消.

得到的結果是除了這三個儲存格之外, 所有儲存格都不能選擇了, 而B4的公式是一片空白. B5 體積公式因為沒有解除鎖定, 所以我們也看不到它的公式.

這樣看起來已經保護好  B4 和 B5 的公式, 但我們寫一個簡單的 VBA 函數 DisplayFormula, 回覆的值是參數範圍的 Formula 屬性.

在 Sheet2 的儲存格寫公式 =DisplayFormula(Sheet1的相對位置), 我們發現只有設定 隱藏 的面積公式有受到保護, 體積公式仍然可以看得見.

隱藏工作表也無法保護公式的內容. 我們將 重要資訊 這個工作表隱藏之後, 選擇保護工作簿的結構和視窗.

理論上我們是看不到隱藏的工作表, 從 Visual Basic 編輯器一樣可以看到被隱藏工作表的名稱.

開一個新的工作簿, 將 DisplayFormula 公式指到被保護的工作表上, 公式一樣會顯露出來.

所以要保護儲存格的格式一定要設定為隱藏.

V005 常數變數化, 變數參數化

這一篇我們要談的是一般程式設計的技巧, 不只適用於 VBA, 所有程式語言都一樣.

我們在 V002 修改 Web 外部資料的 URL 中製作了 ChangeX() 和 ChangeY() 兩個函數, 可以依據 Monitor 工作表中的某些特定儲存格的內容變更外部資料的網址. 好的程式設計可以考慮到 重複使用性 (reusability), 我們所寫的程式可以放在不同的地方使用. 最簡單的方法就是將現有的程式彈性變大, 利用變數化和參數化的技巧, 讓我們的程式可以用在更多地方.

現在我們有兩個自訂函數, 程式如下圖

我們先前錄製巨集的時候, 第二行 URL 的部份是一個常數 AMD (下圖取自  V002)

我們在 V002 修改 Web 外部資料的 URL 中將 AMD 這個常數值改為可以讓我們輸入資料的變數 Range("B3"), 這個動作是 常數變數化. 經過變數化的動作, 讓我們的程式彈性大大的提昇了.

再來我們要做的是 變數參數化 的動作. 我們可以發現這兩個程式幾乎是完全一樣, 只有前兩行有兩個 Range 不一樣, 一個是外部資料的位址, 一個是股票代碼的位址. 我們將這兩個變數提昇到參數的階層, 另外寫一個程式叫 ChangeURL(rangeData, rangeCode), 再修改 ChangeX 和 ChangeY 呼叫 ChangeURL, 程式變得簡單多了.

寫程式有時候會為了方便, 直接將程式複製很多份再修改內容, 這樣子的程式要維護非常麻煩. 參數化的自訂函數最大的好處是當需要修改函數的時候, 不需要每個地方都修改一次, 在我們的例子裡只要修改 ChangeURL 的功能, 不需要修改 ChangeX 和 ChangeY 兩次.

延伸習作

1. 將 V001 利用巨集錄製功能開始寫 VBA錄製的 ImportX, ImportY 參數化
   

A008 使用 Excel 2007 的表格功能

經常會有人問說該不該升級到 Excel 2007. 相信還有很多人是用 Excel 2003 或是更早的版本. 剛開始使用 Excel 2007 會很痛苦, 因為介面跟舊版完全不同, 經常找不到想要的功能在哪裡. 不過我相信升級還是值得的, 在這裡我要介紹最值得使用 Excel 2007 的理由: 表格功能.(本文所提到的功能大部份在 Excel 2007 以前的版本並沒有)

我們在 A007 用 Offset 函數參考不連續的儲存格 中將 Yahoo!奇摩的投資組合匯入到 清單工作表, 現在將一個空白工作表更名為 未實現損益, 我們需要的欄位如下圖.  如果你不想匯入資料可以直接輸入.

將儲存格內容指向清單工作表的內容

選擇 常用/格式化為表格

選擇想要的格式和表格範圍後, 表格設定就完成了

現在來寫損益的公式. 損益是每股市價減成本後, 乘以股數. 當我們在F2寫公式時, 輸入 =( 並將滑鼠點選 D2 後, 出現奇怪的公式

公式有點像我們在 A004 利用範圍名稱來寫公式 中介紹的範圍名稱, 但功能更強, 可以參考到 這個列 中的某一欄的資料. 這種命名方式稱為結構式參照, 想要詳細了解可以參考 Excel 的說明. [#這個列] 的命名有點蠢, 在 Excel 2010 中有改進了這一點. 我們暫時需要忍耐一下.

接著將公式完成, (E2-D2)*C2. 變成下圖的狀態.

所設定的公式會自動填入到這一欄所有的儲存格.

我們可以在下方加一個合計列. 在表格範圍內按右鍵, 選擇 表格/合計列

合計列不只是加總合計, 可以顯示其他不同的計算

如果表格範圍要加大, 右下角有一個縮放控點

隨時可以增加新欄或列.

表格上方的標題列功能跟自動篩選相同

當向下捲動時, 標題列會自動顯示在原本欄名ABC的地方, 不需要先凍結窗格

如果要修改表格名稱, 使用 公式/名稱管理員, 編輯 表格2 的內容

改為 未實現損益表. 公式的可讀性就更高了.

延伸實作

1. 參考 Excel 說明有關表格和結構式參照的其他功能