VBA如何編寫Vlookup公式整理財產目錄 - 會計

VBA如何編寫Vlookup公式整理財產目錄

網誌圖文版：

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

Vlookup是《會計人的Excel小教室》主打的兩大招式之一，對於會計工作有很大幫助，在
整理和補充報表時，往往會用到Vlookup函數公式。但，由於它是「比對特定條件查找並
傳回儲存格內容」，聽起來就比較消耗計算資源，有經驗的讀者應該遇到過，在筆數量巨
多、查找範圍寛廣的情況，Excel一定當場卡關給你看，然後CPU使用率瞬間飇昇50%以上
……。我自己工作便有幾個例行性檔案，資料少說幾萬筆，而且系統產生出來的報表有所
不足，必須以Vlookup函數補好補滿，每次把公式往下拉到底，惡夢於焉開始，我的工作
，跟著我的電腦一起被Excel綁架了。在此分享個人在Excel及VBA如何減輕計算負擔的小
技巧（簡單版和複雜版）：

一、極簡版本的資產目錄，只有6筆資料，大企業絶對是這個的好幾N倍。

二、「資產代碼」、「資產類別」、「會計科目」，這些是原報表沒有、但很想要有的資
料，必須以Vlookup帶入。

三、儲存格「G4」的公式：「=LEFT(A4,2)」，儲存格「H4」的公式：「=VLOOKUP(G4,二
!A:C,2,0)」，儲存格「I4」的公式：「=VLOOKUP(G4,二!A:C,3,0)」，有修過《會計人的
Excel小教室》學分，應該都會寫像這樣的函數公式。

四、如同文章一開始所述，如果資料量大，好幾萬個Vlookup同時運作，結果可想而知。
解決辦法之一：上方功能區移到「公式」頁籤、「計算」區塊，將「計算選項」下拉，有
「自動」、「除運算列表外，自動重算」、「手動」三個選項。「自動」是只要儲存格有
任何變動，包括自動儲存時，都會再跑一次幾萬個Vlookup（其實沒必要）。「除運算列
表外，自動重算」主要是搭配Excel一個「模擬分析」的應用，有機會再作分享。「手動
」則是這篇文章推薦的方法之一，設置後Excel不會主動重算，除非操作者按下這個功能
區塊裡的「立即重算」指令。

五、手動重算縱然有其妙用、而且操作簡單，但往往工作中會同時開好幾個Excel檔案好
幾個工作表，手動重算只能在「都不重算」和「一起重算」兩者之間控制，在靈活度上等
於是沒有。最好的解決方案應當是需要時再重算這幾萬筆Vlookup，就這幾萬Vlookup，其
餘不受影響。想達到這樣的效果，只有編寫VBA程式碼了。首先在這個步驟重點介紹VBA的
「UsedRange.Rows.Count」和「For…Next」。

10：建立一個巨集程序，取名為「UsedRange_Left」。

30：很實用的標準程式碼，計算目前工作表的資料行數，設定其值為「R」，如同步驟一
的圖片所示，這裡的R值為10。

40：在儲存格「G1」輸入「R」值。

60：設置一個從「4」到「R-1」的迴圈，變數為「i」，於此範例即為4到9。

80：利用「Left」函數取儲存格文字串的左邊兩個字元，並且設定其值為「C」。例如當i
＝4時，「Left(Cells(4, 1), 2)」即為「OF」。

90：在儲存格「(i,7)」輸入「C」值，例如當i＝4時，亦即於「G4」輸入「OF」。

110：完成一次循環，跳回到「For…」執行下一個「i」。

130：結果此程式。

六、執行結果，如圖所示。「G1」為「10」，表示資料含標題共有10行。「G4」在資料編
輯列為「OF」，即使把它的引用對象儲存格「A4」清除，「OF」仍然在，這代表Left函數
是在VBA執行時計算，把結果值輸入於Excel儲存格中，在Excel並沒有任何函數公式，因
此也不會有公式計算的問題。

七、在先前VBA的基礎上進一步編寫程式碼：

90：在迴圈「For i = 4 To R - 1」中再套一個迴圈「For j = 2 To 4」，第一個「i」
迴圈類似於Excel公式往下拉、好幾個Vlookup的效果，第二個「J」迴圈類似於一個個的
Vlookup，相當於模擬一行一行往下查找的效果。

110~130：白話解釋程式碼，如果「C」(Left(Cells(i, 1), 2))等同於Sheets("二
").Cells(j, 1).Value，那麼將「Sheets("二").Cells(j, 2).Value」輸入在「Sheets("
八").Cells(i, 7).Value」。用心琢磨這一段程式碼，應當能理解其執行效果，便是函數
公式「VLOOKUP(G4,二!A:C,2,0)」。

150~170：另外一組Vlookup，前面查找的是資產類別，這裡查找的是會計科目。嚴格比較
VBA程式碼和Excel函數公式，只有在如果有兩個查找條件同時成立，VBA程式碼取的是成
立的最後一行內容，Excel的Vlookup函數會取成立的第一行內容，這部份文字說明較為抽
象，日後有機會再以適當範例作介紹，總之於實務工作中，很少會遇到因此所產生的困難
。

八、一如預期，以VBA巨集實現Vlookup計算結果，除非再執行一次巨集，否則Excel再怎
麼自動計算或自動儲存，都不會再作這方面的處理，所以當然也不會影響電腦速度。

這節範例用到了一般應用程式中很常見的迴圈語句。如果肯花時間閱讀理解，它其實相當
於高中數學方程式的概念，應該不難理解，它的概念。這裡的VBA程式碼就是單純依照
Vlookup函數的運作邏輯，把工作表上的每一個儲存格當做對象來做計算處理。擴大而言
，在現有的函數公式裡面，只要有涉及到範圍，從哪個儲存格到哪個儲存格的，都可以寫
成VBA的一個迴圈，從這個角度來思考VBA程式碼，會是一個蠻有趣的過程、也會比較有成
就感。


延伸閱讀：

VBA如何自動建立資料夾（下）

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

VBA如何合併報表資料

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

VBA如何保持檔案乾淨

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

--


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

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


--