职场人必备的26个Excel工作技巧|字符串|工作簿|工作表 / 开普饭

我们在工作中常常要与 Excel 打交道，了解其中的使用技巧并能灵活运用一些函数公式对于提高工作效率大有助益。

小编今天就为各位职场人总结了一些使用技巧，快来了解一下吧。

从身份证号码提取生日

案例场景截图如下（黄色区域输入公式）

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

根据身份证号码计算退休日期

案例场景截图如下（黄色区域输入公式）

=DATE(MID(A2,7,4)+55+MOD(MID(A2,17,1),2)*5,MID(A2,11,2),MID(A2,13,2))

检查身份证号码是否重复

案例场景截图如下（黄色区域输入公式）

=IF(COUNTIF($B$2:$B$11,B2&"*")>1,"重复","")

根据身份证号码判断性别

案例场景截图如下（黄色区域输入公式）

=IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女")

根据身份证号码实现生日提醒

案例场景截图如下（黄色区域输入公式）

=TEXT(7-DATEDIF(--TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2.2),8),"0-00-00"),NOW()+7,"yd"),"0天后生日;;今天生日")

还有其他提取方式：

提取属相

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

提取星座

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

根据分项得分和所占权重计算KPI绩效最后得分

案例场景截图如下（黄色区域输入公式）

=SUMPRODUCT(B$2:E$2*B3:E3)

去掉一个最高分、去掉一个最低分后再计算平均分

案例场景截图如下（黄色区域输入公式）

=TRIMMEAN(B2:H2,2/COUNT(B2:H2))

个人所得税计算

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

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

统计两个日期之间的工作日天数

一提到工作日计算，很多HR都皱紧了眉头，因为各种岗位员工的排班不同，休息日不同，再加上员工的请假和各种节假日的调休......99%的HR都是在手动计算工作日中苦苦煎熬。

看完下面这个示例的解决方案，以上所有问题一扫而光！

上图所示为按照周六周日双休的企业中，加上自定义节假日的工作日算法

首先定义名称，按打开名称管理器创建自定义名称

holidays=OFFSET(Sheet1!$E$2,,,COUNTA(Sheet1!$E:$E)-1)

然后在C2输入公式：

=NETWORKDAYS.INTL(A2,B2,1,holidays)

这样就轻松计算出起始日期和终止日期之间的工作日天数了

为了大家能够举一反三，我再多啰嗦几句，解析一下原理。

NETWORKDAYS.INTL函数的语法结构如下：

=NETWORKDAYS.INTL(起始日期,终止日期,周末日weekend,自定义节假日holidays)

关键点在于第三参数和第四参数。

关于第3参数weekend第二种表达方式的几点说明：

1、weekend 的表达方式2是一个长度为7的字符串

2、该字符串中的每个字符代表一周中的一天，从星期一开始

3、1 代表休息日，0 代表工作日

4、该字符串中只允许使用字符 1 和 0

关于第4参数holidays的几点说明：

1、是一个包含一个或多个日期的可选集合，这些日期将作为节假日不参与工作日个数统计

2、可以是包含日期的单元格区域，也可以是区域的引用，还可以是代表日期序列值的数组常量

3、其中的日期或序列值顺序可以任意打乱，不影响统计。

希望能够帮到大家轻松应对各种情况的工作日计算！

根据工龄计算年假天数的4种方法

【职工带薪年休假条例】

根据职工累计工作年限而定：

1、已满1年不满10年的，年休假5天;

2、已满10年不满20年的，年休假10天;

3、已满20年的，年休假15天。

案例场景截图如下（黄色区域输入公式）

方法1=IF(B2<1,0,IF(B2<10,5,IF(B2<20,10,15)))

方法2=LOOKUP(B2,{0,1,10,20},{0,5,10,15})

方法3=5*MIN(3,ROUNDUP(B2/9.9,0))

方法4=5*MIN(3,CEILING(B2/9.9,1))

日期和星期同时显示

设置单元格格式-数字-自定义: yyyy-m-d aaaa

快速复制表格

按Ctrl不松开往下拖动表格，即可快速复制表格。

多个单元格批量输入同一内容

选中要输入内容的单元格，输入内容，最后按【Ctrl+Enter】结束输入。

小提示：不连续的单元格，可以按【Ctrl】选中。学会用这16个Excel神技巧，零基础都不用怕！

跨表格复制粘贴

按Shift键选取多个工作表，选中需要复制的表格区域，开始-填充-成组工作表-填充“全部”。

快速将多行内容汇总到同一个单元格

利用【填充】功能下的“两端对齐”，可以实现内容合并的功能：拉宽列-选中需要复制的单元格内容-开始-填充-两端对齐。

多个单元格内容合并为一个单元格且分行显示

主要利用剪贴板功能：复制多个单元格-双击要粘贴的单元格-点击剪贴板中已复制的内容。

不复制隐藏的行

按【Alt+; 】键，选取显示的行，然后复制、粘贴，即可避免复制隐藏掉的行。

自动设置小数点

输入数字之前先在单元格内点击百分比样式%，输入数字后再点击千位分隔符样式。

利用名称框快速填充内容

这里我们可以利用Excel的名称框实现快速填充200行的内容：在名称框中输入A1:A200，按回车键，系统自动选中A1:A200的单元格。

然后输入内容，自动填充第一个空白单元格。最后按【Ctrl+Enter】组合键，完成批量录入。

快速显示表格中的公式

我们可以按【Ctrl+~】组合键进行公式的显示或隐藏，不过这个快捷键经常被输入法占用。

所以也可以直接点击【公式】选项卡中的“显示公式”进行切换。

快速批量新建文件夹

在A列中把需要新建文件夹的文件名录进去，然后在B1中输入公式="MD "&A1 （注意：MD后面一定要有个空格）。

按Enter键后，光标移到B1单元格右下角，显示“+”时下拉填充，然后把B列的数据复制到记事本中，单击另存为，把.txt文档后缀改为.bat，双击运行该文件即可。

快速关闭多个Excel工作簿

按Shift键不松，单击右上角关闭按钮，即可关键所有已经打开工作簿。

快速核对同行数据

有些人比较习惯的做法是使用查询函数比如Vlookup进行查询匹配。

其实我们只需要一个快捷键就可完成数据的核对。

选取表格两列，按【Ctrl + 】即可，还可以填充背景颜色突出显示。

快速求和

只需按住快捷键组合【Alt+=】即可实现快速求和。

并排查看两个工作簿

如果要同时查看两个工作簿，可以先打开两个工作簿，然后点击 “视图→并排窗口”。

快速删除值保留公式

一般情况下我们可以通过【Ctrl+G】定位条件设置来完成，其实还可以直接选取非公式值：开始 - 查找和选择 - 常量 - 删除。

上面这26个小技巧，都是我们在Excel使用过程中经常要用到的，操作很简单，但非常实用，如果你能熟练掌握这些技巧，能解决工作中绝大部分的问题。

职场人必备的26个Excel工作技巧|字符串|工作簿|工作表

相关推荐