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

[µo°Ý] ¦p¦ó¥Î°j°é¡A±NADD_MÄ椤¦³§t¦r¥À¨Ö­p

[µo°Ý] ¦p¦ó¥Î°j°é¡A±NADD_MÄ椤¦³§t¦r¥À¨Ö­p

½Ð°Ý¡A°j°é¤¤¡A§PÂ_¦¡¡A¥i¥[¤J¦r¥À¶Ü¡H

°ÝÃD51.rar (271.19 KB)

§Æ±æ¤ä«ù!

¦^´_ 1# s7659109
°ÝÃD¡G¦p¦ó¥Î°j°é¡A±NADD_MÄ椤¦³§t¦r¥À¨Ö­p
¦p¦ó¦X¨Ö ½Ð¦A»¡¸Ô²Ó

§A·Q­nªº¬O³o¼Ë¶Ü?
  1. ub Ex()
  2.     With Range(Cells(2, "C"), Cells(2, "C").End(xlDown))
  3.         .NumberFormatLocal = "G/³q¥Î®æ¦¡"  'ªþÀɪº®æ¦¡¬O¤å¦r
  4.         .Cells = .Value
  5.         .Offset(, 2).FormulaR1C1 = "=IF(ISNUMBER(RC[-2]),0,1)"
  6.         MsgBox "MÄ椤¦³§t¦r¥À ¦@¦³ " & Application.Sum(.Offset(, 2))
  7.         '********************
  8.         '.Offset(, 2).FormulaR1C1 = "=ISNUMBER(RC[-2])"
  9.        'MsgBox "MÄ椤¦³§t¦r¥À ¦@¦³ " &  Application.CountIf(.Offset(, 2), False)
  10.        .Offset(, 2) = ""
  11.     End With
  12. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

ª©¥D§A»~·|§Úªº·N«ä¡A§Úªº°ÝÃD¬O¥Î°j°é²Î­pµ²ªG1»Pµ²ªG2¡A¨Ã¦C¥X©ú²Ó¡Aªþ¥ó­«·s¤W¶Ç¤F¡C

°ÝÃD51.rar (260.66 KB)

§Æ±æ¤ä«ù!

TOP

¦^´_ 3# s7659109

°ÝÃD­n»¡²M·¡,¤~¦³¤H·|¦^ÂÐ.
µ²ªG1 ¥]§t¦³"Y" ªº¸ê®Æ,¦ý¬°¤£¥]§t¦³"Y"ªº¥u¦³183¤@µ§
µ²ªG2 ¥]§t¦³"G" ªº¸ê®Æ,¦ý¬°¤£¥]§t¦³"G"ªº¦³¤@¤j°ï¸ê®Æ
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ s7659109 ©ó 2016-10-21 08:52 ½s¿è

ª©¥D¡G§Ú¬O¤@­ÓVBAªì¾ÇªÌ¡AÁÂÁ§Aªº´£¿ô
§Úªº°ÝÃD¡G¬O§Q¥Î°j°é±N­ì©l¸ê®Æ(¥ª°¼_§Ú¦³¥[¤j¸ê®Æ¡A½Ð¦Ò¼{¨C¤Ñ³£·|·s¼W¸ê®Æ¡A¦Û°Ê§ó·s),¨D¥X
µ²ªG1:¬O±NADDÄ椤A001 ¥BADD_M¤¤001~199°Ï¶¡(¥]§t¦³¦r¥À¤@¨Ö­p¤J¡A¦p1Y1,1A5,07Zµ¥µ¥)²Ö­p¡A¨Ã¦C¥X©ú²Ó
µ²ªG2:¬O±NADDÄ椤A001 ¥BADD_M¤¤200~999°Ï¶¡(¥]§t¦³¦r¥À¤@¨Ö­p¤J¡A¦p2G0,,28Jµ¥µ¥)²Ö­p¡A¨Ã¦C¥X©ú²Ó

°ÝÃD51_.rar (244.07 KB)

§Æ±æ¤ä«ù!

TOP

