Excel的常用函数使用：
1、求和SUM
2、求平均值AVERAGE

3、求最大值MAX

4、求最小值MIN

5、四舍五入ROUND

6、条件判断IF 

7、AND函数：AND

8、统计函数:无条件统计函数COUNT
有条件统计函数COUNTIF

excel从身份证号码中提取信息

如下图为所示，是一份模拟的员工信息表，A列是姓名，B列是身份证号码，C列是职级。

1、提取出生年月

D2输入以下公式，向下复制。

=–TEXT(MID(B2,7,8),”0-00-00″)

身份证号码中的第7位到15位是出生年月信息。

首先使用MID函数从B2单元格的第7位开始，提取出8个字符，得到字符 “19820102”。 再使用TEXT函数将这个字符串变成日期样式的文本，得到字符串“1982-01-02”。

由于TEXT函数的结果是文本型的内容，所以加上两个小减号 — ，也就是计算负数的负数，通过四则运算，变成真正的日期序列。这样做的目的是为了便于后续的统计汇总。

如果公式结果显示为5位数字，别忘了设置一下单元格格式为日期。

2、计算年龄

计算年龄时，可以借助刚刚已经提取出的出生年月，E2单元格输入以下公式，向下复制。

=DATEDIF(D2,”2020-1-1″,”y”)

DATEDIF函数用于计算两个日期之间的间隔。用法是：

=DATEDIF(起始日期,截止日期,指定间隔类型)

本例中起始日期是D2的出生年月，截止日期是2020年1月1日。指定间隔类型为“Y”，表示计算间隔整年份。如果是“M”，就表示计算间隔的整月份了。

DATEDIF函数是隐藏函数，输入时没有屏幕提示，需要手工录入哦。

如果不借助D列的出生年月，公式可以这样写：

=DATEDIF(TEXT(MID(B2,7,8),”0-00-00″),”2020-10-1″,”y”)

3、提取性别

使用以下公式，可以提取出员工的性别信息：

=IF(MOD(MID(B2,17,1),2),”男”,”女”)

身份证号码中的第17位是性别信息，单数表示男，双数表示女。

首先使用MID函数，从B2单元格的第17位开始，提取出1个字符。

再使用MOD函数计算这个字符与2相除的余数。

最后使用IF函数判断，如果余数不等于0，返回男，如果余数是0，则返回女。 

4、计算退休年月

接下来，咱们要根据提取出的出生年月和性别以及职级信息，来计算退休年月，根据现有法规，男性退休年龄为60岁，女性干部退休年龄为55岁，女性年退休为50岁。

G2输入以下公式，向下复制。

=EDATE(D2,IF(F2=”男”,720,IF(F2&C2=”女干部”,660,600)))

EDATE函数的作用是根据指定的起始日期和间隔月份，计算从指定日期开始，间隔N个月后的日期。

本例中，指定起始日期是D2单元格的出生年月。

间隔月份的公式部分是：

IF(F2=”男”,720,IF(F2&C2=”女干部”,660,600))

这部分看起来有点小复杂，咱们慢慢拆解一下：

先用IF函数判断F2单元格的性别是不是等于”男”，如果是，返回720（720个月，也就是60岁）。

如果不等于”男”，就继续判断F2的性别连接C2的职级，是不是等于”女干部”，如果是，返回660（660个月，也就是55岁），如果不等于”女干部”，就返回600（600个月，即50岁）。

Excel引用不同表格数据创建下拉菜单

有时候，我们在引用不同表格数据创建下拉菜单时，在“数据验证”对话框中的“来源”中不能直接引用其他工作表的单元格。

如在Sheet2工作表的B2单元格设置数据验证序列，在“来源”文本框中不能直接使用“=Sheet1！A1：A8。

要引用其他工作表的单元格内容作为下拉菜单选择项的来源有两种方法可以实现：

1.定义名称；2. 使用INDIRECT函数。

如下图所示，在工作表Sheet1的单元格区域A1：A8列出了各项职务名称。

