EXCEL 办公必备函数公式集.docx
《EXCEL 办公必备函数公式集.docx》由会员分享，可在线阅读，更多相关《EXCEL 办公必备函数公式集.docx（111页珍藏版）》请在冰豆网上搜索。
EXCEL办公必备函数公式集
excel常用函数公式及技巧搜集
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【年龄和工龄计算】
根据出生年月计算年龄
＝DATEDIF（A1,TODAY（）,"y"）
＝DATEDIF（A1,TODAY（）,"y"）&"周岁"
＝DATEDIF（A1,NOW（）,"y"）
根据出生年月推算生肖
中国人有12生肖，属什么可以推算出来。
即用诞生年份除以12，再用除不尽的余数对照如下：
0→猴，1→鸡，2→狗，3→猪，4→鼠，5→牛，6→虎，7→兔，8→龙，9→蛇，10→马，11→羊例如：
XXX出生于1921年，即用1921年除以12，商得数为160，余数为1，对照上面得知余数1对应生肖是鸡，XXX就属鸡。
=MID（"猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD（YEAR（A2）,12）+1,1）（2007）
如何求出一个人到某指定日期的周岁？
=DATEDIF（起始日期,结束日期,"Y"）
计算距离退休年龄的公式
=IF（E2="","",IF（E2>=V2,"已经退休","距离退休还有"&DATEDIF（TODAY（）,DATE（YEAR（U2）+（V2）,MONTH（U2）,DAY（U2））,"Y"）&"年"&DATEDIF（TODAY（）,DATE（YEAR（U2）+（V2）,MONTH（U2）,DAY（U2））,"YM"）&"个月"&DATEDIF（TODAY（）,DATE（YEAR（U2）+（V2）,MONTH（U2）,DAY（U2））,"Md"）&"天"））
其中E2为年龄（可用身份证号码的公式生成）；
V2为法定退休年龄（男60，女50）公式为：
=IF（D2="","",IF（D2="男",60,50））
D2为男或女（可用身份证号码的公式生成）；U2为出生年月日（可用身份证号码的公式生成）。
求工齡
=DATEDIF（B2,TODAY（）,"y"）
=DATEDIF（B2,TODAY（）,"ym"）
=DATEDIF（B2,TODAY（）,"md"）
=DATEDIF（B2,TODAY（）,"y"）&"年"&DATEDIF（B2,TODAY（）,"ym"）&"月"&DATEDIF（B2,TODAY（）,"md"）&"日"
计算工龄
=DATEDIF（C6,C8,"y"）求两日期间的年数
=DATEDIF（C6,C8,"ym"）求两日期间除去整年数剩余的月数
=DATEDIF（C6,C8,"m"）求两日期间的总月数
如果只需要算出周年的话，可以用=datedif（"1978-8","2006-5","Y"）
年龄及工龄计算
有出生年月如何求年龄？
有工作时间如何求工龄？
（求出的结果为多少年另几个月，如：
0303的形式，即3年零3个月）。
a1是出生年月或工作时间：
=datedif（a1,today（）,"y"）
=text（datedif（a1,today（）,"y"）,"00"）&text（datedif（a1,today（）,"m"）,"00"）
如[B2]=1964-9-1则:
=TEXT（DATEDIF（B2,TODAY（）,"y"）,"00"）&TEXT（MOD（DATEDIF（B2,TODAY（）,"m"）,12）,"00"） '显示4009
=TEXT（DATEDIF（B2,TODAY（）,"y"）,"00年"）&TEXT（MOD（DATEDIF（B2,TODAY（）,"m"）,12）,"00月"） '显示40年09月
如果你找不到DATEDIF函数，也可以不用DATEDIF函数,
如[B2]=1964-9-1则:
=TEXT（RIGHT（YEAR（NOW（）-B2）,2）,"00"）&TEXT（MOD（MONTH（NOW（）-B2）-1,12）,"00"） '显示4009
=TEXT（RIGHT（YEAR（NOW（）-B2）,2）&"年"&MOD（MONTH（NOW（）-B2）-1,12）&"个月",""） '显示40年09个月
自动算出工龄日期格式为（yyyy.mm.dd）
能否用：
（yyyy.mm.dd）这种格式来计算出工龄有多长呢~？
以前用这样一段（=TEXT（RIGHT（YEAR（NOW（）-A1）,2）&"年"&MOD（MONTH（NOW（）-A1）-1,12）&"个月",""））。
但这种方法只能用：
（yyyy-mm-dd）这样的日期格式才能实现！
你不妨把“.”替换成“-”，不就行了吗，再说后者是日期的一种标准格式，
=TEXT（RIGHT（YEAR（NOW（）-SUBSTITUTE（A1,".","-"））,2）&"年"&MOD（MONTH（NOW（）-SUBSTITUTE（A1,".","-"））-1,12）&"个月",""）
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【时间和日期应用】
自动显示当前日期公式
=YEAR（NOW（））当前年
=MONTH（NOW（））当前月
=DAY（（NOW（）））当前日
如何在单元格中自动填入当前日期
Ctrl+;
如何判断某日是否星期天
=WEEKDAY（A2,2）
=TEXT（A1,"aaaa"）
=MOD（A1,7）<2
某个日期是星期几
比如2007年2月9日，在一单元格内显示星期几。
=TEXT（A1,"aaa"）（五）
=TEXT（A1,"aaaa"）（星期五）
=TEXT（A1,"ddd"）（Fri）
=TEXT（A1,"dddd"）（Friday）
什么函数可以显示当前星期
如：
星期二10:
41:
56
=TEXT（NOW（）,"aaaa hh:
mm:
ss"）
求本月天数
设A1为2006-8-4 求本月天数
A1=DAY（DATE（YEAR（A1）,MONTH（A1）+1,0））
也有更簡便的公式：
=DAY（EOMONTH（NOW（）,0））需加載分析工具箱。
当前月天数：
=DATE（YEAR（TODAY（））,MONTH（TODAY（））+1,1）-DATE（YEAR（TODAY（））,MONTH（TODAY（））,1）
用公式算出除去当月星期六、星期日以外的天数
=SUMPRODUCT（--（MOD（ROW（INDIRECT（DATE（YEAR（NOW（））,MONTH（NOW（））,1）&":
"&DATE（YEAR（NOW（））,MONTH（NOW（））+1,0）））,7）>1））
显示昨天的日期
每天需要单元格内显示昨天的日期，但双休日除外。
例如，今天是7月3号的话，就显示7月2号，如果是7月9号，就显示7月6号。
=IF（TEXT（TODAY（）,"AAA"）="一",TODAY（）-3,IF（TEXT（TODAY（）,"AAA"）="日",TODAY（）-2,TODAY（）-1））
=IF（TEXT（TODAY（）,"AAA"）="一",TODAY（）-3,TODAY（）-1）
关于取日期
怎么设个公式使A1在年月日向后推5年,变成2011-7-15
=DATE（YEAR（A1）+5,MONTH（A1）,DAY（A1））
=EDATE（A1,12*5）
如何对日期进行上、中、下旬区分
=LOOKUP（DAY（A1）,{0,11,21,31},{"上旬","中旬","下旬","下旬"}）
如何获取一个月的最大天数
"=DAY（DATE（2002,3,1）-1）"或"=DAY（B1-1）",B1为"2001-03-01
日期格式转换公式
将“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"）+0然后将单元格格式为常规。
将“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-05-01”格式
=DATE（LEFT（A2,4）,MID（A2,5,2）,RIGHT（A2,2））
将“199306”转换为“1993-6”
公式1：
=LEFT（A3,4）&"-"&RIGHT（A3,2）*1
公式2：
=--TEXT（A3*100+1,"#-00-00"）公式2需要设置单元格格式，自定义：
e-m
公式3：
=TEXT（TEXT（A3&"01","0000-00-00"）,"e-m"）
把198405转换成1984.05
一、查找—1984，替换—1984.
二、如果全部是年月的话，我个人建议，
1、采取辅助＝mid（xxxxxx,1,4）&"."&right（xxxxxx,2）
2、选中这列，用数据中的分列。
然后……………
三、单元格格式／数字／自定义，类型下面输入：
####"."##
将文本“2004.01.02”转换为日期格式：
2004-1-2
=DATE（MID（A1,1,4）,MID（A1,6,2）,MID（A1,9,2））
将2005-8-6转换为2005年8月6日格式
=TEXT（A1,"yyyy""年""m""月""d""日"";@"）
象22怎样转换成22日？
转成当年当月的日子
公式为：
=date（year（now（））,month（now（））,22）
将“2006年5月”转换成“2006年05月”
公式为：
=TEXT（A8,"yyyy""年""mm""月"";@"）
也可以这样处理：
选中单元格，设置单元格公式－数字－自定义，将yyyy“年”m“月”改为：
yyyy“年”mm“月”，即可。
但这方法打印出来显示为：
2006/5/
将“1968年6月12日”转换为“1968/6/12”格式
=YEAR（A1）&"/"&MONTH（A1）&"/"&DAY（A1）显示：
1968/6/12
=TEXT（A1,"yyyy/mm/dd"）显示：
1968/06/12
将“1968年6月12日”转换为“1968-6-12”格式
=YEAR（A1）&"-"&MONTH（A1）&"-"&DAY（A1）显示：
1968-6-12
=TEXT（A1,"yyyy-mm-dd"）显示：
1968-06-12
将1993-12-28的日期格式转换成1993年12月
=CONCATENATE（YEAR（A1）,"年",MONTH（A1）,"月"）
=YEAR（A1）&"年"&MONTH（A1）&"月"
也可以自定义格式[$-404]e"年"m"月"
将“1978-5-2”包含年月日的日期转换成“197805”只有年月的格式
＝year（A1）&text（month（A1）,"00"）
要将“99.08.15”格式转换成“1999.08.15”如何做
选中列，数据菜单中选分列，分列过程中“格式”选“日期YMD”，结束。
要保持2005/8/6格式
当输入2005/8/6后系统自动变成2005-8-6，要保持2005/8/6格式，可以使用强制文本（前面加'号）或使用公式=TEXT（A1,"YYYY/MM/DD"）。
也可以用另一种公式：
=IF（ISERROR（TEXT（A1,"yyyy/mm/dd"））,TEXT（A1,"0000!
/00!
/00"）,TEXT（A1,"yyyy/mm/dd"））
将“二○○三年十二月二十五日”转为“2003-12-25”格式，
1、可以用数组公式将中文日期转化为日期系列数｛=14610+MATCH（SUBSTITUTE（A3,"元","一"）,TEXT（ROW（$14611:
$55153）,"[DBNum1]yyyy年m月d日"）,0）｝
该公式速度较慢。
2、改进后的公式，速度要快的多：
｛=DATE（1899+MATCH（LEFT（A7,4）,TEXT（ROW（$1900:
$2100）,"[DBNum1]0000"）,0）,MONTH（MATCH（SUBSTITUTE（MID（A7,6,7）,"元","一"）,TEXT（ROW（$1:
$366）,"[DBNum1]m月d日"）,0））,DAY（MATCH（SUBSTITUTE（MID（A7,6,7）,"元","一"）,TEXT（ROW（$1:
$366）,"[DBNum1]m月d日"）,0）））｝
要设置为1900年的日期格式。
日期格式转换
如A列是月份数为8，B列是日期数为18，如何在C列显示“8月18日”
=A1&"月"&B1&"日"
反之，要将C列的“8月18日”直接分别到D、E列，显示月份和日期，
月数份=LEFT（C5,FIND（"月",C5）-1）
日期数=MID（C5,FIND（"月",C5）+1,FIND（"日",C5）-FIND（"月",C5）-1）
也可分别用公式：
=month（--c5）
=day（--c5）
日期格式转换问题
输入的日期是:
04-07-26.与另一格的"001"合并,合并出来是:
040726001.
=TEXT（A1,"YYMMDD"）&"001"
要想自动取得“编制日期：
XXXX年X月X日”
可在该单元格输入="编制日期：
"&TEXT（TODAY（）,"yyyy年m月d日"）
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【排名及排序筛选】
一个具有11项汇总方式的函数SUBTOTAL
=SUBTOTAL（9,$B$2:
B2）
在数据筛选求和上有意想不到的功能，11项功能为：
1、求平均数，2、求计数，3、求计数值（自动筛选序列）4、求最大值，5、求最小值，6、求乘积，7、求总体标准偏差，8、求标准偏差、9、求和，10、求方差，11、求总体方差。
自动排序
=SUBTOTAL（3,$B$2:
B2）*1
=IF（A2<>A1,1,N（C1）+1）
按奇偶数排序
我想请教怎样按奇数顺序然后再按偶数顺序排序
=IF（MOD（A1,2）,0,1）
=IF（ROW（）>50,（ROW（）*2）-100,（ROW（）*2）-1）
=ROW（）*2-1-（ROW（）>50）*99
自动生成序号
比如在第二列中输入内容回车后第一列的下一行自动生成序列号。
=IF（B2<>"",A2+1,""）
如何自动标示A栏中的数字大小排序？
=RANK（A1,$A$1:
$A$5）
=RANK（A1,A:
A）
如何设置自动排序
A列自动变成从小到大排列
B=SMALL（A$2:
A$28,ROW（1:
1））
A列自动变成从大到小排列
B=LARGE（A$2:
A$28,ROW（1:
1））
重复数据得到唯一的排位序列
想得到数据的出现总数吗（{1,2,2,3,4,4,5}数据的出现总数为5）？
解答：
不需要插列,不需要很多的函数就行了.=RANK（B3,B$3:
B$12）+COUNTIF（B$3:
B3,B3）-1
按字符数量排序
制作歌曲清单时，习惯按字符数量来排列分类，但是EXCEL并不能直接按字数排序。
需要先计算出每首歌曲的字数，然后再进行排序。
如A、B列分别为“歌手”和“歌名”，在C1输入“字数”，在C2输入公式：
＝LEN（B2）下拖，单击C2，单击工具栏上的“升序排列”即可，删除C列。
排序字母与数字的混合内容
日常使用中，表格经常会有包含字母和数字混合的数据，对此类数据排序时，通常是先比较字母的大小，再比较数字的大小，但EXCEL是按照对字符进行逐位比较来排序的，如下表：
A7排在第5位，而不是第1位。
排序结果无法令人满意。
A
1
A122
2
A29
3
A317
4
A43
5
A7
6
B20
7
B3
8
C144
9
C5
10
C33
A
B
1
A7
A007
2
A29
A029
3
A43
A043
4
A122
A122
5
A317
A317
6
B3
B003
7
B20
B020
8
C5
C005
9
C33
C033
10
C144
C144
如果希望EXCEL改变排序的规则，需要将数据做一些改变。
在B1中输入公式：
LEFT（A1,1）&RIGHT（"000"&RIGHT（A1,LEN（A1）-1）,3）下拖
单击B2，单击工具栏上的“升序排列”即可。
随机排序
如A、B列分别为“歌手”和“歌名”，在C1输入“次序”，在C2输入公式：
＝RAND（），下拖，单击C2，单击工具栏上的“降序排列”即可对歌曲清单进行随机排序。
排序的问题
我想要这样的排序:
2001-2003
2004-2006
2007-2009
2010-2012;
其实不是数据排序，应该是数据填充。
输入公式=LEFT（E3,4）+3&"-"&RIGHT（E3,4）+3即可。
怎样才能让数列自动加数
怎样做才能让数列自动加数
A A0001
B B0001
A A0002
C C0001
A A0003
B B0002
C C0002
公式为=A1&"000"&COUNTIF（A$1:
A1,A1）向下拖
=TEXT（COUNTIF（A$1:
A1,A1）,"!
"&A1&"0000"）否则数字超过9就错误了。
一个排序问题
一个电子表格，格式是101、102...999,10101、10102...99901,1010101,1020201...9990101,请问如何将它排列成101,10101,1010101,102,10201,1020101,...999,99901,9990101的形式。
我在数字前加了个字母,比如"d"&"数字",然后用排序就可以把它们按你的需求排列了.最后再把字母"d"去掉。
数字的自动排序,插入后不变?
1赵一总经理
2赵二副经理
3赵三副经理
4赵四技术员
5赵五
6赵六员工
如上的一个表,如何实现当我把赵六这一整行（第6行）插入到上面的表中时,A列的序列号不变?
最后的效果如下:
1赵一总经理
2赵二副经理
3赵六员工
4赵三副经理
5赵四技术员
6赵五
A1单元格输入公式=row（）,往下拉,然后再插入。
=SUBTOTAL（3,$B$2:
$B2）
在A1中输入公式：
“=if（b1="","",counta（$b$1:
b1）”后下拉复制至A列各行即可（“”不必输入）
根据规律的重复的姓名列产生自动序号
姓名序号
张三1
张三1
李四2
李四2
赵五3
赵五3
赵五3
王六4
王六4
=（A1<>A2）+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））
A列是成绩,B列是排名
=SUMPRODUCT（（A$1:
A$9>A1）/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）*（ZF>E2））+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$12>A2）/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）））
依分数比高低名次成绩排
升级会员 