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

¦p¦ó©¹«e±Àºâ¤é´Áªº°ÝÃD¡C

¦p¦ó©¹«e±Àºâ¤é´Áªº°ÝÃD¡C

½Ð°Ý¦U¦ì¥ý¶i¡A¥Ø«e¤p§Ì¦³¤@»Ý¨D¡A¦pA2Äæ¦ì11/5¡A¹ïÀ³¨ìB2Äæ¦ì¡A©¹«e±Àºâ3¤Ñ¡A©¹«e±Àºâ3¤Ñªº¹Lµ{¤¤¡A¦p¹L¸I¨ì¤»¡B¤é¡B°²¤é«h¸õ¹L¡C


¦³¿ìªk¥H¤½¦¡¨Ó§¹¦¨¶Ü?¡A³Â·Ð¦U¦ì¤F¡C

§ó¥¿¤@¤U¡A¤U¹Ï¤~¬O¹ïªº¡AÁÂÁÂ

TOP

http://blog.xuite.net/hcm19522/twblog/360124342

TOP

¦^´_ 3# hcm19522

hcm19522¤j¤j¡A«D±`ÁÂÁ±z¡A¥Ø«e§Ú«e¤T¤Ñ¡B«e¤»¤Ñªº³£¨S°ÝÃD¤F¡C
¦ý¬OÃö©ó«e14¤Ñªº¤ñ¸û¯S§O¡A¥²¶·­n¦³3¤Ñ§t¥H¤Wªº°²¤é¡A¦p¤»¡B¤é¡B¤@³s°²¡A¤~·|¦©°£
¤Ï¤§¦pªG¨S¦³¤T¤Ñ¥H¤W¡A«h¤»¤é¤Î°²¤é«h¤£¦©°£¡C

TOP

¦] "«e14¤Ñªº¤ñ¸û¯S§O¡A¥²¶·­n¦³3¤Ñ§t¥H¤Wªº°²¤é¡A¦p¤»¡B¤é¡B¤@³s°²¡A
¤~·|¦©°£,   ¦pªG¨S¦³¤T¤Ñ¥H¤W¡A«h¤»¤é¤Î°²¤é«h¤£¦©°£¡C"
ªºÃö«Y, §Ú·Q¨ì¥Î "¤é¬W" ¨Ó³B²z,  "¤é¬W" ¨ú¦Û
http://forum.twbts.com/thread-11971-1-1.html
¨Ï¥Î«e½Ð¥ý®Ö¹ï, ¦]¸Ó "³s°²" «Y®Ú¾Ú¤½®a¾÷Ãö¦Ó­q, ¨p¤H¾÷ºc¥i¯à­n­×¥¿¡C

¤@¡B«Ø¥ß »²§UÄæ "xx" ¤Î "yy"
1. ´¡¤JÄæB¤ÎÄæC
2. ±NÄæA½Æ»s¨ìÄæB¤ÎÄæC
3. ±NÄæB¤ÎÄæC ¼ÐÃD§ï¬° xx ¤Î yy
4. §R°£ÄæB©Ò¦³°²¤éÀx¦s®æ(¤U¤èÀx¦s®æ¤W²¾)
(¥i¤â°Ê©Î°õ¦æ¤U¦CVBA Code)
Sub §R°£°²¤é()
    Dim I As Integer
    Sheets("Sheet2").Activate
    For I = 460 To 2 Step -1
        If Cells(I, 2).Font.ColorIndex = 3 Or Cells(I, 2).Font.ColorIndex = 5 Then
            Cells(I, 2).Delete Shift:=xlUp
        End If
    Next
End Sub
5. (¤â°Ê)±N ÄæC ©Ò¦³³sÄò3¤Ñ¥H¤Wªº
     Àx¦s®æ¥þ³¡§R°£(¤U¤èÀx¦s®æ¤W²¾)

¤G¡B«Ø¥ß¦WºÙ "¤W½u¤é","xx","yy"
1. ¿ï¨ú "A1:C428"
2. «ö Ctrl + Shift + F3
3. ¤Ä¿ï[³»ºÝ¦C](¨ä¾l¤£¥i¿ï)

¤T¡B±N ÄæB¤ÎÄæC °Å¤U ¶K¤W¨ì Äæx¤ÎÄæy
¥|¡B¿é¤J¤½¦¡
[B17]¤½¦¡, ¦V¤U©Ô
=IF(ISERROR(MATCH(¤W½u¤é,xx,0)),"",INDIRECT("X"&MATCH(¤W½u¤é,xx,0)-2))
[C17]¤½¦¡, ¦V¤U©Ô
=IF(ISERROR(MATCH(¤W½u¤é,xx,0)),"",INDIRECT("X"&MATCH(¤W½u¤é,xx,0)-5))
[D17]¤½¦¡, ¦V¤U©Ô
=IF(ISERROR(MATCH(¤W½u¤é,yy,0)),"",INDIRECT("Y"&MATCH(¤W½u¤é,yy,0)-13))
test.gif

¦V«e±À¤T¤Ñ.rar (114.21 KB)

TOP

¦] "«e14¤Ñªº¤ñ¸û¯S§O¡A¥²¶·­n¦³3¤Ñ§t¥H¤Wªº°²¤é¡A¦p¤»¡B¤é¡B¤@³s°²¡A
¤~·|¦©°£,   ¦pªG¨S¦³¤T¤Ñ¥H¤W¡A«h¤» ...
yen956 µoªí©ó 2015-11-27 04:53


yen956¤j¤j¡AÁÂÁ±zªºÀ°¦£¡A¬O§_¯à³Â·Ð±z±NÀɮשñ¸m¶³ºÝ¤¤¡A¤p§Ì¥Ø«e¨S¿ìªk¨Ï¥Î¤U¸üªþ¥ó¥\¯à¡AÁÂÁ¡C

TOP

¦^´_ 6# WOW9637

http://www.mediafire.com/download/qxvugva5w6qa9a9/%E5%90%91%E5%89%8D%E6%8E%A8%E4%B8%89%E5%A4%A9.rar

TOP

http://blog.xuite.net/hcm19522/twblog/360456424

TOP

·Q¨ì§ó²±¶ªº¤èªk:
1. «Ø¥ß ³s°²ªí
±NÄæA¤¤©Ò¦³³s°²¤T¤Ñ¥H¤WªºÀx¦s®æ,
¥þ³¡½Æ»s¨ìÄæG
2. ¦bModule1¿é¤J¤U¦CVBACode
  1. Function BackWardDays(ByVal Num As Integer, date2 As Date) As Date
  2.     Dim cnt As Integer, MH, sh As Object
  3.     Set sh = Sheets("Sheet1")
  4.     cnt = 0
  5.     Do
  6.         date2 = date2 - 1
  7.         MH = Application.Match(CLng(date2), sh.[G2:G31], 0)
  8.         cnt = cnt + 1
  9.         If Num < 14 Then
  10.             If Weekday(date2, vbMonday) > 5 Or IsNumeric(MH) Then cnt = cnt - 1
  11.         Else
  12.             If IsNumeric(MH) Then cnt = cnt - 1
  13.         End If
  14.     Loop Until cnt = Num
  15.     BackWardDays = date2
  16. End Function

  17. Sub ¤T¤Ñ«e()
  18.     Dim date1 As Date, sh As Object
  19.     Dim I As Integer
  20.     Set sh = Sheets("Sheet1")
  21.     sh.[B2:B400] = ""
  22.     For I = 2 To sh.[A2].End(xlDown).Row
  23.        date1 = BackWardDays(3, sh.Cells(I, 1))
  24.        sh.Cells(I, 2) = date1
  25.     Next
  26. End Sub

  27. Sub ¤»¤Ñ«e()
  28.     Dim date1 As Date, sh As Object
  29.     Dim I As Integer
  30.     Set sh = Sheets("Sheet1")
  31.     sh.[C2:C400] = ""
  32.     For I = 2 To sh.[A2].End(xlDown).Row
  33.        date1 = BackWardDays(6, sh.Cells(I, 1))
  34.        sh.Cells(I, 3) = date1
  35.     Next
  36. End Sub

  37. Sub ¤Q¥|¤Ñ«e()
  38.     Dim date1 As Date, sh As Object
  39.     Dim I As Integer
  40.     Set sh = Sheets("Sheet1")
  41.     sh.[D2:D400] = ""
  42.     For I = 2 To sh.[A2].End(xlDown).Row
  43.        date1 = BackWardDays(14, sh.Cells(I, 1))
  44.        sh.Cells(I, 4) = date1
  45.     Next
  46. End Sub
½Æ»s¥N½X
test.gif

TOP

ÁÂÁ¦U¦ìªºÀ°¦£ ·P¿E¤£ºÉ!!¡C

TOP

        ÀR«ä¦Û¦b : ¡i¥Í©R¦b©I§l¶¡¡j¦òªû»¡¡G¡u¥Í©R¦b©I§l¶¡¡C¡v¤HµLªkºÞ¦í¦Û¤vªº¥Í©R¡A§óµLªk¾×¦í¦º´Á¡AÅý¦Û¤v¥Ã¦í¤H¶¡¡C¬JµM¥Í©R¥h¨Ó³o»òµL±`¡A§Ú­Ì§óÀ³¸Ó¦n¦n¦a·R±¤¥¦¡B§Q¥Î¥¦¡B¥R¹ê¥¦¡AÅý³oµL±`¡BÄ_¶Qªº¥Í©R¡A´²µo¥¦¯uµ½¬üªº¥ú½÷¡A¬M·Ó¥X¥Í©R¯u¥¿ªº»ù­È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD