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

[moodyblue ()]

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

網誌圖文版：

http://www.b88104069.com/archives/4251

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如何以多層級排序搭配VLOOKUP評價存貨

http://www.b88104069.com/archives/4250

Excel如何以Hlookup整理應付帳款

http://www.b88104069.com/archives/4135

Excel如何將欄位合併進行vlookup比對

http://www.b88104069.com/archives/4089

--


    周末，我們繼續Excel：精華區=>21.心得=>5.其他=>3.office

    會計人的Excel小教室： https://www.facebook.com/acctexcel


--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 112.20.97.12
※ 文章網址: https://www.ptt.cc/bbs/Accounting/M.1510480001.A.3A0.html
推 denny5082: 大推！！感謝您！！
推 simon2608: 推推推!