海量的Excel表格，员工基本信息、

提成计算、考勤统计、合同管理

....

没完没了

什么时候才能处理完啊？

其实今天

在excel中有太多好用的技巧，HR平时工作中可能涉及到的进行整理最快捷、最牛x的Excel技巧，看看哪个最厉害。

01

提取姓名、手机号

想拆分姓名和手机号码？先输入第一个姓名，选取它和下面的空行，按Ctrl+E，一秒搞定拆分（提取手机号码同样方法）

02

对比同行数据是否相同

选取两列后，按Ctrl +

03

隔列粘粘数据

复制 - 选择性粘贴 - 跳过空单元。可以跳过空列/行粘贴。

04

恢复E+的数字格式显示

数字长度如果大于11位会以科学记数法显示，设置自定义格式 0 可以让数字正常显示出来。

05

公式转数值

按 CTRL键 分两段选取并复制，选取要粘贴的位置，按 Enter 即完成转换。

06

转换日期

不规范的数字和字串，用分列可以快速转换成标准的日期格式。

07

快速合并多行数据

把列拉宽 - 选取数据 ， 开始 - 填充 - 两端对齐（Excel2003是内容重排）。

08

快速跨表复制粘贴

按shift选取多个工作表，选取工复制的表格区域，开始 - 填充 - 成组工作表 。

09

快速设置求和公式

Ctrl + g 定位空值 - 按 alt + = 一键设置求和公式。

你以为这就结束了？

赶紧消化上面的内容，

更牛的技能要来了！！！！

一

员工信息表公式

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天;还早")< span="">

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")< span="">

四

其他公式

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

每年各部门入职人数情况

到期提醒，看字眼就觉得温馨实用，尤其在智能化管理的现在。但是在Excel中如何去设置呢？

一、到期前“7”天提醒。

方法：

在目标单元格中输入公式：=IF((E3-TODAY())<8,"合同快到期了","")。

二、一定范围内，还剩X天到期提醒。

方法：

=IF((E3-TODAY())<8,"此合同还有"&E3-TODAY()&"天就到期了","")

三、还剩X天到期提醒。

方法：

在目标单元格中输入公式：="此合同还有"&E3-TODAY()&"天就到期了"。

四、颜色和文字共同提醒。

方法：

1、选定合同到期日期。

2、【条件格式】-【新建规则】-【使用公式确定要设置格式的单元格】。

3、输入公式：=e3-today()<8，并【确定】。

4、在文字提醒单元格输入公式：=IF((E3-TODAY())<8,"此合同还有"&E3-TODAY()&"天就到期了","")。

还在用Excel管理应收单？各种公式翻来覆去。更新维护头皮发麻！

记应收是一款轻量级的应收管理APP，操作异常简单，签合同【记一笔应收】，回款时【记一笔到账】，自动生成按月报表，总报表，应收单汇总，回款汇总等。

图一 应收款项

图二 记到账

图三 主页面

图四 合同列表

懒人推动世界，这款【记应收】有几大特色值得聪明的你去使用：

方便：所有操作，基本都在3次操作之内可以完成；

快截：数据存在本地，所以速度飞快，没网的情况也照常使用；

安全：数据使用非对称加密存于本地，不联网，再黑的客也没法窃取数据；

直观：独创日历视图，在日历中一目了然的看到哪天签订合同、哪天收到回款、哪天合同完成，应收管理再不是难事；

免费：这款应收软件不但好用关键是免费，还不怕数据丢失，真是经济又实惠！

说了这么多，不如下载实际操作下，才知道这款App真的是业界良心，全心的在为人民服务，您值得拥有！

日常工作中，经常需要用到一些到期日提醒的功能，比如人事部门需要能够实时了解员工的合同到期情况，以便在合同到期前能够及时续签合同。搞供应商管理的，比较关心供应商资质文件是否在有效期范围内，等等。

下面我们就来演示制作一张简单的员工合同到期提醒表。

STEP 1

新建一张工作表命名为“合同”，表中内容如下图所示：

然后，将E列、G列、H列分别设置为日期格式；将F列、I列设置为常规或数值格式（保留0位小数）。

