文章目录
数分工具的应用:
小白学习顺序
Excel 学习要点:
- Excel保证新版本(2013+)
- 培养好的数据表格习惯
- 主动性搜索
- 多练习
函数:
1 文本清洗函数
字符串:
拼接函数:concatenate(text,text)
将text中的old_text转换为new_text。
将值转换为时间格式
删除字符串前后多余的空格。
len() 计算字符串的长度。
2 关联匹配类
2.1 LOOKUP和VLOOKUP
=VLOOKUP(查找值,查找区域,返回查找区域第N列,查找模式)
搜索表区域首列满足条件的元素,确定待检索单元格在区域中的行序号,再进一步返回选定单元格的值。默认情况下,表是以升序排序的
2.2 INDEX和MATCH
INDEX:在给定的单元格区域中,返回特定行列交叉处单元格的值或引用
MATCH:返回符合特定值特定顺序的项在数组中的相对位置
组合:返回选定值在表格中对应的另一个值
INDEX(表格,MATCH(值,表格,0),另一个值)
2.3 ROW和COLUMN
ROW():返回一个引用的行号
COLUMN():返回一个引用的列号
2.4 OFFSET
以1所在的S2为参考,偏移一行一列,大小为一个单元格,所以结果为4。负数也可以,向上向左偏移。
2.5 HYPERLINK
HYPERLINK:超链接,创建一个快捷方式或链接,以便打开一个存储在硬盘、网络服务器或Internet上的文档
3 逻辑运算类
if:判断是否满足某个条件,如果满足返回一个值,如果不满足则返回另一个值。
is可以用来查找是否是数字或者逻辑值等等,用来数据清洗。
4 计算统计类
- sum:求和
- sumproduct:两列先相乘,结果求和。
- count:计数,为空的不计算在内
- max/min/average:某个区域内的最大值/最小值/平均值
- rank:返回某数字在一列数字中相对于其他数值的大小排名;如果多个数值排名相同,则返回平均值排名
- rand:返回大于或等于0且小于1的平均分布随机数(依重新计算而变)
- randbetween:返回一个介于指定的数字之间的随机数
quatile:基于从0到1之间(不含0与1)的百分点值,返回一组数据的四分位点,即统计所选数据的几分位点数。
- stdev:计算基于给定的样本总体的标准偏差(忽略逻辑值及文本)
subtotal:返回一个数据列表或数据库的分类汇总
- int:将数值向下取整为最接近的整数
- round:按指定的位数对数值进行四舍五入
- countifs:统计—组给定条件所指定的单元格数
5 时间序列类
Year:返回日期的年份值,一个1900-9999之间的数字。
Month:返回月份值,是一个1(一月)到12(十二月)之间的数字。
Day:返回一个月中的第几天的数值,介于1到31之间。
Weekday:返回代表一周中的第几天的数值,是一个1到7之间的整数。
weeknum:返回—年中的周数
Now:返回日期时间格式的当前日期和时间。
Today:返回日期格式的的当前日期。
Date:返回在Microsoft Excel日期时间代码中代表日期的数字,将分开的年月日组合起来
Days:返回两个日期之间的天数。
6 Excel的常见技巧
快捷键:
定义名称:将一列数据定义为一个名称,通过调用这个名称就可以对整列进行操作
avg(avg)就是对整列求均值
数据
→
\rightarrow
→删除重复值
→
\rightarrow
→就可以知道某一列非重复的值有多少,也就是分类
数据
→
\rightarrow
→数据验证
→
\rightarrow
→验证条件选择序列,就可以实现在下拉菜单中选择
迷你图:点击需要插入表格的单元格,插入
→
\rightarrow
→折线图
7 用Excel进行数据分析
- 点评数最高的饭店
直接对点评数进行排序
或者使用max()找到最大值,使用match(max(),区域,0)找到最大值所在位置,然后用index(区域,match(max(),区域,0),1)找到匹配的店铺名。
2. 哪个城市人均口味最好
建立数据透视表,口味设为均值 ,然后对均值进行排序
排序后的结果:
-
哪个类型的餐饮评价最好
-
类型为川菜的店中,有多少个带「辣」字,又有多少个带「麻」字?
筛选带‘麻’、‘辣’的字段,然后加入筛选器,空值不选,类型选择 川菜,最后看下面的计数。
麻
辣
减去首行的一行,最终结果减1。 -
口味、环境、服务,三个评价都在8.0以上的饭店有几家?它们在哪个城市的占比最多?
在筛选器里选择 → \rightarrow →数字筛选 → \rightarrow →大于
三个都筛选完,查看计数
然后使用数据透视表,查看城市的计数
排序
-
上海地区中,各个类型饭店服务前五名?
使用筛选器,首先选择城市为上海,然后对类型和服务进行排序
使用if语句设置每个类型的店铺有几家
再使用筛选器,选择数量大于5的店铺 -
没有评价的饭店有几家?
使用count()函数,count函数不计算空值,然后总数减去count()的值。 -
将人均价格划分成0-50 ,50-100,100-150,150- 200,200+这几个档次,各个城市分别有几家?其中占比又是多少?
将人均除以50再向上取整,然后使用数据透视表 -
将点评、人均、口味、环境、服务这几个指标加工出一个综合评价系数,并且计算哪十家店是最好的(开放题).
对口味、环境、服务加权求均值,人均求LOG(),评分求LOG来削弱影响,然后对三个结果进行加权,最后归一化排名,取分数最高的十家。