大部分人都不知道的Excel自带插件,让你效率翻倍,轻松早下班

大部分人都不知道的Excel自带插件,让你效率翻倍,轻松早下班

首页游戏大全未转变者3.0内置菜单更新时间:2024-06-17

日常工作中,出于种种原因,我们往往需要对表格的格式做一些调整。

比如下图所示,将同一个城市的区域合并到一个单元格:

解决这种合并需求,通常的办法,是利用 VBA 编写自定义函数,或者用函数构造辅助列来实现。

相信不少小伙伴看到函数、代码可能会打退堂鼓,所以在这里就不展开说这种方法。

今天,我们要说的是一种点一点鼠标,就能解决问题的方法:Power Query

Power Query 是一款微软官方推出的,为大数据时代而生的数据查询、筛选处理工具。

其功能强大,不仅能上九天揽月,还能下五洋抓鳖。

今天,我们就来牛刀小用,分享下如何用 Power Query 来进行同类项的拆分和合并。

小贴士:

Office 2010 以及 2013 版本的 Excel,需要单独下载安装 Power Query。

2016 版本的 Excel 已将其集成,可通过:数据 - 获取和转换 - 新建查询来打开它。

本文以 Excel 2013 为例。

01

合并同类项

首先,打开 Power Query,添加数据。

具体操作步骤:

❶ 打开待处理的表格,把鼠标定位在数据区域的任意单元格,单击 Power Query;

❷ 选择「从表/范围」,Excel 会自动扩展选区;

❸ 在弹出的对话框中,勾选「表包含标题」,单击「确定」。

这时候,我们就打开了新世界的大门!

接下来,添加「索引列」,并以「索引列」为依据,对「地区」进行透视。

具体操作步骤:

❶ 单击「添加列」—「索引列」,选择「从 0 开始」;

❷ 单击刚添加的索引列的标题来选中「索引列」;

❸ 单击「转换」—「透视列」,对「索引列」进行透视。

在设置「透视列」对话框时,需要注意两点:

❶ 列值这里要选「地区」,因为我们是对地区进行透视操作。

❷ 点开高级选项,这里的「聚合函数类型」我们要选「不要聚合」。

(聚合主要是针对数值类型的数据进行计算,我们这里是文本,所以不需要进行任何的计算)

我们还要对透视出来的地区列,进行合并:

完成透视后,我们可以发现,现在的表格已经发生了巨大的变化:

表格的后面多出了很多列,所有城市所对应的地区,也都被放置到了同一行上。

现在,我们需要将这些列的数据合并到一列。

❶ 单击标题为「0」的列,拖动下方的滚动条到末尾;

❷ 按住 Shift 键盘,再次单击末尾列的标题,这样选中了要合并的所有列;

❸ 单击「转换」—「合并列」,按下图所示设置「合并列」对话框,按确定完成合并操作。

接下来,对合并出来的出来的数据,进行修整:

到此,我们已经完成了合并的操作,但是效果似乎有点凌乱。

这是因为,我们合并的数据中,有很多空值(就是 Null),而我们又选择了用空格作为分隔符,导致合并后数据的前后产生了许多无用的空格符号。

所以我们还需要进一步对结果进行修整,方法很简单。

❶ 选中地区列;

❷ 在菜单栏中找到「转换」,单击「格式」在下拉列表中找到「修整」,单击即可;

完成以后看看效果有多好!

最后,将最终结果上载至 Excel 文件中。

单击「文件」—「关闭并上载至」,在弹出的对话框中选择「表」以及「新建工作表」,然后点击「加载」来完成最终结果的加载操作。

上载到 Excel 中的结果,会自己套用一个样式,大家可以根据自己的情况修改。

同样的,如果想用其他符号作为分隔符,只需用替换功能进替换即可。

到此,我们就完成了合并同类项的全部操作。

有的小伙伴可能会问了,我拿到的表格本来就是这种形式的,但这种形式的表格不适合作为数据源进行统计、分析,该怎么样把它还原成常规的一维表呢?

别急,下面我们就来看看,如何进行同类项的拆分。

02

拆分同类项

相较与同类项合并,同类项的拆分的步骤就简单很多。

具体操作步骤:

❶ 按同样的方式打开 Power Query,并加载数据;

❷ 在「转换」菜单栏中单击「拆分列」,选择「按分隔符」,此时会弹出拆分列对话框。

Power Query 会自动判断文本的分隔符,不用做过多的设置,只需要点确定即可;

❸ 选中地区的第一列,拖动下方的滚动条,按住 shift,选中最后一列。

然后,单击「转换」—「逆透视」,选中「仅逆透视选定列」,完成逆透视操作;

❹ 将结果上载至 Excel 中,删除多余的列,根据自己的对样式做适当调整即可;

怎么样?

很棒有没有!刷新了对宇宙的认知有没有!

练习文件都已经送你了,老样子,我们评论区见~

查看全文
大家还看了
也许喜欢
更多游戏

Copyright © 2024 妖气游戏网 www.17u1u.com All Rights Reserved