excel最常用函数公式技巧搜集大全40更新

1、excel 常用函数公式及技巧搜集【身份证信息相关数据提取】从身份证号码中提取出生年月日=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)+0=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)*1=IF(A2,TEXT(LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),#-00-00)+0,)显示格式均为 yyyy-m-d。（最简单的公式，把单元格设置为日期格式）=IF(LEN(A2)=15,19&MID(A2,7,2)&-&MID(A2,9,2)&-&MID(A2,11,2),MID(A2,7,

2、4)&-&MID(A2,11,2)&-&MID(A2,13,2)显示格式为 yyyy-mm-dd。（如果要求为“1995/03/29”格式的话，将”-” 换成”/”即可）=IF(D4=,IF(LEN(D4)=15,TEXT(19&MID(D4,7,6),0000 年 00 月 00 日), IF(LEN(D4)=18,TEXT(MID(D4,7,8),0000 年 00 月 00 日)显示格式为 yyyy 年 mm 月 dd 日。（如果将公式中“0000 年 00 月 00 日”改成“0000-00-00”,则显示格式为 yyyy-mm-dd）=IF(LEN(A1:A2)=18,MID(A1:

3、A2,7,8),19&MID(A1:A2,7,6)显示格式为 yyyymmdd。=TEXT(LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)+0=IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2)=MID(A1,7,4)&年&MID(A1,11,2)&月&MID(A1,13,2)&日=IF(A1,TEXT(LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)从身份证号码中提取出性别=IF(MOD(MID(A1,1

4、5,3),2),男,女)（最简单公式）=IF(MOD(RIGHT(LEFT(A1,17),2),男,女)=IF(A2” ”,IF(MOD(RIGHT(LEFT(A2,17),2),”男”,”女”),)=IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)=1,男,女)从身份证号码中进行年龄判断=IF(A3”,DATEDIF(TEXT(LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),”#-00- 00”),TODAY(),”Y”),)=DATEDIF（A1，TODAY（），“Y”）（以上公式会判断是否已过生日而自动增减一岁）=YEAR(NOW()

5、-MID(E2,IF(LEN(E2)=18,9,7),2)-1900=YEAR(TODAY()-IF(LEN(A1)=15,19&MID(A1,7,2),MID(A1,7,4)=YEAR(TODAY()-VALUE(MID(B1,7,4)&岁=YEAR(TODAY()-IF(MID(B1,18,1)=,CONCATENATE(19,MID(B1,7,2),MID(B1,7,4)按身份证号号码计算至今天年龄=DATEDIF(TEXT(LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00),TODAY(),y)以 2006 年 10 月 31 日为基准

6、日,按按身份证计算年龄(周岁)的公式=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)*1,2006-10-31,y)按身份证号分男女年龄段按身份证号分男女年龄段，身份证号在 K 列，年龄段在 J 列（身份证号为 18 位） 男性 16 周岁以下为1男性 16 周岁（含 16 周岁）以上至 50 周岁为2男性 50 周岁（含 50 周岁）以上至 60 周岁为3男性 60 周岁（含 60 周岁）以上为4女性 16 周岁以下为1女性 16 周岁（含 16 周岁）以上至 45 周岁为2女性 45 周岁（含 45 周岁）以上至 55 周岁为3女性 55

7、周岁（含 55 周岁）以上为4=MATCH(DATEDIF(DATE(MID(K1,7,4),MID(K1,11,2),MID(K1,13,2),TODAY(),y),0,16,5 0,60-0,0,5,5*ISEVEN(MID(K1,17,1)=SUM(- (DATEDIF(MID(K1,7,4)&/&MID(K1,11,2)&/&MID(K1,13,2),TODAY(),y)=0,16,45,5 5+0,0,5,5*MOD(MID(K1,17,1),2)【年龄和工龄计算】根据出生年月计算年龄DATEDIF(A1,TODAY(),y)DATEDIF(A1,TODAY(),y)&周岁DATED

8、IF(A1,NOW(),y)根据出生年月推算生肖中国人有 12 生肖，属什么可以推算出来。即用诞生年份除以 12，再用除不尽的余数对照如下：0猴，1鸡，2狗，3猪，4鼠，5牛，6虎，7兔，8龙，9蛇， 10马，11羊例如：XXX 出生于 1921 年，即用 1921 年除以 12，商得数为 160，余数为1，对照上面得知余数 1 对应生肖是鸡，XXX 就属鸡。=MID(猴鸡狗猪鼠牛虎兔龙蛇马羊,MOD(YEAR(A2),12)+1,1)（2007）如何求出一个人到某指定日期的周岁？=DATEDIF(起始日期,结束日期,Y)计算距离退休年龄的公式=IF(E2=,IF(E2=V2,已经退休,距离退

9、休还有&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2),Y)&年&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2),YM)&个月&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2),Md)&天)其中 E2 为年龄（可用身份证号码的公式生成）；V2 为法定退休年龄（男 60，女 50）公式为：=IF(D2=,IF(D2=男,60,50)D2 为男或女（可用身份证号码的公式生成）；U2 为出生年月日（可用身份证号码的公式生成）。求工

10、齡=DATEDIF(B2,TODAY(),y)=DATEDIF(B2,TODAY(),ym)=DATEDIF(B2,TODAY(),md)=DATEDIF(B2,TODAY(),y)&年&DATEDIF(B2,TODAY(),ym)&月&DATEDIF(B2,TODAY(),md)&日计算工龄=DATEDIF(C6,C8,y)求两日期间的年数=DATEDIF(C6,C8,ym)求两日期间除去整年数剩余的月数=DATEDIF(C6,C8,m)求两日期间的总月数如果只需要算出周年的话，可以用=datedif(1978-8,2006-5,Y)年龄及工龄计算有出生年月如何求年龄？有工作时间如何求工龄？

11、（求出的结果为多少年另几个月，如：0303 的形式，即 3 年零 3 个月）。a1 是出生年月或工作时间：=datedif(a1,today(),y)=text(datedif(a1,today(),y),00)&text(datedif(a1,today(),m),00)如 B2=1964-9-1 则:=TEXT(DATEDIF(B2,TODAY(),y),00)&TEXT(MOD(DATEDIF(B2,TODAY(),m),12), 00)显示 4009=TEXT(DATEDIF(B2,TODAY(),y),00 年)&TEXT(MOD(DATEDIF(B2,TODAY(),m), 12)

12、,00 月) 显示 40 年 09 月如果你找不到 DATEDIF 函数，也可以不用 DATEDIF 函数,如 B2=1964-9-1 则:=TEXT(RIGHT(YEAR(NOW()-B2),2),00)&TEXT(MOD(MONTH(NOW()-B2)- 1,12),00)显示 4009=TEXT(RIGHT(YEAR(NOW()-B2),2)&年&MOD(MONTH(NOW()-B2)-1,12)&个月,)显示 40 年 09 个月自动算出工龄日期格式为(yyyy.mm.dd)能否用：（yyyy.mm.dd）这种格式来计算出工龄有多长呢？以前用这样一段（=TEXT(RIGHT(YEAR(

13、NOW()-A1),2)&年&MOD(MONTH(NOW()-A1)-1,12)&个月,)）。但这种方法只能用：（yyyy-mm-dd）这样的日期格式才能实现！你不妨把“.”替换成“-”，不就行了吗，再说后者是日期的一种标准格式，=TEXT(RIGHT(YEAR(NOW()-SUBSTITUTE(A1,.,-),2)&年&MOD(MONTH(NOW()-SUBSTITUTE(A1,.,-)-1,12)&个月,)【时间和日期应用】自动显示当前日期公式=YEAR(NOW()当前年=MONTH(NOW()当前月=DAY(NOW()当前日如何在单元格中自动填入当前日期Ctrl+;如何判断某日是否星期天

14、=WEEKDAY(A2,2)=TEXT(A1,aaaa)=MOD(A1,7)1)显示昨天的日期每天需要单元格内显示昨天的日期，但双休日除外。例如，今天是 7 月 3 号的话，就显示 7 月 2 号，如果是 7 月 9 号，就显示 7 月 6 号。=IF(TEXT(TODAY(),AAA)=一,TODAY()-3,IF(TEXT(TODAY(),AAA)=日,TODAY()-2,TODAY()-1)=IF(TEXT(TODAY(),AAA)=一,TODAY()-3,TODAY()-1)关于取日期怎么设个公式使 A1 在年月日向后推 5 年,变成 2011-7-15=DATE(YEAR(A1)+5

15、,MONTH(A1),DAY(A1)=EDATE(A1,12*5)如何对日期进行上、中、下旬区分=LOOKUP(DAY(A1),0,11,21,31,上旬,中旬,下旬,下旬)如何获取一个月的最大天数=DAY(DATE(2002,3,1)-1)或=DAY(B1-1),B1 为2001-03-01日期格式转换公式将 “01/12/2005” 转换成“20050112”格式RIGHT(A1,4)&MID(A1,4,2)&LEFT(A1,2)YEAR($A2)&TEXT(MONTH($A2),00)&TEXT(DAY($A2),00)该公式不用设置数据有效性,但要设置储存格格式。也可以用下列两方法：1

16、、先转换成文本,然后再用字符处理函数。2、数据-分列日期-MDY将“2005 年 9 月”转换成“200509”格式先用公式：=text(a1,yyyymm)+0然后将单元格格式为常规。将“2005-8-6”格式转换为“20050806”格式用公式：=TEXT(A1,YYYYMMDD)反之，将 20050806 转为日期 2005-8-6 格式，可用公式：=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)另四种公式：text(a1,0000-00-00)显示：2005-08-06-TEXT(A1,#-00-00)，把单元格设置为日期格式显示：2005-8-6TEX

17、T(20050806,0000-00-00)*1，单元格设置日期型显示：2005-8-6VALUE(LEFT(A1,4)&-&MID(A1,5,2)&-&RIGHT(A1,2)显示：2005-8-6将“20060501”转换为“2006-05-01”格式=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)将“199306”转换为“1993-6”公式 1：=LEFT(A3,4)&-&RIGHT(A3,2)*1公式 2：=-TEXT(A3*100+1,#-00-00) 公式 2 需要设置单元格格式，自定义：e-m公式 3：=TEXT(TEXT(A3&01,0000-00

18、-00),e-m)把 198405 转换成 1984.05一、查找1984，替换1984.二、如果全部是年月的话，我个人建议，1、采取辅助mid(xxxxxx,1,4) & . & right(xxxxxx,2) 2、选中这列，用数据中的分列。然后三、单元格格式数字自定义，类型下面输入：#.#将文本“2004.01.02” 转换为日期格式：2004-1-2=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2)将 2005-8-6 转换为 2005 年 8 月 6 日格式=TEXT(A1,yyyy年m月d日;)象 22 怎样转换成 22 日？转成当年当月的日子公式为：

19、=date(year(now(),month(now(),22)将“2006 年 5 月”转换成“2006 年 05 月”公式为：=TEXT(A8,yyyy年mm月;)也可以这样处理：选中单元格，设置单元格公式数字自定义，将 yyyy“年”m“月”改为：yyyy“年”mm“月”，即可。但这方法打印出来显示为：2006/5/将“1968 年 6 月 12 日”转换为“1968/6/12”格式=YEAR(A1)&/&MONTH(A1)&/&DAY(A1)显示：1968/6/12=TEXT(A1,yyyy/mm/dd)显示：1968/06/12将“1968 年 6 月 12 日”转换为“1968-6

20、-12”格式=YEAR(A1)&-&MONTH(A1)&-&DAY(A1)显示：1968-6-12=TEXT(A1,yyyy-mm-dd)显示：1968-06-12将 1993-12-28 的日期格式转换成 1993 年 12 月=CONCATENATE(YEAR(A1),年,MONTH(A1),月)=YEAR(A1)&年&MONTH(A1)&月也可以自定义格式 $-404e年m月将“1978-5-2”包含年月日的日期转换成“197805”只有年月的格式year(A1)&text(month(A1),00)要将“99.08.15” 格式转换成“1999.08.15”如何做选中列，数据菜单中选分

21、列，分列过程中“格式”选“日期 YMD”，结束。要保持 2005/8/6 格式当输入 2005/8/6 后系统自动变成 2005-8-6，要保持 2005/8/6 格式，可以使用强制文本（前面加号）或使用公式=TEXT(A1,YYYY/MM/DD)。也可以用另一种公式：=IF(ISERROR(TEXT(A1,yyyy/mm/dd),TEXT(A1,0000!/00!/00),TEXT(A1,yyyy/mm/d d)将“二三年十二月二十五日”转为“2003-12-25”格式，1、可以用数组公式将中文日期转化为日期系列数=14610+MATCH(SUBSTITUTE(A3,元,一),TEXT(RO

22、W($14611:$55153),DBNum1yyyy 年 m 月 d 日),0)该公式速度较慢。2、改进后的公式，速度要快的多：=DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW($1900:$2100),DBNum10000),0), MONTH(MATCH(SUBSTITUTE(MID(A7,6,7),元,一),TEXT(ROW($1:$366),DBNum1m 月 d 日),0),DAY(MATCH(SUBSTITUTE(MID(A7,6,7),元,一),TEXT(ROW($1:$366),DBNum1m 月 d 日),0)要设置为 1900 年的日期格式。日期格

23、式转换如 A 列是月份数为 8，B 列是日期数为 18，如何在 C 列显示“8 月 18 日”=A1&月&B1&日反之，要将 C 列的“8 月 18 日” 直接分别到 D、E 列，显示月份和日期， 月数份=LEFT(C5,FIND(月,C5)-1)日期数=MID(C5,FIND(月,C5)+1,FIND(日,C5)-FIND(月,C5)-1)也可分别用公式：=month(-c5)=day(-c5)日期格式转换问题输入的日期是:04-07-26. 与另一格的001合并,合并出来是:040726001.=TEXT(A1,YYMMDD)&001要想自动取得“编制日期：XXXX 年 X 月 X 日”可

24、在该单元格输入 =编制日期：&TEXT(TODAY(),yyyy 年 m 月 d 日)【排名及排序筛选】一个具有 11 项汇总方式的函数 SUBTOTAL=SUBTOTAL(9,$B$2:B2)在数据筛选求和上有意想不到的功能，11 项功能为：1、求平均数，2、求计数，3、求计数值（自动筛选序列）4、求最大值，5、求最小值，6、求乘积，7、求总体标准偏差，8、求标准偏差、9、求和，10、求方差，11、求总体方差。自动排序=SUBTOTAL(3,$B$2:B2)*1=IF(A2A1,1,N(C1)+1)按奇偶数排序我想请教怎样按奇数顺序然后再按偶数顺序排序=IF(MOD(A1,2),0,1)=I

25、F(ROW()50,(ROW()*2)-100,(ROW()*2)-1)=ROW()*2-1-(ROW()50)*99自动生成序号比如在第二列中输入内容回车后第一列的下一行自动生成序列号。=IF(B2,A2+1,)如何自动标示 A 栏中的数字大小排序？=RANK(A1,$A$1:$A$5)=RANK(A1,A:A)如何设置自动排序A 列自动变成从小到大排列B=SMALL(A$2:A$28,ROW(1:1)A 列自动变成从大到小排列B=LARGE(A$2:A$28,ROW(1:1)重复数据得到唯一的排位序列想得到数据的出现总数吗（1,2,2,3,4,4,5 数据的出现总数为 5）？ 解答：不需要

26、插列,不需要很多的函数就行了. =RANK(B3,B$3:B$12)+COUNTIF(B$3:B3,B3)-1按字符数量排序制作歌曲清单时，习惯按字符数量来排列分类，但是 EXCEL 并不能直接按字数排序。需要先计算出每首歌曲的字数，然后再进行排序。如 A、B 列分别为“歌手”和“歌名”，在 C1 输入“字数”，在 C2 输入公式：LEN(B2)下拖，单击 C2，单击工具栏上的“升序排列”即可，删除 C 列。排序字母与数字的混合内容日常使用中，表格经常会有包含字母和数字混合的数据，对此类数据排序时，通常是先比较字母的大小，再比较数字的大小，但 EXCEL 是按照对字符进行逐位比较来排序的，如下

27、表：A7 排在第 5 位，而不是第 1 位。排序结果无法令人满意。123456789AA122 A29 A317 A43 A7 B20 B3 C144 C510C33如果希望 EXCEL 改变排改变。在 B1 中输入公式：RIGHT(A1,LEN(A1)-1),3) 下单击 B2，单击工具栏上的随机排序序的规则，需要将数据做一些A1A72A293A434A1225A3176B37B208C59C3310C144BA007 A029 A043 A122 A317 B003 B020 C005 C033 C144LEFT(A1,1)& RIGHT(000&拖“升序排列”即可。如 A、B 列分别为“

28、歌手”和“歌名”，在 C1 输入“次序”，在 C2 输入公式：RAND（），下拖，单击 C2，单击工具栏上的“降序排列”即可对歌曲清单进行随机排序。排序的问题我想要这样的排序: 2001-20032004-20062007-20092010-2012;其实不是数据排序，应该是数据填充。输入公式=LEFT(E3,4)+3&-&RIGHT(E3,4)+3 即可。怎样才能让数列自动加数怎样做才能让数列自动加数AA0001BB0001AA0002CC0001AA0003BB0002CC0002公式为=A1&000&COUNTIF(A$1:A1,A1)向下拖=TEXT(COUNTIF(A$1:A1,A1

29、),!&A1&0000)否则数字超过 9 就错误了。一个排序问题一个电子表格，格式是 101、102. 999,10101、10102. 99901,1010101,1020201. 9990101,请问如何将它排列成 101,10101,1010101,102,10201,1020101,. 999,99901,9990101 的形式。我在数字前加了个字母,比如d&数字,然后用排序就可以把它们按你的需求排列了.最后再把字母d去掉。数字的自动排序,插入后不变?1赵一总经理2赵二副经理3赵三副经理4赵四技术员5赵五6赵六员工如上的一个表,如何实现当我把赵六这一整行(第 6 行)插入到上面的表中时

30、,A 列的序列号不变?最后的效果如下:1赵一总经理2赵二副经理3赵六员工4赵三副经理5赵四技术员6赵五A1 单元格输入公式 =row(),往下拉,然后再插入。=SUBTOTAL(3,$B$2:$B2)在 A1 中输入公式：“=if(b1=,counta($b$1:b1)”后下拉复制至 A 列各行即可（“”不必输入）根据规律的重复的姓名列产生自动序号姓名序号张三1张三1李四2李四2赵五3赵五3赵五3王六4王六4=(A1A2)+N(B1)=IF(A3=A2,B2,B2+1)姓名已排序：B2=SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)姓 名 未 排 序 ： B2=IF(C

31、OUNTIF(A$2:A2,A2)1,VLOOKUP(A2,A:B,2,0),SUMPRODUCT(1/COUNTIF(A$2:A 2,A$2:A2)排名的函数用排名函数来对成绩进行排名，用起来非常地方便。=IF(ISERR(RANK(M3,M:M),RANK(M3,M:M)A 列是成绩,B 列是排名=SUMPRODUCT(A$1:A$9A1)/COUNTIF(A$1:A$9,A$1:A$9)+1自动排名公式=RANK(C3,$C$3:$C$12)=RANK(A2,$A$2:$A$11,0)=RANK(C2,$C$2:$C$65)+COUNTIF($C$2:C2,C2)-1百分比排名的公式写法

32、为：=PERCENTRANK($C$3:$C$12,C3)平均分及总分排名=AVERAGE(B2:E2)=RANK(F2,$F$2:$F$65536)求名次排名统计成绩时遇到一个分别求班级和年级总分名次排名的问题，不晓得应该运用什么公式来实现。班级名次：=SUMPRODUCT(BJ=A2)*(ZFE2)+1年级名次：=RANK(E2,ZF)公式下拖。排名次根据总分值大小，只将姓名排序后, 降序结果=INDEX(A$2:A$6,RANK(D2,D$2:D$6)根据总分值大小，只将姓名排序后, 升序=INDEX(A$2:A$6,RANK(D2,D$2:D$6,1)根据分数进行普通排名=RANK(A

33、2,$A$2:$A$12)=RANK(A2,A$2:A$12)+COUNTIF(A$2:A2,A2)-1=SUMPRODUCT(1*($E$3:$E$12=E3)=RANK(K3,$K$3:$K$26)=RANK(A2,A$2:A$12)=SUM(A$2:A$12=A2)/COUNTIF(A$2:A$12,A$2:A$12)=COUNTIF($K$3:$K$26,&K3)+1=INDEX($A$2:$A$7,MATCH(LARGE($C$2:$C$7,ROW(A1),$C$2:$C$7,0),1)=SUMPRODUCT($A$2:$A$12A2)/COUNTIF($A$2:$A$12,$A$2

34、:$A$12&)+1=RANK(D2,OFFSET($A$1,MATCH($A2,$A:$A,0)-1,3,COUNTIF($A:$A,$A2),1)对于普通排名分数相同时，按顺序进行不重复排名=RANK(K32,$K$32:$K$55)+COUNTIF($K$32:$K32,K32)-1=COUNTIF($K$32:K32,K32)-1+COUNTIF($K$3:$K$26,&K32)+1=SUMPRODUCT(1*($E$3:$E$12+ROW($E$3:$E$12)/100=($E3+ROW(E3)/100)=RANK(E3,$E$3:$E$12)+COUNTIF($E$3:E3,E3)

35、-1=SUMPRODUCT(1*($E$3:$E$12+$B$3:$B$12/100)=(E3+B3/100)依分数比高低名次成绩排名=RANK($E3,$E$3:$E$22) 內建方式排名=SUMPRODUCT(1*($E$3:$E$12=E3)一般方式排名=RANK(E3,$E$3:$E$22)+SUM(IF($E$3:$E$22E3,1/COUNTIF($E$3:$E$22,$E$3:$E$22),0)-COUNTIF($E$3:$E$22,&E3)一般方式排名=RANK(E3,$E$3:$E$12)+COUNTIF($E$3:E3,E3)-1 不重复排名=SUMPRODUCT(1*($

36、E$3:$E$12+ROW($E$3:$E$12)/100=($E3+ROW(E3)/100)=SUMPRODUCT(1*($E$3:$E$12+$B$3:$B$12/100)=(E3+B3/100) 不重复排名=SUMPRODUCT(1*($E$3:$E$12+$B$3:$B$12/100+$C$3:$C$12/10000)=(E3+B3/100+C3/10000) 不重复排名=RANK($E3,$E$3:$E$22,1) 倒排序美国式排名=RANK(K247,$K$247:$K$270)=RANK(B1,$B1:$H1)中国式排名=RANK(B2,$B$2:$B$21,0)=RANK(B1

37、,$B1:$H1)+COUNTIF($B$1:B1,B1)-1=SUM(IF($A$1:$E$1=A1,1/COUNTIF($A$1:$E$1,$A$1:$E$1),)=SUMPRODUCT($B$2:$B$21=B2)/COUNTIF($B$2:B$21,B$2:B$21)=SUMPRODUCT(B$3:B$21B3)*(1/COUNTIF($B$3:$B$21,$B$3:$B$21)+1（升序）=SUMPRODUCT(B$3:B$21B2)+1=SUM(IF($B$3:$B$21=B3,1/(COUNTIF($B$3:B$21,B$3:B$21)+1（升序）=SUM(IF($B$3:$B$

38、21B2,1/COUNTIF($B$2:B$21,B$2:B$21)+1=SUM(IF($A$1:$E$1=A1,1/COUNTIF($A$1:$E$1,$A$1:$E$1),)=SUM($B$2:$B$21B2)*(MATCH($B$2:B$21,B$2:B$21,)=ROW($1:$20)+1=SUM(IF($B$1:$H$1=E2)/COUNTIF($E$2:$E$21,$E$2:$E$21),第DBNUM1G/通用格式名)排序后排名=SUM(IF($B$2:$B$15=B2,1/COUNTIF($B$2:$B$15,$B$2:$B$15)=SUMPRODUCT(B$2:B$15=B2)

39、/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$25B16)+1由小到大排名=RANK(B3,$B$3:$B$12,1)=SUMPRODUCT($A$16:$A$25=A16)*($B$16

40、:$B$25B16-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)/10000B16+RO W(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)/10000B3)*(1/COUN

41、TIF(B$3:B$12,B$3:B$12)+1=SUMPRODUCT($A$16:$A$25=A16)*($B$16:$B$25B16)/COUNTIF($K$16:$K$25,$K$16:$ K$25)+1由小到大=SUMPRODUCT(B$3:B$12B3)*(1/COUNTIF(B$3:B$12,B$3:B$12)+1=SUMPRODUCT($A$16:$A$25=A16)*($B$16:$B$25B3)+1=COUNTIF($B$3:$B$21,&B3)+1=SUM(IF($B$3:$B$21B3,1,0)+1=19-FREQUENCY($B$3:$B$21,B3)+1=SUMPRO

42、DUCT($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()/1000B3-ROW()/1000,1,0)+1有并列分段排名=SUMPRODUCT($A$3:$A$21=A3)*($C$3:$C$21C3)+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)

43、-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$21C3)/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)/10000C3- ROW(C3)/10000)

44、+1=19-FREQUENCY($A$3:$A$21=A3)*($C$3:$C$21-ROW($C$3:$C$21)/1000),C3-ROW()/1000)+1成绩排名序号姓名杨增海语文数学英语1135136146234郭爱玲华志锋袁文飞138134134137138143141141135能否用一个公式直接找出所用考生中语文成绩中第 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、对英语进行排名，缺考计算在内。英语英语排名429623721