财务常用的excel函数公式大全

一、员工信息表公式

1、计算性别(F列)

=IF(MOD(MID(E3,17,1),2),'男','女')

2、出生年月(G列)

=TEXT(MID(E3,7,8),'0-00-00')

3、年龄公式(H列)

=DATEDIF(G3,TODAY(),'y')

4、退休日期(I列)

=TEXT(EDATE(G3,12*(5*(F3='男') 55)),'yyyy/mm/dd aaaa')

5、籍贯(M列)

=VLOOKUP(LEFT(E3,6)*1,地址库!E:F,2,)

注：附带示例中有地址库代码表

6、社会工龄(T列)

=DATEDIF(S3,NOW(),'y')

7、公司工龄(W列)

=DATEDIF(V3,NOW(),'y')&'年'&DATEDIF(V3,NOW(),'ym')&'月'&DATEDIF(V3,NOW(),'md')&'天'

8、合同续签日期(Y列)

=DATE(YEAR(V3) LEFTB(X3,2),MONTH(V3),DAY(V3))-1

9、合

EXCEL函数大全

1.数据库和清单管理函数

DAVERAGE 返回选定数据库项的平均值

DCOUNT 计算数据库中包含数字的单元格的个数

DCOUNTA 计算数据库中非空单元格的个数

DGET 从数据库中提取满足指定条件的单个记录

DMAX 返回选定数据库项中的最大值

DMIN 返回选定数据库项中的最小值

DPRODUCT 乘以特定字段（此字段中的记录为数据库中满足指定条件的记录）中的值

DSTDEV 根据数据库中选定项的示例估算标准偏差

DSTDEVP 根据数据库中选定项的样本总体计算标准偏差

DSUM 对数据库中满足条件的记录的字段列中的数字求和

DVAR 根据数据库中选定项的示例估算方差

DVARP 根据数据库中选定项的样本总体计算方差

GETPIVOTDATA 返回存储在数据透视表中的数据

2.日期和时间函数

DATE 返回特定时间的系列数

DATEDIF 计算两个日期之间的年、月、日数

DATEVALUE 将文本格式的日期转换为系列数

DAY 将系列数转换为月份中的日

DAYS360 按每年360天计算两个日期之间的天数

EDATE 返回在开始日期之前或之后指定月数的某个日期的系列数

EOMONTH 返回指定月份数之前或之后某月的最后一天的系列数

HOUR 将系列数转换为小时

excel常用函数公式及技巧搜集

目录

1. 【身份证信息相关数据提取】………………………第2页 2. 【年龄和工龄计算】…………………………………第4页 3. 【时间和日期应用】…………………………………第6页 4. 【排名及排序筛选】?????????????第9页 5. 【文本与页面设置】?????????????第21页 6. 【字符截取与增减】?????????????第41页 7. 【数据拆分与合并】?????????????第47页 8. 【条件自定义格式】?????????????第50页 9. 【汇总计算与统计】?????????????第60页 10. 【数值取整及进位】????????????第75页 11. 【大小值或中间值】????????????第80页 12. 【查询和查找引用】????????????第84页 13. 【输入数据的技巧】????????????第89页

第 1 页

1.【身份证信息相关数据提取】

从身份证号码中提取出生年月日

=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),\=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),\

=IF(A2<>\

显示格

excel常用函数公式及技巧

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/

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”格式的话，将

excel常用函数公式及技巧搜集

excel常用函数公式及技巧搜集

【资料名称】：excel常用函数公式及技巧搜集

【资料作者】：jakesen

【资料日期】：2010-5-26

【资料语言】：中文

【资料格式】：其它

【资料目录和简介】：

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))

Excel常用函数公式及技巧搜集

1. 身份证信息的提取

1.1. 从身份证号码中提取出生年月日

=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),\=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),\

