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

[µo°Ý] excel ¦Û°Ê¦X¨ÖÀx¦s®æ

[µo°Ý] excel ¦Û°Ê¦X¨ÖÀx¦s®æ

½Ð°Ý¦U¦ì¤j«e½ú
¤p§Ì§Ú¦³»s§@¤@­Ó±M®×¶i«×ªí
·Q¸ß°Ý¦U¦ì¦p¦óÅýexcel¦Û°Ê§PÂ_¦~/¤ë¥÷¨Ó¤@§Ç¦X¨ÖÀx¦s®æ©O?
¦p¤U¹Ï




¤u§@¶i«×ªí.rar (38.36 KB)

¦^´_ 1# ¬PªÅÉ@¦ÐÁl

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim Arr, xD, C%, T%, T1%
Application.DisplayAlerts = False
Set xD = CreateObject("Scripting.Dictionary")
C = Cells(5, Columns.Count).End(xlToLeft).Column
Arr = Range([e5], Cells(5, C))
For j = 1 To UBound(Arr, 2)
    T = Month(Arr(1, j))
    T1 = Split(Arr(1, j), "/")(2)
    If T1 = 1 Then
        If T = 1 Then
            Cells(4, j + 4) = "¤@¤ë"
        ElseIf T = 2 Then
            Cells(4, j + 4) = "¤G¤ë"
        ElseIf T = 3 Then
            Cells(4, j + 4) = "¤T¤ë"
        ElseIf T = 4 Then
            Cells(4, j + 4) = "¥|¤ë"
        ElseIf T = 5 Then
            Cells(4, j + 4) = "¤­¤ë"
        ElseIf T = 6 Then
            Cells(4, j + 4) = "¤»¤ë"
        ElseIf T = 7 Then
            Cells(4, j + 4) = "¤C¤ë"
        ElseIf T = 8 Then
            Cells(4, j + 4) = "¤K¤ë"
        ElseIf T = 9 Then
            Cells(4, j + 4) = "¤E¤ë"
        ElseIf T = 10 Then
            Cells(4, j + 4) = "¤Q¤ë"
        ElseIf T = 11 Then
            Cells(4, j + 4) = "¤Q¤@¤ë"
        ElseIf T = 12 Then
            Cells(4, j + 4) = "¤Q¤G¤ë"
        End If
    End If
    If xD.Exists(T) Then
        Set xD(T) = Union(xD(T), Cells(4, j + 4))
    Else
        Set xD(T) = Cells(4, j + 4)
    End If
Next
For Each ky In xD.keys
    xD(ky).Merge
Next
Application.DisplayAlerts = True
End Sub

TOP

¦^´_ 2# samwang

§ó·s¤@¤U¦X¨Ö«á¸m¤¤¦p¤U¡AÁÂÁÂ
For Each ky In xD.keys
    xD(ky).Merge
    xD(ky).HorizontalAlignment = xlCenter   '¸m¤¤
Next

TOP

¦^´_ 2# samwang

·PÁ«e½ús¤jªº¦^ÂÐ
§Ú¥ýªáÂI®É¶¡²z¸Ñ¤Î´ú¸Õ
«áÄò¦³°ÝÃD¦A¸ò«e½ú¦^³ø¡C

TOP

¦^´_ 3# samwang


¦nªº¡AÁÂÁÂS¤j¡I
:D

TOP

¦^´_ 4# ¬PªÅÉ@¦ÐÁl


