[Excel教學] 工時計算表製作 (應用函數SUMIF 和 SUMPRODUCT)

剛剛在網上看到有人問關於工時計算表的問題，其情況大約如下：
他的公司有不同的工作內容，員工每天會在不同時間擔當不同工作，他需要利用自動統計的Excel表統計每位員工負責哪一項工作，做了多久，但不知道一些函數怎麼寫。

首先這個是他提供的原檔案：
Excel原檔案下載
Excel原檔(Google試算表，請以建立副本的方式進行測試)

此檔案已輸入了raw data，可看到上面 A1:U1 的標籤為不同工作內容的分類，A3:AA3表示了每個時段的持續時間(分鐘)。

而在表A4:AA23中標示了員工在當天不同的時段負責哪一類工作。

發問者的要求是希望利用表A4:AA23的資料，在表AV5:AV20以小時為單位計算每位員工負責不同工作的工作時間及計算總工作時間。原檔在AV5:AV20填入了部分數字作為結果參照，而完成檔則以公式取代之。


然後這裡是我完成的Excel檔案：
Excel完成案下載
Excel完成檔(Google試算表，請以建立副本的方式進行測試)

我利用函數SUMIF和 SUMPRODUCT完成了統計的需要，公式分為兩類，一類是只需尋找一個對應狀態的時間統計，這部分比較簡單，用SUMIF函數即可完成：

SUMIF的語法如下：
SUMIF(range, criteria, [sum_range])

• range   必要。這是要依據準則進行評估的儲存格範圍。每個範圍中的儲存格都必須是數字，陣列或或包含數字的參照。空白或文字值會被忽略。
  
• criteria   必要。這是以數字、運算式、儲存格參照、文字或函數的形式來定義要加總哪些儲存格的準則。例如，criteria 可透過 32、">32"、B5、"32"、"apples" 或 TODAY()來表示。
  重要  任何包含邏輯符號或數學符號的文字準則或準則都必須以雙引號括住 (")。 如果 criteria 是數值，則不需要使用雙引號。
  
• sum_range   選用。如果您要加總的不是 range 引數中指定的儲存格，這便是要加總的實際儲存格。如果省略 sum_range 引數，Excel 會加總 range 引數中所指定的儲存格 (即是套用準則的相同儲存格)。
  

(詳細參考MS Office的說明)

根據要求，AE4這格對應小明負責"In"的時間，首先range設為B5:AA5(＄的符號是鎖定，使得在拉動公式時，對應＄的範圍不會移動)，criteria設為AE22，即在範圍range檢查數據是否等於AE22，是就輸出1，不是就輸出0，並產生一Array，例如如果range的Array是{In, A, B, In, A, In}，則會產生{1, 0, 0, 1, 0, 1}的結果。sum_range設為B3:AA3，從range得出的Array會與sum_range的Array進行點積，例如sum_range如果是{2, 3, 4, 5, 6, 7}，SUMIF就會進行：
{1, 0, 0, 1, 0, 1}‧{2, 3, 4, 5, 6, 7} = 2 + 5 + 7 = 14

所以這公式會得出在B5:AA5中對應AE22的位置上方的時間總和，最後再除60就得出以小時為單位的時間。

(截圖截少了一部分：AK4=SUMPRODUCT(SUMIF(＄B5:＄AA5,AK＄22:AK＄24,＄B＄3:＄AA＄3))/60)

另一類如上圖要尋找多個不同對應工作狀態的時間統計，需要用到"或(or)"的條件，但Excel內建的SUMIFS只有"及(and)"的條件，但SUMIF又不支援多條件，所以要借用SUMPRODUCT來完成這個工作。

SUMPRODUCT的語法為：
SUMPRODUCT(array1, [array2], [array3], ...)

• Array1     必要。 這是您要求元素乘積和的第一個陣列引數。
  
• Array2, array3,...     選用。 這是您要求元素乘積和的第 2 個到第 255 個陣列引數。
  

(詳細參考MS Office的說明)

SUMPRODUCT是將所有事入的Array進行點積的函數，例如如果三個Array分別是{1, 2}, {3, 4}, {5, 6}，SUMPRODUCT就會計算：
{1, 2}‧{3, 4}‧{5, 6} = 1 x 3 x 5 + 2 x 4 x 6 = 63

如果我沒理解錯誤，SUMIF的運作過程會如上面所說產生Array，雖然輸入複數條件時它不能正常運作，但會產生複數Array並會在SUMIF加起來，只要利用SUMPRODUCT將結果進行點積就可得到"或(or)"的結果。

所以 SUMPRODUCT(SUMIF(B5:AA5,AK22:AK24,B3:AA3))先在B5:AA5中檢查AK22:AK24的三個條件，每檢查一個產生一個Array，並將結果加總。

例如有三個條件A、B和C，SUMIF在range {A, B, C, D}中進行三次檢查，對應條件A得出Array {1, 0, 0, 0}，對應B {0, 1, 0, 0}，對應C {0, 0, 1, 0}，然後進行加總：
{1, 0, 0, 0} + {0, 1, 0, 0} + {0, 0, 1, 0} = {1, 1, 1, 0}

在SUMPRODUCT中，這個結果 {1, 1, 1, 0} 會和sum_range的Array點積，得出對應條件A、B和C的sum_range數值的加總。

SUMPRODUCT(SUMIF(B5:AA5,AK22:AK24,B3:AA3))就會把B5:AA5中符合AK22:AK24任一條件的位置上方的時間進行加總，而得出總工時。

利用同一原理，SUMPRODUCT還可以這樣寫：
SUMPRODUCT(( (B5:AA5=AK22) + (B5:AA5=AK23) + (B5:AA5=AK24) ),B3:AA3)
因為這是，加即"或(or)"的意思，但如果有多個條件的話，這種寫法就會變得非常累贅，用SUMPRODUCT(SUMIF())就能快速得到所需結果。

最後也是除60得出小時為單位的工時。