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

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

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

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

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

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

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

任務概要

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

任務目標

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

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

操作步驟

首先按下快鍵盤快速鍵 ALT + F11，接著會開啟 Microsoft Visual Basic for Applications 視窗

接著點擊上方選單的 插入(I) > 模組(M)，接著把下方的程式碼貼入程式碼的視窗中

再來按下鍵盤上的 F5 按鍵，會跳出一個巨集儲存的視窗，最後按下 ⌈執行⌋ 按鍵，就會以工作表 000 的 A、B欄資料與工作表 001 的 A、B欄進行搜尋比對，若有比對到符合的資料，則會將 000 的資料複製到符合的 001 資料列上

注意事項

所有的工作表(Worksheets) 的資料都必需由欄 A1 開始

所有的工作表(Worksheets) 的資料都必需是同樣的結構

所有的工作表(Worksheets) 的資料都必需先進行由小到大的排序

13 則留言

Wales Ke on 2019/03/28 14:40

您好, 小弟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

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

板主您好:

方便用fb私訊詢問嗎

謝謝

- Jason翰 on 2017/05/12 13:14
  
  Hi Ming-Yen,
  你可以用FB私訊問喔
Ming-Yen Shen on 2017/05/11 17:06

您好:

想請問板主
假設我今天有幾筆資料因為我的資料輸出為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

麻煩板主了

- Jason翰 on 2017/05/11 17:14
  
  Hi Ming-Yen,
  你的需求是可以做到的，但資料不要抓任一筆
  建議不是抓第一筆就是抓最後一筆
  
  另外，你的表單在那 ? 長什麼樣 ?
許展昭 on 2017/05/01 15:09

不好意思可以請教一些問題嗎

許展昭 on 2017/04/30 09:52

請問方便可以私下問嗎@@ 謝謝

- Jason翰 on 2017/05/01 16:23
  
  展昭,
  歡迎提問喔
12 on 2016/04/21 12:02

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

- Jason翰 on 2016/04/21 13:09
  
  Hi 12,
  有幫上你的忙就好 ^^
12 on 2016/04/21 11:07

hello~我又試了一次，但發現…資料有些沒有貼過來….

- Jason翰 on 2016/04/21 11:52
  
  Hi 12,
  我們是用同一份EXCEL做實驗的
  告訴我是那一列的資料沒有複製過來