实际的工作中，我们常用的函数公式其实都是最基本的，对于一些高大上的功能，一般情况下我们用到的很少，所以，对一般函数公式的掌握非常的重要。

一、文本提取。

目的：从指定的字符串中提取年份，部门，编号。

方法：

1、在目标单元格中分别输入：=LEFT(B3,4)、=MID(B3,6,3)、=RIGHT(B3,3)。

2、Ctrl+Enter填充。

二、生成随机数。

目的：生成0-99之间的随机数。

方法：

1、在目标单元格中输入公式：=RANDBETWEEN(1,99)。

2、F9刷新。

三、四舍五入保留2位小数。

目的：对销售额进行规范处理。

方法：

在目标单元格中输入公式：=ROUND(C3,2)。

四、不显示公式错误值。

目的：对数据中未知的错误进行有效的处理。

方法：

在目标单元格中输入公式：=IFERROR(D3/C3,"")。

五、条件判断。

目的：对得分情况进行判断，如果大于等于95分，则“晋升”。

方法：

在目标单元格中输入公式：=IF(C3>=95,"晋升","")。

六、首字母大写。

方法：

在目标单元格中输入公式：=PROPER(B3)。

七、统计排名。

方法：

在目标单元格中输入公式：=RANK(C3,C$3:C$9,0)。

八、计数统计。

目的：统计销售笔数。

方法：

在目标单元格中输入公式：=COUNTIF(B3:B9,H3)。

九、多表求和。

目的：对1、2、3、的销售额及销售笔数进行汇总。

方法：

在目标单元格中输入公式：=SUM('1:3'!D3)、=SUM('1:3'!I3)。

十、按次数重复数据。

目的：将姓名重复3次。

方法：

在目标单元格中输入公式：=REPT(B3,3)。

有一个朋友，他有一个团队，做的是短期临时工作。每天的用工人数不是很固定，有时10来个人，有时20多人，高峰时期有30多人。他每天有一个工作，就是记录工人的考勤，一开始的时候，是记在本子上，每天都记，因为人员不固定，工作量特别大，特别是每2周核算工资的时候容易出错，当时按计算器，按了一会就不记得按到哪了。有时为了核对准确，要花好几个晚上，甚至熬夜到半夜。有一次他和我聊起他的烦恼后，我建议他立即使用Excel记录工资考勤，并利用Excel公式计算工人工资，让他从重复烦索的工作走出来，帮他节省了大量时间。那么用Excel公式计算工资是如何实现的呢？我现在就来演示整个过程。

说明：以下为了表格不会太宽，以便在手机上观看最佳效果，只演示了1号至5号的工资情况（即C列是1号，D列是2号，以此类推，G列是5号），如增到2周或1个月只要在此基础上，在G列后面插入相应的列即可。

第一步：左边红色线框中数据是考勤，根据实际出勤情况填写。1号时只要把C列（即1号）的出勤天数记录上，当天晚上收工回家花上几分钟记录一下。同理2号时把（D列）也是这样记录，当5天记录满时是以下左边红框内的数据。右边红框内的数据也是固定的，即每天的工资数。

第二步,开始利用Execl公式，计算5天内的出勤总天数。用到的公式是=SUM(C3:G3)，记得有个等于号=，SUM是求和意思，C3就代表C列和第三行交叉的数据，以下这种表示方法以此类推。=SUM(C3:G3)的表达的含义就是上图中0.5+0.5+1+1+1=4的结果。

第三步：我们回车后就看到结果4出来了。

第四步：选中H3单元格，出现十字光标时，往下拉。

第五步：这样就得到H4的值是4.5，H5的值是5

第六步，计算总工资时，用到的公式是=H3*i3

第四步：回车时J3的值就出来了，是400。选中J3单元格，出现十字光标时，往下拉。

第五步：这样就得到J4的值是315，H5的值是350

第六步：计算汇总的公式是=SUM(J3:J6)

第七步：回车后，汇总的工资就是1065

至此，工资明细和工资汇总都出来，如果检查也很方便，检查一行一个人的，看看公式的范围有没有错，如果没有错基本就可以放心了。是不是Excel公式比用记在本子上，然后用记算器计算效率提升很多。您如果有需要也可以参考来做。

