☰

Excel：两列中分隔的数据，匹配列之间的元素

tag-icon • tag-icon microsoft-excel
Excel：两列中分隔的数据，匹配列之间的元素

大家好，提前感谢你们的见解

我的 Excel 数据如下所示

Cell A1               Cell B1
x | y | z             1 | 2 | 3

我需要对两者进行匹配并创建如下数据集：

x             1
y             2
z             3

希望这有点道理...

起始数据超过 20K 行。单元格 A1 和 B1 中的数据都是分隔的，但它们并不总是包含相同数量的元素，但它们始终具有相同数量的元素它们之间， 例如：

    Cell A2                    Cell B2
    d | e                       5 | 6
    
    Cell A3                   Cell B3
 f | g | h | i              7 | 8 | 9 | 10

我最终需要修改所有数据，使其看起来像下面这样 - 例如，将字母和数字放在单独的列中

Column H       Column I
 x                  1
 y                  2
 z                  3    
 d                  5
 e                  6
 f                  7
 g                  8
 h                  9
 i                 10

希望这能让你理解！我自己都搞糊涂了……

我尝试过将文本切换为列，并尝试使用数据透视表，但没有成功。任何提示都将不胜感激！

编辑：应该补充一点，以上是虚拟数据，我的真实数据不是固定长度的。

edit2（非常抱歉）-我附上了一张截图，希望能更好地解释我需要什么……前两行是我所拥有的，从第 7 行开始在截图中正是我需要的。

答案1

并不理想，但这是一种实现任务的非 vba 方法。

首先，确定数据中可以看到的最大值数量。假设是 4。在当前的 A 列和 B 列之间插入 4+1 (5) 列。

其次，使用文本到列向导来分隔数据（您有条形分隔符，因此通过向导可以轻松实现）。对两个数据集执行此操作。因此，每个单元格将具有唯一的值。

现在您应该有两“组”数据。

选择集合 1 (A1:D3)，然后右键单击并将名称定义为“first_value”。

选择集合 2 (F1:I3)，然后右键单击并将名称定义为“second_value”。

接下来输入以下公式：

L1: =INDEX(first_value,1+INT((ROW(A1)-1)/COLUMNS(first_value)),MOD(ROW(A1)-1+COLUMNS(first_value),COLUMNS(first_value))+1)

M1: =INDEX(second_value,1+INT((ROW(F1)-1)/COLUMNS(second_value)),MOD(ROW(F1)-1+COLUMNS(second_value),COLUMNS(second_value))+1)

将公式向下拖动到您需要的位置，直到收到 #REF 错误。

现在您需要决定如何处理空行。它们目前在 L 和 M 列中都有 0。您可以将这些完整的列复制到另一张表，粘贴为特殊值以删除公式，然后对这些 0 执行额外的清理。排序和删除是一种选择。只要 0 0 的组合永远不是真实数据，那么这可能是删除它的最简单方法。如果您的实际数据中恰好有 0 0，那么您需要修改公式。

在此处输入图片描述

答案2

请尝试下面的代码。

使用方法 - 选择要转换的单元格范围。选定的范围必须有 2 列。

Option Explicit

Public Sub SplitAndPivot()

Dim r As Range
Dim msg_result As Integer
Dim msg_title As String
Dim exit_suffix As String
Dim rw As Range
Dim nms As String
Dim ids As String
Dim new_sheet As Worksheet
Dim new_sheetname As String
Dim new_sheetrow As Integer
Dim arr_nms() As String
Dim arr_ids() As String
Dim arr_i As Integer
Dim ws As Worksheet
Dim sheet_prefix As String

sheet_prefix = "SplitAndPivot"
msg_title = "Split and pivot"
exit_suffix = vbCrLf & "No transformation done."


msg_result = MsgBox("Do you want to remove " & sheet_prefix & " sheets created earlier?", vbYesNoCancel, msg_title)

If msg_result = 6 Then

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For Each ws In ThisWorkbook.Worksheets
        If Left(ws.Name, Len(sheet_prefix)) = sheet_prefix Then
            ws.Delete
        End If
    Next ws
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

ElseIf msg_result = 2 Then
    MsgBox "You selected Cancel or closed the message box." & exit_suffix, vbInformation, msg_title
    Exit Sub
End If


msg_result = MsgBox("Have you selected the cells you want to transform?", vbYesNoCancel, msg_title)

'Yes = 6
'No = 7
'Cancel = 2
'X = 2

If msg_result <> 6 Then
    MsgBox "You selected No, Cancel or closed the message box." & exit_suffix, vbInformation, msg_title
    Exit Sub
End If

Set r = Selection

If r.Columns.Count <> 2 Then
    MsgBox "You must select a range with two columns." & exit_suffix, vbCritical, msg_title
    Exit Sub
End If

Application.ScreenUpdating = False


new_sheetname = sheet_prefix & "_" & Format(Now(), "YYYYMMDD_HHmmSS")

Set new_sheet = ThisWorkbook.Worksheets.Add

With new_sheet
    .Name = new_sheetname
    .Cells(1, 1) = "Name"
    .Cells(1, 2) = "ID"
    .Cells(1, 3) = "SourceRange"
End With

new_sheet.Name = new_sheetname

new_sheetrow = 2


'Iterate through each selected row, split the values and pivot them on to the new sheet
For Each rw In r.Rows

    nms = rw.Cells(1, 1)
    ids = rw.Cells(1, 2)
    
    Debug.Print "Processing:", rw.Address, nms, ids
    
    arr_nms = Split(nms, " | ")
    arr_ids = Split(ids, " | ")
    
    If UBound(arr_nms) <> UBound(arr_ids) Then
        Application.ScreenUpdating = True
        MsgBox "Number of ID elements is different to number of Name elements in range " & rw.Address & exit_suffix, vbCritical, msg_title
        Exit Sub
    End If
    
    For arr_i = 0 To UBound(arr_nms)
        Debug.Print arr_i, arr_nms(arr_i), arr_ids(arr_i)
        With new_sheet
            .Cells(new_sheetrow, 1) = arr_nms(arr_i)
            .Cells(new_sheetrow, 2) = arr_ids(arr_i)
            .Cells(new_sheetrow, 3) = rw.Address
        End With
        new_sheetrow = new_sheetrow + 1
    Next arr_i
    
Next rw

Application.ScreenUpdating = True

MsgBox "Finished transforming selected range.", vbOKOnly, msg_title

End Sub

答案3

这也可以通过 Power Query 来实现，可在 Windows Excel 2010+ 和 Excel 365（Windows 或 Mac）中使用

使用 Power Query

  • 在数据表中选择一些单元格
  • Data => Get&Transform => from Table/Range或者from within sheet
  • 当 PQ 编辑器打开时：Home => Advanced Editor
  • 记下表格姓名在第 2 行
  • 将下面的 M 代码粘贴到您所看到的位置
  • 将第 2 行的表名改回最初生成的表名。
  • 阅读评论并探索Applied Steps以了解算法
let

//change next line to reflect actual data source in your workbook
    Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col A", type text}, {"Col B", type text}}),

//create a "List" from each column
//then create a "Table" from the two lists
    Result = Table.FromColumns(
                {List.Combine(List.Transform(#"Changed Type"[Col A], each Text.Split(_,"|")))} & 

                //Transform Col B values to numbers depending on your actual data
                {List.Transform(
                    List.Combine(List.Transform(#"Changed Type"[Col B], each Text.Split(_,"|"))), each Number.From(_))},
                type table[Col A=text, Col B=Int64.Type])
in
    Result

在此处输入图片描述

相关内容