[µo°Ý] ¹ïÀ³Äæ¦ì°ÝÃD

starbox520

­n«ç»ò±N¦p¹Ïªº¹ïÀ³Äæ¦ì¡AÅܦ¨¹³¤u§@ªí2³£¼g¦b¦P¤@­ÓÀx¦s®æ¸Ì (0¥H¥~ªº¤~­n)

¥i¥H³]¸m¤@¶}±ÒEXCEL´N¦Û°Ê¶]¦n¶Ü?

¤U¸ü (19.16 KB)

2016-12-9 15:58

test300.rar (7.49 KB)

¤U¸ü¦¸¼Æ: 9

2016-12-9 15:59

starbox520

1. Private Sub Workbook_Open()
   
2.     Dim arA, arB, x%, y%, s$
   
3.     Sheets("¤u§@ªí2").Activate
   
4.     [a1].CurrentRegion.Offset(1).ClearContents
   
5.     arA = Sheets("¤u§@ªí1").UsedRange
   
6.     ReDim arB(1 To UBound(arA, 2) - 1, 1 To 2)
   
7.     For y = 2 To UBound(arA, 2)
   
8.         arB(y - 1, 1) = arA(1, y)
   
9.         For x = 3 To UBound(arA)
   
10.             If arA(x, y) <> 0 Then
    
11.                 s = IIf(arA(x, y) > 0, arA(x, 1) & "+" & arA(x, y), arA(x, 1) & arA(x, y))
    
12.                 arB(y - 1, 2) = IIf(arB(y - 1, 2) = "", s, arB(y - 1, 2) & " , " & s)
    
13.             End If
    
14.         Next
    
15.     Next
    
16.     [a2].Resize(UBound(arB), 2) = arB
    
17. End Sub

½Æ»s¥N½X

c_c_lai

¦^´_ 2# starbox520
©pªº¥\¤O¦³¼W±j¤F¡A¥[ªo¡I
¥H¤U¨â­Ó¼Ò²Õ¦b¨Ï¥Î°}¦C®É¡AÀ³¥Î¤W¦³¨Ç³\ÅܤơA
´£¨Ñ©p°Ñ¦Ò¡G

1. Sub Ex()
   
2.     Dim ln As Variant, ar As Variant
   
3.     Dim cts As Integer, ct2 As Integer
   
4.    
   
5.     With ¤u§@ªí1
   
6.         ln = .[A1].CurrentRegion.Value
   
7.         ReDim ar(1 To UBound(ln, 2) - 1, 1 To 2)
   
8. 
   
9.         For cts = 1 To UBound(ln, 2) - 1
   
10.             ar(cts, 1) = ln(1, cts + 1)
    
11.             ar(cts, 2) = ""
    
12.             For ct2 = 3 To UBound(ln, 1)
    
13.                 If ln(ct2, cts + 1) <> 0 Then
    
14.                     ar(cts, 2) = IIf(ar(cts, 2) = "", ln(ct2, 1) & IIf(ln(ct2, cts + 1) > 0, "+", "") & ln(ct2, cts + 1), _
    
15.                              ar(cts, 2) & "," & ln(ct2, 1) & IIf(ln(ct2, cts + 1) > 0, "+", "") & ln(ct2, cts + 1))
    
16.                 End If
    
17.             Next ct2
    
18.         Next cts
    
19. 
    
20.         With ¤u§@ªí2
    
21.             .UsedRange.ClearContents
    
22.             .[A1].Resize(UBound(ar, 1), UBound(ar, 2)) = ar
    
23.         End With
    
24.     End With
    
25. End Sub

½Æ»s¥N½X

1. Sub Ex1()      '  ReDim Preserve ªºÀ³¥Î¡FÅܧó³Ì«áºû«×ªº¤j¤p®É¡A¥Î¨Ó«O¯d²{¦³°}¦C¸ê®Æ¡C
   
2.     Dim ln As Variant, ar As Variant
   
3.     Dim cts As Integer, ct2 As Integer
   
4.    
   
5.     With ¤u§@ªí1
   
6.         ln = .[A1].CurrentRegion.Value
   
7.         '  UBound(Ln, 1) = 25 : Long   /   UBound(Ln, 2) : 8 : Long
   
8. 
   
9.         For cts = 1 To UBound(ln, 2) - 1
   
10.             If IsEmpty(ar) Then ReDim ar(1 To 2, 1 To 1) Else ReDim Preserve ar(1 To 2, 1 To UBound(ar, 2) + 1)
    
11.             ar(1, cts) = ln(1, cts + 1)
    
12.             ar(2, cts) = ""
    
13.             For ct2 = 3 To UBound(ln, 1)
    
14.                 If ln(ct2, cts + 1) <> 0 Then
    
15.                     ar(2, cts) = IIf(ar(2, cts) = "", ln(ct2, 1) & IIf(ln(ct2, cts + 1) > 0, "+", "") & ln(ct2, cts + 1), _
    
16.                               ar(2, cts) & "," & ln(ct2, 1) & IIf(ln(ct2, cts + 1) > 0, "+", "") & ln(ct2, cts + 1))
    
17.                 End If
    
18.             Next ct2
    
19.         Next cts
    
20.         
    
21.         With ¤u§@ªí2
    
22.             .UsedRange.ClearContents
    
23.             .[A1].Resize(UBound(ar, 2), UBound(ar, 1)) = Application.Transpose(ar)
    
24.         End With
    
25.     End With
    
