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

[µo°Ý] EXCEL¦h­Ó¤u§@­¶¸ê®Æ·J¾ã

[µo°Ý] EXCEL¦h­Ó¤u§@­¶¸ê®Æ·J¾ã

EXCEL¤º¦³3­Ó¤u§@­¶
­n¦bSheet2¤º§ä¨ì½Ò¤á§å¸¹"A120004",§ì¨ú¤W/¤U¦U3µ§¸ê®Æ,¦@7µ§¸ê®Æ,©ñ¨ìSheet3
¦b¥ÎSheet3¤ºªºTcode,¨ìSheet1±N¹ïÀ³ªºB1/B2/B3/B4¸ê®Æ§ì¨ìSheet3¤º
¦Aºâ¥XB1/B2/B3/B4ªº¥¢±Ñ²v(EX:B1/¿é¤J¶q)
½Ð°Ý³o¼Ë¥¨¶°­n¦p¦ó¼g,ÁÂÁÂ



¸ê®Æ§ì¨ú.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("«È¤á¥N¸¹", "«È¤á§å¸¹", "Icode", "¿é¤J¶q", "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
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh
ÁÂÁª©¤j«ü¾É,¦³´X­Ó°ÝÃD½Ð±Ð¤@¤U
1.«È¤á§å¸¹³£Åã¥Ü"A120004"


2.ÁÙ¦³¤@ºØ±¡§Î¬O¸Ó«È¤á§å¸¹µLTCode®ÉB1~B4´NÅã¥ÜN/A
©Î¬O¦³TCode¦ý¦bSheet1§ä¤£¨ì¦¹TCode,B1~B4´NÅã¥ÜN/A

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-12-9 17:54 ½s¿è

¦^´_ 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)



    §ä¤£¨ìªº±¡ªp¡A§A­n¤°»ò¤è¦¡³B²z¡H
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh


²Ä¤@­Ó°ÝÃD§Ú¤w¸g¸Ñ¨M,­n¦^ÂЪ©¥D®É­è¦n¬Ý¨ìª©¥D¤]¦^ÂФF
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)

¦pªG§ä¤£¨ì¸ê®Æ´NÅýÄæ¦ìÅã¥ÜN/A

TOP

¦^´_ 4# Hsieh


    ªO¤j¤£¦n·N«ä¦]¬°»°¥h¤W½Ò,©Ò¥H²Ä2­Ó°ÝÃD¨S¼g±o«Ü²M·¡
     1.«È¤á§å¸¹§ì¥X«á,¬Y¤@«È¤á§å¸¹TcodeÄæ¨S¦³½s½X(ªÅ¥Õ),¨º¸Óµ§«È¤á§å¸¹ªºB1~B4´NÅã¥Ü0
     2.¬YTcode¦bSheet1¸Ì§ä¤£¨ì¬Û²Å¸ê®Æ,¨º¸Óµ§¸ê®ÆªºB1~B4´NÅã¥Ü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("«È¤á¥N¸¹", "«È¤á§å¸¹", "Icode", "¿é¤J¶q", "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
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# Hsieh


    ²Ä¤@­Ó°ÝÃD¤wµL°ÝÃD
   ²Ä¤G­Ó°ÝÃD¦ü¥G¦³¿ù»~(¦³TCode¦ý¦bSheet1¤º§ä¤£¨ì¹ïÀ³ªºTCode)
¿ù»~.JPG

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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 9# Hsieh


    ¥Ø«e°ÝÃD¤w¸Ñ¨M,¥t¥~½Ð±Ð¤@­Ó°ÝÃD
   §Ú·Q§â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"
   ¦ý¬O°õ¦æ¨ì
  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)
   ·|Åã¥Ü«¬ºA¤£²Å¦X,¬O§_¬O­n¥t¥~©w¸q°Ñ¼Æ«¬ºA

TOP

        ÀR«ä¦Û¦b : µoµÊ®ð¬Oµu¼ÈªºµoºÆ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD