2011年5月10日 星期二

Excel-列出週六日的日期

在 Excel 中提供了多種方便使用的日期函數,如果要列出某年的週六、週日之日期(如下圖),該如何設計?

首先在儲存格B1中以資料驗證方式,指定儲存格為「清單」,內容為「2010,2011,2012,2013」。

接著設定一個名稱:DATA

DATA:=INDIRECT("A"&DATE($B$1,1,1)&":A"&DATE($B$1,12,31))

公式中的:"A"&DATE($B$1,1,1)&":A"&DATE($B$1,12,31)

假設儲存格B1的內容為2011,

則公式=A40544:A40908,40544為2011/1/1的數值,而40908為2011/12/31的數值。

再透過INDIRECT函數轉成儲存格位址。也就是當選擇了一個年度時,會產生一組位址和日期數值相同的儲存格陣列。

(1) 找出日期

儲存格B3:{=SMALL(IF(WEEKDAY(ROW(DATA),2)>5,ROW(DATA),FALSE),ROW(1:1))}

ROW(DATA)會將日期位址的陣列轉換回數值(例如:40544、40545、40546…)陣列,而WEEKDAY函數用以找出是否為星期六、日(傳回值為6,7)。

SMALL函數可以依序(藉由ROW函數)由小到大列出日期。

複製儲存格B3,往下貼上。

(2) 顯示星期六、日

儲存格C3:=CHOOSE(WEEKDAY(B3,2)-5,"六","日")

因為WEEKDAY(B3,2)會傳回6或7,所以減5後得1或2,在CHOOSE函數中可得「六或日」。

複製儲存格C3,往下貼上。

(3) 顯示月份

儲存格A4:=IF(MONTH(B4)=MONTH(B3),"",MONTH(B4)&"月")

因為只有該月的第一天會顯示月份,所以只要比對和上一個儲存格所得的月份不同者顯示月份,否則顯示空白。

複製儲存格A4,往下貼上。

同理,如果你想要只找出某一年中星期二的所有日期:

儲存格F3:{=SMALL(IF(WEEKDAY(ROW(DATA),2)=2,ROW(DATA),FALSE),ROW(1:1))}

複製儲存格F3,往下貼上。

--------------------------------------------

詳細函數說明請參閱微軟網站:

INDIRECThttp://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx

INDIRECT:傳回文字串所指定的參照位址。

語法:INDIRECT(ref_text,[a1])

ref_text:單一儲存格的參照位址,其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱,或定義為字串的儲存格參照位址。

a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。

 

WEEKDAYhttp://office.microsoft.com/zh-tw/excel-help/HP010343015.aspx

WEEKDAY:傳回符合日期的星期。給定的日預設為介於1(星期日)7(星期六)之間的整數。

語法:WEEKDAY(serial_number,[return_type])

Serial_number:要找的日期的代表序列值。

Return_type:決定傳回值類型的數字。

 

RETURN_TYPE

傳回的數字

1或省略

數字1(星期日)7(星期六)

2

數字1(星期一)7(星期日)

3

數字0(星期一)6(星期六)

11

數字1(星期一)7(星期日)

12

數字1(星期二)7(星期一)

13

數字1(星期三)7(星期二)

14

數字1(星期四)7(星期三)

15

數字1(星期五)7(星期四)

16

數字1(星期六)7(星期五)

17

數字1(星期日)7(星期六)

2 則留言:

  1. 我試出來不行,是不是定義名稱那邊有錯?

    回覆刪除
  2. 我試出來不行,是不是定義名稱那邊有錯?

    回覆刪除

檢視其他文章

好康東東