EXCEL函数公式大全

来源:网络收集 打印文章内容   下载WORD版到本地  

excel 常用函数公式及技巧搜集(常用的) 【信息?提取】 从号码中提取出生年月日 =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(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,) 显示格式均为 yyyy-m-d。

(最简单的公式,把单元格设置为日期格式) =IF(LEN(A2)=15,"19"&MID(A2,7,2)&"-"&MID(A2,9,2)&"-"&MID(A2,11,2),MID(A2,7,4)& "-"&MID(A2,11,2)&"-"&MID(A2,13,2)) 显示格式为 yyyy-mm-dd。

(如果要求为“1995/03/29”格式的话,将”-” 换成”/”即可) =IF(D4="","",IF(LEN(D4)=15,TEXT(("19"&MID(D4,7,6)),"0000 年 00 月 00 日 "),IF(LEN(D4)=18,TEXT(MID(D4,7,8),"0000 年 00 月 00 日")))) 显示格式为 yyyy 年 mm 月 dd 日。

(如果将公式中“0000 年 00 月 00 日”改成“0000-00-00”,则显示格式 为 yyyy-mm-dd) =IF(LEN(A1:A2)=18,MID(A1:A2,7,8),"19"&MID(A1:A2,7,6)) 显示格式为 yyyymmdd。

=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0 =IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2)) =MID(A1,7,4)&"年"&MID(A1,11,2)&"月"&MID(A1,13,2)&"日" =IF(A1<>"",TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")) 从号码中提取出性别 =IF(MOD(MID(A1,15,3),2),"男","女") (最简单公式) =IF(MOD(RIGHT(LEFT(A1,17)),2),"男","女") =IF(A2<>” ”,IF(MOD(RIGHT(LEFT(A2,17)),2),”男”,”女”),) =IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,"男","女") 从号码中进行年龄判断 =IF(A3<>””,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),”#-00-00”),T ODAY(),”Y”),) =DATEDIF(A1,TODAY(),“Y”) (以上公式会判断是否已过生日而自动增减一岁) =YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900 =YEAR(TODAY())-IF(LEN(A1)=15,"19"&MID(A1,7,2),MID(A1,7,4)) =YEAR(TODAY())-VALUE(MID(B1,7,4))&"岁" =YEAR(TODAY())-IF(MID(B1,18,1)="",CONCATENATE("19",MID(B1,7,2)),MID(B1,7,4)) 按号号码计算至今天年龄 =DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(),"y") 以 2006 年 10 月 31 日为基准日,按按计算年龄(周岁)的公式 1

=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=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 女性 45 周岁(含 45 周岁)以上至 55 周岁为 3 女性 55 周岁(含 55 周岁)以上为 4 =MATCH(DATEDIF(DATE(MID(K1,7,4),MID(K1,11,2),MID(K1,13,2)),TODAY(),"y"),{0,16 ,50,60}-{0,0,5,5}*ISEVEN(MID(K1,17,1))) =SUM(--(DATEDIF(MID(K1,7,4)&"/"&MID(K1,11,2)&"/"&MID(K1,13,2),TODAY(),"y")> ={0,16,45,55}+{0,0,5,5}*MOD(MID(K1,17,1),2))) ㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜ 【年龄和工龄计算】 根据出生年月计算年龄 =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 为出生年月日(可用号码的公式生成) 。

2

求工齡 =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)&"个月","") ㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜ 3

【时间和日期应用】 自动显示当前日期公式 =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(TOD AY()),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) 4

关于取日期 怎么设个公式使 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 5

一、查找—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) =TEXT(A1,"yyyy/mm/dd") 显示:1968/6/12 显示:1968/06/12 将“1968 年 6 月 12 日”转换为“1968-6-12”格式 =YEAR(A1)&"-"&MONTH(A1)&"-"&DAY(A1) =TEXT(A1,"yyyy-mm-dd") 显示:1968-6-12 显示: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/d d")) 6

  • EXCEL 函数公式大全

    EXCEL 函数公式大全

    EXCEL 函数公式大全...

    贡献者:网络收集
    593666
  • EXCEL函数-公式大全

    EXCEL函数-公式大全

    EXCEL函数-公式大全...

    贡献者:网络收集
    319604
  • EXCEL公式函数大全

    EXCEL公式函数大全

    EXCEL公式函数大全...

    贡献者:网络收集
    770375
  • 常用excel函数公式大全

    常用excel函数公式大全

    常用excel函数公式大全...

    贡献者:网络收集
    941963
  • excel常用公式函数大全

    excel常用公式函数大全

    excel常用公式函数大全...

    贡献者:网络收集
    427463
  • Excel常用函数公式大全

    Excel常用函数公式大全

    Excel常用函数公式大全...

    贡献者:网络收集
    794139
  • 最常用Excel函数公式大全

    最常用Excel函数公式大全

    最常用Excel函数公式大全...

    贡献者:网络收集
    319487
  • 最常用的excel函数公式大全

    最常用的excel函数公式大全

    最常用的excel函数公式大全...

    贡献者:网络收集
    694221
  • 常用的excel函数公式大全

    常用的excel函数公式大全

    常用的excel函数公式大全...

    贡献者:网络收集
    316385
  • 最经典实践操作的EXCEL公式函数应用大全

    最经典实践操作的EXCEL公式函数应用大全

    最经典实践操作的EXCEL公式函数应用大全...

    贡献者:网络收集
    821739
  • 网友在搜
    宝马gt528i报价 v6000 f2340 ps4 littlstar 打不开 native app开发工具 雷伊盖亚浴室和布莱克 黑暗之魂3人之脓是什么 redis cli 查询key girls exo百度云 臂力器扳不动 mycafe封号 underdog动漫 excel 判断是否是数字 cloudfoam和phylon 昂科威行车记录仪 15j0121图集 ubuntu查看jdk版本号 original choice 姚明名人堂演讲全文 php限制ip访问次数 日常聊天英语 三国无惨甄姬小说 纽约摩天大楼规划 cocos2dx e0020 黑体辐射线 emc全闪存 uc国际版11精简汉化 zstack特点 纸尿片 行业英文 appoint sb to 喷漆流水线 xnnx videos home classical river town 谷米文化 哈尔滨的简称是什么 水性uv油墨 叮咚电玩世家 游戏 janmarini洗面奶 corporate perks 吕新华委员 sprint在中国能用吗 武汉东湖梅园地铁站 getfoldersize shadowsock游戏模式 土耳其小众包名字 硬盘京东和天猫 vm内部错误 形态特征的意思 ubuntu grub修复 龙之谷小鱼 海贼王72 m.iqiyi.com 强心脏郑秀晶完整版 yoosee软件下载 广大统一身份认证 meiluocheng 迈腾报价2016 蝎子乐队专辑 its time to get up come to my assistance

    声明:本站内容源于网络,出于传递更多信息之目的,并不意味着赞同其观点或证实其描述。文章内容仅供参考,请咨询相关专业人士。

    如果无意之中侵犯了您的版权,或有意见、反馈或投诉等情况, 联系我们:shnews500#tom.com

    All Rights Reserved 生活吧 手机站 网站地图 网站栏目