当前位置:首页 > 科技 > 正文

多行多列查数据,这些方法你会几种?

来源:Excel之家ExcelHome

作者:祝洪忠

小伙伴们好啊,今天咱们分享一组在多行多列数据表中查询数据的公式,看看这些公式你会几种?

如下图所示,需要根据H2单元格中的月份,以及H4单元格的城市名,在左侧数据表中来查询同时符合两个条件的数据。

1、VLOOKUP

=VLOOKUP(H2,A:F,MATCH(H4,A1:F1,0),0)

公式中的“H2”,是VLOOKUP要查询的关键字,“A:F”是要查询的数据区域,至于要在数据区域中返回第几列的内容,这里咱们使用MATCH函数来帮个忙。

MATCH(H4,A1:F1,0) 这部分的作用,是查询H4的城市名在A1:F1中所处的位置,结果返回一个数字。

VLOOKUP以MATCH函数的结果来返回对应列的内容,正所谓指哪儿打哪儿。

2、HLOOKUP

=HLOOKUP(H4,1:7,MATCH(H2,A1:A7,0),0)

公式中的“H4”,是HLOOKUP要查询的关键字,“1:7”,表示第一行至第7行的整行引用,是要查询的数据区域,要在数据区域中返回第几行的内容呢?这里也是使用MATCH函数的结果作为参照。

MATCH(H2,A1:A7,0) 这部分,就是根据H2单元格中的月份,从A1:H7单元格区域中返回所处的位置。

注意,使用MATCH函数的结果作为VLOOKUP以及HLOOKUP函数的参数时,要特别注意MATCH函数本身查询区域的起始位置,必须要和V、H两位大哥的查询区域的起始位置相同。

就像本例中,VLOOKUP的查询区域是从A列开始,那MATCH函数的查询区域A1:F1,也是从A列开始。HLOOKUP函数的查询区域是从第一行开始,那MATCH函数的查询区域A1:A7,也是从第一行开始的。

3、INDEX+MATCH

=INDEX(A1:F7,MATCH(H2,A:A,0),MATCH(H4,1:1,0))

INDEX函数第一参数使用多行多列的A1:F7区域,然后再使用MATCH函数,分别以H2中月份的位置和H4中城市的位置,来作为INDEX函数的行列参数,月份在哪一行,INDEX函数就以此来确定要返回数据的行。城市在哪一列,INDEX函数就以此来确定要返回数据的列。

同样,使用INDEX与MATCH函数配合使用时,要注意MATCH函数本身查询区域的起始位置要和INDEX第一参数所选的行列起始位置相同。

4、SUMPRODUCT

因为查询后要返回的内容是数值,这里咱们也可以使用多条件求和的方法来处理:

=SUMPRODUCT((A2:A7=H2)*(B1:F1=H4)*B2:F7)

5、SUMIF

既然是多条件求和,还可以使用SUMIF来处理:

=SUMIF(A:A,H2,OFFSET(A:A,0,MATCH(H4,B1:F1,0)))

公式中的OFFSET(A:A,0,MATCH(H4,B1:F1,0)部分,以A列为参照基点,向下偏移0行,向右偏移列数由MATCH函数来指定,要查询的城市在哪一列,就返回哪一列的引用。得到引用作为SUMIF函数的求和区域。

6、DSUM

多条件求和,还可以用DSUM函数露一小手:

=DSUM(A1:F7,H4,H1:H2)

公式中的A1:F7是数据列表区域,H4 用于指定返回数据列表中哪一个字段的数据,H1:H2则是带字段标题的统计条件。

使用这个函数时,数据列表以及统计条件的的字段标题都不能是空白的,所以咱们就加上了一样的标题“月份”。

7、XLOOKUP

如果你使用的是Office 365或者是Office 2021,还可以使用XLOOKUP函数:

=XLOOKUP(H2,A2:A7,XLOOKUP(H4,B1:F1,B2:F7),0)

你可能想看:

有话要说...

取消
扫码支持 支付码