fbpx

Excel 小教室 – 計算符合多重條件下的總和,SUM+IF+陣列的運用

2019/03/16 電腦小教室

前陣子分享了一個很實用的 Excel 函式「SUMIF」的基本用法,可以針對單一條件篩選然後進行加總,果然湯粉們非常會舉一反三,立刻學成歸國後反問了阿湯,如果二個以上條件的話怎麼做?其實二個條件以上的做法也有不少種,阿湯腦袋馬上想到的至少就有三種做法,今天來分享一個我自己比較常用的方式,也就是利用 SUM + IF 二個函式並搭配陣列來使用,看到陣列二個字可能你就打退堂鼓,但其實沒有那麼難,往下來為大家解說一下。

SUM + IF + 陣列的使用(符合多重條件的總和):

阿湯往下的範例是符合三種條件下的總和,這方法可以無限增加條件,你要符合十種也是可以的,依樣畫葫蘆而已,這次用的是 SUM + IF + 陣列,另外像是透過 SUMIFS 或 SUMPRODUCT 也都能做到,文末阿湯也會分享一下公式。

假設我們要符合三重條件,範例為條件一:A,條件二:B,條件三:C 的總和,公式如下:

=SUM(IF(B2:B11=”A”,IF(C2:C11=”B”,IF(D2:D11=”C”,E2:E11,0))))

來解析一下公式,SUM 就是總和不太需要解釋,裡面大家可以看共包了三個 IF 條件式,一個個來看:

  • B2:B11=”A”   =>  意思是 B2 到 B11 符合欄位等於 A 這個值的,當然也可以改為大於、小於都沒問題
  • C2:C11 =”B”  => 同上,包括第三個 IF 中的 D2:D11 也是同樣的道理
  • E2:E11  =>  這裡是指要加總的區域

最後一個 0 的意思指,若不符條件則為 0。

但由於這是陣列算法,請往下看,先不要急著 Enter 結束這個算式。

在設完公式後,記得要在公式列裡按下「Ctrl+Shift+Enter」,會看到公式的最前跟後出現了 { },表示這是陣列,這樣就會正常運算了。

接下來我們就可以依此類推的做完剩下的公式,當然你要改成只符合二個條件,比如一跟三符合你要的就可以,那就是二個 IF 就搞定。

更多做法:

首先是 SUMIFS,一個函式就搞定也行,也用不到陣列,公式如下:

=SUMIFS(E2:E11,B2:B11,”A”,C2:C11,”C”,D2:D11,”B”)

解說:E2:E11 是加總範圍,後面則是可以無限增加條件,都是用逗號隔開,先寫範圍再寫條件,條件也可以改為大於或小於。

第三種做法則是 SUMPRODUCT,同樣也是一個函式搞定,只是 SUMPRODUCT 能做的事太多,阿湯一般較不會以這個做教學,公式如下:

=SUMPRODUCT((B2:B11=”A”)*1,(C2:C11=”B”)*1,(D2:D11=”C”)*1,E2:E11)

解說:這是用 AND 運算的概念,只要有一個不符條件就會是 0,條件同樣可以無限增加,符合條件的會從 E2 到 E11 中去加總

以上共三種方法提供給大家參考,但 SUM + IF + 陣列學起來,以後用到機率很多呦。

你可能對這些內容也有興趣

阿湯 作者:阿湯

對電腦 & 網路資訊充滿興趣與熱情、瘋了似的每日堅持發文,「阿湯」這個名字是由湯姆克魯斯而來的,雖然我沒有他帥氣,也不會演電影,但我會寫文章….

更多關於的文章》

0 留言

發表留言 »

姓名(*)
信箱(*)