Board logo

標題: [發問] EXCEL多個工作頁資料彙整 [打印本頁]
作者: jcchiang    時間: 2013-12-9 14:51     標題: EXCEL多個工作頁資料彙整

EXCEL內有3個工作頁
要在Sheet2內找到課戶批號"A120004",抓取上/下各3筆資料,共7筆資料,放到Sheet3
在用Sheet3內的Tcode,到Sheet1將對應的B1/B2/B3/B4資料抓到Sheet3內
再算出B1/B2/B3/B4的失敗率(EX:B1/輸入量)
請問這樣巨集要如何寫,謝謝
[attach]17020[/attach]
[attach]17021[/attach]
[attach]17022[/attach]
[attach]17023[/attach]
作者: Hsieh    時間: 2013-12-9 16:02

回復 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
複製代碼
作者: jcchiang    時間: 2013-12-9 17:47

回復 2# Hsieh
謝謝版大指導,有幾個問題請教一下
1.客戶批號都顯示"A120004"
[attach]17025[/attach]

2.還有一種情形是該客戶批號無TCode時B1~B4就顯示N/A
或是有TCode但在Sheet1找不到此TCode,B1~B4就顯示N/A
作者: Hsieh    時間: 2013-12-9 17:51

本帖最後由 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)



    找不到的情況，你要什麼方式處理？
作者: jcchiang    時間: 2013-12-9 17:59

回復 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
作者: jcchiang    時間: 2013-12-9 21:41

回復 4# Hsieh


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

回復 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
複製代碼
作者: jcchiang    時間: 2013-12-10 10:32

回復 7# Hsieh


    第一個問題已無問題
   第二個問題似乎有錯誤(有TCode但在Sheet1內找不到對應的TCode)
[attach]17028[/attach]
作者: Hsieh    時間: 2013-12-10 10:52

回復 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
作者: jcchiang    時間: 2013-12-10 11:47

回復 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)
   會顯示型態不符合,是否是要另外定義參數型態
作者: Hsieh    時間: 2013-12-10 15:11

  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.       If Not C Is Nothing And TCode <> "" Then
  15.       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)
  16.          Else
  17.       Ar(i + 4) = Array(A.Offset(i, -5).Value, A.Offset(i, 0).Value, A.Offset(i, 2).Value, Cnt, TCode, "NA#", "NA#", "NA#", "NA#", "NA#", "NA#", "NA#", "NA#")
  18.       End If
  19.   Next
  20. End If
  21. With Sheet3
  22. .[A:M].ClearContents
  23. For i = 7 To 13
  24. .Columns(i).NumberFormat = "0.00%"
  25. Next
  26. .[A1].Resize(8, 13) = Application.Transpose(Application.Transpose(Ar))
  27. End With
  28. End Sub
複製代碼
回復 10# jcchiang
作者: jcchiang    時間: 2013-12-10 15:53

回復 11# Hsieh


    最後一個程式碼可以將0改成NA,但第2種情況無法執行(有TCode在Sheet1查無資料)
    目前將B1~B4資料改成
  If Not C Is Nothing And TCode <> "" Then b1 = C.Offset(, 4).Value
   If Not C Is Nothing And TCode <> "" Then b2 = C.Offset(, 6).Value
   If Not C Is Nothing And TCode <> "" Then b3 = C.Offset(, 8).Value
   If Not C Is Nothing And TCode <> "" Then b4 = C.Offset(, 10).Value
    則可執行第2種情形
   感謝H大耐心的指導,謝謝



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