Excel 快速处理不规范数据的案例分析

大家好,我是明镜在心。

对于大部分职场人来说,天天和表格打交道是一件很稀松平常的事情。

经常做各种各样的统计分析报表,也能理解。

不过,在做这些报表的时候,如果有一个规范的数据源,不论是对于自己还是对于他人,会更加方便。

这可不是我瞎说的,有实例为证。

上面这个表格是一位小伙伴昨天发来的,他的需求是将左边记录的数据汇总整理成右边的表格样式。

这样的记录看上去还是挺有规律的,总销量 = 销量 + 销量 3 + 销量 5。

会函数的小伙伴们,应该会想到用 SUMIF 函数吧。

可以简单的用 3 个 SUMIF 相加,从而得出结果。

完整公式:

=SUMIF(A:A,H2,B:B)+SUMIF(C:C,H2,D:D)+SUMIF(E:E,H2,F:F)

当然,也可以用错位引用的方法,使用一个 SUMIF 或者 SUM 函数等也可以搞定。

但是按左边格式来记录原始数据,随着年月的增加,后期统计分析数据也会愈加繁琐。

如果我们把左边的表格整理成下图这个样子呢?

就是将其做成标准的流水账的样子,然后我们就可以通过数据透视表做成各种各样、从不同角度进行统计分析的报表了。

如何将不规范的原始记录做成上面流水账的格式呢?(这种格式就是我们通常所说的一维表格数据。)

今天我就跟大家分享下珍藏已久的小妙招吧~

PS.该做法没有版本限制。

1、操作步骤

STEP01 插入新列

选中 A 列,按住【Ctrl】键之后,依次选中 C 列和 E 列。

然后点击鼠标右键-【插入】新列。

STEP02 将月份填充到新增加的列中

选中 A3:G8 数据区域,按【F5】调出定位对话框中的【定位条件】,并选中其中的【空值】,之后在编辑栏中输入「=E$1」,最后按【Ctrl+Enter】,批量填充月份数据。

PS.上图中操作时,活动单元格在【D3】,实际情况也许不一样,小伙伴们要适当变通。

另外,为什么要对行号进行绝对引用呢?

很简单,是为了在向下填充的时候,让其行号保持不变。向左右填充的时候,列号保持相对引用。

STEP03 使用等于号(=)将数据连接在同列中

在 A9 单元格中,输入「=D3」,然后向右复制拖动至 F9 单元格,再向下复制拖动到最后一行全部出现 0 值的时候为止。

这样我们就把 3 个月的数据整合到同列中了。

STEP04 整理复制所需数据

在 A2 单元格输入月份,并在第二行筛选掉其中为 0 的值,留下有数据的部分,最后复制到新表中即可。

好了,这样就把数据整理完了。

剩下的,我们通过数据透视表点点鼠标就能轻轻松松完成汇总啦!

当然,上面的问题用 SUMIF 也可以搞定,但是使用一维表的好处在于,可以利用数据透视表进行多维度的分析。

比如:

我们还可以按月进行总销量的汇总,只需要将【月份】字段拖动到【行】区域里面就可以了。

如果想按占比统计每月的销量也非常方便。

只要单击鼠标右键,选择【值显示方式】-【总计的百分比】。

然后就可以显示出每个月的销量占比了!

相比使用函数公式来进行统计,数透的方法方便的不止一丢丢吧!

2、知识扩展

上面的技巧,也可以用在其他方面。

比如:在很多情况下,还有一种常见的格式,是下面这种表格记录形式。

就是我们俗称的二维表格。这种表格只适合做数据汇总,不适合作为数据源来存储。

通常需要将其转换为如下的一维表格。

那怎么转换呢?

其实操作方法跟上面介绍的差不多,看一遍差不多就能理解了。

操作步骤如下:

STEP01 插入新行

先选中 B 列,然后按住【Ctrl】键,之后依次选中 C 列和 D 列,点击鼠标右键插入新列。

STEP02 将月份填充到空单元格中

选中 B2:F7 单元格区域,按【F5】调出定位对话框中的定位条件,定位【空值】。

然后在编辑栏中输入公式「=C$1」,最后按【Ctrl+Enter】,成功批量填充各自的月份。

剩下的步骤跟上面的操作基本相同。

在【B8】单元格输入公式「=D2」,向右并向下拖拉复制即可。

最后复制【A】列的姓名,到下面的空白处即可。

3、写在最后

今天我们介绍了将不规范数据源整理为规范的一维表格的技巧。

利用到了很多小知识点:

❶ 定位数据

❷ 批量填充公式

❸ 等号的特殊用法

还介绍了利用数据透视表可以进行多种汇总统计。

掌握了今天的数据整理方法,在以后的工作中处理不规范数据源,将会得心应手!

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:明镜在心

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

文章价值:
人打分
有价值还可以无价值
置顶评论
    热门评论
      文章发布时间太久,仅显示热门评论
      全部评论
      一大波评论正在路上
        取消发送
        软媒旗下人气应用

        如点击保存海报无效,请长按图片进行保存分享