ªð¦^¦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

¦^´_ 1# maiko


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

TOP

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

TOP

¦^´_  maiko
Hsieh µoªí©ó 2012-9-18 20: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

TOP

¦^´_ 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·|§ïÅÜ
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

TOP

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

TOP

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

TOP

        ÀR«ä¦Û¦b : µÊ®ð¼L¤Ú¤£¦n¡A¤ß¦a¦A¦n¤]¤£¯àºâ¬O¦n¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD