[¨D§U] ½Ð°ª¤â¨D§U¡A¦p¦ó¥Îvba¬d§ä¸ê®Æ®w¤¤ªº¸ê®Æ°O¿ý¡H

maiko

¤U¸ü (11.99 KB)

2012-9-18 17:01

1. «È¤á¡Gh001¡A«~¦W¡Gb2¡A¾ú¦~¨ÓªºÁ`ª÷ÃB¡C

¤U¸ü (15.2 KB)

2012-9-18 17:01

2. «È¤á¡Gh001¡A¾ú¦~¨ÓªºÁ`ª÷ÃB¡C

¤U¸ü (10.4 KB)

2012-9-18 17:01

3. À³¸Ó¬O2012¦~8¤ë¥÷¡A«È¤á¡Gh001¡A«~¦W¡Gb2¡AÁ`ª÷ÃB¡C

¤U¸ü (11.18 KB)

2012-9-18 17:01

4. À³¸Ó¬O2012¦~8¤ë¥÷¡A«È¤á¡Gh001¡AÁ`ª÷ÃB¡C

¤U¸ü (13.41 KB)

2012-9-18 17:01

5. À³¸Ó¬O2012¦~8¤ë¥÷¡A«~¦W¡Ga1¡AÁ`ª÷ÃB¡C

¤U¸ü (18.73 KB)

2012-9-18 17:02

6. À³¸Ó¬O2012¦~8¤ë¥÷¡AÁ`ª÷ÃB¡C

¤U¸ü (22.9 KB)

2012-9-18 17:02

7. 2012¦~¥þ¦~Á`ª÷ÃB¡C

¤U¸ü (11.67 KB)

2012-9-18 17:02

8. 2012¦~¡A«È¤á¡Gh001¡A¥þ¦~Á`ª÷ÃB¡C

¤U¸ü (9.28 KB)

2012-9-18 17:02

