excel - 将每个数据集转换为 1 行

Question

我尝试使用 VBA 在 Excel 上创建一个宏，它将每个数据集仅转换为 1 行。
表的初始格式

No.               1
Code              A1B1
Date              10/25/21
No.               2
Code              A2B2
Date              10/26/21

结果 Sheet2中应该是这样的

No.       Code          Date
1         A1B2        10/25/21
2         A2B2        10/26/21

我试图复制包含“No.”的行。但我希望将其提取到单元格 B2。我应该从哪里开始？

    Sub CopyNo()
  Dim LastRow As Long
  LastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
  With Worksheets("Sheet2").Range("A1:A" & LastRow)
    Worksheets("Sheet1").Range("A1:A" & LastRow).EntireRow.Copy .Range("A1")
    With .Offset
      .Replace "No.", "=No.", xlPart
      On Error Resume Next
      .Offset(1).SpecialCells(xlConstants).EntireRow.Delete
      On Error GoTo 0
      .Replace "=No.", "No.", xlPart
    End With
  End With
End Sub

Answer 1

这是一种数据透视表，但没有聚合结果(不能在 Excel 的常规数据透视表中完成)
但您可以在 Windows Excel 2010+ 和 Office 365 中提供的 Power Query 中执行此操作
使用 Power Query

在数据表中选择一些单元格

Data => Get&Transform => from Table/Range

当 PQ 编辑器打开时:Home => Advanced Editor

记下表格 姓名 在第 2 行

粘贴下面的 M 代码代替您看到的

将第 2 行中的表名称更改回最初生成的名称。

阅读代码中的注释并探索 Applied Steps了解算法

M码

let

//Read in the data
//Change table name in next line to actual table name in your worksheet
    Source = Excel.CurrentWorkbook(){[Name="Table22"]}[Content],

//Custom function
//see credits for explanation
    pivot = fnPivotAll(Source, "Column1","Column2"),

//set data types
    #"Changed Type" = Table.TransformColumnTypes(pivot,{
            {"No.", Int64.Type}, 
            {"Code", type text},
            {"Date", type date}})
in
    #"Changed Type"

请注意，您需要为此解决方案使用自定义函数

进入自定义函数:

Data => GetData => From other sources => Blank Query

当 PQ 编辑器打开时:Home => Advanced Editor

用下面的代码替换代码

在右侧菜单上，将查询名称更改为 fnPivotAll

请参阅学分中的链接以了解算法和这样做的原因。

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/
//rename fnPivotAll

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

`