Excel计数唯一值多个列 [英] Excel Count unique value multiple columns

问题描述

我有一本包含多张纸的工作簿.在sheet1上，我想计算A列中的人在C列中具有满足D列=内容标准的特定条目的次数，但仅计算B列是唯一值时的次数.

I have a workbook with multiple sheets. On sheet1 I would like to count the number of times person in column A has a particular entry in Column C that meets criteria of column D = Content but only count if column B is a unique value.

使用下面的公式，除了只计算B列中的唯一数字外，我可以做所有事情.我在Sheet2 C2中输入此公式，然后求和到I2，然后在每列中求和到6.

Using the below formula I can do everything except only counting unique numbers in Column B. I enter this formula in Sheet2 C2 then pull across to I2 then pull down to 6 in each column.

=IF(COUNTIFS(Sheet1!$A$2:$A$150,Sheet2!$B2,Sheet1!$C$2:$C$150,Sheet2!C$1,Sheet1!$D$2:$D$150,"Content")=0,"",COUNTIFS(Sheet1!$A$2:$A$150,Sheet2!$B2,Sheet1!$C$2:$C$150,Sheet2!C$1,Sheet1!$D$2:$D$150,"Content"))

我正在使用双计数不显示0.

I am using the double countifs to not show 0's.

这是Sheet1的外观

Here is what it looks like for Sheet1

这就是我希望工作表2的外观

Here is how I would like Sheet 2 to look

任何帮助或建议将不胜感激.

Any help or advise would be much appreciated.

推荐答案

Vijayakumar的回答有效.如果您需要一个更简单的版本(不带数据模型选项，请在下拉的E列中使用此公式

The answer by Vijayakumar works. In case you need a simpler version (without the data model option, use this formula in column E dragged down

=SUMPRODUCT(--($B$1:B1=B2))>0

1. 选择所有数据
2. 插入->数据透视表
3. 在数据透视表"字段中，将"SR"移至行"部分，将"Stage"移至"Prod"和列部分."IsUnique"( Col E )过滤部分，"QN"过滤值部分
4. 现在右键单击数据透视"中的某个单元格，该单元格显示QN汇总的总和/计数值按->计数
5. 在数据透视表的过滤器部分(位于第一行在显示产品"的数据透视表中)，选择内容"
6. 在数据透视表的过滤器部分(第二行显示"IsUnique"的数据透视表)，请选择"TRUE"

1. Select all data
2. Insert ->Pivot table
3. In The Pivot table fields Move "SR" to Rows section "Stage" to column section "Prod" & "IsUnique" (Col E) to filter section and "QN" to Value section
4. Now right click on some cell in Pivot which shows sum/count of QN Summarize values by -> Count
5. In the filter section of pivot (first row on pivot table which states "Prod"), choose "Content"
6. In the filter section of pivot (Second row on pivot table which states "IsUnique"), choose "TRUE"

拥有新数据时，只需从数据透视表工具-> 分析或刷新进行更改数据源.但是，您可以将整个过程记录为宏以使其自动化.

When you have new data, you just need to Change Data Source from the Pivot Table tools->Analyze or Refresh. However you can record this whole process as macro to automate it.

这篇关于Excel计数唯一值多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！