本头条号：时代新生分享工作、生活、技能方面的经验，愿和您一起成长。请多多关注哦！

上午好，伙伴们！丢掉Excel帮助文件，跟小编一起轻松学常用的十大Excel函数。

一、IF函数

作用：条件判断，根据判断结果返回值。

用法：IF(条件,条件符合时返回的值，条件不符合时返回的值)

案例：假如国庆节放假7天，我就去旅游，否则就宅在家。

=IF(A1=7,"旅游","宅在家")，因为A1单元格是3，只放假3天，所以返回第二参数，宅在家。

二、时间函数

TODAY函数返回日期。NOW函数返回日期和时间。比如要获取今天的日期，可以输入：=TODAY()，要获取日期时间，可以输入：=NOW()

计算部落窝教育EXCEL贯通班上线多少天了，可以使用：=TODAY()-开始日期

三、最大值函数

excel最大值函数常见的有两个，分别是Max函数和Large函数。

案例：分别取出产品A、产品B、产品C在2015年6月1日-6月10日的最大产量。

在B12单元格输入公式：=Max(B2:B11)，然后向右拖动复制得到产品B和产品C的最大产量。前面我们说了excel取最大值函数有MAX函数和Large函数，那么Large函数一样可以做到，公式为=Large(B2:B11,1)。

Max函数只取最大值，而large函数会按顺序选择大，比如第一大的、第二大的、第三大的。

四、条件求和：SUMIF函数

作用：根据指定的条件汇总。

用法：=SUMIF(条件范围,要求,汇总区域)

SUMIF的第三个参数可以忽略，第三个参数忽略的时候，第一个参数应用条件判断的单元格区域就会用来作为需要求和的区域。

五、条件计数

说到Excel条件计数，下面几个函数伙伴们需要了解一下。

COUNT函数：数字控，只要是数字，包含日期时间也算是数值，都统计个数。

案例：A1：B6区域，用count函数统计出的数字单元格个数为4。日期和时间也是属于数字，日期和时间就是特殊的数字序列。

COUNTA函数（COUNT+A）：统计所有非空单元格个数。

输入公式=COUNTA(A1:C5)，返回6，也就是6个单元格有内容。

COUNTIF函数（COUNT+IF）：统计符合条件的单元格个数。

语法：=countif（统计的区域，“条件”）

统计男性有多少人：=COUNTIF(B2:B8,"男")

六、查找函数

VLOOKUP(查找值,查找区域,返回查找区域的第几列,精确还是模糊查找)

E4单元格输入公式：=VLOOKUP(E2,A:B,2,)

前中我们已经提到过，原模板已经预设了五险一金以及个税的公式，所以，我们先来分析一下，原表中的公式是如何使用的，本次文章我们主要开看一下五险一金计算方式以及roundup函数的应用实例。

原表中，五险一金的公式如下，

我们都知道，五险一金的个人及企业部分等于该险种基数与相应比例的乘积（如果有医疗大病项需要单独加入）。Sum求和公式我们不用多说，除此之外，我们不难发现，表中还有两个特殊的细节。

第一，原表中的被乘数即是前面的社保基数一项而乘数选择绝对引用了上面的一个单元格。这个部分是我后期优化的，正是为了前面我们提到过的“可变原则”。

因为，社保的比例未必是恒定的，根据不同地区或者政策变化都会发生改变。单独列出来，清晰明了，比如下个月医疗的公司部分突然改成了10%，我们只需改一个单元格即可，防止了后期改公式的繁琐，也无需点开公式就可以知道现在实行的比例。

有的朋友可能觉得自己脑袋好用，没有必要这么麻烦。但是在此必须提醒各位：做工资是个细节特别多的过程，所以有些地方改的越方便越好，一劳永逸。其他工作也是一样的，宁愿前期多费工夫，也不要后期花时间去检查。

后来，我公司人员同时使用了两种医疗表现比例，却要体现在一个表上，这个以后我们再分析。

第二，我们发现在乘积的同时，又在外面加上了一个roundup函数，它有什么作用呢？在excel中，该函数的解释如下，

Roundup（number，mum_digits）向上舍入数字

即为“保留n位小数，其他部分上前进一”用函数，number指的是需要处理的内容，mum_digits则为需要保留的小数位数，输入“1”，则为保留一位小数，以此类推。

为什么要使用这个公式呢？因为在我市的社保系统中，缴纳金额保留两位小数，且多余部分舍去，向前进一，而非四舍五入。如果不使用这个函数，偶尔就会出现工资表中的社保总额比社保局的金额上下差几分钱的情况。

由此，我们可以举一反三知道三个公式的用法，

这里我们做个小提示，当你还不熟悉某个函数的使用时，可以先输入该公式“=f(x)”后，同时按下ctrl+A键。如果我们使用的是roundup函数则出现下图，

只要按提示输入需要的部分然后确定即可，根据不同的函数，输入可能是某个数字、某个单元格甚至嵌套另一个公式。

在HR同事电脑中，经常看到海量的Excel表格，员工基本信息、提成计算、考勤统计、合同管理....看来再完备的HR系统也取代不了Excel表格的作用。一周前，交给小助理木炭一个任务，尽可能多的收集HR工作中的Excel公式。进行了整理编排，于是有了这篇本平台史上最全HR的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、合同到期日期(Z列)

=TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY(),"[<0]过期0天;[<30]即将到期0天;还早")

10、工龄工资(AA列)

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

11、生肖(AB列)

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

二、员工考勤表公式

1、本月工作日天数(AG列)

=NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),)

2、调休天数公式(AI列)

=COUNTIF(B9:AE9,"调")

3、扣钱公式(AO列)

婚丧扣10块，病假扣20元，事假扣30元，矿工扣50元

=SUM((B9:AE9={"事";"旷";"病";"丧";"婚"})*{30;50;20;10;10})

四、员工数据分析公式

1、本科学历人数

=COUNTIF(D:D,"本科")

2、办公室本科学历人数

=COUNTIFS(A:A,"办公室",D:D,"本科")

3、30~40岁总人数

=COUNTIFS(F:F,">=30",F:F,"<40")

五、其他公式

1、提成比率计算

=VLOOKUP(B3,$C$12:$E$21,3)

2、个人所得税计算

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

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

3、工资条公式

=CHOOSE(MOD(ROW(A3),3)+1,工资数据源!A$1,OFFSET(工资数据源!A$1,INT(ROW(A3)/3),,),"")

注：

A3:标题行的行数+2,如果标题行在第3行，则A3改为A5

工资数据源!A$1:工资表的标题行的第一列位置

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

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

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

六、利用数据透视表完成数据分析

1、各部门人数占比

统计每个部门占总人数的百分比

2、各个年龄段人数和占比

公司员工各个年龄段的人数和占比各是多少呢？

3、各个部门各年龄段占比

分部门统计本部门各个年龄段的占比情况

4、各部门学历统计

各部门大专、本科、硕士和博士各有多少人呢？

5、按年份统计各部门入职人数

每年各部门入职人数情况

附：HR工作中常用分析公式

1.【新进员工比率】＝已转正员工数/在职总人数

2.【补充员工比率】＝为离职缺口补充的人数/在职总人数

3.【离职率】（主动离职率/淘汰率＝离职人数/在职总人数=离职人数/(期初人数+录用人数)×100％

4.【异动率】＝异动人数/在职总人数

5.【人事费用率】＝（人均人工成本*总人数）/同期销售收入总数

6.【招聘达成率】=（报到人数+待报到人数）/（计划增补人数+临时增补人数）

7.【人员编制管控率】=每月编制人数/在职人数

8.【人员流动率】=（员工进入率+离职率）/2

9.【离职率】=离职人数/（（期初人数+期末人数）/2）

10.【员工进入率】=报到人数/期初人数

11.【关键人才流失率】=一定周期内流失的关键人才数/公司关键人才总数

12.【工资增加率】=（本期员工平均工资—上期员工平均工资）/上期员工平均工资

13.【人力资源培训完成率】=周期内人力资源培训次数/计划总次数

14.【部门员工出勤情况】=部门员工出勤人数/部门员工总数

15.【薪酬总量控制的有效性】=一定周期内实际发放的薪酬总额/计划预算总额

