7個典型用法,沒有人比我更懂SUMPRODUCT

小伙伴們好啊,今天咱們一起來學習一下SUMPRODUCT函數的用法,這個函數從字面來理解,SUM是求和,PRODUCT是乘積。綜合到一起,就是對各個數組參數計算乘積,并返回乘積之和。啥是數組?咱們就簡單的把它理解成一組數好了,沒啥高科技含量。接下來,咱們就一起看看這個函數有哪些典型的用法。1、計算商品總價如下圖所示,C列是商品單價,D列是商品數量,要計算所有商品的總價,可以使用下面這個公式:=SUMPRODUCT(C2:C12,D2:D12)公式把每一行中的單價與數量對應相乘,然后再求和。計算過程相當于C2*D2+C3*D3+C4*D4……本例中,也可以將中間的逗號換成乘號:=SUMPRODUCT(C2:C12*D2:D12)那這個乘號和逗號啥區別呢? 當求和區域中有文本的時候,在兩個數組之間使用逗號,會把文本當成0來處理。要是使用乘號的話,如果求和區域中有文本,就返回錯誤值了,既然是文本,你讓小S怎么乘嘛。但是使用逗號也是有前提條件的,就是兩個數組的行、列數必須一樣,否則會返回一個錯誤值。2、計算指定條件的總價如下圖所示,要計算部門為“大食堂”的所有商品總價,可以使用以下公式:=SUMPRODUCT((A2:A12="大食堂")*C2:C12*D2:D12)

這個公式中,先使用(A2:A12="大食堂"),判斷A列的部門是不是等于指定的部門,得到一組由TRUE和FALSE構成的邏輯值。但是邏輯值不能直接作為數組參數,但是可以參加四則運算,所以咱們用乘號,分別乘以C列的單價和D列的數量。如果(A2:A12="大食堂")這部分的結果里是邏輯值TRUE,就相當于1,而邏輯值FALSE的作用就相當于是0。換句話說,符合指定部門的,就用1*單價*數量,不符合指定部門的就用0*單價*數量,最后再將各個乘積進行求和。3、計算指定月份的總金額如下圖,要根據A列的日期,計算2月份的總金額。公式為:=SUMPRODUCT((MONTH(A2:A12)=2)*1,D2:D12,E2:E12)

MONTH(A2:A12)=2部分,先使用MONTH函數計算出A列日期的月份,然后用等式,判斷是不是等于2,這部分得到的,也是一組由TRUE和FALSE構成的邏輯值。接下來把邏輯值乘以1,TRUE*1結果為1,FALSE*1結果為0,最終變成由數值1和0構成的新數組。再用這個新數組作為參數1,分別與第二參數D2:D12和第三參數E2:E12相乘,最后計算出乘積之和。也可以直接把各個參數之間的逗號寫成乘號;=SUMPRODUCT((MONTH(A2:A12)=2)*D2:D12*E2:E12)4、計算指定月份、指定部門的總金額如下圖,要計算2月份大食堂的總金額。公式為:=SUMPRODUCT((MONTH(A2:A12)=2)*(B2:B12="大食堂"),D2:D12,E2:E12)

公式看起來很長,但是和第三個例子是一樣的,就是多了一個部門的判斷條件(B2:B12="大食堂")。5、根據兩個條件計算采購量如下圖,要根據B1:E1單元格中的日期和A2:A12單元格中的品名,來計算對應的采購量。公式為:=SUMPRODUCT((B1:E1=1*"2021-2-2")*(A2:A12="黃瓜")*B2:E12)

這個數據表格和前面幾個表格的結構不一樣了,兩個條件分別在行方向和列方向,對于這種結構的表格,計算時有一個小套路,就是分別對比水平和垂直方向的兩個條件,然后乘以數值區域。這個數值區域的行數,要與垂直方向條件區域的行數一樣,并且列數要與水平方向條件區域的列數一樣。上面這句話可能不太好理解,其實結合到數據中,就可以看出來了:數值區域是B2:E12,這里的行數是2~12行,和(A2:A12="黃瓜")的行數是一樣的。而列數是B:E,和(B1:E1=1*"2021-2-2")的列數是一樣的。另外,在公式中直接寫日期的時候,還要注意,先加上一對半角引號,然后再乘以1變成日期序列值,否則Excel會把2021-2-2當成減法了。6、同一區域設置多個條件如下圖所示,要計算2月份白菜和土豆的總采購量。公式為:=SUMPRODUCT((MONTH(B1:E1)=2)*((A2:A12="白菜")+(A2:A12="土豆"))*B2:E12)

(MONTH(B1:E1)=2) 這部分是計算水平方向的月份的。((A2:A12="白菜")+(A2:A12="土豆"))這部分,用來判斷垂直方向的商品名稱是不是符合條件。 注意,兩個條件之間使用了加號,計算過程咱們就不細說了,作用是表示二個條件符合其一。7、帶通配符的求和如下圖所示,要根據A列的手機型號,統計帶有“摩托羅拉”字樣的手機銷量。公式為:=SUMPRODUCT(ISNUMBER(FIND("摩托羅拉",A2:A12))*B2:B12*C2:C12)

SUMPRODUCT函數的參數中不支持使用通配符,所以在計算帶有關鍵字的問題,就比較吃虧了。ISNUMBER(FIND("摩托羅拉",A2:A12))這部分,先使用FIND函數在A2:A12數據區域中查找關鍵字,如果包含關鍵字,就返回表示關鍵字位置的數值,否則就返回錯誤值。然后使用ISNUMBER函數判斷FIND函數的數組結果是不是數值,如果是數值,說明是包含關鍵字的,如果不是數值,那就是不包含關鍵字了。ISNUMBER最終得到由邏輯值TRUE或FALSE構成的內存數組,然后再與后面的第二參數B2:B12和第三參數C2:C12依次相乘。最后由SUMPRODUCT返回乘積之和。好了,今天咱們分享的內容就是這些,祝大家一天好心情!練手文件在此:https://pan.baidu.com/s/10oh916zdgR9O4v892Ubp9A提取碼: rdkh圖文制作:祝洪忠

免責聲明:本文僅代表文章作者的個人觀點,與本站無關。其原創性、真實性以及文中陳述文字和內容未經本站證實,對本文以及其中全部或者部分內容文字的真實性、完整性和原創性本站不作任何保證或承諾,請讀者僅作參考,并自行核實相關內容。

http://image99.pinlue.com/thumb/img_jpg/BAbVqibwwtmwRZxVB4waYMZBWNeBlRukOBN85LlYh6JIjF0bP0ZibiakY5BFWopGHzge1nsCMiarFVp6NcPiahcE5sQ/0.jpeg
分享
評論
首頁
山西快乐十分投注价格