本课程共七个章节，课程地址：7周成为数据分析师（完结）_哔哩哔哩_bilibili

数据分析思维
业务知识
Excel
数据可视化
SQL
统计学
Python

第三周：Excel（P31-P42）

为什么要学习Excel
Excel的常见函数
Excel的常见技巧
用Excel进行数据分析

第三周：Excel（P31-P42）

一、为什么要学习Excel

二、Excel的常见函数

（一）文本清洗函数

FIND Left Right Mid Concatenate Replace Substitute Text Trim Len

（二）关联匹配函数

Lookup VLOOKUP INDEX MATCH Row Column Offset Hyperlink

（三）逻辑运算类

True False And Or IF IS Not

（四）计算统计类

SUM SUMIF Sumproduct count countif max min ABS Rank Rand Randbetween Average Quartile Stdev Subtotal Int Round

（五）时间序列类

Year Month Weekday Weeknum Day Days Date(year,month,day) Now Today

三、Excel的常见技巧

（一）Excel的快捷键

（二）分列、数据条、色阶

（三）数据透视表（插入里）

（四）视图

（五）数据分析

（六）数据切片（多维分析）

（七）引用数据

（八）删除重复值

（九）下拉菜单

（十）迷你图（在单元格里直接显示折线）

（十一）数组

四、用Excel进行数据分析

1. 全国点评数最高的饭店是哪家？

2. 哪个城市的饭店人均口味最好？

3. 哪个类型的餐饮评价最好？

4. 类型为川菜的店中，有多少个带“辣”字，又有多少个带“麻”字？

5. 口味、环境、服务三个评价都在8.0以上的饭店有几家？它们在哪个城市的占比最多？

6. 上海地区中，各个类型饭店服务前五名？

7. 没有评价的饭店有几家？

8. 将人均价格划分成0-50 50-100 100-150 150-200 200+ 这几个档次，各个城市分别有几家？其中占比又是多少？

9. 将点评、人均、口味、环境、服务这几个指标加工出一个综合评价系数，并且计算哪十家店是最好的（开放题）

10. 对全国类型为日本菜及日本料理的餐馆做一次描述性分析（开放题）

一、为什么要学习Excel

二、Excel的常见函数

文本清洗类
关联匹配类
逻辑运算类
计算统计类
时间序列类

（一）文本清洗函数

先导知识：

1 bit = 两种可能性，用0或1存储
1 byte = 8 bit，如00000001，一共有256种可能性
1 byte 可以存256个字符编码，最初的存储方式 ASCII 就存了英文+数字+符号
汉字远远大于256种可能性，于是用 2 byte 组合表示，叫做 GB2312
为了表示更多的汉字，用了 GBK，它是拓展版，还包括了繁体字
包括少数民族的文字：GB18030
万国码：Unicode

任何字符串 ——> 编码规则（utf-8、GB2312、ASCII等） ——> 一串01字符

常见的文本清洗函数：

Find
Left Right Mid
Concatenate
Replace
Substitute
Text
Trim
Len

操作：

1. 想把salary拆为两列，一列为下限（bottomSalary），一列为上限（topSalary）

首先对该列使用替换，将K替换为k

然后使用 FIND() 函数找到 k 的位置

知道 k 的位置后就可以使用 LEFT() 函数

不想要7k后面的k的话，函数改为 =LEFT(P2,Q2-1) 即可

提取出上限同理，首先使用 FIND() 函数找到 – 的位置

然后使用 RIGHT() 函数

想要去掉 – 和 k

法1：把该列复制一遍，再粘贴为值（这步很重要，否则后面替换会显示找不到）

使用替换功能，将 – 和 k 全部替换为空格

法2：首先使用 FIND() 函数找到 – 的位置

使用 LEN() 函数求出 salary 的长度

然后使用 MID() 函数（从中间开始截断）

text 就是 salary列（如7k-9k），start_num就是 -列+1（如3+1=4，第4位为9），num_chars就是len列－-列－1（如5－3－1=1）。即从9开始提取1位字符，故出来9

2. 求上限工资和下限工资的平均值

想要在数字后面加上 k ，两种方法：

