½Ð°ª¤â«üÂI¤@¤U¡A¦p¦ó°µ¥X°ÊºAªº¦æ¨Æ¾ä¤ÎÂùÀ»¿ï¾Ü¿é¤Jªº¥\¯à¡H

eigen

½Ð°ª¤â«üÂI¤@¤U¡A¦p¦ó°µ¥X°ÊºAªº¦~¾ä¤ÎÂùÀ»¿ï¾Ü¿é¤Jªº¥\¯à¡H

¦pªþ¥ó

https://dl.dropboxusercontent.com/u/12575824/%E8%A1%8C%E4%BA%8B%E6%9B%86.xls

§Ú»Ý­n¤@­Ó¤é´Á¿é¤Jªº¥\¯à¡A¥Ø«e¥~Æ[¤j­P¤W´N¹³ªþ¥ó³o¼Ë¡C

§Ú­n¦b¿é¤J­¶ ¡A ¿ï¾Ü¤@­Ó Àx¦s®æ¡]°ò¥»¤W¬O ¤é´ÁÄæ ¤U­±¡^¡A«ö¤U§Ö³tÁä«á¡A·|¸õ¨ì¦~¾ä³o¤@­¶¡C¡]³o³¡¥÷À³¸Óok¡^

¦~¾ä³o¤@¼Ë¡A§Ú§Æ±æ²Ä¤@¦æ²Ä¤@­Ó¡A¬O¤W­Ó¤ë¡A²Ä¤G¥H¤~¬O today()©Ò¦bªº¤ë¥÷¡C¥H¦¹Åã¥Ü12­Ó¤ë¥÷¡C

¤@¡B¦]¤µ¤Ñªº¤ë¥÷·|ÅÜ¡A©Ò¥H¾ã­Ó·|Åܦ¨¬O°ÊºA½Õ¾ã¡C½Ð«ü±Ð¡A³o¼Ëªº¥\¯à¸Ó¦p¦ó¤U¤â¡H

¤G¡BÂùÀ»Àx¦s®æ¡A·|±N¸Ó¤é´Á¼g¨ì¿é¤J­¶¡A¿ï¾ÜªºÀx¦s®æ¤§¤¤¡C¦pªGµLªkÂùÀ»¡A§ï¥Î§Ö³tÁä¿é¤J¤]¬O¥i¥H¡C
¥Ø«e¤£ª¾¹D¸Ó¦p¦ó°µ¨ì³o¼Ëªº¥\¯à¡A½Ð«ü¥O¤@¤U¡C

¤T¡B¥Ø«e¦~¾äªº 2013-12 2014-1~2014-10 ³o´X­Ó¬O¥Î today() - 30 -60 -90-120 -150 ... ³o¼Ëªº¡yÄê¡z¤èªk°µ¥X¨Óªº
½Ð«ü±Ð¡A¸Ó«ç»ò°µ¤ñ¸û¦n~~

eigen

¦pªþ¥ó
https://dl.dropboxusercontent.com/u/12575824/%E8%A1%8C%E4%BA%8B%E6%9B%86.xls

¨C­Ó¤ëªº²Ä¤@¤Ñ¡A§Ú¥Î =eomonth(TODAY(),quotient(COLUMN()-2,8)+quotient(ROW()-2,9)*4-5)+1 ¸Ñ¨M¤F

¨C­Ó¤ëªº¨C¤@¤Ñ¡A§Ú¥Î
                OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,)
-(weekday(        OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,),1)-1)
+ MOD(ROW()-4,9)*7
+MOD(COLUMN()-2,8)

¸Ñ¨M¤F
¬°¤F­n¦n¬Ý¡A¤S¥[¤W¤F
=if(        month(OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,))<>
        month(OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,)-(weekday(        OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,),1)-1)+ MOD(ROW()-4,9)*7 +MOD(COLUMN()-2,8)),
        "",
        OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,)-(weekday(        OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,),1)-1)+ MOD(ROW()-4,9)*7 +MOD(COLUMN()-2,8))



¡°²{¦bªº°ÝÃD
¤£Ãøµo²{¡A«ü¥O¤Óªø¤F¡A¤S¯ä¤Sªø
OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,)-(weekday(        OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,),1)-1)+ MOD(ROW()-4,9)*7 +MOD(COLUMN()-2,8)
¤Óªø¤F¡A§Ú­ì¥»·Q­n¥Î ´¡¤J->¦WºÙ->©w¸q ªº¤è¦¡¸Ñ¨M¡A¦ý¬O©w¸q month_first_day -->OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,)