16.【人才引进完成率】=一定周期实际引进人才总数/计划引进人才总数

17.【录用比】=录用人数/应聘人数*100%

18.【员工增加率】 =（本期员工数—上期员工数）/上期员工数

【好东西记得分享哦！！！】

【好东西记得分享哦！！！】

【好东西记得分享哦！！！】

1根据成绩的比重，获取学期成绩

=C8*$C$5+D8*$D$5+E8*$E$5

（可以借助F4键快速切换）

也可以换种方式(加入SUMPRODUCT函数)

=SUMPRODUCT(C8:E8,$C$5:$E$5)

2根据成绩的区间判断，获取等级

=IF(B5>=90,"优秀",IF(B5>=80,"良","及格"))

IF函数语法：

=IF(条件,条件为真返回值,条件为假返回值)

IF函数图解

3重量±5以内为合格，否则不合格

=IF(AND(A4>=-5,A4<=5),"合格","不合格")

=IF(ABS(A4)<=5,"合格","不合格")

AND函数当所有条件都满足的时候返回TRUE，否则返回FALSE

ABS是返回数字的绝对值。

4正确显示文本+日期的组合

=A4&TEXT(B4,"!_yyyy-m-d")

=A4&TEXT(B4,"!_e-m-d")

&的作用就是将两个内容合并起来，不过遇到日期，合并后日期就变成数字

。

有

日期存在的情况下要借助TEXT函数，4位数的年份也可以用e代替。

5计算收入大于3万的人的累计收入总和

=SUMIF(C:C,">30000", C:C)

SUMIF函数语法：

=SUMIF(条件区域,条件,求和区域)

对区域进行条件求和。

6序列号为102开头的累计收入总和

=SUMIF(A:A,"102*",C:C)

通配符号有2个，一个是*代表全部，102开头就是102*，如果是包含102用*102*

另一个通配符是?代表一个字符，比如现在有3个字符，就用???

你学会了吗？

推荐阅读

这10条建议，能帮你每天挤出2小时的时间

当面试官问你，你的缺点是什么时，该如何漂亮回答？

1、取绝对值

=ABS(数字)

2、取整

=INT(数字)

3、四舍五入

=ROUND(数字,小数位数)

二、判断公式

1、把公式产生的错误值显示为空

公式：C2

=IFERROR(A2/B2,"")

说明：如果是错误值则显示为空，否则正常显示。

2、IF多条件判断返回值

公式：C2

=IF(AND(A2<500,B2="未到期"),"补款","")

说明：两个条件同时成立用AND,任一个成立用OR函数。

三、统计公式

1、统计两个表格重复的内容

公式:B2

=COUNTIF(Sheet15!A:A,A2)

说明：如果返回值大于0说明在另一个表中存在，0则不存在。

2、统计不重复的总人数

公式：C2

=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。

四、求和公式

1、隔列求和

公式：H3

=SUMIF($A$2:$G$2,H$2,A3:G3)

或

=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)

说明：如果标题行没有规则用第2个公式

2、单条件求和

公式：F2

=SUMIF(A:A,E2,C:C)

说明：SUMIF函数的基本用法

3、单条件模糊求和

公式：详见下图

说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如"*A*"就表示a前和后有任意多个字符，即包含A。

4、多条件模糊求和

公式：C11

=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)

说明：在sumifs中可以使用通配符*

5、多表相同位置求和

公式：b2

=SUM(Sheet1:Sheet19!B2)

说明：在表中间删除或添加表后，公式结果会自动更新。

6、按日期和产品求和

公式：F2

=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)

说明：SUMPRODUCT可以完成多条件求和

五、查找与引用公式

1、单条件查找公式

公式1：C11

=VLOOKUP(B11,B3:F7,4,FALSE)

说明：查找是VLOOKUP最擅长的，基本用法

2、双向查找公式

公式：

=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))

说明：利用MATCH函数查找位置，用INDEX函数取值

3、查找最后一条符合条件的记录。

公式：详见下图

说明：0/(条件)可以把不符合条件的变成错误值，而lookup可以忽略错误值

4、多条件查找

公式:详见下图

