常用的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(1/COUNTIF(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、求某一区域内不重复的数据个数mid函数提取年月日
例如求A1:A100范围内不重复数据的个数，某个数重复多次出现只算一个。有两种计算方法：
一是利用数组公式：
=SUM(1/COUNTIF(A1:A100,A1:A100))
输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号"{}"。
二是利用乘积求和函数：
=SUMPRODUCT(1/COUNTIF(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, ">=80")
10 优秀率：=COUNTIF(C2:C56, ">=80") /COUNT(C2:C56)
11、及格人数：=COUNTIF(C2:C56, ">=60")
12、及格率： =COUNTIF(C2:C56, ">=60”)/COUNT(C2:C56)
13、成绩排名
=RANK.EQ(A2,A$2:A$7)或=RANK(K2，K$2:K$56)
14、中国式排名（相同成绩不占用名次）
=SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7))+1
15、90分以上的人数
=COUNTIF(B1:B7,">90")
16、各分数段的人数（不及格、及格、优秀）
同时选中E2:E4，输入以下公式，按Shift+Ctrl+Enter
=FREQUENCY(B2:B56,{59.5;79.5;100})
17、按条件统计平均值
=AVERAGEIF(B2:B7,"男",C2:C7)
18、多条件统计平均值
=AVERAGEIFS(D2:D7,C2:C7,"男",B2:B7,"销售")
19、分数段人数统计：
（1） =COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数；假设把结果存放于K57单元格；
（2） =COUNTIF(K2:K56,">=95")－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格；
（3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格；
（4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59) ——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格；
（5）=COUNTIF(K2:K56,">=70")－SUM(K57:K60) ——求K2到K56区域70～84.5分的人数；假设把结果存放于K61单元格；
（6）=COUNTIF(K2:K56,">=60")－SUM(K57:K61) ——求K2到K56区域60～69.5分的人数；假设把结果存放于K62单元格；
（7） =COUNTIF(K2:K56,"<60") ——求K2到K56区域60分以下的人数；假设把结果存放于K63单元格；
（8）COUNTIF函数也可计算某一区域男、女生人数。
如：=COUNTIF(C2:C351,"男") ——求C2到C351区域（共350人）男性人数；
（9）优秀率： =SUM(K57:K60)/55*100
（10）及格率： =SUM(K57:K62)/55*100
20、根据出生年月计算年龄
=DATEDIF(A2,TODAY(),"y")或
=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)
21、根据身份证号码提取出生年月日
=--TEXT(MID(A2,7,8),"0-00-00")或
=TEXT(MID(A2,7,8),"0年00月00日")或
=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))或
=MID(E2,7,4)&"-"&MID(E2,11,2)&"-"&MID(E2,13,2)
22、根据身份证号码提取性别
=IF(MOD(MID(A2,15,3),2),"男","女")或
=IF(MOD(MID(A2,17,1),2),"男","女")
23、从输入的身份证号码内让系统自动提取性别，可以输入以下公式：
=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))
24、求入职年限公式：
(1)入职年限(计算到年)
=DATEDIF(参加工作年月日,现在年月日,"y")
(2)入职年限(计算到月)
=TEXT(SUM(DATEDIF(D2,TODAY(),{"Y","YM"})*10^{2,0}),"0年00个月")或
=TEXT(SUM(DATEDIF(A1,B1,{"Y","YM"})*{100,1}),"0年00个月")或
=TEXT(DATEDIF(A1,B1,"Y"),"0年;;")&TEXT(DATEDIF(A1,B1,"YM"),"0个月;;")
(3)入职年限(计算到天)
=DATEDIF(B2,C2,"Y")&"岁"&DATEDIF(B2,C2,"YM")&"个月零"&DATEDIF(B2,C2,"MD")&"天"
或
=TEXT(SUM(DATEDIF(A1,TODAY(),{"Y","YM","MD"})*10^{4,2,0}),"0年00个月00天")或
=TEXT(SUM(DATEDIF(A1,NOW(),{"y","ym","md"})*{10000,100,1}),"0年00个月00天")或
=DATEDIF(B2,NOW(),"Y")&"岁"&DATEDIF(B2,NOW(),"YM")&"个月零"&DATEDIF(B2,NOW(),"MD")&"天"
25、身份证提取户籍所在省份地区
函数：=VLOOKUP(LEFT(A2,6),Sheet2!A:B,2,1)
26、日期格式转换：
YYYY年MM月转YYYY-MM公式：=TEXT(A2,”YYYY-MM”)
1997.01    =SUBSTITUTE(A4,".","-",1)    1997-01
1997.04    =LEFT(A8,4)&"-"&RIGHT(A8,2)    1997-04
1997.07    =TEXT(LEFT(A12,4)&RIGHT(A12,2),"0000-00")    1997-07
1997.10    =TEXT(MID(A16,1,4)&MID(A16,6,2),"0000-00")    1997-10
2014.01.01    =SUBSTITUTE(LEFT(A20,7),".","-",1)    2014-01
2014.03.31    =LEFT(A23,4)&"-"&MID(A23,6,2)    2014-03
2014.05.01    =TEXT(MID(A26,1,4)&MID(A26,6,2),"0000-00")    2014-05
2014.07.01    =LEFT(SUBSTITUTE(A29,".","-"),7)    2014-07
2014.09.10    =MID(SUBSTITUTE(A32,".","-"),1,7)    2014-09
2014.11.22    =LEFT(REPLACE(A35,5,1,"-"),7)    2014-11
将 “01/12/2005” 转换成“20050112”格式
＝RIGHT(A1,4)&MID(A1,4,2)&LEFT(A1,2)或
＝YEAR($A2)&TEXT(MONTH($A2),"00")&TEXT(DAY($A2),"00")
也可以用下列两方法：
1、先转换成文本,  然后再用字符处理函数。
2、[数据]-[分列]    [日期]-[MDY]
将“2005年9月”转换成“200509”格式
=text(a1,"yyyymm")
将“2005-8-6”格式转换为“20050806”格式
=TEXT(A1,"YYYYMMDD")
反之，将20050806转为日期2005-8-6格式，可用公式：
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
另四种公式：
＝text(a1,"0000-00-00")                                    显示：2005-08-06
＝--TEXT(A1,"#-00-00")，把单元格设置为日期格式            显示：2005-8-6
＝TEXT(20050806,"0000-00-00")*1，单元格设置日期型          显示：2005-8-6
＝VALUE(LEFT(A1,4)&"-"&MID(A1,5,2)&"-"&RIGHT(A1,2))  显示：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)&"-"&MID(A2,5,2)& "-"&MID(A2,7,2)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。