兴發国际娱乐手机登录 9

Countif函数高级应用,排名及排序筛选

问题:这是删除重复项的公式:=INDEX(A$2:A$100,MATCH(,COUNTIF(B$1:B1,A$2:A$100),))&\

原文信息
标题:COUNTIF函数高能应用,够了!
作者: Excel之家ExcelHome
链接:http://t.cn/Rx51E5T

 

回答:

原文大意

文中主要介绍了3中应用场景:

  1. 兴發国际娱乐手机登录,不一样的序号。(分组填充不同的序号)

    在A2单元格输入公式,向下复制:

    =COUNTIF(B$2:B2,B2)

    兴發国际娱乐手机登录 1

    img

  2. 计算不重复人数

![](https://upload-images.jianshu.io/upload_images/118902-efcf421409cce54a.png)

img
  1. 提取不重复名单
![](https://upload-images.jianshu.io/upload_images/118902-1ddfbd1acc7c5222.png)

img

原文中也进行了详细的解释。但是还是有几点可以继续改进:

  1. 公式太复杂,不方便记忆
  2. 公式可以进行归类梳理,在sumProduct()函数中,也经常出现类似的公式。

(Excel)常用函数公式及操作技巧之三:

Excel公式看不懂,通常有以下几种情况及解决办法:

拆为己用

根据原文中的三个公式,可以简单的总结出以下几种,编写公式的常用技巧:

排名及排序筛选(二)

一、公式涉及计算逻辑较为复杂

这种情况下,可以使用公式求值的功能一步步查看公式每次的关键计算结果,这样就很容易理解公式的计算过程了,如下图所示:

兴發国际娱乐手机登录 2

1-拉灯法

=COUNTIF(B$2:B2,B2)

拉灯法是指:公式中数据区域地址,只锁定一半,当单元格位置变化,公式引用的数据范围也会随之发生变化。

正如拉灯法的含义,通常当公式引用范围需要动态变更时,可以考虑这个方法。

——通过知识共享树立个人品牌。

二、公式较长,进一步加大了对公式的计算逻辑理解难度

这种情况,可以考虑使用Alt+Enter快捷键对公式进行换行处理——这个快捷键不仅适用于一般的内容换行,在公式内同样适用,然后在每行的开头添加一定数量的空格实现缩进,达到公式层次结构分明的目的,如下图所示:

兴發国际娱乐手机登录 3

2-倒数法

=SUMPRODUCT(1/COUNTIF(C2:C14,C2:C14))

倒数法是指,对一个只包含数字的数组,取其倒数。通常是用1除以这个数组。常有的倒数结果有:

  • 小数:数组取倒数之后,这些小数相加刚好为1,用来获取不重复数据的格式。
  • #DIV/0!:这个数组通常由0和1构成,除以0之后结果错误,对剩余的数据进行求和或计数,计算有效数据的总和或个数。

 

三、公式内省略或省写了参数,以至于公式看不懂

这种情况其实非常不好,所以我一般建议不要在公式里省略参数,能写明确的一定要写明确,能省的也不要省!公式不仅是自己用的,很多时候是要给别人看的!

当然,有时候是因为别人写了这样的公式,那我们也要学会看得懂。

以这个问题里的公式为例,match函数省略了两个参数(严格来说这种不叫省略,或者应该叫省写,后面再说省略和省写的区别),结果很多人一看就蒙圈了,实际上,match函数的参数有3个,其中第3个参数可以省略。问题中的公式有2个逗号,说明3个参数都是存在的,只是没有把参数的具体值写出来而已,像这种情况,参数存在而没有写具体值的,这个参数的值实际就是0。如下图所示:

兴發国际娱乐手机登录 4

接下来,下面再对比一下省略和省写参数的差别,方便大家加深印象:

兴發国际娱乐手机登录 5

兴發国际娱乐手机登录 6

以上是对一些复杂公式的解读办法,同时也说明了一些写公式时应该注意的问题,希望大家以后写公式时都能遵守一定的规范,不仅方便自己,更能方便别人。


3- 构建0-1数组

=INDEX(C:C,1+MATCH(,COUNTIF(E$1:E1,C$2:C$15),))&””

构建0-1数组是指:基于数组公式和COUNTIF构建一组只包含0和1的数组。然后可以对该数组进行以下处理:

  • 求和。结合sumProduct计算匹配总个数。
  • 查找首次出现。结合MATCH函数,计算首次出现的数字位置。

 

欢迎关注

4- Match模糊查找

=INDEX(C:C,1+MATCH(,COUNTIF(E$1:E1,C$2:C$15),))&””

MATCH函数的第1和第3个参数为空(即默认分别为0和1),意思是在第2个参数中,查找第一次出现0的位置。


掌握了这几个技巧后,后续在VLOOKUP和SUMPRODUCT等复杂的函数中,都有可能会用得到。

求最精简的自动排名公式

私信我即可下载60+基础函数及新功能Power系列功能汇总训练材料

=RANK(E2,$E$2:$E$21)

我是大海,微软认证Excel专家,企业签约Power BI技术顾问

=RANK(A2,$A$2:$A$9,0)

让我们一起学习,共同进步

回答:

这个公式的作用是提取一个区域内数据的唯一值,即去掉所有重复项目(重复的保留一项)。=INDEX(A$2:A$100,MATCH(,COUNTIF(B$1:B1,A$2:A$100),))&””,这是一个数组公式,输入公式后需要同时按CTRL、SHIFT、ENTER三个键,不然公式会返回错误值#N/A。

兴發国际娱乐手机登录 7

公式中COUNTIF(B$1:B1,A$2:A$100)这一段得到的数组结果是{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0};MATCH(,COUNTIF(B$1:B1,A$2:A$100),)这一段是用MATCH定位第一个0出现的位置,MATCH第一参数为0时可以简写(可以理解为不写),公式在下拉的过程中COUNTIF第一参数(用于存放不重复项目的区域)会逐步变化,即公式由B2下拉到B3时公式中COUNTIF这段会变成COUNTIF(B$1:B2,A$2:A$100),返回数组是{1;0;0;0;0;1;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0},再往下COUNTIF第一参数继续变化COUNTIF(B$1:B3,A$2:A$100),返回的数组结果将会是另一种0、1组合的结果。第二参数是COUNTIF判断的条件(即所有项目存放的区域),如果第二参数的条件在前面的不重复项目存放区域中已经出现,COUNTIF就会返回1,利用MATCH定位第一个0出现的位置,这样会逐步过滤掉上面已经提取出来的不重复项目,公式下拉到最后就会全部提取出所有唯一项目。

公式中INDEX第一参数和COUNTIF第二参数是一个比所有项目存放区域大的一个区域,比如示例中数据存放是A2:A18,选择一个比数据区域在的区域目的在于避免下拉提取出所有项目继续下拉时出现#N/A错误值。因为下拉到最后提取出所有项目后,COUNTIF返回的数组结果中就没有0了,MATCH定位不到0就会出现#N/A错误值,所以要选择一个比所有项目存放区域大的一个区域,大多少随意,比如这里公式可以这样写=INDEX(A$2:A$19,MATCH(,COUNTIF(B$1:B1,A$2:A$19),))&””。但是只选择一个比所有项目存放区域大的区域并不能单独完成屏蔽错误值的任务,还需要&””这一段代码。这里有两个原因,一个是INDEX在引用真空单元格时会返回结果0,第二个原因是真空单元格在COUNTIF第二参数中会被当作0来处理,所以如果不用&””把INDEX引用的真空单元格变成假空(空文本),下拉到最后提取出所有项目后,COUNTIF返回的数组结果中还是不会有0这样MATCH依然会出现错误值。

兴發国际娱乐手机登录 8

以上就是对这个公式的详细解析,希望对你有所帮助,欢迎关注@Excel泥瓦匠,Excel学习,E路有你。

回答:

题目细节太少了,分析不出问题。

要看公式的执行过程,可以点公式——公式求值——一步一步执行下去,检查哪个步骤错了。

兴發国际娱乐手机登录 9

或者你在写公式的过程中,想要了解某个公式的结果,可以选中公式,按F9获得结果,按
ctrl+z,回到公式。

欢迎关注我的头条号,如果有excel方面的问题,可以私信交流,为你答疑解惑。

=RANK(A2,$A$2:$A$10)+COUNTIF($A$2:$A2,$A2)-1(如果数据列中数值有相同)

=RANK(F10,$F10:$Q10)+COUNTIF($F10:F10,F10)-1

=INDEX(A:A,1/MOD(LARGE(E$2:E$21+1/ROW($2:$21),ROW(1:1)),1))

=LOOKUP(1,0/(($F$2:$F$21=A27)*(COUNTIF(D$26:D26,$A$2:$A$21)=0)),$A$2:$A$21)=INDIRECT(“A”&RIGHT(LARGE(($E$2:$E$21*100+ROW($A$2:$A$21)),ROW(A1)),2))

=RANK(C2,OFFSET($C$1,MATCH(E2,$E$2:$E$768,),,COUNTIF($E$2:$E$768,E2)))

数组公式

{=INDEX(A:A,MOD(LARGE(E$2:E$21*100+ROW($2:$21),ROW(1:1)),100))}

{=OFFSET($A$1,RIGHT(LARGE($E$2:$E$21*1000+ROW($E$2:$E$21),ROW()-25),3)-1,,)}

=OFFSET($A$1,RIGHT(LARGE(($E$2:$E$21*100+ROW($A$1:$A$20)),ROW(A3)),2),)

=TEXT(SUMPRODUCT(($E$2:$E$21>=E2)/COUNTIF($E$2:$E$21,$E$2:$E$21)),”第[DBNUM1]G/通用格式名”)

排序后排名

{=SUM(IF($B$2:$B$15>=B2,1/COUNTIF($B$2:$B$15,$B$2:$B$15)))}

=SUMPRODUCT((B$2:B$15>=B2)/COUNTIF(B$2:B$15,B$2:B$15))

位次排名

{=IF($B2:$O2>=0,RANK($B2:$O2,$B2:$O2,0),)}

根据双列成绩进行共同排名

=RANK(C345,($C$345:$C$356,$H$345:$H$356))

在双列间排名

=RANK(B2,($B$2:$B$26,$E$2:$E$16))

等次排名

由大到小排名

=RANK(B3,$B$3:$B$12)

=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25>B16))+1

由小到大排名

=RANK(B3,$B$3:$B$12,1)

=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25<B16))+1

不等次排名(行小排先)

由大到小

=RANK(B3,$B$3:$B$12)+COUNTIF($B$3:B3,B3)-1

=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25-ROW($B$16:$B$25)/10000>B16-ROW(B16)/10000))+1

由小到大

=RANK(B3,$B$3:$B$12,1)+COUNTIF($B$3:B3,B3)-1

=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25+ROW($B$16:$B$25)/10000<B16+ROW(B16)/10000))+1

不等次排名(行大排先)

由大到小

=COUNT($B$3:$B$12)-RANK(B3,$B$3:$B$12,1)-COUNTIF($B$3:B3,B3)+2

=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25+ROW($B$16:$B$25)/10000>B16+ROW(B16)/10000))+1

由小到大

=COUNT($B$3:$B$12)-RANK(B3,$B$3:$B$12)-COUNTIF($B$3:B3,B3)+2

=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25-ROW($B$16:$B$25)/10000<B16-ROW(B16)/10000))+1

顺次排名

由大到小

发表评论

电子邮件地址不会被公开。 必填项已用*标注