一.为什么要学习Excel
1.Excel的学习路径:
- Excel首先是一个好用的工具(不因为你会Python而成为数据分析师,而是能用任何工具来解决问题)
- 应用场景:Excel,SQL,业务(敏捷,快速,需要立即响应的需求)
Python,BI,业务,ETL (常规,频繁,可复用可工程化的需求,例如词频统计,分组聚合等)
- 如果是一位纯新手的话
Excel函数 ————> SQL函数 ————> Python 函数
(用图形界面了解函数) (用封装好的脚本语言了解函数) (了解编程函数)
2.EXCEL常见的函数
函数分为5类:文本清洗类/关联匹配类/逻辑运算类/计算统计类/时间序列类
- 文本清洗函数:
1bit=两种可能性,用0或1存储
1byte=8bit ,如00000001,一共有256种可能性
UTF-8:英文存储8位,中文状态下存储24位
- 常见的文本清洗函数
- FIND:找出字符在字符串中的位置 =FIND("k",A1,1) k-要查找的字符串;A1被查找的字符串;1-开始位置
- LEFT:从文本函数的第一个字符开始取值=LEFT(A1,FIND("k",A1,1))
- CONCATENATE:拼接函数(输入字符串1,字符串2.....)
- Replece:跟ctrl+F的作用等价
- SUBSTITUTE:=substitute(需要替换的文本,旧文本,新文本,第N个旧文本)
- 屏蔽手机号码的后四位:=substitute(A2,RIGHT(A2,4),"****")
- substitute函数经常用来去掉文本之间的空格:=substitute(A1," ","")
6.trim 去除单元格前后的空格
7.len文本字符串中的字符数
- 关联匹配类函数:
8个函数:LOOKUP/VLOOKUP/INDEX/MATCH/ROW/COLUMN/OFFSET/HYPERLINK
- 逻辑运算函数:false/true
- 计算统计类函数:
12个函数:SUM/SUMPRODUCT/COUNT/MAX/MIN/RANK/RAND BETWEEN/
AVERAGE/QUARTILE/STDEV/SUBTOTAL/INT/ROUND
QUARTILE:四分位数 QUARTILE($C$2:$C$11,0)求最小值、=QUARTILE($C$2:$C$11,4)求最大值,=QUARTILE($C$2:$C$11,2)为中位数
- 时间序列函数
8个函数:Year/Month/Weekday/Weeknum/Day/Date/Now/Today
案例:
给各区域店铺进行综合评分,部分数据如下:
![933283442267c6a7a5098eb4821eb8a8.png](https://i-blog.csdnimg.cn/blog_migrate/60a368936e430513deddf10458015155.jpeg)
- 对脏数据进行数据清洗,异常值和缺失值进行处理
- 最为关键的是不同维度如何给定系数:点评反应的是店铺热度,一定程度上会影响可以下单,但需要对数据LOG处理,人均价格是降权同时也需要log处理
- 最后算出来的值进行归一化(X-min)/(max-min)处理
归一化:把有量纲的表达式变为无量纲表达式的方法,把数据映射到0~1范围内之内处理,把需要处理的数据经过处理后限制在你需要的范围之内,归一化有统一,同一,合一的意思。
二.SQL数据库
1.数据库:
- 数据库是数据存储的集合,表是数据结构化的信息。
- 列存储表中的组织信息,行存储表中的记录信息
- 主键是表中的唯一标示,主键不具备业务意义
注:(1)表的主键不做强制要求,但建议设立
(2)主键值必须唯一
(3)每一行必须有一个主键,不可为空
(4)主键值不可被修改
(5)主键值被删除后不可重用
(6)表A的主键可以作为表B的字段,此时不受约束
- 数据类型:文本(char)/int(整数)/ float (小数点)/date(日期)/ timestamp(精确到秒时间)
2.SQL函数
- LOCATE:找出字符在字符串中的位置 =LOCATE("k",A1,1) k-要查找的字符串;A1被查找的字符串;1-开始位置
- LEFT 与LOCATE结合:LEFT从文本函数的第一个字符开始取值=LEFT(A1,LOCATE("k",A1)-1)
- RIGHT 与LOCATE结合:RIGHT从文本函数的第一个字符开始取值=LEFT(RIGHT(salary,Length(salary)-Locate('-',salary))-1)
- CASE WHEN THEN : 数据
Select
CASE
WHEN (bottom+top)/2<=10 THEN '0-10',
WHEN (bottom+top)/2<=20 THEN '10-20',
WHEN (bottom+top)/2<=30 THEN '20-30'
ELSE '30+'
END
3.SQL练习
- 统计不同月份的下单人数
- 统计用户三月份的回购率和复购率(回购率一段时间与下一段时间对比;复购率是一段时间内客户购买次数)
复购率:
Select count(ct),count(if(ct>1,1,null)) from (
Select uerserID,COUNT(userID) as ct from data.orderinfo
WHERE ispaid="已支付"
and month(paidtime)=3
group by userID) t
回购率:
- 统计男女用户消费频次是否有差异
- 统计多次消费的用户,第一次和最后一次消费间隔是多少?
- 统计不同年龄段,用户的消费金额是否有差异?