VBA教學

如何使用EXCEL之VBA程式，合併不同表的兩欄數據


局內同仁，經常在詢問如何將兩張EXCEL表的資料，利用相同一欄的資料(KEY)湊在同一張表上。EXCEL表面上雖然沒有提供這個功能，但是事實上，卻暗藏強大功能，也就是EXCEL的VBA。所謂的VBA就是Visual Basic for Applications，也就是使用VB這個程式語言為MS offices各種應用程式，諸如EXCEL、ACCESS等辦公室軟體，實現更強大的功能。現在我們就來看看如何將兩資料表的欄位，合併到同一表上吧。

　　在此舉出實用的一例，以下圖一是由GTI所抓的中國自世界進口的各國排名，我只需要Country與%Change這兩欄，%Change(07/06)就是2007年對2006年的成長率，將資料整理成以下圖二，然後再取2006年GTI(%Change)資料，處理後如圖三。

圖一、GTI原始下載資料

圖二、2007年成長率

 圖三、2006年成長率

　接下來就是合併欄位的動作，將表二成長率依據國家別，將數據分別貼到表三去，變成以下圖四，看似簡單，如果你只把圖二的B欄整欄貼到圖三的C欄去，就會貼錯，看看圖二及圖三，第十二名後國家排列就有所不同了，所以亂貼會錯喔，必須讓國家別對應到正確的成長率才行。若不要一個一個貼，該怎麼作才好呢，我來教你吧!

圖四、2006年及2007年成長率

步驟：

Step1 將圖二中的EXCEL資料全部複製(Ctrl+A，Ctrl+C)，貼到圖二中的EXCEL中的sheet2(Ctrl+V)。

Step2 到EXCEL目錄中選，工具→巨集→Visual Basic 編輯器，將以下程式碼，如圖五中，點選VBAProject(選擇目標EXCEL file)下的任一個sheet，寫到右邊空白處。

Sub check()

Dim i, j As Integer

Dim check, check2 As Boolean

check = True

check2 = True

i = 1 

   Do While check

     j = 1 

     Do While check2

        If Worksheets("sheet1").Cells(i, 1).Value = Worksheets("sheet2").Cells(j, 1).Value Then

         Worksheets("sheet1").Cells(i, 3).Value = Worksheets("sheet2").Cells(j, 2).Value

        End If

       j = j + 1

        If j = 200 Then  

            check2 = False

        End If

     Loop

        check2 = True

        i = i + 1

        If i = 200 Then   

            check = False

        End If

    Loop

End Sub

圖五VBA編輯畫面

Step 3 從圖五可以看到工具列上，有一個綠色三角形有沒有，按下去就開始跑程式囉，跑完後就有圖四的結果了，是不是很簡單呢!

　　接下來告訴你程式碼到底是怎麼回事吧!

Sub check()              ‘這個check是程式的名稱

Dim i, j As Integer

Dim check, check2 As Boolean　

check = True

check2 = True

i = 1  ‘--------------------------A

   Do While check　‘----------------B

     j = 1    ‘-----------------------C

     Do While check2      ‘--------------D

                    '如果sheet1的Country欄位 = sheet2的County欄位

        If Worksheets("sheet1").Cells(i, 1).Value = Worksheets("sheet2").Cells(j, 1).Value Then   ‘--------------------E

                           '就把sheet2的成長率欄位貼到sheet1的C欄囉

         Worksheets("sheet1").Cells(i, 3).Value = Worksheets("sheet2").Cells(j, 2).Value        ‘---------------------F

        End If

       j = j + 1

        If j = 200 Then    '-----------------G

            check2 = False

        End If

     Loop

        check2 = True

        i = i + 1

        If i = 200 Then     ‘------------------H

            check = False

        End If

    Loop

End Sub

　　這個程式簡單的原裡就是，sheet1逐列比對Country的國別(sheet1 A欄)，每次將指標移到sheet1的其中一國別，就掃過sheet2的所有國別(sheet2 A欄)，直到找sheet1國別之sheet2相同國別的位置，再將此列的成長率(sheet2 B欄)加到sheet1的C欄之中。

　　在程式中B行是一個迴圈，跑sheet1的國別名，D行是內迴圈跑sheet2的國別名，A行跑sheet1的指標，看你資料從哪行開始，就將數字設定為何，C行則設定sheet2的資料起始位置，G行看你sheet2國別名結束於哪一行，H行看你sheet1國別名結束於哪一行，這裡端看你的資料最多到哪，其實只要把數字設定超過於資料列數即可。

　　接下來是重點了，E行中sheet1的Cells(i,1)跟sheet2的Cells(j,1)比對，Cell(x,y)的前一個參數x是指定EXCEL中，列的位置(垂直方向)，y則是行的位置(水平方向)，因此Cell(x,y)就能指定到EXCEL某個格子去。E行的i跑sheet1迴圈跟j也跑sheet2迴圈，若兩個所跑EXCEL的兩格子所指的資料相同，就將sheet2的資料貼到sheet1指定格子中(C欄)，便是在程式F行將sheet2第二行某格的資料貼到sheet1第三行某格中。

　　這段程式其實不難，卻是許多VBA程式變型的開始，若讀者能活用此段程式加以變化，比如其他應用，將稅則資料貼到相對應的貨號位置上、將幾國的屬性貼到同一個表上去、將50大的貨號的歷年成長率或者市占率貼到同一表去、處理貿易統計EXCEL資料的匯整等等。這麼多應用，聰明的您，肯定動心了吧，這段程式，相信對你工作上一定會有莫大的幫助，若您工作應用到了，有任何疑問，請洽資訊中心，資訊中心一定竭誠為您服務。