(Excel)常用函数公式及操作技巧之三：排名及排序筛选（二）.NET快速开发框架

215 839

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

排名及排序筛选（二）

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

求最精简的自动排名公式

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

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

=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

不等次排名(行小排先）

由大到小

=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

不等次排名(行大排先）

=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

顺次排名

=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

=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25

有并列排名

=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

4

袁文飞

143

能否用一个公式直接找出所用考生中语文成绩中第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

72

48

5

54

缺考

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

6

小计

12

B

24

C

18

11

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"))向下拖

THE END

一文给你整理好了！6种序号的生成技巧，最后一个不要太好用……余数序号生成技巧行号逆序

Excel常用的九十九个技巧Office自学教程快速掌握办公技巧

如何查找重复值快捷键(快速查找重复值快捷键)爱问知识人

实训计算机

(Excel)常用函数公式及操作技巧之三：排名及排序筛选（二）.NET快速开发框架

Excelexcel常用函数公式及技巧搜集

javascriptMySQL从入门到实践，万字详解！前端下午茶

Excel列去重教程，如何快速保留每个重复项的第一个？

1.Excel基础操作小技巧删除重复值 直接选中区域,然后选中数据-删除重复值即可。 对齐名字 首先选中区域,点击开始下的对齐下拉框,之后在相应的对话框中选择分散对齐即可。 快速输入男女 Ctrl+1打开设置单元格格式,直接在自定义格式中输入“[=1]男;[=2]女”。 快速判断条件 https://www.meipian.cn/2f0gnkph
2.如何找出重复值并重新编号?Stata专版经管之...苹果 苹果 苹果 苹果 菠萝 菠萝 香蕉 香蕉 香蕉 ...找出重复值并重新编号，得到结果如下：苹果 1 苹...https://bbs.pinggu.org/thread-6976772-1-1.html
3.某零售电商平台交易数据浅析——Excel4.3 删除重复值 由于各字段,订单编号、商品编号、描述、数量、订单日期、单价、顾客ID、国家,按照收集规则,各列都可能会存在数据重复的问题,因此不做删除。 4.4 缺失值处理和异常值处理 4.4.1 缺失值处理 客户编号出现空值,而且缺失值较多,并且是不可计算或者预估的,故做删除空值。 https://www.jianshu.com/p/e2735d25f258
1.Excel中如何输入重号:轻松解决重复数据问题(excel重号如何输入...重号,即重复的编号或序号,这在处理大量数据时尤其常见。但是,如何才能在Excel中快速输入重号呢?以下是一些简单有效的方法。 首先,我们可以使用填充柄来输入重号。填充柄是位于单元格右下角的小方块。选中包含初始编号的单元格,将鼠标指针移动到填充柄上,直到指针变成一个十字形。然后,按住鼠标左键拖...https://excel666.zaixianjisuan.com/question/excelzhong-ru-he-shu-ru-zhong-hao-qing-song-jie-jue-zhong-fu-shu-ju-wen-ti.html
2.如何在Excel中的值更改时重新开始编号?在Excel 中对行或列进行编号可以非常快捷和容易。 但是你知道如何在某一列中输入不同的值时自动重新编号,并在出现重复值时继续编号,以下所示? 请依照说明完成工作。 Excel 中另外一列中不同值的单独序列号 假定您有一个值列表,并且为这些值分配了序列号,以下所示。 要为列表中的不同值分隔序列号,请履行以下操...https://www.yingxiongyun.com/news/post/15767.html
3.如何在Excel中的值更改时重新开始编号?在Excel 中对行或列进行编号可以非常快速和容易。 但是你知道如何在某一列中输入不同的值时自动重新编号,并在出现重复值时继续编号,如下所示? 请按照说明完成工作。 Excel 中另一列中不同值的单独序列号 假设您有一个值列表,并且为这些值分配了序列号,如下所示。 要为列表中的不同值分隔序列号,请执行以下操...https://www.huoban.com/news/post/148770.html
4.Excel职场手册:260招菜鸟变达人QQ群号更新为806295411如果需要查找某数据区域是否存在重复值,将重复值醒目标识,单击条件格式→突出显示单元格规则→重复值,设置为自己想要的格式,如图1-2-16所示。如果选中“唯一”,则把唯一值醒目标识,如图1-2-17所示。 图1-2-16 图1-2-17 第31 招 国际象棋棋盘式底纹设置方法 要设置如图1-2-18所示的国际象棋棋盘式底纹,方法...https://www.epubit.com/bookDetails?id=N7369
5.Excel常用的九十九个技巧Office自学教程快速掌握办公技巧选中数字单元格区域,按【Ctrl】键+数字【1】打开单元格格式窗口,选择【数字】-【自定义】,在【类型】中输入 “编号:00” 点击【确定】即可。 贰 16、查找重复值 选取查找数据的区域,依次点击【开始】-【条件格式】-【突出显示单元格规则】-【重复值】设置选择重复值格式为【浅红填充色深红色文本】。 https://blog.csdn.net/qq_43365911/article/details/127039484
6.Excel中,怎么给一列数据编号,其中重复值的编号相同,编号重复,如...看图片，每4个不同值一循环，B2单元公式为：=IFERROR(VLOOKUP(A2,$A$1:B1,2,0),MOD(SUM(1/...https://www.3d66.com/answers/question_1193796.html
7.python如何实现排序,并标上序号python表示排序时,遇到相同的值时,重复值序号保持一致,如下图,843.86值重复两次,排名均为9,且下一个数序号为10,序号保持连续 1 2 df['rank']=df['amount'].rank(ascending=0, method='dense') print(df) 第二种常见排序:组内排序 ,将上面数据根据id分组,并按照amount字段进行组内排序 1 2 3 df['rank']=...https://www.jb51.net/article/251074.htm
8.Awesome,09。我们遍历这 10 亿个关键词,并且通过某个哈希算法对其求哈希值,然后哈希值同 10 取模,得到的结果就是这个搜索关键词应该被分到的文件编号。对这 10 亿个关键词分片之后,每个文件都只有 1 亿的关键词,去除掉重复的,可能就只有 1000 万个,每个关键词平均 50 个字节,所以总的大小...https://github.com/Ty-Chen/Awesome-Backend/blob/5ad253a0f2e82d9b83892a60e01a1e0a855d70b3/Data%20Structure%20and%20Algorithm.md
9.交通行政执法考试(精选8篇)Random类中实现的随机算法是有规则的随机抽取,将每次随机抽取的试题编号放入Set集合中,Set集合中是不会出现重复值的,这样可以抽取出不重复的试题。抽取代码为Set对象.add ( (int) Random对象.nextInt (试题个数) 。结合抽取界面中设定的要求,这样可以从多科目中达到随机抽取试题的目的。我们这种随机抽取的方式是在...https://www.360wenmi.com/f/fileb01z79b3.html
10.Python基础教程可以拆成短语,fix me ,意为修理我。如果代码中有该标识,说明标识处代码需要修正,甚至代码是错误的,不能工作,需要修复,如何修正会在说明中简略说明。 文档注释 defSay(info):'''@author 刘博源@date 2000 / 01 / 01@param info 打印的数据信息@desc 打印参数数据@return None 不存在的返回值'''print(info)...https://ld246.com/article/1682265795820
11.身份证号码查重复问:请问怎样在一张excel表格中查找出重复的身份证号? 答:1、比如如下图,选择标题:身份证号,点数据菜单——筛选; 2、点身份证号标题的下拉菜单,选择要筛选的身份证号,例如:310111111111111000; 3、所有此身份证号的重复值就全部显示出来了。 答:1、首先打开一个已经录入身份证号码的excel表格,选中需要查重的单元...https://www.igaichong.com/article/d1819993b6ce19a3e1f56a1b.html
12.Stata数据处理:一文读懂微观数据库清理(上)假设拿到的是 Excel 形式的原始数据,第一步需要对变量进行编号,编号应该清晰易懂不重复;第二步是尽量将数据数字化 (若数值型变量中存在大量文字型回答,也许还需要在 Excel 中进行手工清理);第三步是将数据导入 Stata 中,Stata 主要有以下 4 个数据读入命令: ...https://lianxh.cn/news/f0ea677dfb682.html
13.ProtoBuf的学习和使用(C++)江阴雨辰互联其中oneof字段中的字段与外层的其它字段同级,因此在给oneof字段内的字段设置编号的时候注意不要重复编号; oneof字段中的值不能使用repeated规则修饰; 接着,我们来编译我们的PeopleInfo和Contacts文件,看一看oneof字段会伴随那些方法: 首先针对oneof字段中的内部字段来说,具有常规的: ...https://www.yc00.com/web/1699999705a955486.html
14.MySQL统计信息不准确导致文件大小与系统表大小差异大数据库实例的物理文件的大小是根据系统表计算的表大小的两倍以上,原因是统计数据不准确,具体是索引数据页的数量不准确。 引言 MySQL 的统计信息中包括多个统计项,由于基于采样计算,因此存在误差,最常见的是统计项【表的行数】不准确,可能导致执行计划选择错误。 https://www.51cto.com/article/774277.html