³B²z¹Lµ{¤j­P¦p¤U:
1. ®Ú¾Ú²Ä5¦C¸ê®Æ¡A¦b²Ä4¦C¦ì¸m·|¦Û°Ê¶ñ¤J¤ë¥÷¡A¦p¤@¤ë¡B¤G¤ë...
2. ¦X¨Ö¦P¤ë¥÷¡A®Ú¾Ú²Ä5¦C¸ê®Æ

TOP

¦^´_ 6# samwang

S¤j
¥Ø«e´ú¸Õ¤U¨Ó¡A¦]±M®×±Ò©lªº¤é´Á·|§ïÅÜ
´ú¸Õ«á¦³¤@°ÝÃD
¤ë¥÷¦X¨Ö·|¥X²{¸ó¤ëªº±¡ªp (¦pªþ¹Ï)


½Ð°Ý¦p¦ó§ïµ½©O¡H

¥t¥~¤p§Ì§Ú¨S¦³VBAªº°ò¦
©Ò¥H¥Ø«e·|¦Û¦æ¤Wºô¬d¾\¤F¸ÑS¤j±zªº¥N½X¼gªk
·Q¥t¥~½Ð£¸­Ó°ÝÃD
1.­Y·Q¨Ì±ø¥ó¦Û°Ê¦X¨ÖÀx¦s®æ¡A³æ¯Âªº¨ç¼Æ¬O§_µLªk¹F¦¨¡H

¥H¤W¡A¦A³Â·ÐS¤j«ü±Ð¡AÁÂÁ¡I

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-11-29 14:00 ½s¿è

¦^´_ 1# ¬PªÅÉ@¦ÐÁl


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
ÁÂÁ samwang«e½ú´£¨Ñ¸Ñ¨M¤èªk»P¸Ñ»¡,«á¾Ç¾Ç²ß«á¥Î¬Û¦P¤èªk,¤£¦Pªº³¯­z¤è¦¡½m²ß°}¦C»P¦r¨å
¤ß±oµù¸Ñ¦p¤U,½Ð¦U¦ì«e½ú¦A«ü¾É

Option Explicit
Sub TEST()
Application.DisplayAlerts = False
Dim Brr, C&, x, V, xD, Sh, Tym$, mm&
'¡ô«Å§iÅܼÆ
Set Sh = Sheets("P-012-02A-¹w©w¤u§@¶i«×ªí")
'¡ô¥OSh ¬O¤u§@ªí(¥H¤UºÙ:¶i«×ªí),¦WºÙ¬O "P-012-02A-¹w©w¤u§@¶i«×ªí"
Set xD = CreateObject("Scripting.Dictionary")
'¡ô¥OxD ¬O¦r¨å
C = Sh.UsedRange.EntireColumn.Columns.Count
'¡ô¥OC ¬O¦³¨Ï¥ÎÀx¦s®æ½d³òªº³Ì¤jÄæ¦ì¼Æ
Brr = Sh.Range(Sh.Cells(5, 1), Sh.Cells(5, C))
'¡ô¥OBrr ¬O¤Gºû°}¦C!­Ë¤J±q¶i«×ªí[A5]¨ì²Ä¤­¦C³Ì«á¤@ÄæÀx¦s®æ½d³òÀx¦s®æ­È
For x = 1 To UBound(Brr, 2)
'¡ô³]¶¶°j°é±q1¨ìBrr°}¦C¾î¦VÄæ¦ìªº³Ì¤jÄæ¦ì¼Æ
   If IsDate(Brr(1, x)) Then
   '¡ô¦pªG°j°é°}¦C­È¬O¤é´Á??
      Tym = Format(Brr(1, x), "yyyy/mm")
      '¡ô¦pªGIf±ø¥ó¦¨¥ß!¥OTym¦r¦êÅÜ¼Æ ¬O°j°é°}¦C­ÈÂà¤Æ¬° ¥|½X¦~¥[ "/" ²Å¸¹¥[ ¨â½X¤ëªº¦r¦ê
      If xD.Exists(Tym) = Empty Then
      '¡ô¦pªG¥Î Tym¦r¦êÅܼƬd¹î¦r¨å¸Ì¬Oªì©l­È
         Set xD(Tym) = Sh.Cells(4, x)
         '¡ô¥OTym¦r¦êÅܼƬO xD¦r¨å¸Ìªºkey,Item¬O¶i«×ªí²Ä4¦C.°j°éÄ檺Àx¦s®æ
         Else
         Set xD(Tym) = Union(xD(Tym), Sh.Cells(4, x))
         '¡ô¥OTym¦r¦êÅܼƬO xD¦r¨å¸Ìªºkey,
         'Item¬O­ì¥»Item¸ÌªºÀx¦s®æ¦A¥[¤J ¶i«×ªí²Ä4¦C.°j°éÄ檺Àx¦s®æªº Àx¦s®æ¶°

      End If
   End If
