EXCEL表格函数公式大全.docx

1、EXCEL表格函数公式大全 Excel常用函数公式与技巧搜集常用的信息？提取从中提取出生年月日=TEXTMIDA1,7,6+LEN=18*2,#-00-00+0=TEXTMIDA1,7,6+LEN=18*2,#-00-00*1=IFA2,TEXTLEN=15*19&MIDA2,7,6+LEN=18*2,#-00-00+0,显示格式均为yyyy-m-d。最简单的公式,把单元格设置为日期格式=IFLEN=15,19&MID&-&MID&-&MID,MID&-&MID&-&MID 显示格式为yyyy-mm-dd。如果要求为1995/03/29格式的话,将- 换成/即可=IFD4=,IFLEN=15,

2、TEXT19&MID,0000年00月00日,IFLEN=18,TEXTMID,0000年00月00日显示格式为yyyy年mm月dd日。如果将公式中0000年00月00日改成0000-00-00,则显示格式为yyyy-mm-dd=IFLEN=18,MID,19&MID显示格式为yyyymmdd。=TEXTLEN=15*19&MIDA1,7,6+LEN=18*2,#-00-00+0=IFLEN=18,MID&-MID,19&MID&-MID=MID&年&MID&月&MID&日=IFA1,TEXTLEN=15*19&MIDA1,7,6+LEN=18*2,#-00-00从中提取出性别=IFMODMI

3、D,2,男,女最简单公式=IFMODRIGHTLEFT,2,男,女=IFA2,IFMODRIGHTLEFT,2,男,女,=IFVALUELENROUNDRIGHT/2,2=1,男,女从中进行年龄判断=IFA3,DATEDIFTEXTLEN=15*19&MIDA3,7,6+LEN=18*2,#-00-00,TODAY,Y,=DATEDIFA1,TODAY,Y以上公式会判断是否已过生日而自动增减一岁=YEARNOW-MIDE2,IFLEN=18,9,7,2-1900=YEARTODAY-IFLEN=15,19&MID,MID=YEARTODAY-VALUEMID&岁=YEARTODAY-IFMID

4、=,CONCATENATE19,MID,MID按号计算至今天年龄 =DATEDIFTEXTLEN=15*19&MIDA1,7,6+LEN=18*2,#-00-00,TODAY,y以20#10月31日为基准日,按按计算年龄的公式=DATEDIFTEXTMIDA1,7,6+LEN=18*2,#-00-00*1,2006-10-31,y按号分男女年龄段按号分男女年龄段,号在K列,年龄段在J列号为18位男性16周岁以下为1男性16周岁含16周岁以上至50周岁为 2男性50周岁含50周岁以上至60周岁为 3男性60周岁含60周岁以上为 4女性16周岁以下为1女性16周岁含16周岁以上至45周岁为 2女性

5、45周岁含45周岁以上至55周岁为 3女性55周岁含55周岁以上为 4=MATCHDATEDIFDATEMID,MID,MID,TODAY,y,0,16,50,60-0,0,5,5*ISEVENMID=SUM-DATEDIFMID&/&MID&/&MID,TODAY,y=0,16,45,55+0,0,5,5*MODMID,2年龄和工龄计算根据出生年月计算年龄DATEDIFA1,TODAY,yDATEDIFA1,TODAY,y&周岁DATEDIFA1,NOW,y根据出生年月推算生肖中国人有12生肖,属什么可以推算出来。即用诞生年份除以12,再用除不尽的余数对照如下：0猴,1鸡,2狗,3猪,4鼠,

6、5牛,6虎,7兔,8龙,9蛇,10马,11羊例如：XXX出生于1921年,即用1921年除以12,商得数为160,余数为1,对照上面得知余数1对应生肖是鸡,XXX就属鸡。=MID猴鸡狗猪鼠牛虎兔龙蛇马羊,MODYEAR,12+1,1 2007如何求出一个人到某指定日期的周岁？=DATEDIF计算距离退休年龄的公式=IFE2=,IF=V2,已经退休,距离退休还有&DATEDIFTODAY,DATEYEAR+,MONTH,DAY,Y&年&DATEDIFTODAY,DATEYEAR+,MONTH,DAY,YM&个月&DATEDIFTODAY,DATEYEAR+,MONTH,DAY,Md&天其中E2为

7、年龄可用的公式生成；V2为法定退休年龄男60,女50公式为：=IFD2=,IFD2为男或女可用的公式生成；U2为出生年月日可用的公式生成。求工齡=DATEDIFB2,TODAY,y =DATEDIFB2,TODAY,ym =DATEDIFB2,TODAY,md =DATEDIFB2,TODAY,y&年&DATEDIFB2,TODAY,ym&月&DATEDIFB2,TODAY,md&日 计算工龄=DATEDIF求两日期间的年数=DATEDIF求两日期间除去整年数剩余的月数=DATEDIF求两日期间的总月数如果只需要算出周年的话,可以用=datedif年龄与工龄计算有出生年月如何求年龄？有工作时间