9. 2012¦~¡A«È¤á¡Gh001¡A«~¦W¡Ga1¡A¥þ¦~Á`ª÷ÃB¡C

³o¼Ëªº¤@­Ó¥æ¤e¬d¸ß¸ê®Æ®w¡A½Ð°Ý¥Îvba¦p¦ó¹ê²{¡H·P¿E¦U¦ì½ç±Ð¡C

maiko

¦^´_  maiko


sheet1¼Ò²Õ¤@¯ë¼Ò²Õ
Hsieh µoªí©ó 2012-9-26 20:41

   
Hsieh¤j¡A½Ð§A¤@©w­nÀ°¦£­×§ï¤@¤U¡A³Â·Ð§A¤F¡C

½Ð§âA2¡BB2¡BC2¤]§ï¦¨¼Æ¾Ú®w¸Ì¦s¦bªº¤é´Á²M³æ¡H
¿ï©w¤FA2¡A¨º»òB2¡BC2¡BD2¡BE2¤]´N¸òµÛ§ïÅܲM³æ¤º®e¡F
¿ï©w¤FA2¡BB2¡A¨º»òC2¡BD2¡BE2¤]´N¸òµÛ§ïÅܲM³æ¤º®e¡F
¾l¦¹Ãþ±À¡A¦pªG¿ï©w¤F¬Y¨â­Ó©ÎªÌ¤T­Óªº¸Ü¡A¨º»ò¨ä¾lªº¤]¸òÀH§ïÅܲM³æ¤º®e¡F
´N¬O³o»òªº¤@­Ó¥æ¤e¬d¸ß¡B¤¬¬°§ïÅܪº¤@­ÓÁp¯Å²M³æ¡C

maiko

½Ð¤j¤jÀ°À°¦£¡A«ô°U¤@¤U¡A«ôÁ¤F!

maiko

¦^´_  maiko


sheet1¼Ò²Õ¤@¯ë¼Ò²Õ
Hsieh µoªí©ó 2012-9-26 20:41

   
§Úı±o¦n¹³­n¨D¶V¨Ó¶V¦h¦üªº¡A§Ú¦³ÂIı±o¤£¦n·N«ä¡A¤£¹LÁٳѤU¤@ÂIÂIªF¦è­n¤j¤j¦AÀ°À°¦£¡A´N³o»ò¤@ÂIÂIªF¦è¤F¡A¦pªG§Ë¦n¨º´N§¹¬ü¤F¡A¤£ª¾¹D¤j¤j¯à§_¦A°Ê¤âÀ°¦£­×§ï¤@¤U¡A¯u¬O·P¿E¤£ºÉ¡I

´N¬O¯à§_§âA2¡BB2¡BC2¤]§ï¦¨¼Æ¾Ú®w¸Ì¦s¦bªº¤é´Á²M³æ¡H
¿ï©w¤FA2¡A¨º»òB2¡BC2¡BD2¡BE2¤]´N¸òµÛ§ïÅܲM³æ¤º®e¡F
¿ï©w¤FA2¡BB2¡A¨º»òC2¡BD2¡BE2¤]´N¸òµÛ§ïÅܲM³æ¤º®e¡F
¦p¦¹Ãþ±À¡A¦pªG¿ï©w¤F¬Y¨â­Ó©ÎªÌ¤T­Óªº¸Ü¡A¨º»ò¨ä¾lªº¤]¸òÀH§ïÅܲM³æ¤º®e¡F
´N¬O³o»òªº¤@­Ó¥æ¤e¬d¸ß¡B¤¬¬°§ïÅܪº¤@­ÓÁp¯Å²M³æ¡A·d©w¤F¡A¨º»ò³o­Ó¼Æ¾Ú®w´N§¹¦¨¤F¡C

·P¿E¤j¤jªº²±±¡À°¦£¡I¦b¦¹«ôÁ¡I¤d¸U§O¶û¦b¤U³Â·Ð¡C·PÁ¡I

maiko

¦^´_  maiko


sheet1¼Ò²Õ¤@¯ë¼Ò²Õ
Hsieh µoªí©ó 2012-9-26 20:41

   
¤j¤j¡A¯u¬O·PÁ§AªºÀ°¦£¡A¸Õ¹L¥N½X¡AÁÙ¯u¦æ¡I

¤£¹LÁÙ¦³¤@ÂIÂI¿ù»~¡A¤j¤j¯à§_À°¦£§ï¤@¤U¡A´N¬O¡A¦pªG¦b¿ï©w¤FA2¡BB2¡BC2¤§«á¡A¦pªGD2¤]¿ï©wªº¸Ü¡A¨º»òE2¯à§_ÀHµÛD2ªº¿ï¾Ü¦Ó¥X²{¹ïÀ³ªº²M³æ¤º®e¡H

·PÁ¡I

Hsieh

¦^´_ 14# maiko

Book3_New.rar (26.71 KB)

¤U¸ü¦¸¼Æ: 18

2012-9-26 20:39

sheet1¼Ò²Õ

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. Application.EnableEvents = False
   
3. If Not Intersect(Target, [A2:C2]) Is Nothing Then CreateList
   
4. Application.EnableEvents = True
   
5. End Sub

½Æ»s¥N½X

¤@¯ë¼Ò²Õ

1. Sub Search_Data_New()
   
2. Application.ScreenUpdating = False
   
3. Application.EnableEvents = False
   
4. With Sheet1
   
5.   y = .[A2]: m = .[B2]: d = .[C2]
   
6.   .[A2] = IIf(.[A2] = "", "", "=YEAR(Sheet2!A2)=" & y)
   
7.   .[B2] = IIf(.[B2] = "", "", "=MONTH(Sheet2!A2)=" & m)
   
8.   .[C2] = IIf(.[C2] = "", "", "=DAY(Sheet2!A2)=" & d)
   
9. 
   
10. With Sheet2
    
11.    .Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, Sheet1.[A1:E2], Sheet1.[A6:D6], False
    
12. End With
    
13. 
    
14. If .[A7] = "" Then
    
15.   MsgBox "µL¸ê®Æ"
    
16. Else
    
17.   .Cells(.Rows.Count, 3).End(xlUp).Offset(2).Resize(, 2) = Array("Á`¦@:", "=SUM(R7C:R[-1]C)")
    
18. End If
    
19.   
    
20.   .[A2] = y
    
21.   .[B2] = m
    
22.   .[C2] = d
    
23. 
    
24. End With
    
25. Application.ScreenUpdating = True
    
26. Application.EnableEvents = True
    
27. End Sub
    
28. Sub CreateList()
    
29. Dim A As Range
    
30. Set dic = CreateObject("Scripting.Dictionary")
    
31. Set dic1 = CreateObject("Scripting.Dictionary")
    
32. With Sheet1
    
33.   y = .[A2]: m = .[B2]: d = .[C2]
    
34.   .[A2] = IIf(.[A2] = "", "", "=YEAR(Sheet2!A2)=" & y)
    
35.   .[B2] = IIf(.[B2] = "", "", "=MONTH(Sheet2!A2)=" & m)
    
36.   .[C2] = IIf(.[C2] = "", "", "=DAY(Sheet2!A2)=" & d)
    
37. 
    
38. With Sheet2
    
39. .[F1:G1] = .[B1:C1].Value
    
40.    .Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, Sheet1.[A1:C2], .[F1:G1], True
    
41.    r = .Range("F1").CurrentRegion.Rows.Count
    
42.    With .Range(.[F1], .[F1].End(xlDown))
    
43.    .Sort key1:=.Cells(1, 1), Header:=xlYes
    
44.    If r > 1 Then
    
45.    For Each A In .Cells(1).Offset(1).Resize(.Count - 1, 1)
    
46.       dic(A.Value) = ""
    
47.    Next
    
48.    End If
    
49.    .Clear
    
50.    End With
    
51.    With .Range(.[G1], .[G1].End(xlDown))
    
52.    .Sort key1:=.Cells(1, 1), Header:=xlYes
    
53.    If r > 1 Then
    
54.    For Each A In .Cells(1).Offset(1).Resize(.Count - 1, 1)
    
55.       dic1(A.Value) = ""
    
56.    Next
    
57.    End If
    
58.    .Clear
    
59.    End With
    
60. 
    
61. End With
    
62. With .Range("D2").Validation
    
63.   .Delete
    
64.   If r > 1 Then .Add xlValidateList, , , Join(dic.keys, ",")
    
65. End With
    
66. With .Range("E2").Validation
    
67.   .Delete
    
68.   If r > 1 Then .Add xlValidateList, , , Join(dic1.keys, ",")
    
69. End With
    
70.   
    
71.   .[A2] = y
    
72.   .[B2] = m
    
73.   .[C2] = d
    
74. 
    
75. End With
    
76. End Sub

½Æ»s¥N½X

maiko

¦^´_  maiko

¦bSheet2¤¤ªºB¡BCÄæÀH«K¥´­Ó¸ê®Æ¬Ý¬Ý
Hsieh µoªí©ó 2012-9-25 15:09

   
¸É¥R¤@¤U¡G
¯à§_³q¹LSheet1ªí¤¤ªºA2¡BB2¡BC2Àx¦s®æªº§ïÅܦӥO¨ìD2¡BE2Àx¦s®æªº²M³æ§ïÅÜ¡HÁÂÁ¡I

maiko

¦^´_  maiko

¦bSheet2¤¤ªºB¡BCÄæÀH«K¥´­Ó¸ê®Æ¬Ý¬Ý
Hsieh µoªí©ó 2012-9-25 15:09

ªGµM¥i¥H°Õ¡I
¤£¹L¯à§_§â²M³æªº¶¶§Ç±Æ¬°¤É§Ç¶Ü¡HÁÂÁ¡I

Hsieh

¦^´_ 11# maiko

¦bSheet2¤¤ªºB¡BCÄæÀH«K¥´­Ó¸ê®Æ¬Ý¬Ý

maiko

¤j¤j¡A¯à§_À°§Ú¬Ý¬Ý¡AÁÂÁ¡I