贊贊小屋

VBA如何同時兩欄以上VLOOKUP

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

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

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

存貨首碼是會科

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

達到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」程序的巨集編輯。

結束「VLOOKUPS」程序的巨集編輯

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

本網站所有內容皆受版權保護,网站备案编号:苏ICP备14051307号-1