¦A±N¨C¤éªº­pºâ¤½¦¡¼g¦¨
=if(        month(        month_first_day)<>
        month(        month_first_day-(weekday(        month_first_day,1)-1)+ MOD(ROW()-4,9)*7 +MOD(COLUMN()-2,8)),
        "",
                month_first_day-(weekday(        month_first_day,1)-1)+ MOD(ROW()-4,9)*7 +MOD(COLUMN()-2,8))

µLªk¨Ï¥Î¡A½Ð«ü±Ð¡B«üÂI¡AÁÂÁÂ

GBKEE

¦^´_ 2# eigen
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. '¤u§@ªí¼Ò²Õ:ÂùÀ»Àx¦s®æµ{¦¡
   
3. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   
4.     Ex Target.Cells(1), Target.Parent
   
5.     Target.Select
   
6. End Sub
   
7. 
   
8. Private Sub Ex(Rng As Range, sh As Worksheet)
   
9.     Dim T_Date, T_Mon As Integer, TE As Date
   
10.     Dim M As Integer, Week_Ar(), I As Integer
    
11.     Week_Ar = Array("¤é", "¤@", "¤G", "¤T", "¥|", "¤­", "¤»")
    
12.     Do Until IsDate(T_Date)
    
13.         T_Date = InputBox("½Ð«ü©w¤é´Á", , Date)
    
14.         If T_Date = "" Then Exit Sub
    
15.     Loop
    
16.     With sh
    
17.         .Cells.Clear                            '²M°£¤u§@ªí
    
18.         .Cells.HorizontalAlignment = xlCenter   'Àx¦s®æª«¥óªº¤ô¥­¹ï»ô¼Ò¦¡:¸m¤¤
    
19.         .Cells.VerticalAlignment = xlCenter     'Àx¦s®æª«¥óªº««ª½¹ï»ô¼Ò¦¡:¸m¤¤
    
20.     End With
    
21.    
    
22.     With Rng.Resize(, 7)                        '«ü©w¤é´Áªº®æ¦¡
    
23.         .Merge                                  '¦X¨Ö: .Resize(, 7)
    
24.         .Font.Size = 30
    
25.         .Font.Bold = True
    
26.         .Font.Color = vbBlue
    
27.         .NumberFormatLocal = "yyyy-m-d"
    
28.          Rng = T_Date                           '¤é´Á
    
29.     End With
    
30.     I = I + 3
    
31.     For M = -1 To 1
    
32.         T_Date = DateAdd("M", M, Rng.Cells(1))                           '¨ú±o«ü©w¤ë¥÷ªº¤é´Á
    
33.         T_Date = DateSerial(Year(T_Date), Month(T_Date), 1)     '«ü©w¤ë¥÷¤é´Áªº 1¸¹¶}©l
    
34.         T_Mon = Month(T_Date)                                   '«ü©wªº¤ë¥÷
    
35.         With Rng.Cells(I).Resize(1, 7)                          '¼g¤J «ü©w¤ë¥÷ªº®æ¦¡
    
36.             .Merge
    
37.             .Cells = T_Date
    
38.             .NumberFormatLocal = "yyyy-m"
    
39.             .Font.Size = 15
    
40.             .Font.Bold = True
    
41.             .Font.Color = vbWhite
    
42.             .Interior.Color = vbBlue
    
43.         End With
    
44.         I = I + 1
    
45.         With Rng.Cells(I).Resize(, 7)                           '¼g¤J¬P´Áªº®æ¦¡
    
46.             .Cells = Week_Ar
    
47.             .Interior.ColorIndex = 15
    
48.             .Range("A1,G1").Font.Color = vbRed
    
49.         End With
    
50.         I = I + 1
    
51.         Do While T_Mon = Month(T_Date)                          '°j°éªº±ø¥ó:¦P¤@­Ó¤ë¥÷
    
52.             With Rng.Cells(I, Weekday(T_Date))
    
53.                 .Cells = T_Date
    
54.                 .NumberFormatLocal = "D"
    
55.                 If Weekday(T_Date) = 1 Or Weekday(T_Date) = 7 Then .Font.Color = vbRed
    
56.             End With
    
57.             If Weekday(T_Date) = 7 And T_Mon = Month(T_Date + 1) Then I = I + 1
    
58.             T_Date = T_Date + 1                                 '¤é´Á¥[¤@¤Ñ
    
