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

[µo°Ý] ½Ð°Ý¦p¦ó­pºâ³æ¤ë¤¤¤§¥X²{¦¸¼Æ

[µo°Ý] ½Ð°Ý¦p¦ó­pºâ³æ¤ë¤¤¤§¥X²{¦¸¼Æ

¥»©«³Ì«á¥Ñ ´Ñ»y³¾»ï ©ó 2011-10-15 06:59 ½s¿è

¦pªþ¥ó©Ò¥Ü!½Ð°Ý¦U¦ì¤j¤j:¦³¤°»ò¨ç¼Æ¡A¥i¥H­pºâ³æ¤ë¤¤¥X²{¦¸¼Æ??
¥X²{¦¸¼Æ.rar (7.78 KB)


¶i¶¥²Î­p
¥X²{¦¸¼Æ2.rar (8.43 KB)

¦^´_ 1# ´Ñ»y³¾»ï

G9=SUMPRODUCT((TEXT(OFFSET($A$1,1,,COUNTA($A:$A),),"yyyym")=$G$6&$I$6)*(OFFSET($A$1,1,1,COUNTA($A:$A),)=$F9))
G14=SUMPRODUCT((TEXT(OFFSET($A$1,1,,COUNTA($A:$A),),"yyyym")=$G$6&$I$6)*(OFFSET($A$1,1,2,COUNTA($A:$A),)=$F14))
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

  1. G9 =SUMPRODUCT(--($A$2:$A$20>=DATE($G$6, $I$6,1)), --($A$2:$A$20<DATE($G$6, $I$6+1,1)), --($B$2:$B$20=F9))

  2. G14 =SUMPRODUCT(--($A$2:$A$20>=DATE($G$6, $I$6,1)), --($A$2:$A$20<DATE($G$6, $I$6+1,1)), --($C$2:$C$20=F14))
½Æ»s¥N½X
diabo

TOP

¥»©«³Ì«á¥Ñ ´Ñ»y³¾»ï ©ó 2011-10-13 21:55 ½s¿è

¦^´_ 2# Hsieh
¦^´_ 3# diabo
·PÁÂ2¦ì¤j¤jªº¦^ÂÐ~°w¹ï§Ú©Ò»Ý­nªº§Ö³t¸Ñ¨M¤F§ÚªººÃ´b!
h¤jªº~¨ç¼Æ²³æ©úÁA!
¦Ü©ód¤j¨ç¼Æ¤ºªºªF¦è«Ü¤Ö¬Ý¹L~·Q½Ð±Ð¤@¤U~¨ç¼Æ¤¤ªº"--"¬O¤°»ò·N«ä??

TOP

¦^´_  Hsieh
¦^´_  diabo
¦Ü©ód¤j¨ç¼Æ¤ºªºªF¦è«Ü¤Ö¬Ý¹L~·Q½Ð±Ð¤@¤U~¨ç¼Æ¤¤ªº"--"¬O¤°»ò·N«ä??
¶¶«K°Ý¤@¤U:
¦pªG­n²Î­p¨º­Ó¤ë¥÷Á`¦@¦³´Xµ§¼Æ¾Ú(·N«ä´N¬O¤é´Á¤¤¥u­n²Å¦X"¦~/¤ë")¡A­n¦p¦ó³]¨ä¨ç¼Æ??
´Ñ»y³¾»ï µoªí©ó 2011-10-13 18:16


-- ¬O§âÅÞ¿è­È(True/False)Âର1/0
²Ä1­Ó¤Þ¼Æ¬O§PÂ_¤é´Á¬O§_¤j©óµ¥©ó«ü©w¦~¤ëªº1¤é¡A¬O¬°True¡A¤£¬O¬°False¡A¦A§Q¥Î--Âର1»P0
²Ä2­Ó¤Þ¼Æ¬O§PÂ_¤é´Á¬O§_¤j©ó«ü©w¦~¤ëªº¤U¤@­Ó¤ëªº1¤é¡A¬O¬°True¡A¤£¬O¬°False¡A¦A§Q¥Î--Âର1»P0
²Ä3­Ó¤Þ¼Æ¬O§PÂ_«ü©wÄæ¬O§_¬°«ü©wª««~¡A¬O¬°True¡A¤£¬O¬°False¡A¦A§Q¥Î--Âର1»P0

³Ì«áSUMPRODUCT´N¥i¥Hºâ¥X²Å¦X¦h±ø¥óªº¼Æ¶q....
diabo

TOP

¦^´_ 5# diabo


    ­ì¨Ó¦p¦¹!©Ò¥H§Ú§â³Ì«á¨º­Ó¤Þ¼Æ§R±¼¡A­è¦nµ¥©ó¤ë¼Æ~¨ü±Ð¤F!

TOP

[ª©¥DºÞ²z¯d¨¥]
  • Hsieh(2011-10-14 22:38): ¤W¶ÇÀɮ׬ݬÝ

¦^´_ 2# Hsieh


    ½Ð°Ý¤@¤Uh¤j~©Î¨ä¥L°ª¤â~¦pªG§Ú·Q±N¦¹¨ç¼Æ~§ï¦¨²Î­p¦¹¤ë¥÷¤º"¥]§t"¬Y­È¡A­n¦p¦ó­×§ï¨ä¨ç¼Æ~ÁÙ¬O¦³¨ä¥LÁټƥi¥Î~
§Ú¥Ø«e§@ªk¬O±N«á­±§ï¦¨"*"&F9&"*"¤£¹L¦n¹³µLªk¥Î!

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-10-16 15:23 ½s¿è

¦^´_ 7# ´Ñ»y³¾»ï


    G9=SUMPRODUCT((TEXT(OFFSET($A$1,1,,COUNTA($A:$A)-1,),"yyyym")=$G$6&$I$6)*ISNUMBER(FIND($F9,OFFSET($A$1,1,1,COUNTA($A:$A)-1,))))
¦pªGÁÙ¤£¬O§A­nªºµª®×½Ð¤W¶ÇÀÉ®×»¡©ú
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 8# Hsieh


    Àɮצb¦¹¡A³Â·Ð±z¤F!
¥X²{¦¸¼Æ2.rar (8.43 KB)

TOP

¨S·Q¨ìÁÙ¦³ Part2

G9 ¤½¦¡¦p¤U¡A±µµÛ½Æ»sG9¡A ¶K¤½¦¡¨ì¨ä¥LG10-G12, G14-G15
  1. =SUMPRODUCT(--($A$2:$A$20>=DATE($G$6,$I$6,1)),--($A$2:$A$20<DATE($G$6,$I$6+1,1)), --(ISNUMBER(FIND(F9,$B$2:$B$20))))
½Æ»s¥N½X
§ï²Ä¤T­Ó¤Þ¼Æ...
diabo

TOP

        ÀR«ä¦Û¦b : ¦³¤ß´N¦³ºÖ¡A¦³Ä@´N¦³¤O¡A¦Û³yºÖ¥Ð¡A¦Û±oºÖ½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD