Excel - 建立相邻单元格文本的直方图

Excel - 建立相邻单元格文本的直方图

我有一个包含矩形单元格集的 Excel 电子表格。把它们想象成图块。图块的边框上有文本标记,并且图块在电子表格中没有重叠。以这些图块为例:

示例图块

还请考虑此示例图块集,其中上一个示例中的两个图块在 2D 空间中以某种方式排列。请注意,这配置了两个相邻的单元格文本实例,即White - BlueGreen - Gray相邻。 在此处输入图片描述

请注意,如果对相邻文本进行两次计数,则没有区别(例如,白色和蓝色相邻,但蓝色和白色相邻)。

我想自动创建一个直方图,在电子表格的某个区域内查找相邻的文本,并创建某种列表,列出哪些颜色彼此相邻。在上面的例子中,我希望得到的结果为白色与蓝色相邻,绿色与灰色相邻(不一定是出现多少次,但至少它们确实出现了)。

我怎样才能实现这个目标?

答案1

这可以使用 Power Query 完成。这是一个完整的示例,它创建一个表格,列出相邻颜色对的每次出现,包括重复项。将其转换为频率分布表还需要几个步骤。

这是我的瓷砖样品:

瓷砖


选择包含所有图块的单元格范围,包括额外的空行和空列都不是问题。为此选择定义一个命名范围:

定义名称


打开 Power Query 编辑器并打开一个空白查询。打开高级编辑器并输入以下 M 代码(我已重命名这些步骤以更好地描述它们):

let
    Source = Excel.CurrentWorkbook(){[Name="Tiles"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Unpivoted All Columns Except Index" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Extracted Digits From Column Numbers" = Table.TransformColumns(#"Unpivoted All Columns Except Index", {{"Attribute", each Text.AfterDelimiter(_, "n"), type text}}),
    #"Changed Data Type Attribute Column" = Table.TransformColumnTypes(#"Extracted Digits From Column Numbers",{{"Attribute", Int64.Type}}),
    #"Added Custom Column Equal to Attribute-1" = Table.AddColumn(#"Changed Data Type Attribute Column", "Custom", each [Attribute] - 1, Int64.Type),
    #"Merged Query With Itself" = Table.NestedJoin(#"Added Custom Column Equal to Attribute-1", {"Index", "Attribute"}, #"Added Custom Column Equal to Attribute-1", {"Index", "Custom"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Query With Itself", "Added Custom", {"Value"}, {"Value.1"}),
    #"Filtered Rows To Keep Only The Color Pairs" = Table.SelectRows(#"Expanded Added Custom", each [Value.1] <> null and [Value.1] <> ""),
    #"Removed Other Columns" = Table.RemoveColumns(#"Filtered Rows To Keep Only The Color Pairs",{"Index", "Attribute", "Custom"})
in
    #"Removed Other Columns"

以下是 Power Query 编辑器窗口中的结果:

颜色对


如果需要,您可以在 Power Query 中进一步处理该表(例如,删除重复的行)或将其加载到工作表中的任何位置。

相关内容