现在要在工作表Sheet2的““职务”列（B2:B20）创建下拉菜单，引用各项职务。

下面分别使用定义名称和INDIRECT函数两种方法来解决。我们一起来学习一下吧。

1. 定义名称

点击“公式”，选定“定义名称”下的“定义名称”。

将名称处命名为“职务”。

引用位置为“=Sheet1!\(A\)1:\(A\)8”，点击引用位置后面的“向上箭头”，然后切换到Sheet1表格中选中“A1:A8”范围区域(也可手动输入引用位置的内容)。

最后点击“确定”。

选中Sheet2工作表中的单元格区域B2:B20，单击菜单“数据”     “数据验证”，打开“数据验证”对话框。

切换到“设置”选项卡，在“允许”下拉列表中选择“序列”选项，在“来源”文本框中输入“=职务”，然后单击“确定”按钮。

这样用定义名称方法做的下拉菜单就完成啦。

2. 使用INDIRECT函数

选定Sheet2工作表的单元格区域B2:D20,单击菜单“数据”     “数据验证”打开“数据验证”对话框。

2. 切换到“设置”选项卡，在“允许”下拉列表中选择“序列”选项，在“来源”文本框中输入“=INDIRECT(“Sheet1!\(A\)1:\(A\)8”)”，然后单击“确定”按钮。

这样使用INDIRECT函数制作的下拉菜单就完成啦，你学废了吗？

Excel数据透视表的一个高级用法

有1~12月份的工资表

怎么单独生成某个员工的1~12月的工资表。如下动图所示。

遇到这样的问题，很多人都会想到使用函数公式。但用公式必竟有点复杂，所以今天兰色分享一个透视表的解决方法。

操作步骤：

1、依次按Alt + d + p调出数据透视表向导，第一步选取多区域合并计算区域，第二步选取自定义页字段

2、分别选取添加1~3月的数据表（如果有12月数据，也是分别添加），选择字段数字1，并分别命名为1月、2月、3月。

点击完成后会生成透视表

4、把页字段拖至行标签中，行拖至筛选标签中。然后插入切片器，选择行。

完工！具体的透视表与切片器的美化略。透视表的自定义页标签，算是一个“高级“用法，在合并多个表格时非常有用。

常用Excel排名公式大全

常用Excel排名公式大全在工作中，经常需要对数据进行排名次。但遇到不同的表格需要用不同的函数和公式，今天兰色首次对排名公式进行一次全面的总结，同学们一定要收藏起来了！

1、美式排名

2、中国式排名

3、多列排名

4、多表排名

5、组内美式排名

6、组内中国式排名

1、美式排名

有2个数字相同排名第1时，下一个名次直接是3而不是2

=RANK(C3,C:C)

2、中国式排名

有相同数字时，下一个排名连续而不间隔（如下图中E6单元格）

=SUMPRODUCT((\(C\)3:\(C\)12>=C3)*(1/COUNTIF(C\(3:C\)12,C\(3:C\)12)))

3、多区域排名

多列数字要放在一起排名，需要把区域放在括号内。

=RANK(B3,(\(B\)3:\(B\)12,\(E\)3:\(E\)12,\(H\)3:\(H\)12,\(K\)3:\(K\)12))

4、多表排名

多个表格排名时，可以直接引用连续的表格

=RANK(B2,’1月:3月’!B:B)

5、组内美式排名

同类项子数据进行排名，而不受其他类型数据影响

=SUMPRODUCT((A\(2:A\)17=A2)*(\(B\)2:\(B\)17>B2))+1

6、组内中国式排名

 同组子数据进行中国式排名，而不受其他组数据影响

{=COUNT(0/(B2<=(MATCH(A2&B\(2:B\)34,A\(2:A\)34&B\(2:B\)34,)=ROW(\(1:\)33))*B\(2:B\)34))}

注：数组公式，两边大括号不是手工输入，而且输入公式后按Ctrl+shift+enter三键后自动添加的