¦^´_ 5# s7659109
¥Î¶i¶¥¿z¿ï¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Rng(1 To 3) As Range
  4.     Set Rng(1) = Range("B:D").SpecialCells(xlCellTypeConstants)    '**¸ê®Æ®w
  5.     Set Rng(2) = Cells(1, Columns.Count).Resize(2)                          '**¸ê®Æ®wªº·Ç«h½d³ò,CriteriaRange
  6.     Set Rng(3) = Range("G2,K2")                                                       '**¸ê®Æ®wªº·Ç«h½d³ò,CopyToRange
  7.     Rng(3)(1).CurrentRegion.Clear
  8.     Rng(3).Areas(2).CurrentRegion.Clear
  9.     '** ¸ê®Æ®wªº·Ç«h¬° "­pºâ¦¡·Ç«h",·Ç«hÄæ¦ì¦WºÙ¤£¥i»P ¸ê®Æ®wªºÄæ¦ì¦WºÙ¬Û¦P ****
  10.     Rng(2).Cells(1) = "TEST"                                                              '¸ê®Æ®wªº·Ç«hÄæ¦ì¦WºÙ
  11.     Rng(2).Cells(2) = "=VALUE(MID(ADD_M,1,1))<2"                    ''¿z¿ï¸ê®Æ®wªº·Ç«h , ­pºâ¦¡·Ç«h
  12.     '**MID(ADD_M,1,1) -> ¸ê®Æ®wÄæ¦ì  "ADD_M" ªº²Ä¤@­Ó¦r¦ê"
  13.     '**=VALUE(MID(ADD_M,1,1))<2 ²Ä¤@­Ó¦r¦ê¤p©ó2
  14.      Rng(1).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Rng(2), CopyToRange:=Rng(3).Cells(1), Unique:=False
  15.      Rng(3).Cells(0) = "¤p­p"
  16.      Rng(3).Cells(0, 3) = Application.Sum(Rng(3).Cells(0, 3).EntireColumn)     
  17.      Rng(2).Cells(2) = "=VALUE(MID(ADD_M,1,1))>=2"
  18.      '**=VALUE(MID(ADD_M,1,1))<2 ²Ä¤@­Ó¦r¦ê¤j©óµ¥©ó2
  19.      Rng(1).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Rng(2), CopyToRange:=Rng(3).Areas(2), Unique:=False
  20.      Rng(3).Areas(2).Cells(0) = "¤p­p"
  21.      Rng(3).Areas(2).Cells(0, 3) = Application.Sum(Rng(3).Areas(2).Cells(0, 3).EntireColumn)
  22.     Rng(2).Clear
  23. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ s7659109 ©ó 2016-10-21 13:58 ½s¿è

ª©¥D¡G
ÁÂÁª©¥D¸Ñ´b¡A¦ýµ²ªG1»P2¤¤ADDÄæ¡A¥u­nA001,¨ä¥¦ªº¤£¦C¤J¡A¬G
TOTAL»P©ú²Ó¦³»~¡C

°ÝÃD51_2.rar (368.06 KB)

§Æ±æ¤ä«ù!

TOP

ÃD·N¤£²M, ´NµLªk¦^À³!!!
  1. Sub TEST()
  2. Dim T$, Arr, Brr, Crr, Bn&, Cn&, Sb, Sc, i&, j%
  3. [G3:I6000,K3:M6000].ClearContents:  [I1,M1] = ""
  4. If [G1] = "" Then Exit Sub
  5. Arr = Range("B2:D" & Cells(Rows.Count, 2).End(xlUp).Row)
  6. ReDim Brr(1 To UBound(Arr), 1 To 3): Crr = Brr
  7. For i = 1 To UBound(Arr)
  8.     If Arr(i, 1) <> [G1] Then GoTo 101
  9.     If Left(Arr(i, 2), 1) Like "[0-1]" Then
  10.        Bn = Bn + 1: Sb = Sb + Val(Arr(i, 3))
  11.        For j = 1 To 3: Brr(Bn, j) = Arr(i, j): Next
  12.     ElseIf Left(Arr(i, 2), 1) Like "[2-9]" Then
  13.        Cn = Cn + 1: Sc = Sc + Val(Arr(i, 3))
  14.        For j = 1 To 3: Crr(Cn, j) = Arr(i, j): Next
  15.     End If
  16. 101: Next i
  17. If Bn > 0 Then [G3:I3].Resize(Bn) = Brr: [I1] = Sb
  18. If Cn > 0 Then [K3:M3].Resize(Cn) = Crr: [M1] = Sc
  19. End Sub
