秦路数据分析excel笔记1函数

常见文本清洗函数

乱码问题：txt打开后再复制到excel 或使用其他转编码工具

 

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:https://blog.csdn.net/Reedw1023/article/details/104958465

 

lookup:

=LOOKUP（lookup_value，lookup_vector，result_vector）

lookup_value意思为我们要在数据表中查找的“值”

lookup_vector意思为我们要查找的值的“数据表”

result_vector意思为我们通过数据表想要得到的"值“

 

row:

=row(reference)

如果省略reference，则默认返回row函数所在单元格的行数。

 

column:

=column（reference) 

如果省略reference，则默认返回函数column所在单元格的列数

 

offset:

=OFFSET(Reference, Rows, Cols, [Height], [Width])

OFFSET(引用单元格, 行数, 列数, [返回引用高度], [返回引用宽度])

参数Rows——行偏移量

正数（代表在参照单元格的下方），负数（代表在参照单元格的上方）。

参数Cols——列偏移量 

正数（代表在参照单元格的右边）或负数（代表在参照单元格的左边）。

参数Height——返回几行（必须为正数），即所要返回的引用区域的行数。

参数Width——返回几列(必须为正数），即所要返回的引用区域的列数。

 

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:平均值

quartile:四分位数

stdev:标准差

substotal:=Subtotal(功能代码，数值区域) 可执行多种操作

int:取整

round:按照指定位数四舍五入

 

时间序列函数

year:提取年份

month:提取月份

day:提取日

date:合并为日期

weekday:返回一周中第几天

now:当前系统时间

weeknum:一年中是第几个星期

today:当前系统日期