说明:公式原理同上一个公式

5、指定区域最后一个非空值查找

公式;详见下图

说明：略

6、按数字区域间取对应的值

公式：详见下图

公式说明：VLOOKUP和LOOKUP函数都可以按区间取值，一定要注意，销售量列的数字一定要升序排列。

六、字符串处理公式

1、多单元格字符串合并

公式：c2

=PHONETIC(A2:A7)

说明：Phonetic函数只能对字符型内容合并，数字不可以。

2、截取除后3位之外的部分

公式：

=LEFT(D1,LEN(D1)-3)

说明：LEN计算出总长度,LEFT从左边截总长度-3个

3、截取-前的部分

公式:B2

=Left(A1,FIND("-",A1)-1)

说明：用FIND函数查找位置，用LEFT截取。

4、截取字符串中任一段的公式

公式:B1

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))

说明:公式是利用强插N个空字符的方式进行截取

5、字符串查找

公式：B2

=IF(COUNT(FIND("河南",A2))=0,"否","是")

说明: FIND查找成功，返回字符的位置，否则返回错误值，而COUNT可以统计出数字的个数，这里可以用来判断查找是否成功。

6、字符串查找一对多

公式：B2

=IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其他","东北")

说明：设置FIND第一个参数为常量数组，用COUNT函数统计FIND查找结果

七、日期计算公式

1、两日期相隔的年、月、天数计算

A1是开始日期（2011-12-1），B1是结束日期(2013-6-10)。计算：

相隔多少天？=datedif(A1,B1,"d") 结果：557

相隔多少月? =datedif(A1,B1,"m") 结果：18

相隔多少年? =datedif(A1,B1,"Y") 结果：1

不考虑年相隔多少月？=datedif(A1,B1,"Ym") 结果：6

不考虑年相隔多少天？=datedif(A1,B1,"YD") 结果：192

不考虑年月相隔多少天？=datedif(A1,B1,"MD") 结果：9

datedif函数第3个参数说明：

"Y" 时间段中的整年数。

"M" 时间段中的整月数。

"D" 时间段中的天数。

"MD" 天数的差。忽略日期中的月和年。

"YM" 月数的差。忽略日期中的日和年。

"YD" 天数的差。忽略日期中的年。

2、扣除周末天数的工作日天数

公式：C2

