会计常用excel公式

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

【身份证信息？提取】 从身份证号码中提取出生年月日

=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,\\

显示格式为 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)) =M

常用函数公式及技巧搜集

【身份证信息提取】

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

=IF(LEN(A2)=15,\\

从身份证号码中提取出性别

=IF(MOD(MID(A1,15,3),2),\男\女\

从身份证号码中进行年龄判断

以2006年10月31日为基准日,按按身份证计算年龄(周岁)的公式

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

按身份证号分男女年龄段

按身份证号分男女年龄段，身份证号在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(),\,50,60}-{0,0,5,5}*ISEVEN

一、员工信息表公式

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. 【身份证信息相关数据提取】………………………第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常用函数公式及技巧搜集

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

工业会计常用公式速查（一） 2009-5-7 11:0 【大 中 小】【打印】 1.工业总产值

工业总产值=计算期生产的成品价值+计算期已经销售和准备销售的自制半成品价值+ 计算期已完工的对外承做的工业性作业价值（大修理价值）－半成品、在制品期末期初结存量差额价值

2.工业净产值

生产法：工业净产值=工业总产值-物质资料消耗价值

分配法：工业净产值=工资总额+职工福利基金+税金±利润（亏损）+利息+其他

3.积累率

积累率=积累基金/国民收入×100%

4.固定资产需要量

某项生产设备需要量=计划期生产量/单位设备生产能力

用实物量计算，其计算公式为：

某项生产设备需要量=计划年生产量/单位设备全年有效工作时数×单位设备产量定额

单位设备产量定额=单位设备全年有效工作时间×单位时间的产量定额

单位设备全年有效工作时间=（全年日历数－节假日－设备检修停工天数）×每天工作班次×每班工作小时

用台时数计算，其计算公式为：

某项生产设备需要量=全年计划生产任务需用设备定额总台时/单位设备全年有效工作台时=∑（计划年产品产量×单位产品台时

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

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("