[µo°Ý] «ü©w¸¹½X¦b°¸¼ÆÄ檺Á`­Ó¼Æ¤Î©_¼ÆÄ檺Á`¦¸¼Æ

ziv976688

TEST.rar (6.25 KB)

¤U¸ü¦¸¼Æ: 7

2021-8-19 10:01

C2= (M$2¡JDF$50)ªº°¸¼ÆÄæ=B2ªºÁ`­Ó¼Æ
C3= (M$2¡JDF$50)ªº°¸¼ÆÄæ=B3ªºÁ`­Ó¼Æ
¡J
¡J
C50= (M$2¡JDF$50)ªº°¸¼ÆÄæ=B50ªºÁ`­Ó¼Æ


D2= (M$2¡JDF$50)ªº°¸¼ÆÄæ=B2¤§©_¼ÆÄæÁ`¦¸¼Æ
D3= (M$2¡JDF$50)ªº°¸¼ÆÄæ=B3¤§©_¼ÆÄæÁ`¦¸¼Æ
¡J
¡J
D50= (M$2¡JDF$50)ªº°¸¼ÆÄæ=B50¤§©_¼ÆÄæÁ`¦¸¼Æ

½Ð°Ý¡J
C2ªº¨ç¼Æ¤½¦¡ ?
D2ªº¨ç¼Æ¤½¦¡ ?
ÁÂÁÂ !

ML089

C2 =COUNT(0/((B2=M$2:DE$99)*MOD(COLUMN(M:DE),2)))
D2 =SUM((B2=M$2:DE$99)*MOD(COLUMN(M:DE),2)*N$2:DF$99)

samwang

¦^´_ 1# ziv976688


Sub test()
Dim Arr, xD, T%, i&, j&, Tm
Tm = Timer
Set xD = CreateObject("Scripting.Dictionary")
R = Columns("M:DF").Find("*", , , , , 2).Row
Arr = Range("M1:DF" & R)
For j = 1 To UBound(Arr, 2) Step 2
    For i = 2 To UBound(Arr)
        T = Arr(i, j): If T = 0 Then GoTo 98
        xD(T & "/1") = xD(T & "/1") + 1
        xD(T & "/2") = xD(T & "/2") + Arr(i, j + 1)
    Next i
98: Next j
Arr = Range([C1], [B65536].End(3))
For i = 2 To UBound(Arr)
    For j = 1 To 2: Arr(i - 1, j) = xD(Arr(i, 1) & "/" & j): Next
Next
[c2].Resize(UBound(Arr) - 1, 2) = Arr
MsgBox Timer - Tm
End Sub

ziv976688

¦^´_ 2# ML089
ª©¥D¤j¤j:
ÁÂÁ±zªº«ü¾É©MÀ°¦£~·P®¦

ziv976688

¦^´_ 3# samwang
S¤j¤j:
ÁÂÁ±zªº«ü¾É©MÀ°¦£~·P®¦

ziv976688

¦^´_ 3# samwang
S¤j¤j:±z¦n!
¤£¦n·N«ä~¦³ÂI¤p°ÝÃD~·q½Ð½ç¥¿~ÁÂÁÂ

¤U¸ü (18.69 KB)

2021-8-20 05:47

TEST_1.rar (15.61 KB)

¤U¸ü¦¸¼Æ: 2

2021-8-20 05:48

samwang

¦^´_ 6# ziv976688


§AªºBÄ檺¼Æ¦r1~49 ¤£¨£¤F¡A©Ò¥H¾É­P¨º­Ó°ÝÃD¡AÁÂÁÂ

ziv976688

¦^´_ 7# samwang
¤£¦n·N«ä~§Ú²¨©¿¤F
ÁÂÁ±z

ziv976688

¦^´_ 2# ML089
¦^´_ 3# samwang
TEST_0822.rar (31.76 KB)

¤U¸ü¦¸¼Æ: 3

2021-8-22 01:46

¤U¦C·s¼W»Ý¨Dªº»yªk~Àµ½Ð¤G¦ì¤j¤jÄ~Äò½ç±Ð¡CÁÂÁ !
.[A2] = ((M1:DF1)<>""ªºÄæ¼Æ)/2 & "­Ó"   '¦C10
.[A4:A10] =·íA$1<>""®É¡A«hÅã¥ÜDATA!AÄæ=A$1´Á¼Æ¤§B:H¸¹½X   '¦C12

'¦C32¥H¤U
.[E2:E50] =·íD2>0®É¡A«hÅã¥ÜD2/C2¤§­È¡C   
.[G2:G50] =.[C2:C50]ªº­È~¥Ñ¤j¦Ó¤p¨Ì§Ç©¹¤U±Æ¦C¡C
.[H2:H50] =±N.[G2:G50] <>""ªº±Æ¦W(¥i­«½Æ)¨Ì§Ç©¹¤U±Æ¦C¡C
.[I2:I50] =·í.[G2:G50]¤¤<>""ªº¬Y­È=.[C2:C50]ªº¬Y­È®É¡A«hÅã¥Ü.[C2:C50]ªº¸Ó¬Y­È¦P¦CªºBÄæ­È¡C  
.[J2:J50] =·í.[I2:I50]¤¤ªº¬Y­È=.[B2:B50]ªº¬Y­È®É¡A«hÅã¥Ü.[B2:B50]ªº¸Ó¬Y­È¦P¦CªºDÄæ­È¡C  
.[K2:K50] =·í.[I2:I50]¤¤ªº¬Y­È=.[B2:B50]ªº¬Y­È®É¡A«hÅã¥Ü.[B2:B50]ªº¸Ó¬Y­È¦P¦CªºEÄæ­È¡C

samwang

¦^´_  ML089
¦^´_  samwang


¤U¦C·s¼W»Ý¨Dªº»yªk~Àµ½Ð¤G¦ì¤j¤jÄ~Äò½ç±Ð¡CÁÂÁ !
.[A2] = ((M1F1) ...
ziv976688 µoªí©ó 2021-8-22 01:47

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim Arr, Brr(1 To 7), Crr, xD, T%, i&, j&
Set xD = CreateObject("Scripting.Dictionary")
With Sheets("Sheet1")
T = .[A1]
Arr = Range([DATA!h1], [DATA!a65536].End(3))
For i = 2 To UBound(Arr)
    If Arr(i, 1) = T Then
        For j = 2 To 8: n = n + 1: Brr(n) = Arr(i, j): Next
    End If
Next
.[A4].Resize(7) = Application.Transpose(Brr)
.[A2] = (.Cells(1, 256).End(xlToLeft).Column - 12) / 2
Arr = .Range(.[B2], .[e65536].End(3))
ReDim Crr(1 To UBound(Arr), 1 To 5)
For i = 1 To UBound(Arr)
    If Arr(i, 3) > 0 Then Arr(i, 4) = Arr(i, 3) / Arr(i, 2)
    Crr(i, 1) = Arr(i, 2): Crr(i, 2) = Arr(i, 2)
    Crr(i, 3) = Arr(i, 1): Crr(i, 4) = Arr(i, 3)
    Crr(i, 5) = Arr(i, 4)
Next
.[B2].Resize(UBound(Arr), 4) = Arr
With Range("g2").Resize(UBound(Crr), 5)
    .Value = Crr
    .Sort key1:=.Item(1), Order1:=2, Header:=xlNo
    Crr = .Value
End With
T = Application.Max(.Range("g2:g" & UBound(Crr)))
For i = 1 To UBound(Crr)
    Crr(i, 1) = T - Crr(i, 1) + 1
Next
[H2].Resize(UBound(Crr), 1) = Crr
End With
End Sub