=NETWORKDAYS.INTL(IF(B2

说明：返回两个日期之间的所有工作日数，使用参数指示哪些天是周末，以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日

HR工作中用到的公式多种多样，今天给大家归纳了15个必备的Excel公式，希望对大家的工作起到帮助作用。

1、身份证提取出生日期

=--TEXT(MID(A2,7,8),"0000-00-00")

如果出现以下情况，设置为“日期”格式。

2、身份证提取性别

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

3、根据出生日期计算年龄

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

4、根据入职日期计算入职月数

=DATEDIF(A2,TODAY(),"M")

5、生日提醒

15天之内提醒

=TEXT(15-DATEDIF(A2-15,TODAY(),"yd"),"还有0天生日;;今天生日")

6、提取部分人员信息

=VLOOKUP($F2,$A$1:$D$11,4,0)

7、计算年休假天数

根据《职工带薪年休假条例》和《企业职工带薪年休假实施办法》，年休假是按自然年核算，工龄满1年、10年和20年的年度，要分段计算年休假。

=IFERROR(INT(SUM((DATE($B$1,MONTH(B3),DAY(B3))-DATE($B$1+{0,1},1,1))*{1,-1}*LOOKUP(DATEDIF(B3,DATE($B$1+1,1,1),"y")-{1,0},{0,0;1,5;10,10;20,15}))/SUM(DATE($B$1+{1,0},1,1)*{1,-1})),0)

8、统计员工全年培训情况

=INDEX(B:B,SMALL(IF($A$2:$A$1000=$F$2,ROW($2:$1000),4^8),ROW(1:1)))

G2单元格输入公式后，按Ctrl+Shift+Enter三键，向右和向下拖动填充公式。

9、计算迟到情况

假定8:30上班。

=IF(C2>8.5/24,"迟到","")

10、计算个税

=ROUND(MAX((B2-3500)*{3,10,20,25,30,35,45}%-{0,105,555,1005,2755,5505,13505},0),2)

11、计算年终奖个税

=LOOKUP(MAX(0.0001,(C2+MIN(B2-3500,0))/12),{0;3;9;18;70;110;160}*500+0.0001,MAX(0,(C2+MIN(B2-3500,0)))*{3;10;20;25;30;35;45}%-5*{0;21;111;201;551;1101;2701})

12、按部门汇总人数

=COUNTIF(C:C,F2)

13、按部门汇总工资

=SUMIF(C:C,F2,D:D)

14、统计员工考核结果

60分以下为“不及格”，大于等于60分小于80分为“及格”， 大于等于80分小于90分为“良好”， 大于等于90分为“优秀”。

=LOOKUP(B2,{0,"不及格";60,"及格";80,"良好";90,"优秀"})

15、计算部门平均绩效得分

=ROUND(AVERAGEIF($C$2:$C$357,F2,$D$2:$D$345),2)

转载自百家号作者：KUSO课堂

财务在日常工作中经常会使用Excel表格，处理大量的数据，比如人员工资、个人所得税等等，下面来介绍五个财务人员常用到的工资计算公式，简单又高效：

1、个人所得税计算公式：

=ROUND(MAX((I3-3500)*

{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-

{0,105,555,1005,2755,5505,13505},0),2)

2、根据实发工资，倒推税前工资：

=IF(K3<=3500,K3,ROUND(MAX((K3-3500-5*

{0,21,111,201,551,1101,2701})/(1-5%*

{0.6,2,4,5,6,7,9}))+3500,2))

3、根据实发工资，获取大写金额：

=SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(K3)),"[>0][dbnum2]G/通用格式元;[<0]负[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(K3),2),"[dbnum2]0角0分;;"&IF(ABS(K3)>1%,"整",)),"零角",IF(ABS(K3)<1,,"零")),"零分","整")

4、根据期限和调整日期，查询利率：

数组公式，按Ctrl+Shift+Enter三键结束。

=INDEX(C:C,MAX(IF(($A$3:$A$10=E3)*

($B$3:$B$10<=F3),ROW($3:$10))))

5、获取每个日期的时间段：

=LOOKUP(DATEDIF(B2,TODAY(),"m"),{0,1,2,3,6,12},{"不足1个月","1-2个月","2-3个月","3-6个月","6-12个月","1年以上"})

一、员工信息表公式

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(),"[<0]过期0天;[<30]即将到期0天;还早")

10、工龄工资(AA列)

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

11、生肖(AB列)

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

二、员工考勤表公式

1、本月工作日天数(AG列)

=NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),)

2、调休天数公式(AI列)

=COUNTIF(B9:AE9,"调")

3、扣钱公式(AO列)

婚丧扣10块，病假扣20元，事假扣30元，矿工扣50元

=SUM((B9:AE9={"事";"旷";"病";"丧";"婚"})*{30;50;20;10;10})

四、员工数据分析公式

1、本科学历人数

=COUNTIF(D:D,"本科")

2、办公室本科学历人数

=COUNTIFS(A:A,"办公室",D:D,"本科")

3、30~40岁总人数

=COUNTIFS(F:F,">=30",F:F,"<40")

五、其他公式

1、提成比率计算

=VLOOKUP(B3,$C$12:$E$21,3)

2、个人所得税计算

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

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

3、工资条公式

=CHOOSE(MOD(ROW(A3),3)+1,工资数据源!A$1,OFFSET(工资数据源!A$1,INT(ROW(A3)/3),,),"")

注：

A3:标题行的行数+2,如果标题行在第3行，则A3改为A5

工资数据源!A$1:工资表的标题行的第一列位置

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

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

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

六、利用数据透视表完成数据分析

1、各部门人数占比

统计每个部门占总人数的百分比

2、各个年龄段人数和占比

公司员工各个年龄段的人数和占比各是多少呢？

3、各个部门各年龄段占比

分部门统计本部门各个年龄段的占比情况

4、各部门学历统计

各部门大专、本科、硕士和博士各有多少人呢？

5、按年份统计各部门入职人数

每年各部门入职人数情况