當前位置:首頁 > 科技 > 正文

多行多列查數據,這些方法你會幾種?

來源: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)

你可能想看:

有話要說...

取消
掃碼支持 支付碼