2010年5月23日 星期日

Excel-建立萬年曆

如果要使用Excel來建構一個全年各月依星期幾對照的萬年曆(如下圖),該如何設計呢?

本例以Excel 2010來練習:

在儲存格A3中輸入「星期日」,再往下複製到A3:A40。

在儲存格B3中輸入:

=DATEVALUE($B$1&"/"&B$2&"/1")-WEEKDAY(DATEVALUE($B$1&"/"&B$2&"/1"))+1

然後複製儲存格B3至儲存格B3:M3。

其原理是:

(1)利用年月日代入DATAVALUE函數,轉換為日期格式。

(2)再代入WEEKDAY函數,求得每個月1日為星期幾。

(3)以上兩數相減再加1,即可得到最近的星期日是幾月幾日。

在儲存格B4中輸入:=B3+1,然後複製儲存格B4至儲存格B4:M40。

至此,本年度的每一天都列出來了。

但是希望能將各欄中不屬於該月的日期淡化處理,利用格式化條件來做:

選取所有已列出日期的儲存格,設定條式條為「使用公式來決定要格式化哪些儲存格」。

輸入公式:=MONTH(B3)<>B$2

其原理是利用MONTH函數取出各日期的月份,和表中各月比較,若不相同則文字色彩予以淡化。

接下來要建構萬年曆的使用了。

首先,在Excel選項中的[自訂功能區]中,勾選[開發人員]。

然後,在[開發人員]功能表下,選取[插入]下拉式清單,然後選取[微調按鈕]。

在儲存格中拖曳出適當大小,並在[目前值]中輸入2010,[最小值]輸入1900,最大值輸入2100,遞增值輸入1,儲存格連結點選B1儲存格。意思是該微調按鈕可以調整的範圍為1900~2100,目前指在2010,調整結果為顯示在B1儲存格。

如此,只要使用微調按鈕,就可以使用為萬年曆了。

沒有留言:

張貼留言

好康東東