Hsieh

¦^´_ 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))

¤U¸ü (70.87 KB)

2010-9-5 22:43

¦pªþ¥ó
¯Zªí.rar (100.77 KB)

¤U¸ü¦¸¼Æ: 15

2010-9-5 22:55

Hsieh

¦^´_ 5# oobird

¦Ñ­ô:§Ú¨è·Nµn¥X¨Ó¬Ý¡A¹Ï¤ù¬Ýªº¨ì­ò

Hsieh

¦^´_ 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)

¤U¸ü¦¸¼Æ: 3

2010-9-6 22:53