没有合适的资源?快使用搜索试试~ 我知道了~
EXCEL表格函数公式大全.pdf
1.该资源内容由用户上传,如若侵权请联系客服进行举报
2.虚拟产品一经售出概不退款(资源遇到问题,请及时私信上传者)
2.虚拟产品一经售出概不退款(资源遇到问题,请及时私信上传者)
版权申诉
0 下载量 34 浏览量
2023-05-16
13:28:02
上传
评论
收藏 3.03MB PDF 举报
温馨提示
试读
83页
EXCEL表格函数公式大全.pdf
资源推荐
资源详情
资源评论
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">,TOD
AY<>,"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">
以 20##10 月 31 日为基准日,按按计算年龄<周岁>的公式
=DATEDIF<TEXT<MID<A1,7,6+<LEN<A1>=18>*2>,"#-00-00">*1,"2006-10-31","y">
1 / 83
按号分男女年龄段
按号分男女年龄段,号在 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,5
0,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 / 83
=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 / 83
=YEAR<NOW<>> 当前年
=MONTH<NOW<>> 当前月
=DAY<<NOW<>>> 当前日
如何在单元格中自动填入当前日期
Ctrl+;
如何判断某日是否星期天
=WEEKDAY<A2,2>
=TEXT<A1,"aaaa">
=MOD<A1,7><2
某个日期是星期几
比如 20##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>
如何对日期进行上、中、下旬区分
4 / 83
=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]
将"20##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>>
5 / 83
剩余82页未读,继续阅读
资源评论
hhappy0123456789
- 粉丝: 59
- 资源: 5万+
下载权益
C知道特权
VIP文章
课程特权
开通VIP
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
最新资源
- pta题库答案c语言之排序4统计工龄.zip
- pta题库答案c语言之树结构7堆中的路径.zip
- pta题库答案c语言之树结构3TreeTraversalsAgain.zip
- pta题库答案c语言之树结构2ListLeaves.zip
- pta题库答案c语言之树结构1树的同构.zip
- 基于C++实现民航飞行与地图简易管理系统可执行程序+说明+详细注释.zip
- pta题库答案c语言之复杂度1最大子列和问题.zip
- 三维装箱问题(Three-Dimensional Bin Packing Problem,3D-BPP)是一个经典的组合优化问题
- 以下是一些关于Linux线程同步的基本概念和方法.txt
- 以下是一个简化的示例,它使用pygame库来模拟烟花动画的框架.txt
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功