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

[µo°Ý] ¦p¦ó§P©w®Æ¸¹+§å¸¹¨ä¥Í²£¤Ñ¼Æ

¦^´_ 10# samwang
1111.rar (8.57 KB)

¦bÀ°§Ú¬Ý¤@¤UÀɮסAÁÂÁÂ
Just do it.

TOP

¦^´_ 11# jsc0518

With Range([02!g2]).Resize(n, 3)
>> With Sheets("02").Range("g2").Resize(n, 3)
½Ð­×§ï¦p¤W¡AÁÂÁ   

TOP

¦^´_ 12# samwang
Dear samwang,
¦­¦w¡I§Ú§ï¤F»yªk¦p¤U¡A¦ý¤´¥X²{¿ù»~400ªºµe­±
Noname.jpg
2021-11-20 06:49





Sub test()
Dim Arr, xD, Brr(), T$, T1$, i&, n%, m%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([01!a1], [01!c65536].End(3))
ReDim Brr(1 To UBound(Arr), 1 To 3)
For i = 2 To UBound(Arr)
    T = Arr(i, 2) & "|" & Arr(i, 3)
    T1 = Arr(i, 1) & "|" & Arr(i, 2) & "|" & Arr(i, 3)
    If xD.Exists(T) Then
        m = xD(T)
        If Not xD.Exists(T1) Then Brr(m, 3) = Brr(m, 3) + 1
    Else
        n = n + 1: xD(T) = n: xD(T1) = n
        Brr(n, 1) = Arr(i, 2)
        Brr(n, 2) = Arr(i, 3)
        Brr(n, 3) = 1
    End If
Next

With Sheets("02").Range("g2").Resize(n, 3)
    .Value = Brr
    .Sort Key1:=.Item(1), Order1:=1, _
          Key2:=.Item(2), Order2:=1, Header:=2
End With
End Sub
Just do it.

TOP

¦^´_ 13# jsc0518


§Ú´ú¸Õ¨S°ÝÃD¦pªþ¥ó¡A¤wªþ¤W§Ú´ú¸ÕªºÀɮסA½Ð¦A´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
1.gif

1111_1120.zip (15.62 KB)

TOP

¦^´_ 14# samwang
Dear samwang,
ÁÂÁ§AªºÀ°¦£Åo!§A©Ò´£¨Ñªºªþ¥óÀÉ®×¥i¥H¥Î¡A§Ú¦bcheck§Úªºexcel­þ¸Ì¦³°ÝÃD
·P®¦·P®¦¡I:)
Just do it.

TOP

¦^´_ 14# samwang
Dear samwang,
§Ú¦b¸Õrun¤F»yªk¡Aµo²{
¦b01¤u§@ªí¦A¦¸·s¼W¸ê®Æ
2021/1/31  A123456  R001   ---> ³o¨Ç§Ú³£³]©w¦P¤@¤é´Á(¦h¦C)
²Î­pªº¼Æ¶q¤SÅܦ¨¬O¥X²{"Á`"¦¸¼Æ
¦p°ÊºAÀÉ®×¾Þ§@
12333.gif
2021-11-20 15:56
Just do it.

TOP

¦^´_ 16# jsc0518

2021/1/31  A123456  R001   ---> ³o¨Ç§Ú³£³]©w¦P¤@¤é´Á(¦h¦C)
²Î­pªº¼Æ¶q¤SÅܦ¨¬O¥X²{"Á`"¦¸¼Æ
>> ¤£¦n·N«ä¡A§ó·s¦p¬õ¦r¡A½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test2()
Dim Arr, xD, Brr(), T$, T1$, i&, n%, m%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([01!a1], [01!c65536].End(3))
ReDim Brr(1 To UBound(Arr), 1 To 3)
For i = 2 To UBound(Arr)
    T = Arr(i, 2) & "|" & Arr(i, 3)
    T1 = Arr(i, 1) & "|" & Arr(i, 2) & "|" & Arr(i, 3)
    If xD.Exists(T) Then
        m = xD(T)
        If Not xD.Exists(T1) Then Brr(m, 3) = Brr(m, 3) + 1: xD(T1) = n
    Else
        n = n + 1: xD(T) = n: xD(T1) = n
        Brr(n, 1) = Arr(i, 2)
        Brr(n, 2) = Arr(i, 3)
        Brr(n, 3) = 1
    End If
Next

With Sheets("02").Range("g2").Resize(n, 3)
    .Value = Brr
    .Sort Key1:=.Item(1), Order1:=1, _
          Key2:=.Item(2), Order2:=1, Header:=2
End With
End Sub   

TOP

¦^´_ 16# jsc0518


Sub test_1()
Dim Arr, xD, Brr(), T$, T1$, i&, n%, m%
[02!g:i].ClearContents '¤£²Ö­p, ³o­n¥ý²MªÅ
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([01!a1], [01!c65536].End(3))
ReDim Brr(1 To UBound(Arr), 1 To 3)
For i = 2 To UBound(Arr)
    T = Arr(i, 2) & "|" & Arr(i, 3)
    T1 = Arr(i, 1) & "|" & T
    m = xD(T): xD(T1) = xD(T1) + 1
    If m = 0 Then
       n = n + 1: m = n: xD(T) = n
       Brr(n, 1) = Arr(i, 2): Brr(n, 2) = Arr(i, 3)
    End If
    If xD(T1) = 1 Then Brr(m, 3) = Brr(m, 3) + 1
Next
[02!g1:i1] = [{"®Æ¸¹","§å¸¹","¤Ñ¼Æ"}]
With [02!g2].Resize(n, 3)
     .Value = Brr
     .Sort Key1:=.Item(1), Order1:=1, _
           Key2:=.Item(2), Order2:=1, Header:=2
End With
End Sub

TOP

¦^´_ 17# samwang
Dear samwang,
´ú¸ÕOK¡A·P®¦§Aªº¤jÀ°¦£¡I
Just do it.

TOP

¦^´_ 18# ­ã´£³¡ªL
Dear ­ã´£³¡ªL,
·PÁ§Aªº¼ö¤ß¦^´_»P±Ð¾É¼Ú
Test OK. ¤j·PÁ¡I¡I
Just do it.

TOP

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