[µo°Ý] ¯x°}¸ê®ÆÂà¸mªººÃ°Ý

fusayloveme

¦U¦ìª©¤Wªº¤j¤j¡A·Q½Ð±Ð¦p¦ó±Nªþ¥ó¤¤ªº¯x°}¸ê®Æ¡A¶i¦æ¸ê®ÆÂà¸m¦¨¦¨¸ê®Æ®w®æ¦¡©O?

¹Á¸Õ¹L¤gªk·Ò¿ûªº¿ý»s«á­×§ï¡A¦ý¨BÆJÁcº¾¡A·Q¤W¨Ó°Ý¬Ýª©¤Wªº¤j¤j¬O§_¦³§ó¦nªº¿ìªk¡AÁÂÁ¡C

¸ê®Æ¦pªþ¥ó ºÊ´ú¸ê®Æ.rar (9.44 KB)

ÁÂÁÂ
¤U¸ü¦¸¼Æ: 12

2015-4-1 14:22

SHEET1 ¬°­ì©l¸ê®Æ SHEET2 §Æ±æ¸ê®Æ¥Í¦¨ªº§Î¦¡¡A¥ýÁ¦U¦ìªºÀ°¦£¡C

Scott090

¦^´_ 6# fusayloveme

Application.Transpose() ³oºØ¤u§@ªí¨ç¼Æ¯à³B²zªº½d³ò¬O 65536
µ¹°Ñ¦Ò

GBKEE

¦^´_ 6# fusayloveme
ªþÀÉ¥i¸m©óÀ£ÁYÀɤW¶Ç

¶È¯à°÷²£¥X65521¦Cªº¸ê®Æ,§AªºÀɮת©¥»¥i¯à¬O2003.
2003ªº³Ì¤j¦C¼Æ¬°65536,¶W¥Xªº¦C¼Æ·|²£¥Í¿ù»~.

fusayloveme

¦^´_ 3# GBKEE

G¤j¤£¦n·N«ä¡A·Q¸ò±z½Ð±Ð¤@¤U¡C

¦]¤@¶}©l¬O¥Î¨â­Ó¦Cªº¸ê®Æ¨Ñ¹Á¸Õ¡A¦ý¹ê»Ú¾Þ§@¤W¤j¬ù¨C¦¸·|¦³6993¦Cªº¸ê®Æ¶q

¦]¦¹¸g¹L¯x°}«á¡A¬ù¦³6993*24=167832¦Cªº¸ê®Æ¶q

¦ý¨Ï¥Î¥¨¶°«á¡A¶È¯à°÷²£¥X65521¦Cªº¸ê®Æ¡A¦pªþ¥óÀÉ®×(¤u§@ªí11)

·Q½Ð°Ý³o¬OEXCELªº­­¨î? ÁÙ¬O¦³¨ä¥L¤è¦¡? ¥ýÁÂÁ±zªº¨ó§U¡C

ªþ¥óÀÉ®×(GOOGLE ¶³ºÝ)

fusayloveme

¦^´_ 2# stillfish00

¦^´_ 3# GBKEE

¦^´_ 4# lpk187

·PÁÂS¤j¡BG¤j¤ÎL¤jªºÀ°¦£¡A¥Ø«e¹Á¸Õ³£¥i¥H¥Î³á~

S¤jªº    arDes(1, 1) = "¤é´Á": arDes(1, 2) = "´ú¶µ"
    arDes(1, 3) = "´ú¯¸": arDes(1, 4) = "®É¶¡"
    arDes(1, 5) = "´ú­È"
¸ò
G¤jªº [A1:E1] = Array("¤é´Á", "´ú¶µ", "´ú¯¸", "®É¶¡", "´ú­È")

Åý§Ú¾Ç¨ì¤F¤£¤@¼Ëªº«ä¦ÒÅÞ¿è¡A·PÁ§A­ÌªºÀ°¦£~ ^^~

lpk187

¦^´_ 1# fusayloveme

¤j®aµª®×¥X¦n§Ö¡A§Ú¤]½m²ß¤F¤@¤U

1. Public Sub se()
   
2. end1 = 2
   
3. ®É¶¡ = [d1:aa1]
   
4. For Each rn In Range("A2:A" & Cells(Rows.Count, "a").End(xlUp).Row)
   
5.     ´ú¯¸ = Cells(rn.Row, "B")
   
6.     ´ú¶µ = Cells(rn.Row, "C")
   
7.     arr = Range(Cells(rn.Row, "D"), Cells(rn.Row, "AA"))
   
8.     Sheets("³B²z«á¸ê®Æ").Range("A" & end1).Resize(UBound(arr, 2)) = rn
   
