quickfixer

¦^´_ 10# olisun

°}¦C¤½¦¡
e2    =IF(AND(A2<>"",C2<>""),(A2&REPT(" ",MAX(LEN($A$2:$A$1000))-LEN(A2)) & IF(B2=""," ","+") & B2 & REPT(" ",MAX(LEN($B$2:$B$1000))-LEN(B2)+1) & C2),"")

¤U¸ü (16.91 KB)

2022-3-31 23:28

Andy2483

¦^´_ 1# olisun


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò,ÁÂÁ¦U¦ì«e½ú«ü¾É
«á¾ÇÂǦ¹©«²ß±o«Ü¦hª¾ÃÑ»P¸gÅç,¥Î¥¬ªLÅܼƾã¦X°j°é,»{ÃÑRept()¹Bºâ¦¡.....
¥H¤U°õ¦æ¹Lµ{»P¤ß±oµù¸Ñ½Ð¦U¦ì«e½ú«ü¾É

°õ¦æ«e:

¤U¸ü (45.41 KB)

2022-12-28 15:11

°õ¦æµ²ªG:

¤U¸ü (53.95 KB)

2022-12-28 15:12

Option Explicit
Sub TEST_20221228_2()
Dim Arr, i&, j&, S&, A&(3), B$(3), T As Boolean
'¡ô«Å§iÅܼÆ:Arr¬O³q¥Î«¬ÅܼÆ,(i,j,S)¬Oªø¾ã¼ÆÅܼÆ,(A,B)¬O¤@ºû°}¦C,T¬O¥¬ªLÅܼÆ
Arr = Range([C2], [A65536].End(3))
'¡ô¥OArr¬O¤Gºû°}¦C!¥H[C2]¨ìAÄæ³Ì«á¦³¤º®eÀx¦s®æ­È­Ë¤J
Head:
S = IIf(T, 1, 3)
'¡ô¥OS³oªø¾ã¼ÆÅÜ¼Æ¨Ì IIf()§PÂ_TÅܼƬOTure:S=1,TÅܼƬOFalse:S=3
For i = 1 To S
'¡ô³]¶¶°j°é!i±q1¨ìSÅܼÆ
   For j = 1 To UBound(Arr)
   '¡ô³]¶¶°j°é!j±q1¨ìArr°}¦CÁa¦V³Ì¤j¦C¯Á¤Þ¸¹
      If T Then
      '¡ô¦pªGTÅܼƬO Ture     PS:T¬O ¥¬ªLÅܼÆ,¨äªì©l­È¬OFalse
         B(1) = Arr(j, 1) & Application.Rept(" ", A(1) - Len(Arr(j, 1)))
         '¡ô¥O¯Á¤Þ¸¹1ªºB°}¦C­È¬O j°j°é¦C²Ä1ÄæArr°}¦C­È³s±µ ¼Æ­Ó" "ªÅ¥Õ¦r¤¸,
         '¼Æ­Ó?:¥HRept()¹Bºâ¦¡½Æ»s(¯Á¤Þ¸¹1ªºA°}¦C­È- j°j°é¦C²Ä1ÄæArr°}¦C­Èªº¦r¼Æ)­Ó" "ªÅ¥Õ¦r¤¸
         'https://learn.microsoft.com/zh-tw/office/vba/api/excel.worksheetfunction.rept
         B(2) = Arr(j, 2) & Application.Rept(" ", A(2) - Len(Arr(j, 2)))
         '¡ôÃþ±À
         Arr(j, i) = B(1) & "+" & B(2) & " " & Arr(j, 3)
         '¡ô¥Oj°j°é¦Ci°j°éÄæArr°}¦C­È¬O ¤T­ÓB°}¦C­È³s±µ"+"©M" "²Å¸¹ªº¦r¦ê
         ElseIf A(i) < Len(Arr(j, i)) Then
         '¡ô§_«h¦pªGi°j°éA°}¦C­È < j°j°é¦Ci°j°éÄæArr°}¦C­È¸Ìªº¦r¼Æ??
            A(i) = Len(Arr(j, i))
            '¡ô¥Oi°j°éA°}¦C­È¬O j°j°é¦Ci°j°éÄæArr°}¦C­È¸Ìªº¦r¼Æ
            '(¨D³Ì¤j¦r¼Æ)
      End If
   Next
Next
If T = False Then T = True: GoTo Head
'¡ô¦pªGTÅܼƬO False,´NÅýTÅܼƬO True,µ{§Ç¸õ¨ì Head¼Ð¥Ü³BÄ~Äò°õ¦æ
[H2].Resize(UBound(Arr), 1) = Arr
'¡ô¥O[H2]ÂX®i¦V¤UArr°}¦CÁa¦V³Ì¤j¦C¯Á¤Þ¸¹¼Æ,¦V¥k¤£ÂX®i,³oÄæÀx¦s®æ­È¥HArr°}¦C­È­Ë¤J
Set Arr = Nothing
Erase A, B
End Sub

Andy2483

¦^´_ 12# Andy2483


    ½m²ß§ï¬°¦Û­q¨ç¼Æ,½Ð¦U¦ì«e½ú«ü¾É,ÁÂÁÂ
[I2]Àx¦s®æ¿é¤J:    =¤TÄæ¦X¨Ö¥H¦r²Å³s±µ¨Ã¹ï»ô("="," ",A2:C2,A:C)

¤U¸ü (91.19 KB)

2022-12-29 14:58

I2¤U©Ô¦ÜI6:

¤U¸ü (90.52 KB)

2022-12-29 14:59

Option Explicit
Function ¤TÄæ¦X¨Ö¥H¦r²Å³s±µ¨Ã¹ï»ô(X As String, Y As String, Z As Range, Area As Range)
'¡ô¦Û­q¨ç¼Æ:¤TÄæ¦X¨Ö¥H¦r²Å³s±µ¨Ã¹ï»ô(²Ä¤@­Ó³s±µ¦r²Å,²Ä¤G­Ó³s±µ¦r²Å,³s±µÀx¦s®æ,¹ï»ô°Ñ·ÓÀx¦s®æ)
Dim Arr, i&, j&, S&, A&(2), B$(2), T As Boolean
Arr = Area
Head:
S = IIf(T, 1, 2)
For i = 1 To S
   For j = 1 To UBound(Arr)
      If Arr(j, 1) = "" Then Exit For
      If T Then
         B(1) = Arr(j, 1) & Application.Rept(" ", A(1) - Len(Arr(j, 1)))
         B(2) = Arr(j, 2) & Application.Rept(" ", A(2) - Len(Arr(j, 2)))
         Arr(j, i) = B(1) & "+" & B(2) & " " & Arr(j, 3)
         ElseIf A(i) < Len(Arr(j, i)) Then
            A(i) = Len(Arr(j, i))
      End If
   Next
Next
If T = False Then T = True: GoTo Head
¤TÄæ¦X¨Ö¥H¦r²Å³s±µ¨Ã¹ï»ô = Arr(Z.Row, 1)
Set Arr = Nothing
Erase A, B
End Function