☰

Excel - 基于公式将逗号分隔的值转置为单独的行

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2013
Excel - 基于公式将逗号分隔的值转置为单独的行

我将 CD/DVD 库存保存在 Excel 文件 (Excel 2013) 中。数据按以下快照所示组织。

每行中第一个单元格提到 CD 标签，下一个单元格包含我在其上写的应用程序的逗号分隔列表。

不过，我对这样一种视图感兴趣：将逗号分隔的值转置到行，以便每个单元格和每行只有一个项目，如下所示。

我明白，VBA 可以轻松提供帮助，而且我确实具备基本的 VBA 知识，但是是否有基于公式的解决方案可以将数据从输入转置到输出？我想避免使用宏，也想避免手动将文本转置到列，然后进行复制粘贴转置。

它还应该修剪逗号后面的多余空格（如果存在）。

在此处输入图片描述

答案1

要使用公式来执行此操作，对于比 Office 365 Excel 更旧的版本，您将需要一个辅助列。

在逗号分隔列表旁边（我的列表从 C2 开始）输入：

=LEN(C1)-LEN(SUBSTITUTE(C1,",",""))+1+D1

这将保持单词数量的累计总数。

在此处输入图片描述

然后，您使用 INDEX/MATCH 引用这两列，以便在长公式被拖下时引用正确的单元格。

长公式是：

=TRIM(MID(SUBSTITUTE(INDEX(C:C,MATCH(ROW(1:1),D:D)),",",REPT(" ",99)),(ROW(1:1)-INDEX(D:D,MATCH(ROW(1:1),D:D)))*99 +1,99))

然后两个 INDEX/MATCH 返回要处理的正确单元格。

SUBSTITUTE 添加了大量空白，为 Mid 提供了一个很大的目标。

Mid 将白色空间中的长字符串分割开。

TRIM 删除了多余的空白。

在此处输入图片描述

使用 Office 365，如果字符串不太长，您可以跳过辅助列并使用 TEXTJOIN()。

=TRIM(MID(SUBSTITUTE(TEXTJOIN(",",TRUE,$C$2:$C$5),",",REPT(" ",99)),(ROW(1:1)-1)*99+1,99))

这将用 TEXTJOIN 替换 INDEX/MATCH，否则其作用与上述相同。

在此处输入图片描述

答案2

我想建议一种简单的 VBA（宏）方法，它非常易于使用并且比任何繁琐的公式都要好。

在此处输入图片描述

Private Sub CommandButton1_Click()

    Dim xArr() As String
    Dim xAddress As String
    Dim Rg As Range
    Dim Rg1 As Range

    On Error Resume Next

    xAddress = Application.ActiveWindow.RangeSelection.Address

    Set Rg = Application.InputBox("please select the data range:", "Column to Row", xAddress, , , , , 8)

    If Rg Is Nothing Then Exit Sub

    Set Rg = Application.Intersect(Rg, Rg.Parent.UsedRange)

    If Rg Is Nothing Then Exit Sub

    Set Rg1 = Application.InputBox("please select output cell:", "Column to Row", , , , , , 8)

    If Rg1 Is Nothing Then Exit Sub
    xArr = Split(Join(Application.Transpose(Rg.Value), ","), ",")
    Rg1.Resize(UBound(xArr) + 1) = Application.Transpose(xArr)
    Rg1.Parent.Activate
   Rg1.Resize(UBound(xArr) + 1).Select

Call TrimXcessSpaces

End Sub

怎么运行的：

  • 点击开发者标签， 打设计然后插入。
  • 选择命令按钮从活动窗口类别并在工作表上的任意位置绘制。
  • 右键点击命令按钮并选择财产。
  • 寻找标题从列表中将其更改为列至行。
  • 关闭属性窗口并返回工作表。
  • 双击命令按钮，您将进入 VB 编辑器窗口。
  • 复制粘贴本守则私有子命令按钮1_Click() 和子目录结束并点击保存图标。
  • 从VB 编辑器窗口点击文件命令并点击关闭并返回 Microsoft Excel。
  • 点击设计模式转动它离开。

现在单击 Sheet 命令按钮：

  • 第一的输入框会出现然后选择列表以逗号分隔 (B4:B6)然后单击“Ok”完成。

  • 第二输入框将出现选择任意单细胞就像我一样E4 并单击“确定”按钮完成。

您可以在“行”中找到如屏幕截图所示的列表。

注意： 根据需要调整单元格引用。

編輯：

我已经编辑了这篇文章彼得·H建议我删除白色空间从输出中E 列。

  1. 形成单张纸压机ALT+F11，打开 VB 编辑器。
  2. 从左侧的项目资源管理器窗口中找到工作表名称。
  3. 选择 ＆右键点击工作表名称。
  4. 寻找插入从菜单中，点击模块命令。

  5. 复制并粘贴此代码&节省返回工作表。

     Option Explicit

 Sub TrimXcessSpaces()

  Dim cl As Variant

  For Each cl In Selection
     If Len(cl) > Len(WorksheetFunction.Trim(cl)) Then
         cl.Value = WorksheetFunction.Trim(cl)
     End If
  Next cl
End Sub

笔记， 在之前添加此代码子目录结束在前面的代码中，（检查上面的代码）。

Call TrimXcessSpaces
  1. 从工作表中单击命令按钮，输出将如下所示。

在此处输入图片描述

答案3

建议的答案实际上对我不起作用。我的数据很乱，有重复的数据，这导致它崩溃。所以我想出了一个不同的解决方案： =TRIM(TRANSPOSE(TEXTSPLIT(TEXTJOIN(",",TRUE,C2:C5),",")))

工作原理：

  1. TEXTJOIN 将所有数据放在一个以逗号分隔的长列表中。
  2. 然后，TEXTSPLIT 将每个项目分离出来，形成一个项目数组。
  3. TRANSPOSE 使这些项目出现在一列中。
  4. TRIM 删除所有多余的空格。

如果您想要进一步过滤数据，您可以用其他过滤命令包围列表，例如UNIQUE。

如果您不想用逗号分隔，您可以随时将TEXTJOIN和中的分隔符更改TEXTSPLIT为您想要的任何内容。

相关内容