cmd看excel有多少个子表_Excel使用实例-baby_trade_history

一、日常工作中的基本操作

从电子表中数据类型转换（文本、数字、日期等）开始，到透视表的熟练掌握，常用函数的合理运用都是大部分工作的基本技能，以下搜集了一些比较实用的供大家一起互相学习。

格式转换的：

Excel表格中格式转换的这些套路，你都get了吗？

关于引用的小技巧：

秒懂Excel的三种引用

常用函数Vlookup的：

学会这个技能，你的Excel分析水平就超过了同龄人

善用图表的使用：

如何使用Excel绘制图表？

箱型图的画法：

excel怎么绘制箱形图?excel2016中绘制一组数据的箱形图教程

系统进行分析：

如何使用Excel进行数据分析？

时间充裕，想系统学习的，也可以参考以下Excel进阶教程：

Excel进阶教程_河南应用技术职业学院_中国大学MOOC(慕课)

二、操作实例

回到主题，上次找了个baby trade的历史数据，现在用Excel来实际分析下：

首先对拟解决的问题进行回顾：

（1）时间上购买行为是否有差异，未来的需求是否有扩大的趋势，分析总体到月销售情况，决定后期投入力度。

（2）品类的需求是否因时间而有变化，分析品类在时间上的销售数量变化，决定后期投入方向。

（3）商品购买的性别偏好，分析男/女最喜欢的商品类别，用2表相同的用户id进行关联。

（4）从购买时间和出生日期去统计一般的购买行为是出生前，还是出生后多长时间。

原始数据的初步调整，英文列名的中文置换，性别的中文展示，同时整合入一个电子表的2个子表，然后对数据进行基本的清洗。

1、去重

先看交易历史，使用用户id+购买时间结合作为去重条件，避免出现顾客重复购买数据被误删的情况。

发现1个重复值

另撤销去重，使用透视表对删除的数据进行一一比对，是否错误，得以下：

有一条数据重复

在原始数据中，发现购买的商品大类、小类、购买数量均一样，只产品特性和购买行为不一样，故认定顾客当天实际是购买了一件商品（出现了更换产品的情况），可以按前面去重方式剔除一条。

2、异常数据处理

从购买历史记录，我们发现，单用户单日最小购买数量是1，最大购买数量是10000，结合前面的四分位法判断异常。

因电商有节点促销的爆发性，从单用户角度判别可能也会失真，那可以汇总到天去判断。

先处理购买时间，解决整体市场销售趋势变化，使用数据-分列，按日期YMD进行处理。增加列“购买年“和”购买月”：

然后按天汇总，使用数据分析工具得：

中位数为Q2=44，下四分位数为Q1=30，上四分数为Q3=64；

按最大异常值Q3+3*(Q3-Q1)=166。则单用户单日购买数量大于单日总最大异常值的数据剔除，即大于166的32条数据。

3、解决前面的第1和第2个问题

采用数据透视表，购买月为行，购买年为列，购买数量求和。

复制数据出来，2012年7月是从2号开始，而2015年2月只是1-5号数据。故可以按上下半年来做同比，上半年取2013年、2014年整个上半年比较，1月份取2012-2015进行比较，下半年取2012-2015三年整体进行比较。

从时间上看，整体市场不论上下半年，都呈快速增长的趋势，可以加大投入。

商品品类上看，将商品大类加入行，对月进行组合（起始1，终止12，步长6），对销售数量进行排序。

从品类上看，销售绝对值前三分别是28，50008168和50014815。2014年上半年增速最快的是28品类，下半年最快的是38品类。

4、解决婴儿性别与购买偏好和购买时间问题

使用vlookup函数，关联用户id的婴儿出生日期和性别。

对出生日期，使用分列进行日期处理，用购买日期-出生日期得到购买时婴儿年龄。

因为得到的是天，可以再除以365，得到是几岁，保留1位小数。

筛选出能匹配出年龄的数据，复制出来形成新表，对表格进行透视。商品大类为行，性别为列，购买数量求和，得下表：

从数据看，家长为女孩购买的总数比比男孩多。为男孩购买最多的前三项是50008168，28和50014815；为女孩购买最多的前三项是50014815，50008168和28，所以这三个品类也是男女孩最喜欢的类别。

从小品类看，女孩最喜欢的前十销售490，占总销售935的52.4%；男孩最喜欢的前十销售195，占总销售559的34.9%，这样看女孩喜欢的商品更集中，尤其是排名第一的50018831，占比总销售21.9%，此类商品可以重点关注，加大备货。

下面看婴儿年龄、性别与购买数量的关系。使用购买时婴儿年龄为行，性别为列，购买数量求和得表：

由于数据过于分散，现对年龄进行分段，同时表中年龄28.29异常，剔除。年龄分段如下：

透视表，排序后得如下结果：

家长一般会在婴儿出生后到1岁内购买商品，其次是1岁到2岁之间，也有部分家长在孩子出生前1年就开始购买。性别上，父母均在孩子1岁前购买的可能性>孩子1岁以后购买，5岁起父母还购买商品的可能性急剧减小，10岁以上的可能性<1%。

综合数据得出以下预测：

1、婴儿产品的需求在逐年上升，年内上半年旺季在5月，下半年在11月；

2、品类上28，50008168和50014815需求量最多；

3、性别需求上，女孩的需求总量大于男孩，女孩的需求更集中，主要在50018831品类上；

4、父母更愿意在婴儿出生到1岁，和1-2岁之间购买婴儿产品，后期可在有这个年龄段婴儿的家庭进行重点宣传。