一題多解, 不用我說, 你們看完之後就知道那種方法最好, Vookup最累, 加權這個方法自作多情。 sumif最爽, sumproduct棘手, 題目要求:根據姓名查找數量,
如下圖
一、解法1:Vlookup實現
1、公式截圖
2、公式
=IFERROR(VLOOKUP(B8,A1:B4,2,0),"")&IFERROR(VLOOKUP(B8,D1:E4,2,0),"")&IFERROR(VLOOKUP(B8,G1:H4,2,0),"")&IFERROR(VLOOKUP(B8,J1:K4,2,0),"")
3、公式解釋
4個區域, 用4個vlookup連接
如果找不到就會報錯, 報錯就顯示空
這種方法的缺點就是區域多, 公式越長, 如果資料有50個區域, 就要用50個vlookup, 當然Vlookup累的趴下了, 寫公式的人也夠嗆了。
二、解法2:Sumprodict+Text實現
1、公式截圖
2、公式
=SUMPRODUCT((A2:J4=B8)*(TEXT(B2:K4,"0;-0;0;!0")))
3、公式解釋
判斷區域是否有等於“小老鼠”的, 這個返回的是一個二維陣列, 由true和false組成的, 要注意的是選擇區域時要少先一列, 最後一列不要選
把判斷的結果和區域B2:K4相乘, 注意這個也要是少選了一列, 第1列不選, 這樣就前面的區域判斷就吻合了, 但是由於這個區域有姓名漢字, 所以要用text處理一下, 把漢字強制顯示0用這個公式(TEXT(B2:K4,"0;-0;0;!0"))
最後用sumproduct求和, 就不用三鍵了, 如果用sum就要三鍵一齊下Ctrl+Shift+回車
三、解法3:加權實現
1、公式截圖
2、公式
=INDIRECT(TEXT(MIN(IF(A1:K4=B8,ROW(A1:A4)*10^4+COLUMN(A1:K1)+1)),"R0C0000"),)
3、公式解釋
IF(A1:K4=B8,ROW(A1:A4)*10^4+COLUMN(A1:K1)+1)判斷區域如果有等於"小老鼠", 那麼就行號上加權, 乘以10的4次方, 也就是10000再加上列號, 還要加上, 因為結果是在姓名的後一列
text(加權後的值, “R0C0000")目的讓其顯示R1C1引用樣式, 方便indirect函數引用
四、解法4:sumif函數輕鬆實現, 一個字“爽”
1、公式截圖
2、公式
=SUMIF(A1:J4,B8,B1:K4)
3、公式解釋
這是sumif函數經典的錯位用法
如果資料來源沒有重複的姓名, sumif可以當vlooku函數用, 因為沒有重複的, 求和的結果就是vlookup引用返回的結果。
大家注意第一參少選最後一列;第3參數少選第1列