STEP 2

再新建一张工作表命名为“标准”，通过该表可自由设定各个工龄段的签约合同期限，该表内容如下图所示：

STEP 3

设置F2单元格公式为“=DATEDIF(E2,TODAY(),"Y")”，用来显示员工工龄。设置好公式后，用填充手柄把下面的单元格填充满。

TODAY函数用来表示当天的日期；DATEDIF为Excel的隐藏函数，用来返回第一个参数和第二个参数之间相差的天数、月数、年数等（取决于第三个参数的值）。DATEDIF(E2,TODAY(),"Y")表示E2单元格入职日期与当前日期相差的年数，即工龄。

STEP 4

设置H2单元格公式为“=EDATE(G2,VLOOKUP(F2,标准!$A$2:$B$6,2,TRUE)*12)”，用来显示合同到期日期；设置好公式后，用填充手柄把下面的单元格填充满。

VLOOKUP函数用来查找F2单元格工龄所对应的签订合同有效期限，本例返回结果为1年、3年或5年；

EDATE函数表示G2单元格合同签订日期开始，然后加上VLOOKUP函数返回的月数（年数乘以12，换算成月数）后得到的日期，即合同到期日。

STEP 5

设置I2单元格公式为“=H2-TODAY()”，用来显示合同还有多少天到期。设置好公式后，用填充手柄把下面的单元格填充满。

设置好公式后，表格效果如下：

STEP 6

使用条件格式将合同到期小于10天的背景色填充为红色，看起来可以更醒目一些。

文章来源：友为软件编辑：财物无忧

中小企业经济往来，必然会涉及到合同管理的问题，几个人微企，合同量少还好管理，但随着业务快速发展，量的改变往往会出现很多意想不到的问题，很多企业老板往往因为一时经历太糟糕，如：合同文档半天找不到，合同统计半天出不来等，于是就想到合同管理上来，要求下属去找一款合同管理系统，对合同进行规范化管理。那么作为中小企业该如何选择适合自己的管理系统呢？下面从三个方面对大家进行介绍。

1 弄清合同管理需求

找合同管理系统是为了解决什么样问题，在此除了高层说的几点外，还得根据企业自身情况，联合业务、财务进行合同管理需求调研，列出一个详细合同管理系统需求清单，后面的软件系统就根据这个需求清单一个个去对号入座。

2 选择适合自己的合同管理系统

合同管理系统一般首要考虑SaaS软件，主要是这类软件在满足需求的条件下，它的造价是最便宜的；但SaaS软件是部署在云端也就是在公网上，不是企业内部服务器，如果企业对网络安全比较敏感的话，也可要求对方重新部署在企业内部服务器中，其中会产生一些服务费，增加软件成本。另外，如果企业合同管理需求过于复杂，SaaS软件满足不了，则需要考虑定制化软件。定制化软件基本上能解决合同管理上的所有问题，所付出代价是成本较高，可进行多方对比，选择造价低的系统。

3 合同管理系统应用执行跟进

当选择好管理系统后，接下来就是应用了，之前见到一个老板一时经历太差，要求下面的人找合同软件，下面的人找好之后，又要求人员重新回到excel，当时我是很费解了，excel做合同台账没问题，但对于合同收付款提醒，这点它是做不到智能提醒的，只有人员盯着、记着，等到哪天体验太差，又会回到合同管理系统上来，所以选择好合同管理系统后，接下来就是重在执行和应用。

▌网上搜索“财物无忧”即可免费体验合同、报销管理系统

(点击此处可免费下载使用合同、报销管理软件)

俗话说：万丈高楼平地而起。要做到精细化管理，就要从精细化管理的第一个层次—规范化开始做起，而合同管理的规范化自然也就成了不可忽视的因素。

从新年开始公司实行《集团公司合同管理办法》以来，成效极好。总的来说，规范了公司各项目合同的保存，建立了规范化、高效化地文件资料管理体系，可以更好的保存合同以及快速的查阅。具体情况如下：

1. 项目文件编号

