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

[µo°Ý] ¦p¦óÅã¥Ü¬Y­Ó¤ë¥÷ªºª÷¿ú¥æ©öÁ`©M¡S

[µo°Ý] ¦p¦óÅã¥Ü¬Y­Ó¤ë¥÷ªºª÷¿ú¥æ©öÁ`©M¡S

§Ú¦bB4¡RB270 ªº¤º®e³£¬O¤é´Á¡M¬ÛÃö¹ïÀ³ªº E4¡RE270 ªº¤º®e«h¬Oª÷¿úÃB¡C
§Ú·Q¦bF4Åã¥Ü¬Y­Ó¤ë(¤ñ¦p01/2010)ªºª÷¿ú¥æ©öªºÁ`©M¡M½Ð°Ý¸Ó«ç»ò¿é¤J¡S
§Ú¸Õ¤F sumif¡M¥i¬O¨S¤ÏÀ³¡M T_T

¸Õ¸Õ
=SUMPRODUCT((YEAR(B4:B270)=YEAR(F4))*(MONTH(B4:B270)=MONTH(F4))*E4:E270)
¾÷¾¹¤H¤T¤j©w«ß-«OÅ@¤HÃþ¡BªA±q©R¥O¡B«OÅ@¦Û¤v
§Úªº¤T¤j©w«ß-¥Í©R¡B¥Í¦s¡B¥Í¬¡

TOP

1¼Ó¤é´Á®æ¦¡«Ü©_©Ç

«Øij¤@©w­n¨Ì·Óexcel¨t²Î¤é´Á®æ¦¡
µL½×¬d¸ß©Î¬O­pºâ³£¤è«K
¸Õ¤@¸Õ³o¤½¦¡
=SUMPRODUCT((TEXT(B$4:B$270,"YYYYM")=TEXT(F$4,"YYYYM"))*E$4:E$270)
300 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦pªG½T»{BÄæ¤ÎF4³£¬O¤é´Á®æ¦¡¤]½T»{¬O¤é´Á
À³¸Ó¥ÎSUMIF()¥i¦æ
G4=SUMIF($B$4:$B$270,$F$4,$E$4)
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

=SUMIFS(E4:E240,B4:B240,">=40179",B4:B240,"<=40209")

TOP

¤£¿ù,¤j®a³£¥Î SUMIF
¤p§Ì¤]¨Ó¤@©Û SUMIF

=SUM(SUMIF(B$4:B$270,{">=40179",">=40209"},E$4)*{1,-1})
300 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 6# zz5151353


    ZZª©¥D
±zªº¤½¦¡¦n¯S§O
¥i¥H½Ð±Ð¤@¤U,½Ð±z»¡©ú¤½¦¡¶Ü,ÁÂÁ±z..

TOP

¤½¦¡¤£¤Ó®e©öÁ¿¸Ñ
½Ð¾¨¶q±Ä¥Î Áä½L F9 ®Ø¿ï¤@¨B¨B¥h¤F¸Ñ

=SUM(SUMIF(B$4:B$270,{">=40179",">=40209"},E$4)*{1,-1})

¤½¦¡¤¤ªº

40179 ªí¥Ü 2010/1/1
40209 ªí¥Ü 2010/1/31

¨ä¤¤ {1,-1}

¥Ñ©ó >=40179 ªí¥Ü¥H¤W©Ò¦³ªº¤é´Á 1
¥Ñ©ó <=40209 ªí¥Ü¥H¤U©Ò¦³ªº¤é´Á -1

¤]´N¬O ¤j©ó©Î¬Oµ¥©ó 2010/1/1 ¤p©ó©Î¬Oµ¥©ó 2010/1/31 ½d³òÁ`©M
¥²¶·®M¤W sum °µ ¥[Á`

¤£ª¾¹D³o¼Ë¸Ñµª¬O§_©ú¥Õ
300 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦pªG¬O·í¤ë«Øij
=SUMIF(B$4:B$270,">="&DATE(2010,1,1),$E$4)-
SUMIF(B$4:B$270,">"&DATE(2010,1,31),$E$4)
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

¦^´_ 8# zz5151353

ÁÂÁÂZZª©¥D
¨º¦pªG¬O­nºâ2010/2¤ë´N¦A+30¤Ñ?¬O³oºØ·N«ä..¥H¦¹Ãþ±À¶Ü?(­è­è¸Õ¹Lµª®×¬O¹ïªº..¦ý¬O¤£ª¾¹DÆ[©À¬O¥¿½Tªº¶Ü?)
=SUM(SUMIF(B$4:B$261,{">=40209",">=40239"},E$4)*{1,-1})
©Ò¥H¬°ÁקK¨C¤ëºâªº»~®t..¥ÎGONGª©¥Dªº¤½¦¡¬O¤£¬O¤ñ¸û¤è«K?...

ÁÂÁ±zªº«ü¾É
¤]ÁÂÁÂGONGª©¥D.

TOP

        ÀR«ä¦Û¦b : ¯à·F¤£·F¡A¤£¦p­W·F¹ê·F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD