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

¨C¦¸¿z¿ï«á¦p¦ó¦A¥H±ø¥ó¥[Á`

¨C¦¸¿z¿ï«á¦p¦ó¦A¥H±ø¥ó¥[Á`

°ÝÃD¡Gitem¨C¦¸¿z¿ï³£¥Îv,ªí¤W¤wv¦p¦ó¥[Á`
subtotal(109,b2:b2000),¦p¦ó°t¦óif¥\¯à®ÄªG¡H

0816.zip (7.52 KB)

§Æ±æ¤ä«ù!

{=MAX((A2:A2000="v")*SUBTOTAL(109,OFFSET(B2,,,ROW(1:1999))))}
300 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

{=MAX(IF(A2:A2000="v",SUBTOTAL(109,OFFSET(B2,,,ROW(B1:B1999)))))
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

³o¼Ë¸ÑÄÀ¬O§_¥¿½Tf9¥i¬Ý¨ì°}¦C¡A¡]¤wÁôÂÃ¥H0ªí¥Ü¡^¡ASUBTOTAL(4,OFFSET(B1,ROW(1:20),))Åã¥Ü6000¬O¦]¬°¡]4,¨úmaxªº­ì¦]¡A­Yf9¥i¬Ý¥Xv¬Û¹ïÀ³ªº­È¡A¦A³z¹Lsumproduct(©M¡A±ø¥ó¡^§ä¨ìµª®×¡C
§Æ±æ¤ä«ù!

TOP

B1,ROW(1:2000),))*(A2:A2001="v)
¥t¥~³o¸Ì¬°¦ó®t¤@®æ¡H¦p§ï¦¨¤@¼Ë·|¥X²{n/a
§Æ±æ¤ä«ù!

TOP

¦^´_ 5# s7659109

»¡¤]¤£²M·¡~~
=SUBTOTAL(4,OFFSET(B1,ROW(1:20),)) ¥Î20­ÓÀx¦s®æ´ú¸Õ, «öF9¥h¬Ý
¦A¥[¤J *(A2:A21="v") «öF9¬Ý¬Ý

TOP

SUMPRODUCT(SUBTOTAL(4,OFFSET(B1,ROW(1:2000),))*(A2:A2001="v"))³o­Ó¬°ans
¦ýSUBTOTAL(4,OFFSET(B1,ROW(1:2000),))©î¥X¨Ó=6000
¤GªÌµ²¦X¡A¬O¦p¦ó§PÂ_¦b¤w¦³¿z¿ï¤U,aÄ椤¦³v,²Î­p¬Û¹ïÀ³Äæ¦ì©O¡H
§Æ±æ¤ä«ù!

TOP

=SUMPRODUCT(SUBTOTAL(4,OFFSET(B1,ROW(1:2000),))*(A2:A2001="v"))
=SUMIF(A:A,"v",B:B)

¥ý²q¤@¤U

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2018-8-16 09:49 ½s¿è

¦^´_ 1# s7659109
¥H¼Æ¾Ú¨ì²Ä2000¦C¬°¨Ò
=SUMPRODUCT((A2:A2000="v")*(B2:B2000))
½Ð°Ñ¦Ò

TOP

¬Ý¤£À´¤°»ò·N«ä???

TOP

        ÀR«ä¦Û¦b : ¤f»¡¤@¥y¦n¸Ü¡A¦p¤f¥X½¬ªá¡F¤f»¡¤@¥yÃa¸Ü¦p¤f¦R¬r³D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD