close

2712

Excel指定日期為指定週數的公式方法

歐陽曉曉:「錦子老師,這一次是關於一個指定日期為指定週數的公式方法,以4月份為例。

image

每一個月的星期天為一週的結束,就是4/1-4/5會顯示為第一週,4/6-4/12會顯示為第二週,最後一週為每月最後一天(27-30為最後一週),如此類推。

是否要在第二列先輸入日期資料才可以再輸入公式在B1呢?有什麼方法了?

錦子老師:「我們可以將解方法分為幾個步驟:

日期及星期的輸入

點取A2儲存格輸入20204月的任何一天(本例輸入:2020/04/30)

點取B2儲存格,輸入「1」後,將滑鼠指標移至儲存格右下角拖拉方塊上方,按住CTRL鍵不放,再按住滑鼠左鍵向右拖拉到AE2儲存格,完成1~30的輸入。

image

點取B3儲存格輸入公式:

=DATE(YEAR($A$2),MONTH($A$2),B2)

再將公式複到C3:AE3儲存格。

【公式解說】

抓取指定年份及月份的年YEAR($A$2)、月MONTH($A$2)、日B2,再透過DATE函數傳回該日期值。

B3:AE3框選起來,再點取「常用 > 數字格式鈕」。

image

在【設定儲存格格式】對話方塊,點取「自訂」項目。

點取「類型」文字方塊,輸入「DDD」字串,顯示星期英文縮寫,若「DDDD」則顯示星期英文。

image

點取「確定」鈕,結果如下圖。

image

週的排列

點取A1儲存格輸入「1」,作為起始值,並將其設定字型色彩為白色,將其隱藏。

點取B1儲存格輸入公式:

=IF(WEEKDAY(DATE(YEAR($A$2),MONTH($A$2),B2),2)=1,A1+1,A1)

將公式複製到C1:AE1儲存格。

image

【公式解說】

如果傳回的日期DATE(YEAR($A$2),MONTH($A$2),B2)透過WEEKDAY函數以第2種方式傳回的星期值為1 WEEKDAY(DATE(YEAR($A$2),MONTH($A$2),B2),2)=1,則填入左邊儲存格加1的值A1+1,否則填入左邊儲存格的值A1

WEEKDAY(日期值,傳回星期序列方式)

傳回星期序列方式,若為1表示星期日為1,星期一為2依此類推,若為2,表示星期一為1,星期二為2以此類推。

B1:AE1框選起來,再點取「常用 > 數字格式鈕」。

在【設定儲存格格式】對話方塊,點取「自訂」項目。

點取「類型」文字方塊,輸入「[DBNum1]""0""」字串。

image

點取「確定」鈕,結果如下圖。

image

以上就是今天主要學習的知識點,希望對大家有所幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~

image

更多相關影片教學:請點我

部落格相關範例

2021.11.19

2693Excel利用VLOOKUP函數抓取最新項目

2021.11.18

2689EXCEL工作日與休假統計

2021.11.17

2687Excel可以這樣整合資料嗎-Power Query

2021.11.16

2685詢問要從網址格式的字串中取得部分字串(domain)要如何下公式

2021.11.15

2684Excel可以這樣整合資料嗎-合併彙算

2021.11.14

2682根據一年360天的演算法(每個月以30天計),計算兩個日期間相差的天數

2021.11.13

2681如何不讓人可以預覽檔案內容

2021.11.12

2679VBA 在指定欄位後輸入資料

2021.11.11

2675兩表格內數值重複顯示

2021.11.10

2672Excel兩個日期區間的運算(配息日運算)

2021.11.09

2667資料不同數值用不同顏色標示

2021.11.08

2662請問Excel 能實現VLOOKUP函數多條件取得資料嗎?

2021.11.07

2661職場人必會的4excel函數公式,讓你輕鬆學會字元提取

2021.11.06

2659如何利用Excel函數做表格

2021.11.05

2657Excel 回傳所有儲存格字串但排除相同的字串

2021.11.04

2651學生成績-成績條製作

2021.11.03

2650讓自動校正輸入統一的文字

2021.11.02

2649Excel中自訂梯形面積函數


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()