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

[µo°Ý] °}¦C¤½¦¡¤¤ªº¿z¿ï±ø¥ó

[µo°Ý] °}¦C¤½¦¡¤¤ªº¿z¿ï±ø¥ó

¥»©«³Ì«á¥Ñ solely ©ó 2011-6-5 22:19 ½s¿è

·Ð½Ð°ª¤âÀ°¦£
¦pªþ¥ó
1.¬O§_¦³¨ç¼Æ¥i¥H±NC4:D33ªº¦W³æ¥H¤£­«½Æªº¤è¦¡¶K¦ÜG6:G20?
2.°}¦C¤½¦¡¬O§_¥i¥H"¦rªºÃC¦â"¨Ó°µ¿z¿ï±ø¥ó?
  ¦pªþ¥ó¤¤¨Ï¥Î{=SUM((($B$3:$B$33=1)+($B$3:$B$33=7))*($C$3:$D$33=G6)*$I$2)}
  ¥u¯à²Î­p¬P´Á¤»¡B¤éªº¸ê®Æ¡AµLªk²Î­p¨ì9¤ë22¤é(¬P´Á¤T)ªº¸ê®Æ¡C
3.·íA1¿é¤J¦~¤ë¡A¬O§_¦³¤èªk¥i¥HÅýA3~A33¦Û°ÊÅã¥Ü·í¤ëªº¤é´Á¦Ó¤£¦Ü©ó¦³¸ó¤ëªº¤é´Á¥X²{?
[attach]2691[/attach]

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-9-5 22:56 ½s¿è

¦^´_ 1# solely
¨Ï¥Î°²´Áªí
A:BÄ檺®ÄªG¨Ï¥Î®æ¦¡±ø¥ó
A3=$A$1+ROW(A1)-1
B3=A3
H6=IF(G6="","",SUMPRODUCT((WEEKDAY($B$3:$B$33,2)<=5)*ISERROR(MATCH($B$3:$B$33,$L:$L,0))*(($C$3:$C$33=$G6)+($D$3:$D$33=$G6))*$H$2))
I6=IF(G6="","",SUMPRODUCT(((WEEKDAY($B$3:$B$33,2)>5)+ISNUMBER(MATCH($B$3:$B$33,$L:$L,0)))*(($C$3:$C$33=$G6)+($D$3:$D$33=$G6))*$I$2))
H24=IF(G24="","",SUMPRODUCT((WEEKDAY($B$3:$B$33,2)<=5)*ISERROR(MATCH($B$3:$B$33,$L:$L,0))*($E$3:$E$33=$G24)*$H$3))
I24=IF(G24="","",SUMPRODUCT(((WEEKDAY($B$3:$B$33,2)>5)+ISNUMBER(MATCH($B$3:$B$33,$L:$L,0)))*($E$3:$E$33=$G24)*$I$3))

¦pªþ¥ó
¯Zªí.rar (100.77 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh
·PÁª©¥Dªº¦^´_XD
§ÚÁÙ¤£¯à¤U¸üªþ¥ó¬Ý©O...§V¤Oñ¨ì¤¤~

TOP

Hsieh ª©¥Dª¾¹D§A¤£¯à¤U¸ü©Ò¥H§â¤½¦¡³£¦C¥X¨Ó¤F¥u­n°Ñ¦Ò¹Ï¤ù´N¥i¤F¸Ñ,¤£¤@©w­n¤U¸ü.

TOP

­YµL¤U¸üÅv­­¡A¦P¼Ë¤]µLªk¬Ý¨ì¶K¹Ïªº¡C

TOP

¦^´_ 5# oobird

¦Ñ­ô:§Ú¨è·Nµn¥X¨Ó¬Ý¡A¹Ï¤ù¬Ýªº¨ì­ò
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_  oobird

¦Ñ­ô:§Ú¨è·Nµn¥X¨Ó¬Ý¡A¹Ï¤ù¬Ýªº¨ì­ò
Hsieh µoªí©ó 2010-9-6 14:28



    ¬O®@¡H·s½×¾Âªº¤S¤@­ÓÀuÂI¡I

TOP

¦^´_ 6# Hsieh
ÁÂÁª©¥D...¹Ï¤ù§Ú¬Ý¨ì¤F..¶W·P°Êªº~~
²Ä2¡B3ªº°ÝÃD¸Ñ¨M¤F~
(¦³ÂI±Ë¤£±o§Úªº°}¦C¤½¦¡XDD....¦]¬°..§Ú¤~­è¾Ç·|«ç»ò¥Î°}¦C)

¦³Ãö²Ä1­Ó°ÝÃD
§Ú­Y¥Î¶i¶¥¿z¿ï¥u¯àÂ^¨ú¨ìCÄæ¦ìªº¦W³æ¡A¦ü¥GµLªk¸óÄæ¦ì¿z¿ï¡C
¥t¥~ÁÙ¦³­Ó¯ÊÂI´N¬O..·íC¡BDÄæ¦ìªº¸ê®ÆÅܧó¡AGÄæ¦ìªº¦W³æ¤£·|¥ß§Y§ó·s¡A±o­«·s¿z¿ï¡C
·Q»¡¦³¨S¦³§Oªº¤è¦¡¥i¥HÅýGÄæ¦ì¦C¥XC¡BDÄæ¦ìªº¦W³æ??

TOP

¦^´_ 8# solely
³o¼Ëªºªí®æ­n¨Ï¥Î¤½¦¡©Î¶i¶¥¿z¿ï³£¬O¦³§xÃøªº
¨ú±o¤£­«½Æ²M³æ¸Õ¸Õ¨Ï¥Î¦Û©w¸q¨ç¼Æ(VBA»²§U)
  1. Function Only(Rng As Range, k%)
  2. Set d = CreateObject("Scripting.Dictionary")
  3. Application.Volatile
  4. For Each a In Rng
  5.   If a <> "" Then d(a & "") = ""
  6. Next
  7. ar = d.keys
  8. If k - 1 > UBound(ar) Then Only = "" Else Only = ar(k - 1)
  9. End Function
½Æ»s¥N½X
±N¥N½X½Æ»s¨ìVBEµøµ¡¤@¯ë¼Ò²Õ¤º
G6=only($C$3:$D$33,ROW(A1))
G24=only($E$3:$E$32,ROW(A1))
¯Zªí.rar (105.67 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 9# Hsieh
·PÁª©¥D..¶W¦n¥Îªºonly¨ç¼ÆXD
(³o­Ó¨ç¼Æªº¦W¦r..§Ú³ßÅw~)

¬ðµMµo²{.....¤u§@ªí...°£¤Fªí®æ¬O§Úµeªº-.-""...³£³Qª©¥D§ï¤F@@
¬Ý¨Ó§ÚÁÙ¦³«Ü¦h­n¾Ç²ßªº~

TOP

        ÀR«ä¦Û¦b : ¤Ó¶§¥ú¤j¡B¤÷¥À®¦¤j¡B§g¤l¶q¤j¡A¤p¤H®ð¤j¡C
ªð¦^¦Cªí ¤W¤@¥DÃD