Excel 活頁簿 : 工作表資料合併匯整 Part I

11

果編又要出 EXCEL 工作表資料合併匯整 的任務了

交付任務的是一位叫做 12 的格友,讓我們來看看此次任務的分析

資料量少的情況下,可以容易的利用 EXCEL 本身提供的 If, Index, Match, Copy 等函數來實現

資料量多的情況下,雖然也可以用 EXCEL 函數實作出來,但在重新開啟EXCEL時,就會發生悲劇

所以建議大家乖乖的用VBA來實作,雖然跑的時間比較久

重點是 EXCEL 不會有開不起來的問題 (⌒▽⌒)

工作表資料合併匯整

任務概要

  1. 一個EXCEL 活頁簿 (Workbook) 有二個工作表 (Worksheet) 分別是 000 與 001
  2. 每個工作表皆有 9 個欄位 (Cell) : A 欄 至 I 欄
  3. 000 每個欄位皆有值,001 則只有 A 欄與 B欄二個欄位有值

工作表資料合併匯整

任務目標

  1. 用工作表 000 中的 A 欄與 B 欄做為搜尋比對工作表 001 的關鍵字(Key)
  2. 如果有搜尋比對到,則將工作表 000 中 C 欄至 I 欄的值,複製到工作表 001 所比對到的資料列(Row)

利用 VBA 進行 Excel 工作表資料合併匯整

操作步驟

  1. 首先按下快鍵盤快速鍵 ALT + F11,接著會開啟 Microsoft Visual Basic for Applications 視窗
  2. 接著點擊上方選單的 插入(I) > 模組(M),接著把下方的程式碼貼入程式碼的視窗中
  3. 再來按下鍵盤上的 F5 按鍵,會跳出一個巨集儲存的視窗,最後按下 ⌈執行⌋ 按鍵,就會以工作表 000 的 A、B欄資料與工作表 001 的 A、B欄進行搜尋比對,若有比對到符合的資料,則會將 000 的資料複製到符合的 001 資料列上
Sub CopyDataBlocks()
    Dim sCell, tCell As Range
    Dim sValue As String
    Dim startRowNum As Long
    
    Dim SourceSheet, TargetSheet As Worksheet

    Set SourceSheet = Sheets("000")
    Set TargetSheet = Sheets("001")
    
    startRowNum = 2
    
    For Each sCell In SourceSheet.Range("A2:A" & SourceSheet.Cells.SpecialCells(xlCellTypeLastCell).Row)
        sValue = sCell.Value & "_" & sCell.Offset(, 1).Value
        For Each tCell In TargetSheet.Range("A" & startRowNum & ":A" & TargetSheet.Cells.SpecialCells(xlCellTypeLastCell).Row)
            If sValue = tCell.Value & "_" & tCell.Offset(, 1).Value Then
                SourceSheet.Range("C" & sCell.Row & ":I" & sCell.Row).Copy TargetSheet.Range("C" & tCell.Row & ":I" & tCell.Row)
                startRowNum = tCell.Row
                Exit For
            End If
        Next
    Next
End Sub

 

注意事項

  1. 所有的工作表(Worksheets) 的資料都必需由欄 A1 開始
  2. 所有的工作表(Worksheets) 的資料都必需是同樣的結構
  3. 所有的工作表(Worksheets) 的資料都必需先進行由小到大的排序
Share.

11 篇迴響

  1. 您好:

    想請問板主
    假設我今天有幾筆資料 因為我的資料輸出為115ms 所以一秒會有八筆資料
    A欄 B欄 C欄 D欄
    2017/5/5 12:00:12 12 13
    2017/5/5 12:00:12 13 14
    2017/5/5 12:00:12 14 15
    2017/5/5 12:00:12 15 16
    2017/5/5 12:00:12 12 13
    2017/5/5 12:00:12 13 14
    2017/5/5 12:00:12 14 15
    2017/5/5 12:00:12 15 16
    2017/5/5 12:00:13 16 17
    2017/5/5 12:00:12 17 18
    .
    .
    .

    有沒有辦法利用表單 輸入欄位 填入日期與時間 就可以讀取到C和D欄的值 再將二值做計算
    但我的一秒會有八筆 可以抓取到任一筆做計算就好嗎
    例如 輸入2017/5/5 12:00:12
    12+13

    輸出結果25

    麻煩板主了

    • Hi Ming-Yen,
      你的需求是可以做到的,但資料不要抓任一筆
      建議不是抓第一筆就是抓最後一筆

      另外,你的表單在那 ? 長什麼樣 ?

  2. sorry…因為我有換過資料內容,沒有改到程式碼…剛剛已測試完畢成功,謝謝你的幫忙!!

歡迎您發表迴響唷!