您好，我有 Excel 中两列的信息，我需要将它们合并到一列上

Question

我在 Excel 中拥有的数据如下：

column a      column B   column c    column D

card type    card no        camp type      camp code
standard       20              CLASIC           1
CLASIC         30              standard         2
standard       40              CLASIC           3
CLASIC         50              standard         4
standard       60              CLASIC           5
CLASIC         70              CLASIC           6

我需要看起来像这样：

clasic  
    1;30
    3;30
    5;30
    6;30
    1;50
    3;50
    5;30
    6;30
standard    
    2;20
    4;20
    2;40
    4;40

有没有办法在 Excel 中做到这一点？

Answer 1

假设您显示的结果只是数据的部分结果，您可以使用 Windows Excel 2010+ 和 Office 365 中提供的 Power Query 来执行此操作。

此代码假设您将在card type和camp type表中拥有匹配的条目。如果没有，您将需要提供如何处理这种情况的逻辑。

使用 Power Query

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

M 代码

let

//Change name in next line to match the actual table name in workbook
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 //Split into two tables (card and camp)
 //Then do a  Left Outer Join
    cardTbl = Table.SelectColumns(Source,List.FirstN(Table.ColumnNames(Source),2)),
    campTbl = Table.SelectColumns(Source,List.LastN(Table.ColumnNames(Source),2)),
    joined = Table.Join(campTbl,"camp type",cardTbl,"card type"),

   #"Removed Columns" = Table.RemoveColumns(joined,{"card type"}),

//Type as Text in order to combine the number columns
   #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{
        {"camp type", type text}, {"camp code", type text}, {"card no", type text}}),

//Group by "type"
//Then Sort by card no and camp code to get into the desired order
    #"Grouped Rows" = Table.Group(#"Changed Type", {"camp type"}, {
        {"Sort", each 
            Table.CombineColumns(
                    Table.Sort(_,{{"card no", 
                        Order.Ascending},{"camp code",Order.Ascending}}),
                    {"camp code","card no"},Combiner.CombineTextByDelimiter(";"), "Result"), Table.Type}
    }),

//Add the "type" to the top of each grouped/combined table
//Then expand the table column
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Result", each 
        Table.FromList({[camp type]} & [Sort][Result], Splitter.SplitByNothing(),{"Result"})),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"camp type", "Sort"}),
    
    #"Expanded Result" = Table.ExpandTableColumn(#"Removed Columns1", "Result", 
        {"Result"}, {"Result"})
in
    #"Expanded Result"