在没有学习数据分析前,一直认为Office办公三件套不如iWork好用。直到后来学了用Excel做数据分析,才发现这绝对是个神器(虽然某些时候仍然很难用)。学习中用到的一些函数,整理一下。

清洗处理类

主要是文本、格式以及脏数据的清洗和转换。很多数据并不是直接拿来就能用的,需要经过数据分析人员的清理。数据越多,这个步骤花费的时间越长。

Trim

清除掉字符串两边的空格。
MySQL有同名函数,Python有近似函数strip。

Concatenate

=Concatenate(单元格 1,单元格 2……)
合并单元格中的内容,还有另一种合并方式是 &。"我"&"很"&"帅"=我很帅。当需要合并的内容过多时,concatenate 的效率快也优雅。
MySQL有近似函数concat。
=CONCATENATE(A1,A2,A3)=A1&A2&A3
=A1&””&A2&A3

Replace

=Replace(指定字符串,哪个位置开始替换,替换几个字符,替换成什么)
替换掉单元格的字符串,清洗使用较多。
MySQL中有同名函数,Python中有同名函数。

Substitute

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

Left/Right/Mid

=Mid(指定字符串,开始位置,截取长度)
截取字符串中的字符。Left/Right(指定字符串,截取长度)。left 为从左,right 为从右,mid 如上文示意。
MySQL中有同名函数。
=LEFT(A1,1):从第左边开始取,取一个长度。
=RIGHT(A1,1):从右边开始取,取一个长度。
=MID(A1,2,1):从第二个开始取,取一个长度。

Len/Lenb

返回字符串的长度,在 len 中,中文计算为一个,在 lenb 中,中文计算为两个。
MySQL中有同名函数,Python中有同名函数。
=LEN(C1)
=RIGHT(A1,LEN(A1)-1)

Find

=Find(要查找字符,指定字符串,第几个字符)
查找某字符串出现的位置,可以指定为第几次出现,与 Left/Right/Mid 结合能完成简单的文本提取
MySQL中有近似函数find_in_set,Python中有同名函数。

和 Find 类似,区别是 Search 大小写不敏感,但支持*通配符

Text

将数值转化为指定的文本格式,可以和时间序列函数一起看


关联匹配类

在进行多表关联或者行列比对时用到的函数,越复杂的表用得越多。多说一句,良好的表习惯可以减少这类函数的使用。

Lookup

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

Vlookup

=Vlookup(查找的值,哪里找,找哪个位置的值,是否精准匹配)
Excel 第一大难关,因为涉及的逻辑对新手较复杂,通俗的理解是查找到某个值然后黏贴过来。
=VLOOKUP(A12,B2:E8,2,0)

Tips:2 代表要查询的目标值距离 A12 值得相对位置。0 位精确匹配,1 为模糊匹配。只能匹配从匹配值开始右边的列。
Tips: 通过 “数据验证” 工具将某个值变成下拉列表。

Index

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

Match

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

Row

返回单元格所在的行

Column

返回单元格所在的列

Offset

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


逻辑运算类

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

IF

经典的如果但是,在后期的 Python 中,也会经常用到,当然会有许多更优雅的写法。也有 ifs 用法,取代 if(and()) 的写法。
MySQL中有同名函数,Python中有同名函数。
=IF(A1<200,5%,IF(A1<1000,10%))
Tips:IF(判断条件, 成立值, 不成立值);用$(F4) 锁定不可变的值。

And

全部参数为 True,则返回 True,经常用于多条件判断。
MySQL中有同名函数,Python中有同名函数。

Or

只要参数有一个 True,则返回 Ture,经常用于多条件判断。
MySQL中有同名函数,Python中有同名函数。

IS 系列

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


计算统计类

常用的基础计算、分析、统计函数,以描述性统计为准。具体含义在后续的统计章节再展开。

Sum/Sumif/Sumifs

统计满足条件的单元格总和,SQL 有中同名函数。
MySQL中有同名函数,Python中有同名函数。
=SUMIFS(C1:C12,A1:A2,” 李*”,B1:B2,” 员工”)
=SUMIF(A1:A2,” 李*”,C1:C12)
Tips:SUMIFS把求和列写在前面,条件在后面;SUMIF把条件写在前面,求和列在后面

Sumproduct

统计总和相关,如果有两列数据销量和单价,现在要求卖出增加,用 sumproduct 是最方便的。
MySQL中有同名函数。

Count/Countif/Countifs

统计满足条件的字符串个数
MySQL中有同名函数,Python中有同名函数。
=COUNTIFS(C2:C7,”>”&B3,C2:C7,”<5000”)
Tips:运算符和单元格之间用&进行连接。

Max

返回数组或引用区域的最大值
MySQL中有同名函数,Python中有同名函数。

Min

返回数组或引用区域的最小值
MySQL中有同名函数,Python中有同名函数。

MOD

取余操作,IF(MOD(A1,2)
Tips:IF(MOD(A1,2)=0,"偶数","奇数")

Rank

排序,返回指定值在引用区域的排名,重复值同一排名。
SQL中有近似函数row_number()。

Rand/Randbetween

常用随机抽样,前者返回 0~1 之间的随机值,后者可以指定范围。
MySQL中有同名函数。

Averagea

求平均值,也有 Averageaif,Averageaifs
MySQL中有同名函数,python有近似函数mean。

Quartile

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

Stdev

求标准差,统计型函数,后续数据分析再讲到

Substotal

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

参数:

  1. AVERAGE101
  2. COUNT102
  3. COUNTA103
  4. MAX104
  5. MIN105
  6. PRODUCT106
  7. STDEV.S107
  8. STDEV.P108
  9. SUM109(隐藏和筛选时有区别,手动隐藏单元格时使用 109,先筛选,后隐藏)
  10. VAR.S110
  11. VAR.P111

Int/Round

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

Indirect

数据引用跳转


时间序列类

专门用于处理时间格式以及转换,时间序列在金融、财务等数据分析中占有较大比重。时机序列的处理函数比我列举了还要复杂,比如时区、分片、复杂计算等。这里只做一个简单概述。

Year

返回日期中的年
MySQL中有同名函数。

Month

返回日期中的月
MySQL中有同名函数。

Weekday

=Weekday(指定时间,参数)
返回指定时间为一周中的第几天,参数为 1 代表从星期日开始算作第一天,参数为 2 代表从星期一开始算作第一天(中西方差异)。我们中国用 2 为参数即可。
MySQL中有同名函数。

Weeknum

=Weeknum(指定时间,参数)
返回一年中的第几个星期,后面的参数类同 weekday,意思是从周日算还是周一。
MySQL中有近似函数week。

Day

返回日期中的日(第几号)
MySQL中有同名函数。

Date

=Date(年,月,日)
时间转换函数,等于将 year(),month(),day() 合并
MySQL中有近似函数date_format。

Now

返回当前时间戳,动态函数
MySQL中有同名函数。

Today

返回今天的日期,动态函数
MySQL中有同名函数。

Datedif

=Datedif(开始日期,结束日期,参数)
日期计算函数,计算两日期的差。参数决定返回的是年还是月等。
MySQL中有近似函数DateDiff。