☰

要重新格式化行和列中的 Excel 数据

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2007
要重新格式化行和列中的 Excel 数据

我在 Excel 中拥有交错格式的数据。最左侧列中有一个客户 ID，该客户的详细信息位于不同列中的不同行中。我需要将数据重新排列到每个客户所在的一行中。所有其他客户的格式都相同。

CustomerID FirstName LastName Education Training TrainingYear City State Zip
1          John       C       Masters
1                                      SAP
1                                                2017
1                                                             LA
1                                                                   CA
1                                                                        91433
2          Max        G       Bachelors
2                                       Oracle
2                                               2015
2                                                             NR
2                                                                  CA
2                                                                        91234

答案1

用这个

=INDIRECT(CONCATENATE("A", ROW()*6-10))

将“A”更改为相应的列（B 表示名字、C 表示姓氏、D 表示教育背景，等等），并将“-10”更改为相应的列（-10 表示教育背景、-9 表示培训、-8 表示培训年份，...，-5 表示邮政编码）

适用于培训专栏

=INDIRECT(CONCATENATE("E", ROW()*6-9))

对于 Zip 列

=INDIRECT(CONCATENATE("H", ROW()*6-6))

致谢修复者1234评论。

这种直观的方法不起作用（或者至少不太方便，因为自动填充不能识别简单的 6 步骤模式）：

CustomerID | Firstname | Lastname | Education | Training | TrainingYear | City | State | Zip
----------------------------------------------------------------------------------------------
=A2        | =B2       | =C2      | =D2       | =E3      | =F4          | =G5  | =H6   | =I7
=A8        | =B8       | =C8      | =D8       | =E9      | =F10         | =G11 | =H12  | =I13

答案2

我需要将数据重新排列到每个客户所在的一行。所有其他客户的数据格式都相同。

如果您熟悉运行宏。这是一个简单的嵌套循环：

Sub moveAndCleanup()
    Call move
    Call cleanup
End Sub

Sub move()
    Dim row, col, i, j As Integer
    row = 2
    col = 4
    j = 6
        Do While Cells(row, 1).Value <> ""
            For i = 1 To 5
                Cells(row, col + i).Value = Cells(row + i, col + i).Value
            Next i
            row = row + j
        Loop
End Sub

Sub cleanup()
    Dim row, col, i, j As Integer
    row = 2
    col = 4
    j = 6
        Do While Cells(row, 1).Value <> ""
            For i = 1 To 5
                Cells(row + i, col + i).Value = ""
                Cells(row + i, 1).Value = ""
            Next i
            row = row + j
        Loop
End Sub

但请记住先复制您的数据或复制您的工作簿，因为您无法撤消宏。

答案3

这可以使用一些公式来实现，这些公式根据行计算要使用的单元格，就像 Adi Nugroho 的答案那样。事实上，您可以更进一步，对所有单元格使用一个公式。

1：单一配方解决方案

以下是一个示例（可以使用多种方法）。将列标题复制到另一张表。然后在 A2 中输入此公式：

=OFFSET(Sheet1!$A$2,ROW()*6-12+(COLUMN()>4)*(COLUMN()-4),COLUMN()-1)

将其复制到各列，然后根据需要复制行。它为您提供：

单一配方

解释：

OFFSET 函数将新表固定到原始数​​据的第一个单元格，然后将每个单元格定义为距该单元格的偏移量。

ROW()*6-12将原来的六行模式转换为单行间隔。

(COLUMN()>4)*(COLUMN()-4)增加 E 列及以后的行引用。第一个括号包含一个逻辑测试，用于判断您是否超出了第 4 列，1如果为真则返回，0如果为假则返回。将其乘以超出 4 的列号以反映数据模式（第 5 列删除一行）。

COLUMN()-1将实际列调整为偏移量（结果的第 1 列0与数据的第 1 列偏移）。

因此，单一公式可以为结果中的每个单元格找到正确的源单元格。

2：无需脑细胞的解决方案

作为替代方案，有一个非常简单的解决方案，不需要任何花哨的功能。

从 Sheet1 中的数据开始：

数据

  • 将列标题复制到新工作表。

  • 对于第一行，输入 Sheet 1 中数据的第一个单元格引用（在 A2 中）：

    =sheet1!A2

将其复制到 B2、C2 和 D2。然后将其复制到 E3、F4、G5、H6 和 I7。这将自动将单元格指向正确的位置。然后将 E 列到 I 列中的单元格拖（移动）到第 2 行，公式将保留。因此第 2 行将包含：

A2: =sheet1!A2
B2: =sheet1!B2
C2: =sheet1!C2
D2: =sheet1!D2
E2: =sheet1!E3
F2: =sheet1!F4
G2: =sheet1!G5
H2: =sheet1!H6
I2: =sheet1!I7
  • 选择第 2 行的值并将它们复制到页面下方，复制的行数与工作表 1 上的数据相同。它看起来像这样：

步骤1

它包含单行数据，但位于每个六行组的前几行中。请注意，如果 A 列的格式为数字（这不是问题），则 B 至 I 列中的行可能为零，而不是空白行。

我们通过另一个步骤消除多余的行。

  • 选择整个表格并打开过滤。
  • 在 B 列上进行过滤，并选择仅显示空单元格（或零，如果是空单元格）。过滤器将隐藏包含您值的行，并看起来像这样（或看起来像除 A 列之外的零行）；即，仅显示额外的行：

筛选

  • 选择所有可见数据行并通过右键菜单删除行：

删除行

  • 现在关闭过滤，您将得到结果：

在此处输入图片描述

相关内容