2017年12月4日 星期一

Excel-自動產生月曆並且標示星期六日(TEXT)

(網友提問)如何在 Excel 的工作中,利用輸入年和月後,自動產生該月的月曆(標示星期幾),並且能將星期六、日加以標示,該如何處理?
參考下圖,當年輸入2019、月輸入10後,自動產生該月各日是星期幾,並且將所有的星期六日儲存格用不同色彩標示。
Excel-自動產生月曆並且標示星期六日(TEXT)

【公式設計與解析】
1. 依年月日產生星期幾
儲存格B3:=RIGHT(TEXT(DATE($A$2,$B$2,B3),"[$-zh-TW]aaa;@"),1)
(1) DATE($A$2,$B$2,B3)
利用 DATE 函數取得年月日的日期。
(2) TEXT(DATE($A$2,$B$2,B3),"[$-zh-TW]aaa;@")
將日期代入 TEXT 函數,並依『[$-zh-TW]aaa;@』格式顯示。(週一、週二、…)
(3) RIGHT(TEXT(DATE($A$2,$B$2,B3),"[$-zh-TW]aaa;@"),1
利用 RIGHT 函數取得最右邊一個字,即為一、二、…、六、日。

2. 設定星期六日的格式
如圖,選取儲存格B5:AF14(所有黃色儲存格),設定格式化的條件的規則:
規則類型:使用公式來決定要格式哪些儲存格。
規則說明:=WEEKDAY(DATE($A$2,$B$2,B$3),2)>5
Excel-自動產生月曆並且標示星期六日(TEXT)
在 WEEKDAY 函數中使用參數 2,星期一~星期日對應傳回值 1~7,所以傳回值 6 和 7 為假日(>5)。
Excel-自動產生月曆並且標示星期六日(TEXT)

【延伸學習】
在依年月日產生星期幾的公式中使用參數「[$-zh-TW]aaa;@」,是如何產生的呢?你可以由以下管道取得。
先選取一個日期,再進入「儲存格格式」設定中的日期部分,然後選取「日期/週三」。
image
再切換至「自訂」,即可取得參數設定。
image

沒有留言:

張貼留言

檢視其他文章

好康東東