VBA如何同時兩列以上VLOOKUP - 會計

VBA如何同時兩列以上VLOOKUP

網誌圖文版：

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

Excel左看右看，就是由切好的豆腐塊組成的座標方格紙，每個方塊輸入文字
或者數值，能夠透過引用內容或者是函數計算的方式，和其他方塊產生連結
，整體呈現出我們所需要的資料報表。在這裡，有個不知不覺的先天性限制
，就是我們只能夠以儲存格（豆腐塊）作為介面去操作。雖然可以選取範圍
，批次地複製或貼上，但儲存格操作的本質並沒有改變，無形中受到很多限
制。

例如說，想要大範圍依照某種規則輸入函數公式，這個在Excel的「前臺」很
難辦到，但是如果，我們透過VBA，繞到Excel後臺，瞬間擺脱了方格子束縛
，如果能夠編寫VBA程式，會發現自由自在許多。在此便以熟悉的Vlookup函
數，作為範例具體介紹如下：

一、如圖所示，存貨首碼是會科碼，依照編碼原則，簡單兩套VLOOKUP帶出會
計科目及會科名稱：「=VLOOKUP(C2,I:K,2,0)」、「=VLOOKUP(C2,I:K,3,0)
」。

二、藉助VBA，希望一次到位，達到「VLOOKUPS」的效果，程式如下：

三、逐行解說：「Sub VLOOKUPS()」意思是建立一個程序巨集，巨集的名稱
為「VLOOKUPS」。

四、定義一個變量「Acctcode」，賦予其值為儲存格範圍「I1:K4」，也就是
範例的編碼原則。

五、建立一個字典表型態的對象：「
CreateObject("scripting.dictionary")」，「Set」命名此字典為「
Dictionary」，這裡的「("scripting.dictionary"」也許剛開始看不太習慣
，把它想成是一個VBA函數的名稱，就跟Excel裡的「VLOOKUP」一樣，久了成
自然。
六、從第二欄到第三欄：「For Column = 2 To 3」，這裡的「Column」，其
實就是個變量，跟國中數學相同，它也可以是x、y、z，我取「Column」，純
粹顧名思義，容易理解。

七、再建立一套字典表：Set Dictionary(Acctcode(1, Column)) =
CreateObject("scripting.dictionary")」，注意到這裡的「Acctcode(1,
Column)」，先前第四步驟，已經把Acctcode定義為Excel工作表中的範圍「
I1:K4」，再搭配：「For Column = 2 To 3」，作用便是建立「J1」（會科
）和「K1」（分類）兩個字典表，也就是編碼原則列表。

八、從第二列到第四列：「For Row = 2 To 4」，編寫字典如下：「
Dictionary(Acctcode(1, Column))(Acctcode(Row, 1)) = Acctcode(Row,
Column)」，這裡如果看不太懂，其實只要帶一下儲存格內容，例如第一組
Column2和Row2，亦即Excel裡的「J2」（1510），把全部帶進去，便是：「
Dictionary(會會科)(A) ＝1510」，意思是在會科字典表裡，A單字的解釋詞
是1510。

九、兩個「Next」，分別是Row3、Row4循環，然後是外面一層的Column3循環
，剛開始不太熟悉這種程式寫法，建議依照上個步驟，再模擬下去，應該就
會有感覺了。而進一步思考，如此模擬的過程，其實也就是還原VLOOKUP函數
的計算過程。

十、第三到第九步驟為上半部，定義好了字典表，下半部開始，同樣是建立
循環。第一層依序從第四欄到第五欄：「For Column = 4 To 5」，第二層依
序從第二列到第七列：「For Row = 2 To 7」，依照編碼原則代入：「
Cells(Row, Column) = Dictionary(Cells(1, Column).Text)(Cells(Row,
3).Text)」，以第一組Column4和Row2模擬，：「Cells(2,4) =
Dictionary(Cells(1,4).Text)(Cells(2, 3).Text)」，這裡的「
Cells(1,4).Text」，意思是取儲存格(1,4)所顯示的文字值，所以套入
Excel儲存格內容便是「D2 = Dictionary(會科)(A)」，配合第八步驟的模擬
，也就是「D2 = 1510」。

十一、又是兩個「Next」，把「D2：E7」都依照編碼原則(字典表)，依序寫
入儲存格內容。最後「End Sub」，結束「VLOOKUPS」程序的巨集編輯。

通常的Excel批次操作，大概只有兩個方法，一個是選定特定範圍，複製、貼
上（選擇性貼上），或者於某個儲存格裡輸入公式，然後在邊角的小黑十字
架，下拉（上移）、右拉（左移）。無論哪種方式，都是在Excel前臺方格紙
上跳來跳去。而如同此篇文章範例所分享的，透過VBA進入Excel後臺，以文
字方式給定範圍，設定指令，雖然文字不如圖像來的直覺，但文字總歸是相
當自由。況且，VBA的操作對象，不僅限於儲存格或範圍，尚能直接對工作表
工作簿下達命令，對於Excel的使用上是如虎添翼，這部份留待以後有機會，
再詳加介紹。


延伸閱讀：

Excel如何以Hlookup整理應付帳款

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

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

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

Excel如何vlookup兩套帳本傳票核對

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

--


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

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


--