首页办公网络 › 今天有朋友说,根据原文中的三个公式

今天有朋友说,根据原文中的三个公式

Excel公式看不懂,常常有以下两种情状及化解办法:

1-拉灯法

=COUNTIF(B$2:B2,B2)

拉灯法是指:公式中数据区域地址,只锁定二分之一,当单元格地方变动,公式引用的数量范围也会跟着发生变化。

正如拉灯法的意思,经常当公式援引范围要求动态更改时,能够记挂这一个点子。

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


公式分步剖析

私信作者就能够下载60+基础函数及新效率Power种类功能汇总演练材质

拆为己用

依靠原来的文章中的多个公式,能够大致的计算出以下两种,编写公式的常用技术:

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

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

我们的“office每天学”群里有位大师朋友说她更爱好INDEX+SMALL,那韩先生前些天就把INDEX+SMALL这一神组合形成一对多搜索的议程给大家讲一讲。

二、公式较长,进一步加大了对公式的乘除逻辑精通难度

这种意况,可以虚拟动用Alt+Enter急迅键对公式实行换行管理——那几个飞快键不独有适用于一般的内容换行,在公式内一样适用,然后在每行的启幕增添一定数额的空格完毕缩进,达到公式等级次序结构显明的指标,如下图所示:

图片 1

3- 构建0-1数组

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

构建0-1数组是指:基于数组公式和COUNTIF营造一组只包蕴0和1的数组。然后能够对该数组举行以下处理:

  • 求和。结合sumProduct总结相配总个数。
  • 寻觅首次面世。结合MATCH函数,总计首次面世的数字地方。

男的也是从0001-9999

进行应用2:计算不重复数据

假使A$2:A$13区域中的单元格内容等于E2单元格内容,则赶回该单元格所在的行,不然再次来到整个专门的学业表的行数。

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

4- Match模糊查找

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

MATCH函数的第1和第四个参数为空(即暗许分别为0和1),意思是在第一个参数中,查找第二遍出现0的任务。


掌握了那多少个技术后,后续在VLOOKUP和SUMPRODUCT等繁杂的函数中,都有一点都不小希望会用获得。

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

函数介绍:

第二步:

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

这种景色下,能够使用公式求值的效劳一步步翻看公式每趟的要紧总结结果,这样就很轻便通晓公式的预计进程了,如下图所示:

图片 2

初稿音讯
题目:COUNTIF函数高能应用,够了!
作者: Excel之家ExcelHome
链接:http://t.cn/Rx51E5T

在双列间排行

事先大姨子发表过一篇《“别人家的男女”都以那般用SUMIF的!》图像和文字推送(推送46),随着如今直播课程的有利于,十分的多小朋侪又留言询问,与SUMIF很接近的COUNTIF函数是或不是也会有其他扩张使用方式,经过四姐彻夜整理,明日就推出一篇SUMIF函数的姐妹篇——COUNTIF函数的恢宏应用,想要成为人上人的同伙,快快来读书一下呢!

为此:此部分再次来到的数组是:

自个儿是海洋,微软认证Excel专家,公司签订Power BI技艺顾问

2-倒数法

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

倒数法是指,对一个只满含数字的数组,取其尾数。日常是用1除以那个数组。常有的倒数结果有:

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

=SMALL(C2:C417,416*0.35)


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

回答:

原作大体

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

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

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

    =COUNTIF(B$2:B2,B2)

    图片 3

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

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

如上便是COUNTIF函数的两种布满的展开使用方式,最后一种办法,小同伴们能够团结入手总结一下,以便加深精通。学会了那二种扩充应用,小同伴们在使用函数的时候就能够越来越高大上了,再也不用担忧被外人家的心机Child比下去了吧!

当公式在F2单元格时,重返B列第3行的值,即张二的率先次花费记录7478。因为公式中IF部分是数组总结,所以公式以三键组合甘休。

款待关切

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

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

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

让我们共同念书,共同进步

回答:

以此公式的法力是领取三个区域内数据的独占鳌头值,即去掉全部重复项目(重复的保存一项)。=INDEX(A$2:A$100,MATCH(,COUNTIF(B$1:B1,A$2:A$100),))&"",那是两个数组公式,输入公式后需求同期按CTENVISIONL、SHIFT、ENTEKoleos八个键,不然公式会回来错误值#N/A。

图片 4

公式中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照旧会油可是生谬误值。

图片 5