9.     Sheets("³B²z«á¸ê®Æ").Range("B" & end1).Resize(UBound(arr, 2)) = ´ú¶µ
   
10.     Sheets("³B²z«á¸ê®Æ").Range("C" & end1).Resize(UBound(arr, 2)) = ´ú¯¸
    
11.     Sheets("³B²z«á¸ê®Æ").Range("D" & end1).Resize(UBound(arr, 2)) = Application.Transpose(®É¶¡)
    
12.     Sheets("³B²z«á¸ê®Æ").Range("E" & end1).Resize(UBound(arr, 2)) = Application.Transpose(arr)
    
13.     end1 = UBound(arr, 2) + end1
    
14. Next
    
15. With Sheets("³B²z«á¸ê®Æ").[A2].Resize(end1 - 2, 5)
    
16.     .Borders.LineStyle = xlContinuous
    
17.     .Borders.Weight = xlThin
    
18. End With
    
19. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 1# fusayloveme

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Rng As Range, Ar(), i As Integer
   
4.     With Sheets("2013")
   
5.         Set Rng = .Range("A1:C1").Resize(.[A1].End(xlDown).Row)  'End(xlDown) '¶µ¥Ø¥i©µ¦ù
   
6.         Ar = .Range("D1").Resize(Rng.Rows.Count, .[D1].End(xlToRight).Column - 3).Value 'ºÊ´ú­È
   
7.     End With
   
8.     With Sheets("³B²z«á¸ê®Æ")
   
9.         .UsedRange.Clear
   
10.         .[A1:E1] = Array("¤é´Á", "´ú¶µ", "´ú¯¸", "®É¶¡", "´ú­È")
    
11.         For i = 2 To Rng.Rows.Count         '±q2¦C¶}©l
    
12.            With .Cells(.Rows.Count, "A").End(xlUp)
    
13.                 .Offset(1).Resize(UBound(Ar, 2)) = Rng.Cells(i, "A").Text    '¤é´Á
    
14.                 .Offset(1, 1).Resize(UBound(Ar, 2)) = Rng.Cells(i, "B")      '´ú¶µ
    
15.                 .Offset(1, 2).Resize(UBound(Ar, 2)) = Rng.Cells(i, "C")      '´ú¯¸
    
16.                 '°}¦C¾É¤JÀx¦s®æªº­È¬°2ºû°}¦C,¤¸¯À¤U­­¯Á¤Þ±q1¶}©l.
    
17.                 .Offset(1, 3).Resize(UBound(Ar, 2)) = Application.Transpose(Application.Index(Ar, 1))      '®É¶¡
    
18.                 .Offset(1, 4).Resize(UBound(Ar, 2)) = Application.Transpose(Application.Index(Ar, 2))      '´ú­È
    
19.            End With
    
20.         Next
    
21.         With .UsedRange.Borders   '®Ø½u
    
22.             .LineStyle = 1
    
23.             .Weight = xlThin
    
24.         End With
    
25.     End With
    
26. End Sub

½Æ»s¥N½X

stillfish00

¦^´_ 1# fusayloveme

1. Sub test()
   
2.     Dim arSrc(), arDes()
   
3.    
   
4.     arSrc = Sheets(1).[A1].CurrentRegion.Value
   
5.     ReDim arDes(1 To 1 + (UBound(arSrc, 2) - 3) * (UBound(arSrc) - 1), 1 To 5)
   
6.     'Äæ¦ì¦WºÙ
   
7.     arDes(1, 1) = "¤é´Á": arDes(1, 2) = "´ú¶µ"
   
8.     arDes(1, 3) = "´ú¯¸": arDes(1, 4) = "®É¶¡"
   
9.     arDes(1, 5) = "´ú­È"
   
10.         
    
11.     Dim i As Long, j As Long, r As Long
    
12.     r = 2
    
13.     For i = 2 To UBound(arSrc)
    
14.         For j = 4 To UBound(arSrc, 2)
    
15.             arDes(r, 1) = arSrc(i, 1)
    
16.             arDes(r, 2) = arSrc(i, 3)
    
17.             arDes(r, 3) = arSrc(i, 2)
    
18.             arDes(r, 4) = arSrc(1, j)
    
19.             arDes(r, 5) = arSrc(i, j)
    
20.             r = r + 1
    
21.         Next
    
22.     Next
    
23.    
    
24.     With Sheets.Add
    
25.         With .[A1].Resize(UBound(arDes), UBound(arDes, 2))
    
26.             .Value = arDes
    
27.             .Borders.LineStyle = xlContinuous
    
28.             .Borders.Weight = xlThin
    
29.         End With
    
30.     End With
    
31.    
    
32. End Sub

½Æ»s¥N½X