返回列表 上一主題 發帖

[發問] EXCEL多個工作頁資料彙整

[發問] EXCEL多個工作頁資料彙整

EXCEL內有3個工作頁
要在Sheet2內找到課戶批號"A120004",抓取上/下各3筆資料,共7筆資料,放到Sheet3
在用Sheet3內的Tcode,到Sheet1將對應的B1/B2/B3/B4資料抓到Sheet3內
再算出B1/B2/B3/B4的失敗率(EX:B1/輸入量)
請問這樣巨集要如何寫,謝謝
sheet1.jpg
2013-12-9 14:42

sheet2

sheet2.jpg
2013-12-9 14:42

sheet3

sheet3.jpg
2013-12-9 14:43

資料抓取.zip (8.06 KB)

回復 1# jcchiang
  1. Sub ex()
  2. Dim Ar(0 To 7), A As Range, C As Range, TCode, Cnt
  3. Ar(0) = Array("客戶代號", "客戶批號", "Icode", "輸入量", "TCode", "B1", "B1失敗", "B2", "B2失敗", "B3", "B3失敗", "B4", "B4失敗")
  4. Set A = Sheet2.[F:F].Find("A120004", lookat:=xlWhole)
  5. If Not A Is Nothing Then
  6.    For i = -3 To 3
  7.    TCode = A.Offset(i, 7).Value
  8.    Set C = Sheet1.[D:D].Find(TCode, lookat:=xlWhole)
  9.    b1 = C.Offset(, 4).Value
  10.    b2 = C.Offset(, 6).Value
  11.    b3 = C.Offset(, 8).Value
  12.    b4 = C.Offset(, 10).Value
  13.    Cnt = A.Offset(i, 4).Value
  14.       Ar(i + 4) = Array(A.Offset(i, -5).Value, A.Value, A.Offset(i, 2).Value, Cnt, TCode, b1, b1 / Cnt, b2, b2 / Cnt, b3, b3 / Cnt, b4, b4 / Cnt)
  15.    Next
  16. End If
  17. With Sheet3
  18. .[A:M].ClearContents
  19. For i = 7 To 13
  20. .Columns(i).NumberFormat = "0.00%"
  21. Next
  22. .[A1].Resize(8, 13) = Application.Transpose(Application.Transpose(Ar))
  23. End With
  24. End Sub
複製代碼
學海無涯_不恥下問

TOP

回復 2# Hsieh
謝謝版大指導,有幾個問題請教一下
1.客戶批號都顯示"A120004"
客戶批號.JPG
2013-12-9 17:41


2.還有一種情形是該客戶批號無TCode時B1~B4就顯示N/A
或是有TCode但在Sheet1找不到此TCode,B1~B4就顯示N/A

TOP

本帖最後由 Hsieh 於 2013-12-9 17:54 編輯

回復 3# jcchiang
 Ar(i + 4) = Array(A.Offset(i, -5).Value, A.OffSet(i,0).Value, A.Offset(i, 2).Value, Cnt, TCode, b1, b1 / Cnt, b2, b2 / Cnt, b3, b3 / Cnt, b4, b4 / Cnt)



    找不到的情況,你要什麼方式處理?
學海無涯_不恥下問

TOP

回復 4# Hsieh


第一個問題我已經解決,要回覆版主時剛好看到版主也回覆了
Ar(i + 4) = Array(A.Offset(i, -5).Value, A.Offset(i, 0).Value, A.Offset(i, 2).Value, Cnt, TCode, b1, b1 / Cnt, b2, b2 / Cnt, b3, b3 / Cnt, b4, b4 / Cnt)

如果找不到資料就讓欄位顯示N/A

TOP

回復 4# Hsieh


    板大不好意思因為趕去上課,所以第2個問題沒寫得很清楚
     1.客戶批號抓出後,某一客戶批號Tcode欄沒有編碼(空白),那該筆客戶批號的B1~B4就顯示0
     2.某Tcode在Sheet1裡找不到相符資料,那該筆資料的B1~B4就顯示0

