Vlookup函式尋找數據很方便,但很多新函式,如fitler、xlookup,甚至textjoin都比它好用,難道Vlookup要被淘汰了嗎?
No! No! 它還一個絕妙的功能,就是
隔多列取數
。
Vlookup有4個參數,其中第3個參數是返回值所在的列數。正常情況下只需要返回一個值,所以常用一個整數。
=VLOOKUP
(G2,B:E
,
4
,0)
很多人不知道的是,如果第3個參數是一個組數,就可以返回多列值。
=VLOOKUP(A11,A1:E7, {2,3,5} ,0)
註: 非WPS和Office365版本需要按三鍵(ctrl shift enter) 輸入公式
利用這個特點可以解決工作中常見的隔列取值問題。比如最常遇到的尋找後的隔列求和
【例】如下圖所示,要求根據下表的姓名,從上表中尋找並隔列對實際、計劃求和。
分析:估計很多同學首先想到Sumif函式,但這是一個多列求和而且要先分產品。如果非要用它,公式為:
=SUMIF(B2:Y2,B2,OFFSET(B2:Y2,MATCH(A14,A3:A10,0),0))
或:
=SUM(SUMIF(A:A,AA2,OFFSET(A:A,,ROW(1:12)*2-1)))
用Sumproduct函式公式會簡單些,但數據量大了會很卡。
=SUMPRODUCT((A3:A10=A14)*(B2:Y2=B13)*B3:Y10)
其他函式都不理想,換作Vlookup怎麽樣?嘿嘿,很簡單!
=SUM(VLOOKUP(A14,A:Y,ROW($1:$12)*2,0))
用Vlookup尋找並隔列取值,用Sum求和就可以了,其中row(1:12)生成1,2,3,4....12序號*2就變成了2,4,6...24用來取實際列的數位。
蘭色說 :其實很多函式的參數都可以把一個數換成一組數,用來批次運算。
另:在蘭色函式教程Vlookup精講一集中介紹了共31種用法,其中也包括本文功能,買過視訊的同學可以看視訊講解。該
屬蘭色錄制的
四合一大全套教程
(
含
函式大全144集+技巧大全119集+透視表50集套+圖表從入到到精通185集
),點選下面連結可以直接進蘭色小店購買,購買後記得聯系客服加QQ學習群。