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

[µo°Ý] EXCEL¬O§_¥i¦Û°Ê±N­È±a¤J¼Æ¶q&¤é´Á

[µo°Ý] EXCEL¬O§_¥i¦Û°Ê±N­È±a¤J¼Æ¶q&¤é´Á


¦p¹Ï¡A§Æ±æ¥Ñ¤W­±ªº³Æµù.xlsªºCÄæ&LÄæ¡A¥ÎAÄæ¥h¤ñ¹ï¥»¤å.xls¤¤ªº¼Æ¶q»P¤é´Á¡A±N¨ä±a¤J¦UÄæ¦ì¤¤
¨Ò¦p5¤ë20¤é:¤é´Á«h¥]§t5/20~5/27ªº©Ò¦³¸ê®Æ³£±a¤J¼Æ¶q&¤é´Á¤¤

¤£ª¾¹Dª©¤W¦Ñ®v­Ì¬O§_¦³¸Ñªk¥i±Ð¾Ç¡A¤p§Ì¹ïVBA§¹¥þ¤£¼ô¡A¤@¯ëEXCEL¨ç¼Æ¦n¹³¤]¨S¿ìªk¹F¨ì³o­Ó¥Øªº><

2items.rar (78.29 KB)

¥»©«³Ì«á¥Ñ luhpro ©ó 2016-5-18 22:49 ½s¿è

¦^´_ 1# abc9gad2016

¬Ý¤£À´§AªºÅÞ¿è­C.

GVHMCWY00 ¦b 105-06-08 ªº¸ê®Æ¬O¨Ì¾Ú¤°»òÅÞ¿è­n©ñ¦b 5 ¤ë 27 ¤é ªºÄæ¦ì¤º?
°£¤F¤é´Á¥~ªº¨ä¥L¼Æ¦r­þ¨Ç¬O­n±a¤J©Î¬O­pºâªº¤]¬Ý¤£¥X¨Ó,
Ãø¹D¥u­n±a¤J 1 ­Ó¼Æ¦r,
±a¤J«á¨ä¥L¼Æ¦r¤£·|¦³ÅܤÆ(¤£»Ý­n­pºâ)¶Ü?

¬Y¨Ç§PÂ_ªº¨Ì¾Ú¤£¯àÀH·N®³±¼,
§_«hµL±q§PÂ_°_.
¬O§_¯à¦A±Ô­z²M·¡¤@¨Ç?

«Øij¦Ü¤Ö­nÁ|¨Ò 3 µ§ ¥H¤W¦³¥Nªí©Êªº¸ê®Æ,
¼ÒÀÀ¹ê»Ú±a¤Jªº­ì¦]©Î¤è¦¡¨Ã¥[¥H»¡©ú,

¨Ò¦p§ä¨ì­þ­Ó(©Î¬O¹M¾ú©Ò¦³)¸ê®Æ,
¨Ì¾Ú¬Æ»òÅÞ¿è©Î±ø¥ó,
©Ò¥H­n½Æ»s¨ì­þ¸Ì,
­þ¨Ç¼Æ¦r­n«ç»ò­pºâ...µ¥µ¥.

TOP

¦^´_ 2# luhpro

Hi¤j¤j¡AGVHMCWY00À³¸Ó¬O­n©ñ¦b6/3¨º¶g¡A¦]¬°§Ú¥u·|«Ü²Âªº¥Î¥»¤åCÄæ¥hVLOOKUP³Æµù¤ºªºCÄæ¤é´Á¸òLÄæ¥[Á`¥¼¥æ

§Ú¬O¥ý§â¥L¥þ³¡V¨ì5/20¨º¶g¡A¦b¤â°Ê¥h½Õ¾ã¶g§O¡A¦ý¬Oµo²{¸ê®Æ¤Ó¦h³o¼Ë°µ¦³°ÝÃD>"<

TOP

¦^´_ 3# abc9gad2016
¸Õ¸Õ¬Ý:
  1. Sub nn()
  2.   Dim iCol%
  3.   Dim lRC&, lRow&, lRows&
  4.   Dim sPath$, sStr$
  5.   Dim bNFind As Boolean, bCor As Boolean
  6.   Dim dDte As Date
  7.   Dim vdPdt, vdDte
  8.   Dim wbSou As Workbook, wsSou As Worksheet, wsTar As Worksheet
  9.   
  10.   sPath = ThisWorkbook.Path
  11. ChDrive sPath
  12. ChDir sPath
  13.   Set vdPdt = CreateObject("Scripting.Dictionary")
  14.   Set vdDte = CreateObject("Scripting.Dictionary")
  15.   Set wsTar = Workbooks("¥»¤å.xls").Sheets(1)
  16.   
  17.   bNFind = True
  18.   For Each wbSou In Workbooks
  19.     If wbSou.Name = "³Æµù.xls" Then
  20.       bNFind = False
  21.       Exit For
  22.     End If
  23.   Next
  24.   
  25.   If bNFind Then
  26.     Set wsSou = Workbooks.Open("³Æµù.xls").Sheets(1)
  27.   Else
  28.     Set wsSou = wbSou.Sheets(1)
  29.   End If
  30.    
  31.   With wsTar
  32.     .Activate
  33.     lRC = 3
  34.     While .Cells(lRC, 3) <> ""
  35.       vdPdt(CStr(.Cells(lRC, 3))) = lRC
  36.       lRC = lRC + 1
  37.     Wend
  38.     lRows = lRC ' °O¿ý³Ì«á¦C¸¹
  39.    
  40.     lRC = 3
  41.     bCor = True
  42.     dDte = #5/20/2016#
  43.     While lRC <= Columns.Count - 7
  44.       If Weekday(dDte) = 6 Then
  45.         lRC = lRC + 4
  46.         With .Cells(1, lRC)
  47.           With .Offset(1).Offset(, 1)
  48.             .Value = "¼Æ¶q"
  49.             .Interior.ColorIndex = 35
  50.           End With
  51.           With .Offset(1).Offset(, 2)
  52.             .Value = "¤é´Á"
  53.             .Interior.ColorIndex = 35
  54.           End With
  55.           .Resize(, 4).Merge
  56.           .Value = dDte
  57.           .NumberFormat = "m" & Chr(34) & "¤ë" & Chr(34) & "d" & Chr(34) & "¤é" & Chr(34) & ";@"   ' m"¤ë"d"¤é";@
  58.           If bCor Then .Resize(Rows.Count, 4).Interior.ColorIndex = 35
  59.           bCor = Not bCor
  60.         End With
  61.         vdDte(CStr(dDte)) = lRC
  62.       Else
  63.         vdDte(CStr(dDte)) = lRC
  64.       End If
  65.       dDte = dDte + 1
  66.     Wend
  67.   End With
  68.   
  69.   With wsSou
  70.     lRC = 8
  71.     While .Cells(lRC, 1) <> ""
  72.       sStr = CStr(.Cells(lRC, 1))
  73.       lRow = vdPdt(sStr)
  74.       If lRow = 0 Then
  75.         wsTar.Cells(lRows, 3) = sStr
  76.         vdPdt(sStr) = lRows
  77.         lRow = lRows
  78.         lRows = lRows + 1
  79.       End If
  80.       sStr = .Cells(lRC, 3)
  81.       sStr = Left(sStr, 3) + 1911 & "/" & CInt(Mid(sStr, 5, 2)) & "/" & CInt(Right(sStr, 2)) ' 2016/6/1
  82.       iCol = vdDte(sStr) + 1
  83.       If iCol <> 0 Then
  84.         wsTar.Cells(lRow, iCol) = .Cells(lRC, 9) ' ¼Æ¶q
  85.         wsTar.Cells(lRow, iCol + 1) = .Cells(lRC, 3) ' ¤é´Á
  86.       End If
  87.       lRC = lRC + 1
  88.     Wend
  89.   End With
  90. End Sub
½Æ»s¥N½X
¥»¤å.zip (54.86 KB)

TOP

¦^´_ 4# luhpro

¤£¦n·N«ä¡A§Ú¥Î¤F¤j¤jµ¹ªº¥¨¶°¥hRUN¡AÂà¤F«Ü¤[¡A³Ì«á¸ê®ÆÁÙ¬O¨S¦³ÅÜ°Ê
¤£ª¾¹D¬O¤£¬O§Ú¤Ó²Â¤F¡A¹ïVBA¤@¬¤£³q¤£ª¾¹D¦p¦ó¨Ï¥Î

TOP

¦^´_ 5# abc9gad2016
§A¬O¥Î§A´£¨ÑªºÀɮרӶ]ªº¶Ü?(PS. ÀɦW­n§¹¥þ¬Û¦P, ¥B­n©ñ¦b¦P¤@­Ó¥Ø¿ý¤U)
§Ú¬O¥H§A´£¨ÑªºÀɮפº®e¨Ó·Qµ{¦¡,
¹ê»Ú¶]¹L¨S°ÝÃD¤~¶K¥X¨Óªº³á.

TOP

¦^´_ 6# luhpro

¤j¤j¡A§Úªº¨BÆJ±N§Aªº¥»¤å.zip¥´¶}¡A¦A§â³Æµù¥á¶i¥h¥»¤å¸ê®Æ§¨

¤§«á¶}¥»¤åªºÀɮסA¥´¶}¥¨¶°sheet.nn¡A¶]¥X¨ÓÀɮרS¦³°Ê¡A¬O§Ú­þÃä°µ¿ù¤F¶Ü

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2016-5-26 22:23 ½s¿è

¦^´_ 7# abc9gad2016
°õ¦æ«e :


°õ¦æ«á :


§A°Ñ¦Ò¤@¤U¤W¤èªºªþ¹Ï¤º®e,
§Ú¬O¥H ¥»¤å.xls Àɮפ¤ªº C Äæ(½s¸¹) »P ¤é´Á(±q G Äæ¶}©l) °µ ¸ê®Æ¼Ðªº ¦ì¸mªº°ò·Ç,
(5¤ë20¤é ¥H«eªº¸ê®Æ·|¶]¨ì A, B Äæ, ³o³¡¤À§Ú¨S°µ°w¹ï³B²z, ½Ðª½±µ©¿²¤µ²ªG)
¦]¬°§Aªº ¥»¤å.xls »P ³Æµù.xls ¤¤ªº¸ê®Æ¦³º¡¦hªº®t²§,
©Ò¥H¹ê»Ú¤W·|¦³¤@°ï·s¼W½s¸¹¥X²{,
¥¦­Ì³£¬O·s¼W¨ì³Ì©³¤U,
§A°Ñ¦Ò¬Ý¬Ý.

µù :
§A¥i¥H¦b²Ä 69 ¦æ¤U¤¤Â_ÂI, («e­±³£¬O«Ø¥ß¯Á¤Þ, ¦¹¦æ¤§«á¤~¯u¥¿©ñ¤J¸ê®Æ)
µM«á¨Ì¦¸«ö F8 ³æ¨B°õ¦æ½T»{µ²ªG.

TOP

¦^´_ 8# luhpro

¸U¤Àªº·PÁ«e½ú«ü¾É!

TOP

        ÀR«ä¦Û¦b : ¬°¤H³B¥@­n¤p¤ß²Ó¤ß¡A¦ý¤£­n¡u¤p¤ß²´¡v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD