数据分析之EXCEL篇

一、目录

• 透视表的使用及运用场景
• 常用函数的使用及运用场景
• 数据处理类
• VLOOKUP的使用及运用场景

二、透视表的使用及运用场景

1、定义
透视表是用来对数据快速分类、汇总、求和、求均值、计数等的数据分析工具
2、常见使用场景
每日供应链日报汇总表
各地区零售商销售额分类统计表
分级订单核对统计表等等
3、如何使用
第一步：插入-确认数据源区域-新工作表-确认
第二步：根据需要勾选相应的字段
目的1：
可以统计不同品类的人均总额、口味分总额、服务分总额
选中某单元格-右键-值汇总依据-平均值，可以将求和状态更改为求平均值

目的2：
根据需要勾选的字段，可以对数据进行分组处理，例如，将人均选项拖动到行和值选项卡处
先求和项-更改为计数，然后行标签-组合-修改下步长


可以看出人均消费主要集中在3-502的区间内
回答以下几个问题：
1、求那种类型的菜人均消费最高？
首先，将类型拉到行选项卡处，人均拉到值选项卡处，接下来，见计数改成求和项，最后右键排序-降序，可以看出人均消费最高的类型为其他，其次是火锅

三、常用函数的使用及运用场景

if（判断条件）
口味在7以上为优秀，5以上7以下为良好，5以下为差

=IF(F2>7,"优秀",IF(F2>5,"良好","差"))

sumif求指定类型的汇总数据
sumif（判断数据源，判断条件，目标数据源）
求北京区的人均消费额

=SUMIF(A:A,A2,E:E)

sumifs 求多个指定类型的汇总数据
sumifs（汇总数据源即用来求和的数值区域,条件1判断所在区域1，条件1，条件2判断所在区域2，条件2…）
求北京川菜的人均总额：

=SUMIFS(E:E,A:A,"北京",B:B,"川菜")

countif指定类型的数量
countif(判断数据源，判断条件)
求私房菜类型有多少个？

=COUNTIF(B:B,"私房菜")

countifs求多个指定类型的数量
countifs(判断数据源1，判断条件1，判断数据源2，判断条件2)

=COUNTIFS(B:B,"私房菜",A:A,"北京")

sumproduct 连乘求和

iferror 计算错误无结果或空白值时，iferror让数据返回指定的结果

四、数据处理类

text：将任意日期格式转化为需要的格式
text（数据源，“yyyymmdd”）
如：将年月日转化为日期格式
left/right 从左（右）开始取指定数量的数
left/right(待处理的字符串, 开始位置)
find函数
用来“返回一个字符串在另一个字符串中的起始位置（区分大小写）“，返回的是一个数字
find(要寻找的字符串, 要寻找的字符串所在的字符串,开始寻找的位置)

五、VLOOKUP的使用及运用场景

• VLOOKUP：纵向查询，按列方向查询
• Hlookup：横向查询，按行方向查询
• lookup：两者兼备
  VLOOKUP(要查找的数据，要查找的位置（要查找的数据必须在该位置区域里面）和要返回数据的区域，要返回数据区域的列号，精准匹配用0近似匹配用1)
  找到小东北私房菜的服务评价分

=VLOOKUP(J5,C:I,6,0)

为保证多行店名向下拖拽时不出现错误，要对第二个参数加锁，即$ C:$I