Excel - 基于两列的唯一值的返回计数 [英] Excel - Return Count of Unique Values Based on Two Columns

问题描述

我正在研究一个有很多列和几张的项目。为了简单起见，我将发布我需要帮助的人：在表中，我有一个用户名，以及他们是否收到一个证书。证书的计算是根据他们对其他测试的通过次数（三个或更多给他们一个证书）。在另一张表上，我发布统计信息，我想显示只有唯一证书总数的数量。表格看起来像下面（简化）。

  *用户名* | *测试结果* | * Bundle * 
 abc |通| 
 abc |通| 
 123 |通| True 
 123 |通| True 
 123 |通| True 
 123 |通|真
 qwerty |失败| False 
 qwerty |通| False 
 qwerty |通| False 
  

所以，以上，他们需要三个或更多的测试来获得捆绑。如果他们通过了两个，并且失败了第三个他们得到一个失败的捆绑。如果他们没有进行三次测试，它不会被视为通行证或失败。

在另一张表上，我想显示唯一的号码 我试过的东西：

  = COUNTIFS（MOS_Table [Username]，*，MOS_Table [Bundle]，TRUE）
  

（注：MOS_Table是表的名称）总共捆绑列中的TRUE值的数量，但对每个测试的同一名学生进行计数。

  = COUNTIF（MOS_Table [Bundle]，TRUE）
  

这样返回与上述相同。

我尝试过使用SUMPRODUCT和SUM，COUNTIFS和其他事情。我不知道是否有更好的方法来解决这个问题，但是我会采取任何行动让它上班。长期来看，可能会有400-500行。

提前感谢

解决方案

请尝试： p>

  = SUM（IF（MOS_Table [Bundle] = TRUE，1 / COUNTIF（MOS_Table [Username]，MOS_Table [Username]））） 
  

这是一个数组公式，必须用 ctrl + shift + 输入！

一个完整的数组 {2,2,4,4,4,4,3,3,3} 和 1 / 使它可以总结出来，但只适用于 IF {FALSE，FALSE，0.25,0.25 ，0.25,0.25，FALSE，FALSE，FALSE} 。在你的例子中是1。

如果你还有任何问题，请问：）

I'm working on a project that has many columns and a few sheets. For simplicity, I'll post just the ones I'm in need of help for: In a table, I have a username and whether or not they received a certificate. The calculation of the certificate is based on the number of passes they have on other tests (three or more gives them a certificate). On another sheet, I'm posting statistics where I would like to show just the number of unique certificates total. The table looks something like below (simplified).

*Username* | *Test Result* | *Bundle*
abc        | Pass          | ""
abc        | Pass          | ""  
123        | Pass          | True
123        | Pass          | True
123        | Pass          | True
123        | Pass          | True
qwerty     | Fail          | False
qwerty     | Pass          | False
qwerty     | Pass          | False

So with the above, they need three or more tests to have the "Bundle". If they pass two and fail the third they get a fail for the "Bundle". If they haven't taken three tests, it doesn't count as a pass or fail.

On another sheet, I want to show the number of unique students whom have gotten the "Bundle" and whom haven't.

Some of the things I've tried:

=COUNTIFS(MOS_Table[Username],"*",MOS_Table[Bundle],TRUE)

(NOTE: MOS_Table is the name of the table) This will count the total number of TRUE values in the Bundle column but counts the same student for each test they have taken.

=COUNTIF(MOS_Table[Bundle],TRUE)

This returned the same as above.

I've tried others with SUMPRODUCT and combinations of SUM, COUNTIFS, and other things. I'm not sure if there's a better way to go about this, but I'll take any hack to get it to work. Long term, there will be up to 400-500 possible rows.

Thanks in advance!

解决方案

Please try this:

=SUM(IF(MOS_Table[Bundle]=TRUE,1/COUNTIF(MOS_Table[Username],MOS_Table[Username])))

This is an array-formula and must be confirmed with ctrl+shift+enter!

The countif returns an array of the full counts {2,2,4,4,4,4,3,3,3} and the 1/ makes it so it can be summed up, but only for the true part of the IF {FALSE,FALSE,0.25,0.25,0.25,0.25,FALSE,FALSE,FALSE}. Which is 1 in your example.

If you still have any questions, just ask :)

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