8、如何求工龄？求出的结果为多少年另几个月,如：0303的形式,即3年零3个月。a1是出生年月或工作时间：=datedifa1,today,y=textdatedifa1,today,y,00&textdatedifa1,today,m,00如 B2=1964-9-1 则:=TEXTDATEDIFB2,TODAY,y,00&TEXTMODDATEDIFB2,TODAY,m,12,00 显示 4009=TEXTDATEDIFB2,TODAY,y,00年&TEXTMODDATEDIFB2,TODAY,m,12,00月显示 40年09月如果你找不到 DATEDIF 函数,也可以不用 DATEDIF 函数

9、,如 B2=1964-9-1 则:=TEXTRIGHTYEARNOW-B2,2,00&TEXTMODMONTHNOW-B2-1,12,00 显示 4009=TEXTRIGHTYEARNOW-B2,2&年&MODMONTHNOW-B2-1,12&个月, 显示 40年09个月自动算出工龄日期格式为能否用：yyyy.mm.dd这种格式来计算出工龄有多长呢？ 以前用这样一段 =TEXTRIGHTYEARNOW-A1,2&年&MODMONTHNOW-A1-1,12&个月, 。但这种方法只能用：yyyy-mm-dd这样的日期格式才能实现！你不妨把.替换成-,不就行了吗,再说后者是日期的一种标准格式,=TE

10、XTRIGHTYEARNOW-SUBSTITUTE,2&年&MODMONTHNOW-SUBSTITUTE-1,12&个月,时间和日期应用自动显示当前日期公式=YEARNOW 当前年=MONTHNOW 当前月=DAYNOW 当前日如何在单元格中自动填入当前日期Ctrl+;如何判断某日是否星期天=WEEKDAY=TEXT=MOD2某个日期是星期几比如20#2月9日,在一单元格显示星期几。=TEXT 五=TEXT 星期五=TEXT Fri=TEXT Friday什么函数可以显示当前星期如：星期二 10:41:56=TEXTNOW,aaaahh:mm:ss求本月天数设A1为2006-8-4求本月天数A

11、1=DAYDATEYEAR,MONTH+1,0也有更簡便的公式：=DAYEOMONTHNOW,0 需加載分析工具箱。当前月天数：=DATEYEARTODAY,MONTHTODAY+1,1-DATEYEARTODAY,MONTHTODAY,1用公式算出除去当月星期六、星期日以外的天数=SUMPRODUCT-MODROWINDIRECTDATEYEARNOW,MONTHNOW,1&:&DATEYEARNOW,MONTHNOW+1,0,71显示昨天的日期每天需要单元格显示昨天的日期,但双休日除外。例如,今天是7月3号的话,就显示7月2号,如果是7月9号,就显示7月6号。=IFTEXTTODAY,AA

12、A=一,TODAY-3,IFTEXTTODAY,AAA=日,TODAY-2,TODAY-1=IFTEXTTODAY,AAA=一,TODAY-3,TODAY-1关于取日期怎么设个公式使A1在年月日向后推5年,变成2011-7-15=DATEYEAR+5,MONTH,DAY=EDATE如何对日期进行上、中、下旬区分=LOOKUPDAY,0,11,21,31,上旬,中旬,下旬,下旬如何获取一个月的最大天数=DAYDATE-1或=DAY,B1为2001-03-01日期格式转换公式将 01/12/2005 转换成20050112格式RIGHT&MID&LEFTYEAR&TEXTMONTH,00&TEXT

13、DAY,00 该公式不用设置数据有效性,但要设置储存格格式。也可以用下列两方法：1、先转换成文本, 然后再用字符处理函数。2、数据-分列 日期-MDY将20#9月转换成200509格式先用公式：=text+0 然后将单元格格式为常规。将2005-8-6格式转换为20050806格式用公式：=TEXT反之,将20050806转为日期2005-8-6格式,可用公式：=DATELEFT,MID,RIGHT另四种公式：text显示：2005-08-06-TEXT,把单元格设置为日期格式 显示：2005-8-6TEXT*1,单元格设置日期型显示：2005-8-6VALUELEFT&-&MID&-&RIG

14、HT显示：2005-8-6将20060501转换为2006-05-01格式=DATELEFT,MID,RIGHT将199306转换为1993-6公式1：=LEFT&-&RIGHT*1公式2：=-TEXT公式2需要设置单元格格式,自定义：e-m公式3：=TEXTTEXT,e-m把198405转换成1984.05一、查找1984,替换1984.二、如果全部是年月的话,我个人建议, 1、采取辅助mid & . & right 2、选中这列,用数据中的分列。然后三、单元格格式数字自定义,类型下面输入：#.#将文本2004.01.02 转换为日期格式：2004-1-2=DATEMID,MID,MID将2

15、005-8-6转换为20#8月6日格式=TEXT象22怎样转换成22日？转成当年当月的日子公式为：=dateyearnow,monthnow,22将20#5月转换成20#05月公式为：=TEXT也可以这样处理：选中单元格,设置单元格公式数字自定义,将yyyy年m月改为：yyyy年mm月,即可。但这方法打印出来显示为：2006/5/将1968年6月12日转换为1968/6/12格式=YEAR&/&MONTH&/&DAY显示：1968/6/12=TEXT显示：1968/06/12将1968年6月12日转换为1968-6-12格式=YEAR&-&MONTH&-&DAY显示：1968-6-12=TEX

