[attach]34072[/attach]
C26 =從47列起始，每跳17列統計有顯示"0"的儲存格之個數加總。

Sub test()
Dim Arr, n%, i&, j&
R = [b65536].End(3).Row
Arr = Range("c47:ay" & R)
For j = 1 To UBound(Arr, 2)
For i = 1 To UBound(Arr) Step 17
If Arr(i, j) = "" Then GoTo 99
If Arr(i, j) = 0 Then n = n + 1
99: Next i
Arr(1, j) = n: n = 0
Next j
Range("c26").Resize(1, 49) = Arr
End Sub

================================

EX : 在C26補充每跳17列統計有顯示"0"的個數~因為"最小的數字不一定是"0"~將"O"統計出個數比對。個數愈多的C1:AY1對應號碼~不開出的機率愈大。

====================================

=IF(SUMPRODUCT(SUBTOTAL(3,OFFSET(C47,ROW(\$1 : \$1026)*17-17,)))>0,SUMPRODUCT(SUBTOTAL(3,OFFSET(C47,ROW(\$1 : \$1026)*17-17,))),"")

C26
=IF(SUMPRODUCT(SUBTOTAL(2,OFFSET(C47,ROW(\$1:\$1026)*17-17,)))>0,SUMPRODUCT(SUBTOTAL(2,OFFSET(C47,ROW(\$1:\$1026)*17-17,))*(N(OFFSET(C47,ROW(\$1:\$1026)*17-17,))=0)),"")

(N(OFFSET(C47,ROW(\$1 : \$1026)*17-17,))=0)
N是什麼意思?

ol

N(OFFSET(....)) 這是N()是搭配OFFSET()使用，一言難盡，上網找 N(OFFSET( 說明吧

1_

2_
C27=統計C47 : AY1026當欄的各"最小"列~有顯示"0"值的總個數

[attach]34088[/attach]

>> 不好意思，公式不會，2#程式碼只要多個判斷如下僅供參考，謝謝
Sub test()
Dim Arr, n, i&, j&
R = [b65536].End(3).Row
Arr = Range("c47:ay" & R)
For j = 1 To UBound(Arr, 2)
For i = 1 To UBound(Arr) Step 17
If Arr(i, j) = "" Then GoTo 99
If Arr(i, j) = 0 Then n = n + 1
99: Next i
If n > 0 Then Arr(1, j) = n: n = 0
Next j
Range("c26").Resize(1, 49) = Arr
End Sub

https://www.excelhome.net/1436.html
https://club.excelhome.net/forum ... &extra=page%3D1
https://club.excelhome.net/forum ... &extra=page%3D1

=IF(SUMPRODUCT(SUBTOTAL(3,OFFSET(C47,ROW(\$1:\$1026)*17-17,))*(N(OFFSET(C47,ROW(\$1:\$1026)*17-17,))=0))=0,"",SUMPRODUCT(SUBTOTAL(3,OFFSET(C47,ROW(\$1:\$1026)*17-17,))*(N(OFFSET(C47,ROW(\$1:\$1026)*17-17,))=0)))

samwang大大 : 您好 !

C26
=IFERROR(1/(1/SUMPRODUCT(SUBTOTAL(2,OFFSET(C47,ROW(\$1:\$1026)*17-17,))*(N(OFFSET(C47,ROW(\$1:\$1026)*17-17,))=0))),"")

=IF(SUMPRODUCT(SUBTOTAL(2,OFFSET(C47,ROW(\$1:\$1026)*17-17,))*(N(OFFSET(C47,ROW(\$1:\$1026)*17-17,))=0))>0,
SUMPRODUCT(SUBTOTAL(2,OFFSET(C47,ROW(\$1:\$1026)*17-17,))*(N(OFFSET(C47,ROW(\$1:\$1026)*17-17,))=0)),"")

PS：
1_小弟的1樓提問，如果改為 C26=

2_我只會用2層IF，還不懂怎麼用{ n；" V " }

[attach]34090[/attach]
[attach]34091[/attach]

[attach]34099[/attach]

 歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)