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

½Ð±Ð ¤½¦¡_¸õÄæ¨Ã¥B²Å¦X±ø¥ó¤~¥[Á`

½Ð±Ð ¤½¦¡_¸õÄæ¨Ã¥B²Å¦X±ø¥ó¤~¥[Á`

¦U¦ì«e½ú¦n
½Ð±Ð ¤½¦¡_¸õÄæ¨Ã¥B²Å¦X±ø¥ó¤~¥[Á`
1.¥[Á`®æAC13
2.±qAG13¶}©l¸õ4Äæ¬Û¥[¦Ü³Ì«áÄæ¦ì
2.1.·íÄ檺²Ä10¦CÀx¦s®æ¤£¥H"«e¸m" ¶}ÀY
2.2.¥H°j°é°õ¦æªº°j°é½d¨Ò¦p¤U
ÁÂÁ«ü¾É

Sub °j°é½d¨Ò()
Dim i&
[AC13] = 0
For i = 33 To 256 Step 4
   If Cells(10, i) Like "«e¸m*" = False Then
      [AC13] = [AC13] + Cells(13, i)
   End If
Next
End Sub
'AC13 =SUMPRODUCT((MOD(COLUMN(AG:IV),4)=1)*AG13:IV13)
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

VBA¤¤ªº°}¦C¥Îªk, ­n¦­ÂI¤F¸Ñ,
³Ì¦n¤]²z¸Ñ¦r¨å¥Îªk,
¦r¨å+°}¦C, ¥i³B²z«Ü¦hºØ¸ê®Æªº³B²z¤Î²Î­p¹Bºâ»Ý¨D,
...
­ã´£³¡ªL µoªí©ó 2020-8-24 17:19



    ÁÂÁ«e½ú«ü¾É
«á¾Ç¾q¶w! ¸g¹L¤F2¦~¤~¾Ç¨ì°}¦C»y¦r¨åªº¤@ÂIÂI¥Ö¤ò
¶Ô¯à¸É©å!,¥Ã¤£©ñ±ó!
¤U¦Cµ{¦¡½X¬O¤µ¤é¾Ç²ß¨ìªº¦r¨å»P°}¦C§Þ¥©
¦A½Ð«e½ú¼·ªÅ¦A«ü¾É
  1. Option Explicit
  2. Sub TEST_20220920_3()
  3. Dim Brr, R&, C&, i&, j&, k%, T$, TT, Y, Z, W, P, Q
  4. Dim Crr, V, xR, n
  5. TT = Timer
  6. Set Y = CreateObject("Scripting.Dictionary")
  7. Set Z = CreateObject("Scripting.Dictionary")
  8. Set V = CreateObject("Scripting.Dictionary")
  9. Set Z = ActiveSheet.Cells
  10. For i = 1 To 4
  11.    Set V(i) = CreateObject("Scripting.Dictionary")
  12. Next
  13. R = Z(Rows.Count, "D").End(xlUp).Row '³Ì«á¤@¦æ
  14. W = Z(12, Columns.Count).End(xlToLeft).Column '³Ì«á¤@Äæ
  15. Crr = Z(12, 33).Resize(R - 11, W - 32)
  16. Z = Z(1, 1).Resize(R, W) '©w¸q¸ê®Æ½d³ò--A1¦Ü¾ã­Ó°Ï
  17. For i = 1 To 13 Step 4
  18.    Y.Add Mid(Z(11, i + 12), 2, 2), (i + 3) / 4
  19. Next
  20. For i = 33 To W Step 4
  21.    P = Right(Split(Z(11, i), "]")(0), 2)
  22.    V(Y(P)).Add V(Y(P)).Count, i
  23. Next
  24. ReDim Brr(1 To R - 12, 1 To 20) '³]ªÅ°}¦C
  25. For i = 13 To R
  26.    For Each xR In V(1)
  27.       If Z(i, V(1)(xR) + 1) > Brr(i - 12, 2) Then
  28.          Brr(i - 12, 1) = Z(i, V(1)(xR))
  29.          Brr(i - 12, 2) = Z(i, V(1)(xR) + 1)
  30.       End If
  31.       If Z(i, V(1)(xR) + 1) - Z(i, V(1)(xR)) > Brr(i - 12, 3) Then
  32.          Brr(i - 12, 3) = Z(i, V(1)(xR) + 1) - Z(i, V(1)(xR))
  33.       End If
  34.    Next
  35.    For n = 1 To 3
  36.       For Each xR In V(n + 1)
  37.          Brr(i - 12, n * 4 + 1) = Brr(i - 12, n * 4 + 1) + Z(i, V(n + 1)(xR))
  38.          Brr(i - 12, n * 4 + 2) = Brr(i - 12, n * 4 + 2) + Z(i, V(n + 1)(xR) + 1)
  39.          Brr(i - 12, n * 4 + 3) = Brr(i - 12, n * 4 + 2) - Brr(i - 12, n * 4 + 1)
  40.       Next
  41.    Next
  42.    For n = 1 To 3
  43.       For j = 1 To 3
  44.          Brr(i - 12, 16 + n) = Brr(i - 12, 16 + n) + Brr(i - 12, j * 4 + n)
  45.       Next
  46.    Next
  47. Next i
  48. [M13].Resize(UBound(Brr), UBound(Brr, 2)) = Brr
  49. MsgBox Timer - TT
  50. End Sub