=IF(A2<>\

显示格式均为yyyy-m-d。（最简单的公式，把单元格设置为日期格式）

=IF(LEN(A2)=15,\2,7,4)&\

显示格式为yyyy-mm-dd。（如果要求为“1995/03/29”格式的话，将”-” 换成”/”即可）

=IF(D4=\年00月00日\年00月00日\

显示格式为yyyy年mm月dd日。（如果将公式中“0000年00月00日”改成“0000-00-00”,则显示格式为yyyy-mm-dd）

=IF(LEN(A1:A2)=18,MID(A1:A2,7,8),\

显示格式为yyyymmdd。

=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),\

=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)&\年\月\日\

=IF(A

常用函数练习

一、PV函数 例1.3年后想得到10000元,那么现在应一次存入多少钱?(银行利率为12%) FV= 10000 Nper= 3 使用参数(单元格)

EXCEL常用财务函数 PV(rate,number of periods,paymemts,future value,type)Rate=

例2.如果预在10年后使得存款数额达到150000元，且现在每月存入1000元，计算其开始应存入银行 FV= 150000 Nper= 10 Rate= 使用参数(单元格) 二、FV函数 FV(rate,number of periods,paymemts,present value,type) 例3.现在每年年初存入1000元,10年后到期能收回多少钱?(银行利率为10%) Pmt= 1000 Nper= 10 Rate= 使用参数(单元格) 例4.现在将10000元存入银行,银行利率为10%,4年后能收回多少钱? PV= 10000 Nper= 4 使用参数(单元格)

Rate=

三、PMT函数 PMT(rate,number of periods,present value,future value,type) 例5.向银行贷款200000元购买

函数大全总目录符1 符2 一、 二、 三、 四、 五、 六、 七、 八、 九、 十、 十一、 十二、常用指数

函数基本知识 函数索引 日期与时间函数 数学与三角函数 逻辑函数 查找与引用函数 数据库函数 文本函数 统计函数 财务函数 工程函数 信息函数 外部函数 用户自定函数

名称函数基本知识 函数索引 日期与时间函数 1、 计算日期DATE EOMONTH WORKDAK

链接

返回

跳转 跳转 跳转

2、 计算天数NETWORKDAYS DATEDIF 跳转 跳转

函

DAYS360

跳转

3、 计算指定期间的比率YEARFRAC 跳转

4、 计算日期为第几个星期WEEKNUM 跳转

5、 计算表示日期的数值DATEDIF DATEVALUE 跳转 跳转

6、 计算表示时间的数值TIME TIMEVALUE 跳转 跳转

7、 计算日期或时间TODAY NOW 跳转 跳转

8、 从日期中提取出年/月/日和星期YEAR MONTH DAY WEEKDAY 跳转 跳转 跳转 跳转

8、 从时间中提取出时/分/秒HOUR MINUTE SECOND 返回 跳转 跳转 跳转

数学与三角函数 1、 数值求和SUMSUMIF COUNTIF

★★★★★ ★★★★★ ★★★☆☆

跳转 跳转

1、计算性别(F列)

=IF(MOD(MID(E3,17,1),2),"男","女")

2、出生年月(G列)

=TEXT(MID(E3,7,8),"0-00-00")

3、年龄公式(H列)

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

4、退休日期(I列)

=TEXT(EDATE(G3,12*(5*(F3="男")+55)),"yyyy/mm/dd aaaa")

5、籍贯(M列)

=VLOOKUP(LEFT(E3,6)*1,地址库!E:F,2,)

注：附带示例中有地址库代码表

6、社会工龄(T列)

=DATEDIF(S3,NOW(),"y")

7、公司工龄(W列)

=DATEDIF(V3,NOW(),"y")&"年"&DATEDIF(V3,NOW(),"ym")&"月"&DATEDIF(V3,NOW(),"md")&"天"

8、合同续签日期(Y列)

=DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3),DAY(V3))-1

9、合同到期日期(Z列)

=TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY(),"[

10、工龄工资(AA列)

=MIN(700,DATEDIF($V3,NOW(),"y")*50)

11、生肖(AB列)

=MID("