位移速度公式常用的Excel函数公式(整理版)

常用的Excel函数公式
1、查找重复内容
=IF(COUNTIF(A:A,A2)>1,重复
2、重复内容首次出现时不提示
=IF(COUNTIF(A$$2:A2,A2)>1,重复
3、重复内容首次出现时提示重复
=IF(COUNTIF(A2:A99,A2)>1,重复
4、统计不重复的个数
=SUMPRODUCT(1COUNTIF(A2:A9,A2:A9))
5、提取不重复的内容
C2单元格输入以下数组公式，按Ctrl+ Shift+Enter，向下复制。
=INDEX(A:A,1+MATCH(,COUNTIF( C$$1:C1,A$$2:A$$10),))&
6、查找重复内容公式：
=IF(COUNTIF(A:A,A2)>1,重复。
7、求某一区域内不重复的数据个数
例如求A1:A100范围内不重复数据的个数，某个数重复多次出现只算一个。有两种计算方法：
一是利用数组公式：
=SUM(1COUNTIF(A1:A100,A1:A100))
输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号。
二是利用乘积求和函数：
=SUMPRODUCT(1COUNTIF(A1:A100,A1:A100))
8、几个常用的汇总公式
A列求和：=SUM(A:A)
A列最小值：=MIN(A:A)
A列最大值：=MAX (A:A)
A列平均值：=AVERAGE(A:A)
A列数值个数：=COUNT(A:A)或=COUNTA（A：A）
9、优秀人数：=COUNTIF(C2:C56,
10 优秀率：=COUNTIF(C2:C56,
11、及格人数：=COUNTIF(C2:C56,
12、及格率： =COUNTIF(C2:C56, ”)COUNT(C2:C56)
13、成绩排名
=(A2,A$$2:A$$7)或=RANK(K2，K$$2:K$$56)
14、中国式排名（相同成绩不占用名次）
=SUMPRODUCT((B$$2:B$$7>B 2)COUNTIF(B$$2:B$$7,B$$2:B$$7))+1
15、90分以上的人数
=COUNTIF(B1:B7,
16、各分数段的人数（不及格、及格、优秀）
同时选中E2:E4，输入以下公式，按Shift+Ctrl+Enter
=FREQUENCY(B2:B56,{59.5;79.5;100})
17、按条件统计平均值
=AVERAGEIF(B2:B7,男
18、多条件统计平均值
=AVERAGEIFS(D2:D7,C2:C7,男销售
1

19、分数段人数统计：
（1） =COUNTIF(K2:K56,——求K2到K56区 域100分的人数；假设把结果存放于
K57单元格；
（2） =COUNTIF(K2:K56,－K57 ——求K2到K56区域95～99.5分的人数；假设把
结果存放于K58单元格；
（3）=COUNTIF(K2:K56,－SUM(K57:K58) ——求K2到K56区域90～94.5分的人数；
假设把结果存放于K59单元格；
（4）=COUNTIF(K2:K56,－SUM(K57:K59) ——求K2到K56区域85～89.5分的人数；
假设把结果存放于K60单元格；
（5）=COUNTIF(K2:K56,－SUM(K57:K60) ——求K2到K56区域70～84.5分的人数；
假设把结果存放于K61单元格；
（6）=COUNTIF(K2:K56,－SUM(K57:K61) ——求K2到K56区域60～69.5分的人数；
假设把结果存放于K62单元格；
（7） =COUNTIF(K2:K56,——求K2到K56区域60分以下的人数；假设把结果存放
于K6 3单元格；
（8）COUNTIF函数也可计算某一区域男、女生人数。
如：=COUNTIF(C2:C351,男——求C2到C351区域（共350人）男性人数；
（9）优秀率： =SUM(K57:K60)55*100
（10）及格率： =SUM(K57:K62)55*100
20、根据出生年月计算年龄
=DATEDIF(A2,TODAY(),或
=TRUNC((DAYS360(H6,
21、根据身份证号码提取出生年月日
=--TEXT(MID(A2,7,8),或
=TEXT(MID(A2,7,8),年00月00日或
=CONCATENATE(MID(E2,7,4),或
=MID(E2,7,4)&
22、根据身份证号码提取性别
=IF(MOD(MID(A2,15,3),2),男女或
=IF(MOD(MID(A2 ,17,1),2),男女
23、从输入的身份证号码内让系统自动提取性别，可以输入以下公式： < br>=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,男女男
女
24、求入职年限公式：
(1)入职年限(计算到年)
=DATEDIF(参加工作年月日,现在年月日,
(2)入职年限(计算到月)
=TEXT(SUM(DATEDIF(D2,TODAY(),{年00个月或
=TEXT(SUM(DATEDIF(A1,B1,{年00个月或
=TEXT(DATEDIF(A1,B1,年;;个月;;
(3)入职年限(计算到天)
=DATEDIF(B2,C2,岁个月零天
或
=TEXT(SUM(DATEDIF(A1,TODAY(),{年00个月00天或
2

=TEXT(SUM(DATEDIF(A1,NOW(),{年00个月00天或
=DATEDIF(B2,NOW(),岁个月零
天
25、身份证提取户籍所在省份地区
函数：=VLOOKUP(LEFT(A2,6),Sheet2!A:B,2,1)
26、日期格式转换：
YYYY年MM月转YYYY- MM公式：=TEXT(A2,”YYYY-MM”)
1997.01 =SUBSTITUTE(A4,1997-01
1997.04 =LEFT(A8,4)&
1997.07 =TEXT(LEFT(A12,4)&RIGHT(A12,2),1997-07
1997.10 =TEXT(MID(A16,1,4)&MID(A16,6,2),1997-10
2014.01.01 =SUBSTITUTE(LEFT(A20,7),2014-01
2014.03.31 =LEFT(A23,4)&2014-03
2014.05.01 =TEXT(MID(A26,1,4)&MID(A26,6,2),2014-05
2014.07.01 =LEFT(SUBSTITUTE(A29,2014-07
2014.09.10 =MID(SUBSTITUTE(A32,2014-09
2014.11.22 =LEFT(REPLACE(A35,5,1,2014-11
将 “01122005” 转换成“20050112”格式
＝RIGHT(A1,4)&MID(A1,4,2)&LEFT(A1,2)或
＝YEAR($$A2)&TEXT(MONTH($$A2),
也可以用下列两方法：
1、先转换成文本, 然后再用字符处理函数。
2、[数据]-[分列] [日期]-[MDY]
将“2005年9月”转换成“200509”格式
=text(a1,
将“2005-8-6”格式转换为“20050806”格式
=TEXT(A1,
反之，将20050806转为日期2005-8-6格式，可用公式：
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
另四种公式：
＝text(a1,显示：2005-08-06
＝--TEXT(A1,，把单元格设置为日期格式 显示：2005-8-6
＝TEXT(20050806,，单元格设置日期型 显示：2005-8-6
＝VALUE(LEFT(A1,4)&显示：2005-8-6
将“20060501”转换为“2006-5-1”格式
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
将“20060501”转换为“2006-05-01”格式
=MID(A2,1,4)&
将“199306”转换为“1993-6”
公式1： =LEFT(A3,4)&
公式2：=--TEXT(A3*100+1,公式2需要设置单元格格式， 自定义：e-m
公式3：=TEXT(TEXT(A3&
将文本“2004.01.02” 转换为日期格式：2004-1-2
=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))
3

将2005-8-6转换为2005年8月6日格式
=TEXT(A1,年月日
象22怎样转换成22日？转成当年当月的日子
公式为：=date(year(now()),month(now()),22)
将“2006年5月”转换成“2006年05月”
公式为：=TEXT(A8,年月
也可以这样处理：选中单元格，设置单元格公式－数字－自定义，将yyyy“年”m“月”改
为：y yyy“年”mm“月”，即可。但这方法打印出来显示为：20065
将“1968年6月12日”转换为“1968612”格式
=YEAR(A1)&显示：1968612
=TEXT(A1,显示：19680612
将“1968年6月12日”转换为“1968-6-12”格式
=YEAR(A1)&显示：1968-6-12
=TEXT(A1,显示：1968-06-12
将1993-12-28的日期格式转换成1993年12月
=CONCATENATE(YEAR(A1),年月或
=YEAR(A1)&年月
也可以自定义格式 [$$-404]e年月
将“1978- 5-2”包含年月日的日期转换成“197805”只有年月的格式
＝year(A1)&text(month(A1),
要将“99.08.15” 格式转换成“1999.08.15”如何做
选中列，数据菜单中选分列，分列过程中“格式”选“日期YMD”，结束。
要保持200586格式
当输入200586后系统自动变成2005-8-6，要保持20 0586格式，可以使用强制文本（前
面加'号）或使用公式=TEXT(A1,
如A列是月份 数为8，B列是日期数为18，如何在C列显示“8月18日”
=A1&月日
反之，要将C列的“8月18日” 直接分别到D、E列，显示月份和日期， < br>月数份=LEFT(C5,FIND(月
日期数=MID(C5,FIND(月日月
也可 分别用公式：
=month(--c5)
=day(--c5)
27、小数四舍五入取整:
= ROUND(A2,0)
28、小数去尾取整：
=INT（A2）或
=TRUNC(A2)
29、筛选后序号连续排序：
=SUBTOTAL(103,$$E$$2:$$E3)-1
30、筛选区域合格率计算公式：
=SUMPRODUCT((SUBTOTAL(103,OFFSET(E1,ROW(I3:I11 1)-1,)))*(I3:I111>=60))SUBTOTAL(
103,I3:I111)

4

31、金额小写转大写：
=RIGHT(TEXT(A1 *100,仟0佰0拾0万0仟0佰0拾0元0角0分
或=NUMBERSTRING(INT(C10 0),2)&元角0分;;整
或=SUBSTITUTE(SUBSTITUTE(IF(-RMB(F 3),TEXT(F3,负
通用格式元;;角0分;;整零角零零
分整
=IF(C10 0=0,负

元角0分零角零分整零
分整零角零零元零
逆向查找：=INDEX (档案号!$$A$$5:$$A$$303,MATCH(B5,档案号!$$B$$5:$$B$$303,))
5