ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ½Ð°Ý¦p¦ó¼¶¼gVBA¡A¨Ï¯S©w¼Æ­È¦¨¬°©Ò¹ïÀ³©ó¨ä¥L¤u§@ªíÀx¦s®æ¤º®e¨Ã¥[Á` (¤w¸Ñ¨M)

[µo°Ý] ½Ð°Ý¦p¦ó¼¶¼gVBA¡A¨Ï¯S©w¼Æ­È¦¨¬°©Ò¹ïÀ³©ó¨ä¥L¤u§@ªíÀx¦s®æ¤º®e¨Ã¥[Á` (¤w¸Ñ¨M)

[ª©¥DºÞ²z¯d¨¥]
  • Hsieh(2012-4-7 08:58): ½Ð±NÀÉ®×À£ÁY¤W¶Ç

¥»©«³Ì«á¥Ñ sax868 ©ó 2012-5-8 23:06 ½s¿è

¿Ë·Rªº¤j¤j­Ì±ß¦w¡I

¦]¬°§Ú¸Õ¤F¾ã¤Ñªº¨ç¼Æ¨Sµ²ªG¡A¯à¤£¯à³Â·Ð½Ð¤j¤j±Ï§Ú...
½Ð°Ý¦p¦ó¼¶¼gVBA¡A¨Ï¯S©w¼Æ­È©Ò¹ïÀ³©ó¨ä¥L¤u§@ªíÀx¦s®æ¤º®e¨Ã¥[Á`
§Ú¸ÕµÛ¥Î±_ª¬
SUMIFS+MID+SEARCH+OR+VLOOKUP+LEN+LEFT..¼g¨ç¼Æ¦ý¬O¦³»Ùê...µ²ªG¥X¤£¨Ó (Sheet 4 ¦³¦UºØsize°µ°Ñ¦Ò),©Ò¥H§Y«K§Ú³]¥ß¨ç¼Æ¤]µLªk¶]¥X¸ê®Æ(¤j·§¬O§Ú¦Û¤v¤£·|...)
¥Ø«e¹J¨ìªº§xÃø¬°:
1. Color¦@¥|ºØ(ABCD): ³æ¤@color A (1330000_A) ¥i¥Hª½±µ¥Îvlookup¦bColorªí¤¤§ä¥X¹ïÀ³ªº¼Æ¶q¦Ê¤À¤ñ, ¦ý¤@¥¹¼Æ­È¬°½Æ¼Æ(¨Ò:Color A, B ©Î Color B,C,D), «hµLªk­pºâ¡C
2. Size±Æ¦C²Õ¦X¦@258ºØ: ³æ¤@size 15 ¥i¥Hª½±µ¥Îvlookup¦bsizeªí¤¤§ä¥X¹ïÀ³ªº¼Æ¶q¦Ê¤À¤ñ, ¦ý¤@¥¹¼Æ­È¬°½Æ¼Æ(¨Ò:Size 15, 17.5), «hµLªk­pºâ¡C

½Ð°Ý­n¦p¦ó¨Ï:
1. "G:G_FF" (¨Ò: G2_F2: 133000_AA)
2. Model Number 1330000ªºSizeÁ`¦@­n¬O(P2­È)=1330000_15.5Lªº12.94%+1330000_17.5ªº39.59%+1330000_20ªº34.89%+1330000_22.5ªº11.77%+1330000_25ªº0.80%¡A©Ò¥Hµ¥©ó100%                                                               
3. Model Number 1330000ªºColorÁ`¦@­n¬O(Q2­È)=1330000_Aªº22.95%+1330000_Bªº51.83%+1330000_Cªº13.35%+1330000_Dªº11.86%¡A©Ò¥Hµ¥©ó100%
¦]¬°¸ê®Æ¬O3¸U¦hµ§,¥B¨º¨Ç¦Ê¤À¤ñ·|¦]¬°¤£¦Pmodel¦³¤£¦P¤Ø¤o³W®æ¦Ó²£¥Í¤£¦P¼Æ¦C±Æ§Çªº¥i¯à¡C§Úª¦¤å¾ã±ß¬Ý¨Ó¬Ý¥hı±o¥u¯à¼gVBA³Ì¾A·í¤F¡C³Â·Ð½Ð¤j¤j°ª¤â­Ì±Ï§Ú!! «ô°U!!

·P¿E¸U¤À!!

ÁÂÁÂ!!

®z¤k¤l¯d

Book4.rar (10.67 KB) ¦^´_ 1# sax868


    ¹ï¤£°_...¨S¶Ç¦¨¥\...
½Ð¸Ô¨£ªþ¥ó!ÁÂÁÂ!!^^

TOP

¦^´_ 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
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 3# Hsieh

:kiss: :handshake
¯«©_ªº¶W¯Åª©¥D¡A¯uªº¤Ó·P¿E±z¤F!!
§Úªº¤H¥Í²×©ó¤SÅܦ^±m¦âªºÅo!!
·P®¦....

TOP

Book4.rar (21.33 KB) ¦^´_ 3# Hsieh

¶W¯Åª©¥D±z¦n:

¯u¤£¦n·N«ä¡A¦b¬ã¨s¹L©Ò¦³¸ê®Æ«áµo²{¹J¨ì¤@¨Ç²~ÀV(¸Ô¨£ªþ¥ó)¡A¥i¤£¥i¥H³Â·Ð½Ð¶W¯Åª©¥D±Ï§Ú?

1. ½Ð°ÝP & Q Ä檺­È¥i¥H³]¦¨"¦Ê¤À¤ñ"¶Ü?
2. GÄæÁö¦³³]End xl Down¦ý¥u­n¦³ªÅ®æ¡A¼Æ¾Ú´N°±¦b¨º¤£·|¦A©¹¤U¶]¤F¡A½Ð°Ý¥i¥H§ï¦¨¦pªGGÄæªÅ®æ«h¤£¶]OPQ­È¡Aª½¨ìA¨ìNÄæ³£¬OªÅ¥Õªº¤~°±¤î¶]¼Æ¾Ú¶Ü?
3. Size (QÄæ) ¦]¬°¯S®í­È¬° "20-inch wheel" ¦Ó«D¨ä¥L¥¿±`¼Æ¾Ú(15.5L, 17.5, 20, 22.5, 25) ´N¶]¤£¥X¸ê®Æ¡A½Ð°Ý¥i¥H¥[³]¦p: ¥X²{"-inch" «h§ì"-inch«e­±ªº¼Æ­È¶Ü?
    (¦]¬°¨ä¬Û¹ïÀ³ªºSize/Color¤u§@ªí¸Ì¥uÅã¥Ü"20"¦Ó«D"20-inch wheel"¡A©Ò¥HµLªk¶]¼Æ¾Ú¡C¹ï¤£°_!¨t²Î¸ê®Æ¦³ÂI¨â¥ú...)
4. Model number (GÄæ) ²Ä14¦æ¦h­ÓE (1578601E) ¼Æ¾Ú¬°¹s¡A½Ð°Ý¯à§_³]©wModel number ¥u§ì«e­±¤C¦ì¼Æ¦r? (¦]¬°¨ä¬Û¹ïÀ³ªºSize/color¤u§@ªí¥uÅã¥Ü¼Æ¦r)
5. ½Ð°Ý¬O¤£¬O¦³¼Ï¯Ã¤ÀªRªíªº¦æ¡B¦C´NµLªk¶]¼Æ¾Ú©O? (¦]¬°§Ú¥t¦s¤@±iªí¦¨­È´N¥X²{¼Æ¾Ú¤F!)

·P¿E¤£ºÉ¡B¸U¤À·PÁ¡I¡I

®z¤k¤l¯d

TOP

¦^´_ 5# 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], .Cells(.Rows.Count, 1)).SpecialCells(xlCellTypeConstants)

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

  7. Next

  8. With Sheet3 'Color

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

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

  11. Next

  12. With Sheet1 'Original

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

  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(Left(A, 7) & ar(i))

  18.    Next

  19.    For i = 0 To UBound(ay)

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

  21.    Next

  22.    

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

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

  25.    x = 0: y = 0

  26. Next

  27. End With

  28. End With

  29. End With

  30. End Sub
½Æ»s¥N½X
¤£²M·¡²Ä5­Ó°ÝÃD¦b´y­z¬Æ»ò?
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP


³o¯uªº¤Ó¯«©_¤F!!Åý§Ú¤@¤U¤S±q¦aº»¦^¨ì¤F¤Ñ°ó~
¶W¯Åª©¥D¯uªº¤Ó·P¿E±z¤F!±z¬O§Úªº±Ï©R®¦¤H!! ¯uªº«D±`ÁÂÁ±zªºÀ°¦£!!
¦^´_ 6# Hsieh

TOP

        ÀR«ä¦Û¦b : ¨C¤ÑµL©Ò¨Æ¨Æ¡A¬O¤H¥Íªº®ø¶OªÌ¡A¿n·¥¡B¦³¥Î¤~¬O¤H¥Íªº³Ð³yªÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD