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中的图表功能可以制作出可视化的图表，本次使用的是简单的柱状图。

首先，选择好数据源

其次，选择插入柱状图

最后调整柱状图格式

更改月均消费次数为折线

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

每月消费金额可视化

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

注：数据可视化可以根据数据的不同特性选择不同的图表，以达到简单，重点突出，一目了然的目的。

至此，我们的数据分析完成，得到了我们需求的结果，并展示出了我们的分析成果。