果編又要出 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 資料列上
注意事項

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

13 則留言

  1. Wales Ke Windows other version Google Chrome 72.0.3626.119 on

    您好, 小弟VBA新手,近日苦惱資料筆數較多, 執行速度很慢, 不知您是否可指導該如何修改.
    萬分感謝.

    Sub 效率總表G欄空白填上資料()
    Application.ScreenUpdating = False
    Dim i, j As Integer

    For i = 2 To 32000
    For j = 2 To 32000

    If Worksheets(“Page1”).Cells(i, “G”) = “” And Worksheets(“Page1”).Cells(i, “I”) “全檢” _
    And Worksheets(“Page1”).Cells(i, “F”) = Worksheets(“標準工時表”).Cells(j, “F”) Then
    Worksheets(“Page1”).Cells(i, “G”) = Worksheets(“標準工時表”).Cells(j, “K”)
    ‘Exit For
    End If
    Next
    Next

    Application.ScreenUpdating = True
    End Sub

    • Hi Wales,

      照你的邏輯看起來,你的資料好像一定得這樣用迴圈去檢索
      但一但還到資料量大的時候,一般的機器一定會跑的比較久
      如果你想要全文檢索的速度有所提升,有三個簡單的方法
      1. 把資料分成幾份小的Excel
      2. 把機器加資源,例如:換CPU、加RAM、當然也可以加SSD
      3. 可以把你的程式放到雲端去處理

  2. 您好:

    想請問板主
    假設我今天有幾筆資料 因為我的資料輸出為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,
      你的需求是可以做到的,但資料不要抓任一筆
      建議不是抓第一筆就是抓最後一筆

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

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

歡迎您發表迴響唷!

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料