今天整理了4类22个Excel硬核技巧,有函数公式,有快捷键操作,也有单元格设置代码,总之,都是工作中最常用的,掌握了你就是公司的表哥。

数据录入类

01.快速填充空单元格内容

当表格中多个空行需要录入的内容与上一行相同时,有没有快速录入的方法?

解决方法:选中数据,按Ctrl+G键打开定位窗口,依次选择【定位条件】-【空值】-【确定】,然后输入等号,选择上一个单元格,最后按Ctrl+回车键搞定。

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(1)

02.输入带圈的数字

有小伙伴喜欢在序号上画个圈,Excel中能给数字加圈吗?

解决方法:在目标单元格中输入公式:=UNICHAR(ROW(A9312)),就是带圈的①,需要其他带圈的序号下拉填充公式即可。

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(2)

03.输入带方框或圈的对号和错号

选中需要输入的区域,设置字体为【Windings2】,分别输入大写的O、P、R、S、U就是对应的对号和错号,如下图:

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(3)

04.利用列表选择录入内容

当表格一列中录入的内容有重复时,可以利用Alt+↓(下方向键)来完成。

在目标单元格中按下Alt+↓,出现下拉列表,也就是上面录入的内容,可直接选择输入数据,演示如下图:

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(4)

05.固定长度编号录入

表格中编号是多位数字组成时,长短不一致,影响表格美观,如何解决?

解决方法:选中编号单元格区域,按【Ctrl+1】打【设置单元格格式】窗口,选择【自定义】;,在类型文本框内输入【00000】,点击【确定】,编号前在用0补齐了。

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(5)

求和、计算类

06.快速行、列求和

同时选中数据区域和求和结果存放区域,按Alt+ = 键,快速完成行、列同时求和。

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(6)

07.快速小计求和

表格中销售数据按部门进行了分类,如何按部门小计求和呢?

解决方法:选中需要小计求和的目标单元格;按【Ctrl+G】键,打开定位窗口,依次选择【定位条件】-【空值】-【确定】,最后按【Alt+=】键即可完成快速小计求和。

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(7)

08.合并单元格求和

表格中按部门合并了单元格,合计数如何也要按部门进行汇总求和呢》

解决方法:首先选中所有合并单元格,然后输入公式:=SUM(C2:C17)-SUM(D3:D17) ,输入完成后按Ctrl+回车键确认公式,即可得到每个店的合计数

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(8)

09.带单位的数值求和

表格中数据后面加上了单位:万、百万,如何进行求和呢》

解决方法:选中C列,按Ctrl+H键,调出查找和替换窗口,在查找内容输入:百万,替换为输入:E+06,点击【全部替换】按钮

重复上面操作, 查找内容输入:万,替换为输入:E+04,点击【全部替换】按钮

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(9)

替换后数据变成了带E+的科学计数格式,再设置单元格格式为【常规】或【数字】,再用公式或快捷键求和即可

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(10)

10.快速计算选手得分

比赛中记分规则一般是去掉一个最高分和一个最低分,再计算平均分,用Excel设置好记分模板,输入完分数后就可以计算出最终得分,是不是很快呢?

最高分公式:=MAX(C3:H3);

最低分公式:=MIN(C3:H3);

最终得分公式:=TRIMMEAN(C3:H3,2/COUNTA(C3:H3))

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(11)

操作技巧类

11.快速对比两列数据

选中两列数据→【Ctrl+\】→【开始】→【填充色】填充一个颜色突出显示差异数据

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(12)

12.阿拉伯数字转换大写数字

选中需要转换的数字区域,按Ctrl+1键调出设置单元格格式窗口,在【数字】页面,分类选择【特殊】,类型列表中选择【中文小写数字】,就转换成中文小写,选择【中文大写数字】,就转换成中文大写。

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(13)

13.智能提取、合并数据

Excel在2013版本以后,出现 了一个神奇的快捷键Ctrl+E,可以拆分数据,可以合并数据,还可以提取数据等等。更多Ctrl+E键教程文末附链接。

合并数据:在C2单元格中把第一个姓名和手机号分别复制-粘贴过来,再选中C3单元格,按Ctrl+E键,完成整列姓名和手机号的合并,动图演示如下:

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(14)

14.隐藏数据

当表格中有的数据我们不想展示时,怎么办呢?

解决方法:选中数据区域,按Ctrl+1键调出设置窗口;在数据页面,分类选择【自定义】,类型框中输入;;; 最后点击【确定】。数据就隐藏了。

注意:输入的是三个英文的分号

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(15)

15.分段显示手机号

首先选中所有手机号码,按Ctrl+1键调出设置单元格格式窗口;

数字页面中分类选择【自定义】,右边的类型框中输入000-0000-000,点击【确定】手机号就分段显示了。

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(16)

16.不同颜色突出显示数值

成绩表中不及格的显示红色,及格的显示蓝色

解决方法:选中成绩→鼠标右键→【设置单元格格式】→【自定义】→在类型框中输入:[蓝色][>=60];[红色][<60]

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(17)

17.上、下箭头显示数据涨、跌情况

两个月的销售业绩对比,用上、下箭头表示涨跌情况,一目了然。

选中数据,按Ctrl+1键打开设置单元格格式窗口,数字页面中分类选择【自定义】,类型框中输入代码:[红色]↑0%;[绿色]↓0%;0%

最后点击【确定】

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(18)

日期、时间类

18.日期转化为星期

选中日期,按Ctrl+1键调出设置窗口,【自定义】-【类型】框中输入aaaa,确定后就自动显示星期几了。

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(19)

19.计算两个日期之间的工作日天数

计算2020年6月的工作日天数,除去周末,还有一个端午节,在D2单元格输入公式:=NETWORKDAYS(A2,B2,C2:C7) 即可得出工作日天数。

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(20)

20.设计倒计时牌

想到知道离情人节还有多少天,两个日期相减即可得出还有多少天。

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(21)

21.快速填充工作日日期

一般下拉填充日期时,日期是连续的,如何只填充工作日呢?

解决方法:先下拉填充日期,完成后在右下角出现一个【自动填充选项】按钮,点击后选择【填充工作日】即可。

excel教程技巧大全(工作中最常用的22个Excel操作技巧)(22)

22.快速录入当前日期和时间

Ctrl+;键是当前日期

Ctrl+Shift+;键是当前时间