[¨D§U] ½Ð°ª¤â¨D§U¡A¦p¦ó¥Îvba¬d§ä¸ê®Æ®w¤¤ªº¸ê®Æ°O¿ý¡H
- ©«¤l
- 96
- ¥DÃD
- 29
- ºëµØ
- 0
- ¿n¤À
- 129
- ÂI¦W
- 0
- §@·~¨t²Î
- win
- ³nÅ骩¥»
- 7
- ¾\ŪÅv
- 20
- µù¥U®É¶¡
- 2011-3-8
- ³Ì«áµn¿ý
- 2024-8-14
|
[¨D§U] ½Ð°ª¤â¨D§U¡A¦p¦ó¥Îvba¬d§ä¸ê®Æ®w¤¤ªº¸ê®Æ°O¿ý¡H
|
|
|
|
|
|
- ©«¤l
- 96
- ¥DÃD
- 29
- ºëµØ
- 0
- ¿n¤À
- 129
- ÂI¦W
- 0
- §@·~¨t²Î
- win
- ³nÅ骩¥»
- 7
- ¾\ŪÅv
- 20
- µù¥U®É¶¡
- 2011-3-8
- ³Ì«áµn¿ý
- 2024-8-14
|
|
|
|
|
|
|
- ©«¤l
- 4901
- ¥DÃD
- 44
- ºëµØ
- 24
- ¿n¤À
- 4916
- ÂI¦W
- 44
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Office 20xx
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_
- µù¥U®É¶¡
- 2010-4-30
- ³Ì«áµn¿ý
- 2025-2-20
               
|
¦^´_ 2# maiko - Sub Search_Data()
- d = [A2]
- [A2] = IIf([A2] >= 1 And [A2] <= 12, "=MONTH(Sheet2!A2)=" & d, IIf(d = "", "", "=YEAR(Sheet2!A2)=" & d))
- [A1] = IIf([A2] <> "", "", "¤é´Á")
- With Sheet2
- .Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, Sheet1.[A1:C2], Sheet1.[A6:D6], False
- End With
- Cells(Rows.Count, 3).End(xlUp).Offset(2).Resize(, 2) = Array("Á`¦@:", "=SUM(R7C:R[-1]C)")
- [A2] = d
- [A1] = "¤é´Á"
- End Sub
½Æ»s¥N½X |
|
¾Ç®üµL²P_¤£®¢¤U°Ý
|
|
|
|
|
- ©«¤l
- 96
- ¥DÃD
- 29
- ºëµØ
- 0
- ¿n¤À
- 129
- ÂI¦W
- 0
- §@·~¨t²Î
- win
- ³nÅ骩¥»
- 7
- ¾\ŪÅv
- 20
- µù¥U®É¶¡
- 2011-3-8
- ³Ì«áµn¿ý
- 2024-8-14
|
|
|
|
|
|
|
- ©«¤l
- 96
- ¥DÃD
- 29
- ºëµØ
- 0
- ¿n¤À
- 129
- ÂI¦W
- 0
- §@·~¨t²Î
- win
- ³nÅ骩¥»
- 7
- ¾\ŪÅv
- 20
- µù¥U®É¶¡
- 2011-3-8
- ³Ì«áµn¿ý
- 2024-8-14
|
¦^´_ 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 |
|
|
|
|
|
|
- ©«¤l
- 4901
- ¥DÃD
- 44
- ºëµØ
- 24
- ¿n¤À
- 4916
- ÂI¦W
- 44
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Office 20xx
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_
- µù¥U®É¶¡
- 2010-4-30
- ³Ì«áµn¿ý
- 2025-2-20
               
|
¦^´_ 5# maiko
¶i¶¥¬d¸ß¡A¦b¤@¯ë¼Ò²Õ- Sub Search_Data()
- With Sheet1
- y = .[A2]: m = .[B2]: d = .[C2]
- .[A2] = IIf(.[A2] = "", "", "=YEAR(Sheet2!A2)=" & y)
- .[B2] = IIf(.[B2] = "", "", "=MONTH(Sheet2!A2)=" & m)
- .[C2] = IIf(.[C2] = "", "", "=DAY(Sheet2!A2)=" & d)
- With Sheet2
- .Range("A1").CurrentRegion.AdvancedFilter xlFilterCopy, Sheet1.[A1:E2], Sheet1.[A6:D6], False
- End With
- If .[A7] = "" Then
- MsgBox "µL¸ê®Æ"
- Else
- .Cells(.Rows.Count, 3).End(xlUp).Offset(2).Resize(, 2) = Array("Á`¦@:", "=SUM(R7C:R[-1]C)")
- End If
- .[A2] = y
- .[B2] = m
- .[C2] = d
- End With
- End Sub
½Æ»s¥N½X ¨ú±oSheet2¤u§@ªíB¡BCÄ椣«½Æ²M³æ°µ¬°ÅçÃÒ²M³æ
Sheet2¤u§@ªí¼Ò²Õ- Private Sub Worksheet_Change(ByVal Target As Range)
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- If Target.Column = 2 Or Target.Column = 3 Then
- For Each a In Range([B2], Cells(Rows.Count, 2).End(xlUp)).SpecialCells(xlCellTypeConstants)
- d(a.Value) = ""
- d1(a.Offset(, 1).Value) = ""
- Next
- With Sheet1
- With .Range("D2").Validation
- .Delete
- .Add xlValidateList, , , Join(d.keys, ",")
- End With
- With .Range("E2").Validation
- .Delete
- .Add xlValidateList, , , Join(d1.keys, ",")
- End With
- End With
- End If
- End Sub
½Æ»s¥N½X Sheet2¤u§@ªíB¡BCÄ榳ÅܰʮɡASheet1¤u§@ªí[D2]¡B[E2]ªºÅçÃÒ²M³æ´N·|§ïÅÜ |
|
¾Ç®üµL²P_¤£®¢¤U°Ý
|
|
|
|
|
- ©«¤l
- 96
- ¥DÃD
- 29
- ºëµØ
- 0
- ¿n¤À
- 129
- ÂI¦W
- 0
- §@·~¨t²Î
- win
- ³nÅ骩¥»
- 7
- ¾\ŪÅv
- 20
- µù¥U®É¶¡
- 2011-3-8
- ³Ì«áµn¿ý
- 2024-8-14
|
¦^´_ 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 |
|
|
|
|
|
|
- ©«¤l
- 96
- ¥DÃD
- 29
- ºëµØ
- 0
- ¿n¤À
- 129
- ÂI¦W
- 0
- §@·~¨t²Î
- win
- ³nÅ骩¥»
- 7
- ¾\ŪÅv
- 20
- µù¥U®É¶¡
- 2011-3-8
- ³Ì«áµn¿ý
- 2024-8-14
|
¦^´_ 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)
|
|
|
|
|
|
|
- ©«¤l
- 96
- ¥DÃD
- 29
- ºëµØ
- 0
- ¿n¤À
- 129
- ÂI¦W
- 0
- §@·~¨t²Î
- win
- ³nÅ骩¥»
- 7
- ¾\ŪÅv
- 20
- µù¥U®É¶¡
- 2011-3-8
- ³Ì«áµn¿ý
- 2024-8-14
|
¦^´_ 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)
|
|
|
|
|
|
|