使用 Python（或 Power Query）将不同行中的数据转置到同一列，但每隔一段时间

请确保您的初始数据从 Sheet1 的左上角开始 （A1=“Point”）。

在Excel中创建一个宏并将其命名为Sub change_format（），并将以下代码粘贴在“Sub change_format（）”和“End sub”之间。然后运行它，您将在 Sheet2 中获得结果。确保以 .xlsm 格式保存您的文件！！

i = 1 'First row of Sheet1 for reading
r = 2 'First row of Sheet2 for writing
   
'Find number of Points

While Sheet1.Cells(i, 1) <> ""
    
    i = i + 1

Wend

pnt = i - 2

For p = 1 To pnt 'Points
    For s = 2 To 7 'Species
                 
    cur_type = Sheet1.Cells(p + 1, 1) 'Current type
    cur_spc = Sheet1.Cells(1, s)      'Current species
    cur_val = Sheet1.Cells(p + 1, s)  'Current value
    
    Sheet2.Cells(1, 1) = "Point"
    Sheet2.Cells(1, 2) = "Species"
    Sheet2.Cells(1, 3) = "Value"

    Sheet2.Cells(r, 1) = cur_type
    Sheet2.Cells(r, 2) = cur_spc
    Sheet2.Cells(r, 3) = cur_val
    r = r + 1
    
    Next s
    
Next p

这也可以使用 Windows Excel 2010+ 和 Microsoft 365（Windows 或 Mac）中提供的 Power Query 完成

使用 Power Query

• 选择数据表中的某个单元格
• Data => Get&Transform => from Table/Range
• 当 PQ 编辑器打开时：Home => Advanced Editor
• 记下第 2 行中的表名
• 粘贴下面的 M 代码来代替您看到的内容
• 将第 2 行中的表名称改回最初生成的名称。
• 阅读评论并探索以了解算法Applied Steps

来源与命运

M代码

let

//Read in Source Table
//  Change depending on your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Source"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,
        {{"Point", type text}} & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source),1), each {_, Int64.Type})), 

//Unpivot to produce a three column table
    #"Unpivot Source" = Table.UnpivotOtherColumns(#"Changed Type",{"Point"},"Species","Value"),

//Read in Destiny Table
//  Change depending on your actual data source
    Source2 = Excel.CurrentWorkbook(){[Name="Destiny"]}[Content],

//Add Index Column for Sorting
    #"Added Index" = Table.AddIndexColumn(Source2, "Index", 0, 1, Int64.Type),
    #"Destiny Typed" = Table.TransformColumnTypes(#"Added Index",{
        {"Point", type text}, {"Species", type text},{"Value", type any}}),

//Join the two tables
    joined = Table.NestedJoin(#"Destiny Typed",
        {"Point","Species"},#"Unpivot Source",{"Point","Species"},"Join",JoinKind.FullOuter),

//Replace the null Values
    #"Replace null Values" = Table.ReplaceValue(
        joined,
        each [Value],
        each if [Value] = null then [Join][Value]{0} else [Value],
        Replacer.ReplaceValue,
        {"Value"}
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Replace null Values",{"Join"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns1"

结果

注意：实际上，您可以从一个包含每个点的 Colletor 和 Habitat 的源表开始，然后生成整个 Destiny 表。

例如，如果您的表如下所示：Source

然后，一个简单的表格将生成：UnPivotDestiny

et

//change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Source14"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,
        {{"Point", type text}, {"Colletor", type text}, {"Habitat", type text}}
        & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source),3), each {_, Int64.Type})),

//Unpivot all except the Point Column
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Point"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

生产=>

如果这是可行的，可以很容易地修改代码以包含“空白”条目以及所需的排序顺序。