第三周

为什么要学习excel
excel首先是一个好用的工具
不因为你会python而成为数据分析师，而是能用任何工具解决问题
exce，sql 敏捷、快速、需要立即响应的需求
python，bi，etl， 常规，频繁。

excel函数 -> sql函数 -> python函数
excel保证新版本（2013+）
培养好的数据表格习惯
主动性搜索
多练习

字符串概念

 

 

什么是函数
excel常见函数

文本清洗类
find 返回一个字符串在另一个字符串中出现的起始位置

例子

假设我们要找出 7k-9k(假设位置是在Q2) 中第一次出现k的位置

那我们可以使用 =find("k",Q2,1)

substitute

将字符串部分字符用新的字符代替

SUBSTITUTE(text, old_text, new_text, [instance_num])

Text  必需。需要替换其中字符的文本，或对含有文本（需要替换其中字符）的单元格的引用。

Old_text  必需。需要替换的旧文本。

New_text  必需。用于替换 old_text 的文本。

Instance_num  可选。用来指定要以 new_text 替换第几次出现的 old_text。如果指定了 instance_num，则只有满足要求的 old_text 被替换；否则会将 Text 中出现的每一处 old_text 都更改为 new_text。

例子：

假如我们想要替换7k-9k（位置为Q2）中的9替换为10，

=substitute(Q2,9,10)

left right mid  从一个字符串的左边/中间/右边开始返回指定个数的字符

例如，7k-9k（位置为Q2），我们想要返回第一个字符

=left(Q2,1)      PS:这个函数一般都是和其他函数连用的

例如有一堆数据 7k-9k,11k-12k

那我们想要返回k前面的数字

可以使用 =left(Q2,find("k",Q2)-1)

text

这个没什么，只是将数值转化为文本

concatenate

将多个字符串合并为一个字符串。其实&也能起到同样的作用，但是concatenate操作起来比较舒服，可以用:直接合并多个

trim

删除字符串多余的空格，但是会在英语字符串中间保留一个空格作为分隔符（用处不大）

replace

将一个字符串的部分字符用另一个字符串代替

=REPLACE(U11,1,2,"小林")
len

查看长度

 

关联匹配类

lookup

LOOKUP(lookup_value, lookup_vector, [result_vector])

lookup_value  必需。LOOKUP 在第一个向量中搜索的值。Lookup_value 可以是数字、文本、逻辑值、名称或对值的引用。

lookup_vector  必需。只包含一行或一列的区域。lookup_vector 中的值可以是文本、数字或逻辑值。

lookup_vector 中的值必须以升序排列：...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE。否则，LOOKUP 可能无法返回正确的值。大写文本和小写文本是等同的。

result_vector  可选。只包含一行或一列的区域。result_vector 参数必须与 lookup_vector 大小相同。

即第一个值是搜索的值，第二个是搜索的值所在的列，第三个是想要返回该值所在行的列

例如：

 

我想要返回应届毕业生 则我可以使用

=lookup(R2,R:R,T:T)  PS:R:R 表示R整列

当然，也可以模糊匹配，如果该值不在列中，则找到列中与其距离最近且比其低的值

 

vlookup

vlookup函数一般用于在多个表中寻找

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

第一个是搜索的值，第二个是想要搜索的列和行，第三个是返回第几行，第四个是是否精确匹配（0表示是，1表示否）

例如：

该数据在sheet1中

我们想要返回公司的名字

=VLOOKUP(D2,Sheet1!A:B,2,FALSE)

 

index

INDEX(array, row_num, [column_num])

选择一个多行多列的数字，然后返回第几行第几列

 

match

MATCH(lookup_value, lookup_array, [match_type])

第一个是想要查看的数字，第二个是一个数组，第三个默认为1，返回的是数字所在的第几行（或者第几列）

 

row

返回行数

 

column

返回列数

 

offset（少用）

偏移量 

 

hyperlink

例如超链接。。

逻辑运算类（这些一般都认识，所以就不写了）

and

or

if

is

not 

false true

计算统计类

sum  总和

sumproduct   返回的是乘积。两个数组，相乘

例如

=SUMPRODUCT(R2:R3,S2:S3) = 168

 

count

统计出现的次数

max min

最大值和最小值

rank

排名

有三个参数，第一个是所要排名的值，第二个是所在区域，第三个是1（升序）/0（降序）

可以和其他函数合用

例如

想要寻找区域中小于10的值，然后2在其中的排名

=RANK(R2,IF(R2:R22<10,R2:R22),1)

Rand Randbetween

rand() 0-1之间的数

randbetween(1,5)  1-5之间的数

Average

平均值

Quartile

四分位数

stdev

标准偏差

substotal

 

int

向下取整

round（值，取几位小数点）

四舍五入

时间序列类

year

month

day

date(1990,10,10)

now

today

weekday：返回一周中的第几天

weeknum：返回一年中的周数