如何在Excel的2列中取消4組不同的列？

我有一個特殊情況，將 8 個不同的列轉為 2 列。我在 excel School 有一張桌子，上面有四個科目的分數和百分比。

我在 Math、Bio、Social 和 Absent（計數）上使用 unpivot-selected 列將 12 列轉換為 11 和 16 行。

但是如何獲取 %age 的剩余 4 列以將列從 11 轉換為 7 列和 16 行？

請幫助如何取消旋轉。

謝謝，新B

uj5u.com熱心網友回復：

我只想添加一個自定義列。

這是從主頁 => 高級編輯器訪問的 M 代碼

檢查應用的步驟和代碼注釋以更好地理解它在做什么。

let

//change next line to reflect your actual data source (Table name)
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],

//set data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"School", type text}, {"Class", Int64.Type}, {"Group", type text}, {"EOM", type date}, 
        {"Math", Int64.Type}, {"Bio", Int64.Type}, {"Social", Int64.Type}, {"Absent", Int64.Type}, 
        {"Math %", Int64.Type}, {"Bio%", Int64.Type}, {"Social%", Int64.Type}, {"Absent%", Int64.Type}}),

//Unpivot the Scores columns (Math, Bio, Social, Absent)
//Note the resulting two columns are named Subject and Score
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", 
        {"School", "Class", "Group", "EOM", "Math %", "Bio%", "Social%", "Absent%"}, 
        "Subject", "Score"),

//Add an Index column so the Custom Column can refer to the correct row
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Columns", "Index", 0, 1, Int64.Type),

//Add Custom column to return the appropriate percentage
// This relies on the name similarity between Subject and Percentage: eg Math => Math %
// but you could also do a bunch of If Then statements
    #"Added Custom" = Table.AddColumn(#"Added Index", "%age", each 
        Table.Column(#"Added Index", List.Select(Table.ColumnNames(#"Unpivoted Columns"), 
            (li)=> Text.Contains(li,[Subject])){0}){[Index]}, Int64.Type),

//Remove the unwanted columns
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Math %", "Bio%", "Social%", "Absent%", "Index"})
in
    #"Removed Columns"

除了上面的，您可以在現有代碼的末尾選擇 to Add Conditional Column。對話框如下所示：

和 M 代碼：

let

//change next line to reflect your actual data source (Table name)
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],

//set data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"School", type text}, {"Class", Int64.Type}, {"Group", type text}, {"EOM", type date}, 
        {"Math", Int64.Type}, {"Bio", Int64.Type}, {"Social", Int64.Type}, {"Absent", Int64.Type}, 
        {"Math %", Int64.Type}, {"Bio%", Int64.Type}, {"Social%", Int64.Type}, {"Absent%", Int64.Type}}),

//Unpivot the Scores columns (Math, Bio, Social, Absent)
//Note the resulting two columns are named Subject and Score
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", 
        {"School", "Class", "Group", "EOM", "Math %", "Bio%", "Social%", "Absent%"}, 
        "Subject", "Score"),

//add Custom Column with multiple `if then else if` clauses
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "%age", each 
        if [Subject] = "Math" then [#"Math %"] 
        else if [Subject] = "Bio" then [#"Bio%"] 
        else if [Subject] = "Social" then [#"Social%"] 
        else if [Subject] = "Absent" then [#"Absent%"] 
        else null),

    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Math %", "Bio%", "Social%", "Absent%"})
in
    #"Removed Columns"

第二種方法的優點是發生的事情更明顯。但是，如果您添加/洗掉/更改課程和相應的百分比，則需要編輯代碼。有了第一個，你就不必在那個特定的步驟中了。您將需要在其他步驟中編輯代碼，但這些更容易更改為與課程名稱無關。

標籤：擅长力比转换不枢轴

上一篇：Excel：值與過濾器中相應列的DISTINCT值的總和

下一篇：是否可以通過查找兩個引數來創建一個對多列求和的公式？