公司合同归档编号规则采用公司名称拼音简称+部门简称拼音+K或R（分别代表框架合同或日常合同）+年份+月份+序号的规则编制，形象醒目，方便查找。如采购部的第1号合同：CQSB/CG R/2016-（月）-0001（当月自然数排序）。以前公司合同编号没有作统一规定，杂乱无章，不便查找，无形中浪费巨大的人力物力。

2. 电子归档

在excel里制定一个表格，建立电子化的合同台帐。内容主要包括：序号、合同名称、合同编号、合同单位、履行期限、履行情况信息、每份合同都必须编号，包括合同正本及附件。另外公司各部门将每月汇总台帐、明晰合同进行扫描存档备份。实行电子化归档，节省了翻阅合同的时间，避免了时间浪费。

3. 装盒归档

准备固定摆放合同的位置以及专用的文件盒，按照实际情况向内放入一定数量的合同，合同用文件夹统一归纳起来，在文件盒的侧而用醒目的笔，按时问顺序写上盒号，盒号即档案盒的排列顺序号C1. 2. 3形式)，并且将里面的文件编号按照从小至大(例如项目1-7)编排同样写在侧面，以便在电子档查阅合同的时候，可以准确的知道己编号的合同文件，在几号文件盒之中，另外用A4纸打印出横排为项目名称目录，竖列为文件编号的相关信息，贴在文件盒正面，方便查阅。

4. 纸质目录方式

将所有文件编号、项目名称以及每个合同的摆放盒号，按照顺序，用A4纸全部打印出来，摆放在归档合同的文件柜上，方便查阅，避免盲目翻找。

我们应该看到：公司实行合同化管理办法以来，取得重大成效的同时也存在如下问题：

1. 执行力度不够。在实际工作中，一些部门并没有完全按照上述规定进行

操作，导致此次券商和事务所需要资料时还是浪费了一些时间。同时合同的不完整仍然存在。

2. 没有建立起借阅登记制度。各部门可以准备一本专用于合同借出或者查阅登记本，登记人应将合同编号以及项目名称写在页面上，签署姓名以及借阅日期。归还时，可将之划去，且注明归还日期，以便知道合同的去向，防止合同丢失。

综上所述：找到一个合适的管理方式，遵循文件的形成规律，按照随办随归方式将当天接手的合同整理归档。纸质目录上，要及时添加更新，保证合同项目能全部在上体现出，避免盲目翻找，节约时间。归档的原文件应保持清洁，完整，对已损坏的文件予以修整。

Excel中最完美的合同到期提醒

每个公司都会有合同管理，今天我们学习用excel制作合同到期提醒模板，要实现的功能非常强大，包括:

提示到期天数按到期天数自动排序按到期时间段自动设置颜色提醒在到期提醒表中自动设置超链接

和兰色一起开始制作吧！

合同明细表：

操作步骤：

1、在A列前插入2列辅助列，在表格最后设置到期天数辅助列。并分别设置公式：

A2=H2*10000+ROW()B2=IF(OR(H2120),0/0,RANK(A2,A:A,1))注：120是在到期提醒表中提示的最大天数。可以按需要修改。H2=G2-TODAY()

‘

2、制作“合同到期提醒”表，进行以下操作

B列输入1~n的序列号C2设置公式并复制到C:H列：=IFERROR(VLOOKUP($B2,合同明细!$B:$H,COLUMN(B1),0),"")I2设置并向下复制公式：=IF(C2="","",HYPERLINK("#合同明细!A"&MATCH(C2,合同明细!C:C,0),C2))，作用是生成指向“合同明细表”中原行数的链接。

3、设置分段显示不同填充色。

选取表格 - 开始 - 条件格式 - 添加规则 - 单元格数值

介于0~30 填充红色介于31~90填充蓝色介于91~9999 填色绿色。因为公式返回的""也大于90，所以这里要设置一个足够大的数字区间，防止为空时也填充绿色。

动画演示：

合并提醒最终效果如下图所示：

卢子以前负责某公司的所有数据，老板让卢子全权处理。在数据方面，公司当时没有比卢子能力好的，因此，一切只能靠自己想，自己学，自己做，不断摸索，总结经验。

