导语
在工作中,你有这样的经历吗?
面对大量数据,只会简单地计算加、减、乘、除,好尴尬!
本来1小时内就可以完成的数据整理,却要加班才能完成, 好心塞!
花费很多精力呈现出来的数据图表,Leader却不认可,好无奈!
......
俗话说:工欲善其事,必先利其器。鹅厂达人聂春霞拥有12年丰富的数据分析经验,并担当了6年腾讯学院Excel课程讲师,总结提炼出7招Excel独门技巧,来帮助大家提升工作效率,事半功倍,让你整理数据毫不费力哦~
同时,在文章末尾,还有来自作者的独家好礼,千万不要错过哦~
第1招
如何快速把数据的单位从元转换为万元
Leader
小A,明天要向老板汇报工作了,你今天把最近的业务收入数据整理下给我。
好的!
小A
于是小A吭哧吭哧做了半天,表格终于弄好了,发给leader,满心等待leader的认可。
表格部分内容如表1:
3001414.3
2340865.6
60517.01
3417015.3
1113500
164809.91
1963434.6
1357088.8
57336.75
3651511.1
2873985.5
161384.73
2589910.7
1386226.3
45124.23
2903621.7
1819778.7
97855.85
3930069.1
3179008.7
99105.4
- 表1-
Leader
小A,你这些数据都是带有小数点的,老板只会关心收入的数量级呀,请你把这些数据单位转换为万元吧。
把以“元”为单位的数据转换为“万元”为单位的数据,要用公式,复制再选择性粘帖。
小A
Leader
不需要公式也可以。你在数据区域旁边任意一个空白单元格输入10000,选中复制,然后选择性粘贴,选择运算的“除”,就得到想要的结果,如图1:
- 图1-
哦,原来把数据缩小一定的倍数还可以这样做啊,太方便了。
小A
第2招
如何批量输入求和公式
Leader
小A,你把最近几个产品的上半年数据汇总下。
有好多产品呢,一个个求和需要点时间。
小A
Leader
你可以批量将需要求和的单元格输入公式,一个个求和太慢了。
- 图2-
如图2,你先选中要求和的第一个区域,按住ctrl键,再选中其他要求和的区域,按【alt+=】就可以批量求和。
注意:如果要求和的数据没有空值,如图3:
- 图3-
全部选中数据,按F5快捷键,定位条件选择空值,这样可以快速选中要求和的单元格,再按【alt+=】。
真是大开眼界,原来求和公式可以批量输入!
小A
第3招
文本与数字格式的相互批量转换
大神,请教一个问题,我这个数字为啥不能求和呢?
小A
Excel达人小B
你这个数据格式是文本格式,只能计数,不能做求和等运算,需要转换为数字格式才可以。
那要怎样转换呢?
小A
Excel达人小B
如果只是一个单元格文本转换为数字,只需删掉单元格数据前面的单引号,如果是一列或多列文本数据转换为数字格式,最简单的方法用【Ctrl + Shift +向下键↓】,再用【Ctrl + Shift +向右键→】选中需要转换为数字格式的数据区域,点击下拉框转换为数字。如图4:
- 图4-
文本转换为数字还有一种常用的方法,就是把文本数据乘以1,除以1或者+0,-0,如A列是文本格式的数据,B列通过乘以1得到数字格式的数据。
那如果我要把数字格式转换为文本呢?比如,身份证号码录入需要文本格式。
小A
Excel达人小B
数字格式要转换为文本格式,如果只有一个单元格,在数据前面加单引号,记住,一定是英文状态下的单引号。如果要把一列数据由数字格式转换为文本格式,用分列功能实现,前两步选择默认的选择项,第三步把默认的“常规”改为“文本”。如图5到7。
- 图5-
- 图6-
- 图7-
点击“完成”即可。
如果要录入身份证号码,需要先把单元格格式设置为文本格式再录入。
明白了, 又学到了一招。
小A
第4招
利用数据验证防止重复录入相同的内容
我要录入数据,有时候不小心重复录入了,怎样避免重复录入呢?
小A
Excel达人小B
可以用数据验证定义,如图8:
- 图8-
这样,如果数据重复录入了就会提示图9错误:
- 图9-
数据验证原来还有这个功能。
小A
第5招
对齐两个字的名字
Leader
小A,你打印的这份名单名字没有对齐,不美观,再改改吧。
姓名为2个字的我在中间敲个空格吧。
小A
Leader
不用了,用分散对齐就可以。选中需要设置对齐的单元格区域,按【Ctrl+1】设置单元格格式,点击对齐→水平对齐→分散对齐(缩进)即可,如图10:
- 图10-
第6招
将单元格数字变成条形图
Leader
你这个数据表数据没有问题,但感觉不够直观。
我怎么在单元格既显示数字又显示图形呢?是不是要制作图表呢?
小A
Leader
用数据条可以清晰看到数据的大小,不需要通过制作图表就能看出来,如图11:
- 图11-
如果只想显示数据条,不显示数字,在编辑格式规则时在“仅显示数据条”前面方框内打勾,如图12。
- 图12-
第7招
神奇的快速填充
正如每个人都有不同的脾气一样,Excel单元格也有它的“脾气”,在数据录入的时候需要了解单元格的特点,数据录入要规范,如果不规范,后续数据统计和数据分析会非常麻烦。经常有人问到因为录入的数据不规范,要提取数字和字符串,用公式比较复杂,难以理解,如果你的Excel版本是2013或以上版本,用快速填充功能秒秒钟就可以搞定。这个功能智能到让你惊叹,强大到足以让分列功能和文本函数下岗,看完下面几个案例就能体会到。
1、提取数字和字符串
我之前录入的数据数字和单位放在一起了,现在要统计汇总,怎么办呀?
如果要将图13中的字符串中的数字提取出来,由于原数据缺乏规律,无法使用Left、Right、Mid、Find等文本函数来提取。一个个输入数据也不现实呀。
小A
Excel达人小B
使用“快速填充”功能分分钟搞定。
- 图13-
复制A2单元格的“手机”,粘帖到B2单元格,按快捷键【Ctrl+E】,或者点击菜单开始→填充→快速填充,如图14,这样就可以把A列左边的文字提取出来。提取数字方法类似,在C2单元格输入A2单元格中的单位为元的数字5400,C3输入15000,再按快捷键【Ctrl+E】,其他单元格单位为元的数字就全部提取出来。在D2输入2,再按快捷键【Ctrl+E】,A列中最后的一个数字也提取出来了。
Excel达人小B
如果输入一个单元格数字无法正确填充,就再输入一个单元格数字,根据两次输入的数字,快速填充就读懂你的意思了。这好比你和别人解释某个问题,解释一遍人家没有明白,再解释一遍就明白了。
- 图14-
哇塞,快速填充真是太神奇了。
小A
2、提取身份证的出生日期
我要把我们部门员工出生日期找出来,除了用函数还有什么更简单的方法吗?
小A
Excel达人小B
要把图15中A列身份证的出生日期提取出来,用函数和分列都可以实现,用快速填充更快,先设置B列单元格格式为日期格式,在B1输入A1的出生日期1982-12-05,按快捷键【Ctrl+E】就可以迅速填充所有A列身份证的出生日期。
- 图15-
3、多列合并
我需要把两列内容合并,除了函数还有什么更快的方法吗?
小A
Excel达人小B
要把图16的A列和B列合并,通常用&连接,只要在C1单元格输入A1和B1的内容,按快捷键【Ctrl+E】就可以快速合并。
- 图16-
4、向字符串中添加字符
我需要把公司的座机号码总计和分机分隔开,在数字中间一个个添加-好麻烦呀。要把图17中A列的电话号码区号、总机、分机号码用-隔开。
小A
Excel达人小B
把A1,A2内容复制粘帖到B1和B2单元格,并在区号、总机、分机之间添加分隔符-,在B3单元格按快捷键【Ctrl+E】就可以快速填充A列其他单元格的内容。
- 图17-
需要提醒的是,如果只是在B1单元格输入对应A1的内容,在B2单元格按快捷键【Ctrl+E】得到的都是B1的内容,这里需要输入2次快速填充才能理解你的意图。
5、快速填充功能组合
我想将两列单元格的不同内容合并起来。例如提取图18中省市中的市,提取街道中的号码,将两者合并为新的地址。
小A
Excel达人小B
可以利用“快速填充”一步到位解决这一问题。
- 图18-
在C1单元格输入成都198,按【Ctrl+E】得到的默认是A列的城市名称和B列的数字。
6、调整字符串的顺序
要把A列的中英文互换位置,一个个复制太慢了。
小A
Excel达人小B
在B1输入A1的互换内容,在B2按【Ctrl+E】就可以快速填充A列其他单元格需要互换位置的内容,如图19。
- 图19-
7、大小写的转换
A列是大写字母,需要在B列转换为小写,C列首字母大写,其他小写。
小A
Excel达人小B
只需要在B1和C1输入相应的内容,按按【Ctrl+E】就可以把A列其他单元格内容批量转换,如图20。
- 图20-
快速填充就像“知心姐姐”,真是太懂我们的意思了,这个功能真是太贴心了。
小A
联系客服