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. 【身份证信息相关数据提取】………………………第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

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、ABS函数 函数名称：ABS

主要功能：求出相应数字的绝对值。 使用格式：ABS(number)

参数说明：number代表需要求绝对值的数值或引用的单元格。

应用举例：如果在B2单元格中输入公式：=ABS(A2)，则在A2单元格中无论输入正数（如100）还是负数（如-100），B2中均显示出正数（如100）。

特别提醒：如果number参数不是数值，而是一些字符（如A等），则B2中返回错误值“#VALUE！”。 2、AND函数 函数名称：AND

主要功能：返回逻辑值：如果所有参数值均为逻辑“真（TRUE）”，则返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。 使用格式：AND(logical1,logical2, ...)

参数说明：Logical1,Logical2,Logical3……：表示待测试的条件值或表达式，最多这30个。 应用举例：在C5单元格输入公式：=AND(A5>=60,B5>=60)，确认。如果C5中返回TRUE，说明A5

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

全书虽然以讲解知识点为主线,但书中所举的大多数实例都和日常的工作、学习紧密相结合,采用简单易懂的叙述方式,让读者能够轻松读懂和掌握这些知识。所以通过本书的学习,不但可以系统地掌握Excel 2007,而且书中提供的大多数实例都可作为模板或直接应用到您的工作中。

Excel 2007公式与常用函数

分为四种类型，包括算术运算符、比较运算符、文本连接运算符和引用运算符。

2.比较运算符是用来比较两个数值，比较运算符计算的结果为逻辑值，即TRUE或FALSE，比较运算符多

1

Excel 2007

全书虽然以讲解知识点为主线,但书中所举的大多数实例都和日常的工作、学习紧密相结合,采用简单易懂的叙述方式,让读者能够轻松读懂和掌握这些知识。所以通过本书的学习,不但可以系统地掌握Excel 2007,而且书中提供的大多数实例都可作为模板或直接应用到您的工作中。

3.连接运算符是使用“&”号加入或连接一个或多个文本字符串形成一串文本。如果使用文本连接运算符，4.引用运算符是用来表示单元格在工作表中位置的坐标集，引用运算符为计算公式指明引用单元格的位置，引用运算符的功能及说明如下。

运算符号

运算符名称

功能及说明

区域选择，包括两个引用单元格之间的所有单元格，如A1：B

常用函数公式及技巧搜集

【身份证信息提取】

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

=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