数据分析学习03-函数SUBTOTAL, IF, VLOOKUP, INDEX和MATCH

再学会吧

已于 2023-12-21 17:04:59 修改

阅读量492

点赞数 8

文章标签：数据分析学习

于 2023-12-21 16:54:05 首次发布

本文链接：https://blog.csdn.net/weixin_61866295/article/details/135134592

版权

技巧：

通配符：
*代表不定数量的字符
?代表下一个字符

函数：

SUBTOTAL：其中有多个函数功能
```
= SUBTOTAL(function_num, [ref1], [ref2], ...)
# = SUBTOTAL(指定函数, 选择区域1, 选择区域2，...)
```
SUBTOTAL的SUM功能 和 SUM的区别：SUBTOTAL可以根据字段的筛选而改变求和的值，而SUM则是求和范围内所有的，无法改变。简而言之：SUBTOTAL更加灵活。

IF：判断，注意嵌套使用，缕清内在逻辑

= IF(logical, value_if_true, [value_if_false])
# = IF(逻辑比较条件, 结果成立时的返回值, [结果不成立时的返回值])
# [value_if_false] 该参数选填，没有该参数时，返回值False

比如判断是否目标大于10w，=IF(单元格 > 100000, "达标", "不达标")

VLOOKUP：查找，根据 A（ID）查找 B（姓名）
```
= VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
# VLOOKUP(要查找的数据, 要查找的数据所在区域 和 要返回的数据所在区域, 要返回的数据在区域中是第几列, 查找的匹配方式)
```
关于匹配方式：
- 近似匹配 1 / TRUE
- 精确匹配 0 / FALSE

lookup_value：就是待查找的值，即 A

table_array：待查找的值（A）和待匹配的值（B）的所在区域，二者加起来的区域

col_index_num：要返回的值（B）所在的列是第几列，如果从左到有分别为 A 所在列和 B 所在列，那么 col_index_num 的值是 2

lookup_value如果有多个符合，那么只会返回第一个值
```
查找 b 开头并且是三个字符所对应的数值，其中 b 所在的单元格为 C99
= VLOOKUP(C99 & "???", 
		b所在的区域 和 对应数值所在的区域的总区域，
		2，	# 对应数值所在的区域 在二者总区域的第几列
		0)	# 精确查找
```
VLOOKUP和数据透视表的聚合：先做好透视表，再去引用透视表算好的聚合运算的数值

如果直接引用可能会造成引用区域的偏移，所以需要用$把相应区域固定住。
INDEX 和 MATCH函数：可以让EXCEL根据表头自动完成相应的运算，比如将上图中的GMV改为进店人数，则会自动显示相应的进店人数的数据

MATCH：返回要查找的值在查找区域内第几个位置。准确的说是某个单元格在行或列中的位置。

某个单元格a在A列或者行，那么返回a在A中是第几个位置。
```
= MATCH(lookup_value, lookup_array, [match_type])
# MATCHI(查找项, 查找区域, 匹配模式)
匹配模式：	 - 1 小于	- 0 精确匹配		-1 大于
```
MATCH区域只能是单行或者单列

INDEX：返回单元格的内容。

某个单元格a 在A区域，那么返回a的内容。
```
= INDEX(array, row_num, column_num)
# INDEX( A 区域, a 行号, a 列号)
```
INDEX和MATCH的组合使用：可以返回某个单元格的内容。
```
= INDEX(数据区域, 
		MATCH(行查找项, 该项在INDEX数据区域的相应列, 0),
		MATCH(列查找项, 该项在INDEX数据区域的相应行, 0)
		)
```
用MATCH函数确定行号在列中查找，确定列号在行中查找。

在表格中使用，如果要用到句柄填充注意区域的锁定。

用INDEX也可以返回全部行或者全部列内容：

INDEX(array, 0, column_num)返回全部列内容

INDEX(array, row_num, 0)返回全部行内容

SUMIFS, MATCH和INDEX一起使用：自动根据列名的数据求和。
```
= SUMIFS(
	INDEX( 数据区域, 
		MATCH( 行查找项, 该项在INDEX数据区域的相应列, 0),
		MATCH( 列查找项, 该项在INDEX数据区域的相应行, 0)
		),
	条件1判断所在的区域1, 条件1, 
	条件2判断所在的区域2, 条件2,
	...
	)
```