点击蓝字【秋叶 Excel】
发送【礼包】
免费领办公神器、Office 模板和免商字体!
![](http://dingyue.ws.126.net/2023/1219/40fdae91j00s5wa0m001vd200u0008xg00hv005b.jpg)
本文作者:小爽
本文编辑:竺兰
大家好,我是处理数据的小爽鸭~
每到季度初,公司的项目部负责人老叶会对上一季度进行业绩评分,以便发放上季度的业绩奖金。
他要对参与项目的成员,针对已经完结的项目,分别进行业绩评分。出于方便登记的缘故,他制作了如下表格。
![](http://dingyue.ws.126.net/2023/1219/605f1973j00s5wa0n004zd200u000b4g00hv006m.jpg)
老叶知道这种表格并不是标准的数据表,不能利用数据透视表进一步进行分析业绩情况,所以找到了我,想让我帮忙转换成标准的一维数据表。
![](http://dingyue.ws.126.net/2023/1219/03988779j00s5wa0o005ed200u000cog00hv007j.jpg)
所以,今天小爽就带大家来看看具体的做法,操作并不难,比较基础,重在理解思路哦~
![](http://dingyue.ws.126.net/2023/1219/ea2eb1cdj00s5wa0p0007d2006o006og005w005w.jpg)
![](http://dingyue.ws.126.net/2023/1219/6fbb4c19j00s5wa0p0005d200ku007cg00hv006a.jpg)
Word 替换法
适用于:任何版本
难度指数:★
我们观察一下数据,可以看到数据集中在单元格里,并用逗号合并。
不难想到,如果逗号本身就是一个回车符呢?那不就是直接分开了?
![](http://dingyue.ws.126.net/2023/1219/1a8c78c8j00s5wa0q004rd200u000abg00hv0064.jpg)
那怎么将逗号换成回车符,然后在 Excel 中拆分到其他单元格啊?
用 Word 鸭!
具体步骤:
❶ 将数据复制到 Word 中(【Ctrl+C/V】复制粘贴不用我教吧)。
![](http://dingyue.ws.126.net/2023/1219/eeed29edj00s5wa0r001ad200rl00ftg00hv00a8.jpg)
考考大家,Word 替换窗口快捷键是?
对!就是【Ctrl+H 】!
❷ 按住快捷键 【Ctrl+H】打开 Word 的替换窗口。
查找内容:,(逗号)
替换为需要一个手动换行符(也叫软回车),我们可以在【更多】中,通过特殊格式中添加。
![](http://dingyue.ws.126.net/2023/1219/949d63cfj00s5wa0s003jd200u000dag00hv007w.jpg)
选择特殊格式-手动换行符,此时,替换为的输入框,出现了个^l。
![](http://dingyue.ws.126.net/2023/1219/dc2d74bej00s5wa0t00bnd200u000rlg00hv00gf.jpg)
^l 就是手动换行符的符号,单击【全部替换】按钮。
![](http://dingyue.ws.126.net/2023/1219/e2fbae1ej00s5wa0v002hd200u000cfg00hv007e.jpg)
替换后的数据如下图所示。
![](http://dingyue.ws.126.net/2023/1219/0e7b225ej00s5wa0w0016d200ob00k4g00hv00es.jpg)
❸ 我们将 Word 的数据表重新复制粘贴到 Excel 中。
选中整个表,按住快捷键【CTRL+C】进行复制。
![](http://dingyue.ws.126.net/2023/1219/ab627cfbj00s5wa0x001ed200qb00mtg00hv00fh.jpg)
选中一个单元格,按住【Ctrl+V】进行粘贴,如下图所示,可以看到逗号已经拆分到行了。
![](http://dingyue.ws.126.net/2023/1219/590c6771j00s5wa0z0020d200pv00omg00hv00gz.jpg)
拆分合并单元格并填充,如果是 WPS ,系统自带该功能。如下图。
![](http://dingyue.ws.126.net/2023/1219/e2f7365cj00s5wa10005hd200u000p0g00hv00ev.jpg)
到这里,直接搞定了!
![](http://dingyue.ws.126.net/2023/1219/5dc98ef5j00s5wa12006kd200u000fsg00hv009e.jpg)
通过鼠标点点+快捷键的方式,我们搞定了表格的整理。
不过这个方法虽然可以应急,但是如果像老叶一样,每个季度都要做这个数据,还是得用函数或者 M 函数等方法。
![](http://dingyue.ws.126.net/2023/1219/bb173d8dj00s5wa12000hd200c800bag0069005r.jpg)
![](http://dingyue.ws.126.net/2023/1219/43349d15j00s5wa120006d200ku007cg00hv006a.jpg)
函数拓展
适用版本:Office 365
难度指数:★★★
如果你是 Office 365 或者 WPS(最新版),可以用 Textsplit 函数和 Hstack 函数。
=HSTACK(TEXTSPLIT(CONCAT(SUBSTITUTE(","&B1:B5,",",";"&A1:A5&",")),",",";",1),TEXTSPLIT(TEXTJOIN(",",,C1:C5),,","))
![](http://dingyue.ws.126.net/2023/1219/77fb3148j00s5wa14005pd200u000crg00hv007l.jpg)
有些小伙伴可能对这两个新函数感觉有点陌生。
不怕,咱得先知道,才会想到去用它,去搜索。
![](http://dingyue.ws.126.net/2023/1219/de55fc2aj00s5wa14001hd200u000rkg0069005q.jpg)
Textsplit 函数,是个非常强大的函数。
感兴趣的小伙伴,点击文末的链接,即可跳转到 Textsplit 系列文章。
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty],[match_mode], [pad_with])=TEXTSPLIT(文本, 列分隔符, [行分隔符], [是否忽略空单元格],[是否区分大小写], [出错时填充的值])
HSTACK 函数,是个列拼接函数,有了这个函数,很多问题越来越简单。
举个例子:将 A1:A2,C1:C2,E1:E3 三个区域按照列进行合并。
![](http://dingyue.ws.126.net/2023/1219/5da37f39j00s5wa15004dd200u000e3g00hv008d.jpg)
了解了这两个函数,我们再来看看这个公式,就不难理解啦!
=HSTACK(//列拼接
TEXTSPLIT(CONCAT(SUBSTITUTE(","&B1:B5,",",";"&A1:A5&",")),",",";",1),
//获取姓名和参与完结项目合并后拆分处理的数据列
TEXTSPLIT(TEXTJOIN(",",,C1:C5),,","))
//获取评分列
Textsplit 函数教程参考:
![](http://dingyue.ws.126.net/2023/1219/0079a88ej00s5wa150006d200ku007cg00hv006a.jpg)
最后的话
本文讲解的是:如何将不规范的表格,通过操作或者函数方法,转化为一维数据表。
将数据表转化后,就可以通过数据透视表分析啦。
如下图:上个季度每个项目成员参与的项目数,总分是多少?每个项目有多少人参加,参与成员有谁?
![](http://dingyue.ws.126.net/2023/1219/92c20819j00s5wa170065d200u000cjg00hv007g.jpg)
通过数据的进一步分析,除了可以解决绩效计算的问题,领导也可以进一步做决策,比如更合理安排当前季度的规划。
看到这里,是不是感觉 Excel 很强大呢~
如果你想学习更多关于表格设计、数据处理、数据分析、汇报表达的知识,想提高 Excel 技能、升职加薪,那么今天就是一个很好的机会:
小编为大家申请了专属福利,原价 99 元 的《秋叶 Excel 3 天集训营》,现在报名,只需 0 元!
秋叶 Excel 3 天集训营
0 基础也能学
报名额外赠送
100 套职场必备图表模板
35 个常用函数说明
学到就是赚到
名额有限,快来免费加入
悄悄告诉你,除了前文提到的两种方法,我们其实还可以用 PowerQuery(数据清洗的利器)解决这个问题,不过由于文章篇幅有限,之后我们再聊~
![](http://dingyue.ws.126.net/2023/1219/2edf9deaj00s5wa17000pd200jg00jgg00620062.jpg)
![](http://dingyue.ws.126.net/2023/1219/8d864e53j00s5wa18003bd200u00076g00f6003m.jpg)
以上内容包含广告
热门跟贴