=U2&"k"
=CONCATENATE(U2,"k")

3. REPLACE() 函数

=REPLACE(Y2,1,2,"k") 效果如下：

4. SUBSTITUTE() 函数

想从 “应届毕业生” 里提取 “应届” 两个字

这里 text 就是 “应届毕业生” ， old_text 就是 “毕业生”，new_text 为空格，即""

效果如下：

5. TEXT() 函数 将数字进行一定的格式转换，主要将数字转换成时间格式（更建议的方法是直接右键 —> 设置单元格格式）

6. TRIM() 函数 删除字符串中多余的空格（字符串前后的空格，字符串中间的空格删除不了）

7. LEN() 函数 计算文本中有多少个字符（由多个字符组成一个字符串）

（二）关联匹配函数

Lookup
Vlookup
Index
Match
Row
Column
Offset
Hyperlink

1. VLOOKUP() 函数

只通过搜索某一个值，来查找、返回区域里的对应值

比如我们想通过 companyId 查找到对应的 companyFullName

lookup_value即为对应的 companyId
col_index_num即为 companyFullName 所在的列（第2列）
table_array即为

2. INDEX() 函数

INDEX(区域，行，列)

3. MATCH() 函数

返回符合特定值特定顺序的项在数组中的相对位置

MATCH(查找的值，区域，0)

如果查找的值在区域中有重复值的话，只返回第一个位置

INDEX() 和 MATCH() 联合使用

需求：想要知道8对应的workYear

首先使用 MATCH() 找出8对应的位置

然后在外面嵌套一层 INDEX()

4. ROW() 函数 返回单元格在第几行

场景：随机抽样

COLUMN() 同理，不过返回的是列

5. Offset() 函数 偏移

以指定的引用为参照系，通过给定偏移量返回新的引用

往下偏移 rows（正）行，往右偏移 cols（正）列
往下是正的，往上是负的；往右是正的，往左是负的

6. Hyperlink() 函数 超链接

（三）逻辑运算类

True（数学运算中默认为1）
False（数学运算中默认为0）

True and True = True

True and False = False

True or False = True

False or False = False

应用场景：多条件判断

1. IF() 函数

如筛选出岗位薪资>6k，岗位名称为数据分析师，工作年限要求为应届毕业生的岗位

结果为3，说明3个条件都满足

如最低工资高于10的我们认为是高，否则是低 =IF(R2>10,"高","低")

R2为 bottomSalary 单元格

IF还可以嵌套使用，如：如果最低工资低于10，输出低；如果最低工资高于10，再判断是否要求应届毕业生，如是，就yes，否则no

=IF(R2>10,IF(U2="应届毕业生","yes","no"),"低")

2. IS() 系列函数

ISNUMBER() 判断是否为数字（常用在数据清洗里）
ISERROR() 是否报错

（四）计算统计类

SUM
SUMIF
Sumproduct：对应相乘，再相加
count：计数（不计算空单元格）
countif：条件计数（多个条件用 countifs）
Max Min
ABS：绝对值
Rank：查找某个数字的排名
Rand
Randbetween：随机
Average
Quartile：分位数
Stdev：标准差
Subtotal：万能函数
Int：向下取整（后面+1可以向上取整）
Round：四舍五入（第二个参数为0）

第二个参数为取整的范围，若为负数，则往十位数、百位数上面拓展

1. 应届毕业生的最低薪资求和

2. 薪资小于10k的那批岗位的平均薪资

（五）时间序列类

时间的本质是数字

Year
Month
Weekday：判断为一周中的周几（1为周日，2为周一）
Weeknum：本周是一年中的第几周
Day
Days：返回两个日期相隔的天数，第一个参数为end_date，第二个参数为start_date
Date(year,month,day)：拼接成一个日期
Now：返回当前时间，精确到秒
Today：返回当前日期

日期可以直接相减，返回相差的天数

三、Excel的常见技巧

（一）Excel的快捷键

Ctrl+方向键：光标快速移动
Ctrl+Shift+方向键：快速框选
Ctrl+空格键：选定整列
Shift+空格键：选定整行
Ctrl+A：选择整张表
Alt+Enter：换行

