1、1excel 常用函数公式及技巧搜集（常用的）【身份证信息？提取】从身份证号码中提取出生年月日=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(A2A2)+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
2、)19 排名的函数用排名函数来对成绩进行排名，用起来非常地方便。=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百分比排名的公式写法为：=PERCENTRANK($C$3:$C$12,C3)平均分及总分排名=AVERAGE(B2:E2)=RANK(F2,$F$2:$F$
3、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)根据分数进行普通排名20=RANK(A2,$A$2:$A$12)=RANK(A2,A$2:A$12)+COUNTIF(A$2:A2,A2)-1=SUMPRODUCT(1*
4、($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:$A$12&“)+1=RANK(D2,OFFSET($A$1,MATCH($A2,$A:$A,0)-1,3,COUNTIF($A
5、:$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)-1=SUMPRODUCT(1*($E$3:$E$12+$B$3:$B$12/100)=(E3+B3/100)依分数比高低名次
6、成绩排名=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不重复排名21=SUMPRODUCT(1*($E$3:$E$12+ROW($E$3:$E$12)/100=($E3+ROW(E3)/100)=SUMPRODUCT(
7、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,$B1:$H1)+COUNTIF($B$1:B1,B1)-1=SUM(IF($A$1:$E$1=A1,1/COUNT
8、IF($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$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
9、($B$2:B$21,B$2:B$21,)=ROW($1:$20)+221=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)/COUNTIF(B$2:B$15,B$2:B$15)位次排名23=IF($B2:$O2=0,RANK($B2:$O2,$B2:$O2,0),)根据双列成绩进行共同排名=RANK(C345,($C$345:$C$356,
10、$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:$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$
11、16:$B$25+ROW($B$16:$B$25)/10000B16+ROW(B16)/10000)+124由小到大=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/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由小到
12、大=SUMPRODUCT(B$3:B$12B3)+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=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()/1000B3-ROW()/1000,1,0)+1有并列分段排名=SUMPRODUCT($A$3:$A$21=A3)*($C$3:
13、$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),ROW(INDIRECT(“1:“&COUNTIF($A$3:$A$21,A3),0)=SUMPRODUCT($A$3:$A$21=A3)*($C$3:$C$21C3)/COUN
14、TIF($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)+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 1462郭爱玲138 137 1413华志锋134 138 1414 袁文 134 143 13526飞能否用一个公式直接找出所用考生中语文成
15、绩中第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)如何
16、排名1、对英语进行排名，缺考不计算在内。2、对英语进行排名，缺考计算在内。英语 英语排名42 962 372 148 548 572 154 442 9缺考 缺考 45 846 7缺考不计算在内27b2=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$
17、11,0)如果隔几行排名，如下表，第五行、第九行和第十二行不参与排名。单位 数据 排名A 1 8A 5 7A 6 6小计 12B 8 4B 9 3B 7 5小计 24C 18 1C 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,“0,“OK“,“)名次筛选名次=RANK(K5,K$2:K$435)班名次=RANK(K6,OFFSET(K$2,MATCH(A6,A:A,
18、)-2,COUNTIF(A$1:A$500,A6)如何实现快速定位（筛选出不重复值）29=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()自动为性别编号的问
19、题有一个编码,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),“2000
20、0“)向下拖【文本与页面设置】EXCEL 中如何删除*号在录入帐号是录入了*号，如何删除。可以用函数 SUBSTITUTE(a1,“*“,“)查找*，替换为空。30将字符串中的星号“*”替换为其它字符在查找栏输入*替换为“-” 即可。去空格函数如何删去单元格中的空格，如姓名前，中，后的空格，即单元格中是两个字的人名中间有一个空格，想删去有何方法。如：中 国，改为：中国。1、用公式：=SUBSTITUTE(A2,“ “,“) 注：第一对双引号中有一空格。而第二个“”中是无空格的。2、利用查找替换，一次性全部解决。“编辑”“替换” （或 Ctrl+H） ，在“查找”栏内输入一空格， “替换”什么也
21、不输入（空白） 。然后“全部替换”即可。3、有一个专门删除空格的函数: TRIM()在 EXCEL 编辑栏里，不管输中文还是英文只能输一个字节的空格，但如果字与字中间是两个字节的空格，那么 TRIM（）就不起作用了，它就不认为是一个空格，而是一个汉字，怎么去“TRIM”也没用。如：单元格 A1 中有“中 心 是” ，如果用 TRIM 则变成“中 心 是”， 想将空格全去掉，只能用 SUBSTITUDE（）函数，多少空格都能去掉。如何去掉字符和单元格里的空格8900079501 89000795011900078801 1900078802=SUBSTITUTE(B2,“,“)怎样快速去除表中不同行和列的空格编辑-定位-定位条件-空值,可选中所有空单元格, 再删除。如何禁止输入空格在 Excel 中如何通过编辑“有效数据”来禁止录入空格？烦请大侠们费心解答。解答：有效性公式。=COUNTIF(A1,“* *“)=0(注： COUNTIF(A1,“* *“) 在单元格有空格时结果为 1,没有空格时结果为 0如希望第一位不能输入空格:countif(a1,“ *“)=0
