maiko

¦^´_  maiko


    §A§â¥N½X©ñ¿ù¤u§@ªí¼Ò²Õ
­n©ñ¦bSheet2¤u§@ªí¼Ò²Õ¤º
µM«áÅÜ°ÊB¡BCÄæ¸ê®Æ
¥u¦³D2¡B ...
Hsieh µoªí©ó 2012-9-20 19:35

   
§A¦n¡A§Ú§â¥N½X©ñ¦bSheet2¤u§@ªí¼Ò²Õ¤º¡AµM«áÅÜ°ÊB¡BCÄæ¡A¥i¬OÁÙ¬OµLªkÅçÃÒ¥XD¡BEÄ檺¸ê®Æ¡A½Ð¬d¤@¬dªþ¥ó¡C
¥Ñ©óµLªk¤U¸ü¤j¤j´£¨Ñªºªþ¥ó¡A½ÐÀ°¦£¬Ý¬Ý¡AÁÂÁ¡I

Book3_New.zip (21.64 KB)

¤U¸ü¦¸¼Æ: 15

2012-9-24 10:28

Hsieh

¦^´_ 8# maiko


    §A§â¥N½X©ñ¿ù¤u§@ªí¼Ò²Õ
­n©ñ¦bSheet2¤u§@ªí¼Ò²Õ¤º
µM«áÅÜ°ÊB¡BCÄæ¸ê®Æ
¥u¦³D2¡BE2¦³ÅçÃÒ¡A¬O¦]¬°¶i¶¥¿z¿ï·Ç«h½d³ò¥Ø«e¥u¦³A1:E2
¨ä¥L³¡¤À¨Ã¤£»Ý­nÅçÃÒ²M³æ¡A­Y§A¦³»Ý¨D¥u­n§â½d³ò§ï¤@¤U´N¦n

maiko

¦^´_  maiko
¶i¶¥¬d¸ß¡A¦b¤@¯ë¼Ò²Õ¨ú±oSheet2¤u§@ªíB¡BCÄ椣­«½Æ²M³æ°µ¬°ÅçÃÒ²M³æ
Sheet2¤u§@ªí¼Ò²ÕShee ...
Hsieh µoªí©ó 2012-9-19 09:27

    ¦b¸Õ¹L¥HSheet2¬d¸ß¤£­«½Æ²M³æ®É¡ASheet1ªºA2¡AB2¡AC2¡AD2¡AE2Àx¦s®æ¥u¦³D2¡AE2¥X²{²M³æ¡A¨ä¥¦¨S¥X²{¡A¦Ó¥BD2¡AE2¥X²{ªº²M³æ¨Ã¤£¬O¥HSheet2ªí¤¤¬d¸ßªº¤£­«½Æ²M³æ¡A½Ð¬d¬Ýªþ¥ó¬O§_¦³¨S¿ù?ÁÂÁ¡I

Book3_New.zip (20.75 KB)

¤U¸ü¦¸¼Æ: 8

2012-9-20 16:25

maiko

¦^´_  maiko
¶i¶¥¬d¸ß¡A¦b¤@¯ë¼Ò²Õ¨ú±oSheet2¤u§@ªíB¡BCÄ椣­«½Æ²M³æ°µ¬°ÅçÃÒ²M³æ
Sheet2¤u§@ªí¼Ò²ÕShee ...
Hsieh µoªí©ó 2012-9-19 09:27

    ÁÂÁ¤j¤j´£¨Ñ³o»ò¦nªºvba»y¨¥¡A¥u¬O¤À¼Æ¤Ó§C¡AµLªk¤U¸ü­ì¥ó´ú¸Õ¡A¥u¦n®³¤j¤jªºvba¥hºCºC°Ö¿i¡AÁÂÁ¡I

¦p¹J¦³¤°»ò°ÝÃD¡A®e«á¦A´£¡A¥i¥H¶Ü¡HÁÂÁ¡I

Hsieh

¦^´_ 5# maiko
¶i¶¥¬d¸ß¡A¦b¤@¯ë¼Ò²Õ

1. Sub Search_Data()
   
2. With Sheet1
   
3.   y = .[A2]: m = .[B2]: d = .[C2]
   
4.   .[A2] = IIf(.[A2] = "", "", "=YEAR(Sheet2!A2)=" & y)
   
5.   .[B2] = IIf(.[B2] = "", "", "=MONTH(Sheet2!A2)=" & m)
   
6.   .[C2] = IIf(.[C2] = "", "", "=DAY(Sheet2!A2)=" & d)
   
7. With Sheet2
   
8.    .Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, Sheet1.[A1:E2], Sheet1.[A6:D6], False
   
9. End With
   
10. If .[A7] = "" Then
    
11.   MsgBox "µL¸ê®Æ"
    
12. Else
    
13.   .Cells(.Rows.Count, 3).End(xlUp).Offset(2).Resize(, 2) = Array("Á`¦@:", "=SUM(R7C:R[-1]C)")
    
14. End If
    
15.   .[A2] = y
    
16.   .[B2] = m
    
17.   .[C2] = d
    
18. End With
    
19. End Sub

½Æ»s¥N½X

¨ú±oSheet2¤u§@ªíB¡BCÄ椣­«½Æ²M³æ°µ¬°ÅçÃÒ²M³æ
Sheet2¤u§@ªí¼Ò²Õ

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. Set d1 = CreateObject("Scripting.Dictionary")
   
4. If Target.Column = 2 Or Target.Column = 3 Then
   
5. For Each a In Range([B2], Cells(Rows.Count, 2).End(xlUp)).SpecialCells(xlCellTypeConstants)
   
6. d(a.Value) = ""
   
7. d1(a.Offset(, 1).Value) = ""
   
8. Next
   
9. With Sheet1
   
10.   With .Range("D2").Validation
    
11.   .Delete
    
12.   .Add xlValidateList, , , Join(d.keys, ",")
    
13.   End With
    
14.   With .Range("E2").Validation
    
15.   .Delete
    
16.   .Add xlValidateList, , , Join(d1.keys, ",")
    
17.   End With
    
18. End With
    
19. End If
    
20. End Sub

½Æ»s¥N½X

Sheet2¤u§@ªíB¡BCÄ榳ÅܰʮɡASheet1¤u§@ªí[D2]¡B[E2]ªºÅçÃÒ²M³æ´N·|§ïÅÜ

maiko

¦^´_  maiko
Hsieh µoªí©ó 2012-9-18 20:16

   

¤U¸ü (8.13 KB)

2012-9-19 08:16

­è­èµo²{¤@­Ó°ÝÃD¡A´N¬OÀ³¸Ó§â¤é´Áªº¦~¤ë¤é¤À¶}¨Ó¬d¸ß¡A³o¼Ë¤~¯à°÷·Ç½T¤@ÂI¡A¯à§_§ï¦¨¦pªG¥u¿é¤J¦~¡A¤ë¤é¤£¿é¤Jªº¸Ü´N¬d¸ß¾ã¦~ªº¸ê®Æ¡A¦~¤ë¦P®É¿é¤Jªº¸Ü´N¬d¸ß·í¦~·í¤ëªº¸ê®Æ¡A¦~¤ë¤é´N«ü©w³o¤Ñ¬d¸ßªº¸ê®Æ¡A¨ä¥¦«È¤á¡B«~¦W¨SÅÜ¡C¬Ý¬Ý¯à§_§ï¤@§ï¡HÁÂÁ¡I

¯à§_¥Îvba§@¤@­Ó±qSheet2¼Æ¾Ú®w¸Ì¬d¸ß¤£­«½Æªº«È¤á¦W¡B«~¦Wªº¤U©Ô¦Cªí¡A¥iÅý¨Ï¥ÎªÌ®e©öªº¿ï¾Ü«È¤á¦W¡B«~¦W¡A¤£¦Ü©ó¥´¿ù¦r¡CÁÂÁ¡I

³Ì«á¡A¯à§_¥[¤@±ø¡A¦pªG¬d¸ß¤£¨ì¸ê®Æ¡A´N´£¨Ñ¨Ï¥ÎªÌ¨S¦¹¸ê®Æ¡HÁÂÁ¡I

maiko

¥i±¤¤£°÷¿n¤À¡A¤£¯à¤U¸ü¡I

Hsieh

¦^´_ 2# maiko

1. Sub Search_Data()
   
2. d = [A2]
   
3. [A2] = IIf([A2] >= 1 And [A2] <= 12, "=MONTH(Sheet2!A2)=" & d, IIf(d = "", "", "=YEAR(Sheet2!A2)=" & d))
   
4. [A1] = IIf([A2] <> "", "", "¤é´Á")
   
5. With Sheet2
   
6. .Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, Sheet1.[A1:C2], Sheet1.[A6:D6], False
   
7. End With
   
8. Cells(Rows.Count, 3).End(xlUp).Offset(2).Resize(, 2) = Array("Á`¦@:", "=SUM(R7C:R[-1]C)")
   
9. [A2] = d
   
10. [A1] = "¤é´Á"
    
11. End Sub

½Æ»s¥N½X

maiko

¦^´_ 1# maiko


¸É¤Wªþ¥ó
Book3.zip (9.08 KB)

¤U¸ü¦¸¼Æ: 16

2012-9-18 17:08