[µo°Ý] ²Î­pµ§¼Æ¤Î­pºâ¼Æ¶q

b9208

«e½ú±z¦n
¦pªþÀɤº»¡©ú¡u²Î­pµ§¼Æ¡v¤Î¡u­pºâ¼Æ¶q¡v
¡u²Î­pµ§¼Æ¡v¡G±ø¥óÄæ¦ì¬Û¦PªÌ¡A¥uºâ¤@µ§¸ê®Æ¡C
¡u­pºâ¼Æ¶q¡v¡G±ø¥óÄæ¦ì¬Û¦PªÌ¡A¥u­pºâ¼Æ¶q­È¡ª³Ì¤j­È¡ª¡C
«D±`·PÁ«ü¾É

T1.rar (5.31 KB)

¤U¸ü¦¸¼Æ: 35

2013-4-18 10:10

b9208

¡u²Î­pµ§¼Æ¡v¡G±ø¥óÄæ¦ì¬Û¦PªÌ¡A¥uºâ¤@µ§¸ê®Æ¡C
¡u­pºâ¼Æ¶q¡v¡G±ø¥óÄæ¦ì¬Û¦PªÌ¡A¥u¥[Á`¼Æ¶q­È¡ª³Ì¤j­È¡ª¡C
Àµ½Ð¥ý¶i­Ì«ü¾É
·P¿E¤£ºÉ

ML089

¥ýµ¹ O8 ¤½¦¡
=COUNT(MATCH(ROW($8:$24)-7,MATCH($N8&$C$8:$C$24&O$7,TEXT($B$8:$B$24,"ddd")&$C$8:$C$24&$D$8:$D$24,),))
°}¦C¤½¦¡(¥ý«öCTRL¡BSHIFT¤£©ñ¡A¦A«öENTER¤TÁä»ô«ö¿é¤J)

O19¤½¦¡¤ñ¸û½ÆÂø­n·Q¤@¤U¡Aµ¥±ß¤W¦A¬ã¨s

ML089

¤U¸ü (24.98 KB)

2013-4-24 11:16

µo©««á¡A¤½¦¡¸Ì­±«ç·|¦³³o¨Çªí±¡²Å¸¹¡A­n¦p¦ó²M°£?

Hsieh

¦^´_ 4# ML089

µo¤å®É¡A¤Ä¿ï¤U¤è¸T¥Îªí±¡¿ï¶µ

Hsieh

¦^´_ 1# b9208

1. Sub ex()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. Set d1 = CreateObject("Scripting.Dictionary")
   
4. For Each a In Range([B8], [B8].End(xlDown))
   
5. m = a.Text & "," & a.Offset(, 2) & "," & a.Offset(, 4)
   
6. n = a.Text & "," & a.Offset(, 1) & "," & a.Offset(, 2)
   
7.    If d(m) <= a.Offset(, 7) Then _
   
8.    d(m) = a.Offset(, 7)
   
9.    d1(n) = ""
   
10. Next
    
11. For Each ky In d.keys
    
12.   ar = Split(ky, ",")
    
13.   d(ar(0) & ar(1)) = d(ar(0) & ar(1)) + d(ky)
    
14. Next
    
15. For Each ky In d1.keys
    
16.   ar = Split(ky, ",")
    
17.   d1(ar(0) & ar(2)) = d1(ar(0) & ar(2)) + 1
    
18. Next
    
19. For Each a In [N8:N14]
    
20.    For Each c In [O7:P7]
    
21.      Cells(a.Row, c.Column) = IIf(d1(a & c) = "", 0, d1(a & c))
    
22.    Next
    
23. Next
    
24. For Each a In [N19:N25]
    
25.    For Each c In [O7:P7]
    
26.      Cells(a.Row, c.Column) = IIf(d(a & c) = "", 0, d(a & c))
    
27.    Next
    
28. Next
    
29. End Sub

½Æ»s¥N½X

ML089

O19¤½¦¡
=SUM(IF(FREQUENCY(IF($N19&O$18=TEXT($B$8:$B$24,"ddd")&$D$8:$D$24,MATCH(TEXT($B$8:$B$24,"ddd")&$D$8:$D$24&$F$8:$F$24,TEXT($B$8:$B$24,"ddd")&$D$8:$D$24&$F$8:$F$24,),""),ROW($8:$24)-7), SUBTOTAL(4,OFFSET($I$7,ROW($8:$25)-7,,FREQUENCY(IF($N19&O$18=TEXT($B$8:$B$24,"ddd")&$D$8:$D$24,MATCH(TEXT($B$8:$B$24,"ddd")&$D$8:$D$24&$F$8:$F$24,TEXT($B$8:$B$24,"ddd")&$D$8:$D$24&$F$8:$F$24,),""),ROW($8:$24)-7))),0))
¤TÁä¿é¤J

¹ê¥ÎÁÙ¬O¥H6¼Ó¶Wª©ªºVBA¦n¥Î

¤½¦¡ÁÙ¬O¦³¨Ç­­¨î¡A¦p¦PÃþ»Ý±Æ§Ç¦b¤@°_¤~¯à²Î­p
¸ê®Æ¥H¤é´Á±Æ§Ç©¹¤U¡A"¬P´Á" ´N·|Åܦ¨´`Àô¡A¬P´Á+²Õ§O+¤H­û ¥i¯à´N·|³Q¤À¶}¡A
¦]¦¹¨Ï¥Î¤½¦¡®É»Ý±N¦PÃþ "¬P´Á" ±Æ§Ç¤~¯à¥¿½T¡C

ML089

¦^´_ 6# Hsieh

¶Wª©:
¦p¦³ªÅ¥i§_´£¨ÑO8ªºVBA¡A³o¼Ë¦¹ÃDVBA¤Î¤½¦¡´NÂù»ô¥þ¡C

ML089

¦^´_ 5# Hsieh

¦b¥L¤H·N¨£¤U¤è«ö¦^ÂЮɡA¤U¤è¦³ "¸T¥Îªí±¡¿ï¶µ" ¦Ó¥B¤º©w¬°¤Ä¿ï

­Y¬O¦Û¦æ¦^µª  «ö¤U¤èªº µoªí¦^ÂСA¤U¤è¨S¦³ "¸T¥Îªí±¡¿ï¶µ"

³o¨Çªí±¡¹j¤Ñ¤S·|¤£¨£

º¡§xÂZªº

Hsieh

¦^´_ 9# ML089

¹j¤Ñªí±¡²Å¸¹®ø¥¢¡A¬O¦]¬°§ÚÀ°§A½s¿è¹L¤F