在会计的电脑中，经常看到海量的Excel表格，员工基本信息、提成计算、考勤统计、合同管理....看来再完备的会计系统也取代不了Excel表格的作用。于是，尽可能多的收集会计工作中的Excel公式，所以就有了这篇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(),"[

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

四、其他公式

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、按年份统计各部门入职人数

每年各部门入职人数情况

六、万能的VLookup

在Excel中，也有个著名的万人迷，它就是VLookup，只要是找东西，大家首先想到的就是它。

先来个最基本的查找当开胃菜：

Vlookup语法：

Vlookup(根据什么找,到哪里找,找哪个,怎么找)

注意：

1、“根据什么找”中的“什么”一定要位于“到哪里找”区域的第1列！

2、若从“到哪里找”区域中找到多个“什么”，则仅返回第1个找到的“什么”对应的东西；

3、“找哪个”不是实际列号，而是“到哪里找”区域中的第几列，其中，“什么”位于第1列，以此类推；

4、“怎么找”包含0（精确查找）、1或省略（模糊查找），其中，模糊查找时，首列必须升序排列；

公式分析：

= VLOOKUP(G3,C3:E12,2,0)

根据G3单元格的查找客户（第1参数），到C3:E12单元格区域中找（第2参数），其中第1列是客户名称列，即查找依据所在的列，要查找第2列的数据值（第3参数），即查找客户的付款金额，按精确查找的方式进行查找（第4参数），即客户名称与查找客户要完全相同；

再看以下数据，要根据订单号，查找该订单的所有资料，你怎么做？

在I、J、K、L列分别输入VLOOKUP公式，当然可以，但要是数据列较多，就比较麻烦了，告诉你一个公式就能搞定：

公式分析：

=VLOOKUP($H$3,$B$3:$F$12,COLUMN(B1),0)

1、需要在“客户名称”列返回查找区域第2列的值，在“付款金额”列返回查找区域第3列的值……，以此类推，为了实现一个公式就能在不同的列返回对应的数据，我们需要让VLookup的第3参数，即“找哪个”变成动态的，在I3单元格第3参数为2，在J3单元格第3参数为3，那么，COLUMN函数就能帮上忙了：

2、COLUMN函数可以返回指定单元格的列号，COLUMN(B1)返回B1单元格的列号2，由于使用的是单元格相对引用，随着公式向右复制，J3单元格会变成COLUMN(C1)，即返回C1单元格的列号3；

3、再以COLUMN函数的结果作为VLookup函数的第3参数，就能实现让“找哪个”变成动态的了，刚好满足了我们的要求。

想根据条件找到多个符合的数据，VLookup可以做到吗？比如：一个订单号记录了订购的多款产品，想根据订单号查找该订单下的所有产品，怎么做呢？

第1步：首先我们要构造一个辅助序号列，在A3单元格输入公式，并下拉复制到A12单元格：

=(B3=$G$3)+A2

公式分析：

l B3=$G$3：判断B3单元格的销售订单号是否等于G3单元格的查找订单号，若相同，则返回true，否则返回false；

l 逻辑值再与A2相加，true相当于1，false和空相当于0，得到截止当前行，查询订单号出现的总次数；

第2步：在H3单元格输入公式：

=VLOOKUP(ROW(A1),$A$3:$C$12,3,0)

公式分析：

1、为了查找订单号对应的多个产品，根据下图可以看出，只要查找到1~10（10为查询数据总行数，为某订单可能包含的最多产品数）在A列中出现的行位置，再找到相应的第3列即C列的订单产品，就搞定了。

2、我们需要将查找到的第1个产品放入H3列，第2个产品放入H4列，依次向下，直至填完查找订单号包含的所有订单产品；

3、于是，我们在H3单元格查找A列的序号1，即查询订单号第1次出现的位置，并返回该订单下的第1个产品，H4单元格查找序号2……

4、而ROW函数恰好可以满足以上要求，在H3单元格使用ROW(A1)作为VLookup的查找条件，ROW(A1)可以返回指定单元格A1对应的行号1，随着公式向下复制，由于A1为相对引用，到H4单元格将变为以ROW(A2)即2作为查询条件；

第3步：为H列处理错误值，修改H3单元格的公式，并下拉复制到H12：

=IFERROR(VLOOKUP(ROW(A1),$A$3:$C$12,3,0),"")

公式分析：

1、我们并不确定每个查询订单号下到底有多少个产品，因此，我们将上一步的公式从H3单元格一直复制填充到H12，共10格，即查询数据区域的总行数，意思是，某个订单号下，最多最多可能包含的产品个数；

2、但一般来说，某个查询订单号下，不会有这么多个产品的，于是上一步的公式就出现了下面的情况：

3、这些“#N/A”就是没找到第n个产品时出现的错误值，IFERROR函数的作用就是屏蔽掉它们：若VLookup的结果出现错误值，则显示空值””。

今天分享的Excel公式虽然很全，但实际和会计实际要用到的excel公式相比，还会有很多遗漏。欢迎做会计的同学们补充你工作中最常用到的公式。