【Excel】快速填充的应用
彭怀文
【快速填充】功能是Excel2013版本以上新增的一种功能,善用该功能可以提高工作效率。
作用:基于示例填充数据。它通常在识别数据中的某种模式后开始运行,当您的数据具有某种一致性时效果最佳。先看一下的示例:图4-3-1
假如A列中有一列数据,数值中既有字母、符号等,我们只想取其中的阿拉伯数字。由于字母与符号等以及数字都是不具有规律性,使用【替换】功能也不能达到目的。在Excel低版本时,通常情况下,会使用函数来解决,但是比较繁琐。
在Excel2013版本以上中新增的【快速填充】功能就非常的简单方便。比如图4-3-1中,只需要在单元格B1中输入数字112,然后点击【数据工具】→【快速填充】,就能快速填充下方的数字。
【备注】快速填充的快捷键为Ctrl+E。
![2f41ce79da73e0d6e84fe8ceb92b688b.png](https://i-blog.csdnimg.cn/blog_migrate/1605fb2c0f49678f5cd5731abbc2bf13.jpeg)
图4-3-1 快速填充的应用示意图
看到没,她能模拟、识别你的操作,推测你内心的想法,然后按照你的想法进行数据填充。可以说,用懂你、神奇、智能描绘她都不为过。
![40a8a37023cd38ce30cb98a8b6b3e7d2.png](https://i-blog.csdnimg.cn/blog_migrate/f75cd5e417d88959f02a3713326221ea.jpeg)
(一)提取功能
1.提取数字或文本等
如图4-3-1,由于原数据缺乏规律,无法使用LEFT、RIGHT、MID、FIND等文本函数来提取。使用"快速填充"功能则分分钟搞定。
比如我们前面用【分列】功能的常规应用中图4-2-1,如果使用【快速填充】功能也一样可以完成,读者可是动手一试。
2.提取数据中的出生日期
比如需要提取身份证号码中的出生日期,在以往的Excel版本中需要使用函数公式来提取。
![06c993be31c99c57c7b598f9050cc442.png](https://i-blog.csdnimg.cn/blog_migrate/7586deff2f7105169ec1cf3e6cc7d4ee.jpeg)
图4-3-2 利用快速填充提取身份证号码中的生日示意图
步骤:
2.1提取日期前,需要对日期格式进行设置。步骤:【设置单元格格式】→【数字】→【自定义】,在右侧的"类型"选择"yyyy/mm/dd",确认之后关闭对话框。
2.2在需要填充的单元格B2输入A2包含的生日日期,敲回车键,然后点击【快速填充】或使用快捷键Ctrl+E就可以了。
通过上述的简单步骤,就能得到图4-3-2中的提取效果,远比使用函数公式来的快捷。
【注意】有时身份证号码中有些相同数字容易造成Excel的判断错误,比如遇到生日中有月份数和日数相同的,比如1月1日、2月2日等,也有生日数字与前面的地区编码相同的数字段等,在只输入第一个生日日期后就使用快速填充就可能得出错误的结果。
这时,只需要撤销后再多输入一个或两个生日号码,Excel就能明白您想做什么了,然后再使用【快速填充】功能,就能得出正确的结果。
(二)合并功能
1.将已有的多个数据列合并为一列
例如,将省(直辖市、自治区)、市(州)、县(区)合并为同一个当单元格内,作为地址。在第一行中输入三个合并后的结果,告诉Excel你要做什么之后,她就会按照你的想法完成剩下的工作。
![790f6430e441d113cf9513a4f08f141b.png](https://i-blog.csdnimg.cn/blog_migrate/94b55e39a2e16e4a817558e5c9dcd04f.jpeg)
图4-3-3 利用快速填充合并意图
步骤:只需要在需要填充的单元格D2输入前面三列合并的文本,敲回车建;然后点击【快速填充】或使用快捷键Ctrl+E就可以了。
2.向字符串中添加字符
有时我们需要向已知的字符串中添加字符,比如分隔符"-",比如"/"等等。
如果是添加一个字符的话,使用函数效率还行,但是添加两个以上的字符,使用函数与快速填充相比,简直是龟速。
例如需要将所有的日期,如"20201006",变更为标准日期的格式"2020-10-06"或"2020/10/06"。
![a902b253c58f11c7ffb4c2c517bb3854.png](https://i-blog.csdnimg.cn/blog_migrate/58085e36441458927190f64110bafcd2.jpeg)
图4-3-4 利用快速填充添加字符意图
步骤:
2.1在B列和C列先通过【设置单元格格式】,设置为"yyyy/mm/dd"类型的格式。
2.2在单元格B2和B3手动输入"1982-03-15"和"2020-10-02" ;然后点击【快速填充】或使用快捷键Ctrl+E就可以了。
2.3在C列重复上述2.2的操作即可。
【备注】有时根据数据的特点,需要输入两个甚至三个,Excel快速填充才能正确理解你的意图。
3.快速填充功能组合(提取合并一步到位)
"快速填充"功能不仅可以实现批量提取的效果,而且在提取的同时还可以将两列单元格的不同内容合并起来。
假如我们已有的数据中有"姓名"和"身份证号码",我们现在需要"姓名+出生年"的组合,就可以使用【快速填充】功能来快速实现,如图4-3-5:
![1be10a8ed1a1df16f69a777cb1830aec.png](https://i-blog.csdnimg.cn/blog_migrate/b228aa259da3384b62042f8e51722575.jpeg)
图4-3-5 利用快速填充提取合并一步到位示意图
具体步骤,跟前面几个类似,不赘述。
![fe41cd9f267578b3cadbd84bf5db34a0.png](https://i-blog.csdnimg.cn/blog_migrate/1e8fc508529ddb8b65fd296ccb87350b.jpeg)
(三)扩展应用
1.整理非规范的时间数据
对Excel不熟悉的人,经常是把Excel表格当成word在使用,所以输入的时间格式经常是一些非规范的时间格式,比如输入了"19.10.06"这样的日期格式。这样的格式,在Excel中不被认为是日期(数据),而是文本,是不能参与日期计算的,也不同通过设置格式转化成日期。
![3a21c2e2b221885271e963370fd1ca6f.png](https://i-blog.csdnimg.cn/blog_migrate/b01f7db531cf1dd419e4ad5d54274fe8.jpeg)
图4-3-6 利用快速填充整理不规范时间示意图
步骤:
1.1先在B列通过【设置单元格格式】,设置为"yyyy/mm/dd"类型的格式。
1.2通过观察,发现"非规范时间"列中有两种不同格式,所以需要先在单元格B2和B3分别手动输入规范的时间,然后点击【快速填充】或使用快捷键Ctrl+E就可以了。
【备注】:
通过笔者测试,"非规范时间"列中不同格式的最多只能是两种,否则Excel也无法判断了。
由于表示年分数只有两位数,Excel默认的是在数字前面加上统一的数字,所以"99.10.15"这样的数字也会自动变成"2099/10/15",而不是您可能希望的"1999/10/15"。
2.整理不规范数字
由网站或其他软件等导出数据,经常是不是纯粹的Excel格式文件,而是后缀为".CSV"等格式的,虽然显示很像Excel格式,但是数据中经常包含着不可见字符,导致其数据无法直接参与计算。因此,对于含有不可见字符的源数据需要先清理,删除其不可见字符。
传统的做法在本书第三章第二节"不规范的基础表格的规范化整理"中有讲解,此处不赘述。
![edf711c85b9952bf5c1df5b87f414490.png](https://i-blog.csdnimg.cn/blog_migrate/953cfd1705a49c1d161b00092875ca6c.png)
图4-3-7 利用快速填充整理数据示意图
使用【快速填充】,可以在B2单元格输入规范的数字"100234.05",教会Excel如何转化不规则数据,所有数据立马变得规范并可以用于计算了。
3.调整字符串的顺序
在某些场合,需要中英文的姓名,比如将姓名"成都 CHENGDU"。有时要求中文名在前面,有时要求英文名在前面,如果调整互换往往需要使用复杂的公式才能实现。
借助"快速填充"功能可以快速调整字符串的顺序。
![117a1b3c2e70a66b3ab8753935e15903.png](https://i-blog.csdnimg.cn/blog_migrate/2e3a230a69332e63015b562be06cda7d.jpeg)
图4-3-8 利用快速填充调换字符串的顺序示意图
可以在B2单元格输入"CHENGDU 成都",教会Excel如何调整字符串的顺序,使用【快速填充】功能就能将剩下的文本字符串顺序全部调整。如果把"CHENGDU 成都"更换成"Chengdu 成都",后面所有的都会变更成相同格式,即首字母是大写字母其余字母是小写字母。
![c1dc4d620c41ce371a6407c94479d62b.png](https://i-blog.csdnimg.cn/blog_migrate/0326d00516283d65557987d1caca3911.jpeg)
![54d88c7571e21ed9fa0539fbc779970c.png](https://i-blog.csdnimg.cn/blog_migrate/55b1af04d94b0d0ecfa2bbec0af1f504.jpeg)
![e3782e58cc722f07f5f3baab9e93be55.png](https://i-blog.csdnimg.cn/blog_migrate/3b5cb4331b307dff9eda4de6f2dc88ac.jpeg)