☰

如何使用 Excel 将一行拆分为多行？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 csv
如何使用 Excel 将一行拆分为多行？

我在 Excel 中有一个产品数据库，里面有几百个条目，每个条目都有 1 到 3 个“层级”的定价：标准、豪华和高级。每个层级都有自己的 SKU（在基本 SKU 末尾添加 A、B 或 C）和价格。我的数据如下：

Name, Description, Price A, Price B, Price C, SKU A, SKU B, SKU C
name1,      desc1,   14.95,   19.95,        , sku1A, sku1B, 
name2,      desc2,    4.95,    9.95,   12.95, sku2A, sku2B, sku2C
name3,      desc3,   49.95,        ,        , sku3A,      ,

我怎样才能让数据看起来像这样：

Name, Description,   SKU, Price
name1,      desc1, sku1A, 14.95
name1,      desc1, sku1B, 19.95
name2,      desc2, sku2A,  4.95
name2,      desc2, sku2B,  9.95
name2,      desc2, sku2C, 12.95
name3,      desc3, sku3A, 49.95

如果有帮助，我将把这些产品导入 Magento 安装。

提前感谢您。

答案1

使用 VBA 完成这些任务通常更快。事实上，我花了大约 10 分钟来设置它。
我假设您的数据在 A 列到 H 列中。

转到Excel » Developer » Visual Basic» 在左侧窗格中打开sheet1（或）数据所在的工作表 » 在右侧窗口插入代码 » 运行代码

VBA 代码

1 |Sub NewLayout()
2 |    For i = 2 To Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
3 |        For j = 0 To 2
4 |        If Cells(i, 3 + j) <> vbNullString Then
5 |            intCount = intCount + 1
6 |            Cells(i, 1).Copy Destination:=Cells(intCount, 10)
7 |            Cells(i, 2).Copy Destination:=Cells(intCount, 11)
8 |            Cells(i, 3 + j).Copy Destination:=Cells(intCount, 12)
9 |            Cells(i, 6 + j).Copy Destination:=Cells(intCount, 13)
10|        End If
11|        Next j
12|    Next i
13|End Sub

解释

我的目的是让代码尽可能简短，以便更好地解释它。基本上我们使用两个循环。外循环（i）用于行，内循环（j）用于价格列。

我们大量使用cells(rowNumber,columnNumber)来读/写单元格。

  • 第 2 行 | 从第 2 行开始循环到最后一行。我们遍历每个使用的行

  • 第 3 行|从 0 到 2 开始第二个循环（实际上是 3 个循环，每个 Price 列一个循环）

  • 第 4 行 | 我们使用此内部循环检查当前行和列中的值 Price A，然后是 Price B，最后一个循环中的值 Price C。如果我们在 Price 列中找到值，则继续复制单元格。如果没有插入 Price，则不执行任何操作，而是转到下一个 Price 列

  • 第 5 行 | 计算计数器以了解我们已经复制了多少行，
    因此我们知道在哪一行之后可以复制当前行

  • 第 6 行|复制姓名列

  • 第 7 行|复制描述列

  • 第 8 行 | 根据当前内循环，复制价格 A 或 B 或 C 列

  • 第 9 行 | 根据当前内循环，复制 SKU A 或 B 或 C 列

结果截图

在此处输入图片描述

答案2

这是工作表函数解决方案。公式有点复杂，所以请注意，但这会满足您的要求。

脚步：

  1. 在新表的第一行中，在 下Name，输入对数据中第一个的直接引用Name。在您的示例中，您将输入=A2A2 是数据中列出的第一个名字。在下面我提供的示例屏幕截图中，此公式进入A8。所有后续公式都将遵循屏幕截图中使用的布局。当然，您必须更新所有范围引用以匹配您的工作表。
  2. 在下面的单元格中输入以下公式：
    =IF(COUNTIF($A$9:A9,A9)=COUNTA(OFFSET($C$1:$E$1,MATCH(A9,$A$2:$A$5,0),0)),INDEX($A$2:$A$5,MATCH(A9,$A$2:$A$5,0)+1),A9)
    这基本上会检查上面列出的名称应该有多少行（在 中A9），如果新表中已有的行数与此匹配，则转到下一个名称。如果不匹配，则会为上面的名称添加另一行。
    根据需要向下填充此公式（直到它返回 0 而不是名称）。
  3. 在第一行Description输入以下公式并向下填写。
    =INDEX($B$2:$B$5,匹配(A9,$A$2:$A$5,0))
  4. 在第一行中SKU，将以下公式粘贴到公式栏中，然后按Ctrl+ Shift+ Enter。
    =INDEX(OFFSET($A$1:$H$1,MATCH(A9,$A$2:$A$5,0),0),SMALL(IF(OFFSET($F$1:$H$1,MATCH(A9,$A$2:$A$5,0),0)<>"",COLUMN($F$1:$H$1)),COUNTIF($A$9:$A9,$A9)))
    这是一个数组公式；如果输入正确，公式将出现在公式栏中，并被大括号括起来。将此公式填入表格中（每个实例也应出现在大括号中）。
  5. 类似地，在 下的第一个行中Price，将以下公式粘贴到公式栏中并将其作为数组公式输入（通过按++ Ctrl）。ShiftEnter
    =INDEX(OFFSET($A$1:$H$1,MATCH($A9,$A$2:$A$5,0),0),SMALL(IF(OFFSET($C$1:$E$1,MATCH($A9,$A$2:$A$5,0),0)<>"",COLUMN($C$1:$E$1)),COUNTIF($A$9:$A9,$A9)))
    填写完毕，您的表格就完成了。

表格截图

相关内容