標籤:

[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中數字輸好了,怎樣在數字後面批量添加「元」字?

TAG:忘記 | Excel |