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

Åã¥Ü¼Ð°O¸ê®Æ

Åã¥Ü¼Ð°O¸ê®Æ

½Ð°Ý¦U¦ì¥ý¶i¡A¦p¦ó©ósheet2 ³v¤@±N°ÊºAÄ椤¦³¼Ð¥Ü¡­ªº©m¦WÅã¥Ü¥X¨Ó¡AÁÂÁ¡I Åã¥Ü¼Ð°O¸ê®Æ.rar (5.51 KB)

³oºØªí®æ, «Øij¥ÎVBA¸Õ¸Õ¡I

Sub TEST()
Dim R&, x&, y&, T$, xR As Range, xH As Range
[Sheet2!A:C].ClearContents
Set xH = [Sheet2!A1]
xH.Resize(1, 3) = Array("¼Ó¼h", "§Ç", "©m¦W")
R = Sheets("Sheet1").UsedRange.Rows.Count
For x = 2 To 14 Step 6
¡@¡@For y = 4 To R
¡@¡@¡@¡@Set xR = [Sheet1!A1].Cells(y, x)
¡@¡@¡@¡@If xR = 1 Then T = xR(1, 0) & ""
¡@¡@¡@¡@If xR(1, 3) = "¡­" Or xR(1, 4) = "¡­" Then
¡@¡@¡@¡@¡@Set xH = xH(2)
¡@¡@¡@¡@¡@xH.Resize(1, 3) = Array(T, xR, xR(1, 2))
¡@¡@¡@¡@End If
¡@¡@Next y
Next x
End Sub

TOP

¦^´_ 2# ­ã´£³¡ªL


    ·PÁ­ã¤jªº«ü±Ð,¥Î¨ç¼Æ¤½¦¡¤£ª¾¥i§_,ÁÂÁÂ!!

TOP

¦^´_ 3# shan0948


Sheet2¡D¢Ï¢°¡D°}¦C¤½¦¡¡G¥u¨ú¡e©m¦W¡f
=INDIRECT(TEXT(RIGHT(SMALL(IF((Sheet1!D$3:P$3="°ÊºA")*((Sheet1!E$4:Q$36="¡­")+(Sheet1!D$4:P$36="¡­")),COLUMN(C:O),99)*100001+ROW($4:$36)*100,ROW(A1)),5),"""sheet1!""\R0C00"),)&""
¡@
¡Õ°}¦C¤½¦¡¿é¤Jªk¡Ö¡@
¿é¤J©Î­×§ï¤½¦¡§¹¦¨«á¡A´å¼Ð¯d¦b¡e½s¿è¦C¡f¡A¥ý¤£­n«öEnter¡A
¦P®É«ö¦í¡eShift + Ctrl¡f¤£©ñ¡A¦A«ö¡eEnter¡f¡C

TOP

¦^´_ 4# ­ã´£³¡ªL


    ­ã¤j¦n,¸g«ü¾Éªº¨ç¼Æ¤½¦¡¦b´ú¸Õ«á,¥i¥HÅã¥Ü¼Ð°O¤§¸ê®Æ,¤£¹L¤½¦¡¦³ÂIÁcÂø,»Ý­n¤@ÂI®É¶¡®ø¤Æ,ÁÂÁ·P®¦!!

TOP

¦^´_ 2# ­ã´£³¡ªL
½Ð±Ð­ã¤j,³o¥yIf xR = 1 Then T = xR(1, 0) & ""¬°¦ó¥i¥H§ä¨ì101,102...

TOP

¦^´_ 6# ¤d·u´M


¡e½s¸¹¡f¬°¦X¨Ö®æ¡A»P¥k¤@®æ¡e§Ç¸¹¡D¢°¡f¦P¤@¦C¡A
xR°j°é®É¡A¹J¨ì§Ç¸¹¢°®É¡A¨ú¨ä¡e¥ª¤@®æ¡f­È¡A¯Ç¤JÅܼÆT¡I

TOP

¦^´_ 7# ­ã´£³¡ªL
¤j·§¤F¸Ñ¤F,±N²z¸Ñ»¡©ú¦p¤U:¨C¦¸°j°é§ä¨ì1®É´N´N±N¥ª°¼ªº½s¸¹½á­Èµ¹T(©Ò¥H²Ä¤@¦¸§ä¨ì1®ÉT­È¬°101),©³¤Uªº2,3,4,5ªºT­È³£¬O¬Û¦Pªº(¤£·|§ïÅÜ),ª½¨ì¤S¸I¨ì¥t¤@­Ó1®É(²Ä¤G¦¸®ÉT­È´N§ó§ï¬°102)...¤Sªþ±a°Ý¥t¥~¤@¥y: Set xH = xH(2)¬O¦]¬°³Ìªì³]¬°A1,¦Ó©¹«á§ä¨ì¸ê®Æ®É,©Ò¥H¶·©¹¤U¤@¦C, ³]©w¬°·sªºxH,¥H«K©ñ¤J¸ê®Æ, §YxH¬O·|¤£Â_©¹¤U²¾°Ê,¤£Â_¿±µÈªºÅܼÆ.
¤£ª¾¹D³o¼Ë²z¸Ñ¹ï¤£¹ï!
¤j¤jªº»yªk²¼ä¦³¤O,¨C¨C³£¦³ÅåÆvªº·Pı!ÁÂÁ­ã¤jªº­@¤ß±Ð¾É!

TOP

¦^´_ 8# ¤d·u´M


¥¿¸Ñ¡I^ ^

xH ¬°¤W¥ôÁ`²Î¡A¥ô´Á¨ì¤F¡A·í¥æµ¹¤U¥ôÁ`²Î xH(2)¡I

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2024-1-12 08:56 ½s¿è

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C¤¤ªº°}¦C»P °}¦C»PÀx¦s®æ¹ï·Ó¹B¥Î,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
Sheets(1)¸ê®Æªí:


Sheets(2)°õ¦æµ²ªG:


Option Explicit
Sub TEST()
Dim Brr, i&, j%, R&, xA, xAs
Sheets(2).[A:C].Delete: Brr = Sheets(1).UsedRange
For j = 5 To 17 Step 6
   For i = 4 To UBound(Brr)
      If InStr(Brr(i, j - 1) & Brr(i, j), "¡­") Then
         R = R + 1
         Brr(R, 1) = Sheets(1).Cells(i, j).Item(1, -3).MergeArea(1)
         Brr(R, 2) = Brr(i, j - 3): Brr(R, 3) = Brr(i, j - 2)
      End If
   Next
Next
If R = 0 Then Exit Sub Else xAs = Array([{"¼Ó¼h", "§Ç", "©m¦W"}], Brr)
xA = Array(Sheets(2).[A1].Resize(, 3), Sheets(2).[A2].Resize(R, 3))
For i = 0 To UBound(xA)
      xA(i).Value = xAs(i): xA(i).Borders.LineStyle = 1
      For j = 7 To 10: xA(i).Borders(j).Weight = 4: Next
Next
Application.Goto Sheets(2).[A1]
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤H¥Í³Ì¤jªº¦¨´N¬O±q¥¢±Ñ¤¤¯¸°_¨Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD