重新排列列数据 [英] Re-Arranging the row data in columns

问题描述

我有以下格式的excel数据

I have data in excel in following format

Resource     2/2/2013   2/3/2013  2/4/2013
  Name1         9          9         9  
  Name2         9          9         9  
  Name3         9          9         9  

我必须将以上数据转换成如下：

I have to convert the above data into something like this:

Resource  Date        Hours
Name1     2/2/2013      9
Name1     2/3/2013      9
Name1     2/4/2013      9
Name2     2/2/2013      9
Name2     2/3/2013      9
Name2     2/4/2013      9
Name3     2/2/2013      9
Name3     2/3/2013      9
Name3     2/4/2013      9

可以在 excel 中有任何功能。我只能找到行到的列，不能帮助我，因为它只会转置数据而不是创建多个条目，如上所述。

Is there any function in excel that can do that. I could find only the row to columns function that didn't help me as it will just transpose the data and not create multiple entries like above.

即使通过 VBA 也可以做到最好的方法。 >

What could be the best way to do this even through VBA.

推荐答案

这是一个VBA解决方案：

Here is a VBA solution:

Sub Example()
    Dim Resources() As String
    Dim rng As Range
    Dim row As Long
    Dim col As Long
    Dim x As Long

    ReDim Resources(1 To (ActiveSheet.UsedRange.Rows.Count - 1) * (ActiveSheet.UsedRange.Columns.Count - 1), 1 To 3)

    'Change this to the source sheet
    Sheets("Sheet1").Select

    'Read data into an array
    For row = 2 To ActiveSheet.UsedRange.Rows.Count
        For col = 2 To ActiveSheet.UsedRange.Columns.Count
            x = x + 1
            Resources(x, 1) = Cells(row, 1).Value    ' Get name
            Resources(x, 2) = Cells(1, col).Value    ' Get date
            Resources(x, 3) = Cells(row, col).Value  ' Get value
        Next
    Next

    'Change this to the destination sheet
    Sheets("Sheet2").Select

    'Write data to sheet
    Range(Cells(1, 1), Cells(UBound(Resources), UBound(Resources, 2))).Value = Resources

    'Insert column headers
    Rows(1).Insert
    Range("A1:C1").Value = Array("Resource", "Date", "Value")

    'Set strings to values
    Set rng = Range(Cells(1, 3), Cells(ActiveSheet.UsedRange.Rows.Count, 3))
    rng.Value = rng.Value
End Sub

原始：

结果：

这篇关于重新排列列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！