tft每日頭條

 > 圖文

 > excel表格sumproduct公式怎麼操作

excel表格sumproduct公式怎麼操作

圖文 更新时间:2024-12-25 13:48:04

今天跟大家一起來認識一個很好用的函數:sumproduct函數。

sumproduct函數,sumproduct是由兩個英文單詞組成,即sum和product。Sum代表求和,product代表乘積,組成的sumproduct就是乘積之和。

現在我們來詳細說一下sumproduct函數的含義、語法,以及它的基礎應用。

一、sumproduct函數介紹

1、sumproduct函數——含義

Sumproduct函數,在定的幾組數組中,把數組間對應的元素相乘,最後返回乘積之和。

2、sumproduct函數——語法格式

=sumproduct(數組1,數組2,數組3, ……)

數組裡面的相應元素進行相乘後,再将乘積求和。

比如:

=SUMPRODUCT({1;2;3;4;5},{1;2;3;4;5})=1*1 2*2 3*3 4*4 5*5=25

注意:數組參數必須具有相同的維數,否則返回錯誤。意思就是如果第一個數組有5個元素,那麼其它數組也必須是5個元素。

當然,也可以這樣:

=sumproduct(區域1,區域2,區域3, ……)

比如:=SUMPRODUCT(A1:A3,B1:B3,C1:C3)=A1*B1*C1 A2*B2*C2 A3*B3*C3

同理,要求各個區域的大小和形式是一緻。比如區域1是A1:A3,單列3行,那麼其它區域也必須是單列3行。

總結:Sumproduct是把每個區域或每個數組對應位置的值相乘,最後再将乘積求和。

二、sumproduct函數應用

現在我們要用示例具體來看下sumproduct函數的各種玩法。

以下面一張圖為數據源:

excel表格sumproduct公式怎麼操作(Excel技巧sumproduct函數實例講解)1

現準備統計以下問題:

1、統計表中所有商品總的采購金額。

2、統計表中采購的水果總重量。  

3、統計表中采購的水果總金額。

4、統計表中單價30元以下的水果的采購總金額。

這四個問題歸納一下,第一個問題為簡單數組求和;第二、三個問題為單條件求和;第四個問題為多條件求和。

他們用sumproduct函數分别怎麼統計?看下面:

1、簡單數組求和

統計表中所有商品總的采購金額。

我們平時一般都是分兩步,先将單價*重量算出來,然後再用sum函數将結果求和。

如果是用sumproduct函數,一步就直接搞定,

公式為:=SUMPRODUCT(C3:C11,D3:D11)

或者,你也可以把公式裡的逗号(,)變乘号(*),即

=SUMPRODUCT(C3:C11*D3:D11)

excel表格sumproduct公式怎麼操作(Excel技巧sumproduct函數實例講解)2

兩個公式結果是一樣的。

但是用逗号和用乘号的區别在于:

公式用逗号時,在求和統計時,可以将非數值型的數組元素,當0處理;

公式用乘号時,數組元素就不能存在無法計算的内容,如文本。

2、單條件求和

統計表中采購的水果總重量。

用sumproduct函數表示,即:

公式一:

=SUMPRODUCT((B3:B11="水果")*D3:D11)

公式二:

=SUMPRODUCT(N(B3:B11="水果"),D3:D11)

公式三:

=SUMPRODUCT((B3:B11="水果")*1,D3:D11)

excel表格sumproduct公式怎麼操作(Excel技巧sumproduct函數實例講解)3

三個公式計算得到的結果都一樣,隻是寫法不一樣。

公式中的(B3:B11="水果"),是一個條件,判斷區域B3:B11是否等于"水果",結果返回邏輯值true或是FALSE。在這裡,這一條件表達式最終運算的結果為:

{FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE}

技巧提示:你在編輯欄中,單獨選中這一條件表達式,然後按F9鍵,就可以看到結果。

那麼經過這一步運算後,

公式一得到的是

=SUMPRODUCT({FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE}*D3:D11)

也就是兩個數組相乘,此時邏輯值直接參與運算符進行數值計算時,TRUE會自動轉化為1,FALSE轉化為0,

=SUMPRODUCT({0;1;0;1;1;0;1;0;0}*D3:D11),

将單元格區域D3:D11的數據代入進去,就是:

=SUMPRODUCT({0;1;0;1;1;0;1;0;0}*{20;20;60;40;30;20;10;15;30}),

到這裡就明白了,就是兩個數組相乘了。

公式二在經過條件表達式那步運算後,得到的是:

=SUMPRODUCT(N{FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE},D3:D11)

那公式二為什麼要在條件表達式外面加個N?

這裡的N是一個函數,用來将不是數值形式的值轉換為數值形式。

因為當sumproduct函數公式裡面用的是逗号時,就表示逗号兩邊是作為獨立的參數參與乘積,也就是條件表達式返回的邏輯值就作為獨立參數存在了,沒有直接參與任何運算符(比如:乘号*)的運算,它是通過sumproduct内部機制進行乘積。上面我們說過sumproduct函數會将非數值型的數組元素,當0處理。

所以條件表達式返回的邏輯值不管是TRUE還是FALSE都會全部被直接當作0對待。

因此,我們才要用N函數來将邏輯值轉化為對應的數值1或0。

知道了公式一和公式二的意思後,公式三就不難理解了。

公式三在經過條件表達式那步運算後,得到的是:

=SUMPRODUCT(({FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE})*1,D3:D11)

在條件表達式後面加了*1,也是為了讓邏輯值直接參與運算符的運算,将其結果轉化為1或0的數組元素。

總結:

我們先把sumproduct函數逗号或乘号前後的數據用A、B來代替,表達為:

=SUMPRODUCT(A,B)和=SUMPRODUCT(A*B)。

當為逗号時,A、B必須同時都是數值或者數組,不能一個是數值,一個是數組;

當為乘号時,A、B可以同時都是數值或者數組,也可以一個是數值一個是數組。

那現在要統計采購的水果總金額,公式怎麼寫,懂了吧?

直接見下圖:

excel表格sumproduct公式怎麼操作(Excel技巧sumproduct函數實例講解)4

3、多條件求和

統計表中單價30元以下的水果的采購總金額。

公式:

=SUMPRODUCT((B3:B11="水果")*(C3:C11<30)*C3:C11*D3:D11)

總結:

SUMPRODUCT多條件統計,不管條件有多少,我們隻管在公式裡,将條件用括号括起來,再用乘号(*)把各個條件連接起來就行了。

今天關于sumproduct函數的基礎應用就說到這了。這個函數還有很多其它用法,大家可以去研究一下。下次我們一起來讨論。

,

更多精彩资讯请关注tft每日頭條,我们将持续为您更新最新资讯!

查看全部

相关圖文资讯推荐

热门圖文资讯推荐

网友关注

Copyright 2023-2024 - www.tftnews.com All Rights Reserved