Vlookup曾是表格中最常用查找函數(shù)，但隨著新版本增加了xlookup、Filter等函數(shù)，可以輕松實(shí)現(xiàn)多條件、反向、倒查等，難道Vlookup就沒(méi)用了嗎？NO! 它還有一個(gè)絕招：隔列取數(shù)。

微軟Office LTSC 2021專業(yè)增強(qiáng)版 簡(jiǎn)體中文批量許可版 2024年09月更新

• 類型：辦公軟件
• 大小：2.2GB
• 語(yǔ)言：簡(jiǎn)體中文
• 時(shí)間：2024-09-12

查看詳情

案例一：

Vlookup有4個(gè)參數(shù)，其中第3個(gè)參數(shù)是返回值所在的列數(shù)。正常情況下只需要返回一個(gè)值，所以常用一個(gè)整數(shù)。

=VLOOKUP(G2,B:E,4,0)

很多人不知道的是，如果第3個(gè)參數(shù)是一個(gè)組數(shù)，就可以返回多列值。

=VLOOKUP(A11,A1:E7,{2,3,5},0）

注: 非WPS和Office365版本需要按三鍵(ctrl shift enter) 輸入公式

利用這個(gè)特點(diǎn)可以解決工作中常見(jiàn)的隔列取值問(wèn)題。比如最常遇到的查找后的隔列求和

【例】如下圖所示，要求根據(jù)下表的姓名，從上表中查找并隔列對(duì)實(shí)際、計(jì)劃求和。

分析：估計(jì)很多同學(xué)首先想到Sumif函數(shù)，但這是一個(gè)多列求和而且要先分產(chǎn)品。如果非要用它，公式為：

=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函數(shù)公式會(huì)簡(jiǎn)單些，但數(shù)據(jù)量大了會(huì)很卡。

=SUMPRODUCT((A3:A10=A14)*(B2:Y2=B13)*B3:Y10)

其他函數(shù)都不理想，換作Vlookup怎么樣？嘿嘿，很簡(jiǎn)單！

=SUM(VLOOKUP(A14,A:Y,ROW($1:$12)*2,0))

用Vlookup查找并隔列取值，用Sum求和就可以了，其中row(1:12)生成1，2，3，4....12序號(hào)*2就變成了2，4，6...24用來(lái)取實(shí)際列的數(shù)字。

其實(shí)很多函數(shù)的參數(shù)都可以把一個(gè)數(shù)換成一組數(shù)，用來(lái)批量運(yùn)算。

別怕，IF函數(shù)其實(shí)很簡(jiǎn)單字符拆分的模式化公式，你最喜歡哪一種？

案例二：

如下圖所示，需要從上表中提取1~12月的實(shí)際數(shù)放在下表格

分析：

Vlookup4個(gè)參數(shù)，其中第3個(gè)是提取的列數(shù)，比如3就可以提取上表中第3列的值。

=VLOOKUP(A13,A:Y,3,0)

其實(shí)3可以換成一組數(shù)，提取多列，比如

=VLOOKUP(A13,A:Y,{3,5,7},0)

注：非wps和office365版本沒(méi)溢出功能，所以需要選取多列（幾個(gè)數(shù)選幾列）按Ctrl + shift+Enter輸入。

但如果有很多列，一個(gè)個(gè)輸入數(shù)字覺(jué)得麻煩，可以用Column函數(shù)生成

=COLUMN(A:L)*2+1

最終公式：

=VLOOKUP(A13,A:Y,COLUMN(A:L)*2+1,0)

如果外套 sum就可以求和了。

=SUM(VLOOKUP(A13,A:Y,COLUMN(A:L)*2+1,0))

很多excel函數(shù)的參數(shù)都可以使用數(shù)組，如sumifs、mid，實(shí)現(xiàn)高級(jí)用法。

最新評(píng)論