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

[µo°Ý] sumif ¤½¦¡¦p¦ó­×§ï?

[µo°Ý] sumif ¤½¦¡¦p¦ó­×§ï?

¥»©«³Ì«á¥Ñ t8899 ©ó 2014-4-15 11:49 ½s¿è

=SUMIF(H3:H301,"v",F3:F301)
·Q§ï¦¨F3:F301³Ì«á30µ§(¦³¸ê®Æ)ªºÁ`¦X??(³Ì«á¤@µ§¦³¸ê®Æ¡A©¹«e¼Æ30­ÓÀx¦s®æ)

¥»©«³Ì«á¥Ñ p212 ©ó 2014-4-15 13:53 ½s¿è

¦^´_ 1# t8899
F3:F301¸ê®Æ¬°³sÄò©Êªº¡A§í¬°¤£³sÄò¡H°ÝÃD¦³ÂI¼Ò½k¡B¦³³\¦hªº¥i¯à¡A¥i§_´£¨Ñ½d¨ÒÀɰѦҬã¨s¡H
ÁÂÁ¡I

TOP

¥»©«³Ì«á¥Ñ t8899 ©ó 2014-4-15 16:20 ½s¿è
¦^´_  t8899
F3:F301¸ê®Æ¬°³sÄò©Êªº¡A§í¬°¤£³sÄò¡H°ÝÃD¦³ÂI¼Ò½k¡B¦³³\¦hªº¥i¯à¡A¥i§_´£¨Ñ½d¨ÒÀɰѦҬã¨s¡H ...
p212 µoªí©ó 2014-4-15 13:50

Book3.rar (9.52 KB)
³sÄò©Ê(Àɮתº¨Ò¤lF19:F48),¦p¦A¦h¤@¦æÅܦ¨ F20:F49
¡A¤£³sÄòÃø«×À³¸Ó«Ü°ª

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-4-16 08:56 ½s¿è

¦^´_ 3# t8899
1¡B½Ð©óBook3.xls¥ý¶i¦æ¡u©w¸q¦WºÙ¡v (±ø¥ó¡G­pºâ¸ê®Æ¸s³Ì«á¤@µ§¦³¸ê®ÆªºÀx¦s®æ©¹«e¼Æ30­ÓÀx¦s®æ)
x =Sheet1!$H$3:$H$301
y =OFFSET(Sheet1!$D$1,MAX(IF(x="v",ROW(x),FALSE))-30,4,30)
z =OFFSET(Sheet1!$D$1,MAX(IF(x="v",ROW(x),FALSE))-30,2,30)
2¡B±N1#°Ýªº¤½¦¡=SUMIF(H3:H301,"v",F3:F301)§ï³y¦¨
=SUMIF(y,"v",z)
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 3# t8899
©Î±N1#°Ýªº¤½¦¡=SUMIF(H3:H301,"v",F3:F301)§ï³y¦¨
=SUMPRODUCT((y="v")*z)
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 3# t8899

¦¡¤@ =SUMPRODUCT((H3:H301="v")*(ROW(H3:H301)>MATCH(9^9,F:F)-30),F3:F301)

¦¡¤G =SUMIF(OFFSET(H1,MATCH(9^9,E:E)-1,,-30),"v",OFFSET(F1,MATCH(9^9,E:E)-1,,-30))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¤Ó¶§¥ú¤j¡B¤÷¥À®¦¤j¡B§g¤l¶q¤j¡A¤p¤H®ð¤j¡C
ªð¦^¦Cªí ¤W¤@¥DÃD