必威-必威-欢迎您

必威,必威官网企业自成立以来,以策略先行,经营致胜,管理为本的商,业推广理念,一步一个脚印发展成为同类企业中经营范围最广,在行业内颇具影响力的企业。

原文中也进行了详细的解释,群里有位高手朋友

2019-10-03 14:23 来源:未知

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

4- Match模糊查找

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

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


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

=IF(A2="小计","",RANK(B2,(B$2:B$4,B$6:B$8,B$10:B$11)))    下拉


韩老师曾经写过一篇:Excel | VLOOKUP一对多查找:不连续相同内容对应的多个数据一次提取,今天有朋友说:这个公式写起来好麻烦啊!

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

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

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

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

图片 1

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

图片 2

图片 3

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


3- 构建0-1数组

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

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

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

=IF(A2="缺考",COUNTIF($A$2:$A$13,">=0")+1,RANK(A2,$A$2:$A$13))

3.INDEX函数根据MATCH函数查找的位置,加1后,返回A列中的姓名;

在最后加上&"",这一步是容错处理。用空单元格与空文本合并返回空文本的特性,将超出结果数量的部分不显示出来。

回答:

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

如何筛选奇数行

图片 4

这样,就在第一步的数组中找到了第1、2、3、4小的值,即3、7、10、13。

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

回答:

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

图片 5

公式中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依然会出现错误值。

图片 6

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

回答:

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

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

图片 7

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

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

2-倒数法

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

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

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

=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))

公式:B2 = IFERROR(INDEX(A:A,MATCH(0,COUNTIF(B$1:B1,$A$2:$A$14),0)+1),"")

可以看到:凡是A列单元格内容等于张二的,返回的都是对应的行数,不等于张二的,返回的都是工作表的行数1048576。

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

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

图片 8

原文大意

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

  1. 不一样的序号。(分组填充不同的序号)

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

    =COUNTIF(B$2:B2,B2)

    图片 9

    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()函数中,也经常出现类似的公式。

=19-FREQUENCY(($A$3:$A$21=A3)*($C$3:$C$21),C3)+1

拓展应用1:数据对比

公式实现

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

1-拉灯法

=COUNTIF(B$2:B2,B2)

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

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

{=IF(ROW()>SUM(--x),"",INDEX(A:A,SMALL(IF(x,ROW($A$1:$A$9)),ROW())))}

注意:此公式为数组公式,需要同时按下Ctrl+Shift+Enter三键执行运算

公式分步解析

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

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

图片 10

拆为己用

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

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

具体过程结果如下:

INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)))

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

如何把两列中只要包含A和A+的人员筛选出来

来源:微信公众号表妹的EXCEL

在第一步形成的数组中,查找第第一小的数值。

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

 


在F2单元格输入公式:

欢迎关注

=COUNTIF($B$3:$B$21,">"&B3)+1

图片 11

第三步:

名次筛选

图片 12

结果是这样的:

班名次=RANK(K6,OFFSET(K$2,MATCH(A6,A:A,)-2,,COUNTIF(A$1:A$500,A6)))

说明:在手动录入数据D2:D11中,查找系统数据A2,如果个数大于0,就代表数据已经录入了,返回结果“是”,否则返回结果“否”

ROW(A1)是一个动态的数值,公式往下填充一行,行数加1,即当公式在F2单元格时,是ROW(A1),当公式填充到F3单元格是,是ROW(A2),当到F4单元格时,是ROW(A3)……

=IF((COUNTIF($A$2:A2,A2)=1)=TRUE,A2,"")

公式:B2 = IF(COUNTIF($D$2:$D$11,A2)>0,"是","否")

当公式在F2单元格时,返回B列第3行的值,即张二的第一次消费记录7478。因为公式中IF部分是数组计算,所以公式以三键组合结束。

女     20002

拓展应用2:统计不重复数据

图片 13

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

COUNTIF(range,criteria)

公式向下填充,得到B列第7、10、13行的值。

不等次排名(行大排先)

说明:

第二步:

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

函数介绍:

如果A$2:A$13区域中的单元格内容等于E2单元格内容,则返回该单元格所在的行,否则返回整个工作表的行数。

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

图片 14

第一步:

女的也是从0001-9999

以上就是COUNTIF函数的三种常见的拓展使用方法,最后一种方法,小伙伴们可以自己动手计算一下,以便加深理解。学会了这三种拓展应用,小伙伴们在使用函数的时候就可以更加高大上了,再也不用担心被别人家的心机Child比下去了呢!

INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)))&""

=SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21>C3))+1

说明:在姓名A2:A14中,依次统计A2、A3、...、A14的个数,然后与1做除法运算,所得的商用SUMPRODUCT函数进行求和,具体过程结果如下:

{1048576;3;1048576;1048576;1048576;7;1048576;1048576;10;1048576;1048576;13}

数组公式

关注微信公众号“表妹的EXCEL”,每周一、三、五获取原创分享教程。

第四步:

筛选后自动产生序列号并汇总

1.B$1:B1是一个随着公式向下复制而逐渐扩大的单元格区域,在这个区域中,COUNTIF函数依次统计A2、A3、...、A14的个数,返回一组0与1组合成的集合;

=INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)))&"",以三键组合结束。

由小到大

公式:B2 = SUMPRODUCT(1/COUNTIF(A2:A14,A2:A14))

图片 15

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

2.MATCH函数在COUNTIF函数返回的集合中,查找第一个0的位置;

用IF函数,建立一新的数组,这一新的数组建立的规则是:

=RANK(C2,OFFSET($C$2:$C$5,MATCH(A2,$A$2:$A$33,0)-1,,,))

之前表妹发布过一篇《“别人家的孩子”都是这样用SUMIF的!》图文推送(推送46),随着最近直播课程的推进,不少小伙伴又留言询问,与SUMIF很类似的COUNTIF函数是否也有其他扩展使用方法,经过表妹彻夜整理,今天就推出一篇SUMIF函数的姊妹篇——COUNTIF函数的扩展应用,想要成为人上人的小伙伴,快快来学习一下吧!

我们以查找“张二”的消费记录为例来分析:

=IF(A2="缺考",COUNT($A$2:$A$13)+1,RANK(A2,$A$2:$A$13,0))

如果你觉得表妹的分享内容很实用,欢迎分享给其他小伙伴呦,独乐乐不如众乐乐嘛!

SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)

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

~~我是华丽的分割线~~

所以:此部分返回的数组是:

{=SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21>C3)/COUNTIF($N$3:$N$21,$N$3:$N$21))+1}(需辅助列)

拓展应用3:删除重复项

IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B))

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

图片 16

我们的“office天天学”群里有位高手朋友说他更喜欢INDEX+SMALL,那韩老师今天就把INDEX+SMALL这一神组合完成一对多查找的方法给大家讲一讲。

女     20001

~~我是华丽的分割线~~

用ROW(A1)做SMALL函数的第二个参数,即第几小。

如果你是已经输入了其它信息,仅仅为快速输入编码的话。用筛选可以实现吧。
先以“男”为关键字进行排序,然后在第一个男的编码输入10001,下拉复制到最后一单即可。同理再以“女”排序。完成目标。

图片 17

TAG标签:
版权声明:本文由必威发布于必威-办公,转载请注明出处:原文中也进行了详细的解释,群里有位高手朋友