vba 在 Excel 2007 中将具有数据列的行转换为具有多行的列

Convert row with columns of data into column with multiple rows in Excel 2007

提问by jhc

I have a row of data as follows:

我有一行数据如下：

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

so basically, I have a denormalized data set where the datavalues may or may not be empty on a row-by-row basis. I need to normalize them.

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

ie

IE

12345678    NULL         10           3            NULL         14

would become:

会成为：

12345678   header2   10
12345678   header3   3
12345678   header5   14

I could do this by using a paste special transform, but I have thousands of rows and I'd need to make sure that I get the right row key for each. furthermore, each row has a bunch of descriptives associated with it that I need copied over with each datavalue.

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

What is the easiest way to convert each row of columns such that I have multiple rows of a single column with all non-empty datavalues plus the associated datavalue reference? I need to be able to pivot the dataset.

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

回答by Dick Kusleika

If you have five "header" columns, enter these formulas

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

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

Copy H1:J?? and paste special values over the top. Sort on column J and delete anything that's a zero. If you have legitmate zeros in the data, then you first need to replace blank cells with some unique string that you can then delete later.

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

If you have more columns, then replace the '5' in all the above formulas with whatever number you have.

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

回答by f106dart

Seems to me that part of what you are trying to do is to "de-pivot" a pivot table. I've found this tip to be a tremendous help when I've had to do similar tasks: http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/

在我看来，您尝试做的部分工作是“取消旋转”数据透视表。当我不得不做类似的任务时，我发现这个提示是一个巨大的帮助：http: //spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/

Note that in Excel 2007, you can get to the old Excel 2003 pivot table wizard using the keystrokes Alt+D, P .

请注意，在 Excel 2007 中，您可以使用按键 Alt+D, P 访问旧的 Excel 2003 数据透视表向导。

回答by MMurphy

Excel has a transpose feature which may address your needs. It's pretty hidden and a bit clumsy but likely easier than delving into VBA. Here's an excerpt from Excel 2007 Help:

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

Blockquote Switch (transpose) columns and rows Show AllHide All If data is entered in columns or rows, but you want to rearrange that data into rows or columns instead, you can quickly transpose the data from one to the other.

Blockquote 切换（转置）列和行 Show AllHide All 如果在列或行中输入数据，但您想将该数据重新排列为行或列，您可以快速将数据从一个转置到另一个。

For example, the regional sales data that is organized in columns appears in rows after transposing the data, as shown in the following graphics.

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

1.On the worksheet, do the following: To rearrange data from columns to rows, select the cells in the columns that contain the data. To rearrange data from rows to columns, select the cells in the rows that contain the data. 2.On the Home tab, in the Clipboard group, click Copy .

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

Keyboard shortcut To copy the selected data, you can also press CTRL+C.

键盘快捷键 要复制所选数据，您也可以按 CTRL+C。

Note You can only use the Copy command to rearrange the data. To complete this procedure successfully, do not use the Cut command.

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

3.On the worksheet, select the first cell of the destination rows or columns into which you want to rearrange the copied data. Note Copy areas (copy area: The cells that you copy when you want to paste data into another location. After you copy cells, a moving border appears around them to indicate that they've been copied.) and paste areas (paste area: The target destination for data that's been cut or copied by using the Office Clipboard.) cannot overlap. Make sure that you select a cell in a paste area that falls outside of the area from which you copied the data.

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

4.On the Home tab, in the Clipboard group, click the arrow below Paste, and then click Transpose. 5.After the data is transposed successfully, you can delete the data in the copy area. Tip If the cells that you transpose contain formulas, the formulas are transposed and cell references to data in transposed cells are automatically adjusted. To make sure that formulas continue to refer correctly to data in nontransposed cells, use absolute references in the formulas before you transpose them.

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

For more information, see Switch between relative, absolute, and mixed references.

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

Blockquote

块引用

回答by Irwin M. Fletcher

I would create a VBA macro that loops through each row and output the data to another page. This would let you create your pivot table in the new page once the data has been outputed.

我会创建一个 VBA 宏，循环遍历每一行并将数据输出到另一个页面。这将允许您在输出数据后在新页面中创建数据透视表。

Not sure how familiar you are with VBA, but this could pretty easily be done by loading the data into an array (or collection of objects if you really want to do it correctly) and writing it back out.

不确定您对 VBA 的熟悉程度，但这可以通过将数据加载到数组（或对象集合，如果您确实想正确执行）并将其写回来轻松完成。

Here is a link to a good VBA document.

这是一个很好的 VBA 文档的链接。

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

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

Edit

编辑

Please note this is not meant to be a fully working solution but really a generic framework to help you in the right direction.

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

As a generic example that does a lot of what you would need to do (not the best way, but probably the easiest for a beginer), something like this should get you started, although it is hard to say without seeing more of your worksheet.

作为一个可以做很多你需要做的事情的通用例子（不是最好的方法，但对于初学者来说可能是最简单的），这样的事情应该让你开始，虽然很难说没有看到更多的工作表.

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

回答by Ben McCormack

Let's look at a possible solution in VBA. I think this will really help. Here are a few things you should know about my code.

让我们看看 VBA 中的一个可能的解决方案。我认为这真的会有所帮助。关于我的代码，您应该了解以下几点。

• You'll need to put this code in a code module in VBA (the same place where Macros go)
• Look at what I named the sheets: Original and Normalized. You'll either want to change your sheet names or the code
• I'm checking for values with a string field of NULL. If the cell is empty, you'll want to check for If IsEmpty(rngCurrent.Value) Theninstead.

• 您需要将此代码放在 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