所需数据:DataAnalyst.csv
链接:https://pan.baidu.com/s/1d0b6M6vGX5lXeySCOKk0kA
提取码:697s
目录
一、excel—常见的文本清理函数
二、数据来源与介绍
三、薪资处理
一 常见文本清洗函数
乱码问题:txt打开后再复制到excel 或使用其他转编码工具
打开csv,可以另存为excel类型,即以xlsx结尾
- 常见函数
find:确定字符位置
=find(find_text,within_text,start_num)
Find(要查找的文本,文本所在的单元格,从第几个字符开始查找
[可选,省略默认为1,从第一个开始查找])
substitute:替换字符
=substitute(text,old_text,new_text,[instance_num])
=substitute(需要替换的文本,旧文本,新文本,第N个旧文本)
left right mid:对单元格内容进行截取。从左/右/中第一个字符开始截取,截取指定的长度
=left(text,num_chars)
text:将数值转化为自己想要的文本格式
=text(value,format_text)
concatenate:把多个字符文本或数值连接在一起,实现合并的功能
=concatenate(text1, [text2], ...)
Text1,Text2可以是文本或者数值
trim:去掉字符前后和字符之间的空格,在去掉字符之间的空格时,不会把全部空格都去掉,会留下一个空格
如果文本中包含有不可见的非空格元素,则TRIM 无法去除,可以使用SUBSTITUTE函数来进一步去除
replace:用新字符串替换旧字符串,而且替换的位置和数量都是指定的
=Replace(old_text,start_num,num_chars,new_text)
=replace(要替换的字符串,开始位置,替换个数,新的文本)
len:返回文本串的字符数
- 关联匹配函数
vlookup/index/match区别:
秦路数据分析excel笔记1函数_Reedw1023的博客-CSDN博客blog.csdn.net![de579b3b965238eb5f367d5c03b462cd.png](https://i-blog.csdnimg.cn/blog_migrate/f2a6eb707bd93a610eaa816d57ed29cc.jpeg)
lookup:
=LOOKUP(lookup_value,lookup_vector,result_vector)
lookup_value 我们要在数据表中查找的“值”
lookup_vector 我们要查找的值的“数据表”
result_vector 我们通过数据表想要得到的"值“
应用于划分分组
=LOOKUP(8,{0,5,10,15,20},{1,2,3,4})
显示为2
row:
=row(reference)
如果省略reference,则默认返回row函数所在单元格的行数。
column:
=column(reference)
如果省略reference,则默认返回函数column所在单元格的列数
offset:
=OFFSET(Reference, Rows, Cols, [Height], [Width])
OFFSET(引用单元格, 行数, 列数, [返回引用高度], [返回引用宽度])
参数Rows——行偏移量
正数(代表在参照单元格的下方),负数(代表在参照单元格的上方)。
参数Cols——列偏移量
正数(代表在参照单元格的右边)或负数(代表在参照单元格的左边)。
参数Height——返回几行(必须为正数),即所要返回的引用区域的行数,一般为1。
参数Width——返回几列(必须为正数),即所要返回的引用区域的列数,一般为1。
hyperlink:
=HYPERLINK(link_location,[friendly_name])
Link_location 可以作为文本打开的文档的路径和文件名
Friendly_name 非必要参数,可以不存在。单元格中显示的跳转文本或数字值
- 逻辑运算函数
True False And Or IF Is Not
- 计算统计函数
sum:求和
sumproduct:多数组对应元素相乘后相加
count:计数 只计算数字型
counta:计数 非空单元格
max min:最大值 最小值
rank:得到单元格在数组中的排序
rand/randbetween:生成随机数
averagea:平均值
stdev:标准差
quartile:四分位数(统计学中使用较多)
substotal:=Subtotal(功能代码,数值区域) 可执行多种操作
int:向下取整
round:按照指定位数四舍五入
- 时间序列函数
year:提取年份
month:提取月份
day:提取日
date:合并为日期
=date(year,month,day)
weekday:返回一周中第几天
=weekday(“日期”,2)
西方习惯将星期日作为一周中的第一天,我们是星期一作为一周中的第一天,故参数为2
now:当前系统时间
weeknum:一年中是第几个星期
today:当前系统日期
二、数据来源与介绍
数据来源于秦路-数据分析入门https://www.bilibili.com/video/BV1MW411975G?p=34
如图1所示,通过网络爬虫从某招聘网站上获取招聘数据分析师的各项信息
![ee3adb7e1cc66fd4798ac4321d9b567e.png](https://i-blog.csdnimg.cn/blog_migrate/589a49f4e32f1cd8fd1409b0e49556d2.jpeg)
图1
三、薪资处理
目的:通过salary薪资区间得到最低薪资bot_salary和top_salary
秦路老师使用的是函数find+left+right的方法,这里提供一个更为简便的方法。
首先利用k进行分列处理,如图2—图4所示:
复制出一列salary,插入一列空白列——选中除了salary的数据,点击分列——勾选其他,输入k——点击下一步,下一步——分列成功
然后进行替换‘-’为空白,写上表头,如图5所示。
![143d770cff9c2155800fab21352c751b.png](https://i-blog.csdnimg.cn/blog_migrate/0848a0dbfddfedda34dccae13dbd6e3b.png)
图2
![98fc41ea724d4f75e2ee28355cbadacf.png](https://i-blog.csdnimg.cn/blog_migrate/38042c3dd2e84a5fa7a590cd7887b606.png)
图3
![a09425a1fec7a7675ee06ec4d8e8b7b7.png](https://i-blog.csdnimg.cn/blog_migrate/d48885d2b6af5bd9e42bfc122050fad4.png)
图4
原文链接:
秦路数据分析excel笔记1函数_Reedw1023的博客-CSDN博客blog.csdn.net![de579b3b965238eb5f367d5c03b462cd.png](https://i-blog.csdnimg.cn/blog_migrate/f2a6eb707bd93a610eaa816d57ed29cc.jpeg)