Board logo

標題: [發問] 請問如何撰寫VBA，使特定數值成為所對應於其他工作表儲存格內容並加總 (已解決) [打印本頁]
作者: sax868    時間: 2012-4-7 01:50     標題: 請問如何撰寫VBA，使特定數值成為所對應於其他工作表儲存格內容並加總 (已解決)

本帖最後由 sax868 於 2012-5-8 23:06 編輯

親愛的大大們晚安！

因為我試了整天的函數沒結果，能不能麻煩請大大救我...
請問如何撰寫VBA，使特定數值所對應於其他工作表儲存格內容並加總
我試著用巢狀
SUMIFS+MID+SEARCH+OR+VLOOKUP+LEN+LEFT..寫函數但是有障礙...結果出不來 (Sheet 4 有各種size做參考),所以即便我設立函數也無法跑出資料(大概是我自己不會...)
目前遇到的困難為:
1. Color共四種(ABCD): 單一color A (1330000_A) 可以直接用vlookup在Color表中找出對應的數量百分比, 但一旦數值為複數(例:Color A, B 或 Color B,C,D), 則無法計算。
2. Size排列組合共258種: 單一size 15 可以直接用vlookup在size表中找出對應的數量百分比, 但一旦數值為複數(例:Size 15, 17.5), 則無法計算。

請問要如何使:
1. "G:G_FF" (例: G2_F2: 133000_AA)
2. Model Number 1330000的Size總共要是(P2值)=1330000_15.5L的12.94%+1330000_17.5的39.59%+1330000_20的34.89%+1330000_22.5的11.77%+1330000_25的0.80%，所以等於100%                                                               
3. Model Number 1330000的Color總共要是(Q2值)=1330000_A的22.95%+1330000_B的51.83%+1330000_C的13.35%+1330000_D的11.86%，所以等於100%
因為資料是3萬多筆,且那些百分比會因為不同model有不同尺寸規格而產生不同數列排序的可能。我爬文整晚看來看去覺得只能寫VBA最適當了。麻煩請大大高手們救我!! 拜託!!

感激萬分!!

謝謝!!

弱女子留
作者: sax868    時間: 2012-4-7 18:27

[attach]10336[/attach]回復 1# sax868


    對不起...沒傳成功...
請詳見附件!謝謝!!^^
作者: Hsieh    時間: 2012-4-7 19:22

回復 2# sax868
  1. Sub ex()
  2. Dim A As Range
  3. Set d = CreateObject("Scripting.Dictionary")
  4. With Sheet2 'Size
  5. For Each A In .Range(.[A2], .[A2].End(xlDown))
  6.    d(A & A.Offset(, 2)) = A.Offset(, 3).Value
  7. Next
  8. With Sheet3 'Color
  9. For Each A In .Range(.[A2], .[A2].End(xlDown))
  10.    d(A & A.Offset(, 2)) = A.Offset(, 3).Value
  11. Next
  12. With Sheet1 'Original
  13. For Each A In .Range(.[G2], .[G2].End(xlDown))
  14. ar = Split(Replace(A.Offset(, 1), " ", ""), ",") 'Color
  15. ay = Split(Replace(A.Offset(, 3), " ", ""), ",") 'Size
  16.    For i = 0 To UBound(ar)
  17.        y = y + d(A & ar(i))
  18.    Next
  19.    For i = 0 To UBound(ay)
  20.       x = x + d(A & ay(i))
  21.    Next
  22.    
  23.    A.Offset(, 8) = A & A.Offset(, -1)
  24.    A.Offset(, 9).Resize(, 2) = Array(y, x)
  25.    x = 0: y = 0
  26. Next
  27. End With
  28. End With
  29. End With
  30. End Sub
複製代碼
作者: sax868    時間: 2012-4-8 00:02

回復 3# Hsieh

:kiss: :handshake
神奇的超級版主，真的太感激您了!!
我的人生終於又變回彩色的囉!!
感恩....
作者: sax868    時間: 2012-4-8 15:49

[attach]10346[/attach]回復 3# Hsieh

超級版主您好:

真不好意思，在研究過所有資料後發現遇到一些瓶頸(詳見附件)，可不可以麻煩請超級版主救我?

1. 請問P & Q 欄的值可以設成"百分比"嗎?
2. G欄雖有設End xl Down但只要有空格，數據就停在那不會再往下跑了，請問可以改成如果G欄空格則不跑OPQ值，直到A到N欄都是空白的才停止跑數據嗎?
3. Size (Q欄) 因為特殊值為 "20-inch wheel" 而非其他正常數據(15.5L, 17.5, 20, 22.5, 25) 就跑不出資料，請問可以加設如: 出現"-inch" 則抓"-inch前面的數值嗎?
    (因為其相對應的Size/Color工作表裡只顯示"20"而非"20-inch wheel"，所以無法跑數據。對不起!系統資料有點兩光...)
4. Model number (G欄) 第14行多個E (1578601E) 數據為零，請問能否設定Model number 只抓前面七位數字? (因為其相對應的Size/color工作表只顯示數字)
5. 請問是不是有樞紐分析表的行、列就無法跑數據呢? (因為我另存一張表成值就出現數據了!)

感激不盡、萬分感謝！！

弱女子留
作者: Hsieh    時間: 2012-4-8 22:46

回復 5# sax868
  1. Sub ex()

  3. Dim A As Range

  5. Set d = CreateObject("Scripting.Dictionary")

  7. With Sheet2 'Size

  9. For Each A In .Range(.[A2], .Cells(.Rows.Count, 1)).SpecialCells(xlCellTypeConstants)

  11.    d(A & A.Offset(, 2)) = A.Offset(, 3).Value

  13. Next

  15. With Sheet3 'Color

  17. For Each A In .Range(.[A2], .Cells(.Rows.Count, 1)).SpecialCells(xlCellTypeConstants)

  19.    d(A & A.Offset(, 2)) = A.Offset(, 3).Value

  21. Next

  23. With Sheet1 'Original

  25. For Each A In .Range(.[G2], .Cells(.Rows.Count, 7)).SpecialCells(xlCellTypeConstants)

  27. ar = Split(Replace(A.Offset(, 1), " ", ""), ",") 'Color

  29. ay = Split(Replace(A.Offset(, 3), " ", ""), ",") 'Size

  31.    For i = 0 To UBound(ar)

  33.        y = y + d(Left(A, 7) & ar(i))

  35.    Next

  37.    For i = 0 To UBound(ay)

  39.       x = x + d(Left(A, 7) & Split(ay(i), "-")(0))

  41.    Next

  43.    

  45.    A.Offset(, 8) = A & A.Offset(, -1)

  47.    A.Offset(, 9).Resize(, 2) = Array(Format(y, "#.##%"), Format(x, "#.##%"))

  49.    x = 0: y = 0

  51. Next

  53. End With

  55. End With

  57. End With

  59. End Sub
複製代碼
不清楚第5個問題在描述甚麼?
作者: sax868    時間: 2012-4-8 23:50


這真的太神奇了!!讓我一下又從地獄回到了天堂~
超級版主真的太感激您了!您是我的救命恩人!! 真的非常謝謝您的幫忙!!
回復 6# Hsieh



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