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

[µo°Ý] µn¿ý">0"­Èªº³sÄò­Ó¼Æ(§t1­Ó)

[µo°Ý] µn¿ý">0"­Èªº³sÄò­Ó¼Æ(§t1­Ó)

¥»©«³Ì«á¥Ñ johnny1680 ©ó 2020-12-9 17:21 ½s¿è


´ú¸ÕÀÉ :   µn¿ý¤j©ó0­Èªº³sÄò­Ó¼Æ(§t1­Ó).rar (2.92 KB)
AÄ欰>=0ªº¦U³Q¤ñ¹ï­È¡A¦U­È¤§¶¡¤£·|¦³ªÅ¥Õ®æ¡C
BÄ欰­pºâAÄæ>0­Èªº³sÄò­Ó¼Æ(§t1­Ó)¤§µn¿ýÄæ¡C

»Ý¨D¡J
½Ð±NAÄæ>0­È­pºâ«áªº1­Ó©M³sÄòn­Óªº­Ó¼Æ¡A
µn¿ý©óAÄæ¦U¬q>0³sÄò­Èªº³Ì¥½­È¤§¦P¦CBÄæÀx¦s®æ¡C

½Ð°Ý¡JB2ªº¨ç¼Æ¤½¦¡­n«ç»ò¼g¡H
ÁÂÁ¡I

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

TOP

¦^´_ 2# hcm19522
ÁÂÁ±zªº«ü¾É!

TOP

¦^´_ 1# johnny1680

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim Arr, Brr(), T0%, T%, T1%, s
Arr = Range([a1], [a65536].End(3))
ReDim Brr(1 To UBound(Arr), 1 To 1)
For i = 2 To UBound(Arr)
    If i > 2 Then T0 = Arr(i - 1, 1)
    T = Arr(i, 1)
    If i < UBound(Arr) Then
        T1 = Arr(i + 1, 1)
    Else
         If T <> 0 Then Brr(i, 1) = i - s
    End If
    If T = 0 Then
        s = i
    Else
        If T1 = 0 And s <> "" Then: Brr(i, 1) = i - s
    End If
Next
[b1].Resize(UBound(Arr), 1) = Brr
[b1] = "µn¿ý"
End Sub

TOP

¦^´_ 2# hcm19522

A2¼Æ­È¤£¬°0®É¡A·|¦³¤p°ÝÃD¡A½Ð½T»{¡AÁÂÁ¡C

11.PNG (14.51 KB)

11.PNG

TOP

¤@¯ë:
=IF(OR(A2=0,A3>0),"",COUNTIF(A$1:A2,">0")-SUM(B$1:B1))

°}¦C:
=IF(OR(A2=0,A3>0),"",ROW()-MATCH(2,0/ISERR(0/A$1:A1)))

TOP

¤£¦n·N«ä¡A§ó·s4#µ{¦¡¦p¤U¬õ¦r¡A¬Ý¤F­ã¤j¸Ñµª²×©ó¤F¸ÑÃD·N¡AÁÂÁÂ
Sub test()
Dim Arr, Brr(), T0%, T%, T1%, s
Arr = Range([a1], [a65536].End(3))
ReDim Brr(1 To UBound(Arr), 1 To 1)
s = 1
For i = 2 To UBound(Arr)
    If i > 2 Then T0 = Arr(i - 1, 1)
    T = Arr(i, 1)
    If i < UBound(Arr) Then
        T1 = Arr(i + 1, 1)
    Else
         If T <> 0 Then Brr(i, 1) = i - s
    End If
    If T = 0 Then
        s = i
    Else
        If T1 = 0 Then: Brr(i, 1) = i - s
    End If
Next
[b1].Resize(UBound(Arr), 1) = Brr
[b1] = "µn¿ý"
End Sub

TOP

Sub test()
Dim Arr, V&, N&
Arr = Range([a1], [a65536].End(3)(2))
For i = 2 To UBound(Arr) - 1
    V = Val(Arr(i, 1)): Arr(i, 1) = ""
    If V = 0 Then GoTo 101 Else N = N + 1
    If Val(Arr(i + 1, 1)) = 0 Then Arr(i, 1) = N: N = 0
101: Next
Arr(1, 1) = "µn¿ý"
[b1].Resize(UBound(Arr) - 1, 1) = Arr
End Sub

TOP

¦^´_ 6# ­ã´£³¡ªL
¦^´_ 7# samwang
¦^´_ 8# ­ã´£³¡ªL
·PÁ¤G¦ì¤j¤jªº¤£§[«ü¾É~·P®¦³á   

TOP

        ÀR«ä¦Û¦b : ªY½à§O¤H´N¬O²øÄY¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD