多條件查找,99﹪的人不會
Excel辦公,讓職場更輕鬆!
▲
01
VLOOKUP IF
公式:
=VLOOKUP(E3&F3,IF({1,0},A3:A10&B3:B10,C3:C10),2,0)
VLOOKUP(查找值,查找區域,返回結果在查找區域的第幾列,查找方式)
用IF函數構造內存數組,數組公式按<Ctrl Shift Enter>三鍵結束。
▲
02
LOOKUP
公式:
=LOOKUP(,0/((A16:A23=E16)*(B16:B23=F16)),C16:C23)
LOOKUP(1,0/((查找區域1=查找值1)*(查找區域2=查找值2)),返回值的區域)
或者公式:
=LOOKUP(,0/(E16&F16=A16:A23&B16:B23),C16:C23)
▲
03
INDEX MATCH
公式:
=INDEX(C29:C36,MATCH(E29&F29,A29:A36&B29:B36,))
解析:
INDEX:在給定的單元格區域中,返回特定行列交叉處單元格的值或引用。
MATCH:返回符合特定值特定順序的項在數組中的相應位置。
MATCH函數支持數組,其多種條件可以直接用&連接。
或者公式:
=INDEX(C29:C36,MATCH(1,(A29:A36=E29)*(B29:B36=F29),0))
公式為數組公式,按<Ctrl Shift Enter>三鍵結束。
▲
04
OFFSET MATCH
公式:
=OFFSET(C41,MATCH(E42&F42,A42:A49&B42:B49,),)
解析:
OFFSET(參照單元格,偏移的行數,偏移的列數,所要引用的行數,所要引用的行數)
MATCH(E42&F42,A42:A49&B42:B49,)部分找到業務員為渺渺區域為東莞在數組區域A42:A49&B42:B49中的位置為6,以單元格C41為基點,向下偏移6行0列,到達單元格C47,返回值9803。
或者公式:
=OFFSET(C41,MATCH(1,(A42:A49=E42)*(B42:B49=F42),0),)
公式為數組公式,按<Ctrl Shift Enter>三鍵結束。
▲
05
INDIRECT MATCH
公式:
=INDIRECT("C"&MATCH(E55&F55,A55:A62&B55:B62,) 54)
解析:
INDIRECT(對單元格的引用,引用樣式)
該題查找返回的值在C列,所以是對C列對應單元格的引用,MATCH(E55&F55,A55:A62&B55:B62,)部分找到業務員為帥黨區域為珠海在數組區域A55:A62&B55:B62中的位置3,
那它對應返回的值在區域C55:C62中的位置也為3,
因為區域是從55行開始的,
得加上前面的54行,
所以得到返回值在C列的位置為57,
用INDIRECT函數返回C57單元格的引用。
或者公式:
=INDIRECT("C"&MATCH(1,(A55:A62=E55)*(B55:B62=F55),) 54)
公式為數組公式,按<Ctrl Shift Enter>三鍵結束。
▲
06
SUMIFS
公式:
=SUMIFS(C68:C75,A68:A75,E68,B68:B75,F68)
解析:SUMIFS(求和區域,條件區域1,條件1…條件區域n,條件n)
使用查詢注意事項:
①條件區域的數據必須是唯一的(如果不是唯一的,其結果返回的是它們的和);
②查詢的結果必須是數字。
▲
07
SUMPRODUCT
公式:
=SUMPRODUCT((A81:A88=E81)*(B81:B88=F81)*C81:C88)
或者公式:
=SUMPRODUCT((A81:A88=E81)*(B81:B88=F81),C81:C88)
解析:SUMPRODUCT:在給定的幾組數組中,將數組間對應的元素相乘,並返回乘積之和。
SUMPRODUCT((條件1區域=條件1)*(條件2區域=條件2)*(……)*求和區域)
SUMPRODUCT((條件1區域=條件1)*(條件2區域=條件2)*(……),求和區域)
使用查詢注意事項:
①條件區域的數據必須是唯一的(如果不是唯一的,其結果返回的是它們的和);
②查詢的結果必須是數字。
▲
08
SUM
公式:
=SUM((A94:A101=E94)*(B94:B101=F94)*C94:C101)
公式為數組公式,按<Ctrl Shift Enter>三鍵結束。
使用查詢注意事項:
①條件區域的數據必須是唯一的(如果不是唯一的,其結果返回的是它們的和);
②查詢的結果必須是數字。
▲
09
MAX
公式:
=MAX((A107:A114=E107)*(B107:B114=F107)*C107:C114)
公式為數組公式,按<Ctrl Shift Enter>三鍵結束。
解析:(A107:A114=E107)*(B107:B114=F107)部分相乘條件成立的返回1,不成立的返回0,然後乘以要返回的結果列,條件成立為1的將返回對應的銷售額,不成立的都返回0,就可以用MAX函數求最大值,得到的就是想要的結果。
使用查詢注意事項:
①條件區域的數據必須是唯一的(如果不是唯一的,其結果返回的是最大的那個);
②查詢的結果必須是數字。
▲
10
SUM IF
公式:
=SUM(IF(A120:A127=E120,IF(B120:B127=F120,C120:C127)))
公式為數組公式,按<Ctrl Shift Enter>三鍵結束。
使用查詢注意事項:
①條件區域的數據必須是唯一的(如果不是唯一的,其結果返回的是它們的和);
②查詢的結果必須是數字。
▲
11
MAX IF
公式:
=MAX(IF(A133:A140=E133,IF(B133:B140=F133,C133:C140)))
公式為數組公式,按<Ctrl Shift Enter>三鍵結束。
使用查詢注意事項:
①條件區域的數據必須是唯一的(如果不是唯一的,其結果返回的是最大的那個);
②查詢的結果必須是數字。
▲
12
MIN IF
公式:
=MIN(IF(A146:A153=E146,IF(B146:B153=F146,C146:C153)))
公式為數組公式,按<Ctrl Shift Enter>三鍵結束。
使用查詢注意事項:
①條件區域的數據必須是唯一的(如果不是唯一的,其結果返回的是最小的那個);
②查詢的結果必須是數字。
▲
13
AVERAGE IF
公式:
=AVERAGE(IF(A159:A166=E159,IF(B159:B166=F159,C159:C166)))
公式為數組公式,按<Ctrl Shift Enter>三鍵結束。
使用查詢注意事項:
①條件區域的數據必須是唯一的(如果不是唯一的,其結果返回的是它們的平均值);
②查詢的結果必須是數字。
▲
14
資料庫函數
DGET:從資料庫中提取符合指定條件且唯一存在的記錄。
DPRODUCT:與滿足指定條件的資料庫中記錄欄位(列)的值相乘。
DSUM:求滿足給定條件的資料庫中記錄欄位(列)數據的和。
DMAX:返回滿足給定條件的資料庫中記錄欄位(列)數據的最大值。
DMIN:返回滿足給定條件的資料庫中記錄欄位(列)數據的最小值。
DAVERAGE:計算滿足給定條件的列表或資料庫的列中數值的平均值。
它們的使用格式為(單元格區域,數據列,給定條件的單元格區域)
公式:
=DGET(A171:C179,3,E171:F172)
=DPRODUCT(A171:C179,3,E171:F172)
=DSUM(A171:C179,3,E171:F172)
=DMAX(A171:C179,3,E171:F172)
=DMIN(A171:C179,3,E171:F172)
=DAVERAGE(A171:C179,3,E171:F172)
作者:仰望~星空
推薦閱讀: