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

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欄後插入一行 名稱, 將股票名稱包含進來.
   

A006 設定格式化規則找出最大/最小值

我們在處理資料時, 最需要避免的是花很多人工的時間在電腦可以自動處理的事情上面. 最常遇到的是要在一大堆資料裡找出符合特定條件的值. 在 F001-1 銀行牌告匯率比較表 中, 各家銀行的匯率都已經列在我們的工作表裡了, 但該選擇哪家銀行比較有利呢?

每一個匯率都有買價和賣價. 如果不清楚該用哪個價格的話, 只要了解我們去換匯的時候一定是用比較吃虧的價格就對了. 如果美元匯率買賣價是 31.48/32.02, 當我們用美元換台幣時, 一美元會換到 31.48 元而不是較高的 32.02 元, 反過來說要台幣要換美元時, 要用 32.02 元新台幣去跟銀行換一美元, 而不是較低的 31.48元. 所以我們知道, 買價最高的和賣價最低的銀行是對我們比較有利的. 我們可以用 Excel 來標示出我們要換匯的銀行是哪一家. (註: 有些銀行會對小額換匯收手續費, 加上手續費之後不見得比較有利)

我們先選取所有銀行美元的買價

選擇 常用/樣式/格式化條件

選擇 新增規則

Excel 提供多種不同的規則, 在這裡我們選擇格式化最前或最後的值. 將下方的條件設為 前 1 (前為最大值, 後為最小值), 設定格式為黃底 (或你想要的格式).

按確定之後, Excel 就自動將最買價的最大值標示出來了.

將格式複製到其他貨幣的買價

再將賣價設定自動化格式的條件為 後1, 複製到所有貨幣的賣價. 這樣就完成了.

延伸習作

1. 試著使用其他格式化條件在工作表上
2. 試試看格式化條件功能表中的 管理規則 如何使用

F001-1 銀行牌告匯率比較表

當你要出國旅遊結匯時, 會不會因為不知道要如何選擇哪一家銀行來換匯呢? 現在幾乎所有銀行都會將匯率和利率資料放在網站上. 大部份的網站資料是可以直接匯入 Excel. 我們的第一個應用範例就來做各家銀行匯率的比較表.

首先我們先做一個比較表, 在這個範例中我們包含五家銀行和四種不同的貨幣.

 從網站上搜尋各家銀行網站上的牌告匯率網頁. 範例裡使用的五家銀行網址為

1. 台灣銀行: http://rate.bot.com.tw/Pages/Static/UIP003.zh-TW.htm
2. 土地銀行: https://ebank.landbank.com.tw/infor/infor.aspx
   
3. 第一銀行: http://www2.firstbank.com.tw/eportal/fcbweb/exchange.htm
   
4. 台新銀行: https://my.taishinbank.com.tw/include/rate_body.jsp
   
5. 國泰世華銀行: https://www.cathaybk.com.tw/cathaybk/personal_info07.asp
   

首先匯入台灣銀行的匯率資料. (匯入Web外部資料的方法請參考A003 匯入Web外部資料)

 在比較表中將各幣別的匯率指向資料頁面

重複這些動作到各銀行的資料

我們再加一個可以隨時更新資料的按鈕. 首先我們錄製一個巨集 RefreshAll. (錄製巨集功能請參考 V001 利用巨集錄製功能開始寫 VBA)

選擇 資料/全部重新整理. 這個功能會讓所有外部Web資料重新整理一次.

停止錄製巨集之後, 我們要製作一個按鈕連結這個巨集. (加入按鈕的功能請參考V004 為工作表加入表單控制項 -- 按鈕)

只要按這個按鈕就會自動更新所有的網頁了.

下載: 範例檔案

延伸習作

1. 試著加入其他貨幣和其他銀行. 有些銀行的網頁沒有辦法直接匯入, 我們在後面的範例會說明)
2. 外部資料可以設定每隔幾分鐘自動更新, 請自行設定所有外部資料每小時更新一次.
3. 可以將各家銀行的匯率更新時間加入比較表嗎?
   

