2016年2月21日 星期日

Excel-以星期幾為主顯示各月日期的萬年曆(DATE,WEEKDAY)

在下圖中,是一個以星期幾為主,來顯示各月日期的萬年曆,如何在 Excel 的工作表中建立這個萬年曆?
有了這個萬年曆,只要改變儲存格A1(年份),即可顯示當年的月份和星期幾的對照。因此,例如:可以快速找到各月週六的日期。要如何來設計公式?
image

【公式設計與解析】
儲存格B2:=VALUE(TEXT(DATE($A$1,COLUMN(A:A),1)-WEEKDAY(DATE($A$1,
COLUMN(A:A),1),2)+ROW(1:1),"dd"))
(1) DATE($A$1,COLUMN(A:A),1)
藉由 DATE 函數取得各年各月的第一天日期,Excel 會傳回一個數值。
(2) WEEKDAY(DATE($A$1,COLUMN(A:A),1),2)
先藉由 DATE 函數取得各年各月的第一天日期,再利用 WEEKDAY 函數來找出每個月的第一天的傳回值。本例是選取參數 2,代表星期一到星期日,傳回數字 1 到 7。
image
公式中的 COLUMN(A:A) 乃用於向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→ ... →COLUMN(M:M)=12。
(3) 完整公式
儲存格B2:=VALUE(TEXT(第(1)式-第(2)式+ROW(1:1),"dd"))
第(1)式-第(2)式+ROW(1:1):取得每個月的第一個儲存格(儲存格B2)應該顯示的日期,而 ROW(1:1) 向下複製時,會產生 ROW(1:1)=1→ROW(2:2)=2→ ... →ROW(31:31)=31。
TEXT(第(1)式-第(2)式+ROW(1:1),"dd"):透過 TEXT 函數將上式的日期取出日期中的『日期數值』,並以二碼顯示("dd")。當向下複製公式時,即可產生連續的日期數值。
最後再以 VALUE 函數將上式 TEXT 取得的結果(文字)轉換為數字。
複製儲存格B2,貼至儲存格B2:M43。

沒有留言:

張貼留言

好康東東