# EXCEL常用函数公式及技巧搜集之二

2017-01-29

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 138 141 4 袁文飞 134 143 135

=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))

=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 3 72 1 48 5 48 5 72 1 54 4 42 9 缺考 缺考 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 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

=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(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),)

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

=SUBTOTAL(3,\$B\$2:B2)*1

=SUBTOTAL(9,\$B\$2:B2)

=IF(ISNUMBER(FIND("A",C2))+ISNUMBER(FIND("A",B2))>0,"OK","")

=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()))&""（数组公式）

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

㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜

EXCEL中如何删除*号

1用公式：=SUBSTITUTE(A2," ","")  注：第一对双引号中有一空格。而第二个“”中是无空格的。

2、利用查找－替换，一次性全部解决。

“编辑”－“替换”（或Ctrl+H），在“查找”栏内输入一空格，“替换”什么也不输入（空白）。然后“全部替换”即可。

3、有一个专门删除空格的函数: TRIM()

8900079501     8900079501~

1900078801     1900078802~

=SUBSTITUTE(B2,"~","")

Excel中如何通过编辑“有效数据”来禁止录入空格？烦请大侠们费心解答。

(注：COUNTIF(A1,"* *") 在单元格有空格时结果为1,没有空格时结果为0

windows2000变成windows2K

=REPLACE(B2,8,3,"K")

EETTCCTTFF变成EEUUCCUUFF

=SUBSTITUTE(B394,"TT","UU")

EETTCCTTFF变成EEUUCCTTFF

=SUBSTITUTE(B397,"TT","UU",1)

10000                                变成带有人民币符号的字符

151581                                  变成带有欧元符号的字符

1451451                                  变成中文繁体的字符

15748415                                变成中文简体的字符

=TEXT(B73,"￥0.00")                          10000.00

=TEXT(B74,"€0.00")                          €151581.00

=TEXT(B75,"[DBNum2]G/通用格式")         壹佰肆拾伍万壹仟肆佰伍拾壹

=TEXT(B76,"[DBNum1]G/通用格式")         一千五百七十四万八千四百一十五

=INDIRECT("r"&(COLUMN()-3)*48+ROW()&"C1",0) 复制到其他单元格

N列变M列公式归纳为

=OFFSET(\$A\$1,INT(((ROW(A1)-12)*m+COLUMN(A1)-1)/n),MOD((ROW(A1)-1)*m+COLUMN(A1)-1,n))

=OFFSET(\$A\$1,INT(((ROW(A1)-1)*7+COLUMN(A1)-1)/4),MOD((ROW(A1)-1)*7+COLUMN(A1)-1,4))   四列变七列

=OFFSET(\$A\$1,INT(((ROW()-20)*10+COLUMN()-1)/7),MOD((ROW()-20)*10+COLUMN()-1,7))         七列变十列

=OFFSET(\$A\$1,ROW(\$A1)*4-COLUMNS(C:\$F),)

=OFFSET(\$A\$1,(ROW()-3)*4+MOD(COLUMN()-8,4),)

=OFFSET(\$A\$1,ROW(A1)*4-4+MOD(COLUMN()-13,4),)

=OFFSET(\$F\$1,INT(ROW(1:1)/4+3/4)-1,MOD(ROW()-1,4))

=OFFSET(\$F\$1,INT((ROW(1:1)-1)/4),MOD(ROW()-1,4))

=OFFSET(\$F\$1,ROUNDUP((ROW(1:1)/4),0)-1,MOD(ROW()-1,4))

=OFFSET(\$F\$1,(ROW()-1)/4,MOD(ROW()-1,4))

=TEXT(INT(ROW()/4+3/4),"00")

=IF(TRUNC((ROW()-1)/4,0)<9,"0"&TRUNC(ROW()/4-0.01,0)+1,TRUNC(ROW()/4-0.01,0)+1)

=TEXT(ROUNDUP(ROW()/4,),"00")

=TEXT(ROW(2:2)/4,"00")

 a1 b1 c1 d1 e1 f1 g1 h1 i1 a2 b2 c2 d2 e2 g2 h2 i2 a3 c3 d3 g3 h3 i3 a4 c4 g4 h4 i4 A5 c5 g5 h5 g6

 a1 a2 a3 a4 A5 b1

=IF(ROW()>COUNTA(\$A\$1:\$I\$10),"",INDEX(\$A\$1:\$I\$10,MOD(SMALL(IF(\$A\$1:\$I\$10<>"",ROW(\$A\$1:\$I\$10)+COLUMN(\$A\$1:\$I\$10)*100000),ROW()),100000),INT(SMALL(IF(\$A\$1:\$I\$10<>"",ROW(\$A\$1:\$I\$10)+COLUMN(\$A\$1:\$I\$10)*100000),ROW())/100000)))

1、先确定1列的最适合的列宽,再将其宽度乘以分成列数,

2、编辑—填充—内容重排。

3、数据—分列。

china  China

=PROPER(B160)

lafayette148      LAFAYETTE148

=UPPER(B1)

=LOWER(B1)         大写字母变成小写字母公式）

姓名用字，有的是三个汉字，有的是两个汉字，打印出来很不美观，要使姓名用字是两个字的与三个字的左右对齐也有两种方法:

方法一:格式设置法。选中我们已经删除完空格的姓名单元格，单击“格式→单元格”在打开的“单元格格式”对话框中的水平对齐方式中选择“分散对齐”选项，确定退出后即可使学生姓名用字左右对齐。

2.0

12.001

=COUNTA(B252:B262)

74P125148    74P125148

=EXACT(B53,C53)

=ROW()/2=INT(ROW()/2)    设定颜色

=VALUE(B1)

Excel表格里如何插入页码的?

Excel页脚设置页码是按顺序来的，首页为第1页。如何设置首页为第5页？

shift依次点表单的标签。

1．文件页面设置页眉/页脚页脚（F,选自己需要的页脚格式

2．文件打印整个工作簿。

首先：点CTRL+F3打开定义名称，再在上面输入“纵向当前页”，在下面引用位置处输入=IF(ISNA(MATCH(ROW(),GET.DOCUMENT(64))),1,MATCH(ROW(),GET.DOCUMENT(64))+1)。然后再继续添加第二个名称：“横向当前页”，在下面引用位置处输入=IF(ISNA(MATCH(column(),GET.DOCUMENT(65))),1,MATCH(column(),GET.DOCUMENT(65))+1)。再输入“总页”；引用位置处输入：=GET.DOCUMENT(50)+RAND()*0。最后再定义“无拘无束的页眉”；引用位置：=""&IF(横向当前页=1,纵向当前页,横向当前页+纵向当前页)&"/"&总页&""。

“=IF(ISNA(MATCH(ROW(),GET.DOCUMENT(64))),1”此句利用MATCH之模糊查找功能将当前行号与分页符下行（分页符下一行是一个单元N行的一维数组，文档有几页则有几行，本实例文档有三页，请看公式求值之计算图示）做比较，此处省略MATCH第三参数，即查找小于等于目标值，如果目标值大于当前行号，则MATCH返回错误值。那么此处再用IF(ISNA(),1)加以判断，即若找不到小于等于当前行号的值则显示1，表示当前行处于第一页。

MATCH(ROW(),GET.DOCUMENT(64))+1

=GET.DOCUMENT(50)+RAND()*0

GET.DOCUMENT(50)即求当前设置下欲打印的总页数，其中包括注释，如果文件为图表，值为1

RAND（）*0作用是当文件分页数改变时，本公式结果根随变化，起公式结果刷新作用。

="第"&IF(横向当前页=1,纵向当前页,横向当前页+纵向当前页)&"页/共"&总页&"页"

0条评论