excel函数学习

前期处理类

trim

去除空格，很多数据里面有空格，不方便处理

具体操作

选择处理后的数据存放列,trim（需处理数据）

PS:仅可去除字符串首尾空格，且中间会保留一位空格

MySQL有同名函数，Python有近似函数strip

Concatenate

合并单元格中的内容，还有另一种合并方式是& 

具体操作

xx&yy 得到 xxyy      xx&“-”&yy 得到 xx-yy

concatenate（xx,yy）得到 xxyy    concatenate（xx,"-",yy）得到 xx-yy

ps:当需要合并的内容过多时，concatenate的效率快

MySQL有近似函数concat

Replace

新字符串替换旧字符串，而且替换的位置和数量都是指定的

具体操作

=Replace（指定字符串，哪个位置开始替换，替换字符数，"新文本"）

MySQL中有同名函数，Python中有同名函数。

Substitute

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

=substitute（需要替换的文本，旧文本，新文本，第N个旧文本）

ps:substitute函数经常用来去掉文本之间的空格

输入公式=SUBSTITUTE(A1," ","")

我 爱 你  我爱你

输入公式=SUBSTITUTE(A1," ","",1)

我 爱 你  我爱 你

Left／Right／Mid

帮助我们取得某个数值或者文本数据中我们需要特定值。left是从左边的第一位开始取值，right从右边开始取值，mid则从指定位置开始取值。

操作

123456789

LEFT(123456789, 2)  得到 12

right(12345678,3) 得到 678

=MID(12345678,2,3) 得到 234

组合应用

从按照时间自动设置的编号“20150812145012”中提取年月日的数据

=LEFT(A2,4)&"年"&MID(A2,6,1)&"月"&MID(A2,7,2)&"日"

Find

查找某字符串出现的位置，可以指定为第几次出现，与Left／Right／Mid结合能完成简单的文本提取

操作

=Find（要查找字符，指定字符串，第几个字符[一般都省略就是1]）

Left(用来提取所需字符串的区域，从左边数所提取的字符串长度)

Search

Find函数是精确查找，区分大小写。Search函数是模糊查找，不区分大小写。

search函数的参数find_text可以使用通配符“*”，“？”。

通配符——星号“*”可代表任何字符串,所以返回1

如果参数find_text就是问号或星号，则必须在这两个符号前加上“～”符号

关联匹配类

在进行多表关联或者行列比对时用到的函数，越复杂的表用得越多。

Lookup

=Lookup（查找的值，值所在的位置，返回相应位置的值）

注意：

lookup函数的使用要求查询条件按照升序排列，所以该函数之前需要对表格进行排序处理。

查询的条件可以高于查询条件列的最大值，但是不能低于查询条件列的最小值。

Vlookup

=Vlookup(查找的值，哪里找，找哪个位置的值，是否精准匹配一般是0)

注意：

查找必须从开始列起，位置值也是从开始那列算起

入门应用：查找

VLOOKUP函数查找时出现错误值的几个原因

A、实在是没有所要查找到的值

B、查找的字符串或被查找的字符中含有空格或看不见的空字符，验证方法是用=号对比一下，如果结果是FALSE，就表示两个单元格看上去相同，其实结果不同。

C、参数设置错误。VLOOKUP的最后一个参数没有设置成1或者是没有设置掉。第二个参数数据源区域，查找的值不是区域的第一列，或者需要反回的字段不在区域里，参数设置在入门讲里已注明，请参阅。

D、数值格式不同，如果查找值是文本，被查找的是数字类型，就会查找不到。解决方法是把查找的转换成文本或数值，转换方法如下：

文本转换成数值：*1或--或/1

数值转抱成文本：&""

进阶应用：多条件同时查找

结合

COLUMNS（A1） 返回值1 返回指定单元格的列数

ROW返回行号

应用：模糊查找

VLOOKUP的第一个参数允许使用通配符“*”来表示包含的意思，把*放在字符的两边，即"*" & 字符 & "*"

Vlookup的反向查找

一般情况下，VLOOKUP函数只能从左向右查找。但如果需要从右向右查找，则需要把区域进行“乾坤大挪移”，把列的位置用数组互换一下。

VLOOKUP(A9,IF({1,0},B2:B5,A2:A5),2,0)

IF({1,0},B2:B5,A2:A5  代表B A数列

整个代表在B列查找A9内容，然后返回A的值

indirect

=INDIRECT(ref_text,[a1])

index

连续区域的引用

=index(array,row_num,column_num)

非连续区域的引用

=index(（array_1,array_2,array_3....array_n),row_num,column_num,array_num) ，

MATCH

MATCH(lookup-value,lookup-array,match-type)

lookup-value:表示要在区域或数组中查找的值，可以是直接输入的数组或单元格引用。

lookup-array:表示可能包含所要查找的数值的连续单元格区域，应为数组或数组引用。

match-type:表示查找方式，用于指定精确查找（查找区域无序排列）或模糊查找（查找区域升序排列）。取值为-1、1、0 。其中0为精确查找。

遇到反向，双向等复杂的表格查找，用INDEX+MATCH

offset

以指定的应用为参考系，通过上下左右偏移得到新的区域的引用。返回的引用可以是一个单元格也可以是一个区域。并且可以引用指定行列数的区域。

以区域做参考系

最后的1,1 可以省略

以区域做参考系

逻辑运算类

IF

经典的如果但是，在后期的Python中，也会经常用到，当然会有许多更优雅的写法。

也有ifs用法，取代if(and())的写法。

MySQL中有同名函数，Python中有同名函数。

And

全部参数为True，则返回True，经常用于多条件判断。

MySQL中有同名函数，Python中有同名函数。

and(logical1,logical2, ...)，

其中Logical1, logical2为判断条件

通过下拉填充公式就能快速把整列的考评结果给计算出来

注意：

1.条件值或表达式，最多为30个。

2.Logical1,Logical2,Logical3……：参数必须是逻辑参数，否则会出错。

Or

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

MySQL中有同名函数，Python中有同名函数。

IS系列

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

计算统计类

sum／Sumif／Sumifs/SUMPRODUCT

统计满足条件的单元格总和，SQL有中同名函数。

MySQL中有同名函数，Python中有同名函数。

SUM：计算单元格区域中数值的和

SUMIF：对满足一个条件的单元格求和

SUMIF(range，criteria，sum_range)，

其中，range是原表中用于条件判断的区域，criteria是求和的条件与区域，sum_range是需要求和的区域。

Sumproduct

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

MySQL中有同名函数。

Count／Countif／Countifs

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

MySQL中有同名函数，Python中有同名函数。

Max

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

MySQL中有同名函数，Python中有同名函数。

Min

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

MySQL中有同名函数，Python中有同名函数。

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（引用区域，参数）

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

Int／Round

取整函数，int向下取整，round按小数位取数。

round(3.1415,2) =3.14 ;

round(3.1415,1)=3.1