我需要将多行不均匀的组转换为单行
我尝试从多行中提取所需数据到多列。但所有数据都放在不同的行下(与输入行相同)。
但我需要按照下面的截图将结果放在单行中。请提出您的想法...
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
样本格式
必填数据:
- 序号 2. 零件编号 3. 数量
可选数据:(有时会空白)
- 描述 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