|
在Excel中，IF是查詢函數(shù)，Sum是求和函數(shù)，Vlookup是查詢函數(shù)……其實(shí)這些都是常規(guī)應(yīng)用，但是有部分函數(shù)公式就是不按規(guī)矩“出牌”，其功能顛覆了我們的想象…… 一、Sumproduct函數(shù)。 作用：返回相應(yīng)的數(shù)組區(qū)域乘積的和。 語法：=Sumproduct(值或數(shù)組區(qū)域)。 （一）、基本用法。 目的：快速計(jì)算商品的總銷售額。 方法： 在目標(biāo)單元格中輸入公式：=SUMPRODUCT(C3:C9,D3:D9)。 解讀： 公式：=SUMPRODUCT(C3:C9,D3:D9)的計(jì)算過程就是：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ū)域中符合條件的值。 語法：=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),'無銷量')。 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ù)庫或數(shù)據(jù)表的分類匯總。 語法：=Subtotal(格式代碼，數(shù)組或引用)。 常用的格式代碼： 代碼分為1-11和101-111，通過觀察發(fā)現(xiàn)對(duì)應(yīng)的函數(shù)名稱是相同的，難倒是功能相同？ 1-11：包含手動(dòng)隱藏的值。 101-111：不包含隱藏的值，暨僅對(duì)可見單元格有效。 （一）、基本用法。 目的：對(duì)篩選的值求平均值。 方法： 在目標(biāo)單元格中輸入公式：=SUBTOTAL(101,E3:E9)。 解讀： 代碼101-111僅對(duì)可見單元格有效，從示例中也可以看出，篩選前和篩選后的平均值截然不同。 （二）、保持序號(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)的值。 語法： 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),'無銷量')。 結(jié)束語： Sumproduct、Vlookup、Subtotal、Lookup都是日常工作中經(jīng)常要用到的函數(shù)，對(duì)于基本應(yīng)用，很多親還是基本掌握的，但是對(duì)于一些高級(jí)技巧，絕大部分的親就犯迷糊，希望本節(jié)內(nèi)容能給大家?guī)聿灰粯拥捏w驗(yàn)哦！ |
|
|
