数据分析常用的Excel函数
Excel常用函数
简介
什么是函数
可以把函数理解为一个可以控制的黑箱子，输入X到黑箱子中，他就会输出Y，参数就是黑箱子的控制开关，打到不同的档位，黑箱子会输出不同的Y。
函数示意图
常见函数分类
- 文本清洗函数
- 关联匹配函数
- 逻辑运算函数
- 计算统计函数
- 时间序列函数
文本清洗函数
常用的文本清洗函数
- 清除字符串空格：
TRIM - 合并单元格：
CONCATENATE - 截取字符串：
LEFT/RIGHT/MID - 替换单元格中的内容：
REPLACE/SUBSTITUTE - 查找文本在单元格中的位置：
FIND/SEARCH
清除字符串空格
TRIM
清除字符串text左右的空格。
=TRIM(text)
清除A1单元格左右的空格
合并单元格
CONCATENATE
将几个文本字符串合并为一个文本字符串。
=CONCATENATE(text1, text2, ...)
合并字符串以及单元格内容
截取字符串
LEFT：从text中，提取num_chars个字符（从左开始）。
=LEFT(text, num_cahrs)
RIGHT：从text中，提取num_chars个字符（从右开始）。
=RIGHT(text, num_chars)
MID：从text中，从stat_num开始，提取num_chars个字符串。
=MID(text, start_num, num_chars)例子
在A2中从左开始提取2个字符
在A1中从右开始提取2个字符
在A1中，从位置3开始，提取2个字符
替换单元格中内容
替换指定位置：REPLACE
从“原字符串”的“开始位置”开始，选择“字符个数”个，替换为“新字符串”
=REPLACE(原字符串, 开始位置, 字符个数, 新字符串)例子
从A1的位置1开始，选取4个字符串，替换为新的字符串“2018”。
将2019替换为2018
替换指定文本：SUBSTITUTE
在text中用new_text替换old_text，instance_num指定要替换第几次出现的old_text，如果不指定则替换old_text。
=SUBSTITUTE(text, old_text, new_text, instance_num) 例子
用“k”替换A1中第二次出现的“应届”
查找文本在单元格中的位置
FIND & SEARCH
从within_test中查找FIND_text，返回查找字符的起始位置编号。
=FIND(FIND_text, within_text, start_num)
=SEARCH(要查找字符, 字符所在的文本, 从第几个字符开始查找)FIND 和 SEARCH 两个函数几乎相同，区别在于FIND 精确查找，区分大小写； SEARCH 模糊查找，不区分大小写。
例子
从A1中查找k，并返回第一个k的起始位置编号
关联匹配函数
VLOOKUP
VLOOKUP 函数简介
VLOOKUP 函数总共有4个参数，分别是：用谁去找、匹配对象范围、返回第几列、匹配方式（0/FALSE表示精确匹配，1/TRUE表示模糊匹配）。
vlookup简介
四种查询方式
1.单条件查找
根据工号，将左边检索区域的“电脑销售额”匹配到右边对应位置，只需要使用VLOOKUP函数，结果存在则显示对应的“电脑销售额”；结果不存在则显示#N/A。
=VLOOKUP(F2,$A$2:$D$55,4,0)
单条件查询
注意：检索关键字必须在检索区域的第1列，也就是说如果是根据“姓名”检索，那么检索区域应该从B列开始。
2.反向查找
当检索关键字不在检索区域的第1列，可以使用虚拟数组公式IF来做一个调换。
=VLOOKUP(G2,IF({1,0},B2:B8,A2:A8),2,0)
反向查找
反向查找的固定公式用法：
=VLOOKUP(检索关键字，IF({1,0},检索关键字所在列，查找值所在列)，2，0)注意：其实反向查找除了检索区域改成一个虚拟数组公式IF之外，其他和单条件查找没有区别。
3.多条件查询
在匹配数据时，往往条件不是单一的，那么就可以利用&将字段拼接起来，并且利用IF数组公式构建出一个虚拟的区域。
=VLOOKUP(F2&G2,IF({1,0},A2:A53&B2:B53,D2:D53),2,0)
多条件查找
注意事项，所有使用了数组的公式，不能直接回车，需要使用Ctrl+Shift+Enter，否则会出错。
4.查询返回多列
查找返回多列需要用到另外一个辅助函数——COLUMN函数。
返回结果为单元格引用的列数。 例如：column(B1)返回值为2，因为B1为第2列。
=COLUMN(待查询单元格/区域)需要注意的是第三个参数“返回第几列”的写法。
=VLOOKUP($G2,$A$2:$E$55,COLUMN(D1),0)
多条件查找
返回多列的固定公式用法：
=VLOOKUP(混合引用关键字，查找范围，COLUMN(xx)，0)返回第几列就用COLUMN函数引用第几列的单元格即可。
HLOOKUP
=HLOOKUP(用谁去找, 匹配对象范围, 返回第几行, 匹配方式)和VLOOKUP的区别：HLOOKUP返回的值与查找的值在同一列上，而VLOOKUP返回的值与查找的值在同一行上。
INDEX
返回数组array中指定索引的单元格的值。
=INDEX(array, Row_num, Column_num)
返回指定区域第2行第2列的单元格内容
MATCH
功能：在区域内查找指定的值，返回第一个查找值的位置。 lookup_value：需要查找的值； lookup_array：查找的区域； match_type：-1、0或1，0表示查找等于lookup_value的值。
=MATCH(lookup_value, lookup_array, [match_type])
查找A1到A4中6的位置
Index & Match联合使用 = VLookup
ROW & COLUMN
ROW：返回指定引用的行号；COLUMN：返回指定引用的列号。
=ROW(reference)
=COLUMN(reference)例子
求C列为第几列
OFFSET
OFFSET：以指定的引用reference为起点，按照偏移量偏移之后，返回值。 rows：向下偏移多少行； columns：向右偏移多少列； height：返回多少行； width：返回多少列。
=OFFSET(reference, rows, columns, height, width)例子
A1向下偏移一行，向右偏移一列
计算返回的两行两列的和，如果不求和，则会报错，因为一个cell不能填充四个cell的内容。
HYPERLINK
HYPERLINK：创建一个超链接指向link_location，以friendly_name的字符串进行显示，link_location可以是URL链接或文件路径。
=HYPERLINK(link_location, friendly_name)
插入超链接
逻辑运算函数
一般用于条件运算，在Excel中，True代表数值1，False代表0。
IF
如果满足判断条件，则返回“真值”，否则返回“假值”。
=IF(判断条件, 真值, 假值)
=IF(AND(条件1, 条件2), 真值, 假值)
=IF(OR(条件1, 条件2), 真值, 假值)例子
计算统计函数
求最值
MAX MIN
MAX：求某区域中的最大值；MIN：求某区域中的最小值。
求数目
COUNT COUNTIF COUNTIFS
COUNT：计数。COUNTIF：单条件计数。COUNTIFS：多条件计数。
=COUNTIF(区域, 条件)
=COUNTIFS(区域1, 条件1, [区域2, 条件2], ...)例子 单条件计数：
A1到A3中大于等于10的数量
多条件计数：
A1到A3中大于5小于10的数量
求和
SUM
功能：计算单元格区域中所有数值的和。
SUMIF
功能：求满足条件的单元格的和。
=SUMIF(条件判断区域, 判断条件, 求和区域)
计算一班的总成绩
SUMPRODUCT
将数组间的对应元素相乘，并返回乘积之和。
=SUMPRODUCT(array1, array2, ......)例子
如果只选取一列，和SUM一样只是求和。
只选一列
选取多列，就会返回对应元素乘积之和。
选取多列
取整
INT ROUND
INT：向下取整；ROUND：四舍五入取整，num_digits指定精确到哪一位。
=INT(number)
=ROUND(number, num_digits)例子
向下取整
0表示精确到个位数
排序：RANK
功能：计算number在reference中排名。 order：0或默认，则为降序排列；其他数值则为升序排列。
=RANK(number, reference, order)
升序排列
描述统计
AVERAGEA
求算数平均值。
QUARTILE
求分位数。
STDEV
求标准差。
SUBTOTAL
该函数相当于以上几个函数的汇总，通过输入function_num参数，选择不同的函数。
=SUBTOTAL(function_num, ref1, ref2, ...)时间序列函数
时间的本质是数字。
YEAR MONTH DAY
分别返回日期序号的年、月、日。
=YEAR(日期序号)
=MONTH(日期序号)
=DAY(日期序号)
DAY
DATE
将year，month，day组合成一个日期，相当于这三个函数的逆操作。
=DATE(year, month, day)
DATE
WEEKDAY WEEKNUM
WEEKDAY：根据一个日期是星期几来返回一个数字。 return_type：设置返回数字的规则。
WEEKNUM：根据一个日期是今年的第几周来返回一个数字。 return_type：
=WEEKDAY(serial_number, return_type)
=WEEKNUM(serial_number, return_type)例子
2019/2/28属于第九周星期四。
WEEKDAY
WEEKNUM
NOW TODAY
返回当前的时间，now精确到时间，today只精确到日期。
=NOW()
=TODAY()