26. End Sub

½Æ»s¥N½X

hcm19522

http://blog.xuite.net/hcm19522/twblog/474457571

starbox520

¦^´_ 3# c_c_lai
¦^´_ 3# hcm19522

    ¦n­ò!!!
    ¥¿¦b§V¤O¾Ç²ß¤@¨Ç¦nÀ´ªº><

    ¤UÅܱoª©h¤j¥Î¥¨¶°
    §Ú¬Ý¤£¤ÓÀ´XD  ³o¬O¤½¦¡ªº·N«ä¶Ü
    ¨S¸Õ¹L

1. K2:O8{=IFERROR(IF(COLUMN(A1)=1,"",",")&INDEX($A:$A,SMALL(IF(($B$3:$H$25<>0)*($B$1:$H$1=$J2),ROW(B$3:H$25)),COLUMN(A1)))&TEXT(N(INDIRECT(TEXT(SMALL(IF(($B$3:$H$25<>0)*($B$1:$H$1=$J2),ROW(B$3:H$25)*100+COLUMN($B3:$H25)),COLUMN(A1)),"!R0C00"),)),"!+0;!-0")&L2,"")

½Æ»s¥N½X

starbox520

¦^´_ 3# c_c_lai


    C¤j²Ä¤GºØ¬O¥i¥H¿ï¾Ü½d³òªº·N«ä§q
    ·|Åܦ¨³o¼Ë?
   '  UBound(Ln, 1) = 177 : Long   /   UBound(Ln, 2) : 31 : Long
      31¬O§Ú¥u­n§PÂ_¨ì "AE"Äæ¦Ó¤w
     ¤u§@ªí "TEST¬O¼g¤J"ªº¦a¤è¡A³oÃä§Ú­n¦h§PÂ_ AÄ檺  M#SCC   ¸ò S#SCC  ¤£­n°µ³B²z(Åã¥Ü)
     §Ú¸Õ¹L¦bIF¥[¤J If arA(x, y) <> 0 And arA <> "M#SCC" And arA <> "S#SCC" Then
     ·|¥X²{«¬ºA¤£²Å
     ±Æª©¤]ÅܤF¡A¸Ô½ÐªþÀÉ
     ¸Ó«ç»ò­×§ï©O

    scanttt.rar (20.36 KB)

¤U¸ü¦¸¼Æ: 5

2016-12-12 12:14

c_c_lai

¦^´_ 6# starbox520

¤U¸ü (106.22 KB)

2016-12-12 13:51

c_c_lai

¦^´_ 6# starbox520
scanttt.rar (27.91 KB)

¤U¸ü¦¸¼Æ: 2

2016-12-12 14:04

³o¬O§Ú§â POA¡B¥H¤Î POB ªº¤º®e­È­×§ï¤F¡C

c_c_lai

¦^´_ 6# starbox520
¨Ì·Ó©p #6 ©Òªþ scanttt.xlsx  ­ì¥»¸ê®Æ (¥¼¥[²§°Ê)¡A
°õ¦æ¤§­×§ïª©¥»¡C
¦]¬°¨C¤@°}¦CÅܼƦ³ "³Ìªø¤£¯à¶W¹L 255" ªº­­¨î¡A
©Ò¥Hµ{¦¡¸Ì¥[¤F§PÂ_¡A¶W¥X³¡¤À¤©¥HºI±¼¤£³B¸Ì¡C
scanttt.rar (30.89 KB)

¤U¸ü¦¸¼Æ: 7

2016-12-12 17:35

starbox520

¦^´_ 9# c_c_lai


    C¤j§Ú¥H§Ú·|ªº¼gªk½m²ß¤@¦¸
    ³o¤èªk¦n¹³¤£·|¶W¹L
    ¦ý°ÝÃDÂI¬O§âAEÄæ¥H«áªº¤]§PÂ_¶i¥h¤F
    ©È¥¼¨Ó¸ê®Æ¶q¶V¨Ó¶V¦hªº¸Ü¡A·|¥d¦b³o­Ó°ÝÃDXD
    ©Ò¥H¥ý¬ã¨s°_¨Ó©ñ«¢«¢

1. Private Sub Workbook_Open()
   
2.     Dim vData As Variant, nRow As Integer, nCol As Integer
   
3.     Dim vFill As Variant
   
4.    
   
5.     vData = Sheets("Data").UsedRange
   
6.     ReDim vFill(2 To UBound(vData, 2), 1 To 8)
   
7.     For nCol = 2 To UBound(vData, 2)
   
8.         vFill(nCol, 2) = vData(1, nCol)
   
9.         For nRow = 3 To UBound(vData)
   
10.             If vData(nRow, nCol) <> 0 And vData(nRow, 1) <> "M#SCC" And vData(nRow, 1) <> "S#SCC" Then
    
11.                 If vFill(nCol, 8) <> "" Then vFill(nCol, 8) = vFill(nCol, 8) & ","
    
12.                 vFill(nCol, 8) = vFill(nCol, 8) & vData(nRow, 1) & IIf(vData(nRow, nCol) > 0, "+", "") & vData(nRow, nCol)
    
13.             End If
    
14.         Next
    
15.     Next
    
16.     With Sheets("TEST")
    
17.         .[A1].CurrentRegion.Offset(1).ClearContents
    
18.         .[A2].Resize(UBound(vFill) - 1, 8) = vFill
    
19.     End With
    
20. End Sub

½Æ»s¥N½X