在 Excel 2007 中将包含数据列的行转换为包含多行的列

Question

我有一行数据，如下：

            header1      header2      header3      header4      header5
row key     datavalue1   datavalue2   datavalue3   datavalue4   datavalue5....

基本上，我有一个非规范化数据集，其中数据值逐行可能为空，也可能不为空。我需要使它们正常化。

即

12345678    NULL         10           3            NULL         14

会变成：

12345678   header2   10
12345678   header3   3
12345678   header5   14

我可以通过使用粘贴特殊转换来做到这一点，但我有数千行，我需要确保为每一行获得正确的行键。此外，每一行都有一堆与之相关的描述，我需要将其与每个数据值一起复制。

转换每一行列以使我拥有单列的多行且包含所有非空数据值加上关联的数据值引用的最简单方法是什么？我需要能够旋转数据集。

Answer 1

我将创建一个循环遍历每一行并将数据输出到另一页的 VBA 宏。一旦数据输出，您就可以在新页面中创建数据透视表。

不确定您对 VBA 有多熟悉，但这可以很容易地通过将数据加载到数组（或对象集合，如果您确实想正确执行）中并将其写回来完成。

这是一个优秀的 VBA 文档的链接。

http://social.msdn。 microsoft.com/Forums/en/isvvba/thread/d712dbdd-c876-4fe2-86d2-7d6323b4262c

编辑

请注意，这并不是一个完全有效的解决方案，而是一个真正的通用框架帮助您朝正确的方向前进。

作为一个通用示例，它完成了您需要做的很多事情（不是最好的方法，但对于初学者来说可能是最简单的），这样的事情应该可以帮助您开始，尽管在没有看到更多工作表的情况下很难说。

Sub RowsToColumns ()
  Application.ScreenUpdating = False
  Dim srcWrkSheet As Worksheet
  Dim destWrkSheet As Worksheet
  Dim excelData as pExcelData
  Dim srcRowNumber As Long
  Dim srcRolNumber As Long
  Dim destRowNumber As Long
  Dim destColNumber As Long

  SET srcWrkSheet = Sheets("YourSourceWorkSheetName")
  SET destWrkSheet = Sheets("YourDestinationWorkSheetName")

  srcRowNumber = 1
  srcColNumber = 1
  destRowNumber = 1
  destColNumber = 1

  'Loop until blank row is encountered in column 1
  Do
    destWrkSheet.Cells(destRowNumber ,1).Value = "Header 1 " & srcWrkSheet.Cells(srcRowNumber,srcColNumber )
    destWrkSheet.Cells(destRowNumber ,1).Value = "Header 2 " & srcWrkSheet.Cells(srcRowNumber ,srcColNumber)

    srcRowNumber = srcRowNumber + 1
    srcColNumber = srcColNumber + 1
    destRowNumber = destRowNumber  + 1
  Loop Until srcWrkSheet .Cells(rowNumber, 1).value = ""

End Sub

Answer 2

如果您有五个“标题”列，请输入以下公式

H1: =OFFSET($A$1,INT((ROW()-1)/5)+1,0)
I1: =OFFSET($A$1,0,IF(MOD(ROW(),5)=0,5,MOD(ROW(),5)))
J1: =INDEX($A$1:$F$9,MATCH(H1,$A$1:$A$9,FALSE),MATCH(I1,$A$1:$F$1,FALSE))

复制 H1:J??并将特殊值粘贴到顶部。对 J 列进行排序并删除所有为零的内容。如果数据中有合法的零，那么您首先需要用一些唯一的字符串替换空白单元格，然后可以将其删除。

如果您有更多列，请将上述所有公式中的“5”替换为您拥有的任何数字。

Answer 3

在我看来，您要做的部分工作是“取消透视”数据透视表。当我必须执行类似的任务时，我发现此提示非常有帮助： http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/

请注意，在 Excel 2007 中，您可以使用按键 Alt+D、P 进入旧版 Excel 2003 数据透视表向导。

Answer 4

Excel 具有转置功能，可以满足您的需求。它相当隐蔽，而且有点笨拙，但可能比深入研究 VBA 更容易。以下是 Excel 2007 帮助的摘录：

块引用
切换（转置）列和行
显示全部隐藏全部
如果数据是按列或行输入的，但您希望将数据重新排列到行或列中，则可以快速将数据从一个数据转置到另一个数据。

例如，按列组织的区域销售数据在转置数据后会显示在行中，如下图所示。

1.在工作表上，执行以下操作：
要将数据从列重新排列到行，请选择包含数据的列中的单元格。
要将数据从行重新排列到列，请选择包含数据的行中的单元格。
2.在“开始”选项卡的“剪贴板”组中，单击“复制”。

键盘快捷键 要复制选定的数据，您还可以按 CTRL+C。

注意 您只能使用“复制”命令来重新排列数据。要成功完成此过程，请勿使用“剪切”命令。

3.在工作表上，选择要将复制的数据重新排列到的目标行或列的第一个单元格。
注意 复制区域（复制区域：当您要将数据粘贴到另一个位置时复制的单元格。复制单元格后，单元格周围会出现一个移动边框，表明它们已被复制。）和粘贴区域（粘贴区域：使用 Office 剪贴板剪切或复制的数据的目标位置不能重叠。确保您在粘贴区域中选择的单元格位于复制数据的区域之外。

4.在“开始”选项卡的“剪贴板”组中，单击“粘贴”下方的箭头，然后单击“转置”。
5.数据转置成功后，可以删除复制区的数据。
提示 如果转置的单元格包含公式，则公式会转置，并且对转置单元格中数据的单元格引用会自动调整。为了确保公式继续正确引用非转置单元格中的数据，请在转置公式之前使用绝对引用。

有关详细信息，请参阅在相对、绝对和混合引用之间切换。

块引用

Answer 5

让我们看一下 VBA 中可能的解决方案。我认为这真的很有帮助。以下是关于我的代码您应该了解的一些事项。

• 您需要将此代码放入 VBA 中的代码模块中（与宏所在的位置相同）
• 看看我对工作表的命名：原始和规范化。您要么想要更改工作表名称，要么更改
• 我正在检查字符串字段为 NULL 的值的代码。如果单元格为空，您需要检查 If IsEmpty(rngCurrent.Value) Then。

'

Sub NormalizeSheet()
Dim wsOriginal As Worksheet
Dim wsNormalized As Worksheet
Dim strKey As String
Dim clnHeader As Collection
Dim lngColumnCounter As Long
Dim lngRowCounterOriginal As Long
Dim lngRowCounterNormalized As Long
Dim rngCurrent As Range
Dim varColumn As Variant

Set wsOriginal = ThisWorkbook.Worksheets("Original")     'This is the name of your original worksheet'
Set wsNormalized = ThisWorkbook.Worksheets("Normalized") 'This is the name of the new worksheet'
Set clnHeader = New Collection

wsNormalized.Cells.ClearContents        'This deletes the contents of the destination worksheet'

lngColumnCounter = 2
lngRowCounterOriginal = 1
Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)

' We'll loop through just the headers to get a collection of header names'
Do Until IsEmpty(rngCurrent.Value)
    clnHeader.Add rngCurrent.Value, CStr(lngColumnCounter)
    lngColumnCounter = lngColumnCounter + 1
    Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
Loop

'Here we'll reset our Row Counter and loop through the entire data set'
lngRowCounterOriginal = 2
lngRowCounterNormalized = 1
lngColumnCounter = 1

Do While Not IsEmpty(wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter))

    Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
    strKey = rngCurrent.Value ' Get the key value from the current cell'
    lngColumnCounter = 2

    'This next loop parses the denormalized values for each row'
    Do While Not IsEmpty(wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter))
        Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)

        'We're going to check to see if the current value'
        'is equal to NULL. If it is, we won't add it to'
        'the Normalized Table.'
        If rngCurrent.Value = "NULL" Then
            'Skip it'
        Else
            'Add this item to the normalized sheet'
            wsNormalized.Range("A" & lngRowCounterNormalized).Value = strKey
            wsNormalized.Range("B" & lngRowCounterNormalized).Value = clnHeader(CStr(lngColumnCounter))
            wsNormalized.Range("C" & lngRowCounterNormalized).Value = rngCurrent.Value
            lngRowCounterNormalized = lngRowCounterNormalized + 1
        End If

        lngColumnCounter = lngColumnCounter + 1
    Loop
    lngRowCounterOriginal = lngRowCounterOriginal + 1
    lngColumnCounter = 1    'We reset the column counter here because we're on a new row'
Loop



End Sub