2009年9月12日 星期六

Excel-陣列應用(計算請假時數)

如果你要將記錄完整的請假記錄表,轉換成摘要資訊,而不想使用樞紐分析工具,則使用陣列公式應該是不錯的選擇。重點說明相關做法:

在記錄表中若要對齊日期,而不要出現例如:2008/1/4、2008/12/4、2008/10/10等字數不一樣多的對齊問題,則在日期儲存格設定格式為自訂:yyyy/mm/dd。

在星期幾的欄位,其公式為B3儲存格為「=A3」,再設定其格式為「星期X」。

請假假別和時數則自行輸入。

整個記錄表應依日期順序,由小到大記錄。

在F1儲存格中若輸入學年度,則會計算由該年度的8/1到次年的7/31之間的各種假別時數。

在F3儲存格中的公式為:

{=SUM(IF(LEFT($C$3:$C$60,2)=E3,IF($A$3:$A$60>=DATE($F$1+1911,8,1),IF($A$3:$A$60<=DATE($F$1+1912,7,31),VALUE(MID($C$3:$C$60,3,1)),),),0))}

再複製到F4:F6。

在G3儲存格中要將時數換算成天數,則將8小時換算為一天,

在G3儲存格中的公式為:

=INT(F3/8) & "天" & MOD(F3,8) & "小時"

再複製到G4:G6。

接著要建立一個Table,要能自動列出該學年度各種假別的日期和時數。

在I3儲存格中的公式為:

{=IF(ISERROR(SMALL(IF(LEFT($C$3:$C$60,2)=I$2,IF($A$3:$A$60>=DATE($J$1+1911,8,1),IF($A$3:$A$60<=DATE($J$1+1912,7,31),$A$3:$A$60,""))),ROW(1:1))),"",SMALL(IF(LEFT($C$3:$C$60,2)=I$2,IF($A$3:$A$60>=DATE($J$1+1911,8,1),IF($A$3:$A$60<=DATE($J$1+1912,7,31),$A$3:$A$60,""))),ROW(1:1)))}

其中ISERROR用於判斷有公式的儲存格,但卻沒有日期值的時候,避免顯示#Num!。

由於要挑出某個區間的日期,所以借用DATE函數,先將學年轉換成西洋年(+1911),再設定日期。

使用陣列時,則透過IF(IF(IF…))方式達到將三個條件執行AND的功能。

使用SMALL函數和ROW(1:1)(複製後會變成ROW(2:2), ROW(3:3)…),將挑選出來的日期陣列,第一個儲存格顯示最小值(日期),下一個儲存格顯示第2最小值(日期),餘類推。

將儲存格往下複製。

接著利用所顯示的日期,利用查表法將時數顯示出來。

在J3儲存格中的公式為:

=IF(ISERROR(MID(VLOOKUP(I3,$A$3:$C$60,3),3,1)),"",MID(VLOOKUP(I3,$A$3:$C$60,3),3,1)&"小時")

將儲存格往下複製。

將I3複製到K3, M3, O3。

將J3複製到L3, N3, P3。

利用陣列公式,可以自動產生摘要表,並且可以查詢各學年,還可以列出所有請假的日期和時間。

1 則留言:

  1. 你好關於以下這一行{=SUM(IF(LEFT($C$3:$C$60,2)=E3,IF($A$3:$A$60>=DATE($F$1+1911,8,1),IF($A$3:$A$60<=DATE($F$1+1912,7,31),VALUE(MID($C$3:$C$60,3,1)),),),0))}
    我輸入之後雖然函數計算的總合是正確的,但是F3那一個欄位就只顯示C3那一欄位的時數,並不會顯示總計的時數,請問是為什麼呢?

    回覆刪除

好康東東