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

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

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 公式指到被保護的工作表上, 公式一樣會顯露出來.

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

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 說明有關表格和結構式參照的其他功能

A007 用 Offset 函數參考不連續的儲存格

要製作自動化的工作表時, Offset 函數和 VLookup 函數是兩個最重要的工具. 只要這兩個函數用得熟練, 可以做出許多方便的 Excel 工作表.

我們將 Yahoo!奇摩股市 的投資組合匯入到 Excel 裡. 首先我們先建立一個投資組合.

將這個投資組合的資料匯入 Excel (匯入外部資料請參考 A003 匯入Web外部資料)

匯入的資料不如我們預期的整齊, 每一檔股票佔了三列, 而且中間還有一列是廣告...
將一個空白工作表改名為 清單, 將資料的表頭複製過去

如果資訊業是一種手工業的話, 我們就將每一列用人工指向資料工作表的個別列

幸好 Excel 有提供很方便的工具給我們, 不需要花人工做重複的事情.
我們打開函數精靈, 找分類 檢視與參照 裡面的 Offset

有關 Offset 函數的詳細用法請參考函數說明. 在這裡我們只介紹一部份.
Offset 可以讓我們動態地決定我們要參考的儲存格位置. 我們先試試看, 在 A2 儲存格寫入 =OFFSET(資料!A1,1,0). 這個公式讓 Excel 從 資料!A1 這個儲存格出發, 向下位移1列 (第二個參數), 向右位移 0 行(第三個參數), 所以回應的資料是 資料!A2 的內容 1101.

因為我們的每一檔股票在 資料工作表會佔三列, 所以我們在 Offset 的第二個參數每向下移一列, 數字就要乘3. 使用 ROW() 函數會回應儲存格所在的列數, 在 C欄試著寫入 = ROW(), C6儲存格的值等於6.

現在我們可以將A2的公式改為 =OFFSET(資料!A$1,ROW()*3-5,0), 向下拉之後變成如下的結果

前三檔股票沒問題了, 可是從第四檔以後因為中間插了入一行廣告就抓不到了. 我們可以在 Offset 的第二個參數加上一個 IF 函數, 公式變成
=OFFSET(資料!A$1,ROW()*3-5+IF(ROW()>4,1,0),0)

這樣子是沒有錯, 但有一點手工業的感覺, 如果哪天沒有這行廣告, 或是廣告換位置, 或是廣告多了兩行, 我們的公式就會出錯. 所以我們要用比較通用的原則來寫公式.

回到資料工作表, 我們可以了解廣告這一列跟其他資料列不一樣的地方在 O 欄的個股資訊. 廣告這一列的 O 欄是空白的. 我們只要知道O欄在資料列前面有幾格空白, 就可以知道要跳過去幾格了.

要知道有幾格空白用 COUNTBLANK 函數, 我們將 資料!O2 一直到預計資料應該出現的位置 當作參數, COUNTBLANK 會告訴我們有幾列是廣告列. 這個時候我們就用到 Offset 函數的後面兩個選用參數 Height 和 Width.

在D欄試作公式 =COUNTBLANK(OFFSET(資料!$O$1,1,0,ROW()*3-5,1))
Offset 函數的第三、四個參數告訴 Excel 要傳回的不是單一個儲存格, 而是列數為 ROW()*3-5, 行數為1的一個範圍. D欄的公式就是我們所要跳過去的廣告列數.

將 A2的公式改為 =OFFSET(資料!C$1,ROW()*3-5+COUNTBLANK(OFFSET(資料!$O$1,1,0,ROW()*3-5,1)),0)

做 Excel 試算表最有成就感的就是寫一個公式複製到所有的儲存格. 到這裡我們已經完成了公式的設計, 將 A2 的公式複製到所有儲存格.

延伸習作

1. 在公式中的欄位移 (第三個參數) 我們的設計是 0. 試著將 Offset 第一個參數 資料!A$1 固定為 資料!$A$1, 然後在第三個參數用 COLUMN() 函數來取代
2. 範例公式如果遇到廣告超過三列會有錯誤. 試著寫一個公式可以容許無限制的廣告列.
3. 在 A欄後插入一行 名稱, 將股票名稱包含進來.