fbpx

Excel 小教室 – 如何分別統計正負數欄位(例如收入與支出)

2019/04/06 電腦小教室

阿湯發現蠻多人很認真的運用 Excel 做各種不同的使用,今天同樣來解答一下近期收到的問題,原需求是這樣的,公司共有 12 家店,我希望可以各別統計每家店的收和支出,但是填寫在同一列上,或者如果 12 家店分別在 12 列上的話,我要如何一次抓出全部的收入和支出各別為多少,這樣的需求不僅是可以用在會計使用,其實自己記帳也很好用,就不需要收入和支出分開欄位填寫,只要一直線寫到底,再用函數抓出各別的值就好,往下來分享給大家。

如何分別統計正負數欄位

我們先從最簡單的,像下圖一樣,收支都是記錄在同列一上,那麼,我們要怎麼取出收入以及支出各別為多少,往下來看。

首先,如果只有一列的話,最簡單的,我們可以用「SUMIF」這個函數來做,公式如下:

=SUMIF(B2:B17,">0",B2:17)

解說一下公式,第一個 B2:B17 是指要比對的範圍,">0" 是比對條件值大於零,而最後一個就是比對範圍的就將  B2:B17 內符合條件的通通加總起來。

以此類推,支出的因為是負數,所以我們只要將上面的收入公式,把 >0 改為 <0 這樣就搞定了。

第二種情況如下:

如果你有多列數據想一次擷取出收入及支出的話,其實也可以用 SUMIF 來做,修改範圍就好。

不過阿湯想來分享一下 SUMPRODUCT 的用法,往下來看。

公式如下:

=SUMPRODUCT(B2:C17*(B2:C17>0))

SUMPRODUCT 比較像是邏輯運算的概念,中間是用乘號 * 來帶入,意思是 B2:C17 中有大於 0 的就符合條件(後面的括號),結果就會是 True(值為 1),如果不符的為 False(值為 0),所以所有的值跟 1 或 0 相乘後加總就會獲得結果,當然啦,如果跟 0 相乘的就是 0 了,所以只會加總符合條件的。

相對的,支出的公式,只要將 > 改成 < 同樣就收工了,SUMPRODUCT 在這個需求中其實算殺雞用牛刀,但因為 SUMPRODUCT 的用法其實很多元,所以還是用他來解說一下,給大家做為參考。

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

阿湯 作者:阿湯

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

更多關於的文章》

0 留言

發表留言 »

姓名(*)
信箱(*)