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

[µo°Ý] ¸õ¦C²Î­p¦³Åã¥Ü"0"ªºÀx¦s®æ¤§Á`­Ó¼Æ

[µo°Ý] ¸õ¦C²Î­p¦³Åã¥Ü"0"ªºÀx¦s®æ¤§Á`­Ó¼Æ

¸õ¦C²Î­p¦³Åã¥Ü0ªºÀx¦s®æ¤§Á`­Ó¼Æ.rar (23.4 KB)
C26 =±q47¦C°_©l¡A¨C¸õ17¦C²Î­p¦³Åã¥Ü"0"ªºÀx¦s®æ¤§­Ó¼Æ¥[Á`¡C

½Ð°Ý¡GC26ªº¤½¦¡­n«ç»ò­×¥¿¡H
ÁÂÁ¡I

¦^´_ 1# ziv976688

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
¥t¥~¡A¬°¤°»ò³£¨S¦³0®É°Ñ¦Òµª®×¦³0©MªÅ¥Õ?

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

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-9-28 15:23 ½s¿è

¦^´_ 2# samwang
µª®×¥¿½T
ÁÂÁ±zªº­@¤ß«ü¾É~·P®¦
================================
¥t¥~¡A¬°¤°»ò³£¨S¦³0®É°Ñ¦Òµª®×¦³0©MªÅ¥Õ?
¦]¬°¥Ø«e©Ò¦³°ò¥»»Ý¨D³£¤w§¹¦¨~©Ò¥H¦b¹ê»Ú¾Þ§@«á¡A·|¦³¤@¨Ç²Ó¶µ¥²¶·¸É¥R~
EX : ¦bC26¸É¥R¨C¸õ17¦C²Î­p¦³Åã¥Ü"0"ªº­Ó¼Æ~¦]¬°"³Ì¤pªº¼Æ¦r¤£¤@©w¬O"0"~±N"O"²Î­p¥X­Ó¼Æ¤ñ¹ï¡C­Ó¼Æ·U¦hªºC1:AY1¹ïÀ³¸¹½X~¤£¶}¥Xªº¾÷²v·U¤j¡C
²Ó¶µ¸É¨¬«á~´N¾ã²zÂkÀɦs©³¡C
====================================
¦]¬°¾á¤ß¤é«á»Ý¨D¦³ÅÜ°Ê¡A¦Ó¥½¾Ç¦p¤S¤£·|¦Û¦æ­×§ïµ{¦¡(³oÃþ»Ý¨Dªºµ{¦¡½X¡Aª¦¤å¬O¤£¥i¯à§ä¨ì)~
©Ò¥H³o¤@ÃD~¤p§ÌÁÙ¬O§Æ±æ¦³¨ç¼Æ¸Ñ(EXCEL2003ª©)~
¥Ø«eªºC26¦³ª¦¤å§ä¨ìÃþ¦ü¨ç¼Æ¸Ñ~«DªÅ¥ÕªºÀx¦s®æ­Ó¼Æ¥[Á`~
=IF(SUMPRODUCT(SUBTOTAL(3,OFFSET(C47,ROW($1 : $1026)*17-17,)))>0,SUMPRODUCT(SUBTOTAL(3,OFFSET(C47,ROW($1 : $1026)*17-17,))),"")
¥u¬O­n¦A¥[¤W³æ¿W­pºâ¦³Åã¥Ü"0"ªºÀx¦s®æ­Ó¼Æ±ø¥ó~¦p·Q¥HCOUNTIF©ÎSUMIF¦A°t¦X~¦ý¤@ª½¼g¤£¥X¨Ó~¥¿¦b§V¤Oª¦¤å¤¤

¥H¤W  ¦A¦¸ÁÂÁ±zªº«ü¾É©MÀ°¦£

TOP

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¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-9-28 21:52 ½s¿è

¦^´_ 4# ML089
µª®×§¹¥þ²Å¦X»Ý¨D~·P®¦

½Ð±Ðª©¥D :
(N(OFFSET(C47,ROW($1 : $1026)*17-17,))=0)
N¬O¤°»ò·N«ä?

¤S¦pªG·íÄæ"³Ì¤p"³£¨S¦³Åã¥Ü"0"ªºÀx¦s®æ~
«h¸ÓC26 : AY26ªºÀx¦s®æÅã¥Ü""
¤½¦¡­n«ç»ò­×§ï?
¯Âºé¥u¬O¤½¦¡¬ã²ß~¥¼«Bº÷Á[~¥H³Æ«á»Ý~
ÁÂÁ±z:l
ol

TOP

N(OFFSET(....)) ³o¬ON()¬O·f°tOFFSET()¨Ï¥Î¡A¤@¨¥ÃøºÉ¡A¤Wºô§ä N(OFFSET( »¡©ú§a

¦pªG·íÄæ"³Ì¤p"³£¨S¦³Åã¥Ü"0"ªºÀx¦s®æ~
«h¸ÓC26 : AY26ªºÀx¦s®æÅã¥Ü""
¤£¬O«ÜÀ´
³Ì¦n¦³¼ÒÀÀµª®×¡A¤ñ¸û¯à¤F¸Ñ§Aªº»¡©ú
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-9-29 00:46 ½s¿è

¦^´_ 6# ML089
1_
¦³¤Wºô§ä¹L~¨S¦³§ä¨ì¾A·íªº»¡©ú~¤p§Ì¦AÄ~Äò§ä¬Ý¬Ý~ÁÂÁ±z

2_
C27=²Î­pC47 : AY1026·íÄ檺¦U"³Ì¤p"¦C~¦³Åã¥Ü"0"­ÈªºÁ`­Ó¼Æ¡F
¦p·íÄ檺¦U"³Ì¤p"¦C~³£µLÅã¥Ü"0"­È®É~«hÅã¥Ü""       
¸Ô¦p½d¨ÒÀÉC27 :AY27
¸õ¦C²Î­p¦³Åã¥Ü0ªºÀx¦s®æ¤§Á`­Ó¼Æ_V2.rar (23.99 KB)

½Ð°Ý: C27ªº¨ç¼Æ¤½¦¡ ?
Àµ½Ð½ç±Ð~ÁÂÁ±z

TOP

¦^´_ 7# ziv976688

¦p·íÄ檺¦U"³Ì¤p"¦C~³£µLÅã¥Ü"0"­È®É~«hÅã¥Ü""        
>> ¤£¦n·N«ä¡A¤½¦¡¤£·|¡A2#µ{¦¡½X¥u­n¦h­Ó§PÂ_¦p¤U¶È¨Ñ°Ñ¦Ò¡AÁÂÁÂ
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

TOP

¦^´_ 7# ziv976688

¦³Ãö OFFSET()»P N(OFFSET(....)) ªº¨Ï¥Î¡A¦pªG¼Æ¾Ú·½¬O³æ¤¸®æ¤Þ¥Î¡A³q±`¨Ï¥ÎN/T+OFFSET²Õ¦X¨ç¦¡¹ê²{¡C
°Ñ¦Òºô§}¦p¤U
https://www.excelhome.net/1436.html
https://club.excelhome.net/forum ... &extra=page%3D1
https://club.excelhome.net/forum ... &extra=page%3D1
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 7# ziv976688


¦p·íÄ檺¦U"³Ì¤p"¦C~³£µLÅã¥Ü"0"­È®É~«hÅã¥Ü""  
=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)))

TOP

        ÀR«ä¦Û¦b : ¯u¥¿ªº·R¤ß¡A¬O·ÓÅU¦n¦Û¤vªº³oÁû¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD