跳到主要內容區塊

ntuccepaper2019

技術論壇

EXCEL巨集實用範例
  • 卷期:v0019
  • 出版日期:2011-12-20

作者:林淑芬 / 臺灣大學計算機及資訊網路中心教學研究組程式設計師


在資料處理上,Excel整體來說功能算是相當好用的。但有些時候我們可能會發覺Excel怎麼剛好沒有所需的功能,這時便可以寫一點小小的巨集程式來加強。因此擅長使用Excel軟體的人,只要再加上一點小小的巨集程式撰寫,就好比如虎添翼,幾乎無所不能了。

 

在這裡我們將Excel活頁簿模擬成一維、二維、三維陣列,介紹幾個實用的巨集範例,本文將著重在說明程式指令的撰寫,讓不會寫程式的人也可以很快學會這幾種常用的Pattern,至於巨集的編輯和執行方式在另一篇「Excel學習三部曲」中有詳細說明,於此將不再贅述。
 

第一階段:以(Range("A" & I)) 模擬一維陣列

範例一:計算ID不重複的資料有幾筆,需先根據ID排序,Range("A" & I)是表示A欄的第I列儲存格,茲將程式碼說明如下, '符號表示之後的文字是註解,作為說明之用,可不必打在程式裡:

Sub countunique()

    Dim I, count As Integer

    I = 2  '第一列是標題 資料從第二列開始

    count = 0  '計數器變數

    Worksheets(2).Select  '資料放在Sheet2

'將第一筆資料的ID放到AA變數以便和下一筆比較

          AA = Range("A" & I).Value  

    I = I + 1  '指向下一筆資料

Do While AA <> ""  '還有記錄資料 即ID不等於空字串

'假使ID值不等於AA變數的上一筆ID值 則計數加一

 

    If Range("A" & I).Value <> AA Then

        count = count + 1      '不重複的ID則計數器變數加一

'將目前的ID保留到變數AA以便和下一筆ID比較

              AA = Range("A" & I).Value

    End If

    I = I + 1  '指向下一筆資料

Loop

'迴圈結束表示不再有ID資料了 把加總計數結果放到H1儲存格

Range("H1").Value = count

End Sub

 

範例二:如上圖,ID和NAME兩個key都相同的資料要刪除,需先根據ID再根據NAME排序,茲將程式碼說明如下:

Sub delsame()

    Dim I As Integer

    I = 2

    Worksheets(2).Select  '資料放在Sheet2

'將目前的ID和Name保留到變數AA和BB以便和下一筆比較

    AA = Range("A" & I).Value

    BB = Range("B" & I).Value

    I = I + 1 '指向下一筆資料

Do While AA <> ""  '還有記錄資料 即ID不等於空字串

'假使ID等於上一筆ID , Name等於上一筆Name則該筆刪除

    If Range("A" & I).Value = AA And Range("B" & I).Value = BB Then

        Rows(I).Delete

    Else

'不然繼續將目前的ID和NAME保留到變數AA和BB以便和下一筆比較

        AA = Range("A" & I).Value

        BB = Range("B" & I).Value

        I = I + 1 '指向下一筆資料

    End If

Loop

End Sub

 

第二階段:以(Cells(I, J)) 模擬二維陣列

範例三:以下是矩陣相乘的例子,Cells(I, J)表示二維陣列第I列第J欄(索引皆從1開始,所以Cells(3, 2)即是指B3儲存格)的位置,程式碼也說明如下:

Sub Array_Mult()

Dim I, J, K, temp As Integer

Worksheets(1).Select

 For K = 1 To 3

      For I = 1 To 3

         temp= 0

         For J = 1 To 2

            temp = temp + (Cells(I + 2, J + 1) * Cells(J + 2, K+ 4))

         Next J

         Cells(I + 6 , K + 1) = temp

      Next I

Next K

End Sub

範例四:這是一個較複雜的例子,從下圖原始的明細資料(sheet1)可看到一個病歷號有數十種不同的檢驗項目,及其檢驗結果,我們要將同一個病歷號的數十種檢驗合併成一個病歷號一筆資料(sheet2),但是每個病歷號檢驗的項目只是所有檢驗項目的一部分,也就是說,假設檢驗項目總共是100項,那麼每個病歷號在sheet1是小於等於100,而且每個病歷號的檢驗項目並不盡相同。首先我們要在sheet1根據病歷號,檢驗項目加以排序。sheet2則先要在第一列準備好所有檢驗項目。

 

sheet1:原始明細檢驗資料。

 

sheet2:合併後一個病歷號一筆資料。

Sub Rearrange()

Dim I, J, M, N As Integer

I = 2    '表示原始明細資料的第幾列

M = 1  '表示合併後資料的第幾列

N = 1  '表示合併後資料的第幾欄

Worksheets(1).Select  '選擇原始明細檢驗資料

oldID = ""  '用來保留前一筆的病歷號

ID = Range("A" & I).Value '將目前的病歷號保留到變數ID以便和下一筆比較

Item = Range("B" & I).Value '將目前的檢驗項目保留到變數Item以便比較

Value1 = Range("C" & I).Value '將目前的檢驗值保留到變數Value1以便比較

 

Do While ID <> ""  '還有記錄資料 即ID不等於空字串

    Worksheets(2).Select  '選擇合併後一人一筆資料

    N = 1 '合併資料從第一欄開始比對檢驗項目

    If ID = oldID Then '同一病歷號則開始合併

        Do While Item <> Cells(1, N) '檢查檢驗項目是否相同

            N = N + 1 '檢驗項目不同則繼續找下一欄位

        Loop

        Cells(M, N) = Value1  '檢驗項目相同則在此位置放入檢驗值

 

Else '當開始一個新的病歷號時

        M = M + 1  '合併檔往下新增一列

        Cells(M, N) = ID '加入新的一筆病歷號

        Do While Item <> Cells(1, N) '檢查檢驗項目是否相同

             N = N + 1 '檢驗項目不同則繼續找下一欄位

        Loop

        Cells(M, N) = Value1 '檢驗項目相同則在此位置放入檢驗值

    End If

 

    Worksheets(1).Select      '再切回原始明細檢驗資料

    oldID = ID       '將病歷號保留以便和下一筆比較

    I = I + 1    '移到下一筆

    ID = Range("A" & I).Value '將目前的病歷號保留以便和下一筆比較

    Item = Range("B" & I).Value '將目前的檢驗項目保留以便和下一筆比較

    Value1 = Range("C" & I).Value '將目前的檢驗值保留以便和下一筆比較

  Loop

End Sub

 

第三階段:以Worksheets("sheet" & N)模擬三維陣列

範例五:這是一個升等聘任的實例,從下圖中我們看到sheet1-sheet5每一頁表示一個人的明細資料,想要彙總成一頁total,只存放最重要的幾項摘要資料,每一個人一列,Worksheets("sheet" & N)是指選擇哪一頁的工作表,我們利用巨集程式將多頁的明細摘要成一整頁,這裡只有示範5頁明細資料,當有更多頁的明細資料時,會為我們省下很多時間,非常實用。

Sub TOTAL()

Dim i, j As Integer

Dim sh As String

i = 5  ' total sheet 從第五列開始

For N = 1 To 5  '總共有sheet1-sheet5

        sh = "sheet" & N  ' 工作表名稱

        Worksheets(sh).Select  '選擇某頁工作表

        Value1 = Range("G2").Value '需彙總的儲存格值分別放入各個變數

        Value2 = Range("D2").Value '需彙總的儲存格值分別放入各個變數

        Value3 = Range("L2").Value '需彙總的儲存格值分別放入各個變數

                  ……

       Worksheets("TOTAL").Select  '選擇TOTAL  sheet

        Range("A" & i).Value = N '放入這是第幾位的編號

        Range("B" & i).Value = Value1 '將各個變數值分別放入儲存格位置

        Range("C" & i).Value = Value2 '將各個變數值分別放入儲存格位置

        Range("D" & i).Value = Value3 '將各個變數值分別放入儲存格位置

                                  ……

        i = i + 1 '彙總頁total往下新增一列 5頁明細表就有5列資料

Next

End Sub

範例六:這是某個風力發電廠在一個月內每天每隔30分鐘所記錄的數據,從下圖我們看到日報表1-日報表28記錄著二月份每天的數據,想要把每一天的工作表從第4列到第51列的數據(半小時一次,一天紀錄48次),累積彙總到工作表ALL,我們可以看到工作表ALL會累積到一千多列(48列x 28天=1344列)。

 

利用EXCEL撰寫巨集程式的好處是程式非常簡短(頂多二、三十行指令),而且可以充分利用EXCEL強大的功能。稍具程式設計基礎的同學可以利用上述幾個實用巨集程式的pattern,舉一反三,提升進一步解決問題的能力。