在Excel中，IF是查詢函數(shù)，Sum是求和函數(shù)，Vlookup是查詢函數(shù)……其實(shí)這些都是常規(guī)應(yīng)用，但是有部分函數(shù)公式就是不按規(guī)矩“出牌”，其功能顛覆了我們的想象……

一、Sumproduct函數(shù)。

作用：返回相應(yīng)的數(shù)組區(qū)域乘積的和。

語(yǔ)法：=Sumproduct(值或數(shù)組區(qū)域)。

（一）、基本用法。

目的：快速計(jì)算商品的總銷售額。

方法：

在目標(biāo)單元格中輸入公式：=SUMPRODUCT(C3:C9,D3:D9)。

解讀：

公式：=SUMPRODUCT(C3:C9,D3:D9)的計(jì)算過(guò)程就是：C3*D3+C4*D4+C5*D5+C6*D6+C7*D7+C8*D8+C9*D9。

（二）、行列交叉查詢。

方法：

在目標(biāo)單元格中輸入公式：=SUMPRODUCT((B3:B9=J3)*(D2:G2=K3)*D3:G9)。

解讀：

在B3:B9=J3和D2:G2=K3的兩組條件判斷中，如果條件成立，則返回1,1*1*X=X，如果有其中的一個(gè)條件不成立，則返回0。

（三）、中國(guó)式排名。

方法：

在目標(biāo)單元格中輸入公式：=SUMPRODUCT((H$3:H$9>H3)/COUNTIF(H$3:H$9,H$3:H$9))+1。

解讀：

條件H$3:H$9>H3如果成立，則返回1,1/1=1，暨公式：=SUMPRODUCT((H$3:H$9>H3)/COUNTIF(H$3:H$9,H$3:H$9))返回一組以1和0位數(shù)組元素的數(shù)組，和值即為大于當(dāng)前值的個(gè)數(shù)，暨相對(duì)排名哦。+1位修正值。

二、Vlookup函數(shù)。

作用：查詢指定區(qū)域中符合條件的值。

語(yǔ)法：=Vlookup(查找值，數(shù)據(jù)范圍，返回值的列數(shù)，匹配模式)。

（一）、基本用法。

目的：查詢銷售員的銷量和。

方法：

在目標(biāo)單元格中輸入公式：=VLOOKUP(K3,B3:H9,7,0)。

解讀：

匹配模式分為0和1兩種，其中0為精準(zhǔn)匹配，1位模糊匹配。

（二）、多條件查詢。

方法：

1、在目標(biāo)單元格中輸入公式：=IFERROR(VLOOKUP(H3&I3,IF({1,0},B3:B9&E3:E9,D3:D9),2,0),"無(wú)銷量")。

2、Ctrl+Shift+Enter填充。

解讀：

當(dāng)多條件查詢時(shí)，條件和條件范圍之間用&符號(hào)連接。

（三）、多列查詢。

方法：

在目標(biāo)單元格中輸入公式：=VLOOKUP($H$3,$B$3:$E$9,COLUMN(B3),0)。

解讀：

多列查詢的關(guān)鍵在于返回第幾列的值，所以用Column函數(shù)替代固定的列數(shù)。

三、Subtotal函數(shù)。

作用：返回一個(gè)數(shù)據(jù)庫(kù)或數(shù)據(jù)表的分類匯總。

語(yǔ)法：=Subtotal(格式代碼，數(shù)組或引用)。

常用的格式代碼：

代碼分為1-11和101-111，通過(guò)觀察發(fā)現(xiàn)對(duì)應(yīng)的函數(shù)名稱是相同的，難倒是功能相同？

1-11：包含手動(dòng)隱藏的值。

101-111：不包含隱藏的值，暨僅對(duì)可見(jiàn)單元格有效。

（一）、基本用法。

目的：對(duì)篩選的值求平均值。

方法：

在目標(biāo)單元格中輸入公式：=SUBTOTAL(101,E3:E9)。

解讀：

代碼101-111僅對(duì)可見(jiàn)單元格有效，從示例中也可以看出，篩選前和篩選后的平均值截然不同。

（二）、保持序號(hào)（No）的連續(xù)性。

方法：

在目標(biāo)單元格中輸入公式：=SUBTOTAL(103,B$2:B2)。

解讀：

代碼103對(duì)應(yīng)的函數(shù)為Counta，暨統(tǒng)計(jì)非空單元格個(gè)數(shù)。

四、Lookup函數(shù)。

作用：在指定的區(qū)域中查找對(duì)應(yīng)的值。

語(yǔ)法：

1、向量形式：=Lookup(查找值，查找值所在范圍，返回值所在范圍)。

2、數(shù)組形式：=Lookup(查找值，查找值和返回值所在范圍)。

前提條件：必須對(duì)查找值所在范圍中值【升序】排序。

（一）、基本用法。

方法：

在目標(biāo)單元格中輸入公式：=LOOKUP(H3,B3:B9,D3:D9)、=LOOKUP(H3,B3:D9)。

解讀：

1、Lookup函數(shù)在使用前，必須對(duì)查詢值所在的范圍按【升序】次序排列。

2、使用數(shù)組形式時(shí)，查詢值的范圍必須是第1列，返回值必須是最后1列。

（二）、單條件查詢。

方法：

在目標(biāo)單元格中輸入公式：=LOOKUP(1,0/(B3:B9=H3),D3:D9)。

解讀：

1、當(dāng)Lookup函數(shù)在查找范圍中找不到對(duì)應(yīng)的值時(shí)，則向下匹配，原則是匹配小于查詢值的最大值。

2、如果B3:B9=H3成立，則返回1，否則返回0;0/(B3:B9=H3)對(duì)應(yīng)的結(jié)果為0和錯(cuò)誤組成的數(shù)組，公式：=LOOKUP(1,0/(B3:B9=H3),D3:D9)查找值1匹配到0所對(duì)應(yīng)的結(jié)果。

（三）、多條件查詢。

方法：

在目標(biāo)單元格中輸入公式：=IFERROR(LOOKUP(1,0/((B3:B9=H3)*(E3:E9=I3)),D3:D9),"無(wú)銷量")。

結(jié)束語(yǔ)：

Sumproduct、Vlookup、Subtotal、Lookup都是日常工作中經(jīng)常要用到的函數(shù)，對(duì)于基本應(yīng)用，很多親還是基本掌握的，但是對(duì)于一些高級(jí)技巧，絕大部分的親就犯迷糊，希望本節(jié)內(nèi)容能給大家?guī)?lái)不一樣的體驗(yàn)哦！

如果有不懂、不明白的地方，歡迎在留言區(qū)留言討論哦，如果親覺(jué)著實(shí)用，別忘了“點(diǎn)轉(zhuǎn)評(píng)”哦，有親的支持，小編會(huì)進(jìn)一步努力的哦！