vba - 通过vba将具有数据列的行转换为Excel 2003中具有多行的列

Question

我浏览了有关此主题的模拟主题，但遇到了困难，我需要 excel 2003 中的 VBA 专家的帮助

我有一行数据如下

 (Project Code)    ...(Milestone1)  (Date1)     (Milestone2)  (Date2) ... (Milestone 10)(Date3)
        ColumnA    ... ColumnAG     ColumnAH    ColumnAI     ColumnAJ  ...  ColumnAY    ColumnAZ
        Header1        Header33     Header34    Header35     Header36  ...  Header51    Header52
 rowkey datavalue1     datavalue33   datavalue34 datavalue35  datavalue36 ... datavalue51

我有一个非规范化数据集，其中数据值逐行可能为空，也可能不为空。我需要将它们标准化。

即

        1234567        start    12/12/2012    build      12/01/2013 ... finish 01/02/2014        
        1234568        plan     14/12/2012    contract   02/02/2013  ... NULL   NULL

会变成

        1234567        header33  start    12/12/2012
        1234567        header34  build    12/01/2013
        ...
        1234567        header51  finish   01/02/2014
        1234568        header33  plan     14/12/2012
        1234568        header34  contract 02/02/2013

我有大约数百行，包含名称为 10 个里程碑和 10 个关联日期的项目数据，并且需要将它们拆分，以便我有多行列出每个项目代码的里程碑标题/里程碑名称/日期......如果里程碑名称和数据不一致，则可以跳过该记录...

有一个类似的帖子 Convert row with columns of data into column with multiple rows in Excel 2007 ，但不知道如何更改以将其应用于上述场景...

如有任何帮助，我们将不胜感激谢谢

Answer 1

好吧，这个代码确实不太先进，但是有点乏味。我为您设计的这款产品应该可以满足您的需求。它充满了注释，您也可以使用它们来帮助学习代码(如果您想自己再次执行此操作)。祝你好运!

Sub normalizeData()

Dim i As Integer, j As Integer, k As Integer
Dim r As Integer
Dim bReport As Workbook, Report As Worksheet, Report2 As Worksheet
Dim m(0 To 9) As Integer 'This sets up the variables for your milestones
Dim mileString As String, dateString As String

Set Report = Excel.ActiveSheet
Set bReport = Report.Parent
Set Report2 = bReport.Worksheets.Add

'There are two ways to identify your milestones to the subroutine: either have some kind of identifier to search _
for within the value of the milestone cell; or define them explicitly. I'm going to do the latter since I don't know _
what might be in those cells.

m(0) = 33 'The column number for each milestone
m(1) = 35
m(2) = 37
m(3) = 39
m(4) = 41
m(5) = 43
m(6) = 45
m(7) = 47
m(8) = 49
m(9) = 51


r = Report.UsedRange.Rows.Count 'Get the last row number and assign it to a _
                                short variable (just to make things easier).

Report2.Cells(1, 1).Value = "Normalized Data" 'Placing a header row allows us to use used range without having _
                                                to worry about identifying the first iteration. If you've ever tried this _
                                                by yourself you probably know what I'm referring to.

With Report2.Range("A1:D1") 'Just makin it purdy
    .Merge
    .HorizontalAlignment = xlCenter
    .Interior.Color = RGB(0, 20, 99)
    .Font.Color = RGB(224, 238, 255)
    .Font.Bold = True
    .Font.Size = 14
End With


For i = 2 To r
    For j = 0 To 9
        If Report.Cells(i, m(j)).Value <> "" And _
        Report.Cells(i, m(j)).Value <> "Null" Then 'I'm not sure if the empty values will be null or blank strings.
            mileString = Report.Cells(i, m(j)).Value
            dateString = Report.Cells(i, m(j) + 1).Value
            k = Report2.UsedRange.Rows.Count + 1
            Report2.Cells(k, 1).Value = Report.Cells(i, 1).Value
            Report2.Cells(k, 2).Value = Report.Cells(1, m(j)).Value 'Assuming your header is on the first row
            Report2.Cells(k, 3).Value = mileString
            Report2.Cells(k, 4).Value = dateString
            Report2.Cells(k, 4).NumberFormat = "MM/dd/yyyy"
        End If
    Next j
Next i

For i = 2 To Report2.UsedRange.Rows.Count
    If i Mod 2 = 0 Then
        Report2.Range("A" & i & ":D" & i).Interior.Color = RGB(227, 235, 252) 'Alternating row color for easier reading.
    End If
Next i

'************** This part adds borders. Omit this block if you don't want them.************
With Report2.Range("A1:D" & Report2.UsedRange.Rows.Count)
    .Borders(xlEdgeTop).Weight = xlThin
    .Borders(xlEdgeBottom).Weight = xlThin
    .Borders(xlLeft).Weight = xlThin
    .Borders(xlRight).Weight = xlThin
    .Borders(xlInsideHorizontal).Weight = xlThin
    .Borders(xlInsideVertical).Weight = xlThin
End With
'******************************************************************************************



End Sub

PS:运行宏时，请确保您位于非规范化工作表上。它旨在将该工作表引用为事件工作表，并将标准化数据放入新工作表中。

另请注意，我使用了一个数组来标识里程碑，但我确信您可以轻松地使用带有步骤 2 增量的 For...Next 循环来提高可扩展性。例如，在本例中，for 循环的开头将类似于 For j = 33 to 51 Step 2。这是假设您的所有里程碑都相隔两列。