2011年11月29日 星期二

Excel-週行事曆(VLOOKUP+WEEKDAY)

在 Excel 中如果你在工作表中按日輸入一些資料,做為簡單的行事曆(如下圖左)。現在,想要從這個行事曆資料中摘要出某一週的行事曆,及每天開啟資料夾時自動顯示當週的行事曆,該如何處理?

【準備工作】

將行事曆資料範圍設定名稱為:資料。

【指定週別】

(1) 首先,要求某一週的第一天(星期一):

儲存格E4:=DATE($F$1,1,1)-WEEKDAY(DATE($F$1,1,1),1)+1+($F$2-1)*7

DATE($F$1,1,1):取得某一年的1月1日之日期序列。

WEEKDAY(DATE($F$1,1,1),1):取得某一年的1月1日是星期幾。

DATE($F$1,1,1)-WEEKDAY(DATE($F$1,1,1),1)+1:取得某一年第一週的第一天的日期。

將上式加上($F$2-1)*7,即可取得任一週的第一天。

儲存格E5:=E4+1

複製儲存格E4,貼至儲存格E4:E10。

 

(2) 找出星期幾

儲存格F4:=E4

將儲存格E4的數值格式設定為星期幾格式

 

(3) 查詢工作項目

儲存格G4:=IFERROR(VLOOKUP(E4,資料,3,FALSE),"")

利用 VLOOKUP 函數查詢對應日期的第3欄(工作項目)的資料。

其中的 IFERROR 乃是為了某些日期沒有在行事曆上,可以避免產生錯誤訊息。

如果你使用較早版本的 Excel,沒有提供 IFERROR 函數,可以改用 ISERROR 函數:

儲存格G4:=IF(ISERROR(VLOOKUP(E4,資料,3,FALSE)),"",VLOOKUP(E4,資料,3,FALSE))

最後,複製儲存格F4:G4,貼至儲存格F4:G10。

 

【顯示當週】

如果你對指定週別的算法已有瞭解,則變通一下來求今日當週的資料。

儲存格F11:=TODAY()

儲存格G12:="第"&WEEKNUM(TODAY())&"週"

WEEKNUM 函數可以求得某一日期在當年度是第幾週。

儲存格E14:=DATE($F$1,1,1)-WEEKDAY(DATE($F$1,1,1),1)+1+(WEEKNUM(TODAY())-1)*7

複製儲存格E14,貼至儲存格E14:E20。

沒有留言:

張貼留言

檢視其他文章

好康東東