[Excel]忘記乘積求和,SUMPRODUCT其實還能這麼用……
「
你用過SUMPRODUCT函數嗎?
除了能夠實現,pivot table中乘和運算外,SUMPRODUCT還能幹嘛?
本期,火箭君將帶你揭示, SUMPRODUCT不被你所熟知的另一項用處。
函數SUMPRODUCT基礎使用
SUMPRODUCT函數公式非常簡單,公式後直接在括弧內輸入若干組數組即可,而公式則會將若干數組間的數據相乘,再求總和。
舉例而言,如果你有一組數據為 {2,3,4},另一組數據為{5,10,20}。如果你使用SUMPRODUCT函數,那你會得到120。(因為2*5 3*10 4*20=120)
這不就是一個乘法公式和一個求和公式的結合體么?貌似很一般啊?
但看完後面的敘述,火箭君覺得你會對這個公式另眼相看!
SUMPRODUCT的多條件查詢
說道多條件查詢,火箭君曾經在去年的推文讓vlookup搞定多條件查詢中,提及使用vlookup函數實現多條件查詢的方法。但我們今天的主角SUMPRODUCT函數也同樣能夠實現多條件查詢。而且火箭君覺得實際使用中,利用SUMPRODUCT進行多條件,尤其是3個以上條件查詢時,更為便利。
何為多條件查詢?
顧名思義,「多條件」就是超過2個條件,也就是說要查詢的結果必須符合條件1和條件2,甚至是條件1到條件n。
從數據結構上來看,可能條件是按兩個方向進行排列的:
亦或者是並行排列的
SUMPRODUCT實現2個條件查詢
需要查詢的數據位於B2:B19這個範圍,而限制條件分別對應C12和C13單元格。
整個公式可以分成這麼幾個部分:
(A2:A9=C12) - 依次對比A2至A9單元格中的字元串是否與C12單元格相同,若相同則為1,否則為0。並由此生成一個8行1列的數組。
(B1:I1=C13) - 同理,依次對比B1至I1單元格中的字元串是否與C13單元格相同,若相同則為1,否則為0。並由此生成一個1行8列的數組。
B2:I9 - 一個8行8列的數組。
下面可能是喚醒你高中數學的時間了,火箭君帶你梳理下這三個部分相乘的計算過程。
1. (A2:A9=C12) *(B1:I1=C13) 得到了一個新的8x8數組
2.再乘以B2:I9,則是兩個8x8數組的進一步相乘,最後的結果就為¥8,377
SUMPRODUCT實現3個以上條件查詢
公式依舊不複雜,對應條件1、2、3的判斷生成了3個數組,再進行簡單的數組相乘計算,最後找到了滿足3個條件的數值。而這就是為什麼 火箭君在上文中提到,利用sumproduct函數來實現3個以上條件查詢時,要比vlookup函數來得更為簡單,整個公式也更易讀。
推薦閱讀:
※Excel 數組公式應用及實例介紹
※VLOOKUP函數怎麼用?
※[Excel技巧]使用數組公式
※新手進階篇:Excel之如何通過出生年份自動生成生肖屬相
※Excel中數字輸好了,怎樣在數字後面批量添加「元」字?