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

¤j¶q¸ê®Æ¤ñ¹ï­pºâ¤Î±Æ¦W

¤j¶q¸ê®Æ¤ñ¹ï­pºâ¤Î±Æ¦W

¦U¦ì¥ý¶i¦n
§Ú¦³¤@­Ó¸ê®Æ¦pªþ¥ó(¦]¤W¶ÇÀɮפj¤p­­¨î,¬G§R°£³¡¥÷¤u§@ªí¤ÎÀx¦s®æ)
1.(¦p¹Ï01)¦b¤u§@ªí"¤ñ¹ï"ªºI3~IN3Àx¦s®æ¿é¤J¸ê®Æ
2..(¦p¹Ï02)¤u§@ªí"01"ªºI3~IN3Àx¦s®æ¸ê®Æ=¤u§@ªí"¿é¤J"I3~IN3Àx¦s®æ¸ê®Æ
3..(¦p¹Ï02)¤u§@ªí"01"I3:IN1048528ªº¨C¤@¦CÀx¦s®æ¸ê®Æ³£»PI3~IN3Àx¦s®æ¸ê®Æ¤ñ¹ï,¬Û¦P=1,¤£¬Û¦P=0.ªÅ¥Õ=¤£¶i¦æ¤ñ¹ï,±N¤ñ¹ïµ²ªG³v¦CÅã¥Ü©óIT5:RY1048528Àx¦s®æ
4..(¦p¹Ï02)»P¤u§@ªí"01"¬Û¦Pªº¤u§@ªí¦³10­Ó,°£I3:IN1048528ªºÀx¦s®æ¤º¸ê®Æ¤£¤@¼Ë(¦C¼ÆÄæ¼Æ³£¤£¤@¼Ë),¨ä¥L³£¤@¼Ë
5..(¦p¹Ï01)±N¨C­Ó¤u§@ªí("01"~"10")ªºIT5:RY1048528Àx¦s®æ¤§¨C¤@¦C¦X­pÅã¥Ü©ó¤u§@ªí"¿é¤J"ªºI5~R1048528(­Y¾ã¦CªÅ¥Õ«h¤£§@­pºâ)
6.(¦p¹Ï01).±N¤u§@ªí"¿é¤J"ªºI5~R1048528«DªÅ¥Õ³v¦C¦X­p
7..(¦p¹Ï01)±N¤u§@ªí"¿é¤J"ªºI5~R1048528«DªÅ¥Õ³v¦C±Æ¦W(¨Ò¦pS5=SUMPRODUCT(($S$5:$S$14>=S5)*(1/COUNTIF(S$5:S$14,S$5:S$14))))

¹Ï01.jpg (239.16 KB)

¹Ï01.jpg

¹Ï02.jpg (238.51 KB)

¹Ï02.jpg

(1110513)¤ñ¹ï.rar (37.42 KB)

¦^´_ 1# oak0723-1

¹Ï1»P¹Ï2³£¬O¤@¼Ë

TOP

¦^´_ 2# samwang


  ©êºp©êºp
§Ñ¤FÀˬd

¹Ï02.jpg (424.43 KB)

¹Ï02.jpg

TOP

¦^´_ 3# oak0723-1 [/b


§Aªº¹Ï2»PªþÀɦ³ÂI¤£¤@¼Ë¡A¥BªþÀɪº¤½¦¡¤]¶]±¼¤F¡A
¹ï¤£°_«Ü»{¯uªº¦b¬Ý¤F¡A¦ýÁÙ¬O¬Ý¤£»Ý¨D¡A ¥i¥H¥t¥~´y±Ô¤@¤U¡A·PÁÂ

TOP

¦^´_ 4# samwang


    ©êºp©êºp
¤@¿ù¦A¿ù

§ó·s¸ê®Æ

(1).(¦p¹Ï001)¦b¤u§@ªí"¤ñ¹ï"ªºI3~IN3Àx¦s®æ¿é¤J¸ê®Æ
(2).(¦p¹Ï002)¤u§@ªí"01"ªºI3~IN3Àx¦s®æ¸ê®Æ=¤u§@ªí"¿é¤J"I3~IN3Àx¦s®æ¸ê®Æ
(3).(¦p¹Ï002)¤u§@ªí"01"I3:IN1048528ªº¨C¤@¦CÀx¦s®æ¸ê®Æ³£»PI3~IN3Àx¦s®æ¸ê®Æ¤ñ¹ï,¬Û¦P=1,¤£¬Û¦P=0.ªÅ¥Õ=¤£¶i¦æ¤ñ¹ï,±N¤ñ¹ïµ²ªG³v¦CÅã¥Ü©óIT5:RY1048528Àx¦s®æ
(4).(¦p¹Ï002)»P¤u§@ªí"01"¬Û¦Pªº¤u§@ªí¦³10­Ó,´Á¤º®e®æ¦¡¬Û¦P,¦ýI3:IN1048528ªºÀx¦s®æ¸ê®Æ¤£¦P
(5).(¦p¹Ï001)±N¨C­Ó¤u§@ªí("01"~"10")ªºIT5:RY1048528Àx¦s®æ¤§¨C¤@¦C¦X­pÅã¥Ü©ó¤u§@ªí"¿é¤J"ªºI5~R1048528
(6).(¦p¹Ï001).±N¤u§@ªí"¿é¤J"ªºI5~R1048528³v¦C¦X­p
(7).(¦p¹Ï001)±N¤u§@ªí"¿é¤J"ªºI5~R1048528³v¦C±Æ¦W(¨Ò¦p T5=SUMPRODUCT(($S$5:$S$14>=S5)*(1/COUNTIF(S$5:S$14,S$5:S$14))))

¹Ï002.jpg (320.14 KB)

¹Ï002.jpg

(1110513)¤ñ¹ï.zip (61.25 KB)

TOP

¦^´_ 5# oak0723-1

¦]¬°§Aªº´y­z¤¤²Ä1ÂI~7ÂI¦³¥]§t»¡©ú©M»Ý¨D¡A©Ò¥H¤£½T©w¦p¤U¬O§_¬°±zªº»Ý¨D¡A¦p¹Ï¤ù¬õ®Ø¬O¦³³B²zªºµ²ªG¡A½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C

