作者:xxw9485
时间:2018/3/1
Excel常用函数
下面是本人之前学习和总结的一些Excel的常用函数,为了方便查看,现整理于此。
清洗处理类
- 函数:Trim(text)
用途:删除字符串中多余的空格,但在英文字符串中保留一个作为词与词之间分隔的空格,text表示要删除的字符串。 - 函数:Concatenate(单元格1,单元格2……)
用途:合并单元格中的内容,类似&的用法。 - 函数:Replace(指定字符串,哪个位置开始替换,替换几个字符,替换成什么)
用途:替换单元格的部分字符串。 - 函数:Substitute(需要替换的文本,旧文本,新文本,第N个旧文本)
用途:替换单元格的部分字符串。 - 函数:Left/Right(指定字符串,截取长度)
用途:截取指定字符串的部分内容。 - 函数:Mid(指定字符串,开始位置,截取长度)
用途:截取指定字符串的部分内容。 - 函数:Len/Lenb(指定字符串)
用途:返回字符串的长度,在len中,中文计算为一个,在lenb中,中文计算为两个。 - 函数:Find/Search(要查找字符,指定字符串,第几个字符)
用途:查找某字符串出现的位置,可以指定为第几次出现,与Left/Right/Mid结合能完成简单的文本提取。find区分大小写,search不区分。
关联匹配类
- 函数:Lookup(text,区域1,区域2)
用途:返回text在区域1中的位置对应区域2的值。 - 函数:Vlookup(查找的值,哪里找,找哪个位置的值,是否精准匹配)
用途:功能类似lookup()。 - 函数:index(查找的区域,区域内第几行,区域内第几列)
用途:返回某个位置的值,与match组合后功能类似于vlookup()。 - 函数:Match(查找指定的值,查找所在区域,是否精准匹配)
用途:返回值所在的位置。 - 函数:Row(单元格)
用途:返回单元格所在的行。 - 函数:Column(单元格)
用途:返回单元格所在的列。 - 函数:Offset(指定点,偏移多少行,偏移多少列,返回多少行,返回多少列)
用途:建立坐标系,以坐标系为原点,返回距离原点的值或者区域。正数代表向下或向左,负数则相反。
逻辑运算类
- 函数:If(表达式,值1,值2)
用途:当表达式成立时返回值1,不成立时返回值2。 - 函数:And()
用途:全部参数为True,经常用于多条件判断。 - 函数:Or()
用途:只要参数有一个True,则返回True,经常用于多条件判断。 - 函数:Is()
用途:常用判断检验,返回的都是布尔数值True和False。常用ISERR,ISERROR,ISNA,ISTEXT,可以和IF嵌套使用。
计算统计类
- 函数:Sum/Sumif/Sumifs()
用途:统计满足条件的单元格总和,SQL有同名函数。 - 函数:Sumproduct()
用途:统计总和相关,如果有两列数据销量和单价,现在要求销售总额,用sumproduct是最方便的。 - 函数:Count/Countif/Countifs()
用途:统计满足条件的字符串个数。 - 函数:Max()
用途:返回数组或引用区域的最大值。 - 函数:Min()
用途:返回数组或引用区域的最小值。 - 函数:Rank(指定值,引用区域,排序方式)
用途:排序,返回指定值在引用区域的排名,重复值同一排名。 - 函数:Rand/Randbetween()
用途:常用随机抽样,前者返回0~1之间的随机值,后者可以指定范围。 - 函数:Averagea()
用途:求平均值,当出现True、False以及文本时,True表示1、False表示0、文本表示0,也有Averageaif,Averageaifs。Average()则只计算已有数字。 - 函数:Quartile(指定区域,分为参数)
用途:求指定区域内的四分位数,参数为0代表最小值,参数4代表最大值,1~3对应25、50(中位数)、75分位。 - 函数:Stdev()
用途:求标准差。 - 函数:Substotal()
用途:汇总型函数,将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化。 - 函数:Int/Round()
用途:取整函数,int向下取整,round按小数位取数。
时间序列类
- 函数:Year()
用途:返回日期中的年。 - 函数:Month()
用途:返回日期中的月。 - 函数:Weekday(指定时间,参数)
用途:返回指定时间为一周中的第几天,参数为1代表从星期日开始算作第一天,参数为2代表从星期一开始算作第一天。 - 函数:Weeknum(指定时间,参数)
用途:返回一年中的第几个星期,后面的参数类同weekday,意思是从周日算还是周一。 - 函数:Day()
用途:返回日期中的日。 - 函数:Date(年,月,日)
用途:时间转换函数,等于将year(),month(),day()合并。 - 函数:Now()
用途:返回当前时间戳,动态函数。 - 函数:Today()
用途:返回今天的日期,动态函数。 - 函数:Datedif(开始日期,结束日期,参数)
用途:日期计算函数,计算两日期的差。参数决定返回的是年还是月等。