轻松早下班,再难的数据整理我都能轻松搞定了葡萄游戏厅官网

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

作为职场人士,尤其是表哥表姐们,利用Excel来快速调整数据是一种常用技能。然而据我观察,我很多人整理数据的效率却非常慢,有些明明一分钟就完成的活,非得折腾一个小时才能完成。例如下面这种情况(从左侧的表要得到右侧的表):

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

文:傲看今朝

葡萄游戏厅官网 1

其实很简单,但很多朋友却只知道复制粘贴,虽然表现得很勤奋,但效率也太低下了。今天我就来介绍两种解决此种需求的技巧。

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

一、Excel逆透视整理

1.打开编辑查询器窗口。选中数据区域,单击数据选项卡–从表格。

记得必须勾选“表包含标题”。

2.在弹出的查询编辑器窗口中差分列。单击姓名列选中,然后单击开始选项卡–拆分列–选择按字符。

3.设定自定义分隔符“、”。葡萄游戏厅官网,在弹出按分隔符拆分列对话框中设定指定字符,由于此例子中是按照顿号分隔的,因此我们选择自定义,符号输入“、”,拆分位置选择“每次出现分隔符时”,单击确定。

4.做逆透视。按住Ctrl键然后单击依次选中:姓名.1、姓名.2、姓名.3、姓名.4。然后单击转换选项卡—逆透视列–逆透视列。

5.删除属性列(姓名所在的列)。单击选中属性列,右键单击下拉菜单选择:删除。

6.将制作好的表格导入到Excel中。单击文件选项卡–关闭并上载。这样我们就得到我们想要的效果,重新命名一下列名称即可。注意,我们得到的表格是一个智能表格。

得到效果

得到漂亮的智能表格

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

二、word查找替换法

1.复制数据区域至Word软件。选中全部数据区域,按下Ctrl+C组合键复制,然后打开word
2016软件,按下Ctrl+V组合键粘贴,单击粘贴右下角的粘贴选项–只保留文本。

从Excel复制数据至Word软件

2.替换顿号。按下Ctrl+H组合键打开”查找与替换”对话框,查找内容输入“、”,替换为输入:“^t^p^t”,然后单击全部替换,单击确定,最后关闭查找与替换对话框。

3.从Word中复制文本至Excel。在word中复制“顿号已经被替换了”的文本,然后回到Excel中,选择E1单元格,右键单击,下拉菜单选择匹配目标格式。

4.填充空白单元格。选中E2:E21单元格区域,按下F5键或者Ctrl+G快捷键打开定位对话框,单击定位条件–空值–确定即可。

5.在编辑栏中输入公式“=E2”,然后按下Ctrl+Enter键完成公式的输入。我们就得到了我们想要的效果。

关于快速整理数据的小技巧就分享到这里。

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

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

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

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

小贴士:

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

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

本文以 Excel 2013 为例。

01

合并同类项

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

具体操作步骤:

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

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

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

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

葡萄游戏厅官网 2

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

具体操作步骤:

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

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

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

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

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

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

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

葡萄游戏厅官网 3

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

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

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

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

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

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


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

葡萄游戏厅官网 4

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

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

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

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

❶ 选中地区列;


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

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

葡萄游戏厅官网 5

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

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

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

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

葡萄游戏厅官网 6

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

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

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

02

拆分同类项

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

具体操作步骤:

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


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

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

葡萄游戏厅官网 7

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

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

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

葡萄游戏厅官网 8

怎么样?

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

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

网站地图xml地图