Sub test()
Dim Ar_in, Arr, Arr1, Brr(), Crr(), xD, T, T1, n&, i&, j&, R&
Set xD = CreateObject("Scripting.Dictionary")
Ar_in = Sheets("¿é¤J").Range("i3:in3")
For sh = 2 To Sheets.Count
    With Sheets(sh)
        .Range("i3").Resize(1, UBound(Ar_in, 2)) = Ar_in
        R = .[b65536].End(3).Row: If R < 2 Then GoTo 95
        Arr1 = .Range(.[b5], .[b65536].End(3)): Arr = .Range("i3:in" & UBound(Arr1) + 4)
        ReDim Brr(1 To UBound(Arr), 1 To UBound(Arr, 2))       'IT3:RY
        ReDim Preserve Crr(1 To UBound(Arr) - 1, 1 To sh - 1)  '¿é¤Jªº²Î­p
        Crr(1, sh - 1) = .Name
        For x = 1 To UBound(Arr1)
            If Arr1(x, 1) = "" Then GoTo 95
            For i = 3 To UBound(Arr)
                For j = 1 To UBound(Arr, 2)
                    T = Arr(i, j): T1 = Arr(1, j)
                    If T1 = "" Then GoTo 90
                    If T1 = T Then
                        Brr(i - 2, j) = 1: n = n + 1
                    Else
                        Brr(i - 2, j) = 0
                    End If
90:             Next j
            Crr(i - 1, sh - 1) = n: n = 0
            Next i
95:     Next x
        .[it5].Resize(UBound(Brr), UBound(Brr, 2)) = Brr
    End With
Next
n = 0
With Sheets(1)
    .[i4:r4].NumberFormatLocal = "@"
    .[i4].Resize(UBound(Crr), UBound(Crr, 2)) = Crr
    With [s5].Resize(UBound(Crr) - 1)
            .Formula = "=Sum(i5:r5)": .Value = .Value
    End With
    With Range([s5], [s4].End(4))
        Arr = .Value
        .Sort Key1:=.Item(1), Order1:=2, Header:=2
        Brr = .Value: .Value = Arr
    End With
    For i = 1 To UBound(Brr)
        T = Brr(i, 1): If Not xD.Exists(T) Then n = n + 1: xD(T) = n
    Next
    For i = 1 To UBound(Arr): Arr(i, 1) = xD(Arr(i, 1)): Next
    .[t5].Resize(UBound(Arr)) = Arr
End With
End Sub

1.JPG (184.45 KB)

1.JPG

TOP

¦^´_ 6# samwang

¥ý¶i§A¦n
§Ú°õ¦æ«á¥d¦í¦p¹Ï
§Ú³o­ÓÀɪº·§©À´N¬O·Q­pºâ1²Õ¼Æ¦r¸ò¤u§@ªí01-10ªº¨C1¦C¼Æ¾Ú¤ñ¸û
­pºâ¨C¦C¬Û¦P¼Æ¾Úªº¼Æ¶q¦³¦h¤Ö

¹Ï0001.jpg (291.84 KB)

¹Ï0001.jpg

(1110516)¤ñ¹ïtest.part001.rar (1 MB)

(1110516)¤ñ¹ïtest.part002.rar (1 MB)

(1110516)¤ñ¹ïtest.part003.rar (1 MB)