59.         Loop
    
60.         I = I + 2
    
61.     Next
    
62. End Sub

½Æ»s¥N½X

eigen

¦^´_ 3# GBKEE


ÁÂÁ¤j¤jªº¤j¤OÀ°¦£¡A§Ú¸ÕµÛ­×§ï¡A¥Ø«e¤w¸g¡y¤j­P¡zok¤F¡A

§Ú°Ñ¦Ò§Aªº½d¨Ò¡A­×§ïµ{¦¡

¦pªþ¥ó
https://dl.dropboxusercontent.com/u/12575824/%E8%A1%8C%E4%BA%8B%E6%9B%86.xls

1. Option Explicit
   
2. '¤u§@ªí¼Ò²Õ:ÂùÀ»Àx¦s®æµ{¦¡
   
3. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   
4. Dim aa As Integer
   
5. 
   
6.         Cells(7, 2).Value = Target.Cells(1, 1)                'copy to B2
   
7.         'Cells(7, 2).NumberFormatLocal = "G/³q¥Î®æ¦¡"
   
8. 
   
9.         Ex Cells(7, 2), Target.Parent
   
10. 'Target.Select
    
11. End Sub
    
12. 
    
13. Private Sub Ex(Rng As Range, sh As Worksheet)
    
14.         Dim E As Range
    
15. 
    
16.         If Rng = "" Then Exit Sub
    
17.         If IsNumeric(Rng) Then
    
18.                 Sheets("Sheet1").Activate
    
19.                 If Selection.Row > 1 And Selection.Row < 52 And Selection.Column >= 6 And Selection.Column <= 7 Then
    
20.                         Selection.Value = Rng
    
21.                 End If
    
22.         End If
    
23. End Sub

½Æ»s¥N½X

¥[¨ì sheet ¤ë¾ä¤§¤¤¡A¨Ã¦b sheet1 ¤§¤¤¥[¤J

1. Option Explicit
   
2. '¤u§@ªí¼Ò²Õ:ÂùÀ»Àx¦s®æµ{¦¡
   
3. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   
4.         If Target.Cells(1).Row > 1 And Target.Cells(1).Row < 52 And Target.Cells(1).Column >= 6 And Target.Cells(1).Column <= 7 Then
   
5.                 Sheets("¤ë¾ä").Activate
   
6.         End If
   
7. End Sub

½Æ»s¥N½X

¦p¦¹¤@¨Ó¡A§Ú«K¥i¥H¦b¨â­Ósheet ¤¤¡A¥Î ÂùÀ»°µ¤Á´«¡C¡]³o³¡¥÷¥\¯àok¤F¡A¤Q¤G¸U¤Àªº·PÁ¡^

²{¦b§Ú¤£¤F¸Ñªº¬O

¤@¡B­ì¥»´£¨Ñªºµ{¦¡¬O

Ex Target.Cells(1), Target.Parent

³o­Ó Target.Cells(1) §Ú«ç»ò§ï¡A³£§ï¤£¥X­ì©l­È¡]³£¬O¸g¹L®æ¦¡¤Æªº¸ê®Æ¡^¡A³Ì«á§Ú¨S§é¡A¥u¦n±N­È¼g¨ìb2 ¡A¦A±Nb2 ªº­È¥á¥X¨Ó¡]¬Ý¤£¨ì­È¬O¦]¬°¦r«¬ÃC¦â§Ú½Õ¾ã¥Õ¦â¡^

¤G¡B
­pºâ¤é´Á
=if(        month(OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,))<>
        month(OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,)-(weekday(        OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,),1)-1)+ MOD(ROW()-4,9)*7 +MOD(COLUMN()-2,8)),
        "",
        OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,)-(weekday(        OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,),1)-1)+ MOD(ROW()-4,9)*7 +MOD(COLUMN()-2,8))
³o­Ó¼ö¼öªøªºµ{¦¡¡A¦³¾÷·|ÁYµu¶Ü¡H ¡]¨Ò¦p¥Î ¦WºÙ ©w¸q¡^

eigen

°l¨D§¹¥þªº³Ì«á¤@Ãö¡A±N¥k°¼ªí¦Cªº¥ð°²¤é¡A¦b¥ª°¼ªº¤ë¾ä¤¤¡A±N¦r«¬§ï¥Î¬õ¦â

¦pªþ¥ó
https://dl.dropboxusercontent.com/u/12575824/%E8%A1%8C%E4%BA%8B%E6%9B%86.xls

¦]¬°¤é´Á³£¬O¥Îºâªº¡A¦ì¸m¤£©T©w¡A§Ú¤£ª¾¦p¦ó¤U¤â

´Nºâ¥Î ¡y³]©w®æ¦¡¤Æªº±ø¥ó¡z¤]¥u¯à¦³¤T­Ó±ø¥ó¡AµLªk±N¼Æ¤Q­Ó¤é´Á¥[¤W¥h¡A½Ð°ª¤â«üÂI~~ÁÂÁÂ

GBKEE

¦^´_ 4# eigen

1. '¤u§@ªí¼Ò²Õ:ÂùÀ»Àx¦s®æµ{¦¡
   
2. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   
3.     Ex Target, Target.Parent
   
4.     'Sub Ex ªºµ{¦¡½X¤¤
   
5.     'With Rng.Resize(, 7)    '¤é´Áªº½d³ò
   
6.     '   .Merge               '¦X¨Ö¬°Àx¦s®æ
   
7.     '*** -> Target.Resize(, 7)  ¦X¨Ö¬°Àx¦s®æ  ****
   
8.     '***    ­ì¥»ªº¦ì¸m·í§A¦A¦¸,ÂùÀ»Àx¦s®æ,¬Ý¬Ý¦³¦óÅܤÆ
   
9. End Sub

½Æ»s¥N½X

³o¤u§@ªí¨ç¼Æ¸¨¸¨ªø,²´¤O¤£¦n,¦³½Ð²´¤O¦nªÌ,À°À°¦£.

eigen

¦^´_ 6# GBKEE


   ¨þ~~§Úªº²´¤O¤]«Ü®t¡A¼gªº®É­Ô¡A§Ú¬O¥Î ultraedit ¤@¬q¤@¬q«÷¥X¨Ó«á¡A¤~¶K¨ì excel

¥Ø«e§Ú¥Î²´¤£¨£¬°²bªº¤è¦¡¡AÀx¦s®æ®æ¦¡->«OÅ@->ÁôÂà ¥[¤W¤u¨ã->«OÅ@ ±N¤½¦¡ÁôÂð_¨Ó¡]¤£µM¤@ÂIÀx¦s®æ¡A¾ã­Óµe­±#$$^%¡^

eigen

¦^´_ 6# GBKEE

§Ú¸Õ¹L Ex Target, Target.Parent

¤U¸ü (18.07 KB)

2013-12-16 21:22

ÂùÀ»®Étarget Åܦ¨ 1/23/2014 ¸g¹L®æ¦¡¤Æªº­È¦Ó¤£¬O 41662 ¡A¥H­P©ó«á­±§ÚªºÀˬdµ{§Ç³£µLªk¹B§@¡C

¤é¾ä¤Wªº¤é´Á³£¬O¥¿½Tªº¤é´Á¡A¥u¬O³z¹LÀx¦s®æ®æ¦¡ ¦Û©w¦¨ DD ©Î YYYY-MM ¡A­ì©l¸ê®Æ³£ÁÙ¬O ¼Æ­È

§Q¥Î³o¼Ëªº¯S©Ê¡A§Ú¦bÂùÀ»Àx¦s®æ®É¡A§Ú´Nª¾¹D¨Ï¥ÎªÌÂIÀ»ªº¬O¤£¬O¤é´Á¡C

stillfish00

¦^´_ 7# eigen
§Ú¥Î2¼Ó©w¸q¦WºÙ¨S°ÝÃD°Ú!
month_first_day
=OFFSET($B$2,QUOTIENT(ROW()-4,9)*9,QUOTIENT(COLUMN()-2,8)*8,,)

calc_day
=IF(MONTH(month_first_day)<>MONTH(month_first_day-(WEEKDAY(month_first_day,1)-1)+ MOD(ROW()-4,9)*7 +MOD(COLUMN()-2,8)),"",month_first_day-(WEEKDAY(month_first_day,1)-1)+ MOD(ROW()-4,9)*7 +MOD(COLUMN()-2,8))

eigen

¦^´_ 9# stillfish00


Dear Sir,
³Â·Ð¦AÀ°¤@¤U

¦pªþ¥ó
https://dl.dropboxusercontent.com/u/12575824/%E8%A1%8C%E4%BA%8B%E6%9B%86.xls

quotient ¨ì©³¬O¨º¥X¤F°ÝÃD¡H

¤U¸ü (89 KB)

2013-12-16 23:19