技巧:
通配符:
*
代表不定数量的字符
?
代表下一个字符
函数:
-
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 / FALSElookup_value
:就是待查找的值,即 Atable_array
:待查找的值(A)和 待匹配的值(B)的所在区域,二者加起来的区域col_index_num
:要返回的值(B)所在的列是第几列,如果从左到有分别为A
所在列和B
所在列,那么col_index_num
的值是 2lookup_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, ... )