VBA如何執行一次性Vlookup帶入新帳本會科

VLOOKUP函數是Excel兩大妙用絕招之一,可是在資料量大的時候,讓Excel去跑VLOOKUP,常常老牛拖車,右下角那個計算百分比極其緩慢在上昇。依照個人實務經驗,資料超過一萬筆,Excel就吃不下去了。其實,如果只算那麼一次, 大家也可以理解,資料量太大了嘛,但偏偏,Excel先天設計是全面重算,所有正開啟中的活頁簿、所有工作表、每個儲存格,舉凡有帶到計算公式,Excel都會認真算,一個不放過!可想而知,很多時候我們只想算算旁邊那個簡單加減乘除,Excel卻走火入魔,再算一次那個算了N次的好幾萬筆VLOOKUP。相信實務上有過經驗的人,都知道跟那個春節高速公路塞車一樣XX。以下,介紹如何以VBA一勞永逸,輕鬆執行一次性VLOOKUP!

一、2015年的傳票,當時還是老系統老會科,可能因管理分析需要,必須拿出來和當前作比較。

二、2016年開始已經新ERP上線,當時留有導入新系統的會科更新對照表。

三、為了方便作跨年度比較,必須把15年傳票的舊會科,套上新系統會科。第一個想到的「=VLOOKUP($ B2 , ‘ 2 ‘ ! $A:$D,3,0)」,這裡精心設計了固定參照「$」,方便直接拖曳複製公式。

四、如同文章一開始所述,像這樣的VLOOKUP,如果資料有上萬筆以上,Excel將會「無言的抗議」,不過在「公式」頁籤中的計算群組,可以設定「計算選項」:預設是「自動」,表示每次變更數值、公式、名稱即會重算,「手動」是自己決定何時「立即計算」儲存格公式,但這不代表原有的公式不會再進行計算(Excel本身很難判斷哪些不該算,所以乾脆全部都算),另外還有一個「計算工作表」,意思是僅計算當前工作表。有了這選項雖然不錯,但卻無法真正解決此範例所遇到的問題。

五、設計VBA程式,首先得到目前活動範圍有多少水平列(變數「R」),設置第「2」到第「R」的迴圈,將Excel函數公式帶到VBA程式裡。

六、執行巨集「VBA_Vlookup」,得到和VLOOKUP函數一樣的效果,仔細看,儲存格並沒有公式,Excel再怎麼「自動重算」,也不會算到這一塊。

七、Excel可以「$」快速複製函數公式,其實只要熟悉VBA程式,複製貼上也是很快的。

八、成功以VBA得到兩行的VLOOKUP結果,程式碼不會太難,建議讀者可以刷看看,和第三步驟的傳統VLOOKUP方式來個超級比一比。

不想Excel一直在重覆計算沒有問題的公式,有個最簡單的解決辦法,第一次函數計算完來個複製值貼上,等於是大絶招,就算怕之後忘了怎麼算的,也可以在第一格或最後一格保留公式,如此既不會造成Excel負擔,又方便有需要時再整批拉公式。

話說回來,如果能夠身懷VBA絶技,像這裡的範例小露身手一番,當然是最好的!贊贊小屋預計12月開設VBA實體教室課程,歡迎大家有時間有興趣,來學幾手上乘VBA武功!

Excel整理術 - 入門密技+進階實作 台北場淡江大學台北校區
2017-12-23(星期六) 09:00~16:00

http://smart.businessweekly.com.tw/ecourse/product.aspx?PROD_TYPE_NO=PROD000008934

《會計人的Excel小教室》:每位會計人都應該具備的武功秘笈!

http://www.books.com.tw/products/0010734874

《會計人的Excel VBA小教室》:會計人Excel強化並且VBA入門!

http://www.books.com.tw/products/0010753074