關於Vlookup的用法,蘭色寫的教程夠多的了,同學們估計也快看膩了。正當蘭色覺得Vlookup不可能再有什麼新用法時,這兩天遇到的一個Excel問題,讓我對它有了新的認識。
【問題】下圖左表為員工1~6月份銷售明細表,在右表J列中要求根據I列姓名,判斷它的銷售是否達標。判斷規則:
1~6月銷量均大於10為達標,有任一月份小於10則為不達標。
分析:
首先要根據姓名從左表中查詢對應1~6月的銷售,查詢某個月可以,同時查詢6個月難道要用6個Vlookup?
=IF(AND(VLOOKUP(I2,A:G,2,0)>10,VLOOKUP(I2,A:G,3,0)>10,VLOOKUP(I2,A:G,4,0)>10,VLOOKUP(I2,A:G,5,0)>10,VLOOKUP(I2,A:G,6,0)>10),“達標”,“未達標”)
當然不用!可以用條件計數的方式完成判斷
=IF(SUMPRODUCT((A2:A12=I2)*(B2:G12>10))=6,“達標”,“不達標”)
注:Sumproudct可以對滿足條件的多列進行求和、計數。
這個問題好像應該圓滿解決了?
No! 如果此問題修改一下,就很難用Sumprdouct完成了。
1~6月銷量均>10算達標。
1月、4月除外
(銷售淡季不統計)
那公式改成
=IF(SUMPRODUCT((A2:A12=I2)*(B2:G12>10))=4,“達標”,“不達標”)
不行,如果1或4月有一個月大於10,其他有3個月大於10也屬達標,不屬合要求。
看來還是需要
逐月提取出數字
進行對比。
到這兒,蘭色突然想到原來分享過的用Vlookup隔列求和的示例。
【例】要求對2,3,5,6,8,9數字進行求和
{=SUM(VLOOKUP(A2,A2:K2,,0))}
注:
把要求和的列數放在大括號內,用逗號分隔。
上面這個示例只是求和,如果配合AND或OR進行判斷,不就解決今天的問題了嗎?一試果然成功。
=IF(
AND
(VLOOKUP(I3,A:G,
,)>10
),“達標”,“不達標”)
由此,也引申出Vlookup的新用法,即
Vlookup的第三個引數使用
常量陣列形式
從源表中提取出多個值,然後結合IF+AND/OR進行批次條件判斷。
蘭色說:Vlookup竟然也有新用法了,是不是很意外。Execl的魅力也在於此。當你覺得已完全掌握了Excel某個功能時,其實是你剛入門,瞭解越多就會發現不會的越多。
工作中最常用的Excel函式公式,全印在一張超大的滑鼠墊上(送40集配套影片),點我檢視詳情