麻辣家族討論版版 - Powered by Discuz! Board

標題: VAB 資料比對及轉寫 [打印本頁]

作者: maggie1313 時間: 2016-3-22 15:31 標題: VAB 資料比對及轉寫

大家好，最近要寫一個需求是，有二份資料表，二份資料表(資料表名稱：單頭、單身) 有一個共同的KEY 值(不會重覆)，二份彼此比對後，會將二份裡面的某些欄位轉入到另一個資料表，
但要有一個判斷是，轉入到另一個資料表的值必須先核對KEY值，不知大家懂不懂我的意思......哈哈，有點難表達
[attach]23522[/attach]
[attach]23523[/attach]
[attach]23524[/attach]

因為寫了一星期，卻發現愈寫沒頭緒一直寫不出來，請大家可以給我個方向，謝謝

作者: luhpro 時間: 2016-3-25 00:29

本帖最後由 luhpro 於 2016-3-25 00:38 編輯

回復 1# maggie1313
一般若遇到 "有一個共同的KEY 值(不會重覆)" 這類的需求,
我會優先使用 Directory 函數來實現.

因為你沒提供範例Excel檔案,
所以我另外做一個,
你可以參照修改實現你的需求.
[attach]23570[/attach]

Private Sub cbMerge_Click()
Dim lRow&
Dim sStr$
Dim vD1, vD2
Set vD1 = CreateObject("Scripting.Dictionary")
Set vD2 = CreateObject("Scripting.Dictionary")
Sheets("合併").Range([B2], [C100]).Clear
With Sheets("索引一")
lRow = 2
While .Cells(lRow, 1) <> ""
vD1(CStr(.Cells(lRow, 1))) = .Cells(lRow, 2)
lRow = lRow + 1
Wend
End With
With Sheets("索引二")
lRow = 2
While .Cells(lRow, 1) <> ""
vD2(CStr(.Cells(lRow, 1))) = .Cells(lRow, 2)
lRow = lRow + 1
Wend
End With
With Sheets("合併")
lRow = 2
While .Cells(lRow, 1) <> ""
sStr = .Cells(lRow, 1)
.Cells(lRow, 2) = vD1(sStr)
.Cells(lRow, 3) = vD2(sStr)
lRow = lRow + 1
Wend
End With
End Sub

複製代碼

[attach]23571[/attach]

補充:
如果需要用到整列中不只一欄的資料,
可以索引列號:
vD1(CStr(.Cells(lRow, 1))) = lRow
再用
Sheets("合併").Cells(lRow, 2) = Sheets("索引一").Cells(vD1(sStr), 欄號)
來取得想要的資料.

另外,建議大家開題文中儘量能一併提供已經打好範例資料的Excel檔案,
這樣回文的人才不用還要自己依照圖片內容一個一個輸入資料建Excel檔案,
可以把時間專注在如何實現你的需求上.

作者: kim223824 時間: 2016-3-25 20:39

如果編號有重複的，姓名不同或是城市不同呢?

作者: luhpro 時間: 2016-3-25 23:33

回復 3# kim223824
那就改以編號_姓名或是編號_城市的組合鍵值來做區分.
其中的 _ 可以改為任何系統可接受並足資區分的符號或文數字來取代.

作者: maggie1313 時間: 2016-3-28 17:00

[attach]23624[/attach]回復 6# maggie1313
你好，我想在請問一下，一開始我必須先要把KEY值COPY到合併的資料中，先當KEY值
這樣的方式我要怎樣用?[attach]23626[/attach]

作者: maggie1313 時間: 2016-3-29 08:27

回復 7# maggie1313
[attach]23637[/attach]
因為不確定是否有上傳附件成功，故重新上傳一次

作者: luhpro 時間: 2016-3-29 21:46

本帖最後由 luhpro 於 2016-3-29 21:51 編輯

回復 5# maggie1313
你沒有提供正確結果範例,所以我只能用猜的:

Sub nn()
Dim lRow&
Dim wsSou As Worksheet, wsTar As Worksheet
Dim vD
Set vD = CreateObject("Scripting.Dictionary")
Set wsSou = Sheets("EF單身")
Set wsTar = Sheets("合併資料")
lRow = 2
With wsSou
While .Cells(lRow, 8) <> ""
wsTar.Cells(lRow, 1) = .Cells(lRow, 8)
wsTar.Cells(lRow, 2) = .Cells(lRow, 9) & "-" & .Cells(lRow, 10)
vD(CStr(.Cells(lRow, 8) & "-" & .Cells(lRow, 9) & "-" & .Cells(lRow, 10))) = lRow
lRow = lRow + 1
Wend
End With
End Sub

複製代碼

作者: maggie1313 時間: 2016-3-30 11:12

回復 7# luhpro
你好，感謝你的解答。測試後是OK的。但現在我有另個問題想請問，如圖[attach]23643[/attach]
就是我們的合併資料要從第三行開始寫入資料，但後來發現有些問題，想在請教。(如果時間允許是否可簡單加入註解，這樣我就可以自已試著調整才不用每次都麻煩你們)
想在請問二個進階的問題。
1.：延續一開始所問的，如果我有了KEY值，後續我會開始把「EF單頭」跟「EF單身」的欄位開始丟到「合併資料」，那這樣的話我是一樣是依照一開始的程式套用嗎?
2：如圖的表達[attach]23644[/attach]
[attach]23645[/attach]
[attach]23646[/attach]
謝謝大家幫忙

作者: maggie1313 時間: 2016-3-30 11:14

回復 8# maggie1313
補上要從第三行插入資料的圖片[attach]23647[/attach]

作者: maggie1313 時間: 2016-3-30 13:59

回復 8# maggie1313
不好意思，關於第三行開始圖片沒有附加成功，重新附件[attach]23648[/attach][attach]23649[/attach]
[attach]23650[/attach]

作者: maggie1313 時間: 2016-3-30 14:04

回復 10# maggie1313
不好意思，電腦異常，故重新發送關於「合併資料」從第三行開始的圖片[attach]23651[/attach][attach]23652[/attach]
[attach]23653[/attach]

作者: luhpro 時間: 2016-4-1 03:08

本帖最後由 luhpro 於 2016-4-1 03:14 編輯

回復 luhpro
你好，感謝你的解答。測試後是OK的。但現在我有另個問題想請問，如圖
就是我們的合併資料要 ...
maggie1313 發表於 2016-3-30 11:12

同我 2# 所述,
這裡依你結果資料來看只需要抓單一資料,
故 Dictionary 直接存該資料.

Sub DATABASE()
Dim lRow& ' 列號
Dim wsSou1 As Worksheet, wsSou2 As Worksheet, wsTar As Worksheet '工作表
Dim vD
Set vD = CreateObject("Scripting.Dictionary")
Set wsSou1 = Sheets("EF單頭")
Set wsSou2 = Sheets("EF單身")
Set wsTar = Sheets("合併資料")
With wsTar ' With wsTar 與 End With 間若有以 . 開頭的指令, 其 . 等同 wsTar (亦即 Sheets("合併資料"))
.Range(.[A2], .[D50]).Clear ' 等同 wsTar.Range(wsTar.[A2], wsTar.[D50]).Clear .[A2]=wsTar.Range("A2")
End With
lRow = 1 ' 因為沒有標題列, 故從第1列開始抓
With wsSou1 ' EF單頭
While .Cells(lRow, 8) <> "" ' Cells(列號, 欄號) 欄號 8 = H 攔, 逐列抓取資料
vD(CStr(.Cells(lRow, 8) & "-" & .Cells(lRow, 9))) = .Cells(lRow, 14) ' vD("ODMGET_M00602-201602220001") = 4355 若只要抓金額,那就直接放金額
lRow = lRow + 1
Wend
End With
lRow = 2
With wsSou2 ' EF單身
wsTar.Range(wsTar.[A2], wsTar.[D20]).Clear ' 清除上次產生的資料, 正式使用可以刪掉此行
While .Cells(lRow, 8) <> ""
wsTar.Cells(lRow, 1) = .Cells(lRow, 8)
wsTar.Cells(lRow, 2) = .Cells(lRow, 9) & "-" & .Cells(lRow, 10)
' 用 "-" 是因為鍵(Key, 索引)值內沒有 "-", 故以其做為兩攔資料的銜接符號,避免類似111222333 加上 123456 與 1112223331 加上 23456 兩者會視為相同的問題.
wsTar.Cells(lRow, 3) = vD(CStr(.Cells(lRow, 8) & "-" & .Cells(lRow, 9))) ' 從 Dictionary 中索引 EF單頭的金額
wsTar.Cells(lRow, 4) = .Cells(lRow, 18) ' 從 R 攔(欄號 = 18)直接抓金額
lRow = lRow + 1
Wend
End With
End Sub

複製代碼

[attach]23672[/attach]

補充 : 剛剛才看到第二頁的發文,
所以列號起始數字沒變,
你可以直接改成你需要的數字就可以了.

作者: maggie1313 時間: 2016-4-1 11:10

回復 13# luhpro
謝謝指導，有加入註解比較清楚了。如有問題再請教。萬分感謝

作者: maggie1313 時間: 2016-4-1 11:42

回復 13# luhpro
目前測試都OK。還有幾個問題想要在請教
一、如果來源單頭的資料還有多筆資料要載入「合併資料」的話，是要在多設定多個變數嗎?例：VD2
因為現在我這樣設定是OK的 [attach]23674[/attach]
二、另外程式中有一段我不是很清楚用意。但我也沒有寫上去，但也是可行，沒有錯誤訊息。[attach]23675[/attach]
三、另外我想要在請教一個問題，假設我的合併資料都齊全了，要設計一個查詢的表單畫面，讓人員查詢後，將查詢的結果另外在產生一個頁面，但內容是跟原有的「合併資料」頁簽相同[attach]23676[/attach]

作者: luhpro 時間: 2016-4-2 22:14

本帖最後由 luhpro 於 2016-4-2 22:28 編輯

回復 luhpro
目前測試都OK。還有幾個問題想要在請教
一、如果來源單頭的資料還有多筆資料要載入「合併資 ...
maggie1313 發表於 2016-4-1 11:42

我在 2# 有說了喔 :
如果需要用到整列中不只一欄的資料,
可以索引列號:
vD1(CStr(.Cells(lRow, 1))) = lRow
再用
Sheets("合併").Cells(lRow, 2) = Sheets("索引一").Cells(vD1(sStr), 欄號)
來取得想要的資料.
只要變更欄號就可以抓該筆資料中的任一個資料,
重點是要先取得該筆資料的列號,
查詢時也是以 Key 值(列號)來索引資料的.

所以:
vD1(CStr(.Cells(lRow, 8) & "-" & .Cells(lRow, 9))) = .Cells(lRow, 14)
vD2(CStr(.Cells(lRow, 8) & "-" & .Cells(lRow, 9))) = .Cells(lRow, 15)
...
MixDB.Cells(lRow, 3) = vD1(CStr(.Cells(lRow, 8) & "-" & .Cells(lRow, 9)))
MixDB.Cells(lRow, 5) = vD2(CStr(.Cells(lRow, 8) & "-" & .Cells(lRow, 9)))

可以改成:

vD1(CStr(.Cells(lRow, 8) & "-" & .Cells(lRow, 9))) = lRow
...
MixDB.Cells(lRow, 3) = EFhand.Cells(VD1(CStr(MixDB.Cells(lRow, 1) & "-" & Right(MixDB.Cells(lRow, 2), 12))),14) ' 只抓前 12 個字(201602220001)
MixDB.Cells(lRow, 5) = EFhand.Cells(VD1(CStr(MixDB.Cells(lRow, 1) & "-" & Right(MixDB.Cells(lRow, 2), 12))),15)
程式變很長,但實際可依你需求善用 With 與 End With 簡化及利於判讀.

至於 VD2 可以留給 MixDB 用 .

三、另外我想要在請教一個問題...

vD2(CStr(MixDB.Cells(lRow, 1) & "~" & MixDB.Cells(lRow, 2) = lRow ' 請留意同樣為避免誤判,這裡改用 "~" 而非 "-" 來做區隔.
則單頭金額可用 MixDB.Cells(VD2(CStr(TextBox1 & "~" & TextBox2 & "-" & TextBox3)), 3) 來抓 ' 假設 TextBox? 分別放單身單別、單身單號及序號

二、另外程式中有一段我不是很清楚用意...

這只是重複驗證執行結果前清資料用的,
看你實際的需求不見得要放.

作者: maggie1313 時間: 2016-4-6 08:52

回復 7# luhpro
了解，謝謝。我會在試試

作者: maggie1313 時間: 2016-4-6 17:51

回復 16# luhpro
感謝你的協助與指教。關於第一個問題我決定還是用變數的方式，因為我是初學者第一次寫，這樣我比較清楚。但還是很感謝
關於第三個問題，我不是很清楚寫法。我已經有設計好一個userform的查詢畫面，裡面會有查詢的欄位目前設計三欄。
人員只要輸入查詢的資料(ps.有可能只輸入一筆，二筆，或三筆不一定)，但系統要住他所輸入的資料穩和，才會將查詢的資料「合併資料」內容複製到「查詢」的頁簽。
[attach]23730[/attach][attach]23731[/attach][attach]23732[/attach]
[attach]23733[/attach]
作者: luhpro 時間: 2016-4-8 00:04

回復 luhpro
...
關於第三個問題，我不是很清楚寫法。我已經有設計好一個userform的查詢畫面，裡面會有查詢的欄位目前設計三欄。
人員只要輸入查詢的資料(ps.有可能只輸入一筆，二筆，或三筆不一定)，但系統要住他所輸入的資料穩和，才會將查詢的資料「合併資料」內容複製到「查詢」的頁簽。 ...
maggie1313 發表於 2016-4-6 17:51

Excel 有個進階篩選的功能, 你可以試試 :
[attach]23754[/attach]
先把 User 輸入的條件依序放入 C14 ~ E14,
再執行下列指令:
[A1].CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=[C13:E14], CopyToRange:=[A22], Unique:=False
複製代碼
最後再把結果 [A22].CurrentRegion Copy 過去即可.
作者: maggie1313 時間: 2016-4-8 14:59

回復 19# luhpro
你好，感謝你的回覆。但老實說我研究了一下，但還不是很了解你的意思，因為此份資料是要給前端的USER使用的，我才設定一個USERFORM的方式。
所以你所說的EXCEL中的篩選也有適用嗎?

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)