excel常用函数公式及技巧搜集(常用的)

EXCEL 提供直观快捷的数据输入、强大灵活的数据处理、丰富多样的图表制作、完善快捷 的报表设计、快速完备的统计分析功能，办公人员几乎没有不用到 EXCEL 的。如果我们 只满足手工操作这些功能，那我们处理一些重复性的工作时就只能象机器人一样进行机械 的操作。庆幸的是，OFFICE 的开发者们为大家准备了一个好方法，那就是 VBA。 哪些人需要 VBA 呢？ 如果你是信息部门的员工： 可能会对 EXCEL 的应用深有体会，大量的数据处理，如果变动一个小地方就可能是几十、 几百个文件或几千条记录的变动，有人会说我今天又要加班了，要是学会了 VBA，想加班 都没活干呀。- 如果你是办公室文员： 老板说：“把所有员工的考勤按日(月、季) 做个线图给我看看 ”。My God！做这么多图，要 累死人呀！没关系，要是学会了 VBA 作图部分，只需要几十分钟的时间写代码，再运行 程序，OK，不到一分钟完成了！下次老板再让做，没关系，调出代码再运行一遍嘛， EASY! 如果你是刚毕业的大学生： 面对激烈的竞争，面试的时候，别人要花 1 小时才能完成的数据处理问题，你 10 分钟搞定， 这时候主管一定会着急起来：“你什么时候能够上班 ” 。 如果你想学 VBA VBA 这么好，学起来会很难吗？不！其实 VBA 是程序语言中最易学，上手极快的一个， 并不强调你必须要有专门的程序开发基础，因为 EXCEL 提供了 VBA 录制功能。即使你不 会写代码也可以录制一段 VBA 代码去处理重复工作，如果忘记一些代码的语法了，录制 一段代码就能看到了，所以学起来最容易了。 EXCEL VBA 也 【身份证信息？提取】 从身份证号码中提取出生年月日 =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) 排名的函数 用排名函数来对成绩进行排名，用起来非常地方便。 =IF(ISERR(RANK(M3,M:M),“,RANK(M3,M:M) excel 常用函数公式及技巧搜集（常用的） 石 家 庄 仁 和 会 计 培 训 ： 学 校 整 理 提 供 ， 详 情 请 登 入 河 北 仁 和 会 计 官 网 学会计到仁和 仁和会计中国实战会计全国领导者 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$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(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) =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:$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) 依分数比高低名次成绩排名 =RANK($E3,$E$3:$E$22) 內建方式排名 =SUMPRODUCT(1*($E$3:$E$12=E3) 一般方式排名 excel 常用函数公式及技巧搜集（常用的） 石 家 庄 仁 和 会 计 培 训 ： 学 校 整 理 提 供 ， 详 情 请 登 入 河 北 仁 和 会 计 官 网 学会计到仁和 仁和会计中国实战会计全国领导者 =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*($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) =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/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$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)/COUNTIF(B$2:B$15,B$2:B$15) 位次排名 excel 常用函数公式及技巧搜集（常用的） 石 家 庄 仁 和 会 计 培 训 ： 学 校 整 理 提 供 ， 详 情 请 登 入 河 北 仁 和 会 计 官 网 学会计到仁和 仁和会计中国实战会计全国领导者 =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:$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/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$21,“&B3)+1 =SUM(IF($B$3:$B$21B3,1,0)+1 =19-FREQUENCY($B$3:$B$21,B3)+1 excel 常用函数公式及技巧搜集（常用的） 石 家 庄 仁 和 会 计 培 训 ： 学 校 整 理 提 供 ， 详 情 请 登 入 河 北 仁 和 会 计 官 网 学会计到仁和 仁和会计中国实战会计全国领导者 =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:$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) /COUNTIF($N$3:$N$21,$N$3:$N$21)+1 （需辅助列） 无并列分段排名 =SUMPRODUCT($A$3:$A$21=A3)*($C$3:$C$21-