Excel常见函数

[TOC]

清洗处理类

  • 主要是文本、格式以及脏数据的清洗和转换

Trim

  • 清楚调字符串两边的空格
  • MySQLl中有同名函数,Python有类似函数strip

Concatenate

1
=Concatenate(单元格1,单元格2,...)
  • 合并单元格中的内容,还有另外一个合并方式是&。”我”&”很”&”帅” = 我很帅。当需要合并的内容过多时,concatenate的效率快也优雅。

Replace

1
=Replace(指定字符串,开始替换的位置,替换几个字符,替换成什么)
  • 替换掉单元格的字符串,清洗使用较多。
  • 实例:
1
2
# 替换手机号后四位为****
=replace(A2,8,4,'****')

Substitute

  • 和replace接近,区别是进行全局替换,没有起始位置的概念

Left/Right/Mid

1
=Mid(指定字符串,开始位置,截取长度)
  • 截取字符串中的字符,Left/Right(指定字符串,截取长度)。left为从左,right为从右。

Len/Lenb

  • 返回字符串的长度,在len中,中文计算为一个,在lenb中,中文计算为两个。

Find

1
=Find(要查找字符,指定字符串,第几个字符)
  • 查找某字符串的位置,可以指定位第几次出现,与left/right/mid结合能完成简单的文本提取。
  • 和find类似,区别是Search不区分大小写,支持*通配符

关联匹配类

  • 在进行多表关联或者进行行列对比是用到一下函数。

Lookup

1
=Lookup(查找的值,值所在的位置,返回相应位置的值)
  • 功能性和Vlookup一样,但是引申有数组匹配和二分法。

Vlookup

1
=Vlookup(查找的值,哪里查找,找哪个位置的值,是否精准匹配)
  • 通俗理解:查找到某个值然后粘贴过来

Index

1
=Index(查找的区域,区域内第几行,区域内第几列)
  • 和Match组合,媲美Vlookup,但是功能更强大。

Match

1
=Match(查找指定的值,查找所在区域,查找方式的参数)
  • 和Vlookup类似,但是可以按照指定方式查找,比如大于、小于或等于,返回值所在的位置。

Row

  • 返回单元格所在的行

Column

  • 返回单元格所在的列

Offset

1
=Offset(指定点,偏移多少行,偏移多少列,返回多少行,返回多少列)
  • 建立坐标系,以坐标系位原点,返回距离原点的值或者区域。正数代表向下或者向左,负数则相反。

逻辑运算类

  • 数据分析中不得不用到逻辑运算,逻辑运算返回的是布尔类型,True和False。很多复杂的数据分析会涉及到较多的逻辑运算。

If

  • 经典的如果但是,在Python中也进场使用,会有许多更优雅的写法。有ifs用法,取代if(end())的写法。

And

  • 全部参数位True,则返回True,经常用于多条件判断。

Or

  • 只要参数有一个True,则返回True,经常用于多条件判断。

IS系列

  • 常用判断检验,返回的多是布尔数值True和False。常用ISERR,ISERROR,ISNA,ISTEXT,可以和IF嵌套使用。

计算统计类

  • 常用的基础计算、分析、统计函数,以描述性统计为准。

Sum /Sumif /Sumifs

  • 统计满足条件的单元格总和。

Sumproduct

  • 统计总和相关,在给定的几组数组中,然后把数组间对应的元素相乘,最后返回乘积之和。例如,有两列数据销量和单价,现在要求卖出总额,用sumproduct最方便。

Count /Countif /Countifs

  • 统计满足条件的字符串个数。

Max

  • 返回数组或引用区域的最大值。

Min

  • 返回数组或引用区域的最小值。

Rank

  • 排序,返回指定值在引用区域的排名,重复值同一排名。

Rand /Randbetween

  • 常用随机抽样,前者返回0~1之间的随机值,后者可指定范围。

Averagea

  • 求平均值,也有Averageaif,Averageaifs。

Quartile

1
=Quartile(指定区域,分位参数)
  • 计算四分位数,比如1~100的数字中,25分位就是按从小到大排列,在25%位置的数字,即25。参数0代表最小值,参数4代表最大值,1~3对应25、50(中位数)、75分位

Stdev

  • 求标准差,统计型函数。

Substoal

1
=Substotal(引用区域,参数)
  • 汇总型函数,将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化,换言之,只要会了这个函数,上面的都可以抛弃掉。

Int /Round

  • 取整函数,int向下取整,round按小数位取数。
  • round(3.14159,2) = 3.14
  • round(3.14159,1) = 3.1

时间序列类

  • 专门用于处理时间格式以及转换,时间序列在金融、财务等数据分析中占有较大比重。

Year

  • 返回日期中的年。

Month

  • 返回日期中的月。

Weekday

1
=Weekday(指定时间,参数)
  • 返回指定时间位一周中的第几天,参数位1代表从星期日开始算第一天,参数2代表从星期一开始算第一天。

Weeknum

1
=Weeknum(指定时间,参数)
  • 返回一年中的第几个星期,后面的参数类同weekday。

Day

  • 返回日期中的日。

Date

1
=Date(年,月,日)
  • 时间转换函数,等于将year(),month(),day()合并。

Now

  • 返回当前时间戳,动态函数

Today

  • 返回今天的日期,动态函数

Datedif

1
=Datedif(开始日期,结束日期,参数)
  • 日期计算函数,计算两日期的差,参数决定返回的是年还是月。