Excel 计算不重复一个变量的唯一组合

Question

例如，我有一个按教室类型分组的数据列表，每个教室类型都有两个配对的变量。我需要计算这些变量有多少个独特的组合，以及哪些变量不重复。

For example,
ClassRoom   Color Option    Seat
Classroom 1 Green   3
Classroom 1 Blue    3
Classroom 1 Red     4
Classroom 1 Yellow  5
Classroom 1 Orange  6
Classroom 1 Purple  6
Classroom 1 Black   8
Classroom 2 Green   2
Classroom 2 Pink    2
Classroom 2 Red     4
Classroom 2 White   6
Classroom 2 Yellow  6
Classroom 2 Purple  8
Classroom 2 Black   8

在教室 1 中，有独特的颜色选项，但当与座位号配对时，座位号变量会在某些组合中重复。我希望有一种方法可以将绿色座位 3 算作独特配对，而将蓝色座位 3 算作重复配对，因为座位变量重复。颜色选项在同一教室中重复的情况也可能如此。

再说一次，我的数据集包含不同教室的多个分组，因此我需要一种方法来汇总按教室划分的唯一和重复的配对计数。

Answer 1

仍然不确定您希望如何格式化输出。

选项1

您可以使用公式来确定计数。

我认为重新表述你的问题的一种方法是寻找

• 教室和座位号的独特组合
• 重复数就是总席位数与唯一席位数之间的差值。

在具有动态公式的 Excel/O365 中，使用表格和结构化引用，您可以使用以下输出格式：

Unique:  =COUNT(UNIQUE(INDEX(FILTER(tblClassRoom,tblClassRoom[ClassRoom]=$J2),0,3)))
Duplicates: =COUNTIF(tblClassRoom[ClassRoom],$J2)-COUNT(UNIQUE(INDEX(FILTER(tblClassRoom,tblClassRoom[ClassRoom]=$J2),0,3)))

如果您使用的早期版本的 Excel 缺少这些功能，则可以使用以下可能需要使用 CSE 输入的数组公式（即：按住ctrl+shift并点击enter）：

Unique:  =SUM(N(FREQUENCY(IF(tblClassRoom[ClassRoom]=$J2,tblClassRoom[Seat]),IF(tblClassRoom[ClassRoom]=$J2,tblClassRoom[Seat]))>0))
Duplicates: =COUNTIF(tblClassRoom[ClassRoom],$J2)-SUM(N(FREQUENCY(IF(tblClassRoom[ClassRoom]=$J2,tblClassRoom[Seat]),IF(tblClassRoom[ClassRoom]=$J2,tblClassRoom[Seat]))>0))

选项 2

您还可以使用Power QueryExcel 2010+ 中的功能，输出显示唯一和重复条目的列表。

• 将表格输入PQ后

  • Data → Get & Transform → From Table/Range

  • 按教室和座位分组

  • 操作：所有行 （无聚合）

---

• 然后添加一个自定义列，提取颜色选项柱子
  • 公式：=Table.Column([Grouped],"Color Option")

• 在结果列的顶部List，有一个双向箭头。单击该箭头并选择使用逗号分隔符提取值。
• 最后，删除不需要的列并重新排列。

这样就生成了每个教室和座位可用的各种颜色选项的列表：

以上所有操作都可以通过 UI 完成，但这里是生成的 M 代码：

M 代码

let
    Source = Excel.CurrentWorkbook(){[Name="tblClassRoom"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClassRoom", type text}, {"Color Option", type text}, {"Seat", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ClassRoom", "Seat"}, {{"Grouped", each _, type table [ClassRoom=text, Color Option=text, Seat=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Color Options", each Table.Column([Grouped],"Color Option")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Color Options", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Grouped"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ClassRoom", "Color Options", "Seat"})
in
    #"Reordered Columns"

选项 3

或者，如果您只想标记原始表中的每个条目是否是唯一的还是重复的，则可以使用以下公式：

=IF(COUNTIFS($A$2:A2,A2,$C$2:C2,C2)>1,"Duplicate","Unique")

或者，如果您想使用带有结构化引用的表：

=IF(COUNTIFS(tblClassRoom[[#Headers],[ClassRoom]]:[@ClassRoom],[@ClassRoom],tblClassRoom[[#Headers],[Seat]]:[@Seat],[@Seat])>1,"Duplicate","Unique")

Answer 2

您可以尝试以下公式：

=SUMPRODUCT((A7=$A$2:A7)*((B7=$B$2:B7)+(C7=$C$2:C7)))>2

在提到的情况下@Doc Brown，此公式将返回一个唯一值和两个重复值。