16、T 显示：1968-06-12将1993-12-28的日期格式转换成1993年12月=CONCATENATEYEAR,年,MONTH,月 =YEAR&年&MONTH&月也可以自定义格式 $-404e年m月将1978-5-2包含年月日的日期转换成197805只有年月的格式year&textmonth,00要将99.08.15 格式转换成1999.08.15如何做选中列,数据菜单中选分列,分列过程中格式选日期YMD,结束。要保持2005/8/6格式当输入2005/8/6后系统自动变成2005-8-6,要保持2005/8/6格式,可以使用强制文本前面加号或使用公式=TEXT。也可以用另一种公式：=I

17、FISERRORTEXT,TEXT,TEXT将二三年十二月二十五日转为2003-12-25格式,1、可以用数组公式将中文日期转化为日期系列数=14610+MATCHSUBSTITUTE,TEXTROW,DBNum1yyyy年m月d日,0该公式速度较慢。2、改进后的公式,速度要快的多：=DATE1899+MATCHLEFT,TEXTROW,DBNum10000,0,MONTHMATCHSUBSTITUTEMID,元,一,TEXTROW,DBNum1m月d日,0,DAYMATCHSUBSTITUTEMID,元,一,TEXTROW,DBNum1m月d日,0要设置为1900年的日期格式。日期格式转换如

18、A列是月份数为8,B列是日期数为18,如何在C列显示8月18日=A1&月&B1&日反之,要将C列的8月18日直接分别到D、E列,显示月份和日期,月数份=LEFTC5,FIND-1日期数=MIDC5,FIND+1,FIND-FIND-1也可分别用公式：=month=day日期格式转换问题输入的日期是:04-07-26.与另一格的001合并,合并出来是:040726001.=TEXT&001要想自动取得编制日期：XXXX年X月X日可在该单元格输入 =编制日期：&TEXTTODAY,yyyy年m月d日排名与排序筛选一个具有11项汇总方式的函数SUBTOTAL=SUBTOTAL在数据筛选求和上有意想不

19、到的功能,11项功能为：1、求平均数,2、求计数,3、求计数值自动筛选序列4、求最大值,5、求最小值,6、求乘积,7、求总体标准偏差,8、求标准偏差、9、求和,10、求方差,11、求总体方差。自动排序=SUBTOTAL*1=IFA2A1,1,N+1按奇偶数排序我想请教怎样按奇数顺序然后再按偶数顺序排序=IFMOD,0,1=IFROW50,ROW*2-100,ROW*2-1=ROW*2-1-ROW50*99自动生成序号比如在第二列中输入容回车后第一列的下一行自动生成序列号。=IFB2,A2+1,如何自动标示A栏中的数字大小排序？=RANK=RANK如何设置自动排序A列自动变成从小到大排列B=SM

20、ALLA$2:A$28,ROWA列自动变成从大到小排列B=LARGEA$2:A$28,ROW重复数据得到唯一的排位序列想得到数据的出现总数吗1,2,2,3,4,4,5 数据的出现总数为5？解答：不需要插列,不需要很多的函数就行了. =RANK+COUNTIF-1按字符数量排序制作歌曲清单时,习惯按字符数量来排列分类,但是EXCEL并不能直接按字数排序。需要先计算出每首歌曲的字数,然后再进行排序。如A、B列分别为歌手和歌名,在C1输入字数,在C2输入公式：LEN 下拖,单击C2,单击工具栏上的升序排列即可,删除C列。排序字母与数字的混合容日常使用中,表格经常会有包含字母和数字混合的数据,对此类数

21、据排序时,通常是先比较字母的大小,再比较数字的大小,但EXCEL是按照对字符进行逐位比较来排序的,如下表：A7排在第5位,而不是第1位。排序结果无法令人满意。A1A1222A293A3174A435A76B207B38C1449C510C33AB1A7A0072A29A0293A43A0434A122A1225A317A3176B3B0037B20B0208C5C0059C33C03310C144C144如果希望EXCEL改变排序的规则,需要将数据做一些改变。在B1中输入公式：LEFT& RIGHT000& RIGHTA1,LEN-1,3 下拖单击B2,单击工具栏上的升序排列即可。随机排序如A

22、、B列分别为歌手和歌名,在C1输入次序,在C2输入公式：RAND,下拖,单击C2,单击工具栏上的降序排列即可对歌曲清单进行随机排序。排序的问题我想要这样的排序: 2001-20032004-20062007-2009 2010-2012;其实不是数据排序,应该是数据填充。输入公式=LEFT+3&-&RIGHT+3 即可。怎样才能让数列自动加数怎样做才能让数列自动加数A A0001B B0001A A0002C C0001A A0003B B0002C C0002公式为=A1&000&COUNTIF向下拖=TEXTCOUNTIF,!&A1&0000否则数字超过9就错误了。一个排序问题一个电子表格,格式