­pºâ¯S©w°Ï¶¡¦³Åã¥ÜªºÀx¦s®æ­Ó¼Æ

oak0723-1

¦pªþ¥óÀ£ÁYÀÉ
¥u­nAÄæ¦ì¦³Åã¥Ü¤§°Ï¶¡´N­pºâ¸Ó°Ï¶¡ªºCÄæ¦ì¤ÎEÄæ¦ì¸Ì¦³Åã¥ÜªºÀx¦s®æ­Ó¼Æ
ª½¨ì§ä¤£¨ì«áÄòªºAÄæ¦ìÀx¦s®æ¦³Åã¥Ü¬°¤î(A~EÄæ¦ì¦³Åã¥Üªº¦C¼Æ¬°¤£¤@©w)
¨Ò¦pªþ¥ó¹Ï¤ù©Ò¥Ü
A3Àx¦s®æ¦³Åã¥Ü,A20Àx¦s®æ¦³Åã¥Ü,©Ò¥H­pºâC3~C19¤ÎE3~E19Àx¦s®æÅã¥Üªº­Ó¼Æ
1>L3Àx¦s®æ¤º®eµ¥©óA3Àx¦s®æ®æ¤º®e
2>M3Àx¦s®æ¤º®eµ¥©óB3Àx¦s®æ®æ¤º®e
3>N3Àx¦s®æ¤º®eµ¥©óC3~C19Àx¦s®æÅã¥Üªº­Ó¼Æ
4>O3Àx¦s®æ¤º®eµ¥©óE3~E19Àx¦s®æÅã¥Üªº­Ó¼Æ
¥H¤UÃþ±À

samwang

¦^´_ 1# oak0723-1

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim Arr, Brr(), i&, i2&
Arr = [a2].CurrentRegion
ReDim Brr(1 To UBound(Arr), 1 To 6)
For i = 2 To UBound(Arr)
    If Trim(Arr(i, 1)) <> "" Then
        Brr(i - 1, 1) = Arr(i, 1): Brr(i - 1, 2) = Arr(i, 2)
        If Trim(Arr(i, 3)) <> "" Then Brr(i - 1, 3) = 1
        If Trim(Arr(i, 5)) <> "" Then Brr(i - 1, 4) = 1
        For i2 = i + 1 To UBound(Arr)
            If Trim(Arr(i2, 1)) <> "" Then Exit For
            If Trim(Arr(i2, 3)) <> "" Then Brr(i - 1, 3) = Brr(i - 1, 3) + 1
            If Trim(Arr(i2, 5)) <> "" Then Brr(i - 1, 4) = Brr(i - 1, 4) + 1
        Next
        Brr(i - 1, 5) = Brr(i - 1, 2) / Brr(i - 1, 3)
        Brr(i - 1, 6) = Brr(i - 1, 2) / Brr(i - 1, 4)
    End If
Next
Range("L3").Resize(UBound(Brr), 6) = Brr
End Sub

hcm19522

https://blog.xuite.net/hcm19522/twblog/590359821

oak0723-1

¦^´_ 2# samwang


  ÁÂÁÂ~·P®¦~

oak0723-1

¦^´_ 3# hcm19522

©êºp,§Ú¤£¤ÓÀ´¦p¦ó¨Ï¥Î(¦pªþ¥óÀÉ®×)

­ã´£³¡ªL

Sub TEST_A1()
Dim Arr, Brr, R&, i&, j%, V, S&, X(6)
R = Range("a:e").Find("*", , , , 1, 2).Row
Arr = Range("a2:e" & R + 1)
ReDim Brr(1 To R, 1 To 6)
For i = 2 To R - 1
    If Trim(Arr(i, 1)) <> "" Then
       X(1) = Trim(Arr(i, 1)): X(2) = Trim(Arr(i, 2)):  S = i - 1
       For j = 3 To 6: X(j) = 0: Next
    End If
    X(3) = X(3) - (Trim(Arr(i, 3)) <> ""): X(5) = X(2) / X(3)
    X(4) = X(4) - (Trim(Arr(i, 5)) <> ""): X(6) = X(2) / X(4)
    If Trim(Arr(i + 1, 1)) <> "" Or i = R - 1 Then
       For j = 1 To 6: Brr(S, j) = X(j): Next
    End If
Next i
[l3].Resize(R, 6) = Brr
End Sub


'==========================

oak0723-1

¦^´_ 6# ­ã´£³¡ªL


    ÁÂÁÂ
·P®¦
·P®¦

hcm19522

¦^´_ 5# oak0723-1

¼Æ¾ÚªÅ¥Õ «D¥þªÅ¥Õ {=IF($A2=" ","",IF(COLUMN(A1)<3,A2,SUM(N(LEN(OFFSET(A2,,,-LOOKUP(9,-MATCH(1=1,$A3:$A22<>" ",{1,0}))))>1))))