½Æ»s¥N½X
Xl0000193.rar (20.08 KB)

TOP

¦^´_ 7# s7659109
ÂX¤j¸ê®Æ®wªº·Ç«h½d³ò¬°¤GÄæ
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Rng(1 To 3) As Range
  4.     Set Rng(1) = Range("B:D").SpecialCells(xlCellTypeConstants)    '**¸ê®Æ®w
  5.     Set Rng(2) = Cells(1, Columns.Count - 1).Resize(2, 2)                       '**¸ê®Æ®wªº·Ç«h½d³ò,CriteriaRange
  6.     Set Rng(3) = Range("G2,K2")                                                       '**¸ê®Æ®wªº·Ç«h½d³ò,CopyToRange
  7.     Rng(3)(1).CurrentRegion.Clear
  8.     Rng(3).Areas(2).CurrentRegion.Clear
  9.     '** ¸ê®Æ®wªº·Ç«h¬° "­pºâ¦¡·Ç«h",·Ç«hÄæ¦ì¦WºÙ¤£¥i»P ¸ê®Æ®wªºÄæ¦ì¦WºÙ¬Û¦P ****
  10.    
  11.     Rng(2).Cells(1, 1) = "TEST"                                                             '¸ê®Æ®wªº·Ç«hÄæ¦ì¦WºÙ
  12.     Rng(2).Cells(2, 1) = "=VALUE(MID(ADD_M,1,1))<2"                   ''¿z¿ï¸ê®Æ®wªº·Ç«h , ­pºâ¦¡·Ç«h
  13.     Rng(2).Cells(1, 2) = "ADD"                                                             '¸ê®Æ®wªº·Ç«hÄæ¦ì¦WºÙ
  14.     Rng(2).Cells(2, 2) = "A001"                                                           ''¿z¿ï¸ê®Æ®wªº·Ç«h
  15.    
  16.     '**MID(ADD_M,1,1) -> ¸ê®Æ®wÄæ¦ì  "ADD_M" ªº²Ä¤@­Ó¦r¦ê"
  17.     '**=VALUE(MID(ADD_M,1,1))<2 ²Ä¤@­Ó¦r¦ê¤p©ó2
  18.      Rng(1).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Rng(2), CopyToRange:=Rng(3).Cells(1), Unique:=False
  19.      Rng(3).Cells(0) = "¤p­p"
  20.      Rng(3).Cells(0, 3) = Application.Sum(Rng(3).Cells(0, 3).EntireColumn)
  21.      Rng(2).Cells(2, 1) = "=VALUE(MID(ADD_M,1,1))>=2"
  22.      '**=VALUE(MID(ADD_M,1,1))<2 ²Ä¤@­Ó¦r¦ê¤j©óµ¥©ó2
  23.      Rng(1).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Rng(2), CopyToRange:=Rng(3).Areas(2), Unique:=False
  24.      Rng(3).Areas(2).Cells(0) = "¤p­p"
  25.      Rng(3).Areas(2).Cells(0, 3) = Application.Sum(Rng(3).Areas(2).Cells(0, 3).EntireColumn)
  26.     Rng(2).Clear
  27. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦p¦ó¥Î°j°é¡A±NADD_MÄ椤¦³§t¦r¥À¨Ö­p(¶i¶¥»Ý¨Dª©)

¤j¤j¡G
°ÝÃD¡G®Ú¾Ú¿é¤J±ø¥ó¡A¿é¥X©ú²Ó¤G±iªí»P·J­p¡A¨ä¤¤¤é´Á­nÂà´«¡A¦³©T©w¦C¦b²Ä¤@¦C¤§±ø¥ó¡AÁÙ¦³¯S®í±ø¥ó»Ý¨D

°ÝÃD52.rar (17.25 KB)

§Æ±æ¤ä«ù!

TOP

        ÀR«ä¦Û¦b : §g¤l¬°¥Ø¼Ð¡A¤p¤H¬°¥Øªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD