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

«D©T©w¦C¼Æ­pºâ¡I

«D©T©w¦C¼Æ­pºâ¡I

¤H¼Æ¤é³øªí.rar (14.02 KB) ½Ð°Ý¦U¦ì¥ý¶i¡A¤p§Ì¦³¤@«D¤W¯Z¤H¼Æ¤é³øªí¡A¨ä¤¤¤é´ÁÄæ¬O 1 ¦Ü 31 ¤é¡A2¤éªº¡u«e¤é¤W¯Z¤H¼Æ¡v¬O1¤é¡u·í¤é¤W¯Z¤H¼Æ¡v¦A¡Ï2¤é·í¤é¤½®t¤H¼Æ¡Ð2¤é·í¤é½Ð°²¤H¼Æ¡A´N¬O2¤éªº¡u·í¤é¤W¯Z¤H¼Æ¡v¡A¨C¤é¤½®t¤Î½Ð°²¤H¼Æ¤£¤@¡A¤]¤£¬O¨C¤é³£·|¦³¤½®t¤Î½Ð°²¤H¼Æ¡A¤p§Ì­ì¥ý·Qªk¬O¥ý­pºâ¨C¤éªº¤½®t¤Î½Ð°²­Ó§O¤H¼Æ¡A¦A­pºâ·í¤é¤W¯Z¤H¼Æ¡A¦ý¨C¤é¤½®t¤Î½Ð°²¤H¼Æ¤£¤@¡A«D©T©w¦C¼Æ³y¦¨­Ó§O¤H¼Æ¤Î·í¤é¤W¯Z¤H¼Æ¬OÅܰʤ£¤@¡A¤£ª¾¥Î¦ó¨ç¼Æ³]©w¤~¯à¦]À³¡A½Ð¦U¦ì¥ý¶i«ü±Ð¡AÁÂÁ¡I

·í¤é¤½®t¤H¼Æ(±q²Ä¤T¦C)=IF(A3="","",SUMPRODUCT((LOOKUP(ROW(A3:A$16),ROW(A3:A$16)/(A3:A$16<>""),A3:A$16)=A3)*(F3:F$16="¤½®t")))
µ{¦¡¤½®t§ï½Ð°² ,¬O·í¤é½Ð°²¤H¼Æ(±q²Ä¤T¦C)

TOP

¦^´_ 2# hcm19522


    H ¤j¦n¡A«ü¾Éªº¤½¦¡¤w¥i¥Hº¡¨¬­pºâ¥X¨C¤éªº¤½®t¤Î½Ð°²¦U§O¤§¤H¼Æ¡A¤£¹L¦¹ªí¥D­nªº¤H¼ÆÅã¥Ü¬O B Ä檺¡u«e¤é¤W¯Z¤H¼Æ¡v¤ÎC Ä檺¡u·í¤é¤W¯Z¤H¼Æ¡v¡A³£¬O¥ÎÁ`¼ÆÅã¥Ü¡A¦p¢°¤éªº¡u«e¤é¤W¯Z¤H¼Æ¡v¬O 73 ¤H¡A¥[¤½®t5¤H´î½Ð°²3¤H¡A¡u·í¤é¤W¯Z¤H¼Æ¡v¬O75 ¤H¡A¨Ì¦¹Ãþ±À¤U¥h¡A¦Ó 1 ¤é©M 2 ¤é¤§¶¡§¡¤£Åã¥Ü¤H¼Æ¡A¤£ª¾¦p¦ó³]¨ç¼Æ¡A¬O§_¥i¥Î»²§UÄæ¡A¦A³Â·Ð«ü¾É¡AÁÂÁ¡I

TOP

C3=IF(A3="","",B3+SUMPRODUCT((LOOKUP(ROW(A3:A$16),ROW(A3:A$16)/(A3:A$16<>""),A3:A$16)=A3)*(F3:F$16="¤½®t"))-SUMPRODUCT((LOOKUP(ROW(A3:A$16),ROW(A3:A$16)/(A3:A$16<>""),A3:A$16)=A3)*(F3:F$16="½Ð°²")))
B4=IF(A4="","",LOOKUP(1,0/(C$3:C3<>""),C$3:C3))
¬O¦p¦¹?

TOP

¦^´_ 4# hcm19522


    H ¤j¦n¡A¤Ó±·¤F¡A¸g´ú¸Õ«á§¹¥þ²Å¦X»Ý¨D¡A¦p¦¹¦b¤u§@¤W¥i¥H¬Ù¤U«Ü¦h¯È±i¡AÁÂÁÂ10000*N¡I

TOP

¦PE¤j¡G
¢Ñ¢²¤½¦¡
=IF(A3="","",B3+SUMPRODUCT((LOOKUP(ROW(A3:A$16),ROW(A3:A$16)/(A3:A$16<>""),A3:A$16)=A3)*(F3:F$16={"¤½®t","½Ð°²"})*{1,-1}))
©Î:
=IF(A3="","",B3+SUMPRODUCT((SUBTOTAL(4,OFFSET(A$2,1,,ROW(A3:A$16)-2))=A3)*(F3:F$16={"¤½®t","½Ð°²"})*{1,-1}))

TOP

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


    ­ã¤j¦n¡A±z«ü¾Éªº¤½¦¡¸g¹L¹ê»Ú¨Ï¥Î«á§¹¥þ²Å¦X»Ý¨D¡A¤]¾Ç¨ì¤F¤½¦¡§Þ¥©¡AÁÂÁ¡A·P®¦¡I

TOP

        ÀR«ä¦Û¦b : ·R¤£¬O­n¨D¹ï¤è¡A¦Ó¬O­n¥Ñ¦Û¨­ªº¥I¥X¡C
ªð¦^¦Cªí ¤W¤@¥DÃD