TOP

回復 6# jcchiang
  1. Sub ex()
  2. Dim Ar(0 To 7), A As Range, C As Range, TCode, Cnt
  3. Ar(0) = Array("客戶代號", "客戶批號", "Icode", "輸入量", "TCode", "B1", "B1失敗", "B2", "B2失敗", "B3", "B3失敗", "B4", "B4失敗")
  4. Set A = Sheet2.[F:F].Find("A120004", lookat:=xlWhole)
  5. If Not A Is Nothing Then
  6.    For i = -3 To 3
  7.       TCode = A.Offset(i, 7).Value
  8.       Set C = Sheet1.[D:D].Find(TCode, lookat:=xlWhole)
  9.       If Not C Is Nothing And C <> "" Then b1 = C.Offset(, 4).Value Else b1 = 0
  10.       If Not C Is Nothing And C <> "" Then b2 = C.Offset(, 6).Value Else b2 = 0
  11.       If Not C Is Nothing And C <> "" Then b3 = C.Offset(, 8).Value Else b3 = 0
  12.       If Not C Is Nothing And C <> "" Then b4 = C.Offset(, 10).Value Else b4 = 0
  13.       Cnt = A.Offset(i, 4).Value
  14.       Ar(i + 4) = Array(A.Offset(i, -5).Value, A.Offset(i, 0).Value, A.Offset(i, 2).Value, Cnt, TCode, b1, b1 / Cnt, b2, b2 / Cnt, b3, b3 / Cnt, b4, b4 / Cnt)
  15.   Next
  16. End If
  17. With Sheet3
  18. .[A:M].ClearContents
  19. For i = 7 To 13
  20. .Columns(i).NumberFormat = "0.00%"
  21. Next
  22. .[A1].Resize(8, 13) = Application.Transpose(Application.Transpose(Ar))
  23. End With
  24. End Sub
複製代碼
學海無涯_不恥下問

TOP

回復 7# Hsieh


    第一個問題已無問題
   第二個問題似乎有錯誤(有TCode但在Sheet1內找不到對應的TCode)
錯誤.JPG
2013-12-10 10:17

TOP

回復 8# jcchiang

   If Not C Is Nothing And Tcode<> "" Then b1 = C.Offset(, 4).Value Else b1 = 0
      If Not C Is Nothing And  Tcode<> "" Then b2 = C.Offset(, 6).Value Else b2 = 0
      If Not C Is Nothing And Tcode<> "" Then b3 = C.Offset(, 8).Value Else b3 = 0
      If Not C Is Nothing And  Tcode<> "" Then b4 = C.Offset(, 10).Value Else b4 = 0
學海無涯_不恥下問

TOP

回復 9# Hsieh


    目前問題已解決,另外請教一個問題
   我想把0改為顯示"N/A"
    If Not C Is Nothing And TCode <> "" Then b1 = C.Offset(, 4).Value Else b1 = "N/A"
   If Not C Is Nothing And TCode <> "" Then b2 = C.Offset(, 6).Value Else b2 = "N/A"
   If Not C Is Nothing And TCode <> "" Then b3 = C.Offset(, 8).Value Else b3 = "N/A"
   If Not C Is Nothing And TCode <> "" Then b4 = C.Offset(, 10).Value Else b4 = "N/A"
   但是執行到
  Ar(i + 4) = Array(A.Offset(i, -5).Value, A.Offset(i, 0).Value, A.Offset(i, 2).Value, Cnt, TCode, b1, b1 / Cnt, b2, b2 / Cnt, b3, b3 / Cnt, b4, b4 / Cnt)
   會顯示型態不符合,是否是要另外定義參數型態

TOP

        靜思自在 : 人要自愛,才能愛普天下的人。
返回列表 上一主題