🔥 热搜 🔥
上海习近平新疆鄂州父女瓜乌鲁木齐疫情H工口小学生赛高习明泽芊川一笑图包印尼排华
分类
社会娱乐国际人权科技经济其它
🔥 热搜 🔥
上海习近平新疆鄂州父女瓜乌鲁木齐疫情H工口小学生赛高习明泽芊川一笑图包印尼排华
分类
社会娱乐国际人权科技经济其它
查看原文
其他

牛X！这才是真正的表格整理技巧，谁写的，太精辟！

明镜在心 秋叶Excel 2022-06-30
收录于合集 #和高手一起学Excel 414个
点击蓝字【秋叶 Excel】👆
发送【交流】
立即进秋叶 Excel 读者快乐交流群！

本文作者：明镜在心
本文编辑：雅梨子


嗨，小伙伴们你们好呀！我是明镜在心。

我们前段时间发布的一篇《用公式太难，手动太慢，这才是最牛 x 的 Excel 整理工具！》文章中，内容是将如下的表一格式转换成表二的格式。

表一：


表二：


之前介绍的方法是，用 Power Query 进行转换。

但是，我在后台看见有很多小伙伴们留言觉得 Power Query 操作起来还是挺复杂的，还有些小伙伴们使用的是 WPS，没有 Power Query 这个功能……

怎么办呢？

今天我就给大家介绍下另一种适合大多数人的办法——基础功能+函数，下面就跟我一起来操作下吧！
数据替换


这一步的目的是将其中的顿号（、）替换为换行符。

按【Ctrl+H】，调出【查找和替换】对话框，在【查找内容】中输入顿号（、），在【替换为】中按【Ctrl+J】键（此为换行符，是一个看不见的符号），


最后点击【全部替换】，如下图：


数据转换


这一步需要借助 Word 这个好兄弟来帮忙转换下数据了，目的是把原来在一个单元格中分行显示的数据拆分为多个单元格显示！

新建一个空白的 Word 文档，选中【A1:B7】单元格数据，按【Ctrl+C】复制，之后在文档中按【Ctrl+V】粘贴进去，


再选中 Word 中这些数据，按【Ctrl+C】复制到 Excel 中一张空白表的【A1】单元格中，

鼠标【右键】选中其中的粘贴选项【匹配目标格式】。


效果如下图：


这样我们就把在一个单元格里面显示的名字分为多个单元格存放了。

数据填充


这一步是将【A 列】的空白单元格填充为它的上一个单元格的内容。

先选中【A 列】，之后选择【开始选项卡】下面的【查找和选择】中的【定位条件】功能。


在【定位条件】对话框中，选中【空值】，


此时将把【A 列】中的已使用区域中空白单元格全部选中。


在【编辑栏】中输入等于（=）之后，输入【A2】或者按向上箭头（↑），


最后按【Ctrl+Enter】批量填充数据。

效果如下图：


删除重复值


这一步是将有重复出现的人名删除掉，只保留唯一人名。

将【B 列】的数据复制到【D 列】，然后，在【数据选项卡】中调出【删除重复值】功能。

 
保持其中的选项，直接点【确定】。

 
效果如下图：


使用函数返回结果


这一步使用函数来返回我们最终需要的结果。

在【E1】单元格输入如下公式，即可返回想要的结果!


=TEXTJOIN("、",1,IF($B$2:$B$14=D2,$A$2:$A$14,""))
▲左右滑动查看

公式大概的意思是：
 
先用 IF 函数进行判断，如果【B2:B14】区域的值等于【D2】单元格中人名的话，就返回【A2:A14】区域中的值，否则就返回空文本（「」），最后用 Textjoin 函数把符合条件的值用分隔符（顿号、）进行相连。

函数解析如下：

TEXTJOIN 函数将多个区域或字符串的文本组合起来，并包括你在要组合的各文本值之间指定的分隔符。

语法为：

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
 
delimiter 第一参数为：分隔符。 ignore_empty 第二参数如果为 TRUE，则忽略空白单元格，如果为 false 则不忽略空白单元格。 Text1 text2... 从第三参数开始为：要联接的文本项。

PS. 此函数在高版本的 WPS 中也可以使用噢！而且 WPS 和微软的 OFFICE 可以同时装在同一台电脑上。
知识扩展


有时候，我们的数据是下面这样：


人员名单是分单元格存放的，并不是写在同一个单元格中。
 
这种情况如何处理呢？
 
像这样的数据是一个类型的二维数据。
 
当然，我们可以先把多个单元格的人名用分隔符连接在一个单元格中，然后使用上面的方法来处理。
 
另外：我们也可以使用数据透视表将二维数据转换成的一维数据。

👉 具体的操作方法如下：
 
❶ 选中数据区域中任一单元格，比如【A1】单元格，然后按【ALT+D+P】，调出【数据透视表和数据透视图向导】，


❷ 选中【多重合并计算数据区域】，点击【下一步】。


保持【创建单页字段】选项不变，继续点击【下一步】。

❸ 在【第 2b 步】中，点击【选定区域】之后选择【A1：D7】区域，之后点击【添加】，再点击【下一步】，


❹ 选择【新工作表】来放置数据透视表，最后点击【完成】


此时数据透视表将放在一张新工作表中显示出来。

如下图：


❺ 双击【行总计】与【列总计】交叉的单元格【E11】单元格，此时将会创建一张新工作表来显示数据。
 
如下图：


这个数据表基本就是我们需要的一维表格了。
 
将第三列中的筛选（空白）去掉，点击【确定】。


最后的数据就是我们需要的一维表格了。


将其复制到一张新工作表的【A1】单元格，并将中的【B 列】和【D 列】删除。


最后我们就可以按照上面的方法操作下就 OK 啦!

总结


今天，我们学习了各种常用技巧结合函数来处理数据的综合实战方法。
 
❶ 查找替换数据
❷ 数据转换。
❸ 数据填充。
❹ 删除重复值。
❺ 使用 Textjoin 函数返回结果。
❻ 利用数据透视表将二维表格转换为一维表格的另类应用。
 
这些技巧单个使用都很简单，如果把它们结合在一起使用，那就要一定的实战基础了。

在处理不规范数据时经常会使用到它们，小伙伴一定要熟练运用噢！

如果你觉得文章对你有所帮助，别忘了给我点个「赞」噢!
欢迎加入秋叶 Excel 专属读者群~和群友一起互相交流学习 Excel，互帮互助。

现在进群，还会掉落各种学习资源，助力大家提升办公效率





↓↓↓


遇到有价值的文章
不放过 !
👇👇👇
动动小手
分享给朋友~
👇👇👇

您可能也对以下帖子感兴趣

文章有问题？点此查看未经处理的缓存