一个完整的Excel工程|从数据清洗到可视化过程

今天我们来学习如何在Excel中完成从数据清洗到可视化，根据数据制作出可以展示有效信息的图表。

数据集：Excel-Tutorial/Excel Project Dataset.xlsx at main · AlexTheAnalyst/Excel-Tutorial · GitHub

一、数据清洗

首先我们先创建新的三个Sheet，一个是Working Sheet,作用在于保留一份原数据，以免处理数据出现失误操作丢失原数据的情况，另外两个是Pivot Table和Dashboard，用于制作数据透视表和可视化仪表盘。

1、去除重复值

点击Highlight Duplicate中的Remove Duplicates去处重复值，在这个数据集中，我们一共去除26个重复值。这一步让我们去除原数据中多余出现的无用数据。

2、将数据具体化显示

数据集中的Marital Status婚姻状况用S表示Single单身，M表示Married，以免其他看到这份数据的伙伴不能马上理解这个意思，我们将这一列数据中的全部S改为Single，M改为Married显示。操作：点击Ctrl+H，将S填在Find what一行，Single填在Replace with一行，点击OK即可，M的处理同理。你操作完以上步骤会发现列名Marital Status的S和M也改变了，需要手动修正回来。以同样的操作将Gender一列的F改为Female，M改为Male。这时候的数据显示更加具体清晰易懂。

3、去除数值多余小数点

我们可以看到Income收入这一列的数据是以货币的形式展现了，我们暂且不作修改，选中这一类数据，确定为Currency货币形式，然后点击两次右下角的去除小数点即可。

4、将具体年龄划分成三个年龄段

我们可以看到Age年龄那一列的数据都是具体的数值，到时候可视化的时候不太好展示，都是具体的数值的话在可视化上会很混乱，不凝聚难以看出有效信息。这里使用IF公式进行修改，公式如下：=IF(L2>54,"Old",IF(L2>=31,"Middle Age",IF(L2<31,"Adolescent","Invalid")))，大于54岁分到Old一层，大于等于31岁分到Middle Age，小于31岁是Adolescent。

数据清理进行到这里，等一下制作数据透视表和可视化的时候有需要再进行修改。

二、制作数据透视表

先展示最后的Dashboard成果：

这些图都在Pivot Table中制作完成，接下来我们一个一个讲怎么制作。

1、在Pivot Table界面点击Insert插入Pivot Table，到Working Sheet中点击Ctrl+A将所有内容复制作为透视表的数据素材，开始制作第一个图表。

将Income放到VALUES处然后右击将Income设置为Average Income，Purchased Bike自行车的购买情况为列，Gender性别为行，这里将数据全部选中改为Number格式去除小数点，然后插入Chart，选择Clustered Column,点击图中右上角的第一个图标Chart Element为图标增加Chart Title和Data Table，此时我们第一个可视化图标便制作完成啦。

这个图表的意义在于展示不同收入情况对自行车购买的情况，以及不同性别之间的购买区别，可以看出，不论是女性还是男性，收入增加的情况下会增加对自行车的购买。

 

2、第二个图表是一个线性图，按照上述第一步的操作将相关数值放到相应的位置，然后我们会发现Commute Distance中的“10+ Miles"并没有本应地在这一列的最底部，这时需要回到Working Sheet中修改一下，同样使用Ctrl+H的查找替换功能，将“10+ Miles"修改为”More Than 10 Miles",修改完成之后回到Pivot Table中点击Data中的Refresh all将数据更新一下，这时通勤距离便正常按照大小排列好了。

透视表制作好之后开始制作Chart，点击Chart中的Line ，按步骤一中的操作为图表添加标题和水平上的x轴标注。这副线性图展示了不同通勤距离对是否购买自行车的影响。 

 

3、第三幅图也是一副线性图， 我们将相应数值放到透视表的相应区域，选择Chart中的Line With Marks,此时我们可以清晰地观察到Adolescent即小于31岁和Old大于54岁的群里购买自行车较少，中间群体购买较多。如果一开始在数据清理没有将年龄进行群体划分的话，就回像下图一样，难以看到任何明显的趋势等有效数据信息，而划分之后更加清晰，如果需要还可以对年龄进行更细致的划分。

 三、制作可视化仪表盘

将上述三个制作好的图表复制到Dashboard的界面，点击View将界面的Gridlines去掉，然后添加一个大标题，设置好标题的名字和底色，让整个界面的颜色达到协调一致的效果。如果你使用的是微软里面的Excel，你还可以添加Slicer，比如婚姻状况，受教育情况等，让Slicer Connect到三个图表中，随时点击观察这些变量对自行车购买的影响，WPS中似乎暂时没有这个比较高级的切片功能。

再次展现最后的Dashboard成果：

文章行文至此，有任何疑问的小伙伴欢迎留言，感谢您的阅读!

Don't forget to subscibe and hit a like !More useful contents are coming for you!