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

[µo°Ý] countif

[µo°Ý] countif

½Ð±Ð¦U¦ì¥ý¶i¡A§Ú¦³¤@ÄæI¡A¤é´ÁªºÅã¥Üªº®æ¦¡¬O"2014/12/2"¡A
¦³¤@­ÓªÅ®æ»Ý­n¥h­pºâIÄ椤¦³¦h¤Ö®æ¬O­þ¨Ç¤ë¥÷¡A
©Ò¥H§Ú¥Îcountif¸òmonth¤G­Ó¨ç¼Æ¡C

Ä´¦p§Ú­n­pºâ7¤ëªºµ§¼Æ¡A¤½¦¡´N¿é¤J=COUNTIF(I3:I37,"month()=7")¡A
¦ý¬O¥X¨Óªº­È«o¬O0¡A¬°¤°»ò?
¬O§Úªº¤½¦¡¦³¿ù¶Ü?ÁÙ¬OÀx¦s®æ®æ¦¡¦³¿ù?

¦^´_ 1# eghost


    §ï¦¨³o¼Ë´N¦æ¤F
    =SUMPRODUCT((MONTH(i3:i37)=7)*1)

TOP

¦^´_ 2# owen06


¥i¥H½Ð±Ð¤@¤U§Ú­ì¥»¥Îªº¤½¦¡¦³¤°»ò¿ù»~¶Ü?
§Ú·Q¥Îcountif³B²z¡A¦Ó¥B§Ú¦³¤G­Ó±ø¥ó¡A©Ò¥H¨ä¹ê§Ú¬O¥Îcountifs¦b°µ­pºâ¡C

TOP

¦^´_ 3# eghost


    ¦ó¥²°õµÛ©ócountif¡Asumproduct¥i¥H¹B¥Îªº¼h­±§ó¼s¡A¬Ý§A¬O­n¤T¥|¤­¤»¤C±ø¥ó³£ÀH«K§A¡AÁÙ¥i¥HÀ°§A¥[Á`²Å¦X¥H¤W±ø¥óªº¼Æ¶q¡A
    ¥u­n¤@­ÓÀx¦s®æ¡A´N¯à­pºâ¥X²Å¦X¦h±ø¥óªº¼Æ¶q©ÎÁ`¶q¡A¦nsumproduct¡A¤£¥Î¶Ü~

    ¦Ü©ócountif¬°¤°¤£¯à¨º¼Ëºâ¤é´Áªº°ÝÃD¡A¥i¯à´N­n½Ðª©¤Wªº¶W¯Å°ª¤â¨Ó¸Ñ´b¤F¡C

TOP

¦^´_ 4# owen06


«¢«¢«¢~±Ð°Vªº¬O¡A¦]¬°§Ú¥u·|¥Îcountif¦Ó¤w~~~
¬Ý¨Ó­n¦A¨Ó¬ã¨s¤@¤Usumproduct«ç»ò¥Î¤F¡AÁÂÁÂ~~~

TOP

¥»©«³Ì«á¥Ñ owen06 ©ó 2014-12-2 20:22 ½s¿è

¦^´_ 5# eghost


    ¦pªG¤@©w­n¥Îcountif¥hºâªº¸Üoffice2003­n³o¼Ë=COUNTIF(I:I,">"&DATE(2014,6,30))-COUNTIF(I:I,">"&DATE(2014,7,31))
    §A¦pªG¬Ooffice2007¥i¥H¥Î=COUNTIFS(A:A,">2014/6/30",A:A,"<2014/7/31")
    ¦ý¬O§Úı±o¦³ÂI¤Ó³Â·Ð¡C
    §ÚÁÙ¬O«Øij§A¥h¥Îsumproduct¨Ó¼g¡A¦]¬°¤@¨Ó¤½¦¡¤º®e¸û²©ö¡A¥B¥i¥H¦h±ø­pºâ¡A¥i¥H¬Ù¤U«Ü¦h³Â·Ð¡A
    ¤j¬ù»¡©ú¤@¤Usumproduct±ø¥óªº¥Îªk
    =sumproduct((«ü©wªº½d³ò=«ü©wªº±ø¥ó)*(«ü©wªº½d³ò=«ü©wªº±ø¥ó)*1)===>­pºâ¥X²Å¦X¤W­z±ø¥óªº¶µ¥Ø¼Æ¡A¥i¥H«ü©w«Ü¦h±ø¥ó³£¨S°ÝÃD
    =sumproduct((«ü©wªº½d³ò=«ü©wªº±ø¥ó)*(«ü©wªº½d³ò=«ü©wªº±ø¥ó),­n¥[Á`½d³ò)===>­pºâ­n¥[Á`ªº½d³ò¤º¡A²Å¦X¤W­z±ø¥ó¶µ¥ØªºÁ`¦X

   §A¥i¥H¸ÕµÛ±N§A­nªºªí¥Îsumproduct°µ°µ¬Ý¡A¦pªG¦³°ÝÃDªº¸Ü¡A¦A´£¥X¨Ó¤@°_°Q½×¡C

TOP

©êºp¸É¥R¤@¤U¡A2003ªº¥Î  =COUNTIF(A:A,">2014/6/30")-COUNTIF(A:A,">2014/7/31")   ´N¦æ¤F

TOP

¦^´_ 6# owen06


¶â¡A¦b³oºØ±¡ªp¤U¥Îcountif¯uªº¤ñ¸û³Â·Ð¡A
Å¥§Aªº¡A´N¥Îsumproduct¤F¡C

«D±`·PÁÂ~~~

TOP

¦^´_ 8# eghost


2¼Óªº¤½¦¡¨Ï¥Î­nª`·N­­¨î¡A¦¹¤½¦¡¨C¦~¥÷ªº7¤ë³£·|³Q­pºâ¡A­Y¥u¦³2004¦~ªº¸ê®Æ¨Ï¥Î¤~¤£·|¦³°ÝÃD
=SUMPRODUCT((MONTH(i3:i37)=7)*1)

­Y¦³¦h¦~¥÷¸ê®Æ­n¥[¦~¥÷­­¨î
=SUMPRODUCT((MONTH(i3:i37)=7)*(YEAR(i3:i37)=2014))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 9# ML089


¾¾¡A¤F¸Ñ¡AÁÂÁª©¤jªº´£¿ô!

TOP

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