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

[µo°Ý] ­«½Æ¤º®e®É¶¡¥[Á`¨Ã§R°£­«½Æ«O¯d°ß¤@­È

[µo°Ý] ­«½Æ¤º®e®É¶¡¥[Á`¨Ã§R°£­«½Æ«O¯d°ß¤@­È

¥»©«³Ì«á¥Ñ v03586 ©ó 2020-12-14 02:31 ½s¿è

½Ð°Ý¦U¦ì«e½ú
§Ú¦³¼g¤@­Óµ{¦¡¥D­n¬O­n§PÂ_
°²³]¤£¦P¡yIÄæ¦ì¡zªº­È, ¦ý¬Û¦P¡yJÄæ¦ì¡zªº­È, «h¡yLÄæ¦ì¡z¬Û¥[¦b¡yMÄæ¦ì¡z(¥[Á`µ²ªG)
¨Ã«O¯d¤@­Ó¡yJÄæ¦ì¡zªº­È, §R°£­«½Æªº¡yJÄæ¦ì¡z

¦p¤U¹Ï, ¦ý¥Ø«e°õ¦æ¥X¨Óªºµ²ªG¦³°ÝÃD, ½Ð°Ý¦³«e½ú¥i¥HÀ°¦£«üÂI¶Ü?



  1. For l = Range("A65536").End(xlUp).Row To 2 Step -1
  2.         If .Cells(l, "I") = .Cells(l - 1, "I") And .Cells(l, "J") = .Cells(l - 1, "J") Then
  3.             .Cells(l - 1, "M") = .Cells(l, "L") + .Cells(l - 1, "L")
  4.             Rows(l).ClearContents
  5.         End If
  6.     Next

  7.     Cells.Select

  8.     Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, _
  9.                       Header:=xlYes
½Æ»s¥N½X
½d¨Ò.rar (13.09 KB)

¦^´_ 1# v03586

½Ð´ú¸Õ¬Ý¬Ý¡A·PÁ¡C

Sub TEST()
Dim Arr, xD, T, N&, j%, NR&
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([M1], [A65536].End(3))
For i = 1 To UBound(Arr)
    T = Arr(i, 9) & Arr(i, 10)
    If xD.Exists(T & "") Then
        NR = xD(T & "")
        Arr(NR, 13) = Arr(NR, 13) + Arr(i, 12)
    Else
        N = N + 1
        xD(T & "") = N
        For j = 1 To 12: Arr(N, j) = Arr(i, j): Next
  End If
Next
Range("A1:M" & [A65536].End(3).Row) = ""
Arr(1, 13) = "TIME"
[A1].Resize(N, 13) = Arr
End Sub

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 2# samwang


§A§Ñ°O²Ö¥[ÀY¤@¦C¤F³á~

    If xD.Exists(T & "") Then
        NR = xD(T & "")
        Arr(NR, 13) = Arr(NR, 13) + Arr(i, 12)
    Else
        N = N + 1
        xD(T & "") = N
        For j = 1 To 12: Arr(N, j) = Arr(i, j): Next
        Arr(N, 13) = Arr(N, 12)
   End If
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 1# v03586

§Ú´£¨Ñ¤@­Ó¤ñ¸û "ª½Ä±" ¥B ¤£¥Î¦r¨åª«¥óªº¼gªkµ¹§A

Arr°}¦C¥u¬O¬°¤F¥[§Ö¹Bºâ³t«× (¥Î Cellª«¥ó ¹Bºâ·|¸ûºC)

­Y¬Ý¤£À´¡A¥u­n§âArr §ï¦¨ Cell §A´NÀ´¤F Ex: Arr(R,12) => Cell(R,12)

µ{¦¡¦p¤U


Sub ¥[Á`()
Dim Arr, R_Del_Arr, PKey$, §R°£¦C As Range
Arr = [A1].CurrentRegion    '§ìÀx¦s®æ¸ê®Æ ¨ì Arr °}¦C
'¤£¦PªºKey¬ö¿ýÀY¤@­Ó¦C¸¹¡A¬Û¦PKey°µ²Ö¥[¡A°O¿ý¤§«á­n§R°£ªº¦C¸¹
For R& = 2 To UBound(Arr)
  Key$ = Arr(R, 9) & Arr(R, 10)
  If Key <> PKey Then
    R0& = R: PKey = Key
    Arr(R, 13) = Arr(R, 12)
  Else
    Arr(R0, 13) = Arr(R0, 13) + Arr(R, 12)
    R_Del$ = R_Del$ & "," & R
  End If
Next R
R_Del_Arr = Split(Mid(R_Del, 2), ",")
[A1].Resize(UBound(Arr), UBound(Arr, 2)) = Arr  'Arr¸ê®Æ­Ë¦^¥hÀx¦s®æ
'§R°£­«½Æ¦C
For Each Rd In R_Del_Arr
  If §R°£¦C Is Nothing Then
    Set §R°£¦C = Rows(Rd)
  Else
    Set §R°£¦C = Union(§R°£¦C, Rows(Rd))
  End If
Next
§R°£¦C.Delete   '¥i§ï¬° §R°£¦C.Select ½T»{§R°£½d³ò
End Sub
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 4# n7822123


§A§Ñ°O²Ö¥[ÀY¤@¦C¤F³á~

    If xD.Exists(T & "") Then
         NR = xD(T & "")
         Arr(NR, 13) = Arr(NR, 13) + Arr(i, 12)
     Else
         N = N + 1
         xD(T & "") = N
         For j = 1 To 12: Arr(N, j) = Arr(i, j): Next
         Arr(N, 13) = Arr(N, 12)
    End If


½Ð°Ýn7822123 ±z´£°ÝÃD  §Ñ°O²Ö¥[ÀY¤@¦C¤F³á~  ¡A¬O­ì¥»§Ú¦^ÂЪº¨º¤@¦C¶Ü?
·PÁ«ü±Ð¡C

Range("A1:M" & [A65536].End(3).Row) = ""
Arr(1, 13) = "TIME"
[A1].Resize(N, 13) = Arr
End Sub

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-12-14 15:36 ½s¿è

¦^´_ 6# samwang

¬Oªü¡A§Aªºµ{¦¡ºâ¥X¨Óªº­È~ ¨S²Ö¥[¨ìÀY¤@¦C~

¥u±q²Ä2¦C©¹¤U²Ö¥[~


µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 7# n7822123


·PÁ«ü¾É¡A¯uªº«Ü²Ê¤ß¤j·N¡A·P®¦¡C

TOP

¦^´_ 1# v03586

§ó·sµ{¦¡½X¦p¤U¡AÁÂÁÂ

Sub TEST_2()
Dim Arr, xD, T, N%, j%, NR
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([M1], [A65536].End(3))
For i = 1 To UBound(Arr)
    T = Arr(i, 9) & Arr(i, 10)
    If xD.Exists(T & "") Then
        NR = xD(T & "")
        Arr(NR, 13) = Arr(NR, 13) + Arr(i, 12)
    Else
        N = N + 1
        xD(T & "") = N
        For j = 1 To 12: Arr(N, j) = Arr(i, j): Next
        Arr(N, 13) = Arr(N, 12)
  End If
Next
Range("A1:M" & [A65536].End(3).Row) = ""
Arr(1, 13) = "TIME"
[A1].Resize(N, 13) = Arr
End Sub

TOP

¦^´_ 5# n7822123


    ·PÁ«e½ú­Ìªº´£¿ô, ¯uªº°}¦C¶]§Ö«D±`¦h, ¨D¾Ç®É´Á°}¦C¯uªº§â§Ú·dºG¤F, ¥XªÀ·|«á¤~µo²{¯u¥¿¹ê¥Îªº¦a¤è!!

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦pÆp¥Û¡j®É¶¡¹ï¤@­Ó¦³´¼¼zªº¤H¦Ó¨¥¡A´N¦pÆp¥Û¯ë¬Ã¶Q¡F¦ý¹ï·M¤H¨Ó»¡¡A«o¹³¬O¤@§âªd¤g¡A¤@ÂI»ù­È¤]¨S¦³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD