☰

如何将Excel中的一行拆分为多行以进行数据库管理

tag-icon • tag-icon microsoft-excel database
如何将Excel中的一行拆分为多行以进行数据库管理

我有一组数据，目前有 39,000 行和 27 列。第一列是 ID 号。后续列对应于选举，第 1 行（单元格B1- AA1）中是选举日期。其余单元格用对应于投票方式的字母填充（如果没有投票则为空）。我需要重新排列此表，以便总共有三列：ID、日期和投票方式。例如：

当前表：

ID          05/2005        11/2005        03/2006 (etc., for 27 total columns)
2345           P              V
3789                          A              V
4321           V              A              V
7890                          I

我需要它看起来像这样：

ID           Date            Voting Method
2345         05/2005               P
2345         11/2005               V
3789         11/2005               A
3789         03/2006               V
4321         05/2005               V
4321         11/2005               A
4321         03/2006               V
7890         11/2005               I

我认为这将需要 VBA 脚本，我尝试将在线找到的脚本片段拼凑在一起（因为我从未学过 VBA），但似乎无法让它正常工作。也许这个函数在 Excel 中已经存在？

以下是我目前使用的脚本：

Sub NewLayout()
For i = 2 To Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
  For j = 0 To 26
    If Cells(i, 3 + j) <> vbNullString Then
      intCount = intCount + 1
      Cells(i, 1).Copy Destination:=Cells(intCount, 10)
      Cells(i, 2).Copy Destination:=Cells(intCount, 11)
                [I think this one is wrong. It needs to copy the column name,
                not the cell value, if there is a cell value.]
      Cells(i, 3 + j).Copy Destination:=Cells(intCount, 12)
    End If
    Next j
  Next i
End Sub

如果有人有任何建议，我将不胜感激！

答案1

使用 VBA，假设数据在 Sheet1 中从 A1 开始，并且 Sheet2 存在：

Sub normalize()
    Dim wks1 As Worksheet, wks2 As Worksheet
    Dim iColCount As Integer, iRowCount As Integer
    Dim i As Integer, j As Integer, k As Integer

    Set wks1 = ActiveWorkbook.Sheets("Sheet1")
    Set wks2 = ActiveWorkbook.Sheets("Sheet2")
    iColCount = Application.WorksheetFunction.CountA(wks1.Range("1:1"))
    iRowCount = Application.WorksheetFunction.CountA(wks1.Range("A:A"))

    k = 1
    For i = 2 To iRowCount
        For j = 2 To iColCount
            If wks1.Cells(i, j) <> vbNullString Then
                wks1.Cells(i, 1).Copy Destination:=wks2.Cells(k, 1)
                wks1.Cells(1, j).Copy Destination:=wks2.Cells(k, 2)
                wks1.Cells(i, j).Copy Destination:=wks2.Cells(k, 3)
                k = k + 1
            End If
        Next j
    Next i
End Sub

Sheet2 中的结果：

2345    5/2005  P
2345    11/2005 V
3789    11/2005 A
3789    3/2006  V
4321    5/2005  V
4321    11/2005 A
4321    3/2006  V
7890    11/2005 I

答案2

为了完整起见，我现在展示如何做到这一点没有诉诸 VBA。我必须警告，以下代码非常复杂，难以有效扩展。

我们假设以下初始条件：

工作表1

   |  A |    B    |    C    |    D    | …
---+----+---------+---------+---------+---
 1 | ID | 05/2005 | 11/2005 | 03/2006 |
 2 |2345|    P    |    V    |         |
 3 |3789|         |    A    |    V    | …
 4 |4321|    V    |    A    |    V    |
 5 |7890|         |    I    |         |
 … |                 …

工作表2

   |  A |   B  |       C       |
---+----+------+---------------+
 1 | ID | Date | Voting method |
 2 | #1 |  #2  |       #3      |

细胞名称#1公式如下：

=INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/27 + 2,0),1,,,"Sheet1"))

此公式的作用是将当前单元格映射到 Sheet1 中的正确单元格。这是在函数的帮助下完成的FLOOR。每当传递 27 行时，函数就会增加 1，从而正确地将 Sheet2 中的行与 Sheet1 上的内容映射。

该ADDRESS函数根据数字输入和工作表名称构造对单元格的可用引用，同时该INDIRECT函数检索引用指向的内容。

其余函数遵循相同的原理：使用辅助函数将当前单元格的坐标映射到 Sheet1 上的正确单元格。

对于名为#2：

=INDIRECT(ADDRESS(1,MOD(ROW(A2)-2,27)+2,,,"Sheet1"))

在这种情况下，MOD函数在 0 和 26 之间按顺序交替，然后转换为 2 和 28 之间的序列（换句话说，就是日期所在的单元格的位置）。

最后，对于名为#3：

=INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/27 + 2,0),MOD(ROW(A2)-2,27)+2,,,"Sheet1"))

它是之前使用的两个序列的混合。之所以如此，是因为内容根据 ID 而有所不同（因此，来自单元格的部分#1被称为）和日期（这是单元格中的部分#2进入）。

将这些函数输入到正确的单元格后，只需向下拖动，结果就会显现出来，但有一个小问题：空票也会弹出。

不过，您可以过滤这些结果。选择标题（在本例中为ASheet2 上的行），然后转到数据 > 过滤器 > 自动过滤器（或您正在使用的 Excel 版本中的等效项）。单击投票方法列上的下拉菜单并个性化排序，以排除由零组成的结果。

答案3

我会使用 Power Query 插件来完成此任务。它不需要任何代码或复杂的功能。从头开始完成这项任务可能只需不到 5 分钟。

您可以从现有的 Excel 表开始查询。然后我将使用 Unpivot 命令根据您的需要转换数据。

http://office.microsoft.com/en-au/excel-help/unpivot-columns-HA104053356.aspx

他们实现 Unpivot 的优点在于它将接受任何添加的进一步的列（新日期）并处理它们，而无需对查询定义进行任何更改。

我会根据需要重命名列并将结果传送到 Excel 表。

相关内容