说到查找,我们不得不说说Index和Match这对好兄弟。这是一对经典的组合,学好用好能解决工作中的不少问题。 Index语法 INDEX(array, row_num, [column_num]) INDEX(reference, row_num, [column_num], [area_num]) 说明:Index通过指定参数中的行号、列号,返回查找数组或引用中相应位置的数据。 Match语法 MATCH(lookup_value, lookup_array, [match_type]) 说明:Match函数用于确定查找值在查找区域中的位置,参数[match_type]通常用0,表示精确查找。 我们用以下简单的数据示例来说明一下这对好兄弟的常用套路。 套路一:单条件查找 示例:根据销售员来查询销量 公式: =INDEX(C2:C7,MATCH(F2,B2:B7,0)) 说明:Match返回销售员“Mary”在销售员区域B2:B7中的位置,结果是3,然后用Index返回销量区域C2:C7中相应位置的数据,即第三个数,也就是977。 套路二:多条件查找 示例:根据销售员和日期来查找销量 公式: =INDEX(C2:C7,MATCH(F5&'|'&G5,B2:B7&'|'&A2:A7,0)) 说明:这是数组公式,输完公式之后,需要同时按Ctrl Shift Enter来结束。 Match函数的参数中,将销售员和日期用&符号组合起来,中间加了一个竖线|作为分割标识,match函数第二个参数也是将数据区域用&符号连接起来,这也是常用的多条件确定行号的方式。 套路三:行列交叉点 示例:根据销售员来查找销售额或销量(可动态选择) 公式: =INDEX(B2:D7,MATCH(F8,B2:B7,0),MATCH(G8,B1:D1,0)) 说明:行号和列号分别由Match函数得出。这样就根据行号和列号找到了对应的位置的数据,就像坐标一样,给定一个坐标(x,y),就确定了位置。 套路四:组合查找 示例:根据销售员、日期来查找销售额或销量(可动态选择) 公式: =INDEX(A2:D7,MATCH(F12&'|'&G12,B2:B7&'|'&A2:A7,0),MATCH(H12,A1:D1,0)) 说明:这是上面套路中的组合应用,在多行多列的区域中根据多个条件来确定行号、根据单个条件来确定列号,从而返回结果。这是数组公式,输入完公式后需要同时按Ctrl Shift Enter结束。 套路五:反向查找 示例:根据销售员查找日期 公式: =INDEX(A2:A7,MATCH(F15,B2:B7,0)) 说明:这里查找区域在后面,返回结果所在的区域在前面,所以叫反向查找;Match函数返回找到的第一个值的位置,所以返回结果是2017/4/4而不是2017/4/6。 套路六:返回整行、整列数据 示例:根据选择,对销量或销售额求和 公式: =SUM(INDEX(A2:D7,,MATCH(F19,A1:D1,0))) 说明:Index的第一个参数是多行多列的区域,第二个参数省略或者是0,第三个参数指定值,表示返回整列的数据;如果第二个参数指定值,第三个参数省略或者是0(逗号不可省略),表示返回整行的数据。 |
|