V004 為工作表加入表單控制項 -- 按鈕

在 V003 利用 Change 事件自動執行程式碼 中, 我們提到了用 Change 事件來自動執行巨集. 但有時候我們希望想要手動執行巨集. 這個時候加入一個按鈕控制項是最方便的方式.

在功能區 開發人員 的插入項目中選擇按鈕. 如果你的功能表中沒有 開發人員 這個項目, 請參考 顯示開發人員索引標籤或在開發人員模式中執行.

滑鼠游標變成十字形, 在工作表中拉出一個按鈕.

Excel 會要求你選擇一個巨集. 選擇要執行的巨集後, 按確定.

點選按鈕上顯示的文字, 將它修改為適當的說明

再點選工作表其他地方, 這個按鈕就連結到指定的巨集了.

延伸習作

1. 試試看其他控制項(例如下拉式方塊, 核取方塊, 或微調按鈕) 的功能, 並思考一下怎麼應用到你的試算表中.
   

A005 使用 VLookup 函數尋找資料

要製作自動化的工作表, 最重要的函數可能就是 VLookup. 這個函數可以在一串資料之中搜尋特定的資料列. 善用 VLookup 可以讓工作表針對不同的資料顯示不同的結果.

首先我們從台灣證交所匯入水泥工業股票的收盤價 (參考 A003 匯入Web外部資料).
從證交所網址 http://www.twse.com.tw 進入後, 選擇 交易資訊/盤後資訊/每日收盤行情

從每日收盤行情的畫面選擇 水泥工業 (不要選全部, 會等很久)

 選擇收盤價格的表格匯入

為了說明方便, 我們要在資料的上方寫公式, 所以選擇匯入到A6.

 

在上方輸入如下圖的資料, B1:B3 輸入名稱, C1輸入股票代碼, C2輸入如D2內容的 VLOOKUP 公式

C2的公式讓 Excel 去尋找 A10:I16 這個範圍的資料. VLOOKUP 函數會在這個範圍的第一欄(A欄)中找與第一個參數 C1 的內容相同的列(第10列). 第三個參數告訴函數要回應的是這個範圍的第幾欄. 公式中. 在上圖中第三個參數是2, 所以會回應 A10:I16 這個範圍的第二欄(B欄). 所以整個參數回應的值是 B10 的值 (台泥).

可以試著將C1的值改為其他股票的代碼. 看看名稱有沒有變.

現在我們來試試看如果輸入資料裡面沒有的代碼會有什麼結果. 先試試 1107

當我們輸入1107時, VLOOKUP回應的是1104的環泥. 為什麼會這樣呢? 我們點選函數精靈(在公式前方 fx 的地方) 看看為什麼:

函數精靈告訴我們 VLOOKUP 函數有第四個參數 Range_lookup. 選擇第四個參數之後, 函數精靈告訴我們這個參數的用法:

原來省略第四個參數會找出最接近的值(在我們的範例是前一個值 1104) . 我們將 C2 的公式加上第四個參數 FALSE.

因為 VLOOKUP 找不到 1107, 所以回應了錯誤值 #N/A. Range_lookup要用 True 還是 False 要視應用的情況而定, 如果要找到正確的資料時, 一定別忘了加上 False, 以免找到錯誤的資料而不自知.

再來我們要複製公式到下一格, 先在公式列上選擇 C1 公式的前兩個參數

按一下鍵盤上最帥的鍵 F4 (在數字 3 的上方)

公式會改為絕對參照. 這樣子將公式複製到其他儲存格時不會變動位址. 複製到C3之後, 再把第三個參數改為 9 (第9欄), 就如同下圖的樣子了.

延伸習作

1. 如果資料的第一欄沒有排序的話會有什麼結果?
2. 如果我的資料範圍寬度只有8 (A10:H16), 而第三個參數是9時, 會有什麼結果?