Excel公式看不懂,原来的书文中也进展了详实的分

2019-11-07 13:06栏目:网络办公
TAG:

问题:这是删除重复项的公式:=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

顺次排名

由大到小

=SUMPRODUCT((B$3:B$12>B3)*(1/COUNTIF(B$3:B$12,B$3:B$12)))+1

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

由小到大

=SUMPRODUCT((B$3:B$12<B3)*(1/COUNTIF(B$3:B$12,B$3:B$12)))+1

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

有并列排名

=RANK(B2,$B$2:$B$20)

=SUMPRODUCT(1*($B$3:$B$21>B3))+1

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

{=SUM(IF($B$3:$B$21>B3,1,0))+1}

=19-FREQUENCY($B$3:$B$21,B3)+1

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

无并列排名

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

=SUMPRODUCT((B3-ROW()/1000<$B$3:$B$21-ROW($B$3:$B$21)/1000)*1)+1

=19-FREQUENCY($B$3:$B$21-ROW($B$3:$B$21)/1000,B3-ROW()/1000)+1

{=SUM(IF($B$3:$B$21-ROW($B$3:$B$21)/1000>B3-ROW()/1000,1,0))+1}

有并列分段排名

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

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

{=MATCH(C3,LARGE(OFFSET($C$2,IF($A$3:$A$21=A3,ROW($A$3:$A$21)-2),),ROW(INDIRECT("1:"&COUNTIF($A$3:$A$21,A3)))),0)}

{=MATCH(C3,LARGE(IF($A$3:$A$21=A3,$C$3:$C$21),ROW(INDIRECT("1:"&COUNTIF($A$3:$A$21,A3)))),0)}

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

无并列分段排名

{=SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21-ROW($C$3:$C$21)/10000>C3-ROW(C3)/10000))+1}

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

成绩排名

序号

姓名

语文

数学

英语

1

杨增海

135

136

146

2

郭爱玲

138

137

141

3

华志锋

134

138

141

4

袁文飞

134

143

135

能否用一个公式直接找出所用考生中语文成绩中第100名的成绩是多少?

=LARGE(C2:C417,100)

=PERCENTILE(C2:C417,(416-100)/416)

=PERCENTILE($C$2:$C$417,(COUNTA($C$2:$C$417)-100)/COUNTA($C$2:$C$417))

能否用一个公式直接找出所用考生中语文成绩中按与考人数的35%切线中位于第35%的成绩是多少?

升冪

=SMALL(C2:C417,416*0.35)

=PERCENTILE($C$2:$C$417,0.35)

    降冪

=LARGE(C2:C417,416*0.35)

=PERCENTILE($C$2:$C$417,1-0.35)

如何排名

1、对英语进行排名,缺考不计算在内。
2、对英语进行排名,缺考计算在内。

英语

英语排名

42

9

62

3

72

1

48

5

48

5

72

1

54

4

42

9

缺考

 

缺考

 

45

8

46

7

缺考不计算在内

b2=IF(A2="缺考","",RANK(A2,$A$2:$A$13)) 然后按照B列排序

缺考计算在内

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

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

数据排名(隔几行排名)

=IF(A2="","",RANK(A2,$A$2:$A$11,0))

如果隔几行排名,如下表,第五行、第九行和第十二行不参与排名。

单位

数据

排名

A

1

8

A

5

7

A

6

6

小计

12

 

B

8

4

B

9

3

B

7

5

小计

24

 

C

18

1

C

11

2

小计

29

 

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

根据分数进行倒排名

=RANK($E3,$E$3:$E$22,1)

=RANK(K60,$K$60:$K$83,1)

=COUNTIF($K$60:$K$83,"<"&K60)+1

倒数排名函数是什么

1为正排序,0为逆排序。

倒数排名=RANK(A2,$A$2:$A$5,0)

正数排名=RANK(A2,$A$2:$A$5,1)

如何实现每日各车间产量的排名

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

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

分数相同时按照一科的分数进行排名

{=MATCH(K308*100+D308,LARGE($K$308:$K$331*100+$D$308:$D$331,ROW($K$308:$K$331)-307),)}

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

自动产生序列号:在A1输入以下公式,往下拖。

=SUBTOTAL(3,$B$2:B2)*1

自动汇总,用以下公式:

=SUBTOTAL(9,$B$2:B2)

说明:汇总时,不要在“全选”状态下进行,先“筛选”出某一单位,自动求和∑。然后再恢复到“全选”或者选择任何单位,就能自动汇总了(在“筛选”出某一单位进行求和时,一般表格会自动产生以上汇总公式)。

其它:如同时要在其它单元格显示人数,在“全选”状态下,选定单元格,点“fx”(用“sum”函数)再点击序列号最末尾数,即可。

如何筛选奇数行

公式=MOD(A1,2)=1

函数筛选姓名

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

=IF(ISNUMBER(FIND("A",C2))+ISNUMBER(FIND("A",B2))>0,"OK","")

名次筛选

名次=RANK(K5,K$2:K$435)

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

如何实现快速定位(筛选出不重复值)

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

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

=INDEX(A:A,SMALL(IF(MATCH(A$1:A$20,A$1:A$20,)=ROW($1:$20),ROW(A$1:A$20),65536),ROW()))&""(数组公式)

如何请在N列中列出A1:L9中每列都存在的数值

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

自动为性别编号的问题

有一个编码,5位,第1位,1为男,2为女,后面4位,代表他的编号,从0001-9999,如何达到下表:

性别  编码

男     10001

男     10002

女     20001

男     10003

女     20002

男的也是从0001-9999

女的也是从0001-9999

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

用公式:=IF(A2="",TEXT(COUNTIF(A$2:A2,A2),"10000"),TEXT(COUNTIF(A$2:A2,A2),"20000"))向下拖

 

版权声明:本文由大奖888-www.88pt88.com-大奖888官网登录发布于网络办公,转载请注明出处:Excel公式看不懂,原来的书文中也进展了详实的分