2010年7月5日 星期一

Excel-員工生日摘要表

在Excel中有一個員工基本資料表,為了慶生會,現在要製作一個以季別區分的生日摘要表。(如下圖)

輸入陣列公式(輸入完成,按Ctrl+Shift+Enter鍵):

(1) 找出人名

儲存格F2:{=IFERROR(VLOOKUP(SMALL(IF(MONTH($C$2:$C$25)={1,2,3},$A$2:$A$25,""),ROW(1:1)),$A$1:$D$25,2),"")}

公式中 IF(MONTH($C$2:$C$25)={1,2,3},$A$2:$A$25,"") 為求得生日月份為1,2,3月的序號儲存格。

公式中 SMALL(IF(MONTH($C$2:$C$25)={1,2,3},$A$2:$A$25,""),ROW(1:1)),利用ROW和SMALL函數來找出第1,2,3,4…個序號。

公式中 VLOOKUP(SMALL(IF(MONTH($C$2:$C$25)={1,2,3},$A$2:$A$25,""),ROW(1:1)),$A$1:$D$25,2),利用序號在VLOOKUP函數中查表求得姓名。

[注意:如果有人同名同姓,可能會查表錯誤!]

最後利用IFERROR函數,使發生錯誤的儲存格顯示空的內容(因為儲存格會往下複製)。

儲存格H2:{=IFERROR(VLOOKUP(SMALL(IF(MONTH($C$2:$C$25)={4,5,6},$A$2:$A$25,""),ROW(1:1)),$A$1:$D$25,2),"")}

儲存格J2:{=IFERROR(VLOOKUP(SMALL(IF(MONTH($C$2:$C$25)={7,8,9},$A$2:$A$25,""),ROW(1:1)),$A$1:$D$25,2),"")}

儲存格L2:{=IFERROR(VLOOKUP(SMALL(IF(MONTH($C$2:$C$25)={10,11,12},$A$2:$A$25,""),ROW(1:1)),$A$1:$D$25,2),"")}

各欄往下複製。

(2) 找出對應的生日

欄據人名於表格中以VLOOKUP函數來查表出生日:

儲存格G2:=IFERROR(VLOOKUP(F2,$B$2:$C$25,2,FALSE),"")

將儲存格G2複製到儲存格I2,K2,M2。再於各欄往下複製。

將生日欄位的數值格式自訂為「mm/dd」,則生日中的月份和日期都會以2碼顯示。

如果要以單位和月份對照來看各月份的生日人數,該如何計算?

儲存格P2:=SUMPRODUCT((MONTH($C$2:$C$25)=ROW(1:1))*($D$2:$D$25=P$1)*1)

將儲存格P2複製到儲存格P2:R13。

公式中:

以ROW(1:1)=1, ROW(2:2)=2,ROW(3:3)=3…

($D$2:$D$25=P$1)*1為使邏輯值(True,False)轉換成數值(1,0)。

沒有留言:

張貼留言

好康東東