其中有一项工作就是管理扫描版合同，今天就来聊这个。

1.最初阶段

当时，同事们是将合同扫描后放在一个文件夹中，每份合同放一个文件夹，一个文件夹内又有N张合同图片。要看合同就需要找到合同的文件夹，再翻阅每张图片，不太方便。

以上为模拟图片，如有雷同，纯属巧合。

2.初步改善

将扫描的图片，通过软件转换成PDF文件，这样一份合同就一个PDF。查看合同，就相对方便了一些。

不过，随着合同越来越多，一个文件夹内往往有几百份合同，要找到某一份合同也要花费不少时间。

3.进一步改善

为了方便理解，这里模拟2份PDF合同。

Step 01创建一个Excel工作簿，命名为公司合同管理，然后打开工作簿。

接下来提供Excel2016+WIN10的操作方法，高版本在处理问题上会更加便利。

Step 02选择所有PDF合同，单击复制路径。

Step 03将路径粘贴到Excel中，这里就提取到了路径跟合同名称。

如果是其他版本，推荐阅读这篇文章。

好用！文件夹内工作簿名称一键提取以及重命名

Step 04将路径替换掉，只保留合同名称。按快捷键Ctrl+H，再将路径复制到查找内容栏，替换栏这里不用写任何内容，单击全部替换。

Step 05增加一些相关的文字说明。

Step 06在C2输入公式，下拉。HYPERLINK就是创建超级链接函数。

=HYPERLINK(B2,"打开")

说明：Excel工作簿一定要跟PDF在同一个文件夹。

Step 07比如，现在要找广州市xx学校合同。只需筛选就可以找到，然后点击C列的超级链接，这时会弹出一个对话框，因为是自己制作的PDF，绝对安全，直接单击确定就可以。

以上，就是卢子管理扫描版合同的一些经验分享。

如你所愿，文件夹里面存放着各种PDF合同，Excel工作簿跟PDF不在同一个文件夹。

在C2单元格输入公式，下拉。因为不在同一个文件夹，就必须添加路径。

=HYPERLINK("C:\Users\chenxilu\Desktop\公司合同管理\"&B2,"打开")

现在问题来了：在实际工作中，不可能所有文件的位置一直存放在你最初安排好的位置，然后就永远不移动了，对吧？

假设，现在将公司合同管理放在了培训班文件夹。

移动了文件夹，超级链接就失效了。

这时，只能更改公式中的路径，也就是每移动一次文件夹，需要更改一次公式。如果自己使用也没关系，大不了每移动一次，就改一次公式。但如果是发送给领导呢，难不成还要在他电脑上改公式？

=HYPERLINK("D:\0 书籍以及培训资料\新资料\2 培训班\公司合同管理\"&B2,"打开")

现在，回头看看我上篇文章提供的方法，Excel工作簿跟PDF放在同一个文件夹，然后移动文件夹。

这样照样可以打开。

其实，有很多问题，当年在处理的时候已经考虑到了。

简单，也许就是最好的！

知识扩展：如何用公式自动提取Excel工作簿所在路径？

在B2单元格输入公式。

=REPLACE(CELL("filename",A1),FIND("[",CELL("filename",A1)),255,)

公式中CELL("filename",A1)这部分是获取路径以及工作簿名称和工作表名称。

而路径就是[之前的所有内容，也就是用FIND函数判断[的位置，然后用REPLACE将后面的内容替换掉。

当然，这里也可以用LEFT函数。

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)

公式看不懂也没关系，这里再提供一种最简单的办法。

右键工作簿，选择属性。

位置这里就是路径，复制出来后，加一个\就好。

可能有读者在想，卢子这家伙，也奇怪，每次都搞这种简单的东西，也不搞点高大上的，一点水平都没。

Excel对我而言，已深入骨髓，压根儿没有简单与高大上之分。我只是将好用的方法分享给你，希望你能吸收，用在工作上，提高效率，仅此而已。

最后，大道至简是我的人生哲学。

废话不多说 先上图

客户管理

供应商管理

客户收款登记

供应商付款记录

合同账款一览表

觉得不错请点赞！！！