Next
V = Split(",¤@,¤G,¤T,¥|,¤­,¤»,¤C,¤K,¤E,¤Q,¤Q¤@,¤Q¤G", ",")
'¡ô¥OV¬O¦r¦ê¥Î ","²Å¸¹¤À³Îªº¤@ºû°}¦C
For Each x In xD.Keys
'¡ô³]¶¶°j°é!¥Ox¬O xD¦r¨åKeysªº¤@­û
   xD(x).UnMerge
   '¡ôxD¦r¨å¸Ì°j°éKey¹ïÀ³ªº Àx¦s®æ¶°¨ú®ø¦X¨ÖÀx¦s®æ
   xD(x).Merge
   '¡ôxD¦r¨å¸Ì°j°éKey¹ïÀ³ªº Àx¦s®æ¶°°õ¦æ¦X¨ÖÀx¦s®æ
   xD(x).HorizontalAlignment = xlCenter
   '¡ôxD¦r¨å¸Ì°j°éKey¹ïÀ³ªº Àx¦s®æ¶°¤å¦r¥ª¥k¸m¤¤
   mm = Split(x, "/")(1)
   '¡ô¥Omm&¼Æ¦rÅܼƬOx¥Î "/"²Å¸¹©î¸Ñªº¤@ºû°}¦C!¨ú²Ä 1­Ó¦r¦êÂন¼Æ¦r(¤ë¥÷)
   '¦]¬°¦¹¤@ºû°}¦C¤¸¯À¬O¦r¦ê,¦]«Å§i¬Omm¼Æ¦r!©Ò¥H¦r¦ê·|Åܦ¨¼Æ¦r
   'ps:¦¹¤@ºû°}¦Cªº²Ä0­Ó¦r¦ê¬O(¥|½X¦~¤À)
   xD(x)(1) = V(mm) & "¤ë"
   '¡ôxD¦r¨å¸Ì°j°éKey¹ïÀ³ªº Àx¦s®æ¶°²Ä¤@®æ¶ñ¤J¤å¦r:
   '¤å¦r¬O¥Îmm¼Æ¦r§ì¨ú V¤@ºû°}¦C¸Ì¹ïÀ³ªº°ê¦r¼Æ¦r¦ê,«á­±¦A¥[ "¤ë"¦r

Next
Set Brr = Nothing
Set xD = Nothing
End Sub

TOP

¦^´_  samwang

S¤j
¥Ø«e´ú¸Õ¤U¨Ó¡A¦]±M®×±Ò©lªº¤é´Á·|§ïÅÜ
´ú¸Õ«á¦³¤@°ÝÃD
¤ë¥÷¦X¨Ö·|¥X²{¸ó¤ëªº±¡ªp ...
¬PªÅÉ@¦ÐÁl µoªí©ó 2022-11-29 13:52


½Ð´£¨Ñ¦³°ÝÃDÀɮסA³o¼Ë¤ñ¸û®e©ö´ú¸Õ¡AÁÂÁÂ

­Y·Q¨Ì±ø¥ó¦Û°Ê¦X¨ÖÀx¦s®æ¡A³æ¯Âªº¨ç¼Æ¬O§_µLªk¹F¦¨¡H³o«á¾Ç¤£ª¾¹D¡A¥i¥H¦A¬Ý¬Ý¨ä¥L¤j¤j

TOP

¦^´_ 9# samwang

S¤j
¥H¤U¬°¦³°ÝÃDªºÀÉ®×
¤u§@¶i«×ªí-´ú¸Õ¥¨¶°.rar (47.88 KB)
   
¥t¥~
¤é´Áªº²£¥Í¬O
°£¤F°_©l¤é´Á(²Ä¤@¤Ñ)¬O¤â°Ê¶ñ¤J¥H¥~
¨ä¾l¬O¥Î+1ªº¤è¦¡¨Ó§PÂ_«áÄò¤é´Á¡C
¦A³Â·ÐS¤j°£¿ù¤F¡A·PÁ¡C

TOP

        ÀR«ä¦Û¦b : µoµÊ®ð¬Oµu¼ÈªºµoºÆ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD