设置
  • 日夜间
    随系统
    浅色
    深色
  • 主题色

Excel 数据整理,两个新函数介绍: Textsplit 和 Hstack 函数

秋叶Excel 2023/12/23 19:30:03 责编:梦泽

大家好,我是处理数据的小爽鸭~

每到季度初,公司的项目部负责人老叶会对上一季度进行业绩评分,以便发放上季度的业绩奖金。

他要对参与项目的成员,针对已经完结的项目,分别进行业绩评分。出于方便登记的缘故,他制作了如下表格。

老叶知道这种表格并不是标准的数据表,不能利用数据透视表进一步进行分析业绩情况,所以找到了我,想让我帮忙转换成标准的一维数据表。

所以,今天小爽就带大家来看看具体的做法,操作并不难,比较基础,重在理解思路哦~

1、Word 替换法

适用于:任何版本

难度指数:★

我们观察一下数据,可以看到数据集中在单元格里,并用逗号合并。

不难想到,如果逗号本身就是一个回车符呢?那不就是直接分开了?

那怎么将逗号换成回车符,然后在 Excel 中拆分到其他单元格啊?

用 Word 鸭!

具体步骤:

❶ 将数据复制到 Word 中(【Ctrl+C / V】复制粘贴不用我教吧)。

考考大家,Word 替换窗口快捷键是?

对!就是【Ctrl+H 】!

❷ 按住快捷键 【Ctrl+H】打开 Word 的替换窗口。

查找内容:,(逗号)

替换为 需要一个手动换行符(也叫软回车),我们可以在【更多】中,通过特殊格式中添加。

选择特殊格式-手动换行符,此时,替换为的输入框,出现了个 ^l。

^l 就是手动换行符的符号,单击【全部替换】按钮。

替换后的数据如下图所示。

❸ 我们将 Word 的数据表重新复制粘贴到 Excel 中。

选中整个表,按住快捷键【CTRL+C】进行复制。

选中一个单元格,按住【Ctrl+V】进行粘贴,如下图所示,可以看到逗号已经拆分到行了。

拆分合并单元格并填充,如果是 WPS ,系统自带该功能。如下图。

到这里,直接搞定了!

通过鼠标点点 + 快捷键的方式,我们搞定了表格的整理。

不过这个方法虽然可以应急,但是如果像老叶一样,每个季度都要做这个数据,还是得用函数或者 M 函数等方法。

2、函数拓展

适用版本:Office 365

难度指数:★★★

如果你是 Office 365 或者 WPS(最新版),可以用 Textsplit 函数和 Hstack 函数。

=HSTACK(TEXTSPLIT(CONCAT(SUBSTITUTE(","&B1:B5,",",";"&A1:A5&",")),",",";",1),TEXTSPLIT(TEXTJOIN(",",,C1:C5),,","))

有些小伙伴可能对这两个新函数感觉有点陌生。

不怕,咱得先知道,才会想到去用它,去搜索。

Textsplit 函数,是个非常强大的函数。

感兴趣的小伙伴,点击文末的链接,即可跳转到 Textsplit 系列文章。

=TEXTSPLITtext, col_delimiter, [row_delimiter], [ignore_empty],[match_mode], [pad_with])=TEXTSPLIT文本 列分隔符 [行分隔符], [是否忽略空单元格],[是否区分大小写], [出错时填充的值])

HSTACK 函数,是个列拼接函数,有了这个函数,很多问题越来越简单。

举个例子:将 A1:A2,C1:C2,E1:E3 三个区域按照列进行合并。

了解了这两个函数,我们再来看看这个公式,就不难理解啦!

=HSTACK(//列拼接
TEXTSPLIT(CONCAT(SUBSTITUTE(","&B1:B5,",",";"&A1:A5&",")),",",";",1),
//获取姓名和参与完结项目合并后拆分处理的数据列
TEXTSPLIT(TEXTJOIN(",",,C1:C5),,","))
//获取评分列

Textsplit 函数教程参考:

Textsplit,一个超强大的新函数来了!

这个超强大的文本处理函数,30 分钟帮你干完一天的工作!

这 3 个 Excel 文本函数,又是被低估了的函数!(建议收藏)

3、最后的话

本文讲解的是:如何将不规范的表格,通过操作或者函数方法,转化为一维数据表。

将数据表转化后,就可以通过数据透视表分析啦。

如下图:上个季度每个项目成员参与的项目数,总分是多少?每个项目有多少人参加,参与成员有谁?

通过数据的进一步分析,除了可以解决绩效计算的问题,领导也可以进一步做决策,比如更合理安排当前季度的规划。

看到这里,是不是感觉 Excel 很强大呢~

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:小爽

广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。

相关文章

关键词:Excel教程Excel学院

软媒旗下网站: IT之家 最会买 - 返利返现优惠券 iPhone之家 Win7之家 Win10之家 Win11之家

软媒旗下软件: 软媒手机APP应用 魔方 最会买 要知