½Æ»s¥N½X

TOP

¦^´_ 25# ­ã´£³¡ªL


    ½Ð±Ð«e½ú Ãö©ó SET °ÝÃD
¤U¤èµ{¦¡½X ¥[¤FSET ¤§«á°õ¦æ®É¶¡¬O¨S¥[SETªº3­¿
Set Z = Z(1, 1).Resize(R, W)     '0.09¬í
Z = Z(1, 1).Resize(R, W)            '0.03¬í

1.¬O¦]¬°¨S¥[SET¬O°}¦C,¥[¤FSET ¬O¦r¨å¶Ü?
2.¤°»ò®É­Ô¥Î °}¦C?¤°»ò®É­Ô¥Î ¦r¨å?

ÁÂÁ«e½ú
  1. Option Explicit
  2. Sub TEST_20220920_2()
  3. Dim Brr, R&, C&, i&, j&, k%, T$, TT, Y, Z, W
  4. TT = Timer
  5. Set Y = CreateObject("Scripting.Dictionary")
  6. Set Z = CreateObject("Scripting.Dictionary")
  7. Set Z = ActiveSheet.Cells
  8. R = Z(Rows.Count, "D").End(xlUp).Row '³Ì«á¤@¦æ
  9. W = Z(12, Columns.Count).End(xlToLeft).Column '³Ì«á¤@Äæ
  10. Set Z = Z(1, 1).Resize(R, W) '©w¸q¸ê®Æ½d³ò--A1¦Ü¾ã­Ó°Ï
  11. For i = 1 To 13 Step 4
  12.    Y.Add Mid(Z(11, i + 12), 2, 2), i
  13. Next
  14. ReDim Brr(1 To R - 12, 1 To 20) '³]ªÅ°}¦C
  15. For i = 13 To R
  16.    For j = [AG1].Column To W Step 4
  17.        T = Right(Split(Z(11, j), "]")(0), 2)  '¨ú[??]¤¤ªº¤å¦r
  18.        C = Y(T) 'ÀË´ú¦U¤À¶µ­n¶ñ¤JBrrªº¦ì¸m
  19.        If C = 1 Then  '«e¸m--¨ú³Ì¤j
  20.           For k = 0 To 2
  21.               If Z(i, j + k) > Brr(i - 12, C + k) Then
  22.                  Brr(i - 12, C + k) = Z(i, j + k)
  23.               End If
  24.           Next k
  25.        ElseIf C >= 5 Then '¨ä¥¦¶µ--²Ö­p
  26.           For k = 0 To 2
  27.               Brr(i - 12, C + k) = Brr(i - 12, C + k) + Z(i, j + k) '¦U¤À¶µ²Ö­p
  28.               Brr(i - 12, 17 + k) = Brr(i - 12, 17 + k) + Z(i, j + k) '¦X­p
  29.           Next k
  30.        End If
  31.     Next j
  32. Next i
  33. [M13].Resize(UBound(Brr), UBound(Brr, 2)) = Brr
  34. MsgBox Timer - TT
  35. End Sub
½Æ»s¥N½X

TOP

¦^´_ 25# ­ã´£³¡ªL


    ÁÂÁ«e½ú«ü¾É
«e½úªºµ{¦¡½X¤Óºë²¤F
«á½ú¥Ñ ²¤ÆÁc,³v¨B½m²ß¦r¨å»P°}¦C
¤µ²ß±o ¦r¨å.Add
  1. Option Explicit
  2. Sub TEST_20220919()
  3. Dim Arr, Brr, R&, C&, i&, j&, k%, T$, TT, Y
  4. TT = Timer
  5. R = Cells(Rows.Count, "d").End(xlUp).Row '³Ì«á¤@¦æ
  6. C = Cells(12, Columns.Count).End(xlToLeft).Column '³Ì«á¤@Äæ
  7. Set Y = CreateObject("Scripting.Dictionary")
  8. For i = 1 To 13 Step 4
  9.    Y.Add Mid(Cells(11, i + 12), 2, 2), i
  10. Next
  11. Arr = Range([A1], Cells(R, C)) '©w¸q¸ê®Æ½d³ò--A1¦Ü¾ã­Ó°Ï
  12. ReDim Brr(1 To UBound(Arr) - 12, 1 To 20) '³]ªÅ°}¦C
  13. For i = 13 To UBound(Arr)
  14.    For j = [AG1].Column To UBound(Arr, 2) Step 4
  15.        T = Right(Split(Arr(11, j), "]")(0), 2)  '¨ú[??]¤¤ªº¤å¦r
  16.        C = Y(T) 'ÀË´ú¦U¤À¶µ­n¶ñ¤JBrrªº¦ì¸m
  17.        If C = 1 Then  '«e¸m--¨ú³Ì¤j
  18.           For k = 0 To 2
  19.               If Arr(i, j + k) > Brr(i - 12, C + k) Then
  20.                  Brr(i - 12, C + k) = Arr(i, j + k)
  21.               End If
  22.           Next k
  23.        ElseIf C >= 5 Then '¨ä¥¦¶µ--²Ö­p
  24.           For k = 0 To 2
  25.               Brr(i - 12, C + k) = Brr(i - 12, C + k) + Arr(i, j + k) '¦U¤À¶µ²Ö­p
  26.               Brr(i - 12, 17 + k) = Brr(i - 12, 17 + k) + Arr(i, j + k) '¦X­p
  27.           Next k
  28.        End If
  29.     Next j
  30. Next i
  31. [M13].Resize(UBound(Brr), UBound(Brr, 2)) = Brr
  32. MsgBox Timer - TT
½Æ»s¥N½X

TOP

¦^´_ 27# ­ã´£³¡ªL


    ÁÂÁ«e½ú¨ó§U»P«ü¾É!
1.¥Í¬¡Á`¬O¦£¦£¸L¸Lªº! ³o©«¤w¸g¹L¤F11­Ó¤ë¤F!
2.¤u§@§i¤@¬q¸¨ªº¥ð®§¤ù¨è,¦A¦^¨Ó¾Ç²ß³o¹ê¥Îªºµ{¦¡½X!ÁÙ¬O«Ü¦Y¤O!
3.³o¬q®É¶¡¸Ì¦]¨t²ÎÂà´«Oracle>SAP ,¤j¶qªºExcelµ{¦¡½X»Ý­n­«¼g!
4.¦]¬°¤S¦h¤S«æµÛ­n¤W½u¨Ï¥Î,³£¥u¨D¯à¥Î´N¦n!
®Ä¯à»Ý­n´£¤É!"
Andy¾Ç²ß¶i¨Bªº«ÜºC!½Ð «e½ú­Ì¦A«ü¾É!
ÁÂÁ ³Â»¶®a±Ú°Q½×ª©ª© ªºÀu½è¥­¥x


Sub TEST_20200826()
Dim Arr, Brr, R&, C&, i&, j&, k%, T$
R = Cells(Rows.Count, "d").End(xlUp).Row '³Ì«á¤@¦æ
C = Cells(12, Columns.Count).End(xlToLeft).Column '³Ì«á¤@Äæ
Arr = Range([A1], Cells(R, C)) '©w¸q¸ê®Æ½d³ò--A1¦Ü¾ã­Ó°Ï
'¡ô Arr©w¸q¦¨¾ã­Ó°Ï°ì§ó®e©ö²z¸Ñ«á­±ªºÅÞ¿è³W«h!ÁÂÁ«e½ú!
ReDim Brr(1 To UBound(Arr) - 12, 1 To 20) '³]ªÅ°}¦C
For i = 13 To UBound(Arr)
   For j = [AG1].Column To UBound(Arr, 2) Step 4
       T = Right(Split(Arr(11, j), "]")(0), 2)  '¨ú[??]¤¤ªº¤å¦r
       '¡ô ¦]¬°¯S®í²Å¸¹[ ]·|¼vÅT°õ¦æµ²ªG!©Ò¥H¨ú¸Ì­±ªº¦r
       C = InStr("/«e¸m//¨ø¼Ò//¬[¼Ò//½Õ²£/", T) - 1 'ÀË´ú¦U¤À¶µ­n¶ñ¤JBrrªº¦ì¸m
       '¡ô¥ÎT¦r¦êªº©Ò¦bªº¦r¼Æ¦ì¸m ¨M©wT¶µ¥ØªºÁ`©M©ñ¦bBrrªº¦ì¸m! ¯u©¯¹B¯àÀ°¦P¨Æ³]­p³o¥÷¸ê®Æ!ÁÂÁÂ!
       If C = 1 Then  '«e¸m--¨ú³Ì¤j
          For k = 0 To 2
              If Arr(i, j + k) > Brr(i - 12, C + k) Then Brr(i - 12, C + k) = Arr(i, j + k)
          Next k
       ElseIf C >= 5 Then '¨ä¥¦¶µ--²Ö­p
          For k = 0 To 2
              Brr(i - 12, C + k) = Brr(i - 12, C + k) + Arr(i, j + k) '¦U¤À¶µ²Ö­p
              Brr(i - 12, 17 + k) = Brr(i - 12, 17 + k) + Arr(i, j + k) '¦X­p
          Next k
       End If
    Next j
Next i
[M13].Resize(UBound(Brr), UBound(Brr, 2)) = Brr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 27# ­ã´£³¡ªL
¤F¸Ñ
ÁÂÁ«ü¾É
³o½d¨Ò¬O±M®×¹ê»Ú­n¨Ï¥Îªº²Î­p¤u¨ãÀÉ
·|¨Ì¹Î¶¤¹ê»Ú»Ý¨D±¡ªp§@½Õ¾ã
¦³±zªºÀ°¦£Åý¥\¯à§ó§¹¾ã ®Ä¯à§ó¦n ¥Nªí¹Î¶¤·PÁª©ª©.·PÁ¦U¦ì«e½ú.
¯S§O·PÁ±z¹ïAndy­@¤ßªº«ü¾É
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 26# Andy2483

2003ª©¥u¬O´£¨Ñ°Ñ¦Ò¥Î,
µ{¦¡½XÁÙ¬O­n·h¨ì2013ª©, §K±o¦]ª©¥»¦³¨Ç¥\¯à¤£¥i¥Î~~

TOP

¦^´_ 25# ­ã´£³¡ªL


ÁÂÁ«e½ú«ü¾É
Andy·|»{¯u¬ã¨s ¤£ªP¶O±zªº«ü¾É

¥t½Ð±Ð ½d¨Ò·í¤¤ªº¹Ïªí¦C¦L½d³ò°ÝÃD
1.¥H2013ª©ªº¤À­¶¹wÄý½Õ¾ã¹Ïªí¨Ó°t¦XA4ª½¦L,¤]¥i²£¥ÍPDFÄÒ
2.¦A¥H2003ª©¶}±Ò®É¹Ïªí¦ì¸m´N¶]±¼¤F
3.ºÃ¦üª©¥»°ÝÃD!
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 24# Andy2483

ºCºC¬ã¨s:
½d¨Ò_20200826_v1.rar (221.41 KB)

TOP

¦^´_ 23# ­ã´£³¡ªL


¥¿ ­t¤@°_¤ñ ¨ú³Ì¤j ! ¦pªG¨CÄæ³£¬O­t¼Æ ¤]¬O¤ñ³Ì¤j¼Æ
¨Ò¦p
»~®t­È 5   ,10   ,20  ,6   .....¨ú20
»~®t­È -5   ,-10   ,-20  ,-6   .....¨ú-5
»~®t­È- 5   ,10   ,-20  ,6   .....¨ú10

­t¼Æ¬O´£«e§¹¦¨ªº·N«ä,©Ò¥H¤£ÅýÅÜ¥¿¼Æ
¥¿¼Æ¬O¶W¹L¼Ð·Ç®É¶¡
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¦³¤ß´N¦³ºÖ¡A¦³Ä@´N¦³¤O¡A¦Û³yºÖ¥Ð¡A¦Û±oºÖ½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD