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

[µo°Ý] ¿é¤J¸ê®Æ»P³øªíªº°ÝÃD

¦^´_ 9# q1a2z5

§Ú¤w¥[¤Wµù¸Ñ¡A©Î³\±z¥i¥H±q¸Ì­±¾Ç¨ì¤@¨Ç¦Û¤v·Q­nªº¡AµM«á¦Û¤v­×§ï½m²ßÀ³¥Î¡A
§Ú¤]¦b¾Ç²ß¤¤¡A½×¾Â¦³«Ü¦h¼F®`«e½ú¡A¥i¥H¦h¬Ý¬ÝÆ[¼¯¥L­Ìªº¼gªk©M«ä¦ÒÅÞ¿è¡A
¦Û¤v¦h¦h½m²ß´N·|¦³©Ò¦¨ªø¡A¤¬¬Û«jÀy¤§¡A·PÁ¡C

Sub tt()
Dim Arr, Ar(1 To 1, 1 To 6), Ar2(1 To 1, 1 To 6), Ar3(1 To 1, 1 To 6)
Dim Ar4(1 To 1, 1 To 6), T$, j&, i&, N%
Application.ScreenUpdating = False
Tm = Timer
Arr = Sheets("¿é¤Jªí").Range("a1:bb" & [¿é¤Jªí!B65536].End(3).Row) '¸ê®Æ¸Ë¤J¼Æ²Õ
For j = 3 To UBound(Arr, 2) '±q²Ä3Äæ¶}©l¨ì³Ì«á
    T = Arr(2, j) '¦~«×
    If T = "" Then GoTo 99 '¦pªG¦~«×ªÅ¥Õ´N´«¤U­Ó
    For i = 3 To 8: N = N + 1: Ar(1, N) = Arr(i, j): Next: N = 0     '109¦~1-6¤ë¸ê®Æ¸Ë¤JAr¼Æ²Õ
    For i = 9 To 14: N = N + 1: Ar2(1, N) = Arr(i, j): Next: N = 0   '109¦~7-12¤ë¸ê®Æ¸Ë¤JAr2¼Æ²Õ
    For i = 17 To 22: N = N + 1: Ar3(1, N) = Arr(i, j): Next: N = 0  '110¦~1-6¤ë¸ê®Æ¸Ë¤JAr3¼Æ²Õ
    For i = 23 To 28: N = N + 1: Ar4(1, N) = Arr(i, j): Next: N = 0  '110¦~7-12¤ë¸ê®Æ¸Ë¤JAr4¼Æ²Õ
    With Sheets(T)  '¨C­Ó¦~«×sheet
        .Range("B5").Resize(1, 6) = Ar      'Ar¸ê®Æ¶K¦ÜB5
        .Range("B7").Resize(1, 6) = Ar2     'Ar2¸ê®Æ¶K¦ÜB7
        .Range("B9").Resize(1, 6) = Ar3     'Ar3¸ê®Æ¶K¦ÜB9
        .Range("B11").Resize(1, 6) = Ar4    'Ar4¸ê®Æ¶K¦ÜB11
    End With
99: Next
Application.ScreenUpdating = True
MsgBox "°õ¦æ§¹¦¨" & Timer - Tm & " ¬í"
End Sub

TOP

¦^´_ 6# q1a2z5


¥ý¿ï¾Ü B5¡GG5
¿é¤J¤½¦¡ =TRANSPOSE(OFFSET(¿é¤Jªí!$A$1,MATCH(MAX(--(0&MID(A2:A4,4,3))),¿é¤Jªí!A:A,)-1+(B4="7¤ë")*6,MATCH(--RIGHT(CELL("filename"),3),¿é¤Jªí!$2:$2,)-1,6))
¥H°}¦C¤½¦¡¿é¤J (CTRL+SHIFT ¥ý«ö¤£©ñ¡A¦A«öENTER)¤TÁä¿é¤J¤½¦¡

¥ý¿ï¾Ü B5¡GG5 ½Æ»s
¥ý¿ï¾Ü B7¡GG7 ¶K¤W
¥ý¿ï¾Ü B9¡GG9 ¶K¤W
¥ý¿ï¾Ü B11¡GG11 ¶K¤W


½Æ»s Sheet "101" ¦Ü Sheet "102"
½Æ»s Sheet "101" ¦Ü Sheet "103"
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦¨´N¤@¤Á¡j®É¶¡¥i¥H³y´N¤H®æ¡A¥i¥H¦¨´N¨Æ·~¡A¤]¥i¥HÀx¿n¥\¼w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD