Excel VBA Vlookup用法:1個學會流程控制的最好方法

Excel VBA Vlookup用法是一個很好的入門範例,因為在工作上會常常用到資料查找和比對,單純設計公式很容易造成工作表卡頓,本文介紹相關VBA寫法有效解決問題。

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

一、財務目錄清單

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

一、財務目錄清單

二、資料會科代碼

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

二、資料會科代碼

三、VLOOKUP公式

儲存格「G4」的公式:「=LEFT(A4,2)」,儲存格「H4」的公式:「=VLOOKUP(G4,二!A:C,2,0)」,儲存格「I4」的公式:「=VLOOKUP(G4,二!A:C,3,0)」,贊贊小屋部落格有一系列VLOOKUP文章,有需要可以參考。

三、Excel VLOOKUP

四、公式手動重算

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

四、公式手動重算

五、Excel VBA Vlookup用法

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

Excel VBA Vlookup用法:1個學會流程控制的最好方法

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的基礎上進一步編寫程式碼:

七、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多條件比對

換個角度思考更有趣

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

微軟參考說明:For…Next 陳述式 (Visual Basic)

本篇文章的講解影片:

YouTube video

想要學習更多其它用法嗎?歡迎前往VBA VLOOKUP教學中心

VBA VLOOKUP:3大重點瞭解程式如何優化Excel函數

最新文章: