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

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

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

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



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


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


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





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



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



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



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



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

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

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

TOP

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

TOP

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

TOP

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

TOP

¦^´_ 14# maiko

Book3_New.rar (26.71 KB)
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. With Sheet2
  10.    .Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, Sheet1.[A1:E2], Sheet1.[A6:D6], False
  11. End With

  12. If .[A7] = "" Then
  13.   MsgBox "µL¸ê®Æ"
  14. Else
  15.   .Cells(.Rows.Count, 3).End(xlUp).Offset(2).Resize(, 2) = Array("Á`¦@:", "=SUM(R7C:R[-1]C)")
  16. End If
  17.   
  18.   .[A2] = y
  19.   .[B2] = m
  20.   .[C2] = d

  21. End With
  22. Application.ScreenUpdating = True
  23. Application.EnableEvents = True
  24. End Sub
  25. Sub CreateList()
  26. Dim A As Range
  27. Set dic = CreateObject("Scripting.Dictionary")
  28. Set dic1 = CreateObject("Scripting.Dictionary")
  29. With Sheet1
  30.   y = .[A2]: m = .[B2]: d = .[C2]
  31.   .[A2] = IIf(.[A2] = "", "", "=YEAR(Sheet2!A2)=" & y)
  32.   .[B2] = IIf(.[B2] = "", "", "=MONTH(Sheet2!A2)=" & m)
  33.   .[C2] = IIf(.[C2] = "", "", "=DAY(Sheet2!A2)=" & d)

  34. With Sheet2
  35. .[F1:G1] = .[B1:C1].Value
  36.    .Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, Sheet1.[A1:C2], .[F1:G1], True
  37.    r = .Range("F1").CurrentRegion.Rows.Count
  38.    With .Range(.[F1], .[F1].End(xlDown))
  39.    .Sort key1:=.Cells(1, 1), Header:=xlYes
  40.    If r > 1 Then
  41.    For Each A In .Cells(1).Offset(1).Resize(.Count - 1, 1)
  42.       dic(A.Value) = ""
  43.    Next
  44.    End If
  45.    .Clear
  46.    End With
  47.    With .Range(.[G1], .[G1].End(xlDown))
  48.    .Sort key1:=.Cells(1, 1), Header:=xlYes
  49.    If r > 1 Then
  50.    For Each A In .Cells(1).Offset(1).Resize(.Count - 1, 1)
  51.       dic1(A.Value) = ""
  52.    Next
  53.    End If
  54.    .Clear
  55.    End With

  56. End With
  57. With .Range("D2").Validation
  58.   .Delete
  59.   If r > 1 Then .Add xlValidateList, , , Join(dic.keys, ",")
  60. End With
  61. With .Range("E2").Validation
  62.   .Delete
  63.   If r > 1 Then .Add xlValidateList, , , Join(dic1.keys, ",")
  64. End With
  65.   
  66.   .[A2] = y
  67.   .[B2] = m
  68.   .[C2] = d

  69. End With
  70. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

TOP

¦^´_  maiko

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



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

TOP

¦^´_ 11# maiko

¦bSheet2¤¤ªºB¡BCÄæÀH«K¥´­Ó¸ê®Æ¬Ý¬Ý
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

TOP

        ÀR«ä¦Û¦b : ¹D¼w¬O´£ª@¦Û§Úªº©ú¿O¡A¤£¸Ó¬O¨þ¥¸§O¤HªºÃ@¤l¡C
ªð¦^¦Cªí ¤W¤@¥DÃD