EXCEL函数公式大全

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)

2、,MID(A2,7,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:A2,7

3、,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(A

4、1,15,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”) (以上公式会判断是否已过生日而

5、自动增减一岁)=YEAR(NOW()-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()-V ALUE(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,),TODA

6、Y(),y,)以 2006 年 10 月 31 日为基准日 ,按按身份证计算年龄 ( 周岁 ) 的公式=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

7、周岁(含 45周岁)以上至 55 周岁为 3女性 55 周岁(含 55周岁)以上为 4 =MATCH(DATEDIF(DATE(MID(K1,7,4),MID(K1,11,2),MID(K1,13,2),TODAY(),y),0,16 ,50,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,55+0,0,5,5*MOD(MID(K1,17,1),2)【年龄和工龄计算】 根据出生年月计算年龄=DATEDIF(A1,TODAY(),y

8、)=DATEDIF(A1,TODAY(),y)&周岁=DATEDIF(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( 起始日期 ,结束日

9、期 ,Y)计算距离退休年龄的公式=IF(E2=,IF(E2=V2, 已经退休 , 距离退休还有&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

10、,50)D2 为男或女 (可用身份证号码的公式生成) ；U2 为出生年月日 (可用身份证号码的公式生成) 。求工齡=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) 求两日期间的总月

11、数如果只需要算出周年的话，可以用 =datedif(1978-8,2006-5,Y)年龄及工龄计算有出生年月如何求年龄？ 有工作时间如何求工龄？(求出的结果为多少年另几个月，如： 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

12、)显示 4009=TEXT(DATEDIF(B2,TODAY(),y),00 年 )&TEXT(MOD(DATEDIF(B2,TODAY(),m),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 年 0

13、9 个月自动算出工龄日期格式为 (yyyy.mm.dd)能否用：(yyyy.mm.dd)这种格式来计算出工龄有多长呢? 以前用这样一段( =TEXT(RIGHT(YEAR(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)&个月 ,)【时

14、间和日期应用】 自动显示当前日期公式 =YEAR(NOW()当前年=MONTH(NOW()当前月=DAY(NOW()当前日如何在单元格中自动填入当前日期Ctrl+;如何判断某日是否星期天=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

15、(),AAA)= 一 ,TODAY()-3,TODAY()-1)关于取日期怎么设个公式使 A1 在年月日向后推 5 年,变成 2011-7-15 =DATE(YEAR(A1)+5,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)

16、&MID(A1,4,2 )&LEFT(A1,2)=YEAR($A2) &TEXT(MONTH($A2),00,) &TEXT(DAY($A2),00,) 有效性 ,但要设置储存格格式。也可以用下列两方法：1、先转换成文本 , 然后再用字符处理函数。2、数据-分列日期-MDY将“ 2005 年 9月”转换成“ 200509”格式 先用公式： =text(a1,yyyymm,)+0 然后将单元格格式为常规。 将“ 2005-8-6”格式转换为“ 20050806”格式用公式： =TEXT(A1,YYYYMMDD,)反之 ，将 20050806转为日期 2 005-8-6格式，可用公式： =DATE

17、(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)另四种公式 ：=text(a1,0000-00-00)=-TEXT(A1,#-00-00)，把单元格设置为日期格式=TEXT(20050806,0000-00-00)*1，单元格设置日期型=VALUE(LEFT(A1,4)&,-,&MID(A1,5,2)&,-,&RIGHT(A1,2)将“ 20060501”转换为“ 2006-05-01”格式 =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2) 将“ 199306”转换为“ 1993-6”公式 1：=LEFT(A3,4)&,-,&RIGHT(A

18、3,2)*1公式 2：=-TEXT(A3*100+1, #-00-00, ) 公式 2需要设置单元格格式， 公式 3：=TEXT(TEXT(A3&,01,0000-00-00,),e-m,)该公式不用设置数据显示： 2005-08-06显示： 2005-8-6显示： 2005-8-6显示： 2005-8-6自定义： e-m把 198405 转换成 1984.05一、查找 1984，替换 1984.二、如果全部是年月的话，我个人建议，1、采取辅助=mid(xxxxxx,1,4) & . & right(xxxxxx,2)2、选中这列，用数据中的分列。然后 ,三、单元格格式数字自定义，类型下面输入

19、： #.#将文本“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日？转成当年当月的日子 公式为： =date(year(now(),month(now(),22) 将“ 2006年 5月”转换成“ 2006年 05月”公式为：=TEXT(A8,yyyy年mm月;)也可以这样处理：选中单元格，设置单元格公式数字自定义，将yyyy “年” m “月”改为：yyyy “年” mm “月”

20、，即可。但这方法打印出来显示为：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-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), 年,MON

21、TH(A1),月) =YEAR(A1)&年&MONTH(A1)& 月也可以自定义格式 $-404e年m月将“ 1978-5-2”包含年月日的日期转换成“ 197805”只有年月的格式=year(A1)&text(month(A1),00)要将“ 99.08.15”格式转换成“ 1999.08.15”如何做选中列，数据菜单中选分列，分列过程中 格式 选3期YMD，结束。 要保持 2005/8/6 格式当输入 2005/8/6后系统自动变成 2005-8-6，要保持 2005/8/6格式，可以使用强制文本(前 面加号)或使用公式 =TEXT(A1,YYYY/MM/DD) 。也可以用另一种公式：=I

22、F(ISERROR(TEXT(A1,yyyy/mm/dd),TEXT(A1,0000!/00!/00),TEXT(A1,yyyy/mm/d d)将“二OO三年十二月二十五日”转为“2003-12-25”格式，1、 可以用数组公式将中文日期转化为日期系列数 =14610+MATCH(SUBSTITUTE(A3, 元,一),TEXT(ROW($14611:$55153),DBNum1yyyy 年 m 月 d 日),0)该公式速度较慢。2、改进后的公式，速度要快的多： =DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW($1900:$2100),DBNum10000),0),M

23、O NTH(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年的日期格式。日期格式转换如A列是月份数为8, B列是日期数为18，如何在C列显示“ 8月18日”=A1& 月 &B1& 日 反之，要将C列的“ 8月18日”直接分别到D、E列，显示月份和日期，月数份=LEFT(C5,FIND(月,C5)-1)日期数=MID(C5,FIND(月,C

24、5)+1,FIND(日,C5)-FIND(月,C5)-1) 也可分别用公式：=month(-c5)=day(-c5)日期格式转换问题输入的日期是 :04-07-26. 与另一格的 001 合并,合并出来是 :040726001.=TEXT(A1,YYMMDD)&001要想自动取得“编制日期：XXXX年 X月X日”可在该单元格输入 =编制日期：&TEXT(TODAY(),yyyy 年m月d日)【排名及排序筛选】一个具有 11 项汇总方式的函数 SUBTOTAL=SUBTOTAL(9,$B$2:B2)在数据筛选求和上有意想不到的功能， 11 项功能为： 1、求平均数， 2、求计数， 3、求 计数值

25、(自动筛选序列) 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)=IF(ROW()50,(ROW()*2)-100,(ROW()*2)-1)=ROW()*2-1-(ROW()50)*99自动生成序号比如在第二列中输入内容回车后第一列的下一行自动生成序列号。=IF(B2,A2+1,)如何自动标示A栏中的数字大小排序？=RANK(

26、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)重复数据得到唯一的排位序列想得到数据的出现总数吗(122,3,4,4,5数据的出现总数为5)？解答：不需要插列，不需要很多的函数就行了 =RANK(B3,B$3:B$12)+COUNTIF(B$3:B3,B3)-1按字符数量排序制作歌曲清单时，习惯按字符数量来排列分类，但是EXCEL并不能直接按字数排序。需要先计算出每首歌曲的字数，然后再进行排序。如A、B列分别为“歌手”和“歌名”

27、，在C1输入“字数”，在C2输入公式： =LEN(B2) 下拖，单击C2,单击工具栏上的“升序排列”即可，删除C列。排序字母与数字的混合内容日常使用中，表格经常会有包含字母和数字混合的数据，对此类数据排序时，通常是先 比较字母的大小，再比较数字的大小，但EXCEL是按照对字符进行逐位比较来排序的，如下表：A7排在第5位，而不是第1位。排序结果无法令人满意。A1A1222A293A3174A435A76B207B38C1449C510C33AB1A7:A0072A29A0293A43:A0434A122:A1225A317A3176B3:B0037B20B0208C5C0059C33C03310

28、C144C144如果希望EXCEL改变排序 变。在B1中输入公式：RIGHT(A1,LEN(A1)-1),3) 下单击B2,单击工具栏上的的规则，需要将数据做一些改LEFT(A1,1)& RIGHT(000& 拖“升序排列”即可。随机排序如A、B列分别为“歌手”和“歌名”，在C1输入“次序”，在C2输入公式：=RAND (),下拖，单击C2,单击工具栏上的“降序排列”即可对歌曲清单进行随机排序。排序的问题我想要这样的排序：2001-20032004-20062007-20092010-2012;其实不是数据排序，应该是数据填充。输入公式=LEFT(E3,4)+3&-&RIGHT(E3,4)+3

29、 即可。怎样才能让数列自动加数怎样做才能让数列自动加数AA0001BB0001AA0002CC0001AA0003BB0002CC0002公式为=A1 &000&COUNTIF(A$1:A1,A1) 向下拖 =TEXT(COUNTIF(A$1:A1,A1),!&A1&0000)否则数字超过 9 就错误了。一个排序问题一个电子表格，格式是 101、102999,10101、1010299901,1010101,10202019990101，请 问如何将它排列成 101,10101,1010101,102,10201,1020101，999,99901,999010 的形式。我在数字前加了个字母，

30、比如d&数字，然后用排序就可以把它们按你的需求排列了最后再把字母d去掉。数字的自动排序,插入后不变？1赵一总经理2赵二副经理3赵三副经理4赵四技术贝5赵五6 赵六 员工如上的一个表 ,如何实现当我把赵六这一整行 (第 6 行)插入到上面的表中时 ,A 列的序列 号不变 ?最后的效果如下 :1 赵一总经理2 赵二副经理3 赵六员工4 赵三 副经理5 赵四技术员6 赵五A1单元格输入公式=row(),往下拉，然后再插入。=SUBTOTAL(3,$B$2:$B2)在A1中输入公式：“ =if(b仁countabWI:) ”后下拉复制至 A列各行即可(“” 不必输入)根据规律的重复的姓名列产生自动序号

31、姓名 序号 张三 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(COUNTIF(A$2:A2,A2)1,VLOOKUP(A2,A:B,2,0),SUMPRODUCT(1/COUNTIF(A$2 :A2,A$2:A2)z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z

32、z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z-_z z_z排名的函数用排名函数来对成绩进行排名，用起来非常地方便。 =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)+COUN

33、TIF($C$2:C2,C2)-1百分比排名的公式写法为：=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,

34、RANK(D2,D$2:D$6,1) 根据分数进行普通排名=RANK(A2,$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) =SUMPRODU

35、CT($A$2:$A$12A2)/COUNTIF($A$2:$A$12,$A$2:$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)

36、/100) =RANK(E3,$E$3:$E$12)+COUNTIF($E$3:E3,E3)-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:$

37、E$12)+COUNTIF($E$3:E3,E3)-1 不重复排名=SUMPRODUCT(1*($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/1 0000) 不重复排名=RANK($E3,$E$3:$E$22,1) 倒排序美国式排名=RANK(K247,$K$247:$K$270)=R

38、ANK(B1,$B1:$H1)中国式排名=RANK(B2,$B$2:$B$21,0)=RANK(B1,$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,

39、1/(COUNTIF($B$3:B$21,B$3:B$21)+1(升序)=SUM(IF($B$3:$B$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

40、,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$25B16)+1由小到

41、大排名=RANK(B3,$B$3:$B$12,1) =SUMPRODUCT($A$16:$A$25=A16)*($B$16:$B$25B16-RO W(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

42、($A$16:$A$25=A16)*($B$16:$B$25-ROW($B$16:$B$25)/10000B3)*(1/COUNTIF(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)+

43、1=SUM(IF($B$3:$B$21B3,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()/1000v$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$

44、3:$B$21-ROW($B$3:$B$21)/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)-2),),ROW(INDIRECT(1:&COUNTIF($A$3:$A$21,A3),0)=MATCH(C3,LARGE(IF($A$3:$A$21= A3,$C$3:$C$21),R

45、OW(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(C 3)/10000)+1=19-FREQUENCY($A$3:$A$21=A3)*($C$3:$C$21-ROW($C$3:$C$21)/1000),C3-ROW()/10 00)+1成绩排名序号姓名语文

46、数学英语1杨增海1351361462郭爱玲1381371413华志锋1341381414袁文飞134143135能否用一个公式直接找出所用考生中语文成绩中第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$4 仃)能否用一个公式直接找出所用考生中语文成绩中按与考人数的35%切线中位于第35%的成绩是多少？升幕=SMALL(C2:C417,416*0.35)=PERCENTILE($C$2:$C$4 仃,0.35)=LARGE(C2:C417,416*0.35)=PERCENTILE($C$2:$C$417,1-0.35)如何排名1、对英语进行排名，