10个EXCEL小技巧 帮你提高工作效率

原创 精选评论501字数 1505

10个EXCEL常用小技巧,话不多说,为了我们的工作效率,直接开始。

1. 手机号码自动加“-”

  做法:通过单元格格式,修改显示格式文章源自合一学院-https://unvmax.com/10%e4%b8%aaexcel%e5%b0%8f%e6%8a%80%e5%b7%a7-%e5%b8%ae%e4%bd%a0%e6%8f%90%e9%ab%98%e5%b7%a5%e4%bd%9c%e6%95%88%e7%8e%87.html

  右击手机号码字段,按下Ctrl+1进入单元格格式面板,点击最下方自定义,输入000-0000-0000。确定之后,手机号码即以XXX-XXXX-XXXX格式显示。文章源自合一学院-https://unvmax.com/10%e4%b8%aaexcel%e5%b0%8f%e6%8a%80%e5%b7%a7-%e5%b8%ae%e4%bd%a0%e6%8f%90%e9%ab%98%e5%b7%a5%e4%bd%9c%e6%95%88%e7%8e%87.html

  注:这种方法只是修改了字段显示样式,并没有修改实际数据,因此后续仍然可以进行统计,这也是和Replace()函数最大一个区别。文章源自合一学院-https://unvmax.com/10%e4%b8%aaexcel%e5%b0%8f%e6%8a%80%e5%b7%a7-%e5%b8%ae%e4%bd%a0%e6%8f%90%e9%ab%98%e5%b7%a5%e4%bd%9c%e6%95%88%e7%8e%87.html

Excel技巧 Excel公式 Excel函数

2. 合同到期前自动提醒

  公式:=IF(G7-TODAY()<30,合同应续签,)文章源自合一学院-https://unvmax.com/10%e4%b8%aaexcel%e5%b0%8f%e6%8a%80%e5%b7%a7-%e5%b8%ae%e4%bd%a0%e6%8f%90%e9%ab%98%e5%b7%a5%e4%bd%9c%e6%95%88%e7%8e%87.html

  首先建立状态列,输入公式=IF(G7-TODAY()<30,合同应续签,)。当合同到期日低于设定值(本例中为30)时,便自动显示合同应续签。此外也可将提醒天数单独设成单元格,通过公式调取单元格数值,以方便用户自行修改提醒期限。文章源自合一学院-https://unvmax.com/10%e4%b8%aaexcel%e5%b0%8f%e6%8a%80%e5%b7%a7-%e5%b8%ae%e4%bd%a0%e6%8f%90%e9%ab%98%e5%b7%a5%e4%bd%9c%e6%95%88%e7%8e%87.html

Excel技巧 Excel公式 Excel函数

3. 避免录入重复值

  公式:=COUNTIF(B:B,B1)=1文章源自合一学院-https://unvmax.com/10%e4%b8%aaexcel%e5%b0%8f%e6%8a%80%e5%b7%a7-%e5%b8%ae%e4%bd%a0%e6%8f%90%e9%ab%98%e5%b7%a5%e4%bd%9c%e6%95%88%e7%8e%87.html

  选中数据列,点击数据→数据工具→数据验证,将验证条件改为自定义,然后输入公式=COUNTIF(B:B,B1)=1。这里COUNTIF()函数的作用,是判断B列中是否有重复记录(如果有则值值一定大于1),如果有中止录入,如果没有继续录入。文章源自合一学院-https://unvmax.com/10%e4%b8%aaexcel%e5%b0%8f%e6%8a%80%e5%b7%a7-%e5%b8%ae%e4%bd%a0%e6%8f%90%e9%ab%98%e5%b7%a5%e4%bd%9c%e6%95%88%e7%8e%87.html

Excel技巧 Excel公式 Excel函数


如果录入时出现重复数值将禁止录入文章源自合一学院-https://unvmax.com/10%e4%b8%aaexcel%e5%b0%8f%e6%8a%80%e5%b7%a7-%e5%b8%ae%e4%bd%a0%e6%8f%90%e9%ab%98%e5%b7%a5%e4%bd%9c%e6%95%88%e7%8e%87.html

