☰

将多行转置为单行 | MS EXCEL

tag-icon • tag-icon microsoft-excel worksheet-function vba microsoft-excel-2016 power-query
将多行转置为单行 | MS EXCEL

我需要将多行不均匀的组转换为单行

我尝试从多行中提取所需数据到多列。但所有数据都放在不同的行下（与输入行相同）。

但我需要按照下面的截图将结果放在单行中。请提出您的想法...

seq.    Criteria
1   Numbers Only
2   1st charater  = Text & last  charater =Number, charater Length = 14 to 20
3   Always 3rd Row, Last charater is Letter
4   First charater = "["
5   First 2 Character = "XY"
6   Must contain the word = "Quantity"
7   Criteria 1 = <> "['   & Criteria 2 = 4th row value in each data set
8   if Helper column had the word = "Replace"



ID  Data    sequence
10  10  1
10  A1  2
10  A2  3
10  A3  4
10  A4  5
10  A5  6
10  A6  7
10  A7  8
20  20  1
20  B1  2
20  B2  3
20  B3  4
20  B4  5
20  B5  6
20  B6  7
20  B7  8
30  30  1
30  C1  2
30  C2  3
30  C3  4
30  C8  5
30  30  1
30  D1  2
30  D2  3
30  D3  4
30  D4  5
30  D5  6
30  D6  7



Required output                             
10  A1  A2  A3  A4  A5  A6  A7  A8
20  B1  B2  B3  B4  B5  B6  B7  B8
30  C1  C2  C3                  C8
30  D1  D2  D3  D4  D5  D6

在此处输入图片描述

样本格式

必填数据：

  1. 序号 2. 零件编号 3. 数量

可选数据：（有时会空白）

  1. 描述 2.注释 3.组 4.替换 5.章节

因此输入行数据将动态变化

在此处输入图片描述

答案1

这也可以使用 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以了解算法

我编写了一个自定义函数来确定数据的类型。但是，你实际上没有为“Notes”定义类型，因此我将其用于不属于任何其他模式的任何格式

对于自定义函数，进入 Power Query 编辑器后，右键单击“查询”窗口以创建新查询；按照代码中所述重命名（参见右侧窗格中的属性=>名称）

请注意，PQ 区分大小写。如果您需要不区分大小写，则需要更改代码（例如，如果您的数据中可能同时包含Quantity和）QUANTITY

自定义函数

//Rename fnPattern

(val as any)=>

let
    txt = Text.From(val),
    Source = Text.ToList(txt),
    

//S. No
    seq1 = List.AllTrue(List.Transform(Source, each not (try Number.From(_))[HasError])),

//Part #
    seq2 = not List.AllTrue(List.Transform(Source, each not (try Number.From(_))[HasError]))
            and not (try Number.From(List.Last(Source)))[HasError]
            and List.Count(Source) >=14
            and List.Count(Source) <=20
            and not Text.Contains(txt,"Replace")
            and not Text.StartsWith(txt,"XY"),

//Description
    seq3 = List.Contains({"A".."Z"}, List.Last(Source)),

//Group
    seq4 = List.First(Source) = "[",

//Section
    seq5 = Text.StartsWith(txt,"XY"),

//Qty
    seq6 = Text.Contains(txt, "Quantity"),

//?
    seq7 = null, 

//Replace
    seq8 = Text.Contains(txt,"Replace"),

    seq = {seq1,seq2,seq3,seq4,seq5,seq6,seq7,seq8},
    colNames = {"S.NO","Part#","Description","Group","Section","Qty","Notes","Replacements"},
    colName = try colNames{List.PositionOf(seq,true)} otherwise "Notes"
    
in
   colName

主要查询

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Parts"]}[Content],

//Call custom function to determine type of entry
#"Invoked Custom Function" = Table.AddColumn(Source, "Col", each fnPattern([Input]), type text),

//Assume that first line for EVERY group is the S.NO
//Add a column to group each set of data
    #"Added Index" = Table.AddIndexColumn(#"Invoked Custom Function", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Grouper", each if [Col] = "S.NO" then [Index] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns",{"Grouper"}),

//Group by each data set
//then Pivot each subgroup (with no aggreagation
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Grouper"}, {
        {"Pivot", each Table.Pivot(Table.RemoveColumns(_,"Grouper"), List.Distinct(_[Col]), "Col","Input" ),
            type table[S.NO=Int64.Type, #"Part#"= text, Description=text, Replacements=text, Group=text, Section=text, Qty=text, Notes=text]
        } }),

//remove unneeded column
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"Grouper"}),

//expand the column of pivoted tables
//then re-arrange the columns into the desired order
    #"Expanded Pivot" = Table.ExpandTableColumn(#"Removed Columns1", "Pivot", 
        {"S.NO", "Part#", "Description", "Replacements", "Group", "Section", "Qty", "Notes"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Pivot",
        {"S.NO", "Part#", "Description", "Notes", "Group", "Replacements", "Section", "Qty"})
in
    #"Reordered Columns"

输入
在此处输入图片描述

输出
在此处输入图片描述

答案2

不确定你的情况是否如此......

在此处输入图片描述

由于“螺钉/螺母”和“m10x25”（黄色填充）的模式不太清楚，所以我只需添加带有“Desc”和“Note”的输入数据即可满足条件。

该子程序假设在 INPUT 列下，第一个数据值始终为数字，最后一个数据值始终不是数字。在数据集本身中，第一个值是数字，其余值不是数字。

例如：
5,A0076...34,Desc:bla,[test]...47,quant:123,replace:blo,XY:123 ---> 正确的数据集，因为它总是以带有数字的行开头，然后下一行不是数字。

11,replace:blo,[zzz] ... 14,23,Desc ---> 数据集不正确，因为有两行连续的数字值（14 和 23）。

A23,Quantity:3,Desc... 10,[yyy],XY=456 ---> 数据集不正确，因为它不是以数字开头 (A23)。

Sub test()

'addr = Range("A2", Range("A2").End(xlDown)) _
'    .SpecialCells(xlConstants, xlNumbers).Address

For Each cell In Range("A2", Range("A2").End(xlDown))
If Application.IsNumber(cell.Value) Then addr = addr & "," & cell.Address
Next
addr = Right(addr, Len(addr) - 1)

cnt = Len(addr) - Len(Application.Substitute(addr, ",", "")) + 1

Set rgR = Range("c" & Rows.Count).End(xlUp).Offset(1, 0)

For i = 1 To cnt

    If i = cnt Then
        Set rgC = Range(Split(addr, ",")(i - 1))
        Set rgC = Range(rgC, rgC.End(xlDown))
    Else
        Set rgC = Range(Split(addr, ",")(i - 1), Split(addr, ",")(i))
        Set rgC = rgC.Resize(rgC.Rows.Count - 1, 1)
    End If
    'rgC.select 'uncomment to see the result of rgC when step run
    For Each cell In rgC
        Select Case True
        Case InStr(cell.Value, "Replace"): rgR.Offset(0, 5).Value = cell.Value
        Case Left(cell.Value, 2) = "XY": rgR.Offset(0, 6).Value = cell.Value
        Case application.isnumber(cell.Value): rgR.Value = cell.Value
        Case InStr(cell.Value, "["): rgR.Offset(0, 4).Value = cell.Value
        Case InStr(cell.Value, "Quantity") = False And _
                InStr(cell.Value, "XY") = False And _
                InStr(cell.Value, "Note") = False And _
                application.isnumber(Left(cell.Value, 1)) = False And _
                application.isnumber(Right(cell.Value, 1)) = True
            rgR.Offset(0, 1).Value = cell.Value
        Case InStr(cell.Value, "Quantity"): rgR.Offset(0, 7).Value = cell.Value
        Case InStr(cell.Value, "Note"): rgR.Offset(0, 3).Value = cell.Value
        Case InStr(cell.Value, "Desc"): rgR.Offset(0, 2).Value = cell.Value
        End Select
    Next
    
Set rgR = rgR.Offset(1, 0)
Next i

End Sub

addr 是每个带有数字值的单元格的地址。cnt
是 addr 中单元格的数量。rgR
是放置结果的起始单元格。

然后从1开始循环，直到cnt为止，将rgC作为集合的范围，然后在rgC内循环，检查循环的单元格值符合哪种情况，然后根据哪一列放置循环的单元格值。

将（循环单元格的）值放到哪一列输出的情况：
数字 ---> col C
包含“replace” ---> col H
包含“quantity” ---> col J
以“[”开头 ---> col G
包含“desc” ---> col E
以“XY”开头 ---> col I
以字母开头，以数字结尾但不包含“XY”，不包含“replace”，不包含“note”，不包含“quantity” ---> col D
包含“note” ---> 列 F

相关内容