來源: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)
有話要說...