4. 自动标亮周末

  公式:=WEEKDAY(E$6,2)>5文章源自合一学院-https://unvmax.com/10%e4%b8%aaexcel%e5%b0%8f%e6%8a%80%e5%b7%a7-%e5%b8%ae%e4%bd%a0%e6%8f%90%e9%ab%98%e5%b7%a5%e4%bd%9c%e6%95%88%e7%8e%87.html

  首先建立星期行,选中考勤表里的数值区域,点击开始→样式→条件格式→新建规则,然后在选框内输入公式=WEEKDAY(E6,2)>5,最后选中E6,按两下F4键锁定行不锁定列(即转换成E$6),再设置一个特别的颜色即可。这里WEEKDAY()函数的作用是判断当前列是否大于5,当WEEKDAY()结果>5时(即周六、周日),条件成立,表格自动为该列刷上颜色,不成立时不处理,从而最终形成周末自动上色效果。文章源自合一学院-https://unvmax.com/10%e4%b8%aaexcel%e5%b0%8f%e6%8a%80%e5%b7%a7-%e5%b8%ae%e4%bd%a0%e6%8f%90%e9%ab%98%e5%b7%a5%e4%bd%9c%e6%95%88%e7%8e%87.html

Excel技巧 Excel公式 Excel函数


自动标亮周末

5. 让表格自动拓展

  方法:Ctrl+T转成超级表

  想让表格自动扩展,可以利用Excel里的超级表功能。具体方法是:选中数据区域,按下Ctrl+T,然后修改表格工具→表格样式为无(即不使用超级表默认样式)。右击隐藏新弹出的筛选行,即可实现表格的自动扩展。

Excel技巧 Excel公式 Excel函数

6. 隔行换色

  公式:=MOD(ROW(),2)=0

  隔行换色有两种方法,一是转换成超级表(Ctrl+T),二是借助公式与条件格式配合完成。以公式法为例,首先选中数据区域,点击开始→样式→条件格式→新建规格→使用公式确定要设置格式的单元格,然后在选框内输入公式=MOD(ROW(),2)=0。这里ROW()函数的作用是获取当前行号,和2取余后,便得到了1、0、1、0、1、0……这样的数列。由于公式整体位于条件格式内,因此当行号取余结果=0时,条件成立,表格自动为该行刷上颜色,不成立时不处理,最终形成了隔行换色的效果。

Excel技巧 Excel公式 Excel函数


公式+条件格式实现隔行换色

  注:与传统的格式刷法相比,超级表和公式法都可以实现周末自动填色。且后期无论如何添删,都不会导致色条出现混乱。

7. 自动标记迟到、早退

  公式:=IF(B2>TIME(8,0,0),迟到,) 和 =IF(C2

  首先在表格后建立一个迟到列,输入公式=IF(B2>TIME(8,0,0),迟到,),再建立一个早退列,输入公式=IF(C2

8. 自动打序号

  公式:=ROW()-1

  首先点击序号列第一组单元格,输入公式=ROW(),此时函数返回的是当前行数,根据实际行数计算(比如本文为1),发现两者差值为1。接下来对公式进行修改,将计算后的差值减到公式后方(),填充后即可实现自动打序号效果。

  尽管自动打序号已经实现,但此时仍然不够智能。可以在此基础上再嵌套一个IF()函数,只有当右侧有数据时才会显示序号,没有的话直接显示空白。

Excel技巧 Excel公式 Excel函数

9. 去除数据有效性列表里的空值

  公式:=OFFSET($O$6,,,COUNTA($O$6:$O$19))

  如果你经常使用数据有效性制作下拉列表,就会发现这样一个尴尬,当我们前期为序列留出大量空白时,下拉列表也会出现大量空白,日常操作很不方便。其实这个问题可以这样解决,首先点击数据→数据验证→序列,在来源框中填入公式。这里COUNTA()函数的作用,是求出当前数据源中的有效记录数,然后通过OFFSET()函数确定取值范围。由于公式限定了下拉列表的取值域,因此我们会得到一个完全没有空值的菜单。同时新记录也将自动添加到列表中,不会影响后续操作。

Excel技巧 Excel公式 Excel函数

10. 分级考核统计

  公式:=LOOKUP(J6,N6:N9,M6:M9)

  传统分级统计是通过COUNT()函数与IF()函数配合计算,虽然简单,可一旦条件过多,就会导致公式异常复杂。类似情况,其实也能借助LOOKUP()函数解决。

  首先建立一个分级副表,左侧为等级,右侧为达到该等级的考核线。接下来在评级框内输入公式=LOOKUP(J6,N6:N9,M6:M9),将两组取值域按F4键全部转为绝对地址。这里LOOKUP()的作用是通过目标值,直接到副表中查找对应等级,相比IF()函数显得精简很多。而且这样处理后的表格,也方便用户随时调整考核线。

Excel技巧 Excel公式 Excel函数

注:使用LOOKUP()函数时,要注意副表考核值必须由小到大排列,否则将导致结果异常。

匿名

发表评论

匿名网友
:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:
确定

拖动滑块以完成验证