EXCEL中如何显示常见数值？

Question

是否可以显示列中的常见值？

我想在不同的表单中显示常用值。例如，字母“a”在第 1、2 和 4 列中很常见。

Answer 1

如果您有Power Query（又名Get & Transform），那么它就相当简单了。

• 选择表格中的某个单元格
• Data => Get & Transform => from Table/Range
• 在Power QueryUI 中，导航到Home => Advanced Editor您在那里看到的代码并将其替换为下面的代码。
• 要了解发生了什么，请选择窗口中的步骤Applied Steps并观察结果。还请阅读代码中的注释

主要步骤是

• 取消透视列，从而得到一个有两列表。
• 然后按“品牌”分组并提取结果“商店”
• 最后转置表格，使“商店”成为列标题。

手动添加了各种代码，以便轻松计算添加/减去行和列，并避免使用固定的列名。

M 代码

let
    Source = Excel.CurrentWorkbook(){[Name="brandsTbl"]}[Content],

//Unpivot => 2 columns
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Store", "Brand"),


    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Brand"}, {{"Grouped", each _, type table [Store=text, Brand=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Sort(Table.Column([Grouped],"Store"))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Grouped"}),
    
//Add column so we can tell how many columns we will need to split the results into
    colCount = Table.AddColumn(#"Removed Columns", "Count", each List.Count([Custom])),

#"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),
      List.Max(Table.Column(colCount,"Count"))),
    
    
    #"Sorted Rows" = Table.Sort(#"Split Column by Delimiter",{{"Brand", Order.Ascending}}),
    #"Transposed Table" = Table.Transpose(#"Sorted Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    
//change all columns to type text
colNames = Table.ColumnNames(#"Promoted Headers"),
changeTypeList = List.Zip({colNames,List.Repeat({type text}, List.Count(colNames))}),
    
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", changeTypeList)
in
    #"Changed Type"