sumif函數的幾種常見用法

來源:互聯網
上載者:User

   一、SUMIF函數的一個怪異用途

  Excel表格中的SUMIF函數在條件求和時經常使用,一般習慣于于根據某一列計算另一列的數字之和。

  例1:假設定A列是部門,B列是姓名,C列是工資,可以用下面的公式算出所有財務部員工的工資總和:

  =SUM(A1:A100,"財務部",C1:C100)

  今天說的不是這個正規用法,而是給大家介紹一種看上去和SUMIF毫無關係的例子。

  如下圖中,是一些不規則的資料,只關注填充顏色的地區,現在要計算所有A下面數字之和,看上去這個題沒有好的思路,其實這個題恰好可以用SUMIF函數輕鬆解決。

  =SUMIF(B2:E7,B12,B3:E8)

  分析一下這個公式,第二個參數是條件可以瞭解,第一個參數和第二個參數有什麼關係呢?大家仔細看會發現第三個參數正好是第一個參數的地區向下位移一個單位。

  總結,SUMIF函數並不是只對規則地區求和,還可以對不規則的排列進行條件求和

  二、SUMIF函數入門篇

  SUMIF作為Excel2003 中一個條件求和函數,在實際工作中發揮著強大的作用,雖然在2007以後被SUMIFS所取代,但它依舊是一個EXCEL函數 的經典。特別是進階用法,依舊適用於後面的版本。

  SUM是求和,IF是如果。如果什麼。。就求和,其實就是按條件求和。它的結構如下:

  SUMIF(條件判斷地區,條件,求和地區)

  說明:

  1 條件判斷地區。即然是條件求和,一是要有條件,二是要有判斷的地區,SUMIF的第二個參數就是求和的條件,第一個參數呢就是拿條件來這個地區進行對比的地區。第一個參數必須是儲存格參照 。

  2、條件。按條件求和就得有條件,該條件可以是字串("ABC",可以用大於等對比符號串連起來的條件運算式(">100"),也可以使用萬用字元來表示匹配求和("AB*C")。

  3、求和地區。求和函數可以沒有求和的地區算什麼求和函數,該參數為儲存格範圍引用。如C2:C100。

  4、如果求和地區和判斷地區是一個地區該怎麼辦呢?如在C2:C100中計算大於100的數字之和。在這種情況下SUMIF函數由三個參數變換為兩個參數。即:

  SUMIF(求和地區,條件)

  例:如下圖所示。要求根據左表中的商家明細表,產生右側的匯總表,匯總出商家的總進貨量和總金額。

  公式:

  =SUMIF($A$2:$A$8,$F3,C$2:C$8)

  公式說明:

  $A$2:$A$8是判斷地區,拿F3的商家名稱在這個地區進行判斷是否相同,如果上同,就把C2:C8地區的金額進行求和。

  如果只按C列進判斷求和呢?比如,計算C列金額大於200的金額之和。公式可以變換為:

  SUMIF(C$2:C$8,">200")

  三、SUMIF函數技巧篇

  在學習了SUMIF函數入門篇後,在常用技巧篇中將學習SUMIF函數常用的一些技巧。

  談到隔列求和,可能就會想到用陣列公式,其實只需要用SUMIF函數就可以輕鬆實現。

  例:計算費用表中的計劃和實際合計數。

  公式:=SUMIF(B$2:G$2,B$2,B3:G3)

  提示:SUMIF函數不只是左右示和,還可以上下求和。

  四、SUMIF函數進階篇

  SUMIF函數一般情況下只能進行一個條件的求和,但在二般情況下卻可以實現對多個條件的求和。什麼是二般情況下呢?

  SUMIF函數的第二個參數是“求和的條件”,這個參數可以用數組形式。比如

  {"A","B"} {">5","<10"}

  條件使用數組形式後,SUMIF會分別根據兩個條件計算出結果,然後再用SUM計算出結果。

  1、對多個商品進行求和

  例:如下圖所示。要求計算商品名稱為手機和冰箱的銷售之和。

  公式:=SUM(SUMIF(A1:A9,{"手機","冰箱"},C2:C9))

  公式說明:

  SUMIF(A1:A9,{"手機","冰箱"},C2:C9) 會分別計算出手機的銷量(16)和與冰箱的銷售量(14)。返回一個數組,即:{16,14},

  SUM():對SUMIF返回的兩個銷售量進行二次求和。

  2、按數字區間求和

  例:如圖一所示。要求計算銷售數量大於等5,小於10的銷售數字之和。

  公式:=SUM(SUMIF(C2:C9,{"<10","<5"})*{1,-1})

  公式說明:

  SUM(SUMIF(C2:C9,{"<10","<5"}):因為大於等5,小於10的區間和可以用<10的和減去<5的和。所以先分別計算出這兩個區間的和{21,10}

  SUMIF()*{1,-1} 因為最終的結果是兩個數相減,所以這裡用數組與數組的計算,把第二個數變成負數{21,-10}。這樣在後面用SUM求和時就可以讓這兩個數相減了。 即SUM( {21,-10})=11

相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。