注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

拥有明天的博客

__没有约定,但有默契

 
 
 

日志

 
 

【转载】怎样应用Excel函数  

2017-06-27 15:10:26|  分类: 电脑知识 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
本文转载自Night Wolf《怎样应用Excel函数》

 

日期型)是你要知道的退休那天的日期:Q请参考下列参数修改公式)

A1到E1分别是:姓名 A1(性别)  B1(出生日期)  C1(年龄)  D1(退休日)

公式

则:D2=year(now())-year(C2) 往下复制到所有即可

 

函数

1、出生日期

=IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,)

2、通过字符串取15位生份证出生日期=MID(D5,9,2)&"-"&MID(D5,11,2)得到例:2007-5

3、可以类推性别

=IF(A2<>"",IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女"),)

4、年龄

=IF(A2<>"",DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00"),TODAY(),"y"),)

A1单元格为出生日期,可用=DATEDIF(A1,NOW(),"y")计算其年龄

=DATEDIF(C3,TODAY(),"y")

5、身份证号更改为18为

=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A2)

6、身份证号更改为15为

=IF(LEN(A2)=15,A2,LEFT(REPLACE(A2,7,2,),15))

7、号码有效验证

=OR(LEN(A2)=15,IF(LEN(A2)=18,MID("10X98765432",MOD(SUM(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2)))

8、所属省份

=IF(A2<>"",VLOOKUP(LEFT(A2,2),data,2,),)

9、所属地区

=IF(A2<>"",VLOOKUP(LEFT(A2,6),data,2,))

10、选取区段时间

=DATEDIF(A3,B3,"Y")&"."&DATEDIF(A3,B3,"YM")注意选区所用格式,"."可以自选。

11、自动生成序号运用小技巧

=ROW()-ROW($A$1)    若序号1在第4行则公式改为=ROW()-ROW($A$4)+1即该行序号从1开始

=ROW()-2

边写边生成=+IF(J4="","",ROW(A1))

边写边生成,由关联的内容控制则即使添加空行但总数不变=IF(H8="","",COUNTA($H$8:H8))

12、批量更改日期小技巧

例:日期2005.3改成2005-3

a、查找“.”更换成“-”,此时桌面变成Mar-05

b、右键—设置单元格式—自定义—(  yy-m  )—显示数据  05-3(已成日期格式)

右键—设置单元格式—自定义—(  yyyy-m  )—显示数据  2005-3(已成日期格式)

 

 

 

13、技师函数

该函数空时显示?=IF(E7="高级工","150",IF(E7="初级工","50",IF(E7="中级工","100",IF(E7="技师","300"))))

=IF(E1="高级工","150",IF(E1="初级工","50",IF(E1="中级工","100",IF(E1="技师","400", IF(E1="内聘技师","300",IF(E1="内聘高级技师","500", IF(E1="助理技师","清除手写", IF(E1="高级技师","600", ""))))))))

=LOOKUP(A16,{50,100,150,200,300,400,500,600,700},{"初级工","中级工","高级工","助理技师","内聘技师","技师","内聘高级技师","高级技师","  "} )

=IF(MID(B3,6,1)="1","高级技师",IF(MID(B3,6,1)="2","技师",IF(MID(B3,6,1)="3","助理技师",IF(MID(B3,6,1)=4,"高级工",IF(MID(B3,6,1)=5,"中级工","")))))

 

 

14、利用进厂时间自动计算合同期,加上条件格式可产生预警效果=IF(TODAY()>(B105+365),"用工时间到",IF((B105+365)-TODAY()<=7,"还有"&(B105+365)-TODAY()&"天","正常用工期"))

合同期满告警已知期满时间“=IF(TODAY()>Q3,"已过试用期",IF(Q3-TODAY()<=7,"试用期还有"&Q3-TODAY()&"天结束","正在试用期"))”。

15、报告男、女退休时间=IF(TODAY()>B78,"退休时间到",IF(B78-TODAY()<=7,"退休时间还有"&B78-TODAY()&"天","正常用工期"))

16、退休日期1

=IF(A2<>"",EDATE(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00"),660+MOD(RIGHT(LEFT(A2,17)),2)*60),)

退休日期2

=IF(C3="男"

,DATE(YEAR(B3)+60,MONTH(B3),DAY(B3)),DATE(YEAR(B3)+55,MONTH(B3),DAY(B3)))

17、简单函数例=IF(A1<>" ","工人","  ")

18、当h5有数字时请加在zhong10000的后面。函数=CONCATENATE("zhong","10000",H5)

 

  评论这张
 
阅读(8)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017