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

¤ë¥÷¦X¨ÖÀx¦s®æ§@ª÷ÃB¥[Á`

¤ë¥÷¦X¨ÖÀx¦s®æ§@ª÷ÃB¥[Á`

¦p¦ó¦bCÄæ³]©w
¨ÌAÄæ¤ë¥÷¦X¨ÖÀx¦s®æ¥[Á`BÄæª÷ÃBÅã¥Ü©óCÄæ

¤ë¥÷¦X¨ÖÀx¦s®æ§@ª÷ÃB¥[Á`.zip (8.68 KB)

joyce

B3: =(C2-B2-B4)/2  
C2 ¤S­n¥[Á` B2+B3

¥Ù¬Þ~~

TOP

D2=IF(A2="","",SUMPRODUCT((LOOKUP(1,0/(A$2:A2<>""),A$2:A2)-LOOKUP(ROW(A$2:A$79),ROW(A$2:A$79)/(A$2:A$79<>""),A$2:A$79)=0)*B$2:B$79))

¤è¦¡¤G  A80³Ì«á­n«DªÅ¥Õ D2=IF(A2="","",SUMPRODUCT(OFFSET(B2,,,MATCH(,0/(A3:A79<>""),))))
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

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


   ¤£¦n·N«ä,ÀɮפºBÄæ¬O­È,¦]¬°¥Ñ§Oªº¦a¤èCOPY¹L¨Ó§Ñ¤F¶K¦¨­È

¤ë¥÷¦X¨ÖÀx¦s®æ§@ª÷ÃB¥[Á`.zip (8.48 KB)

joyce

TOP

¦^´_ 3# hcm19522


    ½Ð°Ý¥Î¤èªk¤G¤½¦¡,A80¬°¦ó¤£¯àªÅ¥Õ
ÁÂÁÂ
joyce

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2018-12-12 14:02 ½s¿è

¦^´_ 5# leiru

³Ì«á¦X¨Ö®æMATCH§ì¤£¨ì¸ê®Æ
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¢Ñ¢±¡þ°}¦C¤½¦¡¡G
=IF(A2="","",SUM(OFFSET(B2,,,-LOOKUP(1,-MATCH(1,N(A3:A299<>""),{1,0})))))
¡@
¡@
¡@

TOP

¦^´_ 4# leiru

=IF(A2="","",SUM(B2:B$9999)-SUM(C3:C$9999))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : Ãø¦æ¯à¦æ¡AÃø±Ë¯à±Ë¡AÃø¬°¯à¬°¡A¤~¯àª@µØ¦Û§Úªº¤H®æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD