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

VBA ¥[Á`¼Æ­È

VBA ¥[Á`¼Æ­È

§A¦n¡A½Ð°Ý¦U¦ì¤j¤j

¥Ø«e´X­Ó°ÝÃD¡A·Q½Ð±Ð¦U¦ì¤j¤jªºÀ°
1¡B¦p¦ó³z¹LVBA±N¦r¦ê201101010001´£¥X«e¤K­Ó¸¹½XµM«á¥[¤W2011/01/01Åܦ¨¤é´Á
2¡B±N¦P¤@¤Ñ¤é´Á©Ò¨Ï¥Îªº¼Æ¶q°µ¼Æ¶qªº¥[Á`

§Úª¾¹D¦p¦ó¥Î¤â¤u¾Þ§@±±¨î¡A¦ý¬O¼Æ¶q¤ÓÃe¤j¨S¿ìªk¤@­Ó¤@­Ó¥Î¡A©Ò¥H·Q­n³z¹LVBA°õ¦æ

«ô°U¦U¦ì¯«¤HÀ°À°¦£~~~~~

PS.¥Ñ©ó·|­ûÁÙ¬O¤p¾Ç¥Í¡A¤£¯à¤U¸üÀɮסA¥i§_±Nµ{¦¡½X¶K¦b¯d¨¥¤W¡A¤£¦n·N«ä!!!!

³] 201101010001 ¦b A2
B2¤½¦¡
=DATE(LEFT($A2,4),MID($A2,6,2),MID($A2,8,2))
¤é´Á®æ¦¡³]¬°
yyyy/mm/dd

TOP

¦^´_ 1# 518587
  1. Sub ex()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. For Each a In Range([A1], [A1].End(xlDown)) '¸ê®Æ¦AAÄæ
  4.    d(Format(Mid(a, 1, 8), "0000/00/00")) = d(Format(Mid(a, 1, 8), "0000/00/00")) + Val(Mid(a, 9))
  5. Next
  6. [D1].Resize(d.Count, 1) = Application.Transpose(d.keys) '¤é´Á©ñ¨ìDÄæ
  7. [E1].Resize(d.Count, 1) = Application.Transpose(d.items) '¼Æ¶q¥[Á`©ñ¨ìEÄæ
  8. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 3# Hsieh


    §A¦n¡A·PÁ±zªºÀ°§U¡A:)

¦ý¬O¦³¨Ç¤p°ÝÃD·Q½Ð±Ð±z¡A
[M1].Resize(d.Count, 1) = Application.Transpose(d.items) '¼Æ¶q¥[Á`©ñ¨ìEÄæ
³o­Ó¬O¥[Á`­þ¸Ìªº¼Æ¶q?!

¹Ï¤ù¬O§Úªº¸ê®Æ¡A¦ý¬O§Úµo²{¤@­Ó°ÝÃD´N¬O¤é´Á¨S¦³³sÄò
¥i¯à¦]¬°¸ê®Æ¤¤¦³¨Ç¤é´Á¨S¦³¡A¦pªG­n¥[¤W¤é´Á¥i¥H³sÄò´Nºâ¨S¦³¸ê®Æ¤]¥i¥H¥X²{¤é´Á¶Ü?!

·s¹Ï¤ù.jpg (148.31 KB)

·s¹Ï¤ù.jpg

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-4-1 06:16 ½s¿è

¦^´_ 4# 518587
d(Format(Mid(a, 1, 8), "0000/00/00")) = d(Format(Mid(a, 1, 8), "0000/00/00")) + Val(Mid(a, 9))
¬O¤é´Áªº¥[Á`
¥i¯à¦]¬°¸ê®Æ¤¤¦³¨Ç¤é´Á¨S¦³¡A¦pªG­n¥[¤W¤é´Á¥i¥H³sÄò´Nºâ¨S¦³¸ê®Æ¤]¥i¥H¥X²{¤é´Á¶Ü?!
¬Ý¤£À´
  1. Option Explicit  'BÄæ¼Æ¶qªº¥[Á`
  2. Sub Ex()
  3.     Dim D As Object, A As Range, K As Long, I As Long
  4.     Set D = CreateObject("Scripting.Dictionary")    '¦r¨åª«¥ó
  5.     For Each A In Range([A1], [A1].End(xlDown))     '¸ê®Æ¦bAÄæ
  6.         D(Val(Mid(A, 1, 8))) = D(Val(Mid(A, 1, 8))) + A.Offset(, 1)  '¼Æ¶q¦bBÄ檺¥[Á`
  7.         'Val(Mid(A, 1, 8)) Âର¼Æ¦r
  8.     Next
  9.     [D:E].Clear
  10.     For I = 1 To D.Count  '³Ì¤p­È¨ì³Ì¤j­È
  11.         K = Application.WorksheetFunction.Small(D.KEYS, I)
  12.         [D1].Offset(I - 1) = Format(K, "0000/00/00")  'Âର¤é´Á®æ¦¡
  13.         [E1].Offset(I - 1) = D(K)                '¼Æ¶q¥[Á`

  14.     Next
  15. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 4# 518587
  1. Sub ex()
  2. Dim k#
  3. Set d = CreateObject("Scripting.Dictionary")
  4. For Each a In Range([A1], [A1].End(xlDown)) '¸ê®Æ¦AAÄæ
  5. k = DateValue(Format(Mid(a, 1, 8), "0000/00/00"))
  6.    d(k) = d(k) + a.Offset(, 1) '¼Æ¶q¦bBÄæ
  7. Next
  8. [D:E] = "" '²MªÅ¸ê®Æ
  9. r = 1
  10. For i = Application.Min(d.keys) To Application.Max(d.keys) '³Ì¤p¤é´Á¨ì³Ì¤j¤é´Á¼g¤J
  11. Cells(r, 4) = CDate(i)
  12. Cells(r, 5) = d(i)
  13. r = r + 1
  14. Next
  15. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 5# GBKEE


    ª©¤j§A¦n¡A·PÁ±z´£¨Ñªº¤èªk¤]ÁÂÁÂÄ@·N´£¨ÑÀ°§U

ªþ¤W¹Ï¤ù¡ADÄæ»PEÄæ¬O¥Ñª©¤j´£¨Ñªºµ{¦¡½X©Ò¶]¥X¨Óªºµ²ªG
¦ý¬ODÄæ¨S¦³¥]§t©Ò¦³ªº¤é´Á¡A¤ñ¹ïGÄæ·|µo²{DÄæ¤é´Á¤£¬O§¹¾ãªº¡A¦³¯Ê¤Ö´X¤Ñ

·s¹Ï¤ù.jpg (173.29 KB)

·s¹Ï¤ù.jpg

TOP

¦^´_ 6# Hsieh


·PÁ¤j¤jªºÀ°¦£¡A¦ý¬O³o¬qµ{¦¡½X¡A
µLªk°õ¦æ¡Aµo¥Í«¬ºA¤£²Å¦Xªºª¬ªp

¸ÕµÛ­n§ä¥X°ÝÃD¡A¦ý¬OÁÙ¬OµLªk§ä¥X¨ì©³¬O­þ¸Ì«¬ºA¿ù»~...
¹ï¤£°_¡A¥u·|³æ¯Âªì¶¥ªºVBA»yªk

TOP

¦^´_ 7# 518587
½ÐªþÀɬݬÝ
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 8# 518587

¤W­±ªºµ{¦¡·|¥X¿ù¡A¤j·§¬O¤é´Á³¡¤À
µ{¦¡¬O¥ÑA1¶}©l¡A¦]¬°§Aªº¸ê®Æ¦³¼ÐÃD
±N½d³ò±qA2¶}©lÀ³¸Ó´N¥i¦æ
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¨ü¤HÂI¤ô¤§®¦¡A¶··í´é¬u¥H³ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD