为什么学习Excel?
Excel函数(用图形界面了解函数)——>SQL函数(用封装好的脚本语言了解函数)——>Python函数(了解函数)
函数:文本清洗类、关联匹配类、逻辑运算类、计算统计类、时间序列类
常见的文本清洗函数
字符串编码:https://www.liaoxuefeng.com/wiki/0014316089557264a6b348958f449949df42a6d3a2e542c000/001431664106267f12e9bef7ee14cf6a8776a479bdec9b9000
Find Substitute
Left Right Mid Text
Concatenate Trim
Replace Len
find
=FIND(find_text,within_text,start_num)
=FIND(要查找的字符串,要查找的区域,指定开始进行查找的字符数)
比如Start_num为1,则从单元格第一个字符开始查找关键字。如果忽略start_num,则假设其为1
结果返回数字,即所查找字符串的首次出现位置。
left
=LEFT(text,num_chars)
=LEFT(截取的单元格内容,从左开始截取的字符数(默认为1))
结果返回字符串
right
=RIGHT(text,num_chars)
=RIGHT(截取的单元格内容,从右开始截取的字符数(默认为1))
结果返回字符串
mid
=MID(text,Start_num,num_chars)
=MID(截取的单元格内容,开始截取位置,截取字符个数)
结果返回字符串
mid(A1,3,5)的意思是:取A1这个数从第3个数字开始总共5位。
concatenate
=concatenate(text1, [text2], ...)
Text1,Text2可以是文本或者数值。最多为 255 项,但是项与项之间必须用逗号隔开。
replace
=Replace(old_text,start_num,num_chars,new_text)
=replace(要替换的字符串,开始位置,替换个数,新的文本)
注意:第四个参数是文本,要加上引号。
返回结果为字符串
substitute
=substitute(text,old_text,new_text,[instance_num])
=substitute(需要替换的文本,旧文本,新文本,第N个旧文本)
返回结果为字符串
参数Instance_num ——为一数值,用来指定以 new_text (新文本)替换第几次出现的 old_text(旧文本)。
参数Instance_num 可省略,这表示用 new_text(新文本)替换掉所有的old_text(旧文本)。
substitute函数经常用来去掉文本之间的空格。 SUBSTUTUTE(A1," ","")表示把A1单元格空格去掉
replace和substitute的区别
replace要指定第几个到第几个字符(数字),substitute则是找到包含的老字符,替换成新字符。
text
=TEXT(value,format_text)
Value 为数值、计算结果为数字值的公式,或对包含数字值的单元格的引用。
Format_text 为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。
简单的说,语法结构可以理解为:TEXT(数值,单元格格式)
trim
=TRIM(text) 用来删除字符串前后的单元格,但是会在字符串中间保留一个作为连接用途。
len
=LEN(text)
=LEN(字符串)
返回字符串的长度
关联匹配函数
Lookup
=LOOKUP(lookup_value,lookup_vector,reslt_vec)
=LOOKUP(要查找的值,查找值得表,返回值得表)
返回的是返回表中对应的那个值。(查找值在表中要按升序排列)
分组:LOOKUP(8,{0,5,10,20},{1,2,3,4}) 返回2 把8在5~10 level2
vlookup
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
index
(https://jingyan.baidu.com/article/63acb44ae54c2f61fcc17eed.html)
数组形式——INDEX(array,row_num,column_num)和
引用形式——INDEX(reference,row_num,column_num,area_num)。
数组形式通常返回数值或数值数组;引用形式通常返回引用。
Array为单元格区域或数组常数;Row_num为数组中某行的行序号,函数从该行返回数值。Column_num是数组中某列的列序号,函数从该列返回数值。
Reference是对一个或多个单元格区域的引用,如果为引用输入一个不连续的选定区域,必须用括号括起来。Area_num是选择引用中的一个区域,并返回该区域中row_num和column_num的交叉区域。
match
=MATCH(lookup_value,lookuparray,match-type)
lookup_value:表示查询的指定内容;lookuparray:表示查询的指定区域;match-type:表示查询的指定方式,用数字-1、0或者1表示。
1:小于或等于的最大值,必须升序
0:等于
-1:大于或等于的最小值,必须降序
逻辑运算函数
And Or IF Is Not False True
计算统计函数
sum
求和
sumproduct
乘积之和,在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。
用函数SUMPRODUCT计算符合多条件的数据和,基本格式是:SUMPRODUCT(条件1*条件2*……,求和数据区域)
sumif
=SUMIF(range,criteria,sum_range)
=SUMIF(条件区域,求和条件,实际求和区域)
第二个求和条件参数在第一个条件区域里。
count
计数
countif
条件计数(区域,条件,)
Rank
查找某个数字的排名
average
平均数
Quartile
四分位数
stdev
标准差
int
数字取整
round
四舍五入
时间序列函数
Year
Month
Weekday
一周中的周几
Weeknum
Days
日期相减
Date
Now
Today
Excel常见技巧
快捷键
数据条、色阶(相关性可视化)
数据透视表
EXCEL工具
数据分析(描述统计等)
数据切片
定义名称
下拉单元格(数据约束等)
迷你图(简单的趋势)