新知一下
海量新知
6 0 8 2 1 8 8

【Excel应用干货】职场中最常用的excel操作大全

Excel高效应用 | 用好Excel工作高效不加班! 2021/04/15 09:24

一、身份证相关

十个要点要记牢

1、身份证的 输入

在excel中输入大于11位数字,会以科学记数法显示。而大于15位,后面的数字全转换为0,所以不能直接在excel中输入身份证号。

解决方法 先把该列设置为 文本型 格式,或输入前先输入 单撇 ( ' ) 再输入身份证号码

新知达人, 【Excel应用干货】职场中最常用的excel操作大全

2、身份证的 导入

从word、网页、数据库中复制含身份证信息的表格时,如果直接粘贴到excel中,身份证码列同样会后3位变成0。

解决方法 同1粘贴或导入前把excel表中存放身份的列设置为 文本类型

3、身份证的 分列

如下图所示的员工信息在一列中 ,我们可以用分列的方式分隔成多列,但分列后身份证号码后3位会变成0。

解决方法 在分列的第3步,选取身份证列,选取 文本类型 即可。

新知达人, 【Excel应用干货】职场中最常用的excel操作大全

4、身份证号 长度验证

在输入身份证号码时,数字个数看起来很费劲。用数据有效性可以限制身份证号码输入必须是18位。

解决方法 选取输入单元格 - 数据 - 有效性 - 允许: 文本长度 ,输入 18

新知达人, 【Excel应用干货】职场中最常用的excel操作大全

单元格 A1 中是身份证号码

5、提取 生日

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

6、提取 年龄

= YEAR(TODAY())-MID(A1,7,4)

7、提取 性别

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

8、提取属相

=CHOOSE(MOD(MID(A1,7,4)-1900,12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")

9、提取星座

=VLOOKUP(--TEXT(MID(A1,11,4),"2015-00-00"),{0,"摩羯";42024,"水瓶";42054,"双鱼";42084,"白羊";42114,"金牛";42145,"双子";42177,"巨蟹";42208,"狮子";42239,"处女";42270,"天秤";42301,"天蝎";42330,"射手";42360,"摩羯"},2)

10、 Countif函数统计身份证号码出错的解决方法

由于Excel中数字只能识别15位内的,在Countif统计时也只会统计前15位,所以很容易出错。不过只需要用 &"*" 转换为文本型即可正确统计。

=Countif(A:A,A2 &"*" )

新知达人, 【Excel应用干货】职场中最常用的excel操作大全

二、入职计算

两个公式要记牢

A1是入职日期

1、入职年数

=Datedif(a1,today(),"Y")

2、入职月数

=Datedif(a1,today(),"M")

三、员工信息统计

三个要点要记牢

1、A部门多少人?(B列为部门)

=Countif(b:b,"A部门")

2、北京分公司经理级别有多少人?

=Countifs(B:B,"北京" ,C:C," 经理")

3、大于40岁的经理级别有多少人?

=Countifs(D:D,">40",C:C," 经理")

4、生日提醒(b2为出生日期)

链接: Excel中最完美的员工生日提醒功能!

四、工龄及工资计算

四个案例要记牢

1、本月工作日天数计算

下面是 2015年1月份 的工资表局部。

新知达人, 【Excel应用干货】职场中最常用的excel操作大全

我们可以帮这位HR妹妹这样设置公式:

C2 = NETWORKDAYS.INTL ( IF(B2<date(2015,1,1),date(2015,1,1),b2)< span=""> ,DATE(2015,1,31) , 11 )

公式说明:

  • IF(B2<date(2015,1,1),date(2015,1,1),b2)< span=""> ; 区分是不是新入职,如果不是新入职按本月1日作为开始日期,否则即为新入职,按实际入职日期算。

  • DATE(2015,1,31) :本月最后一天作为计算的截止日期。

  • 11 : 表示本公司是一周六天工作日,星期日是休息日期

2、工龄工资计算(每6个月40元,封顶200)

C2单元格中为入职日期

=IF(INT(DATEDIF( C2 ,”2015-11-30“,"M")/6)*40>200,200,INT(DATEDIF(C2, ”2015-11-30“ ,"M")/6)*40)

3、个人所得税计算

假如A2中是应税工资,则计算个税公式为:

=5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},)

4、工资条制作

工资表(一定要有 序号 列)

新知达人, 【Excel应用干货】职场中最常用的excel操作大全

制作方法:

步骤1 把标题行复制到另一个表中,然后在序号下输入数字1,然在在B2中输入下面公式并向右复制

=VLOOKUP($A2,销售部工资表!$A:$I,COLUMN(B1),0)

公式说明 :Column(b1)在公式向右复制时生成2,3,4,5,6...数字,作为VLOOKUP的第3个参数。

新知达人, 【Excel应用干货】职场中最常用的excel操作大全

步骤2 、选取前三行向下复制。(如果中间隔2个空行则选前4行)

新知达人, 【Excel应用干货】职场中最常用的excel操作大全

复制后,工资条已自动生成。

新知达人, 【Excel应用干货】职场中最常用的excel操作大全


更多“Excel”相关内容

更多“Excel”相关内容

新知精选

更多新知精选