fbpx

Excel 小教室 – SUMPRODUCT 函數實戰運用基礎(累積休假天數與未休天數計算)

很多人遇到「多元條件」的總和計算時都會想的很複雜,但確實,Excel 的函數如果要達成多元條件成立才加總,大部份來說是不容易達成的,甚至要分好幾個函數才能做到,不過其實在 Excel 中有一個非常實用的函數叫做 SUMPRODUCT,這是一個用來計算總和以及邏輯運算合體的函數,簡單的說,當你需要滿足條件 A、B、C 時才加總 D,那麼就可以只用這一個函數來達成,前提是你要理解 SUMPRODUCT 的使用原理,往下就用個簡單的範例來為大家說明。

SUMPRODUCT 函數實戰運用基礎

範例檔這裡下載:請點我

跟大家先講解一下今天想要達成的需求是什麼,員工一共有四位,後面是休假記錄,阿湯是隨便打的,假設只要有休假就會在後面持續往下記錄,累積休假天數的部份,我們希望的結果是,可以看到某個日期為止已經累積多少休假以及未休天數,比如說李大尾到 1/3 是休假 1 天,那麼累積休假會是 1,到 1/5 時累積休假顯示會是 2,但在 1/3 那欄的累積休假仍然要維持原本的 1,「也就是計算每天目前為止的結果」。

理解要做什麼後,我們往下來實踐。

先提供 G2 的公式如下:

=SUMPRODUCT(($E$2:E2=E2)*($F$2:F2))

我們只需要在 G2 跟 H2 完成公式,後面只要往下拉就可以搞定,那麼來解說一下 G2 這個公式。

原則上今天是要講 SUMPRODUCT 的運用,所以二個欄位我都只用 SUMPRODUCT 來做,SUMPRODUCT 最基本的理解方法就是,指定一個範圍後如果給予條件,那麼就是條件式,如果指定一個範圍無其他條件那就是原始數值。

首先用 G2 來說明 SUMPRODUCT 的運作原理。

SUMPRODUCT 的每個括號裡都會是一個加總計算或者是「條件」,第一個括號 ($E$2:E2=E2) 是指 E2 到 E2 之間等於 E2 欄位值的話就為 1(這個是條件式),而阿湯第一個 E2 加上了 $E$2 就是要讓下拉時只變動後方的值,比如在 1/8 那欄,就會變成

=SUMPRODUCT(($E$2:E8=E8)*($F$2:F8))

這麼一來就可以維持著計算到「目前日期」為止的欄位,我們來說明 G3 那一格的公式,拆解後更能理解 SUMPRODUCT 的做法:

=SUMPRODUCT(($E$2:E3=E3)*($F$2:F3))

如果把他拆解之後,會是 (E2 是否等於 E3) 乘於 (F2) + (E3 是否等於 E3) 乘於 (F3),所以得到結果 (0*1)+(1*1)=1。

不知道大家有理解 SUMPRODUCT 的用法了嗎?  再舉個例:

=SUMPRODUCT((E2:E3=E3)*(A2:A3>1)*(F2:F3))

(E2 是否等於 E3) 乘於(A2 是否大於 1)乘於 (F2) +(E3 是否等於 E3) 乘於(A3 是否大於 1)乘於 (F3),以此類推,可以加入各種條件式。




所以未休天數的累積很簡單,公式如下:

=SUMPRODUCT(($A$2:$A$5=E2)*($B$2:$B$5))-G2

條件式就是將目前員工(E2)比對出 A2~A5 之間的員工後,再加總 B 欄的應休天數,但因為每個員工只有一欄應休天數,所以會很準確,最後再減掉 G 欄位已經累積的休假天數,就會得出目前為止為休天數了。

最後將 G 跟 H 的公式往下拖曳結束這回合。

想看更多 Office 教學嗎?全新的影音頻道 Office 知識家上線囉,快點擊圖片前往訂閱。

office教學

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

作者:阿湯

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

更多關於的文章》

0 留言

發表留言 »

姓名(*)
信箱(*)