在下圖中,是一個以星期幾為主,來顯示各月日期的萬年曆,如何在 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。

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(3) 人氣()