无法在Excel中找到唯一值 [英] Not able to find unique values in excel
问题描述
我在excel中有一些数据，我想基于多个条件选择唯一值。
I have some data in excel, I want to select unique values based on multiple criteria.
我在excel中的数据是
The data that I have in excel is
姓名奖章年份
- A 2 2017
- B 3 2018
- C 5 2018
- A 1 2016
- C 4 2017
- B 7 2018
- A 1 2017
- D 4 2016
- A 2 2017
- B 3 2018
- C 5 2018
- A 1 2016
- C 4 2017
- B 7 2018
- A 1 2017
- D 4 2016
我想得到获得奖牌> 2和<6且年份是2017年或2018年的唯一名称的数目。
I want to get the count of unique names who got medals >2 and <6 and year is 2017 or 2018.
所以当B和C满足时，我应该得到的结果是2
So the result that I should get is 2 as B and C satisfy the criteria.
我在互联网上搜索了有关他的信息，我使用SUMPRODUCT和COUNTIFS得到了一些公式，使用的公式是
I have searched in internet about his and I got some formula using SUMPRODUCT and COUNTIFS, the formula used is
=SUMPRODUCT(1/COUNTIFS(A2:A9,A2:A9,B2:B9,">2",B2:B9,"<6",C2:C9,">2016",C2:C9,"<2019" ))
但是在这种情况下我出错了。
But I am getting error in this case.
请告诉我如何我可以得到所需的计数。
Please suggest me how can I get the desired count. Thanks in advance!!
注意：此数据摘自大量数据。
Note: This data is excerpted from huge data.
推荐答案
因此，如果这是您的数据：
So if this is your data:
E9 中使用的公式：
{=SUM(--(FREQUENCY(IF((B2:B9>2)*(B2:B9<6)*(C2:C9>2016)*(C2:C9<2019),MATCH(A2:A9,A2:A9,0)),ROW(A2:A9)-ROW(A2)+1)>0))}
请参见此处进行更深入的解释。
See here for more in depth explaination.
这篇关于无法在Excel中找到唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