（二）分列、数据条、色阶

1. 将 salary 列拆成两列：

效果如下：

但是同时注意到会有一些有问题的数据：

将“以上”和“以下”全部替换为空格

筛选出 top 列里的空白：

然后复制 bottom 列

2. 开始 — 条件格式 — 数据条

数据条：把数字罗列的可视化

色阶：相关性的可视化

（三）数据透视表（插入里）

1. 想知道不同 workYear 的人的 avg薪资

结果如下：

2. 将不同 workYear 的人筛选出来放在不同的sheet里（数据透视表可以直接针对数据切片）

点击结果中的平均值，会自动筛选出来，如“10年以上”：

3. 工作3年以上，在不同城市的平均薪资

结果如下：

（四）视图

若在第3行点击“冻结首行”，则会冻结前两行

（五）数据分析

怎么打开数据分析功能框？

用Excel进行数据分析：数据分析工具在哪里？_东来东往的数据与产品的博客-CSDN博客_excel数据分析工具在哪里

文件 – 选项 – 加载项 – 转到 – 分析工具库勾选上

如对avg列做描述统计，输出选项为新工作簿

结果为：

（六）数据切片（多维分析）

1. 插入切片器，选择education

行是city，列是workYear，第三个维度education

2. 切片器可以和作图工具联合使用

3. 也可以使用多个切片器

（七）引用数据

公式 – 定义名称（以后引用数据的时候可以使用定义的别名）

（八）删除重复值

数据 – 删除重复值

对ID等唯一值进行数据清洗时使用较方便

（九）下拉菜单

先将上述删除重复值后得到的结果定义名称为workYear

然后：数据 – 数据验证 – 数据验证

效果如下：

（十）迷你图（在单元格里直接显示折线）

插入 – 折线

（十一）数组

对 avg 列分为 0-5 / 5-10 / 10-20 / 20以上四档，返回1/2/3/4

四、用Excel进行数据分析

1. 全国点评数最高的饭店是哪家？

法1：数据 – 排序 – 扩展选定区域 –

结果如下：

法2：对点评列使用 MAX 函数，然后再用 MATCH 函数找出最大值所在行数

再在 MATCH 函数前加一个 INDEX函数（查找想要的数据所在的区域）

得出结果：

若点评数相同的点名有多个，只返回第一个

2. 哪个城市的饭店人均口味最好？

插入 – 数据透视表

结果如下：

不建议直接在透视表上进行数据操作，复制粘贴成新的表格再操作

数据 – 排序

3. 哪个类型的餐饮评价最好？

略

4. 类型为川菜的店中，有多少个带“辣”字，又有多少个带“麻”字？

@的意思是不算C列后面没有数据的地方

对 “麻”列和 “类型”列均开始 – 筛选

对“辣”同理，结果为44个

5. 口味、环境、服务三个评价都在8.0以上的饭店有几家？它们在哪个城市的占比最多？

筛选 – 数字筛选 – 大于或等于8，共找出456家

把此时表中的城市列复制，粘贴到另一个表中，对其进行数据透视表即可

结果如下：

6. 上海地区中，各个类型饭店服务前五名？

先对城市列筛选出上海，把我们需要的数据复制到新表中

法1：对服务列做降序排序（数据-排序），再对类型列做一次降序排序，结果如下：

效果如下：

再过滤，筛选出前五名

优点：服务刚好并列的，只会把优先排在前面的算作第五名

法2：数据透视表

再使用 INDEX 和 MATCH 去做排名

7. 没有评价的饭店有几家？

count不对空值进行计数

对点评列使用COUNT函数，结果为4509。再把总的行数减去4509即可

8. 将人均价格划分成0-50 50-100 100-150 150-200 200+ 这几个档次，各个城市分别有几家？其中占比又是多少？

把人均列除以50得到新列，对得到的新列向上取整（int后再+1），结果如下：

1即为0-50，2即为50-100，以此类推

将城市列和level列复制到新表中：

对level列做筛选：

然后将筛选出的值全部替换为5：

对城市列和level列做数据透视表：

结果如下：

将数据透视表复制到新表中：

占比：

