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




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小教室》學分,應該都會寫像這樣的函數公式。

儲存格「H4」的公式:「=VLOOKUP(G4,二!A:C,2,0)」

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

有「自動」、「除運算列表外,自動重算」、「手動」三個選項

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

重點介紹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並沒有任何函數公式,因此也不會有公式計算的問題。

表示資料含標題共有10行

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

在先前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程式碼就是單純依照Vlookup函數的運作邏輯,把工作表上的每一個儲存格當做對象來做計算處理。擴大而言,在現有的函數公式裡面,只要有涉及到範圍,從哪個儲存格到哪個儲存格的,都可以寫成VBA的一個迴圈,從這個角度來思考VBA程式碼,會是一個蠻有趣的過程、也會比較有成就感。






當前文章延伸閱讀:
VBA自動化