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 成绩排名
能否用一个公式直接找出所用考生中语文成绩中第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、对英语进行排名,缺考不计算在内。
缺考不计算在内 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)) 如果隔几行排名,如下表,第五行、第九行和第十二行不参与排名。
=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,如何达到下表: 性别 男 男 女 男 女 男的也是从0001-9999 女的也是从0001-9999 如果你是已经输入了其它信息,仅仅为快速输入编码的话。用筛选可以实现吧。 用公式:=IF(A2="",TEXT(COUNTIF(A$2:A2,A2),"10000"),TEXT(COUNTIF(A$2:A2,A2),"20000"))向下拖 ㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜ 【文本与页面设置】 EXCEL中如何删除*号 在录入账号是录入了*号,如何删除。 可以用函数 SUBSTITUTE(a1,"*","") 查找~*,替换为空。 将字符串中的星号“*”替换为其它字符 在查找栏输入~* 替换为“-”即可。 去空格函数 如何删去单元格中的空格,如姓名前,中,后的空格,即单元格中是两个字的人名中间有一个空格,想删去有何方法。如:中 1、用公式:=SUBSTITUTE(A2," ","") 2、利用查找-替换,一次性全部解决。 “编辑”-“替换”(或Ctrl+H),在“查找”栏内输入一空格,“替换”什么也不输入(空白)。然后“全部替换”即可。 3、有一个专门删除空格的函数: TRIM() 在EXCEL编辑栏里,不管输中文还是英文只能输一个字节的空格,但如果字与字中间是两个字节的空格,那么TRIM()就不起作用了,它就不认为是一个空格,而是一个汉字,怎么去“TRIM”也没用。如:单元格A1中有“中 心 是”,如果用TRIM则变成“中 心 是”,想将空格全去掉,只能用SUBSTITUDE()函数,多少空格都能去掉。 如何去掉字符和单元格里的空格 8900079501 1900078801 =SUBSTITUTE(B2,"~","") 怎样快速去除表中不同行和列的空格 编辑-定位-定位条件-空值,可选中所有空单元格, 再删除。 如何禁止输入空格 在Excel中如何通过编辑“有效数据”来禁止录入空格?烦请大侠们费心解答。 解答:有效性公式。=COUNTIF(A1,"* *")=0 (注:COUNTIF(A1,"* *") 在单元格有空格时结果为1,没有空格时结果为0 如希望第一位不能输入空格:countif(a1," *")=0 如希望最后一位不能输入空格:countif(a1,"* ")=0) 代替单元格中字符串 单元格编号,开始位数,从开始位数算起第几位数,要用于代替的的字符串。 windows2000变成windows2K =REPLACE(B2,8,3,"K") 单元格编号,要代替掉的字符,要用作代替的字符,第几个。 代替单元格B391中的全部TT,改为UU。 EETTCCTTFF变成EEUUCCUUFF =SUBSTITUTE(B394,"TT","UU") 只代替单元格B391中的第一次出现的TT,改为UU。 EETTCCTTFF变成EEUUCCTTFF =SUBSTITUTE(B397,"TT","UU",1) 把单元格中的数字转变成为特定的字符格式 函数中的第二个参数的双引号一定不能是中文格式的(不能用任意中文输入法输入的双引号。) 实例: 操作步骤: =TEXT(B72,"$0.00") 把有六百多个单元格的一列,变成一页的多列 有一张表,共有14页,但每页只有一列,如何把他们整合在一起,变成一页(按每页的顺序),如果使用剪切和粘贴的方式,那样太麻烦。 =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") 多行数据排成一列
将单元格一列分为多列 如果有一列资料需要分为多列,只要先将此列选中,然后再选择“数据”→“分列”,此时会出现一个对话框,选“固定宽度”或“分隔符号”。如为前者则下一步后只要用鼠标轻点资料即可以按任意宽度进行分割了,如为后者则只要有明显的分隔符号即可,下一步后就可以自定义刚分的列的格式了,定好后就算完成了。 步骤: 1、先确定1列的最适合的列宽,再将其宽度乘以分成列数,即 分列前的列宽=最适合的列宽×需分成的列数. 2、编辑—填充—内容重排。 3、数据—分列。 首写字母大写 把单元格编号中的单词首写字母变成大写字母,其余字母变成小写。 如china =PROPER(B160) 把单元格编号中的小写字母变成大写字母 lafayette148 =UPPER(B1) =LOWER(B1) 让姓名左右对齐 姓名用字,有的是三个汉字,有的是两个汉字,打印出来很不美观,要使姓名用字是两个字的与三个字的左右对齐也有两种方法: 方法一:格式设置法。选中我们已经删除完空格的姓名单元格,单击“格式→单元格”在打开的“单元格格式”对话框中的水平对齐方式中选择“分散对齐”选项,确定退出后即可使学生姓名用字左右对齐。 方法二:函数公式法。利用Excel中的“IF”、“LEN”、“MID”三种函数组合可使姓名用字左右对齐。具体示例为:在C3单元格中输入公式:“=IF(LEN(B2)>=3,B2,(MID(B2,1,1)&&" "&&MID(B2,2,1)))”,确定后利用填充柄将该公式进行复制即可。 数字居中而小数点又对齐 可在小数点的任一边替无效的零加入空间,以便当格式设定为固定宽字型 ,小数点可以对齐。 格式-单元格-数字-自定义-???.???-确定 请问:小数点后的“0”还有办法显示吗?比如: 格式-单元格-数字-自定义-???.0?-确定 计算指定单元格编号组中非空单元格的数量 计算B252到B262之间的非空单元格的数量。 =COUNTA(B252:B262) 比较两个单元格内容是否一致 74P125148 比较单元格B53与C53中的内容是否一致。 假如内容一致,那么返回值为TRUE,不一致的话,返回值为FALSE。 =EXACT(B53,C53) 结果:TRUE 怎么样设置才能让这一列的每个单元格只能输入12位 怎么样设置才能让某一列或某一行的每个单元格只能输入12位,(阿拉伯数字和26个英文字母在内,没有中文。) 选中A列,设置数据有效性:自定义>公式:“=LEN(A1)=12” 如何让工作表奇数行背景是红色偶数行背景是蓝色 用条件格式 =ROW()/2=INT(ROW()/2) 条件格式: 公式为 =MOD(ROW(),2)=0 计算特定的一组单元格中,满足条件的单元格的个数 仍以上题为例,计算三个人在B307到B313中各自所占的单元格数。 李六的: 王武的: 陈丰的: 姓名: 结果: 把文本格式的数字转换成真正的数字 =VALUE(B1) 设置页码 如何设置“第×页,共×页”页码。 在页脚中设置:第&[页码]页,共&[总页码]页 Excel表格里如何插入页码的? 我想把表格中的第1页的页码从第30页开始编,不知道该如何实现,哪位高手能帮忙? 在页面设置的页眉页脚中设置。 在插入页脚中输入&[页码]+29即可。 如何设置页脚首页为第5页 Excel页脚设置页码是按顺序来的,首页为第1页。如何设置首页为第5页? 在页脚输入“第 &[页码]+4 页”,结果本该显示“第1页”的就显示第5页了。(用于多个工作表全选) 页面设置—页面—起始页码输入5(用于单个工作表)。 表格的页脚问题 是这样的,我每个表格有4张,总共一个文件里面有6个表格,相当于总共24页,我希望它能够自动打,而且我想设置页脚为,共24页,第?页,怎么办? 试一试选择所有的工作表(工作组)然后再设置页脚,打印的时候也是用工作组打印。 把所有工作表选中就可以了然后你再点打印,或者你先浏览,再设置也行! 按shift依次点表单的标签。 其实,就是在选择浏览或者打印前,先选中你想要的工作表,然后再一个个的浏览,就相当于你的操作对所有工作表都已经起了作用似的。 请楼主试一试,按以下步骤办: 1.文件→页面设置→页眉/页脚→页脚(F),选自己需要的页脚格式 2.文件→打印→整个工作簿。 无拘无束的页眉 页眉和页脚大家都用过吧?用得最多的莫过于当前第几页/总共第几页。但你是否想过将“第N页/总M页”无拘无束的放置,而不是只能置于页眉页脚中?,现教你一法,可以通用。到任何地方均可使用。 现在你在工作表任何处输入=无拘无束的页眉即可。 本公式核心在于GET.DOCUMENT,这是4.0宏函数,OFFICE 97及以前版专用,新版OFFICE中仍兼容,但只限定义名称中使用。 在帮助中说(64和65为其参数):64 行数的数组,相应于手动或自动生成页中断下面的行。65 列数的数组。相应于手动或自动生成的页中断右边的列。" 本公式中取64,用于计算当前行与分页符之前后关系.GET.DOCUMENT(64)即返回分页符所在行下一行之行号(亦即第二页第一行)。 判断当前行是否大于分页符所在行 “=IF(ISNA(MATCH(ROW(),GET.DOCUMENT(64))),1”此句利用MATCH之模糊查找功能将当前行号与分页符下行(分页符下一行是一个单元N行的一维数组,文档有几页则有几行,本实例文档有三页,请看公式求值之计算图示)做比较,此处省略MATCH第三参数,即查找小于等于目标值,如果目标值大于当前行号,则MATCH返回错误值。那么此处再用IF(ISNA(),1)加以判断,即若找不到小于等于当前行号的值则显示1,表示当前行处于第一页。 取得当前行所在页 =MATCH(ROW(),GET.DOCUMENT(64))+1 如果前一个MATCH返回FALSE,则取IF函数第三参数值即MATCH(ROW(),GET.DOCUMENT(64))+1 此参数再用MATCH在GET.DOCUMENT(64)产生的数组中查找当小于等于前行号的数值,若数组中第N个值小于等于当前行号,则当前行在N+1页。 取得总页 =GET.DOCUMENT(50)+RAND()*0 GET.DOCUMENT(50)即求当前设置下欲打印的总页数,其中包括注释,如果文件为图表,值为1 RAND()*0作用是当文件分页数改变时,本公式结果根随变化,起公式结果刷新作用。 获取“横向当前页” 横向当前页与纵向当前页原理相同,改ROW()为COLUMN(),并将GET.DOCUMENT参数改为65即可 若你的工作表只有纵向分页或者横向分页,那么现在就可以使用前面的公式定义的名称获取当前页及总页了;但如果分页方式为横向多页纵向也多页呢?则在将以上“横向当前页”与“纵向当前页”无缝接合方可使用,否则将返回错误结果。 最后生成“无拘无束的页眉”(或者改称文件分页) ="第"&IF(横向当前页=1,纵向当前页,横向当前页+纵向当前页)&"页/共"&总页&"页" 公式解说完毕!各位可以用不同的文字定义名称在各自的工作表中试用了。 |
|