Excel进行“小数据”分析

在大数据时代,Excel完全满足不了工作需求,最明显的工作反馈就是:数据量太大,运行起来卡!卡!卡!保存一下需要很长时间,工作效率变得低下。然而,对于“小数据”的分析,Excel却是分析的利器。

提出问题、理解数据、数据清洗、构建模型、数据可视化,这5个步骤就是数据分析的基本过程,下面我们通过一个案例,来说明如何用Excel来进行“小数据”的分析。

第一步

提出问题

该案例的数据,是某医院2020年的药品销售数据,我们需要统计出该医院每月的消费次数,每月的销售金额,并分析出月均消费次数,月均消费金额

第二步

理解数据

1、打开该Excel文件,我们可以看到,该销售数据包含的字段有购药时间、社保卡号、商品编码、商品名称、销售数量、应收金额、实收金额等。
2、查看数据有多少行,多少列
Excel能很方便的查看数据总的行数和列数

向下滑动查看

选中某一行,或者某一列,就可以在右下角看到计数(该计数包含表头的字段)

第三步

数据清洗

列名重命名

列名重新命名为自己容易理解和更规范的字段,有助于我们的数据分析。比如我们将购药时间重命名为销售时间。方法就是直接在该单元格重新输入字段名称。

缺失值处理

对于该案例,缺失值我们采用了删除缺失值的处理方式。
首先,使用数据选项卡下的筛选功能
其次,选择销售时间下的空白项,选择出销售时间为空白的行,然后删除。
第三,利用刚才的方法,同样删除社保卡号的缺失值

字符串的分割

根据我们提出的问题,我们需要清洗出销售时间的月份,所以我们需要进行字符串的分割。
字符串的分割有多种,一种是利用函数,另一种是利用Excel的分列功能,因为销售时间的数据比较规范,所以我们可以采用分列功能。分列功能在数据选项卡下。
首先,在分列之前,我们最好在字段的后面增加一列需要分列的字段,避免出现不可挽回的错误。
其次,我们对H列进行分列。注意先选中H列,然后进行分列,分隔符号选择空格(这里可以根据数据的特殊字符选择或者填写相应的字符)
然后选择分列后的数据格式,我们需要的是日期格式

点击完成,得到了如下数据:

再次,处理掉我们不需要星期

根据提出的问题,增加必须的字段
该案例的需求是要分月的,所以我们使用Excel的一些函数和公式,增加销售月份这个字段。

如何提取销售时间的月份呢?我们使用函数month()

看个人情况是否需要加上文本“月”字

异常值处理

该案例异常值的处理,我们也采用删除的方式。
使用筛选功能,首先筛选出销售月份中的错误值,检查为什么错误。
该案例中的销售时间出现了2018年2月29日,说明该值是错误的,删除。
另外我们检查数据还发现,销售数量有小于0的情况,销售数量应该是大于0的,所以,我们对小于等于0的数据也进行删除。

至此,数据清洗完成。

第四步

构造模型

在python中,构建模型时,我们需要写一些构建模型的函数,在Excel中,构建模型这一步我们使用数据透视表就能完成大部分的分析结果。
使用数据透视表注意事项:表头必须要有字段,且表头不能有合并单元格。
1、每月消费次数(要求,同一社保号同一天消费算一次)
在插入选项卡下选择数据透视表

选择需要透视的区域

然后选择需要透视的字段(每月销售次数:需要透视出销售月份、销售日期和社保卡号)

在设计,报表布局下,调整数据透视表格式

得出每天的消费次数

将该数据透视表,复制到一张新表,然后再利用数据透视表功能,得出每月的消费次数。
注意:在值的字段设置里,可以选择求和,平均值,计数等等,因为我们要的数据为次数,所以采用计数。
2、得出了每月的消费次数后,我们就可以求出月均消费次数
月均消费次数=总消费次数/月份数
注意1:因为算出来的不为整数,故使用round函数,取整
注意2:counta函数,计算所选择的所有单元格包含字符的数量
3、求每月消费金额
再次使用数据透视表,透视出销售月份和实收金额,注意实收金额的字段应使用求和调整格式,得出结果
4、求月均消费金额

月均消费金额=实收金额总和/月份数

注意:因为是金额,所以使用round函数,保留两位小数。

至此构建模型完毕,并计算出了所需要的结果

第五步

数据可视化

每月消费次数可视化

使用Excel中的图表功能可以制作出可视化的图表,本次使用的是简单的柱状图。

首先,选择好数据源

其次,选择插入柱状图

最后调整柱状图格式

更改月均消费次数为折线

调整消费次数和月均消费次数格式,填写图表标题,调整布局等等,得出如下结果

每月消费金额可视化

同每月消费次数可视化同样的方式,但是这次我们使用的是折线图

注:数据可视化可以根据数据的不同特性选择不同的图表,以达到简单,重点突出,一目了然的目的。
至此,我们的数据分析完成,得到了我们需求的结果,并展示出了我们的分析成果。
(0)

相关推荐