9. 将点评、人均、口味、环境、服务这几个指标加工出一个综合评价系数，并且计算哪十家店是最好的（开放题）

筛选（对人均去掉不合理的数值及空白值；口味、环境、服务等打分去掉空白值）

将筛选出的2851条数据粘贴到新表中

分析：口味、环境、服务、点评是正比，人均是降权

口味、环境、服务三列，给予主观权重后求和再求平均（1）
点评列数据范围很大，采用 lg（log函数，基底为10）可以使数据达到比较好的收敛（2）
人均也用 log函数，基底为4（3）

基底越大，log后差距越小，用户对该属性越不敏感

最后综合评价系数为（1）+（2）－（3）

对综合评价系数采用 MAX 和 MIN

归一化，让综合评价系数收敛到 0-1 之间：

结果如下：

哪十家店是最好的？

将上述得到的结果列，复制粘贴为数值，再数据-排序

10. 对全国类型为日本菜及日本料理的餐馆做一次描述性分析（开放题）

筛选：类型（文本筛选-开头是-日本）；点评、人均、口味、环境、服务均去掉空值。将得到的结果粘贴到新表中

数据-数据分析-描述统计

结果如下：

【第三周：Excel】7周成为数据分析师

第三周：Excel（P31-P42）

一、为什么要学习Excel

二、Excel的常见函数

（一）文本清洗函数

（二）关联匹配函数

（三）逻辑运算类

（四）计算统计类

（五）时间序列类

三、Excel的常见技巧

（一）Excel的快捷键

（二）分列、数据条、色阶

（三）数据透视表（插入里）

（四）视图

（五）数据分析

（六）数据切片（多维分析）

（七）引用数据

（八）删除重复值

（九）下拉菜单

（十）迷你图（在单元格里直接显示折线）

（十一）数组

四、用Excel进行数据分析

1. 全国点评数最高的饭店是哪家？

2. 哪个城市的饭店人均口味最好？

3. 哪个类型的餐饮评价最好？

4. 类型为川菜的店中，有多少个带“辣”字，又有多少个带“麻”字？

5. 口味、环境、服务三个评价都在8.0以上的饭店有几家？它们在哪个城市的占比最多？

6. 上海地区中，各个类型饭店服务前五名？

7. 没有评价的饭店有几家？

8. 将人均价格划分成0-50 50-100 100-150 150-200 200+ 这几个档次，各个城市分别有几家？其中占比又是多少？

9. 将点评、人均、口味、环境、服务这几个指标加工出一个综合评价系数，并且计算哪十家店是最好的（开放题）

10. 对全国类型为日本菜及日本料理的餐馆做一次描述性分析（开放题）

Published by

风君子

第三周：Excel（P31-P42）

一、为什么要学习Excel

二、Excel的常见函数

（一）文本清洗函数

（二）关联匹配函数

（三）逻辑运算类

（四）计算统计类

（五）时间序列类

三、Excel的常见技巧

（一）Excel的快捷键

（二）分列、数据条、色阶

（三）数据透视表（插入里）

（四）视图

（五）数据分析

（六）数据切片（多维分析）

（七）引用数据

（八）删除重复值

（九） 下拉菜单

（十）迷你图（在单元格里直接显示折线）

（十一）数组

四、用Excel进行数据分析

1. 全国点评数最高的饭店是哪家？

2. 哪个城市的饭店人均口味最好？

3. 哪个类型的餐饮评价最好？

4. 类型为川菜的店中，有多少个带“辣”字，又有多少个带“麻”字？

5. 口味、环境、服务三个评价都在8.0以上的饭店有几家？它们在哪个城市的占比最多？

6. 上海地区中，各个类型饭店服务前五名？

7. 没有评价的饭店有几家？

8. 将人均价格划分成0-50 50-100 100-150 150-200 200+ 这几个档次，各个城市分别有几家？其中占比又是多少？

9. 将点评、人均、口味、环境、服务这几个指标加工出一个综合评价系数，并且计算哪十家店是最好的（开放题）

10. 对全国类型为日本菜及日本料理的餐馆做一次描述性分析（开放题）

Published by

风君子

（九）下拉菜单