我是一名辦公軟件愛好者,喜歡Excel,會函數、會基礎操作、也會一點VBA。現在和大家分享、交流一些excel的基礎知識,歡迎愛好的和需要學習的朋友,相互學習、一起進步。如果你喜歡我的文章,請關注我,以便第一時間看到我分享的內容。
開始正題,今天給大家分享的是雙條件查詢的函數組合。雙條件查找,常見的有以下兩種情況:
第一種情況【常規方法6種】
第一種雙條件查找的情況,整理出來的函數公式方法有6種,公式如下:
方法1【數組公式】
<code>=VLOOKUP(E2&F2,IF({1,0},A2:A15&B2:B15,C2:C15),2,)/<code>
方法2【普通公式】
<code>=LOOKUP(,0/((A2:A15=E2)*(B2:B15=F2)),C2:C15)/<code>
方法3【數組公式】
<code>=INDEX(C2:C15,MATCH(E2&F2,IF(1,A2:A15&B2:B15),))/<code>
方法4【數組公式】
<code>=OFFSET(C1,MATCH(E2&F2,IF(1,A2:A15&B2:B15),),)/<code>
方法5【數組公式】
<code>=INDIRECT("C"&1+MATCH(E2&F2,IF(1,A2:A15&B2:B15),))/<code>
方法6【普通公式】①如果所有內容均為文本,且返回值的字符數均一樣(本例均為個2字符)時
<code>=MID(PHONETIC(A2:C15),FIND(E2&F2,PHONETIC(A2:C15))+2,2)/<code>
方法6【數組公式】②如果返回值為數值,且符合條件的是唯一項時
<code>=SUMPRODUCT((A2:A15=E2)*(B2:B15=F2),C2:C15)/<code>
第一種雙條件查詢這個情況,難度在於【計算滿足條件的內容所在行】,我簡單整理出以下4個方法:
方法1【數組公式】
<code>=MAX(IF((A1:A15=E2)*(B1:B15=F2),ROW(1:15)))/<code>
方法2【數組公式】
<code>=MATCH(E2&F2,IF(1,A1:A15&B1:B15),)/<code>
方法3【數組公式】
<code>=SUM((A1:A15=E2)*(B1:B15=F2)*ROW(1:15))/<code>
方法4【普通公式】
<code>=LOOKUP(,0/((A1:A15=E2)*(B1:B15=F2)),ROW(1:15))/<code>
用這四種方法,再結合查找引用的INDIRECT、OFFSET、INDEX等函數,可以組合成更多的函數組合。
第二種情況【常規方法6種】
第二種雙條件查找的情況,整理出來的函數公式方法有6種,公式如下:
方法1【普通公式】
<code>=VLOOKUP(I2,A2:G15,MATCH(J2,A1:G1,),)/<code>
方法2【普通公式】
<code>=LOOKUP(,0/(I2=A2:A15),OFFSET(A2:A15,,MATCH(J2,B1:G1,)))/<code>
方法3【普通公式】
<code>=INDEX(姓名,MATCH(I2,A2:A15,),MATCH(J2,B1:G1,))/<code>
方法4【普通公式】
<code>=OFFSET(A1,MATCH(I2,A2:A15,),MATCH(J2,B1:G1,))/<code>
方法5【普通公式】
<code>=INDIRECT(CHAR(64+MATCH(J2,A1:G1,))&MATCH(I2,A1:A15,))/<code>
方法6【數組公式】
<code>=SQRT(MAX(IF(A2:A15=I2,B2:G15,)*IF(B1:G1=J2,B2:G15,)))/<code>
數據、取區域交集的方法
一、第一種情況
上圖中,用到了兩個函數公式(這是OFFSET函數的兩種獲取區域的方法,供參考):
<code>=OFFSET(A1,MATCH(E2&F2,IF(1,A2:A15&B2:B15),),,,3) C2:C15/<code>
<code>=OFFSET(A1:C1,MATCH(E2&F2,IF(1,A2:A15&B2:B15),),) C2:C15/<code>
二、第二種情況
上圖是用定義名稱的方式實現的,用這種方式,會產生很多名義的名稱,管理、維護起來不是很方便。那麼,還可以有以下的方法:
演示裡面用到的兩個公式:
<code>=OFFSET(A1,MATCH(I2,A2:A15,),1,1,6) OFFSET(A1,1,MATCH(J2,B1:G1,),14,1)/<code>
<code>=OFFSET(A2:A15,,MATCH(J2,B1:G1,)) OFFSET(B1:G1,MATCH(I2,A2:A15,),)/<code>
用這種方法,公式不見得是最簡潔的,但是這種思路可以學習和掌握。
如果需要文中的示例文件,請關注並私信給我。如果喜歡這次分享的內容,請點贊並轉發,讓更多人可以學習到。
閱讀更多 上班下班 的文章