(1110516)¤ñ¹ïtest.part004.rar (1 MB)

(1110516)¤ñ¹ïtest.part005.rar (1 MB)

(1110516)¤ñ¹ïtest.part006.rar (1 MB)

(1110516)¤ñ¹ïtest.part007.rar (1 MB)

(1110516)¤ñ¹ïtest.part008.rar (1 MB)

(1110516)¤ñ¹ïtest.part009.rar (1 MB)

(1110516)¤ñ¹ïtest.part010.rar (1 MB)

(1110516)¤ñ¹ïtest.part011.rar (1 MB)

(1110516)¤ñ¹ïtest.part012.rar (1 MB)

(1110516)¤ñ¹ïtest.part013.rar (202.04 KB)

TOP

¦^´_ 7# oak0723-1

¤w§ó·s¦p¤U¡A½Ð¦A´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
¥t¥~"¿é¤J"¤u§@ªí­n©ñ¦b²Ä1­Ó

Sub test()
Dim Ar_in, Arr, Arr1, Brr(), Crr(), xD, T, T1, n&, i&, j&, R&, sh%, MaxR&
Set xD = CreateObject("Scripting.Dictionary")
Ar_in = Sheets("¿é¤J").Range("i3:in3")
For sh = 2 To Sheets.Count
    With Sheets(sh)
        .Range("i3").Resize(1, UBound(Ar_in, 2)) = Ar_in
        R = .[b65536].End(3).Row: If R < 2 Then GoTo 95
        Arr1 = .Range(.[b5], .[b65536].End(3)): Arr = .Range("i3:in" & UBound(Arr1) + 4)
        ReDim Brr(1 To UBound(Arr), 1 To UBound(Arr, 2)) 'IT3:RY
        ReDim Preserve Crr(1 To 100000, 1 To sh - 1)     '¿é¤Jªº²Î­p
        If MaxR < UBound(Arr) Then MaxR = UBound(Arr)
        Crr(1, sh - 1) = .Name
        For x = 1 To UBound(Arr1)
            If Arr1(x, 1) = "" Then GoTo 95
            For i = 3 To UBound(Arr)
                For j = 1 To UBound(Arr, 2)
                    T = Arr(i, j): T1 = Arr(1, j)
                    If T1 = "" Then GoTo 90
                    If T1 = T Then
                        Brr(i - 2, j) = 1: n = n + 1
                    Else
                        Brr(i - 2, j) = 0
                    End If
90:             Next j
            Crr(i - 1, sh - 1) = n: n = 0
            Next i
95:     Next x
        .[it5].Resize(UBound(Brr), UBound(Brr, 2)) = Brr
    End With
Next
n = 0
With Sheets(1)
    .[i4:r4].NumberFormatLocal = "@"
    .[i4].Resize(MaxR, UBound(Crr, 2)) = Crr
    With .[s5].Resize(MaxR - 2)
            .Formula = "=Sum(i5:r5)": .Value = .Value
    End With
    With Range([s5], [s4].End(4))
        Arr = .Value
        .Sort Key1:=.Item(1), Order1:=2, Header:=2
        Brr = .Value: .Value = Arr
    End With
    For i = 1 To UBound(Brr)
        T = Brr(i, 1): If Not xD.Exists(T) Then n = n + 1: xD(T) = n
    Next
    For i = 1 To UBound(Arr): Arr(i, 1) = xD(Arr(i, 1)): Next
    .[t5].Resize(UBound(Arr)) = Arr
End With
End Sub

1.JPG (144.75 KB)

1.JPG

TOP

¦^´_ 8# samwang


    §Ú±N¤u§@ªí09ªº!5~IN1048528°Ï°ì¸ê®Æ¥þ³¡§R°£°õ¦æµ{¦¡«á¦A¿é¤JA1¸ê®Æ
µ²ªG´N¥d¦í¤F(¦p¹Ï)

¹Ï00001.jpg (282.21 KB)

¹Ï00001.jpg

(1110516-1)¤ñ¹ïtest.rar (218.55 KB)

TOP

¦^´_  samwang


    §Ú±N¤u§@ªí09ªº!5~IN1048528°Ï°ì¸ê®Æ¥þ³¡§R°£°õ¦æµ{¦¡«á¦A¿é¤JA1¸ê®Æ
µ²ªG´N¥d¦í ...
oak0723-1 µoªí©ó 2022-5-16 19:49


¤w§ó·s¦pªþ¥ó¡A½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

(1110516-1)¤ñ¹ïtest_0516.zip (199.4 KB)

TOP

        ÀR«ä¦Û¦b : ¤â¤ß¦V¤U¬O§U¤H¡A¤â¤ß¦V¤W¬O¨D¤H¡F§U¤H§Ö¼Ö¡A¨D¤Hµh­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD