前往
大廳
主題

做出一個依據不同週數顯示不同底色的月曆

紜璃 | 2023-07-17 19:55:04 | 巴幣 0 | 人氣 187

Q:在A1、B1及C1儲存格分別輸入年、月及日,代表第一天,然後在A2儲存格輸入要顯示的天數,最後產生一個根據不同週數顯示不同底色的月曆,結果如下圖。

A:
首先,假設一個月的月曆(或班表等類似的表格)不超過31天,因此可以把自己的工作表擴增到31欄。

然後在A4儲存格輸入公式=DATE(A1,B1,C1),表示在這個儲存格輸入第一天要求的日期,並改變一下顯示的格式,使用「格式」>「數值」>「自訂日期和時間」,依自己喜好設定,然後點選「套用」。



接下來在B4儲存格輸入公式=A$4+1,然後在C4儲存格輸入公式=B$4+1,依此類推將第四列填滿公式。

但是在A2儲存格有限制顯示天數,所以要將達到天數之後的日期都設為不顯示,因此必須將B4的儲存格公式稍微修改一下,也就是說如果前方的儲存格是有日期的,那麼當下這個儲存格就得+1,反之就保持空著,因此把B4儲存格設定公式成=IF(NE(A$4,"'),A$4+1,''),其中NE()函數就是所謂的不等比較!=,也就是說,如果A$4的值不等於空的字串,那麼就設定值為A$4+1,否則就設定值為空的字串。

此外還有一個條件,就是當下這個儲存格必須在合理的顯示範圍,也就是當下這個日期要小於(起始日期+天數),因此還要把B4儲存格公式改成=IF(AND(((A$4+1)<(DATE($A$1,$B$1,$C$1)+$A$2)),NE(A$4,"")),A$4+1,""),使用AND()函數把所有要符合條件的判斷式連結起來,總共兩個條件,第一就是前面那一儲存格不可以為空,第二就是當下儲存格要在顯示範圍內,於是整個IF的判斷式就變成AND(((A$4+1)<(DATE($A$1,$B$1,$C$1)+$A$2)),NE(A$4,"")),修改完成之後,依照公式把C4、D4、...等儲存格填上公式即可。

然後在第五列顯示星期幾,其實就是把上一列的值複製而已,但顯示成不同格式,因此可以在A5輸入公式=A4,然後填滿公式,並改變顯示的格式,使用「格式」>「數值」>「自訂日期和時間」,之後點選「套用」即可。

接下來,就要處理不同底色的問題,可以使用一個功能,叫做「條件式格式設定」,但在那之前要做一些功課,首先將日期對應的星期轉成數字會比較好處理,因此可以使用WEEKDAY()函數,在A6儲存格輸入公式=weekday(A4,2),2表示使用的規則為星期一表示1,星期日表示7,其餘依此類推,然後向右填滿公式。

因為一周天數是7天,因此讓這個數字除以7的商一樣的為一組(表示同一週內),所以必須將A6的儲存格公式修改成=weekday(A4,2)-1,才能讓星期日會在同一週。

但是這樣會有個問題,就是所有的值分別除以7得出的商都一樣,考慮這個月曆都是連續的天數和日期,因此只要讓A6設定好之後,其他儲存格值+1就可以了。
這樣一來,這個值除以7的商就會因為每一週而不一樣,就可以區別出來了。當然這個第六列的值只是一個輔助思考的列而已,將它刪除資料,然後選擇要套用格式條件的範圍(也就是A4:AE5)
點選上方「格式」>「條件式格式設定」,選擇單色,然後格式規則選擇「自訂公式」,格式設定就依自己喜好選擇背景底色,最後最重要的值或公式由下列慢慢講解:

首先,那個輔助列的值完全可以藉由A4儲存格的日期來產生,因此使用ADDRESS()函數來指向A4這個位址,也就是ADDRESS(4,1),這個函數就會產生一個文字位址$A$4,再使用INDIRECT()函數來連接到這個位址並取得值,於是變成INDIRECT(ADDRESS(4,1)),這個值就是A4儲存格所指的日期,然後再使用WEEKDAY()函數取出星期幾的值並且減去1,於是公式變成WEEKDAY(INDIRECT(ADDRESS(4,1)),2)-1,但由於現在條件式格式設定範圍不是單一個儲存格,考慮到每一個儲存格和第一欄的差值,這樣才可以獲得第六列那個輔助思考的值,於是要加上欄號數並減去1,公式變成WEEKDAY(INDIRECT(ADDRESS(4,1)),2)-1+COLUMN()-1,最後把這個值除以7取商數,依照不同商數設定不同底色即可,公式就變成EQ(QUOTIENT(WEEKDAY(INDIRECT(ADDRESS(4,1)),2)-1+COLUMN()-1,7),0)
然後點選下方新增其他規則,依照上述步驟,設定商數分別等於0~5時的底色就好
但這樣有個問題就是超過範圍的區域也被套上底色了,於是要修正各個規則,只要符合上述條件並且符合當前欄數比規定的天數(A2儲存格)少或相等即可,也就是加上COLUMN()<=$A$2這個條件就好,使用AND()函數和前述條件串起就可以了,公式就修改成AND(EQ(QUOTIENT(WEEKDAY(INDIRECT(ADDRESS(4,1)),2)-1+COLUMN()-1,7),0),COLUMN()<=$A$2)
這樣超過天數的部分就不會套用底色了。

最後可以測試一下不同的日期和天數。


創作回應

相關創作

更多創作