☰

需要按类别对多列文本进行唯一计数

tag-icon • tag-icon microsoft-excel worksheet-function array countif
需要按类别对多列文本进行唯一计数

我的数据看起来像（假设从 A1 开始）：

 Category,Label1,Label2 
 Dogs,blank,doe
 Dogs,zoo,blank 
 Cats,zoo,yup 
 Cats,doe,hgg 
 Cats,blank,doe

结果应该是这样的：

 Dogs, 2
 Cats, 4

我的实际数据有 100 行和 5 个标签列。

我正在寻找一种使用 sumproduct 或数组公式并能处理空白和不同大小的数组的解决方案。如果可能的话，我想避免使用 VBA。

我最终得到了这个公式但仍然不是正确的答案：

=SUM((($A$2:$A$6="Dogs")*($B$2:$C$6<>""))/(COUNTIF($B$2:$C$6,$B$2:$C$6&"")))

我尝试在 countif 范围参数中连接 (A2:A6&B2:C6)，但 countif 不接受。我尝试过 sumproduct，但它不喜欢我使用不同大小的数组（1 列 vs 2 列）等等。我花了两天的时间研究并试图解决这个问题。

期待您的帮助和专业知识。

答案1

我会为此使用 Power Query 插件。它有一个 Group By 命令，其中包括 Count Distinct Rows 操作。它在此处有记录（尽管他们还没有赶上该操作）：

https://support.office.com/en-us/article/Group-rows-in-a-table-Power-Query-e1b9e916-6fcc-40bf-a6e8-ef928240adf1?ui=en-US&rs=en-US&ad=US

为了在 Power Query 中准备数据（在使用 Group By 命令之前），我将选择 Category 列并使用 Transform / Unpivot Columns / Unpivot Other Columns 命令。这会将 Label1 和 Label2 列转换为 Attribute 和 Value 列。我将删除 Attribute 列 - 这样只剩下包含 doe、zoo 等的 Value 列。

最后一步是 Group By，它将正确计算不同/唯一的值，例如 Cats = 4

相关内容