七周学会数据分析|良心教程 第三周

1、为什么要学习excel？ 好用。不因为你会python而成为数据分析师，而是能用任何工具解决问题。
excel、sql 场景：敏捷、快速、需要立即相应的需求
python、bi、etl场景：常规、频繁、可复用可工程化的需求

2、excel常用函数：
2.1 文本清洗类
find 定位，find(“k”,p2,1)
substitude 字符串替换
left right mid 字符串截取
text 对数字做格式转换 等同于 右键设置单元格格式转换
concatenate 字符串拼接,(s2,“k”)
trim 去除空格。(英文字符串不会去除中间的空格
replace 具体到指定位置的字符串替换
len 获取长度
excel自带的替换功能，想要全部替换可使用*表示全部。

2.2 关联匹配类
从不同的表合成一张表，通过索引来进行切片。
lookup(lookup_value,lookup_vector,[result_vector])：lookup(A2,{0,5,10,20},{1,2,3,4}）即A2=0~5则返回1，>20则返回4。
vlookup(B2,sheet1!A:B,2,false)：查找值，查找区域，返回第几列，模糊匹配true/精确匹配false；参数说明也可理解为：连接键，连接表！相关联的列，目标列序号，模糊匹配true/精确匹配false。
row 返回单元格所在行，可结合right(A2,1)获取个位数，来抽样。
column 返回单元格所在列
index(区域,行序号,列序号):（A2:T8,1,1） 即区域相当于一个临时表
offset 以指定的引用为参照区，通过给定偏移量返回新的引用：（参照,行偏移,列偏移）
match 返回特定指定值特定顺序的像在数组中的相对位置，如match(6,A2:A8,0):0是匹配模式不用管。如果有重复值，返回的是第一个值的序号。
hyperlink 创建一个超链接。

2.3 逻辑运算类
and or not
false-0
true-1
if（条件判断，真时的返回，假时的返回）
isnumber 判断是否数字
iserror 比如说除以 0
提醒：true、false可以参与加减乘除运算
妙用：b1+b2+b3 得到多少个条件满足

2.4 计算统计类
sum 求和
rank 查找排名
stdev 标准差，结合平均值得出大部分在什么范围内
sumproduct 相当于客单价*销量的区域求和 （Q2:Q7，R2:R7）
rand randbetween 随机
substotal 函数当中的瑞士军刀。统计平均值、求和
count 计算一个区域内有多少个数字（空值不会算入内，选定列后的底部计数值也是count值）
average 平均值
int 向下取整
round 四舍五入，round(num,取整范围) 正数往小数点上偏移，负数往整数上偏移做四舍五入
max min 最大最小值
quartile 中位数
countif 条件计数，countif(Q:Q,">10")
countifs 统计一组给定条件所指定的单元格数，如countifs(A29:A32,">5",B29:B32,“5”) 指满足A29:A32 中>5的且对应B29:B32=5的个数
sumif(R:R,"=应届毕业生",Q:Q)
averageif 参数同上

2.5 时间序列类
时间的本质是数字
year day month date weekday now weeknum today
注意：
  weekday：一年中的第几天，参数2为1标识星期日等于1，我们习惯于用参数2位2表示星期一等于1。
 weeknum：本周是一年中的第几周，参数2含义同上。
动态函数 now：返回当前日期与时间，精确到秒。today：精确到日。
日期的相减：1、直接相减 2、days(end_date,start_date)
date(年，月，日)

3.Excel的常见技巧
3.1 快捷键
Ctrl+方向键，光标快速移动
Ctrl+Shift +方向键，快速框选
xxxx，选定整列
xxxx，选定整行
Ctrl+A 选择整张表
Alt+Enter 换行

3.2 常用操作
Excel-数据-分列-指定分隔符 比函数效率快。
开始-条件格式-数据条 色阶适用于相关性的可视化。
插入-数据透视表-拖入行和值-点击值的右侧，选择汇总方式-得到表后，双击值可以展开指定分组的所有数据。当数据有更新，右键透视图->刷新数据。
视图-冻结首行。
数据-数据分析-描述统计：会新建一个工作本，会把一些常用的函数区聚合了，比如说最大值最小值中位数平均数等，这样会比较方便。
数据透视工具-分析-插入切片器：帮助快速筛选我们想要的数据。
选择数据透视图-插入-柱形图：可视化，可结合切片器使用。
做报表更厉害的是powerbi和python。
公式-定义的名称-根据所选内容创建-首行：相当于给列起别名，以后就不用字母+数字做区域了。也可用于下拉菜单。
数据-删除重复项：做快速数据检查
数据-数据工具-数据验证-允许序列+介于+来源=别名：即可得到下拉框
插入-折线图-选择一行的范围，选择折线图位置：得到迷你图，可以简单的看一个趋势。

4、练习
1.全国点评数最高的饭店是哪家？
2.哪个城市的饭店人均口味最好？
3.哪个类型的餐饮评价最好？
4.类型为川菜的店中，有多少个带「辣」字，又有多少个带「麻」字？
5.口味、环境、服务，三个评价都在8.0以上的饭店有几家？它们在哪个城市的占比最
多？
6.上海地区中，各个类型饭店服务前五名？
7、将点评、人均、口味、环境、服务这几个指标加工出一个综合评价系数，并且计算哪十家店是最好的？
8、对上海地区的日本料理，做一次描述性分析，

1、 排序 或者
J1=MAX(D:D)
K1=INDEX(C:D,MATCH(J1,D:D,0),1)
2、 透视表均值+排序
3、透视表均值+排序
4、FIND(“辣”,C2,1)
筛选川菜、麻不为#VALUE!、辣不为#VALUE! 可以看到底部有计数结果
5、筛选+透视表计数+排序
注意：排序得先选中表来排，以免只对单独列做了排序。
6、筛选上海+粘贴类型店名服务+先对服务排序，再对类型进行排序+if(B3=B2,E3+1,1) 的效果是分组排序 + 筛选序号<=5 的。有个缺点是并列的序号却不同，可以通过加一个判断服务相等就不加一来解决。

7、 $列 $列号 表示绝对引用，即拖动，这个引用不会变。
归一化，指将值收敛到0-1的范围，(int-min)/(max-min)。
值的范围比较大的，需要收敛的话（即控制敏感度），使用log。
8、数据-数据分析-描述统计，快速进行分析。