以上正是对那些公式的事无巨细分析,希望对你具备辅助,款待关切@Excel泥瓦匠,Excel学习,E路有您。

回答:

主题材料细节太少了,深入分析不出难点。

要看公式的执行进程,能够点公式——公式求值——一步一步试行下去,检查哪个步骤错了。

图片 6

照旧你在写公式的长河中,想要通晓某些公式的结果,能够选中公式,按F9得到结果,按
ctrl+z,回到公式。

接待关怀自身的头条号,假使有excel方面包车型大巴难点,能够私信调换,为您回答解惑。

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

说明:

图片 7

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

这种气象其实拾壹分不佳,所以自身一般提议不用在公式里大概参数,能写鲜明的必然要写分明,能省的也决不省!公式不只有是友好用的,非常多时候是要给别人看的!

本来,一时候是因为外人写了如此的公式,那我们也要学会看得懂。

以这么些题材里的公式为例,match函数省略了七个参数(严苛来说这种不叫省略,大概应当叫省写,前边再说省略和省写的区分),结果相当多人一看就蒙圈了,实际上,match函数的参数有3个,个中第2个参数能够省略。难题中的公式有2个逗号,表达3个参数都以存在的,只是未有把参数的切切实实值写出来而已,像这种气象,参数存在而并未有写具体值的,那几个参数的值实际便是0。如下图所示:

图片 8

接下去,上面再对照一下简约和省写参数的差异,方便大家加深圳影业公司像:

图片 9

图片 10

上述是对有的错综相连公式的解读办法,同期也作证了一部分写公式时应当小心的难题,希望大家从此写公式时都能服从一定的正规化,不止利于温馨,更能方便人家。


证实:汇总时,不要在“全选”状态下开展,先“筛选”出某一单位,自动求和∑。然后再恢复生机到“全选”或许选拔另外单位,就能够自动汇总了(在“筛选”出某一单位展开求和时,一般表格会自动发出以上汇总公式)。

参数表明:COUNTIF(总结区域,总括标准)

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

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

图片 11

用IF函数,建设构造一新的数组,这一新的数组创建的条条框框是:

电动发出种类号:在A1输入以下公式,往下拖。

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

第四步:

 

实际经过结果如下:

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

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

COUNTIF(range,criteria)

在F2单元格输入公式:

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

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

韩先生已经写过一篇:Excel |
VLOOKUP一对多查找:不一连同样内容对应的八个数据贰回提取,明天有对象说:那么些公式写起来好劳顿啊!

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

~~作者是华丽的分界线~~

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

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

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

图片 12

昔不前段时间次排名(行大排先)

图片 13

第一步:

怎么着筛选奇数行

假使您感到四姐的共享内容很实用,应接分享给别的年轻人伴呦,独乐乐不及众乐乐嘛!

在首先步变成的数组中,查找第第一小的数值。

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

图片 14

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

有三个编码,5位,第一位,1为男,2为女,前面4位,代表她的数码,从0001-9999,怎么样到达下表:

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

我们以搜寻“张二”的费用记录为例来剖判:

缺考统计在内

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

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

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

说明:在手动录入数据D2:D1第11中学,查找系统数据A2,倘使个数大于0,就意味着数量现已录入了,重回结果“是”,不然重回结果“否”

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

升冪

进行应用3:删除重复项

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

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

3.INDEX函数基于MATCH函数查找的地方,加1后,重回A列中的姓名;

在最终加上&"",这一步是容错管理。用空单元格与空文本合併再次来到空文本的性状,将不仅结果数量的片段不显得出来。

无并列分段排行

开展应用1:数据比较

公式实现

英语

英语排名

42

9

62

3

72

1

48

5

48

5

72

1

54

4

42

9

缺考

 

缺考

 

45

8

46

7

图片 15

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

单位

数据

排名

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

 

2.MATCH函数在COUNTIF函数再次回到的汇集中,查找第贰个0的岗位;

结果是那样的:

可以还是不可以用二个公式直接寻觅所用考生中语文成绩中按与考人数的35%切线中位居第35%的大成是有个别?

图片 16

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

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

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

第三步:

公式=MOD(A1,2)=1

4.万一遇到错误值,使用IFERubiconRO锐界函数替换错误值为空。

转载本站文章请注明出处:vns威尼斯城官网登入 http://www.tiec-ccpittj.com/?p=2421

上一篇:

下一篇:

相关文章