|
在Excel中，IF是查詢函數，Sum是求和函數，Vlookup是查詢函數……其實這些都是常規應用，但是有部分函數公式就是不按規矩“出牌”，其功能顛覆了我們的想象…… 一、Sumproduct函數。 作用：返回相應的數組區域乘積的和。 語法：=Sumproduct(值或數組區域)。 （一）、基本用法。 目的：快速計算商品的總銷售額。 方法： 在目標單元格中輸入公式：=SUMPRODUCT(C3:C9,D3:D9)。 解讀： 公式：=SUMPRODUCT(C3:C9,D3:D9)的計算過程就是：C3*D3+C4*D4+C5*D5+C6*D6+C7*D7+C8*D8+C9*D9。 （二）、行列交叉查詢。 方法： 在目標單元格中輸入公式：=SUMPRODUCT((B3:B9=J3)*(D2:G2=K3)*D3:G9)。 解讀： 在B3:B9=J3和D2:G2=K3的兩組條件判斷中，如果條件成立，則返回1,1*1*X=X，如果有其中的一個條件不成立，則返回0。 （三）、中國式排名。 方法： 在目標單元格中輸入公式：=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位數組元素的數組，和值即為大于當前值的個數，暨相對排名哦。+1位修正值。 二、Vlookup函數。 作用：查詢指定區域中符合條件的值。 語法：=Vlookup(查找值，數據范圍，返回值的列數，匹配模式)。 （一）、基本用法。 目的：查詢銷售員的銷量和。 方法： 在目標單元格中輸入公式：=VLOOKUP(K3,B3:H9,7,0)。 解讀： 匹配模式分為0和1兩種，其中0為精準匹配，1位模糊匹配。 （二）、多條件查詢。 方法： 1、在目標單元格中輸入公式： =IFERROR(VLOOKUP(H3&I3,IF({1,0},B3:B9&E3:E9,D3:D9),2,0),'無銷量')。 2、Ctrl+Shift+Enter填充。 解讀： 當多條件查詢時，條件和條件范圍之間用&符號連接。 （三）、多列查詢。 方法： 在目標單元格中輸入公式：=VLOOKUP($H$3,$B$3:$E$9,COLUMN(B3),0)。 解讀： 多列查詢的關鍵在于返回第幾列的值，所以用Column函數替代固定的列數。 三、Subtotal函數。 作用：返回一個數據庫或數據表的分類匯總。 語法：=Subtotal(格式代碼，數組或引用)。 常用的格式代碼： 代碼分為1-11和101-111，通過觀察發現對應的函數名稱是相同的，難倒是功能相同？ 1-11：包含手動隱藏的值。 101-111：不包含隱藏的值，暨僅對可見單元格有效。 （一）、基本用法。 目的：對篩選的值求平均值。 方法： 在目標單元格中輸入公式：=SUBTOTAL(101,E3:E9)。 解讀： 代碼101-111僅對可見單元格有效，從示例中也可以看出，篩選前和篩選后的平均值截然不同。 （二）、保持序號（No）的連續性。 方法： 在目標單元格中輸入公式：=SUBTOTAL(103,B$2:B2)。 解讀： 代碼103對應的函數為Counta，暨統計非空單元格個數。 四、Lookup函數。 作用：在指定的區域中查找對應的值。 語法： 1、向量形式：=Lookup(查找值，查找值所在范圍，返回值所在范圍)。 2、數組形式：=Lookup(查找值，查找值和返回值所在范圍)。 前提條件：必須對查找值所在范圍中值【升序】排序。 （一）、基本用法。 方法： 在目標單元格中輸入公式：=LOOKUP(H3,B3:B9,D3:D9)、=LOOKUP(H3,B3:D9)。 解讀： 1、Lookup函數在使用前，必須對查詢值所在的范圍按【升序】次序排列。 2、使用數組形式時，查詢值的范圍必須是第1列，返回值必須是最后1列。 （二）、單條件查詢。
方法： 在目標單元格中輸入公式：=LOOKUP(1,0/(B3:B9=H3),D3:D9)。 解讀： 1、當Lookup函數在查找范圍中找不到對應的值時，則向下匹配，原則是匹配小于查詢值的最大值。 2、如果B3:B9=H3成立，則返回1，否則返回0;0/(B3:B9=H3)對應的結果為0和錯誤組成的數組，公式：=LOOKUP(1,0/(B3:B9=H3),D3:D9)查找值1匹配到0所對應的結果。 （三）、多條件查詢。
方法： 在目標單元格中輸入公式：=IFERROR(LOOKUP(1,0/((B3:B9=H3)*(E3:E9=I3)),D3:D9),'無銷量')。 結束語： Sumproduct、Vlookup、Subtotal、Lookup都是日常工作中經常要用到的函數，對于基本應用，很多親還是基本掌握的，但是對于一些高級技巧，絕大部分的親就犯迷糊，希望本節內容能給大家帶來不一樣的體驗哦！ |
|
|
來自： hercules028 > 《excel》
